Chapter 12 Structured Query Language Solutions
Question - 11 : - Give a suitableexample of a table with sample data and illustrate Primary and Alternate Keysin it. Consider the following tables CARDEN and CUSTOMER and answer (b) and (c)parts of this question :
Table : CARDEN
| Ceode | CarName | Make | Colour | Capacity | Charges |
| 501 | A-Star | Suzuki | RED | 3Q | 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 |
Table : CUSTOMER
| CCode | Cname | Ceode |
| 1001 | Hemant Sahu | 501 |
| 1002 | Raj Lai | 509 |
| 1003 | Feroza Shah | 503 |
| 1004 | Ketan Dhal | 502 |
(b)Write SQL commandsfor the following statements:
1. To display the namesof all the silver coloured cars.
2. Tp display names ofcar, make and capacity of cars in descending order of their sitting capacity.
3. To display the highestcharges at which a vehicle can be hired from CARDEN.
4. To display thecustomer name and the corresponding name of the cars hired by them.
(c)Give the output ofthe following SQL queries:
(i)SELECT COUNT(DlST!NCT Make) FROM CARDEN;
(ii)SELECT MAX(Charges), MIN (Charges) FROM CARDEN;
SELECT COUNTO, Make FROM CARDEN;
(iv) SELECT CarName FROM CARDEN WHERE Capacity=4;
Answer - 11 : -
(a) Primary Key of CARDEN = Ceode of CARDEN
Alternate Key = CarName:
Primary key of Customer = Ceode
Alternate Key of Customer = Cname
(b) (i) SELECT CarName From CARDEN
WHERE Color = “SILVER”;
(ii)SELECT CarName, Make, Capacity
From CARDEN ORDER BY Capacity DESC;
(iii)SELECT MAX(Charges) From CARDEN;
(iv)SELECT Cname, CarName From
CUSTOMER, CARDEN WHERE CARDEN. Ccode = CUSTOMER. Ccode;
(c) (i) 4
(ii) MAX(Charges) MIN(Charges)
35 12
(iii)5
(iv)SX4
C Class