CREATE TABLE employee (
  fname VARCHAR(10), minit CHAR(1), lname VARCHAR(10),
  ssn BIGINT, bdate CHAR(10), address VARCHAR(30),
  sex CHAR(1), salary FLOAT, superssn BIGINT, dno INT
);


INSERT INTO employee VALUES
('John','B','Smith',123456789,'1965-01-09','731 Fondren, Houston, TX','M',30000,333445555,5),
('Franklin','T','Wong',333445555,'1955-12-08','638 Voss, Houston, TX','M',40000,888665555,5),
('Alicia','J','Zelaya',999887777,'1968-01-19','3321 Castle, Spring, TX','F',25000,987654321,4),
('Jennifer','S','Wallace',987654321,'1941-06-20','291 Berry, Bellaire, TX','F',43000,888665555,4),
('Ramesh','K','Narayan',666884444,'1962-09-15','975 Fire Oak, Humble, TX','M',38000,333445555,5),
('Joyce','A','English',453453453,'1972-07-31','5631 Rice, Houston, TX','F',25000,333445555,5),
('Ahmad','V','Jabbar',987987987,'1969-03-29','980 Dallas, Houston, TX','M',25000,987654321,4),
('James','E','Borg',888665555,'1937-11-10','450 Stone, Houston, TX','M',55000,NULL,1);

CREATE TABLE department (
  dname VARCHAR(15), dnumber INT, mgrssn BIGINT, mgrstartdate CHAR(10)
);
INSERT INTO department VALUES
('Research',5,333445555,'1988-05-22'),
('Administration',4,987654321,'1995-01-01'),
('Headquarters',1,888665555,'1981-06-19');

CREATE TABLE dept_locations (dnumber INT, dlocation VARCHAR(15));
INSERT INTO dept_locations VALUES (1,'Houston'),(4,'Stafford'),(5,'Bellaire'),(5,'Sugarland'),(5,'Houston');

CREATE TABLE project (
  pname VARCHAR(20), pnumber INT, plocation VARCHAR(15), dnum INT
);
INSERT INTO project VALUES
('ProductX',1,'Bellaire',5),('ProductY',2,'Sugarland',5),('ProductZ',3,'Houston',5),
('Computerization',10,'Stafford',4),('Reorganization',20,'Houston',1),('Newbenefits',30,'Stafford',4);

CREATE TABLE works_on (essn BIGINT, pno INT, hours FLOAT);
INSERT INTO works_on VALUES
(123456789,1,32.5),(123456789,2,7.5),(666884444,3,40.0),(453453453,1,20.0),(453453453,2,20.0),
(333445555,2,10.0),(333445555,3,10.0),(333445555,10,10.0),(333445555,20,10.0),
(999887777,30,30.0),(999887777,10,10.0),(987987987,10,35.0),(987987987,30,5.0),
(987654321,30,20.0),(987654321,20,15.0),(888665555,20,NULL);

CREATE TABLE dependent (
  essn BIGINT, dependent_name VARCHAR(15), sex CHAR(1), bdate CHAR(10), relationship VARCHAR(10)
);
INSERT INTO dependent VALUES
(333445555,'Alice','F','1986-04-05','Daughter'),
(333445555,'Theodore','M','1983-10-25','Son'),
(333445555,'Joy','F','1958-05-03','Spouse'),
(987654321,'Abner','M','1942-02-28','Spouse'),
(123456789,'Michael','M','1988-01-04','Son'),
(123456789,'Alice','F','1988-12-30','Daughter'),
(123456789,'Elizabeth','F','1967-05-05','Spouse');






-- =========================
-- TASK 1: RETRIEVE DATA
-- =========================

-- 1. Count all employee records.
SELECT COUNT(*) AS employee_count
FROM employee;

-- 2. Find employees earning more than $35,000, highest salary first.
SELECT fname, lname, salary, dno
FROM employee
WHERE salary > 35000
ORDER BY salary DESC;

-- 3. Calculate the average, total, highest, and lowest employee salary.
SELECT
    AVG(salary) AS average_salary,
    SUM(salary) AS total_salary,
    MAX(salary) AS highest_salary,
    MIN(salary) AS lowest_salary
FROM employee;

-- 4. Count how many projects each department controls.
SELECT dnum, COUNT(*) AS project_count
FROM project
GROUP BY dnum
ORDER BY dnum;

-- 5. Find employees who have a recorded total of more than 30 project hours.
-- SUM ignores NULL hours; employees with no work_on records are not included.
SELECT e.fname, e.lname, SUM(w.hours) AS total_project_hours
FROM employee AS e
INNER JOIN works_on AS w ON e.ssn = w.essn
GROUP BY e.ssn, e.fname, e.lname
HAVING SUM(w.hours) > 30
ORDER BY total_project_hours DESC;

-- =========================
-- TASK 2: MODIFY DATA
-- =========================

-- 6. Insert a sample employee record. Use a unique SSN that is not already in the table.
-- This example uses SSN 111223333; check for conflicts before running.
INSERT INTO employee
    (fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno)
SELECT
    'Taylor', 'R', 'Jordan', 111223333, '1998-04-12',
    '100 Sample Road, Houston, TX', 'F', 32000, NULL, 5
WHERE NOT EXISTS (
    SELECT 1 FROM employee WHERE ssn = 111223333
);

-- 7. Delete the sample employee inserted above, if present.
-- This is limited to the unique sample SSN used in Query 6.
DELETE FROM employee
WHERE ssn = 111223333
  AND fname = 'Taylor'
  AND lname = 'Jordan';

-- 8. Give employees in department 4 a 3% salary increase.
-- Run only if you intend to modify the original dataset. In systems supporting transactions,
-- use BEGIN/ROLLBACK to test first, or restore the original data after the exercise.
UPDATE employee
SET salary = salary * 1.03
WHERE dno = 4;

-- =========================
-- TASK 3: JOIN TABLES
-- =========================

-- 9. INNER JOIN: show each employee with their department name.
SELECT e.fname, e.lname, d.dname AS department_name
FROM employee AS e
INNER JOIN department AS d ON e.dno = d.dnumber
ORDER BY d.dname, e.lname;

-- 10. LEFT JOIN: list every employee, including employees who have no dependent.
SELECT e.fname, e.lname, dep.dependent_name, dep.relationship
FROM employee AS e
LEFT JOIN dependent AS dep ON e.ssn = dep.essn
ORDER BY e.lname, dep.dependent_name;

-- 11. Join employees, work assignments, and projects to show who works on what.
SELECT e.fname, e.lname, p.pname AS project_name, w.hours
FROM employee AS e
INNER JOIN works_on AS w ON e.ssn = w.essn
INNER JOIN project AS p ON w.pno = p.pnumber
ORDER BY e.lname, p.pname;

-- 12. Join departments to their locations, including each department's name and location.
SELECT d.dname AS department_name, dl.dlocation AS department_location
FROM department AS d
INNER JOIN dept_locations AS dl ON d.dnumber = dl.dnumber
ORDER BY d.dname, dl.dlocation;