Expanded SQL Assignment Solutions (Q3 & Q4)
Q3 (b)(i)
mysql> SELECT CarName FROM CARDEN WHERE Color='SILVER';
+---------+
| CarName |
+---------+
| Indigo |
| SX4 |
+---------+
Query OK.
Q3 (b)(ii)
mysql> SELECT CarName, Make, Capacity FROM CARDEN ORDER BY Capacity DESC;
+---------+----------+----------+
| CarName | Make | Capacity |
+---------+----------+----------+
| Innova | Toyota | 7 |
| SX4 | Suzuki | 4 |
| C Class | Mercedes | 4 |
| A-Star | Suzuki | 3 |
| Indigo | Tata | 3 |
+---------+----------+----------+
Query OK.
Q3 (b)(iii)
mysql> SELECT CarName, Color, Cname FROM CARDEN CA, CUSTOMER CU WHERE
[Link]=[Link];
+---------+--------+-------------+
| CarName | Color | Cname |
+---------+--------+-------------+
| A-Star | RED | Hemant Sahu |
| SX4 | SILVER | Raj Lal |
| Indigo | SILVER | Feroza Shah |
| Innova | WHITE | Ketan Dhal |
+---------+--------+-------------+
Query OK.
Q3 (b)(iv)
mysql> SELECT Cname, CarName FROM CUSTOMER CU, CARDEN CA WHERE
[Link]=[Link];
+-------------+---------+
| Cname | CarName |
+-------------+---------+
| Hemant Sahu | A-Star |
| Raj Lal | SX4 |
| Feroza Shah | Indigo |
| Ketan Dhal | Innova |
+-------------+---------+
Query OK.
Q3 (c)(i)
mysql> SELECT COUNT(DISTINCT Make) FROM CARDEN;
+----------------------+
| COUNT(DISTINCT Make) |
+----------------------+
| 4 |
+----------------------+
Query OK.
Q3 (c)(ii)
mysql> SELECT MAX(Charges), MIN(Charges) FROM CARDEN;
+--------------+--------------+
| MAX(Charges) | MIN(Charges) |
+--------------+--------------+
| 35 | 12 |
+--------------+--------------+
Query OK.
Q3 (c)(iii)
mysql> SELECT COUNT(*), Make FROM CARDEN GROUP BY Make;
+----------+----------+
| COUNT(*) | Make |
+----------+----------+
| 2 | Suzuki |
| 1 | Tata |
| 1 | Toyota |
| 1 | Mercedes |
+----------+----------+
Query OK.
Q3 (c)(iv)
mysql> SELECT CarName FROM CARDEN WHERE Capacity = 4;
+---------+
| CarName |
+---------+
| SX4 |
| C Class |
+---------+
Query OK.
Q4 (i)
mysql> SELECT CnorName FROM CONSIGNOR WHERE City='Mumbai';
+----------+
| CnorName |
+----------+
| R Kohli |
| S Kaur |
+----------+
Query OK.
Q4 (ii)
mysql> SELECT CneeID, CnorName, CnorAddress, CneeName, CneeAddress FROM
CONSIGNOR CO, CONSIGNEE CE WHERE [Link]=[Link];
+--------+------------+---------------+
| CneeID | CnorName | CneeName |
+--------+------------+---------------+
| MU05 | R Singhal | Rahul Kishore |
| ND08 | Amit Kumar | P Dhingra |
| KO19 | R Kohli | A P Roy |
| MU32 | Amit Kumar | S Mittal |
| ND48 | S Kaur | B P Jain |
+--------+------------+---------------+
Query OK.
Q4 (iii)
mysql> SELECT * FROM CONSIGNEE ORDER BY CneeName ASC;
+--------+--------+---------------+-----------+
| CneeID | CnorID | CneeName | CneeCity |
+--------+--------+---------------+-----------+
| KO19 | MU15 | A P Roy | Kolkata |
| ND48 | MU50 | B P Jain | New Delhi |
| ND08 | ND02 | P Dhingra | New Delhi |
| MU05 | ND01 | Rahul Kishore | Mumbai |
| MU32 | ND02 | S Mittal | Mumbai |
+--------+--------+---------------+-----------+
Query OK.
Q4 (iv)
mysql> SELECT City, COUNT(*) FROM CONSIGNOR GROUP BY City;
+-----------+----------+
| City | COUNT(*) |
+-----------+----------+
| New Delhi | 2 |
| Mumbai | 2 |
+-----------+----------+
Query OK.