Assignment No.
– 3
Name: James
Alieu SLamin
KeitaKamara
University Roll No: 231312014
231312018
Course: B.SC IT 3 Sem
Q1. Create the following table “Employees”
CREATE TABLE Employees (
Emp_id NUMBER(10) PRIMARY KEY,
Emp_name VARCHAR2(20),
Dept_id NUMBER(20)
);
INSERT INTO Employees (Emp_id, Emp_name, Dept_id) VALUES (1, 'Alice', 10);
INSERT INTO Employees (Emp_id, Emp_name, Dept_id) VALUES (2, 'Bob', 20);
INSERT INTO Employees (Emp_id, Emp_name, Dept_id) VALUES (3, 'Charlie', 30);
INSERT INTO Employees (Emp_id, Emp_name, Dept_id) VALUES (4, 'David', NULL);
INSERT INTO Employees (Emp_id, Emp_name, Dept_id) VALUES (5, 'Emma', 10);
INSERT INTO Employees (Emp_id, Emp_name, Dept_id) VALUES (6, 'Frank', 20);
INSERT INTO Employees (Emp_id, Emp_name, Dept_id) VALUES (7, 'Grace', 30);
INSERT INTO Employees (Emp_id, Emp_name, Dept_id) VALUES (8, 'Hannah', 30);
CREATE TABLE Departments (
Dept_id NUMBER(10) PRIMARY KEY,
Dept_name VARCHAR2(10)
);
INSERT INTO Departments (Dept_id, Dept_name) VALUES (10, 'HR');
INSERT INTO Departments (Dept_id, Dept_name) VALUES (20, 'IT');
INSERT INTO Departments (Dept_id, Dept_name) VALUES (40, 'Marketing');
INSERT INTO Departments (Dept_id, Dept_name) VALUES (30, 'Finance');
INSERT INTO Departments (Dept_id, Dept_name) VALUES (50, 'Operations');
INSERT INTO Departments (Dept_id, Dept_name) VALUES (60, 'Research');
INSERT INTO Departments (Dept_id, Dept_name) VALUES (70, 'Legal');
INSERT INTO Departments (Dept_id, Dept_name) VALUES (80, 'Sales');
1. Use an INNER JOIN to find the names of employees who belong to a department.
SELECT
e.Emp_name,
d.Dept_name
FROM
Employees e
INNER JOIN
Departments d
ON
e.Dept_id = d.Dept_id;
2. Use a LEFT OUTER JOIN to list all employees, showing their department if they have
one. If an employee does not belong to a department, show NULL for the department
name.
SELECT
e.Emp_name,
d.Dept_name
FROM
Employees e
LEFT OUTER JOIN
Departments d
ON
e.Dept_id = d.Dept_id;
3. Use a RIGHT OUTER JOIN to list all departments, showing the names of
employees in each department.
SELECT
e.Emp_name,
d.Dept_name
FROM
Employees e
LEFT OUTER JOIN
Departments d
ON
e.Dept_id = d.Dept_id;
SELECT
d.Dept_name,
e.Emp_name
FROM
Departments d
LEFT JOIN
Employees e
ON
d.Dept_id = e.Dept_id
UNION
SELECT
d.Dept_name,
e.Emp_name
FROM
Employees e
LEFT JOIN
Departments d
ON
e.Dept_id = d.Dept_id;
4. Use a FULL OUTER JOIN to list all employees and all departments.
SELECT
d.Dept_name,
e.Emp_name
FROM
Employees e
LEFT JOIN
Departments d
ON
e.Dept_id = d.Dept_id;
SELECT
e.Emp_name,
d.Dept_name
FROM
Employees e
LEFT JOIN
Departments d
ON
e.Dept_id = d.Dept_id
UNION
SELECT
e.Emp_name,
d.Dept_name
FROM
Departments d
LEFT JOIN
Employees e
ON
d.Dept_id = e.Dept_id;
5. Use an INNER JOIN to find the names of employees who are in both IT and HR
departments.
SELECT
e.Emp_name,
d.Dept_name
FROM
Employees e
INNER JOIN
Departments d
ON
e.Dept_id = d.Dept_id
WHERE
d.Dept_name IN ('IT', 'HR');
6. Write a query to find employees without a department using a LEFT JOIN.
SELECT
e.Emp_name
FROM
Employees e
LEFT JOIN
Departments d
ON
e.Dept_id = d.Dept_id
WHERE
d.Dept_id IS NULL;
7. Find the total number of employees in each department using INNER JOIN and GROUP
BY.
SELECT
d.Dept_name,
COUNT(e.Emp_id) AS Total_Employees
FROM
Employees e
INNER JOIN
Departments d
ON
e.Dept_id = d.Dept_id
GROUP BY
d.Dept_name;
8. Find all employees who do not belong to either the "HR" or "IT" department using an
OUTER JOIN.
SELECT
e.Emp_name,
d.Dept_name
FROM
Employees e
LEFT JOIN
Departments d
ON
e.Dept_id = d.Dept_id
WHERE
d.Dept_name NOT IN ('HR', 'IT') OR d.Dept_name IS NULL;
9. List the names of employees and departments where employees work, but exclude
records where department names start with the letter "M."
SELECT
e.Emp_name,
d.Dept_name
FROM
Employees e
INNER JOIN
Departments d
ON
e.Dept_id = d.Dept_id
WHERE
d.Dept_name NOT LIKE 'M%';
10. Use a LEFT JOIN to list all employees and count how many employees are in each
department, even if some departments have no employees.
SELECT
d.Dept_name,
COUNT(e.Emp_id) AS Total_Employees
FROM
Departments d
LEFT JOIN
Employees e
ON
d.Dept_id = e.Dept_id
GROUP BY
d.Dept_name;
11. Find the department name and employee name for employees who have no department
assigned by using an OUTER JOIN.
SELECT
e.Emp_name,
d.Dept_name
FROM
Employees e
LEFT JOIN
Departments d
ON
e.Dept_id = d.Dept_id
WHERE
d.Dept_name IS NULL;
12. Write a query to find employees who are in the same department as another
employee.(This query uses a Self Join to find pairs of employees in the same
department.)
SELECT
e.Emp_name,
d.Dept_name
FROM
Employees e
LEFT JOIN
Departments d
ON
e.Dept_id = d.Dept_id
WHERE
d.Dept_name IS NULL;
SELECT
e1.Emp_name AS Employee_1,
e2.Emp_name AS Employee_2,
d.Dept_name
FROM
Employees e1
INNER JOIN
Employees e2
ON
e1.Dept_id = e2.Dept_id AND e1.Emp_id != e2.Emp_id
INNER JOIN
Departments d
ON
e1.Dept_id = d.Dept_id;
