Check sibling questions

Write SQL queries for (i) to (iii) and find outputs for SQL queries (iv) to (v), which are based on the tables.

Question 1Long Answer 5 - image 1.jpg

Table: CUSTOMER

CNO CNAME ADDRESS
101 Rich Jain Delhi
102 Surbhi Sinha Chennai
103 Lisa Thomas Bengalore
104 Imran Ali Delhi
105 Roshan Singh Chennai

 

Question 1Long Answer 5 - image 2.jpg

Table: TRANSACTION

TRNO CNO AMOUNT TYPE DOT
T001 101 1500 Credit 2017-11-23
T002 103 2000 Debit 2017-05-12
T003 102 3000 Credit 2017-06-10
T004 103 12000 Credit 2017-09-12
T005 101 1000 Debit 2017-09-05

Question 1 (i)

To display details of all transactions of TYPE Credit from table TRANSACTION.

Answer:

To display details of all transactions of TYPE  Credit from table TRANSACTION, the WHERE clause followed by the condition ie., TYPE=’Credit’ should be used in the query.

QUERY: SELECT * FROM TRANSACTION WHERE TYPE='Credit';

Question 1 (i) Long Answer 5 - image 2.jpg

Question 1 (ii)

To display the CNO and AMOUNT of all Transactions done in the month of September 2017 from table TRANSACTION.

Answer:

To display the CNO and AMOUNT of all Transactions done in the month of September 2017 from  table TRANSACTION, the LIKE operator followed by the specific pattern to check for a match ie., ‘2017-09%’ should be used in the query. The % symbol represents zero, one or multiple characters. 

QUERY: SELECT CNO,AMOUNT FROM TRANSACTION WHERE DOT LIKE '2017-09%';

Question 1 (i) Long Answer 5 - image 3.jpg

Question 1 (iii)

To display the last date of transaction (DOT) from the table TRANSACTION for the customer having CNO as 103.

Answer:

To display the last date of transaction (DOT) from  the table TRANSACTION for the customer having CNO as 103:

  • MAX() function is used to find the latest DOT.
  • WHERE clause followed by the condition ie.,CNO=103 is used to select customer having CNO as 103.

QUERY: SELECT MAX(DOT) FROM TRANSACTION WHERE CNO=103;

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

Question 1 (iv)

SELECT CNO, COUNT(*), MAX (AMOUNT) FROM TRANSACTION GROUP BY CNO HAVING COUNT (*)> 1;

Answer

The query will display the CNO, number of Transactions and highest amount of each customer if the number of transactions is more than 1.

Question 1 (iv) Long Answer 5 - image 3.jpg

Question 1 (v)

SELECT CNO, CNAME FROM CUSTOMER WHERE ADDRESS NOT IN (‘DELHI’, ‘BANGALORE’);

Answer:

The query will display the CNO and name of customers who do not live in Delhi or Bangalore

Question 1 (v) Long Answer 5 - image 3.jpg


Transcript

Write SQL queries for (i) to (iii) and find outputs for SQL queries (iv) to (v), which are based on the tables. CNO CNAME ADDRESS 101 Rich Jain Delhi 102 Surbhi Sinha Chennai 103 Lisa Thomas Bengalore 104 Imran Ali Delhi 105 Roshan Singh Chennai Question 1 (i) To display details of all transactions of TYPE Credit from table TRANSACTION. Answer: To display details of all transactions of TYPE Credit from table TRANSACTION, the WHERE clause followed by the condition ie., TYPE=’Credit’ should be used in the query. QUERY: SELECT * FROM TRANSACTION WHERE TYPE='Credit'; Question 1 (ii) To display the CNO and AMOUNT of all Transactions done in the month of September 2017 from table TRANSACTION. Answer: To display the CNO and AMOUNT of all Transactions done in the month of September 2017 from table TRANSACTION, the LIKE operator followed by the specific pattern to check for a match ie., ‘2017-09%’ should be used in the query. The % symbol represents zero, one or multiple characters. QUERY: SELECT CNO,AMOUNT FROM TRANSACTION WHERE DOT LIKE '2017-09%'; Question 1 (iii) To display the last date of transaction (DOT) from the table TRANSACTION for the customer having CNO as 103. Answer: To display the last date of transaction (DOT) from the table TRANSACTION for the customer having CNO as 103: MAX() function is used to find the latest DOT. WHERE clause followed by the condition ie.,CNO=103 is used to select customer having CNO as 103. QUERY: SELECT MAX(DOT) FROM TRANSACTION WHERE CNO=103; Question 1 (iv) SELECT CNO, COUNT(*), MAX (AMOUNT) FROM TRANSACTION GROUP BY CNO HAVING COUNT (*)> 1; Answer The query will display the CNO, number of Transactions and highest amount of each customer if the number of transactions is more than 1. Question 1 (v) SELECT CNO, CNAME FROM CUSTOMER WHERE ADDRESS NOT IN (‘DELHI’, ‘BANGALORE’); Answer: The query will display the CNO and name of customers who do not live in Delhi or Bangalore

Davneet Singh's photo - Teacher, Engineer, Marketer

Made by

Davneet Singh

Davneet Singh is a graduate from Indian Institute of Technology, Kanpur. He has been teaching from the past 12 years. He provides courses for Maths and Science at Teachoo.