Past Year - 5 Mark Questions

Computer Science - Class 12
Chapter 9 - Structured Query Language

## 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

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

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

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

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

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

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;

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

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

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

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

Learn in your speed, with individual attention - Teachoo Maths 1-on-1 Class

### 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