Question

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:

1. Display department-wise average Salary.
2. List all designations in the decreasing order of Salary.
3. Display employee name along with their corresponding departments.

Answer 1

1. SELECT DEPARTMENT, AVG(SALARY) FROM PAYROLL GROUP BY DEPARTMENT;
2. SELECT DESIGNATION FROM PAYROLL ORDER BY SALARY DESC;
3. SELECT EMP_NAME, DEPARTMENT FROM EMPLOYEE E, PAYROLL P WHERE E.EMP_ID=P.EMP_ID;