You are here
This question asked in CBSE 2018
Write SQL queries for (i) to (iii) and find outputs for SQL queries (iv) to (v), 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-11 |
| T002 | 103 | 3000 | Deposit | 2017-06-01 |
| T003 | 102 | 2000 | Withdraw | 2017-06-12 |
| T004 | 103 | 1000 | Deposit | 2017-10-22 |
| T005 | 101 | 12000 | Deposit | 2017-11-06 |
Question 3 (i)
To display details of all transactions of TYPE Deposit from Table TRANSACT
Answer:
To display details of all transactions of TYPE Deposit from Table TRANSACT, the WHERE clause followed by the condition ie., TYPE=’Deposit’ should be used in the query.
QUERY: SELECT * FROM TRANSACT WHERE TYPE=’DEPOSIT’;
long-answer-5---image.jpg)
Question 3 (ii)
To display the ANO and AMOUNT of all Deposits and Withdrawals done in the month of October 2017 from table TRANSACT
Answer:
To display the ANO and AMOUNT of all Deposits and Withdrawals done in the month of October 2017 from table TRANSACT, the LIKE operator followed by the specific pattern to check for a match ie., ‘2017-10%’ should be used in the query. The % symbol represents zero, one or multiple characters.
QUERY: SELECT ANO,AMOUNT FROM TRANSACT WHERE DOT LIKE '2017-10%';
long-answer-5---image.jpg)
OR
To display the ANO and AMOUNT of all Deposits and Withdrawals done in the month of October 2017 from table TRANSACT, the BETWEEN operator followed by the range ie., ‘2017-10-01’ AND ‘2017-10-31’ should be used in the query.
QUERY: SELECT ANO,AMOUNT FROM TRANSACT WHERE DOT BETWEEN '2017-10-01' AND '2017-10-31';
long-answer-5---image.jpg)
Question 3 (iii)
To display the last date of transaction (DOT) from the table TRANSACT for the Accounts having ANO as 103.
Answer:
To display the last date of transaction (DOT) from the table TRANSACT for the Accounts having ANO as 103:
- The MAX( ) function is used to find the last DOT.
- The WHERE clause followed by the condition ie., ANO=103 is used to select only those records which have AN0 as 103.
QUERY: SELECT MAX(DOT) FROM TRANSACT WHERE ANO=103;
-long-answer-5---image-3.jpg)
Question 3 (iv)
SELECT DISTINCT ANO FROM TRANSACT;
Answer:
The query will display the distinct values in column ANO ie., no duplicate values will be displayed.
long-answer-5---image1.jpg)
Question 3 (v)
SELECT COUNT (*), SUM (AMOUNT) FROM TRANSACT WHERE DOT <= ‘2017-06-12’;
Answer:
The query will display the number of transactions and total sum of values in column AMOUNT for transactions which took place on or before ‘2017-06-12’.
long-answer-5---image1.jpg)
