Page 299 - PYTHON-12

23. Write SQL Commands for (i) to (v) and write the outputs for (vi) to (viii) on the basis of the following table:
Table: FURNITURE
NO ITEM TYPE DATEOFSTOCK PRICE DICOUNT
1 WhiteLotus DoubleBed 2002-02-23 3000 25
2 Pinkfeathers BabyCot 2002-01-29 7000 20
3 Dolphin BabyCot 2002-02-19 9500 20
4 Decent OfficeTable 2002-02-01 25000 30
5 Comfortzone DoubleBed 2002-02-12 25000 30
6 Donald BabyCot 2002-02-24 6500 15
(i) To list the details of furniture whose price is more than 10000.
(ii) To list the Item name and Price of furniture whose discount is between 10 and 20.
(iii) To delete the record of all items where discount is 30.
(iv) To display the price of ‘Babycot’.
(v) To list item name, type and price of all items whose names start with ‘D’.
(vi) Select Distinct Type from Furniture;
(vii) Select Max(Price) from Furniture where DateofStock>’2002-02-15’;
(viii) Select Count(*) from Furniture where Discount<25;
24. Write the outputs of the following:
(i) Select POWER(2,5);
(ii) Select ROUND(10.195,2);
(iii) Select TRUNCATE(10.195,2);
(iv) Select SUBSTR(‘COMPUTER SCIENCE’,3,4);
(v) Select INSTR(‘Technical Teaching’,’ch’);
25. Write SQL Commands for the following on the basis of the given table GRADUATE:
Table: GRADUATE
SL.No. NAME STIPEND SUBJECT AVERAGE RANK
1 KARAN 400 PHYSICS 68 1
2 RAJ 450 CHEMISTRY 68 1
3 DEEP 300 MATHS 62 2
4 DIVYA 350 CHEMISTRY 63 1
5 GAURAV 500 PHYSICS 70 1
6 MANAV 400 CHEMISTRY 55 2
7 VARUN 250 MATHS 64 1
8 LIZA 450 COMPUTER 68 1
9 PUJA 500 PHYSICS 62 1
10 NISHA 300 COMPUTER 57 2
(i) List the names of those students who have obtained rank 1 sorted by NAME.
(ii) Display a list of all those names whose AVERAGE is greater than 65.
(iii) Display the names of those students who have opted COMPUTER as a SUBJECT with an AVERAGE of
more than 60.
(iv) List the names of all the students in alphabetical order.
(v) SELECT * FROM GRADUATE WHERE NAME LIKE “% I %”;
(vi) SELECT DISTINCT RANK FROM GRADUATE;
26. (a) What is the difference between Candidate key and Alternate key?
(b) What is the degree and cardinality of a table having 10 rows and 5 columns? Relational Database and SQL

12.69