You are here
Write SQL queries for (i) to (iii) and find outputs for SQL queries (iv) to (v), which are based on the tables.
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 |
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';
-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%';
-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;
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.
-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
-long-answer-5---image-3.jpg)
