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

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.