-- Create the database

CREATE DATABASE IF NOT EXISTS CompanyDB;

USE CompanyDB;

-- Disable foreign key checks temporarily

SET FOREIGN_KEY_CHECKS = 0;

-- Create the Employee table

CREATE TABLE Employee (

ssn CHAR(9) PRIMARY KEY,

name VARCHAR(50) NOT NULL,

salary DECIMAL(10, 2) NOT NULL,

sex CHAR(1),

super_ssn CHAR(9),

address VARCHAR(100),

dno INT

);

-- Create the Department table

CREATE TABLE Department (

dname VARCHAR(50) NOT NULL,

dnumber INT PRIMARY KEY,

mgr_ssn CHAR(9)

);

-- Create the Dept_Loc table

CREATE TABLE Dept_Loc (

dnumber INT,

dloc VARCHAR(50),

PRIMARY KEY (dnumber, dloc)

);
-- Create the Project table

CREATE TABLE Project (

pname VARCHAR(50) NOT NULL,

pnumber INT PRIMARY KEY,

plocation VARCHAR(50),

dnum INT

);

-- Create the Works_On table

CREATE TABLE Works_On (

essn CHAR(9),

pno INT,

hours DECIMAL(5, 2),

PRIMARY KEY (essn, pno)

);

-- Create the Dependent table

CREATE TABLE Dependent (

essn CHAR(9),

depen_name VARCHAR(50),

address VARCHAR(100),

relationship VARCHAR(20),

sex CHAR(1),

PRIMARY KEY (essn, depen_name)

);

-- Add foreign key constraints after all tables are created

ALTER TABLE Employee

ADD CONSTRAINT fk_employee_super_ssn

FOREIGN KEY (super_ssn) REFERENCES Employee(ssn);

ADD CONSTRAINT fk_employee_dno

FOREIGN KEY (dno) REFERENCES Department(dnumber);

ALTER TABLE Department

ADD CONSTRAINT fk_department_mgr_ssn

FOREIGN KEY (mgr_ssn) REFERENCES Employee(ssn);

ALTER TABLE Dept_Loc

ADD CONSTRAINT fk_dept_loc_dnumber

FOREIGN KEY (dnumber) REFERENCES Department(dnumber);

ALTER TABLE Project

ADD CONSTRAINT fk_project_dnum

FOREIGN KEY (dnum) REFERENCES Department(dnumber);

ALTER TABLE Works_On

ADD CONSTRAINT fk_works_on_essn

FOREIGN KEY (essn) REFERENCES Employee(ssn);

ALTER TABLE Works_On

ADD CONSTRAINT fk_works_on_pno

FOREIGN KEY (pno) REFERENCES Project(pnumber);

ALTER TABLE Dependent

ADD CONSTRAINT fk_dependent_essn

FOREIGN KEY (essn) REFERENCES Employee(ssn);

-- Enable foreign key checks

SET FOREIGN_KEY_CHECKS = 1;
-- Insert sample data into Employee table (without supervisor and department initially)

INSERT INTO Employee (ssn, name, salary, sex, super_ssn, address, dno) VALUES

('111111111', 'John Doe', 75000.00, 'M', NULL, '123 Main St', NULL),

('222222222', 'Jane Smith', 80000.00, 'F', NULL, '456 Elm St', NULL),

('333333333', 'Alice Johnson', 90000.00, 'F', NULL, '789 Oak St', NULL),

('444444444', 'Bob Brown', 85000.00, 'M', NULL, '321 Pine St', NULL),

('555555555', 'Charlie Davis', 70000.00, 'M', NULL, '654 Cedar St', NULL);

-- Update Employee table to add supervisors

UPDATE Employee

SET super_ssn = '111111111'

WHERE ssn IN ('222222222', '333333333');

UPDATE Employee

SET super_ssn = '222222222'

WHERE ssn IN ('444444444', '555555555');

-- Insert into Department table

INSERT INTO Department (dname, dnumber, mgr_ssn) VALUES

('HR', 1, '111111111'),

('IT', 2, '222222222'),

('Finance', 3, '333333333');

-- Update Employee table to add department numbers

UPDATE Employee

SET dno = 1

WHERE ssn IN ('111111111', '444444444');

UPDATE Employee

SET dno = 2
WHERE ssn IN ('222222222', '555555555');

UPDATE Employee

SET dno = 3

WHERE ssn = '333333333';

-- Insert into Dept_Loc table

INSERT INTO Dept_Loc (dnumber, dloc) VALUES

(1, 'New York'),

(2, 'San Francisco'),

(3, 'Chicago');

-- Insert into Project table

INSERT INTO Project (pname, pnumber, plocation, dnum) VALUES

('Project A', 1, 'New York', 1),

('Project B', 2, 'San Francisco', 2),

('Project C', 3, 'Chicago', 3),

('Project D', 4, 'New York', 1),

('Project E', 5, 'San Francisco', 2);

-- Insert into Works_On table

INSERT INTO Works_On (essn, pno, hours) VALUES

('111111111', 1, 20.0),

('222222222', 2, 30.0),

('333333333', 3, 25.0),

('444444444', 4, 15.0),

('555555555', 5, 10.0);

-- Insert into Dependent table

INSERT INTO Dependent (essn, depen_name, address, relationship, sex) VALUES

('111111111', 'Mary Doe', '123 Main St', 'Spouse', 'F'),

('333333333', 'Alice Johnson Jr', '789 Oak St', 'Child', 'F'),

('444444444', 'Bob Brown Jr', '321 Pine St', 'Child', 'M'),

('555555555', 'Charlie Davis Jr', '654 Cedar St', 'Child', 'M');

-- Display all tables

SELECT * FROM Employee;

SELECT * FROM Department;

SELECT * FROM Dept_Loc;

SELECT * FROM Project;

SELECT * FROM Works_On;

SELECT * FROM Dependent;

-- Queries for the given questions

-- a. Retrieve the names of the Employees who work on all the projects controlled by dept no 3.

SELECT e.name

FROM Employee e

WHERE NOT EXISTS (

SELECT p.pnumber

FROM Project p

WHERE p.dnum = 3

AND NOT EXISTS (

SELECT w.essn

FROM Works_On w

WHERE w.essn = e.ssn

AND w.pno = p.pnumber

);

-- b. Retrieve the names of the Employees who get the second highest salary.
SELECT name

FROM Employee

WHERE salary = (

SELECT DISTINCT salary

FROM Employee

ORDER BY salary DESC

LIMIT 1 OFFSET 1

);

-- c. Retrieve the names of the Employees who have no dependents in alphabetical order.

SELECT e.name

FROM Employee e

WHERE NOT EXISTS (

SELECT d.essn

FROM Dependent d

WHERE d.essn = e.ssn

ORDER BY e.name ASC;

-- d. List the names of all Employees with at least two dependents.

SELECT e.name

FROM Employee e

WHERE (

SELECT COUNT(*)

FROM Dependent d

WHERE d.essn = e.ssn

) >= 2;

-- e. Retrieve the number of Employees and their average salary working in each Department.

SELECT d.dname, COUNT(e.ssn) AS num_employees, AVG(e.salary) AS avg_salary

FROM Department d
LEFT JOIN Employee e ON d.dnumber = e.dno

GROUP BY d.dname;

-- f. Retrieve the highest salary paid in each Department in descending order.

SELECT d.dname, MAX(e.salary) AS max_salary

FROM Department d

LEFT JOIN Employee e ON d.dnumber = e.dno

GROUP BY d.dname

ORDER BY max_salary DESC;

-- g. Retrieve the SSN of all Employees who work on at least one of the project numbers 1, 2, 3.

SELECT DISTINCT w.essn

FROM Works_On w

WHERE w.pno IN (1, 2, 3);

-- h. Retrieve the number of dependents for an Employee named RAM.

SELECT COUNT(*) AS num_dependents

FROM Dependent d

JOIN Employee e ON d.essn = e.ssn

WHERE e.name = 'RAM';

-- i. Retrieve the names of the managers working in location named xyz who have no female
dependents.

SELECT e.name

FROM Employee e

JOIN Department d ON e.ssn = d.mgr_ssn

JOIN Dept_Loc dl ON d.dnumber = dl.dnumber

WHERE dl.dloc = 'xyz'

AND NOT EXISTS (

SELECT dep.essn

FROM Dependent dep

AND dep.sex = 'F'

);

-- j. Retrieve the names of the Employees who work in the same Department as that of RAM.

SELECT e.name

FROM Employee e

WHERE e.dno = (

SELECT dno

FROM Employee

WHERE name = 'RAM'

);

-- k. Retrieve the names of the Employees whose salary is greater than the salary of all the
Employees working in Department no 3.

SELECT e.name

FROM Employee e

WHERE e.salary > ALL (

SELECT salary

FROM Employee

WHERE dno = 3

);

-- l. Retrieve the names of the Employees who work for dept no 3 and have a daughter as
dependent.

SELECT e.name

FROM Employee e

JOIN Dependent d ON e.ssn = d.essn

WHERE e.dno = 3

AND d.relationship = 'Child'

AND d.sex = 'F';

SELECT e.name

FROM Employee e

JOIN (

SELECT dno, MAX(salary) AS max_salary

FROM Employee

GROUP BY dno

) AS dept_max ON e.dno = dept_max.dno AND e.salary = dept_max.max_salary;

-- n. Retrieve the names of the Employees who are paid the same salary as that of Anil.

SELECT e.name

FROM Employee e

WHERE e.salary = (

SELECT salary

FROM Employee

WHERE name = 'Anil'

);

-- o. Retrieve the total number of Employees in the ‘Research’ Department.

SELECT COUNT(*) AS num_employees

FROM Employee e

JOIN Department d ON e.dno = d.dnumber

WHERE d.dname = 'Research';

-- p. For each project, retrieve the project number, the project name, and the number of Employees
who work on that project.

SELECT p.pnumber, p.pname, COUNT(w.essn) AS num_employees

FROM Project p

LEFT JOIN Works_On w ON p.pnumber = w.pno

GROUP BY p.pnumber, p.pname;