Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 91 of 91

JOINS and SET Operations — Question 27

Back to all questions
27
Question

Question 26(b)

With reference to the below given tables, write commands in SQL for (i) to (iii) :

Table : TRANSPORTER

ORDERIDITEMTRANSPORTDATEDESTINATIONDRIVERID
1120TELEVISION2019-03-19MUMBAID103
1121REFRIGERATOR2019-04-12UDAIPURD101
1122TELEVISION2019-05-06MUMBAID101
1123MICROWAVE2019-05-07KANPURD103
1124FURNITURE2019-06-15KANPURD102

Table : DRIVER

DRIVERIDDRIVERNAMEDRIVERGRADEPHONE
D101Radhey ShyamA981234567
D102Jagat Singh981017897
D103Timsy YadavB
D104Zoravar SinghA981107887

(i) To display OrderId, Item being transported, DriverId and names of Drivers for all the orders that are being transported by drivers with 'A' grade.

(ii) To display DriverId, Names of drivers and Travel dates for drivers who are travelling after 1st March, 2019.

Or

How many rows will be there in Cartesian product of the two tables in consideration here ?

(iii) To display Orderld, Driverld and names of Drivers for all the orders that are transporting TELEVISION.

Answer

(i)

SELECT T.ORDERID, T.ITEM, D.DRIVERID, D.DRIVERNAME 
FROM TRANSPORTER T, DRIVER D 
WHERE T.DRIVERID = D.DRIVERID AND D.DRIVERGRADE = 'A';
Output
+---------+--------------+----------+--------------+
| ORDERID | ITEM         | DRIVERID | DRIVERNAME   |
+---------+--------------+----------+--------------+
|    1121 | REFRIGERATOR | D101     | RADHEY SHYAM |
|    1122 | TELEVISION   | D101     | RADHEY SHYAM |
+---------+--------------+----------+--------------+

(ii)

SELECT D.DRIVERID, D.DRIVERNAME, T.TRANSPORTDATE 
FROM TRANSPORTER T, DRIVER D 
WHERE T.DRIVERID = D.DRIVERID AND T.TRANSPORTDATE > '2019-03-01';
Output
+----------+--------------+---------------+
| DRIVERID | DRIVERNAME   | TRANSPORTDATE |
+----------+--------------+---------------+
| D103     | TIMSY YADAV  | 2019-03-19    |
| D101     | RADHEY SHYAM | 2019-04-12    |
| D101     | RADHEY SHYAM | 2019-05-06    |
| D103     | TIMSY YADAV  | 2019-05-07    |
| D102     | JAGAT SINGH  | 2019-06-15    |
+----------+--------------+---------------+

Or

The number of rows in the Cartesian product of the two tables "TRANSPORTER" and "DRIVER" would be the product of the number of rows in each table. In this case, since the "TRANSPORTER" table has 5 rows and the "DRIVER" table has 4 rows, the Cartesian product would have 5 * 4 = 20 rows.

(iii)

SELECT T.ORDERED, D.DRIVERID, D.DRIVERNAME 
FROM TRANSPORTER T, DRIVER D 
WHERE T.DRIVERID = D.DRIVERID AND T.ITEM = 'TELEVISION';
Output
+---------+----------+--------------+
| ORDERED | DRIVERID | DRIVERNAME   |
+---------+----------+--------------+
|    1120 | D103     | TIMSY YADAV  |
|    1122 | D101     | RADHEY SHYAM |
+---------+----------+--------------+