Informatics Practices

Consider the following tables:
Table 1:
EMPLOYEE which stores Employee ID (EMP_ID), Employee Name (EMP_NAME), Employee City (EMP_CITY)
Table 2:
PAYROLL which stores Employee ID (EMP_ID), Department (DEPARTMENT), Designation (DESIGNATION), and Salary (SALARY) for various employees.
Note: Attribute names are written within brackets.
Table: EMPLOYEE
EMP_ID EMP_NAME EMP_CITY
1 ABHINAV AGRA
2 KABIR FARIDABAD
3 ESHA NOIDA
4 PAUL SEOUL
5 VICTORIA LONDON
Table: PAYROLL
EMP_ID DEPARTMENT DESIGNATION SALARY
1 SALES MANAGER 75000
2 SALES ASSOCIATE 50000
3 ENGINEERING MANAGER 95000
4 ENGINEERING ENGINEER 70000
5 MARKETING MANAGER 65000
Write appropriate SQL queries for the following:
I. Display department-wise average Salary.
II. List all designations in the decreasing order of Salary.
III. Display employee name along with their corresponding departments.

EMP_ID	DEPARTMENT	DESIGNATION	SALARY
1	SALES	MANAGER	75000
2	SALES	ASSOCIATE	50000
3	ENGINEERING	MANAGER	95000
4	ENGINEERING	ENGINEER	70000
5	MARKETING	MANAGER	65000

1 Like

SELECT DEPARTMENT, AVG(SALARY) 
FROM PAYROLL 
GROUP BY DEPARTMENT;

II.

SELECT DESIGNATION 
FROM PAYROLL 
ORDER BY SALARY DESC;

III.

SELECT EMP_NAME, DEPARTMENT 
FROM EMPLOYEE E, PAYROLL P 
WHERE E.EMP_ID = P.EMP_ID;

Answered By

1 Like