This question asked in CBSE Comptt, 2011

Consider the following tables EMPLOYEE and DEPARTMENT and answer (a) and (b) parts.

Question 7  Long Answer 5 - image 1.jpg

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

 

Question 7  Long Answer 5 - image 2.jpg

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;

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

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

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.  

Question 7 (iii) Long Answer 5 - image.jpg

(ii) SELECT MAX(JoinDate), MIN (JointDate) FROM EMPLOYEE;

Answer:

The query will display the l argest and smallest value in the column JoinDate of table EMPLOYEE.

Question 7 (iv) Long Answer 5 - image.jpg

Learn in your speed, with individual attention - Teachoo Maths 1-on-1 Class


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.

Ask a doubt
Davneet Singh's photo - Co-founder, Teachoo

Made by

Davneet Singh

Davneet Singh has done his B.Tech from Indian Institute of Technology, Kanpur. He has been teaching from the past 13 years. He provides courses for Maths, Science, Social Science, Physics, Chemistry, Computer Science at Teachoo.