Past Year - 5 Mark Questions

Computer Science - Class 12
Chapter 9 - Structured Query Language

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

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

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.

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.

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

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

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

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

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

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

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