Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii) which are based on the table.
TABLE: Account
| ANO | ANAME | ADDRESS |
| 101 | Nirja Singh | Bangalore |
| 102 | Rohan Gupta | Chennai |
| 103 | Ali Reza | Hyderabad |
| 104 | Rishabh Jain | Chennai |
| 105 | Simran Kaur | Chandigarh |
TABLE: TRANSACT
| TRNO | ANO | AMOUNT | TYPE | DOT |
| T001 | 101 | 2500 | Withdraw | 2017-12-21 |
| T002 | 103 | 3000 | Deposit | 2017-06-01 |
| T003 | 102 | 2000 | Withdraw | 2017-0S-12 |
| T004 | 103 | 1000 | Deposit | 2017-10-22 |
| T005 | 101 | 12000 | Deposit | 2017-11-06 |
1) To display details of all transaction of TYPE Depos1t from Table TRANSACT.
2) To display the ANO and AMOUNT of all Depos1ts and Withdrawals done in the month of October 2017 from table TRANSACT.
3) To display the last date of the transaction (DOT) from the table TRANSACT for the Accounts having ANO as 103
4) To display all ANO, ANAME and DOT of those persons from tables ACCOUNT and TRANSACT who have done transactions less than or equal to 3000?
5) SELECT ANO, ANAME FROM ACCOUNT WHERE ADDRESS NOT IN ('CHENNAI', 'BANGALORE');
6) SELECT DISTINCT ANO FROM TRANSACT
7) SELECT ANO, COUNT(*), MIN(AMOUNT) FROM TRANSACT GROUP BY AND HAVING COUNT(*) > 1;
8) SELECT COUNT(*), SUM(AMOUNT) FROM TRANSACT WHERE DOT <= '2017-06-01'
Solution
1) SELECT * FROM TRANSACT WHERE TYPE = ‘Deposit’
2) SELECT ANO, AMOUNT FROM TRANSACT WHERE MONTH(DOT) = 10;
3) SELECT DOT FROM TRANSACT WHERE ANO = 103 ORDER BY DOT DESC LIMIT = 1
4) Select A.ANAME, T.ANO, T.DOT FROM ACCOUNT as A
JOIN TRANSACT as T ON A.ANO = T.ANO WHERE T.AMOUNT < = 3000;
5)
| ANO | ANAME |
| 103 | Ali Reza |
| 105 | Simran Kaur |
6)
| ANO |
| 101 |
| 103 |
| 102 |
7)
| ANO | COUNT | MIN(AMOUNT) |
| 101 | 2 | 2500 |
| 103 | 2 | 1000 |
8)
| Count | Sum(AMOUNT) |
| 2 | 5000 |
