Question:
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables.
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-05-12 |
T004 | 103 | 1000 | Deposit | 2017-10-22 |
T005 | 101 | 12000 | Deposit | 2017-11-06 |
(i) To display details of all transactions of TYPE Deposit from Table TRANSACT.
(ii) To display the ANO and AMOUNT of all Deposits and Withdrawals done in the month of October 2017 from table TRANSACT.
(iii) To display the last date of transaction (DOT) from the table TRANSACT for the Accounts having ANO as 103.
(iv) 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.
(v) SELECT ANO, ANAME FROM ACCOUNT
WHERE ADDRESS NOT IN (‘CHENNAI’, ‘BANGALORE’);
(vi) SELECT DISTINCT ANO FROM TRANSACT;
(vii) SELECT ANO, COUNT(*), MIN(AMOUNT) FROM TRANSACT
GROUP BY ANO HAVING COUNT(*)> 1;
(viii) SELECT COUNT(*), SUM(AMOUNT) FROM TRANSACT
WHERE DOT <= ‘2017-06-01’;
CSKC| Created: 2-Jan-2019 | Updated: 4-Jan-2019|CBSE12A-2018