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

Transcript

This question asked in CBSE Outside Delhi, 2011 Consider the following tables EMPLOYEE and SALGRADE and answer (a) and (b) parts of this question: Table: EMPLOYEE ECODE NAME DESIGN SGRADE DOJ DOB 101 Abdul Ahmad EXECUTIVE S03 23-Mar-2003 13-Jan-1980 102 Ravi Chander HEAD-IT S02 12-Feb-2010 22-Jul-1987 103 John Ken RECEPTIONIST S03 24-Jun-2009 24-Feb-1983 105 Nazar Ameer GM S02 11-Aug-2006 03-Mar-1983 108 Priyam Sen CEO S01 29-Dec-2004 19-Jan-1982 Table: SALGRADE SGRADE SALARY HRA S01 56000 18000 S02 32000 12000 S03 24000 8000 Question 8 (a) Write SQL commands for the following statements: (i) To display the details of all EMPLOYEES in descending order of DOJ. Answer: To display the details of all EMPLOYEES in descending order of DOJ, the ORDER BY clause followed by column name ie., DOJ followed by the keyword DESC should be used in the query. QUERY: SELECT * FROM EMPLOYEE ORDER BY DOJ DESC; (ii) To display NAME and DESIGN of those EMPLOYEES, whose SGRADE is either S02 or S03. Answer: To display NAME and DESIGN of those EMPLOYEES, whose SGRADE is either S02 or S03, the WHERE clause followed by the condition ie., SGRADE=’S02’ OR SGRADE=’S03’ should be used in the query. QUERY: SELECT NAME,DESIGN FROM EMPLOYEE WHERE SGRADE=’S02’ OR SGRADE=’S03’; (iii)To display the content of all the EMPLOYEE table, whose DOJ is in between ‘09-Feb-2006’ and ‘08- Aug-2009’. Answer: To display the content of EMPLOYEE table, whose DOJ is in between ‘09-Feb-2006’ and ‘08- Aug-2009’, the BETWEEN operator followed by the range ie., ‘2006-02-09’ AND ‘2009-08-08’ should be used in the query. QUERY: SELECT * FROM EMPLOYEE WHERE DOJ BETWEEN ‘2006-02-09’ AND ‘2009-08-08’; Question 8 (b) Give the output of the following SQL queries: (i) SELECT COUNT(SGRADE), SGRADE FROM EMPLOYEE GROUP BY SGRADE; Answer: The query will display the number of Employees in each grade. (ii) SELECT MIN(DOB), MAX(DOJ) FROM EMPLOYEE; Answer: The query will display the smallest value in the column DOB and the largest value in the column DOJ.