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

Last updated at April 16, 2024 by

This question asked in CBSE Outside Delhi - 2016

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

Question 12 Long Answer 5 - image 1.jpg

Table: VEHICAL

VCODE VEHICAL TYPE PERKM
V01 VOLVO BUS 150
V02 AC DELUX BUS 125
V03 ORDINARY BUS 80
V05 SUV 30
V04 CAR 18

Question 12 Long Answer 5 - image 2.jpg

Table: TRAVEL

CNO CNAME TRAVELDATE KM VCODE NOP
101 K. Niwal 2015-12-13 200 V01 32
103 Fredrick Sym 2016-03-21 120 V03 45
105 Hitesh Jain 2016-04-23 450 V02 42
102 Ravi Anish 2016-01-13 80 V02 40
107 John Malina 2015-02-10 65 V04 2
104 Sahanubhuti 2016-01-28 90 V05 4
106 Ramesh Jaya 2016-04-06 100 V01 25

Note:

  • PERKM is Freight Charges per kilometer. l
  • Km is kilometers travelled
  • NOP is number of passengers travelled in vehicle.

Question 12 (i)

To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of CNO.

Answer:

To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of CNO, the ORDER BY clause followed by the column name ie., CNO followed by the keyword DESC should be used in the query.

QUERY: SELECT CNO,CNAME,TRAVELDATE FROM TRAVEL ORDER BY CNO DESC;

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

Question 12 (ii)

To display the CNAME of all customers from the table TRAVEL who are travelling by vehicle with code V01 or V02.

Answer:

To display the CNAME of all customers from the table TRAVEL who are travelling by vehicle with code  V01 or V02, the WHERE clause followed by the condition ie., VCODE=’V01’ OR VCODE=’V02 ’ should be used in the query. 

QUERY: SELECT CNAME FROM TRAVEL WHERE VCODE=’V01’ OR VCODE=’V02’;

Question 12 (ii) Long Answer 5 - image 1.jpg

OR

To display the CNAME of all customers from the table TRAVEL who are travelling by vehicle with code  V01 or V02, the WHERE clause followed by the condition ie., VCODE IN (‘V01’,’V02’) should be used in the query. The IN operator is a shorthand for multiple OR conditions.

QUERY: SELECT CNAME FROM TRAVEL WHERE VCODE IN (‘V01’,’V02’);

Question 12 (ii) Long Answer 5 - image 2.jpg

Question 12 (iii)

To display the CNO and CNAME of those customers from the table TRAVEL who travelled between '2015-12-31' and '2015-05-01'.

Answer: 

To display the CNO and CNAME of those customers from the table TRAVEL who travelled between  '2015-12-31' and '2015-05-01',  the comparison keyword BETWEEN followed by the range ie., ’2015-12-31’ AND ‘2015-05-01’ should be used in the query.  

QUERY: SELECT CNO,CNAME FROM TRAVEL WHERE TRAVELDATE BETWEEN ’2015-12-31’ AND ‘2015-05-01’;

Question 12 (iii) Long Answer 5 - image.jpg

Question 12 (iv)

SELECT COUNT (*), VCODE FROM TRAVEL GROUP BY VCODE HAVING COUNT (*) > 1;

Answer: 

The query will display the number of passengers and the VCODE of vehicles with passenger count greater than 1.

Question 12 (iv) Long Answer 5 - image.jpg

Question 12 (v)

SELECT DISTINCT VCODE FROM TRAVEL:

Answer:

The query will display the distinct values in the column VCODE of table TRAVEL.

Question 12 (v) Long Answer 5 - image.jpg

Transcript

This question asked in CBSE Outside Delhi - 2016 Write queries for (i) to (iii) and find outputs for SQL queries (iv) to (v), which are based on the tables. Table: VEHICAL VCODE VEHICAL TYPE PERKM V01 VOLVO BUS 150 V02 AC DELUX BUS 125 V03 ORDINARY BUS 80 V05 SUV 30 V04 CAR 18 Table: TRAVEL CNO CNAME TRAVELDATE KM VCODE NOP 101 K. Niwal 13-12-2015 200 V01 32 103 Fredrick Sym 21-03-2016 120 V03 45 105 Hitesh Jain 23-04-2016 450 V02 42 102 Ravi Anish 13-01-2016 80 V02 40 107 John Malina 10-02-2015 65 V04 2 104 Sahanubhuti 28-01-2016 90 V05 4 106 Ramesh Jaya 06-04-2016 100 V01 25 Note: PERKM is Freight Charges per kilometer. l Km is kilometers travelled NOP is number of passengers travelled in vehicle. Question 12 (i) To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of CNO. Answer: To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of CNO, the ORDER BY clause followed by the column name ie.,CNO followed by the keyword DESC should be used in the query. QUERY: SELECT CNO,CNAME,TRAVELDATE FROM TRAVEL ORDER BY CNO DESC; Question 12 (ii) To display the CNAME of all customers from the table TRAVEL who are travelling by vehicle with code V01 or V02. Answer: To display the CNAME of all customers from the table TRAVEL who are travelling by vehicle with code V01 or V02, the WHERE clause followed by the condition ie., VCODE=’V01’ OR VCODE=’V02’ should be used in the query. QUERY: SELECT CNAME FROM TRAVEL WHERE VCODE=’V01’ OR VCODE=’V02’; OR To display the CNAME of all customers from the table TRAVEL who are travelling by vehicle with code V01 or V02, the WHERE clause followed by the condition ie., VCODE IN (‘V01’,’V02’) should be used in the query. The IN operator is a shorthand for multiple OR conditions. QUERY: SELECT CNAME FROM TRAVEL WHERE VCODE IN (‘V01’,’V02’); Question 12 (iii) To display the CNO and CNAME of those customers from the table TRAVEL who travelled between '2015-12-31' and '2015-05-01'. Answer: To display the CNO and CNAME of those customers from the table TRAVEL who travelled between '2015-12-31' and '2015-05-01', the comparison keyword BETWEEN followed by the range ie.,’2015-12-31’ AND ‘2015-05-01’ should be used in the query. QUERY: SELECT CNO,CNAME FROM TRAVEL WHERE TRAVELDATE BETWEEN ’2015-12-31’ AND ‘2015-05-01’; Question 12 (iv) SELECT COUNT (*), VCODE FROM TRAVEL GROUP BY VCODE HAVING COUNT (*) > 1; Answer: The query will display the number of passengers and the VCODE of vehicles with passenger count greater than 1. Question 12 (v) SELECT DISTINCT VCODE FROM TRAVEL: Answer: The query will display the distinct values in the column VCODE of table TRAVEL.

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.