1. Use a join to display the employee identification number, employee name, job
ID: 3736942 • Letter: 1
Question
1. Use a join to display the employee identification number, employee name, job description and department name of all employees of the SALES department.
2. Use a subquery to display employee identification number, employee name, job description and department name of all employees of the SALES department.
3. Use a subquery to display the details of the employees who have been hired after the employee JONES was hired.
4. Use a subquery to display the employee name, job description, and salary of all employees who have the same job title as you (E.g. the employee you created with your name).
5. Display the average, maximum, and minimum salary of all the employees.
6. For each department, display the department number, the average, maximum, and minimum salary earned by the employees of the department. Sort the data by maximum salary.
7. Find the lowest salary earned by any employee in each department. Display only those departments where the minimum basic salary is less than $8,000. Display both the department ID and highest salary.
8. Find the total number of employees working in each department. Display only those departments that have a count greater than 3.
9. Display the HOD and number of employees working for each HOD.
10. Display the department name and average salary of all the employees in each department. Sort the output by average salary.
Explanation / Answer
/*1. Use a join to display the employee identification number,
employee name, job description and department name of all employees of the SALES department.*/
SELECT e.EMPLOYEE_ID ,e.EMPLOYEE_NAME ,e.JOB_DESCRIPTION ,d.DEPARTMENT_NAME
FROM EMPLOYEES e INNER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE d.DEPARTMENT_NAME='SALES';
/*2. Use a subquery to display employee identification number,
employee name, job description and department name of all employees of the SALES department.*/
SELECT EMPLOYEE_ID,EMPLOYEE_ID,JOB_DESCRIPTION,(
SELECT DEPARTMENT_NAME FROM DEPARTMENTS WHERE
DEPARTMENTS.DEPARTMENT_ID=EMPLOYEES.DEPARTMENT_ID
DEPARTMENT_NAME='SALES')
FROM EMPLOYEES;
/*3. Use a subquery to display the details of the employees who have been hired after the employee JONES was hired.*/
SELECT * FROM EMPLOYEES WHERE HIREDATE > (SELECT HIREDATE FROM EMPLOYEES WHERE EMPLOYEE_NAME = 'JONES');
/*4. Use a subquery to display the employee name, job description, and salary of all employees
who have the same job title as you (E.g. the employee you created with your name).*/
SELECT EMPLOYEE_NAME ,JOB_DESCRIPTION,SALARY FROM EMPLOYEES
WHERE JOB_DESCRIPTION = (SELECT JOB_DESCRIPTION FROM EMPLOYEES
WHERE EMPLOYEE_NAME='SMITH'/*U need to provide you name. Please modify*/);
/*5. Display the average, maximum, and minimum salary of all the employees.*/
SELECT AVG(SALARY) AS AverageSalary,MIN(SALARY) AS MinimumSalary,MAX(SALARY) AS MaximumSalary
FROM EMPLOYEES;
/*6. For each department, display the
department number, the average, maximum, and minimum salary earned by the employees of the department.
Sort the data by maximum salary.*/
SELECT AVG(SALARY) AS AverageSalary,MIN(SALARY) AS MinimumSalary,MAX(SALARY) AS MaximumSalary
FROM EMPLOYEES GROUP BY DEPARTMENT_ID ORDER BY MAX(SALARY);
/*7. Find the lowest salary earned by any employee in each department.
Display only those departments where the minimum basic salary is less than $8,000.
Display both the department ID and highest salary.*/
SELECT EMPLOYEE_NAME,MIN(SALARY) AS LowestSalary FROM EMPLOYEES
GROUP BY DEPARTMENT_ID HAVING SALARY < 8000;
/*8. Find the total number of employees working in each department. Display only those departments that have a count greater than 3.*/
SELECT d.DEPARTMENT_NAME,COUNT(e.EMPLOYEE_ID) AS EmployeeCount
FROM EMPLOYEES e INNER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_NAME HAVING COUNT(e.EMPLOYEE_ID) > 3;
/*9. Display the HOD and number of employees working for each HOD.*/
SELECT HOD,COUNT(EMPLOYEE_ID) AS EmployeeCount FROM EMPLOYEES GROUP BY HOD;
/*10. Display the department name and average salary of all the employees in each department. Sort the output by average salary. */
SELECT d.DEPARTMENT_NAME,AVG(e.SALARY) AS AverageSalary
FROM EMPLOYEES e INNER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_NAME
ORDER BY AVG(e.SALARY)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.