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 - Jan- - 1978 | D001 | Manager | 75000 |
| 123 | Nitin | 10-Oct-77 | D002 | AO | 59000 |
| 129 | Navjot | 12-Jul-71 | D003 | Supervisor | 40000 |
| 130 | Jimmy | 30-Dec-80 | D004 | Sales Rep | |
| 131 | Faiz | 06-Apr-84 | D001 | Dep Manager | 65000 |
Table: DEPARTMENT
|
DEPTID |
DEPTNAME | FLOORNO |
| D001 | Personal | 4 |
| D002 | Admin | 10 |
| D003 | Production | 1 |
| D004 | Sales | 3 |
(a) To display the average salary of all employees, department wise.
Answer
(a) SELECT AVG(SALARY)
FROM EMPLOYEE
GROUP BY DEPTID;
(b) To display name and respective department name of each employee whose salary is more than 50000.
Answer
SELECT NAME, DEPTNAME
FROM EMPLOYEE, DEPARTMENT
WHERE
EMPLOYEE.DEPTID=
DEPARTMENT.DEPTID
AND SALARY>50000;
(c) To display the names of employees whose salary is not known, in alphabetical order.
Answer
SELECT NAME FROM EMPLOYEE
WHERE SALARY IS NULL
ORDER BY NAME;
(d) To display DEPTID from the table EMPLOYEE without repetition.
Answer
SELECT DISTINCT DEPTID
FROM EMPLOYEE;
