Page 298 - PYTHON-12

17. Write SQL commands for (i) to (vi) and write output for (vii) on the basis of PRODUCTS relation given
below:
PRODUCTS TABLE
PCODE PNAME COMPANY PRICE STOCK MANUFACTURE WARRANTY
P001 TV BPL 10000 200 12-JAN-2018 3
P002 TV SONY 12000 150 23-MAR-2017 4
P003 PC LENOVO 39000 100 09-APR-2018 2
P004 PC COMPAQ 38000 120 20-JUN-2019 2
P005 HANDYCAM SONY 18000 250 23-MAR-2017 3
(i) To show details of all PCs with stock more than 110.
(ii) To list the company which gives warranty of more than 2 years.
(iii) To find stock value of the BPL company where stock value is the sum of the products of price and
stock.
(iv) To show number of products from each company.
(v) To count the number of PRODUCTS which shall be out of warranty on 20-NOV-2020.
(vi) To show the PRODUCT name of the products which are within warranty as on date.
(vii) Give the output of the following statements:
(a) Select COUNT(distinct company) from PRODUCT;
(b) Select MAX(price)from PRODUCT where WARRANTY<=3;
18. What are DDL and DML?
19. Differentiate between primary key and candidate key in a relation.
20. What do you understand by the terms Cardinality and Degree of a relation in relational database?
21. Differentiate between DDL and DML. Mention the two commands for each category.
22. Consider the given table and answer the questions.
Table: SchoolBus
Rtno Area_Covered Capacity Noofstudents Distance Transporter Charges
1 Vasant Kunj 100 120 10 Shivam travels 100000
2 Hauz Khas 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 travels 55000
6 Krishna Nagar 70 80 30 Yadav travels 80000
7 Vasundhara 100 110 20 Yadav travels 100000
8 Paschim Vihar 40 40 20 Speed travels 55000
9 Saket 120 120 10 Speed travels 100000
10 Janakpuri 100 100 20 Kisan Tours 95000
(i) To show all information of students where capacity is more than the no. of students in order of rtno.
(ii) To show area_covered for buses covering more than 20 km., but charges less than 80000.
Computer Science with Python–XII 12.68 (v) Add a new record with the following data:
(iii) To show transporter-wise total no. of students travelling.
(iv) To show rtno, area_covered and average cost per student for all routes where average cost per student
is—charges/noofstudents.

(11, “Motibagh”,35,32,10, “kisan tours”, 35000)

(vi) Give the output considering the original relation as given:
(a) select sum(distance) from schoolbus where transporter= “Yadav travels”;

(b) select min(noofstudents) from schoolbus;

(c) select avg(charges) from schoolbus where transporter= “Anand travels”;

(d) select distinct transporter from schoolbus;