-- Create the DEPARTMENT table
CREATE TABLE DEPARTMENT (
    DepartmentName NVARCHAR(35) PRIMARY KEY,
    BudgetCode NVARCHAR(30) NOT NULL,
    OfficeNumber NVARCHAR(15) NOT NULL,
    DepartmentPhone NVARCHAR(12) NOT NULL
);
GO

-- Create the EMPLOYEE table
CREATE TABLE EMPLOYEE (
    EmployeeNumber INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(25) NOT NULL,
    LastName NVARCHAR(25) NOT NULL,
    Department NVARCHAR(35) NOT NULL,
    Supervisor INT,
    OfficePhone NVARCHAR(12),
    EmailAddress NVARCHAR(100) NOT NULL,
    FOREIGN KEY (Department) REFERENCES DEPARTMENT(DepartmentName),
    FOREIGN KEY (Supervisor) REFERENCES EMPLOYEE(EmployeeNumber)
);
GO

-- Create the PROJECT table
CREATE TABLE PROJECT (
    ProjectID INT IDENTITY(1,1) PRIMARY KEY,
    ProjectName NVARCHAR(50) NOT NULL,
    Department NVARCHAR(35) NOT NULL,
    MaxHours DECIMAL(8,2),
    StartDate DATE,
    EndDate DATE,
    FOREIGN KEY (Department) REFERENCES DEPARTMENT(DepartmentName)
);
GO

-- Create the ASSIGNMENT table
CREATE TABLE ASSIGNMENT (
    ProjectID INT,
    EmployeeNumber INT,
    HoursWorked DECIMAL(6,2),
    PRIMARY KEY (ProjectID, EmployeeNumber),
    FOREIGN KEY (ProjectID) REFERENCES PROJECT(ProjectID),
    FOREIGN KEY (EmployeeNumber) REFERENCES EMPLOYEE(EmployeeNumber)
);
GO
-- Create sample departments
INSERT INTO DEPARTMENT (DepartmentName, BudgetCode, OfficeNumber, DepartmentPhone)
VALUES
    ('Marketing', 'MKT001', 'MKT-101', '555-1234'),
    ('Research and Development', 'RD001', 'RD-201', '555-5678'),
    ('Sales', 'SAL001', 'SAL-301', '555-9876'),
    ('Finance', 'FIN001', 'FIN-401', '555-5432');

-- Create sample employees
INSERT INTO EMPLOYEE (FirstName, LastName, Department, Supervisor, OfficePhone, EmailAddress)
VALUES
    ('John', 'Doe', 'Marketing', NULL, '555-1111', '[email protected]'),
    ('Jane', 'Smith', 'Marketing', 1, '555-1112', '[email protected]'),
    ('Michael', 'Johnson', 'Research and Development', NULL, '555-2221', '[email protected]'),
    ('Emily', 'Brown', 'Research and Development', 3, '555-2222', '[email protected]'),
    ('David', 'Lee', 'Sales', NULL, '555-3331', '[email protected]'),
    ('Sarah', 'Wilson', 'Sales', 5, '555-3332', '[email protected]'),
    ('William', 'Jones', 'Finance', NULL, '555-4441', '[email protected]'),
    ('Olivia', 'Miller', 'Finance', 7, '555-4442', '[email protected]');

-- Create sample projects
INSERT INTO PROJECT (ProjectName, Department, MaxHours, StartDate, EndDate)
VALUES
    ('Project A', 'Marketing', 100.00, '2023-01-01', '2023-06-30'),
    ('Project B', 'Marketing', 150.00, '2023-02-15', '2023-08-31'),
    ('Project X', 'Research and Development', 200.00, '2023-03-01', '2023-09-30'),
    ('Project Y', 'Sales', 75.00, '2023-04-01', '2023-10-31'),
    ('Project Z', 'Finance', 120.00, '2023-05-01', '2023-11-30');

-- Create sample assignments
INSERT INTO ASSIGNMENT (ProjectID, EmployeeNumber, HoursWorked)
VALUES
    (1, 1, 40.00),
    (1, 2, 30.00),
    (2, 1, 20.00),
    (2, 2, 25.00),
    (3, 3, 45.00),
    (3, 4, 55.00),
    (4, 5, 35.00),
    (4, 6, 40.00),
    (5, 7, 25.00),
    (5, 8, 30.00);
    
    
-- Query 1: What projects are in the PROJECT table? Show all information for each project.
SELECT * FROM PROJECT;

-- Query 1: What projects are in the PROJECT table? Show all information for each project.
SELECT * FROM PROJECT;

-- Query 2: What are the ProjectID, ProjectName, StartDate, and EndDate values of projects in the PROJECT table?
SELECT ProjectID, ProjectName, StartDate, EndDate FROM PROJECT;

-- Query 3: What projects in the PROJECT table started before August 1, 2018? Show all the information for each project.
SELECT * FROM PROJECT WHERE StartDate < '2018-08-01';

-- Query 4: What projects in the PROJECT table have not been completed? Show all the information for each project.
SELECT * FROM PROJECT WHERE EndDate IS NULL OR EndDate > GETDATE();

-- Query 5: Who are the employees assigned to each project? Show ProjectID, EmployeeNumber, LastName, FirstName, and OfficePhone.
SELECT A.ProjectID, A.EmployeeNumber, E.LastName, E.FirstName, E.OfficePhone
FROM ASSIGNMENT A
INNER JOIN EMPLOYEE E ON A.EmployeeNumber = E.EmployeeNumber;

-- Query 6: Who are the employees assigned to each project? Show ProjectID, ProjectName, and Department. Show EmployeeNumber, LastName, FirstName, and OfficePhone.
SELECT A.ProjectID, P.ProjectName, P.Department, A.EmployeeNumber, E.LastName, E.FirstName, E.OfficePhone
FROM ASSIGNMENT A
INNER JOIN EMPLOYEE E ON A.EmployeeNumber = E.EmployeeNumber
INNER JOIN PROJECT P ON A.ProjectID = P.ProjectID;

-- Query 7: Who are the employees assigned to each project? Show ProjectID, ProjectName, Department, and DepartmentPhone. Show EmployeeNumber, LastName, FirstName, and OfficePhone. Sort by ProjectID in ascending order.
SELECT P.ProjectID, P.ProjectName, P.Department, D.DepartmentPhone, A.EmployeeNumber, E.LastName, E.FirstName, E.OfficePhone
FROM ASSIGNMENT A
INNER JOIN EMPLOYEE E ON A.EmployeeNumber = E.EmployeeNumber
INNER JOIN PROJECT P ON A.ProjectID = P.ProjectID
INNER JOIN DEPARTMENT D ON P.Department = D.DepartmentName
ORDER BY P.ProjectID ASC;

-- Query 8: Who are the employees assigned to projects run by the marketing department? Show ProjectID, ProjectName, Department, and DepartmentPhone. Show EmployeeNumber, LastName, FirstName, and OfficePhone. Sort by ProjectID in ascending order.
SELECT P.ProjectID, P.ProjectName, P.Department, D.DepartmentPhone, A.EmployeeNumber, E.LastName, E.FirstName, E.OfficePhone
FROM ASSIGNMENT A
INNER JOIN EMPLOYEE E ON A.EmployeeNumber = E.EmployeeNumber
INNER JOIN PROJECT P ON A.ProjectID = P.ProjectID
INNER JOIN DEPARTMENT D ON P.Department = D.DepartmentName
WHERE P.Department = 'Marketing'
ORDER BY P.ProjectID ASC;

-- Query 9: How many projects are being run by the Marketing department? Be sure to assign an appropriate column name to the computed results.
SELECT P.Department AS DepartmentName, COUNT(P.ProjectID) AS NumberOfProjects
FROM PROJECT P
WHERE P.Department = 'Marketing'
GROUP BY P.Department;

-- Query 10: What is the total MaxHours of projects being run by the Marketing department? Be sure to assign an appropriate column name to the computed results.
SELECT P.Department AS DepartmentName, SUM(P.MaxHours) AS TotalMaxHours
FROM PROJECT P
WHERE P.Department = 'Marketing'
GROUP BY P.Department;

-- Query 11: What is the average MaxHours of projects being run by the Marketing department? Be sure to assign an appropriate column name to the computed results.
SELECT P.Department AS DepartmentName, AVG(P.MaxHours) AS AvgMaxHours
FROM PROJECT P
WHERE P.Department = 'Marketing'
GROUP BY P.Department;

SELECT P.Department AS DepartmentName, COUNT(P.ProjectID) AS NumberOfProjects
FROM PROJECT P
GROUP BY P.Department;

-- Query 13: Who supervises each employee at Wedgewood Pacific? Include the names of employees with no supervisor in the results of the query.
SELECT E.EmployeeNumber, E.FirstName, E.LastName, ISNULL(S.EmployeeNumber, 'No Supervisor') AS Supervisor
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE S ON E.Supervisor = S.EmployeeNumber;

-- Query 14: Write an SQL statement to join EMPLOYEE, ASSIGNMENT, and PROJECT using the JOIN ON syntax.
SELECT E.EmployeeNumber, E.FirstName, E.LastName, A.ProjectID, P.ProjectName
FROM EMPLOYEE E
INNER JOIN ASSIGNMENT A ON E.EmployeeNumber = A.EmployeeNumber
INNER JOIN PROJECT P ON A.ProjectID = P.ProjectID;

-- Query 15: Write an SQL statement to join EMPLOYEE and ASSIGNMENT and include all rows of EMPLOYEE in your answer, regardless of whether they have an ASSIGNMENT.
SELECT E.EmployeeNumber, E.FirstName, E.LastName, A.ProjectID
FROM EMPLOYEE E
LEFT JOIN ASSIGNMENT A ON E.EmployeeNumber = A.EmployeeNumber;