Question

Consider the following tables CARDEN and CUSTOMER and answer (b) and (c) parts of this question:

1. Give a suitable example of a table with sample data and illustrate Primary and Alternate Keys in it.

2. Write SQL commands for the following statements:

o To display the names of all the silver coloured cars.

o To display names of car, make and capacity of cars in descending order of their sitting capacity.

o To display the highest charges at which a vehicle can be hired from CARDEN.

o To display the customer name and the corresponding name of the cars hired by them.

3. Give the output of the following SQL queries:

o SELECT COUNT(DISTINCT Make) FROM CARDEN;

o SELECT MAX(Charges), MIN (Charges) FROM CARDEN;

o SELECT COUNTS), Make FROM CARDEN;

Answer 1

1. Primary Key of CARDEN = C code CARDEN 

Alternate Key = CarName: 

Primary key of Customer = Code 

Alternate Key of Customer = Cname 2

2. SELECT CarName From CARDEN

(i)  WHERE Color = “SILVER”;

(ii) SELECT CarName, Make, Capacity From 

 CARDEN ORDER BY Capacity DESC;

(iii) SELECT MAX(Charges) Frm CARDEN;

(iv) ELECT Cname, CarName From CARDEN, CUSTOMER WHERE CARDEN. Ccode = CUSTOMER. Ccode;

3. (i) 4 

  (ii) MAX(Charges) MIN (Charges) 35 112 

  (iii) 5 

  (iv) SX4 C Class