Page 24 - IP

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

5. Consider the following table ‘Furniture’. Write SQL commands for the statements (i) to
(viii) and write output for SQL queries (ix) and (x).
Table : Furniture
FCODE NAME PRICE MANUFDATE WCODE
10023 Coffee table 4000 19-DEC-2016 W03
10001 Dining table 20500 12-JAN-2017 W01

10012 Sofa 35000 06-JUN-2016 W02
10024 Chair 2500 07-APR-2017 W03
10090 Cabinet 18000 31-MAR-2015 W02

(i) To display ​FCODE​, ​NAME​ and ​PRICE​ of items that have ​PRICE​ less than ​₹​ ​5,000. 1

Ans SELECT FCODE, NAME,PRICE FROM Furniture
WHERE PRICE <5000;
(​ ½ mark for​ ​SELECT​)
(​ ½ mark for​ ​WHERE)

(ii) To display ​NAMES and ​PRICE of those Furniture Items that have ‘table’ anywhere in 1
their names.
Ans SELECT NAME,PRICE FROM Furniture
WHERE NAME LIKE ‘%table%’;
(​ ½ mark for​ ​SELECT​)

( ​½ mark for​ ​WHERE)
(iii) To display ​WCODE​ of Furniture Items. There should be no duplicate values. 1

Ans SELECT DISTINCT(WCODE) FROM Furniture;
(​ ½ mark for ​SELECT​)

( ​½ mark for​ ​DISTINCT)
(iv) To display the ​NAMES and PRICE increased by 500.00 of all the furniture items. (Price 1
should only be displayed as increased; there should be no increase in the data in the
table)

Ans SELECT NAME, PRICE+500 FROM Furniture;
(​½ mark for​ ​SELECT​)
(​½ mark for​ ​PRICE+500)
(v) T​o display​ ​FCODE​ and​ ​NAME​ ​of each Furniture Item in descending order of​ ​FCODE. 1

Ans SELECT FCODE, NAME FROM Furniture
ORDER BY FCODE DESC;

(½ mark for ​SELECT​)
(½ mark for ​ORDER BY​)

vi) To display the details of all the Furniture Items which have Manufacturing 1
date(​MANUFDATE​) between ​01-JAN-2016 and ​15-JUN-2017 (inclusive of both the
dates).

Page 9 of 13