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.