Assignment 6 (Joins)

divya sachan

@divyasachan

Created

Oct 31, 2025

Last Modified

4 months ago

Assignment 6

✅ Theory & Concept (Clean & Enhanced Readability)

Objective: To implement the concept of Joins and Subqueries in SQL.

🔷 Joins in SQL

Sometimes we need to use data from multiple tables as if they were one single table. To do this, we use JOIN.
Tables are joined on columns having the same data type and logically matching values.

📌 Tables to be joined are written in the FROM clause and the joining condition in the WHERE clause.

📍 Algorithm for JOIN in SQL

Step	Description
1	Perform Cartesian Product of tables (FROM clause)
2	Select matching rows based on join condition (WHERE clause)
3	Project required columns (SELECT clause)

🧩 Types of Joins

1. Cartesian Product

Returns all possible combinations of rows from the tables.

text

SELECT B.*, P.*
FROM student B, course P;

2. INNER JOIN

Returns only matching rows.

text

SELECT B.*, P.*
FROM student B, course P
WHERE B.course# = P.course#;

3. LEFT OUTER JOIN

Returns matching + unmatched rows from left table.

text

SELECT B.*, P.*
FROM student B LEFT JOIN course P
ON B.course# = P.course#;

4. RIGHT OUTER JOIN

Returns matching + unmatched rows from right table.

text

SELECT B.*, P.*
FROM student B RIGHT JOIN course P
ON B.course# = P.course#;

5. FULL OUTER JOIN

Returns matching + unmatched rows from both tables.

text

SELECT B.*, P.*
FROM student B FULL JOIN course P
ON B.course# = P.course#;

🔶 SQL Subqueries

A Subquery is a query within another query.
Also called Inner Query or Nested Query.
The main query is called Outer Query.

Subqueries can be used in:
SELECT, INSERT, UPDATE, DELETE, WHERE clause, HAVING, with IN, ANY, ALL, BETWEEN, etc.

✅ Rules of Subquery

Must be enclosed in ( ) parentheses
Can be nested multiple levels
Must contain SELECT and FROM clause
Can include WHERE, GROUP BY, HAVING, DISTINCT
ORDER BY only allowed with TOP/LIMIT
Can return:
- Single Value (Scalar Subquery)
- Single Row
- Single Column
- Complete Table

✨ Assignment – Joins & Subqueries

📍 All Answers in a Single Code Block (as requested)

sql

-- 1. Display the employees-id, employee name, dept_id & department name for all the employees.
SELECT e.emp_id, e.last_name, e.dept_id, d.dname
FROM employee e
JOIN dept d ON e.dept_id = d.dept_id;

-- 2. Display the employee smith department number & department name.
SELECT e.last_name, e.dept_id, d.dname
FROM employee e
JOIN dept d ON e.dept_id = d.dept_id
WHERE e.last_name = 'SMITH';

-- 3. Display the employee last name, department name & city for each employee whose salary is greater than 2000
SELECT e.last_name, d.dname, d.city
FROM employee e
JOIN dept d ON e.dept_id = d.dept_id
WHERE e.salary > 2000;

-- 4. Display the employee last name & department name for all employees who have ‘a’ in their last name.
SELECT e.last_name, d.dname
FROM employee e
JOIN dept d ON e.dept_id = d.dept_id
WHERE e.last_name LIKE '%A%';

-- 5. Display the employee last name & employee number along with their manager last name & manager number.
-- (employee_lastname, emp_id, manager_lastname, manager_id)
SELECT e.last_name AS employee_lastname, e.emp_id,
       m.last_name AS manager_lastname, m.emp_id AS manager_id
FROM employee e
JOIN employee m ON e.manager_id = m.emp_id;

-- 6. Implement right outer join, left outer join and full outer join between employee and dept table.
SELECT e.*, d.* FROM employee e LEFT JOIN dept d ON e.dept_id = d.dept_id;
SELECT e.*, d.* FROM employee e RIGHT JOIN dept d ON e.dept_id = d.dept_id;
SELECT e.*, d.* FROM employee e FULL OUTER JOIN dept d ON e.dept_id = d.dept_id;

-- 7. Display the employees whose job-id is same as that of employee 7902
SELECT * FROM employee
WHERE job_id = (SELECT job_id FROM employee WHERE emp_id = 7902);

-- 8. Display the employees whose job_id is same as that of employee 7902 & whose salary is > employee 7844.
SELECT * FROM employee
WHERE job_id = (SELECT job_id FROM employee WHERE emp_id = 7902)
AND salary > (SELECT salary FROM employee WHERE emp_id = 7844);

-- 9. Display the information of all employees whose salary is equal to minimum salary.
SELECT * FROM employee
WHERE salary = (SELECT MIN(salary) FROM employee);

-- 10. Display all the department minimum salary that have min salary greater than that of department 20.
SELECT dept_id, MIN(salary) AS min_salary
FROM employee
GROUP BY dept_id
HAVING MIN(salary) > (SELECT MIN(salary) FROM employee WHERE dept_id = 20);

-- 11. Find the job with the lowest average salary.
SELECT job_id
FROM employee
GROUP BY job_id
ORDER BY AVG(salary) ASC
FETCH FIRST 1 ROW ONLY;

-- 12. Display the emp number & name of all employees who work in a dept with any employee whose last name contains 'u'.
SELECT emp_id, last_name
FROM employee
WHERE dept_id IN (
  SELECT dept_id FROM employee WHERE last_name LIKE '%U%'
);

-- 13. Display the last name & salary of every employee who reports to KING.
SELECT e.last_name, e.salary
FROM employee e
JOIN employee m ON e.manager_id = m.emp_id
WHERE m.last_name = 'KING';

-- 14. Display the name of employee who is getting the maximum salary.
SELECT last_name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee);

-- 15. Display the emp number, last_name & salaries of all employees who work in a department
-- with any employee whose last name contains letter 'u' & who earn > avg salary.
SELECT emp_id, last_name, salary
FROM employee
WHERE dept_id IN (SELECT dept_id FROM employee WHERE last_name LIKE '%U%')
AND salary > (SELECT AVG(salary) FROM employee);

-- 16. Display the employees who work in the same department as that of Steven.
SELECT * FROM employee
WHERE dept_id = (SELECT dept_id FROM employee WHERE last_name = 'STEVEN')
AND last_name <> 'STEVEN';

-- 17. Find the employee who earns the least.
SELECT * FROM employee
WHERE salary = (SELECT MIN(salary) FROM employee);

-- 18. Show dept no, dept name & number of employees:
-- a) Includes departments having employees less than 3
SELECT d.dept_id, d.dname, COUNT(e.emp_id) AS emp_count
FROM dept d
LEFT JOIN employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dname
HAVING COUNT(e.emp_id) < 3;

-- b) Has the highest no. of employees
SELECT dept_id, dname, emp_count FROM (
  SELECT d.dept_id, d.dname, COUNT(e.emp_id) AS emp_count,
         RANK() OVER (ORDER BY COUNT(e.emp_id) DESC) rnk
  FROM dept d LEFT JOIN employee e ON d.dept_id = e.dept_id
  GROUP BY d.dept_id, d.dname
) WHERE rnk = 1;

-- c) Has the lowest no. of employees
SELECT dept_id, dname, emp_count FROM (
  SELECT d.dept_id, d.dname, COUNT(e.emp_id) AS emp_count,
         RANK() OVER (ORDER BY COUNT(e.emp_id) ASC) rnk
  FROM dept d LEFT JOIN employee e ON d.dept_id = e.dept_id
  GROUP BY d.dept_id, d.dname
) WHERE rnk = 1;

-- 19. Write a query to list the employees who earn more than their managers.
SELECT e.last_name, e.salary, m.last_name AS manager, m.salary AS manager_salary
FROM employee e
JOIN employee m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

-- 20. List the department number, department name & the number of employees.
SELECT d.dept_id, d.dname, COUNT(e.emp_id) AS total_employees
FROM dept d
LEFT JOIN employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dname;