The Question : Write queries
A) to
D) based on the tables EMPLOYEE and DEPARTMENT given below: Table: EMPLOYEE
| EMPID | NAME | DOB | DEPTID | DESIG | SALARY |
| 120 | Alisha | 23- Jan1978 | D001 | Manager | 75000 |
| 123 | Nitin | 10- Oct1977 | D002 | AO | 59000 |
| 129 | Navjot | 12- Jul1971 | D003 | Supervisor | 40000 |
| 130 | Jimmy | 30- Dec1980 | D004 | Sales Rep | |
| 131 | Faiz | 06- Apr1984 | D005 | Dep Manager | 65000 |
Table: DEPARTMENT
| DEPTID | DEPTNAME | FLOORNO |
| D001 | Personal | 4 |
| D001 | Admin | 10 |
| D001 | Production | 1 |
| D001 | Sales | 3 |
A) To display the average salary of all employees, department wise.
B) To display name and respective department name of each employee whose salary is more than 50000.
C) To display the names of employees whose salary is not known, in alphabetical order
D) To display DEPTID from the table EMPLOYEE without repetition.
Solution for the question :
(a) SELECT AVG(SALARY) [5] FROM EMPLOYEE GROUP BY DEPTID;(b) SELECT NAME, DEPTNAME FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.DEPTID= DEPARTMENT.DEPTID AND SALARY>50000; (c) SELECT NAME FROM EMPLOYEE WHERE SALARY IS NULL ORDER BY NAME; (d) SELECT DISTINCT DEPTID FROM EMPLOYEE;
The correct answer to the question is researched by our moderators and shared with you. You can give feedback by commenting for the answers you think are wrong.