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

Transcript

This question asked in CBSE Comptt, 2011 Consider the following tables EMPLOYEE and DEPARTMENT and answer (a) and (b) parts. Table: EMPLOYEE ECode EName DepCde Salary Age JoinDate 15 Sameer Sharma 123 75000 39 01-Apr-2007 21 Raghuvindra K 101 86000 29 11-Nov-2005 34 Rama Gupta 119 52500 43 03-Mar-2011 46 C R Menon 103 67000 38 12-Jul-2004 77 Mohan Kumar 103 63000 55 25-Nov-2000 81 Rajesh Kumar 119 74500 48 11-Dec-2018 89 Sanjeev P 101 92600 54 12-Jan-2009 93 Pragya jain 123 32000 29 05-Aug-2006 Table: DEPARTMENT DepCde DepName DepHead 101 ACCOUNTS Rajiv Kumar 103 HR P K Singh 119 IT Yogesh Kumar 123 RESEARCH Ajay Dutta Question 7 (a) Write SQL commands for the following statements: (i) To display all DepName along with the DepCde in descending order of DepCde. Answer: To display all DepName along with the DepCde in descending order of DepCde, the ORDER BY clause followed by column name ie., DepCde followed by the keyword DESC should be used in the query. QUERY: SELECT DepName,DepCde FROM DEPARTMENT ORDER BY DepCde DESC; (ii) To display the average age of Employees in DepCde as 103. Answer: To display the average age of Employees in DepCde as 103: The AVG(Age ) function is used to display the average age. The WHERE clause followed by the condition ie., DepCde=103 is used to select only those employees with DepCode as 103. QUERY: SELECT AVG(Age) FROM EMPLOYEE WHERE DepCde=103; Question 7 (b) Give the output of the following SQL queries: (i) SELECT COUNT (DISTINCT DepCde) FROM EMPLOYEE; Answer: The query will display the number of distinct values in the column DepCde. (ii) SELECT MAX(JoinDate), MIN (JointDate) FROM EMPLOYEE; Answer: The query will display the largest and smallest value in the column JoinDate of table EMPLOYEE.