Page 25 - IP

CBSE AISSCE 2018 Marking Scheme for Informatics Practices
(​Sub Code: 065 Paper Code 90​)

Ans SELECT * FROM FURNITURE WHERE MANUFDATE BETWEEN ’2016-01-01’ AND
’2017-06-15’;
OR
SELECT * FROM FURNITURE WHERE MANUFDATE >= ’2016-01-01’ AND
MANUFDATE <= ’2017-06-15’;
OR
SELECT * FROM FURNITURE WHERE MANUFDATE BETWEEN ’01-JAN-2016’ AND
’15-JUN-2017’;
OR
SELECT * FROM FURNITURE WHERE MANUFDATE >= ’01-JAN-2016’AND
MANUFDATE <=’15-JUN-2017’;
(½ mark for ​SELECT​)
(½ mark for ​WHERE​)
vii) To display the average ​PRICE ​of all the Furniture Items, which are made of Wood with 1
WCODE​ as ​W02​.

Ans SELECT AVG(PRICE) FROM Furniture
WHERE WCODE = ‘W02’;
(½ mark for ​SELECT​)

(½ mark for ​WHERE​)
OR
(½ mark for ​SELECT​, ½ mark for ‘​FROM​’ clause)

viii) To display ​WCODE​ wise, ​WCODE​ and the highest price of Furniture Items.

Ans SELECT WCODE, MAX(PRICE) FROM Furniture
GROUP BY WCODE;
(½ mark for ​SELECT​)
(½ mark for ​GROUP BY)
ix) SELECT SUM(PRICE) FROM Furniture WHERE WCODE=’W03’; 1

Ans SUM(PRICE)

6500

(1 mark for correct answer)
(x) SELECT COUNT(DISTINCT PRICE) FROM Furniture; 1

Ans COUNT(DISTINCT PRICE)

5

(1 mark for correct answer)

6 (a) Write SQL query to create a table ‘Inventory’ with the following structure: 2
Field Type Constraint
MaterialId Integer Primary key
Material Varchar(50) NOT NULL
Category Char

DatePurchase Date

Page 10 of 13