Database Management – SQL — Computer Science STD 12 Science — Question

Consider the SchoolBus table given below:

Rtno	Area_covered	Capacity	Noofstudents	Distance	T ransporter	Charges
1	Vasantkunj	100	120	10	Shivamtravels	100000
2	HauzKhas	80	80	10	Anand travels	85000
3	Pitampura	60	55	30	Anand travels	60000
4	Rohini	100	90	35	Anand travels	100000
5	Yamuna Vihar	50	60	20	Bhalla Co.	58000
6	Krishna Nagar	70	80	30	Yadav Co.	80000
7	Vasundhara	100	110	20	Yadav Co.	100000
8	Paschim Vihar	40	40	20	Speed travels	55000

(a) State the command that will give the output as:
Area_covered
Yamuna Vihar
Krishna Nagar
Vasundhara
(i) select area_covered from schoolbus where transporter Yadav Co.'and transporter='Bhalla Co.';
(ii) select area_covered from schoolbus where not transporter-Yadav Co.'and transporter=Bhaila Co.';
(iii) select area_covered from schoolbus where transporter - Yadav Co.'
OR transporter'Bhalla Co.';
(iv) select area_covered from schoolbus where transporter IN("Yadav co.", -Bhalla co."):
Choose the correct option:
(i) Both (i) and (ii). (ii) Both (iii) and (iv).
(iii) Any of the options (i), (ii) and (iv) (iv) Only (iii)
(b) What will be the output of the following command?
Select * from schoolbus where distance=20 order by charges;

(c) Ravi has given the following command to obtain the Longest distance Select max(distance) from schoolbus where group by transporter;
But he is not getting the desired result.
Help him by writing the correct command.
(i) select max(distance) from schoolbus where group by transporter;
(ii) select transporter, max(distance) from schoolbus group by distance;
(iii) select transporter, max(distance) group by transporter from schoolbus;
(iv) select transporter, max(distance) from schoolbus group by transporter;
(d) Help Saumya to write the command to display the name of the transporter who is having lowest capacity in his schoolbus?
(i) select transporter.min(capacity) from schoolbus;
(ii) select transporter.max(capacity) from schoolbus:
(iii) select transporter.min(capacity) from schoolbus group by transporter:
(iv) select transporter.maximum(capacity)
(e) Choose the appropriate primary key.
(i) Transporter (ii) Area_covered (iii) Rtno (iv) Charges