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

Last updated at April 16, 2024 by

This question asked in CBSE Comptt, 2010

Consider the following tables RESORT and OWNEDBY and answer the questions (a) and (b) parts of this question:

 

Untitled.png

Table: RESORT

RCODE PLACE RENT TYPE STARTDATE
R001 GOA 15000 5 STAR 12-Jan-02
R002 HIMACHAL 9000 4 STAR 20-Dec-07
R003 KERALA 12500 5 STAR 10-Mar-06
R004 HIMACHAL 10500 2 STAR 25-Nov-05
R005 GUJARAT 8000 4 STAR 01-Jan-03
R006 GOA 18000 7 STAR 30-Mar-08
R007 ORISSA 7500 2 STAR 12-Apr-99
R008 KERALA 11000 5 STAR 03-Mar-03
R009 HIMACHAL 9000 2 STAR 15-Oct-08
R010 GOA 13000 5 STAR 12-Apr-06

Question 9 Long Answer 5 - image 1.jpg

Table: OWNERBY

Place Owner
GOA RAJ RESORTS
KERALA KTDC
HIMACHAL HTDC
GUJARAT MAHINDRA RESORTS
ORISSA OTDC

Question 9 (a)

Write SQL commands for the following statements:

(i) To display the RCODE and PLACE of all ‘5 STAR’ resorts in the alphabetical order of the place from table RESORT.

Answer:

To display the RCODE and PLACE of all ‘5 STAR’ resorts in the alphabetical order of the place from  table RESORT,

  • The WHERE clause followed by condition ie., TYPE=’5 STAR’ should be used to select 5 star resorts.
  • The ORDER BY clause followed by the column name ie., PLACE should be used to arrange the records in alphabetical order of place.

QUERY: SELECT RCODE,PLACE FROM RESORT WHERE TYPE=’5 STAR’ ORDER BY PLACE;

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

(ii) To display the maximum and minimum rent for each type of resort from table RESORT.

Answer:

To display the maximum and minimum rent for each type of resort from table RESORT,

  • The MAX( ) and MIN( ) should be used to find maximum and minimum values in a column.
  • The GROUP BY clause followed by column name ie., TYPE should be used to group resorts of the same type.

QUERY: SELECT MAX(RENT),MIN(RENT),TYPE FROM RESORT GROUP BY TYPE;  

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

(iii)To display the details of all resorts which were started after 31-DEC-05 from table RESORT.

Answer:

To display the details of all resorts which were started after 31-DEC-05 from table RESORT, the WHERE clause followed by condition ie., STARTDATE>’2005-12-31’ s hould be used in the query.

QUERY: SELECT * FROM RESORT WHERE STARTDATE>’2005-12-31’;

Question 9 (iii) Long Answer 5 - image 1.jpg

Question 9 (b)

Give output for the following SQL queries:

(i) SELECT MIN(RENT) FROM RESORT WHERE PLACE = ‘KERALA’;

Answer:

The query will display the minimum rent of resorts in Kerala.

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

(ii) SELECT TYPE, STARTDATE FROM RESORT WHERE TYPE = ‘2 STAR’ ORDERBY STARTDATE;

Answer:

The query will display the type and startdate of 2 star resorts in ascending order of startdate.

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

Transcript

This question asked in CBSE Comptt, 2010 Consider the following tables RESORT and OWNEDBY and answer the questions (a) and (b) parts of this question: Table: RESORT RCODE PLACE RENT TYPE STARTDATE R001 GOA 15000 5 STAR 12-Jan-02 R002 HIMACHAL 9000 4 STAR 20-Dec-07 R003 KERALA 12500 5 STAR 10-Mar-06 R004 HIMACHAL 10500 2 STAR 25-Nov-05 R005 GUJARAT 8000 4 STAR 01-Jan-03 R006 GOA 18000 7 STAR 30-Mar-08 R007 ORISSA 7500 2 STAR 12-Apr-99 R008 KERALA 11000 5 STAR 03-Mar-03 R009 HIMACHAL 9000 2 STAR 15-Oct-08 R010 GOA 13000 5 STAR 12-Apr-06 Table: OWNERBY Place Owner GOA RAJ RESORTS KERALA KTDC HIMACHAL HTDC GUJARAT MAHINDRA RESORTS ORISSA OTDC Question 9 (a) Write SQL commands for the following statements: (i) To display the RCODE and PLACE of all ‘5 STAR’ resorts in the alphabetical order of the place from table RESORT. Answer: To display the RCODE and PLACE of all ‘5 STAR’ resorts in the alphabetical order of the place from table RESORT, The WHERE clause followed by condition ie., TYPE=’5 STAR’ should be used to select 5 star resorts. The ORDER BY clause followed by the column name ie., PLACE should be used to arrange the records in alphabetical order of place. QUERY: SELECT RCODE,PLACE FROM RESORT WHERE TYPE=’5 STAR’ ORDER BY PLACE; (ii) To display the maximum and minimum rent for each type of resort from table RESORT. Answer: To display the maximum and minimum rent for each type of resort from table RESORT, The MAX( ) and MIN( ) should be used to find maximum and minimum values in a column. The GROUP BY clause followed by column name ie., TYPE should be used to group resorts of the same type. QUERY: SELECT MAX(RENT),MIN(RENT),TYPE FROM RESORT GROUP BY TYPE; (iii)To display the details of all resorts which were started after 31-DEC-05 from table RESORT. Answer: To display the details of all resorts which were started after 31-DEC-05 from table RESORT, the WHERE clause followed by condition ie., STARTDATE>’2005-12-31’ should be used in the query. QUERY: SELECT * FROM RESORT WHERE STARTDATE>’2005-12-31’; Question 9 (b) Give output for the following SQL queries: (i) SELECT MIN(RENT) FROM RESORT WHERE PLACE = ‘KERALA’; Answer: The query will display the minimum rent of resorts in Kerala. (ii) SELECT TYPE, STARTDATE FROM RESORT WHERE TYPE = ‘2 STAR’ ORDERBY STARTDATE; Answer: The query will display the type and startdate of 2 star resorts in ascending order of startdate.

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.