Page 292 - PYTHON-12

(b) Consider the following table CARDEN given below:
Table: CARDEN

Ccode CarName Make Color Capacity Charges
501 A-Star Suzuki RED 3 14
503 Indigo Tata SILVER 3 12
502 Innova Toyota WHITE 7 15
509 SX4 Suzuki SILVER 4 14
510 C Class Mercedes RED 4 35
Write SQL commands for the following statements:
(i) To display the names of all the silver-coloured cars.
(ii) To display name, make and capacity of cars in descending order of their sitting capacity.
(iii) To display the highest charges at which a vehicle can be hired from CARDEN.
Ans. (i) SELECT CarName FROM carden WHERE Color LIKE ‘Silver’;
(ii) SELECT CarName,Make,Capacity FROM carden ORDER BY Capacity DESC;
(iii) SELECT MAX(Charges) FROM carden;
(c) Give the output of the following SQL queries:
(i) SELECT COUNT(DISTINCT Make) FROM CARDEN;
(ii) SELECT MAX(Charges),MIN(Charges) FROM CARDEN;
(iii) SELECT COUNT(*),Make FROM CARDEN;
(iv) SELECT CarName FROM CARDEN WHERE Capacity=4;
Ans. (i) COUNT(DISTINCT Make)
4
(ii) MAX(Charges) MIN(Charges)
35 12
(iii) COUNT(*) Make
5 Suzuki
(iv) CarName
SX4
C Class
19. Consider the tables EMPLOYEE and SALGRADE given below and answer (a) and (b) parts of this question.
Table: EMPLOYEE

ECODE NAME DESIG SGRADE DOJ DOB
101 Abdul Ahmad EXECUTIVE S03 23-Mar-2003 13-Jan-1980
102 Ravi Chander HEAD-IT S02 12-Feb-2010 22-Jul-1987
103 John Ken RECEPTIONIST S03 24-Jun-2009 24-Feb-1983
105 NazarAmeen GM S02 11-Aug-2006 03-Mar-1984
PriyamSen
29-Dec-2004
19-Jan-1982
CEO
S01
108
Computer Science with Python–XII 12.62 (a) Write SQL commands for the following statements:
Table: SALGRADE
SALARY
HRA
SGRADE
18000
56000
S01
12000
32000
S02
S03
24000
8000
(i) To display the details of all EMPLOYEEs in descending order of DOJ.
(ii) To display NAME and DESIG of those EMPLOYEEs whose SALGRADE is either S02 or S03.