Question

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'

Answer 1

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