Check sibling questions

This question asked in CBSE Outside Delhi, 2011

Consider the following tables EMPLOYEE and SALGRADE and answer (a) and (b) parts of this question:

Question 8 Long Answer 5 - image 1.jpg

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

Question 8 Long Answer 5 - image 2.jpg

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;

Question 8 (i) Long Answer 5 - image 2.jpg

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

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

(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 (iii) Long Answer 5 - image 1.jpg

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.

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

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

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


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.

Davneet Singh's photo - Teacher, Engineer, Marketer

Made by

Davneet Singh

Davneet Singh is a graduate from Indian Institute of Technology, Kanpur. He has been teaching from the past 12 years. He provides courses for Maths and Science at Teachoo.