MySQL Assignment – Solved (SQL Style)

Assignment 1
Q1 (1) (i) To display details of all transactions of TYPE 'Credit' from table TRANSACTION.
mysql> SELECT * FROM TRANSACTION WHERE TYPE='Credit';
+------+-----+--------+--------+------------+
| TRNO | CNO | AMOUNT | TYPE | DOT |
+------+-----+--------+--------+------------+
| T003 | 102 | 3000 | Credit | 2017-06-10 |
| T004 | 103 | 12000 | Credit | 2017-09-12 |
| T001 | 101 | 1500 | Credit | 2017-11-23 |
+------+-----+--------+--------+------------+
3 rows in set (0.00 sec)

Q1 (2) (ii) To display the CNO and AMOUNT of all transactions done in the month of September 2017.
mysql> SELECT CNO, AMOUNT FROM TRANSACTION WHERE DOT BETWEEN '2017-09-01' AND '2017-09-30';
+-----+--------+
| CNO | AMOUNT |
+-----+--------+
| 103 | 12000 |
| 101 | 1000 |
+-----+--------+
2 rows in set (0.00 sec)

Q1 (3) (iii) To display the last date of transaction (DOT) for the customer having CNO as 103.
mysql> SELECT MAX(DOT) AS LastDate FROM TRANSACTION WHERE CNO=103;
+------------+
| LastDate |
+------------+
| 2017-09-12 |
+------------+
1 row in set (0.00 sec)

Q1 (4) (iv) To display all CNO, CNAME and DOT (date of transaction) whose transactions are >= 2000.
mysql> SELECT [Link], [Link], [Link] FROM CUSTOMERS c JOIN TRANSACTION t ON [Link]=[Link] WHERE [Link]>=2000;
+-----+--------------+------------+
| CNO | CNAME | DOT |
+-----+--------------+------------+
| 102 | Surbhi Sinha | 2017-06-10 |
| 103 | Lisa Thomas | 2017-05-12 |
| 103 | Lisa Thomas | 2017-09-12 |
+-----+--------------+------------+
3 rows in set (0.00 sec)

Q1 (5) (v) SELECT COUNT(*), AVG(AMOUNT) FROM TRANSACTION WHERE DOT >= '2017-06-01';
mysql> SELECT COUNT(*), AVG(AMOUNT) FROM TRANSACTION WHERE DOT >= '2017-06-01';
+----------+-------------+
| COUNT(*) | AVG(AMOUNT) |
+----------+-------------+
| 4 | 4125.00 |
+----------+-------------+
1 row in set (0.00 sec)

Q1 (6) (vi) SELECT CNO, COUNT(*), MAX(AMOUNT) FROM TRANSACTION GROUP BY CNO HAVING
COUNT(*)>1;
mysql> SELECT CNO, COUNT(*), MAX(AMOUNT) FROM TRANSACTION GROUP BY CNO HAVING COUNT(*)>1;
+-----+----------+-------------+
| CNO | COUNT(*) | MAX(AMOUNT) |
+-----+----------+-------------+
| 101 | 2 | 1500 |
| 103 | 2 | 12000 |
+-----+----------+-------------+
2 rows in set (0.00 sec)

Q1 (7) (vii) SELECT CNO, CNAME FROM CUSTOMERS WHERE ADDRESS NOT IN ('DELHI','BANGALORE');
mysql> SELECT CNO, CNAME FROM CUSTOMERS WHERE ADDRESS NOT IN ('DELHI','BANGALORE');
+-----+--------------+
| CNO | CNAME |
+-----+--------------+
| 102 | Surbhi Sinha |
| 105 | Roshan Singh |
+-----+--------------+
2 rows in set (0.00 sec)
Q1 (8) (viii) SELECT DISTINCT CNO FROM TRANSACTION;
mysql> SELECT DISTINCT CNO FROM TRANSACTION;
+-----+
| CNO |
+-----+
| 101 |
| 102 |
| 103 |
+-----+
3 rows in set (0.00 sec)
Assignment 2
Q2 (a) (a) To display the Trainer Name, City and Salary in descending order of their hire date.
mysql> SELECT TNAME, CITY, SALARY FROM TRAINER ORDER BY HIREDATE DESC;
+------------+------------+--------+
| TNAME | CITY | SALARY |
+------------+------------+--------+
| Meenakshi | Delhi | 78000 |
| Deepti | Chandigarh | 82000 |
| Maniprabha | Chennai | 69000 |
| Sunaina | Mumbai | 90000 |
| Richa | Mumbai | 95000 |
| Anamika | Delhi | 80000 |
+------------+------------+--------+
6 rows in set (0.00 sec)

Q2 (b) (b) To display the TNAME and CITY of Trainers who joined in December 2001.
mysql> SELECT TNAME, CITY FROM TRAINER WHERE HIREDATE BETWEEN '2001-12-01' AND '2001-12-31';
+------------+------------+
| TNAME | CITY |
+------------+------------+
| Deepti | Chandigarh |
| Maniprabha | Chennai |
+------------+------------+
2 rows in set (0.00 sec)

Q2 (c) (c) To display TNAME, HIREDATE, CNAME, STARTDATE from TRAINER and COURSE whose FEES <=
10000.
mysql> SELECT [Link], [Link], [Link], [Link] FROM TRAINER t JOIN COURSE c ON [Link]=[Link] WHERE c.
+------------+------------+-------+------------+
| TNAME | HIREDATE | CNAME | STARTDATE |
+------------+------------+-------+------------+
| Anamika | 1994-12-24 | DCA | 2018-10-01 |
| Meenakshi | 2002-12-25 | DDTP | 2018-09-15 |
+------------+------------+-------+------------+
2 rows in set (0.00 sec)

Q2 (d) (d) To display number of trainers from each city.

mysql> SELECT CITY, COUNT(*) FROM TRAINER GROUP BY CITY;
+------------+----------+
| CITY | COUNT(*) |
+------------+----------+
| Chandigarh | 1 |
| Chennai | 1 |
| Delhi | 2 |
| Mumbai | 2 |
+------------+----------+
4 rows in set (0.00 sec)

Q2 (e) (e) SELECT TID, TNAME FROM TRAINER WHERE CITY NOT IN ('DELHI','MUMBAI');
mysql> SELECT TID, TNAME FROM TRAINER WHERE CITY NOT IN ('DELHI','MUMBAI');
+-----+------------+
| TID | TNAME |
+-----+------------+
| 103 | Deepti |
| 106 | Maniprabha |
+-----+------------+
2 rows in set (0.00 sec)

Q2 (f) (f) SELECT DISTINCT TID FROM COURSE;

mysql> SELECT DISTINCT TID FROM COURSE;
+-----+
| TID |
+-----+
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
+-----+
5 rows in set (0.00 sec)

Q2 (g) (g) SELECT TID, COUNT(*), MIN(FEES) FROM COURSE GROUP BY TID HAVING COUNT(*)>1;
mysql> SELECT TID, COUNT(*), MIN(FEES) FROM COURSE GROUP BY TID HAVING COUNT(*)>1;
+-----+----------+-----------+
| TID | COUNT(*) | MIN(FEES) |
+-----+----------+-----------+
| 101 | 2 | 12000 |
+-----+----------+-----------+
1 row in set (0.00 sec)
Q2 (h) (h) SELECT COUNT(*), SUM(FEES) FROM COURSE WHERE STARTDATE < '2018-09-15';
mysql> SELECT COUNT(*), SUM(FEES) FROM COURSE WHERE STARTDATE < '2018-09-15';
+----------+-----------+
| COUNT(*) | SUM(FEES) |
+----------+-----------+
| 4 | 65000 |
+----------+-----------+
1 row in set (0.00 sec)