Question 3 - Past Year - 5 Mark Questions - Chapter 9 - Structured Query Language

Last updated at April 16, 2024 by

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

Question 3 Long Answer 5 - image.jpg

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

 

Question 3 Long Answer 5 - image 2.jpg

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’;

Question 3(i)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%';

Question 3(ii)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';

Question 3(iii)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;

Question 1 (iii) 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.

Question 3(iv)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’.

Question 3(v)Long Answer 5 - image1.jpg

Transcript

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’; 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%'; 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'; 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; 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. 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’.

Ask a doubt
Davneet Singh's photo - Co-founder, Teachoo

Made by

Davneet Singh

Davneet Singh has done his B.Tech from Indian Institute of Technology, Kanpur. He has been teaching from the past 14 years. He provides courses for Maths, Science, Social Science, Physics, Chemistry, Computer Science at Teachoo.