Scribd
Upload
3 views3 pages

Homework4 - SQL

The document outlines a series of SQL queries for retrieving and analyzing employee and department data from a database. It includes operations such as retrieving unique job titles, department IDs, and finding employees with salaries above their department's average. Additionally, it covers finding department-wise average salaries, identifying departments with employees receiving commissions, and selecting job titles from departments with the highest IDs.

Uploaded by

jackiefan475
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views3 pages

Homework4 - SQL

The document outlines a series of SQL queries for retrieving and analyzing employee and department data from a database. It includes operations such as retrieving unique job titles, department IDs, and finding employees with salaries above their department's average. Additionally, it covers finding department-wise average salaries, identifying departments with employees receiving commissions, and selecting job titles from departments with the highest IDs.

Uploaded by

jackiefan475
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Homework №4

1. Retrieve the unique job titles from the employees table and the job
history table.
SELECT DISTINCT job_title
FROM employees
UNION
SELECT DISTINCT job_title
FROM job_history;
2. Retrieve all department IDs from the employees table and the
departments table.
SELECT DISTINCT department_id
FROM employees
UNION
SELECT DISTINCT department_id
FROM departments;
3. Find the common department IDs between the employees table and
the job history table.
SELECT DISTINCT department_id
FROM employees
INTERSECT
SELECT DISTINCT department_id
FROM job_history;
4. Find the department IDs present in the employees table but not in the
departments table.
SELECT DISTINCT department_id
FROM employees
MINUS
SELECT DISTINCT department_id
FROM departments;
5. Find the job title, department ID, and manager ID of the employee
with the highest salary.
SELECT DISTINCT department_id
FROM employees
MINUS
SELECT DISTINCT department_id
FROM departments;
6. Find the employees who have a salary greater than the average salary
of their department.
SELECT job_title, department_id, manager_id
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
7. Retrieve the employees who have a salary greater than the average
salary of their department, correlated with the outer query.
SELECT e.employee_id, e.first_name, e.last_name, [Link],
e.department_id
FROM employees e
WHERE [Link] > (SELECT AVG([Link])
FROM employees e2
WHERE e2.department_id = e.department_id);
8. Find the department-wise average salary and the number of
employees in each department.
SELECT department_id, AVG(salary) AS average_salary, COUNT(*) AS
number_of_employees
FROM employees
GROUP BY department_id;
9. Find the departments that have at least one employee with a
commission
SELECT DISTINCT department_id
FROM employees
WHERE commission_pct IS NOT NULL;
10. Find the job titles of employees who work in departments with
the highest department IDs.
SELECT job_title
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE department_id = (SELECT MAX(department_id)
FROM departments));

You might also like