-- Create tables
CREATE TABLE Employee (
    ID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    City VARCHAR(50),
    DepartmentID INT,
    Salary DECIMAL(10,2),
    YearsWithCompany INT,
    EmployeeManagerID INT,
    ProjectID INT
);

CREATE TABLE Department (
    ID INT PRIMARY KEY,
    DepartmentName VARCHAR(50),
    MainCity VARCHAR(50),
    DepartmentManagerID INT,
    FOREIGN KEY (DepartmentManagerID) REFERENCES Employee(ID)
);

CREATE TABLE Projects (
    ID INT PRIMARY KEY,
    ProjectName VARCHAR(50),
    ProjectManagerID INT,
    DepartmentID INT,
    TimeRemaining INT,
    FOREIGN KEY(ProjectManagerID) REFERENCES Employee(ID),
    FOREIGN KEY(DepartmentID) REFERENCES Department(ID)
);

-- Insert employees (IDs 1–25)
INSERT INTO Employee(ID, EmployeeName, City, DepartmentID, Salary, YearsWithCompany, EmployeeManagerID, ProjectID) VALUES
(1, 'Alice Johnson', 'New York', 1, 85000.00, 5, NULL, 1),
(2, 'Bob Smith', 'Chicago', 2, 78000.00, 3, 1, 2),
(3, 'Carlos Mendes', 'Miami', 3, 92000.00, 7, 1, 3),
(4, 'Diana Lee', 'San Francisco', 1, 67000.00, 2, 1, 1),
(5, 'Ethan Patel', 'Seattle', 2, 73000.00, 4, 2, 2),
(6, 'Fatima Al-Farsi', 'Houston', 3, 99000.00, 6, 3, 3),
(7, 'George Smith', 'Boston', 4, 88000.00, 8, NULL, 4),
(8, 'Hannah Kim', 'Denver', 4, 76000.00, 2, 7, 4),
(9, 'Ivan Petrov', 'Austin', 5, 81000.00, 5, NULL, 5),
(10, 'Julia Chen', 'Los Angeles', 5, 87000.00, 3, 9, 5),
(11, 'Kevin Brooks', 'Dallas', 6, 79000.00, 4, NULL, 6),
(12, 'Lina Torres', 'Phoenix', 7, 72000.00, 2, 11, 7),
(13, 'Mohammed Khan', 'Washington D.C.', 8, 95000.00, 6, NULL, 8),
(14, 'Nina Zhang', 'San Jose', 9, 98000.00, 7, NULL, 9),
(15, 'Oscar Rivera', 'Atlanta', 10, 87000.00, 5, NULL, 10),
(16, 'Priya Desai', 'Philadelphia', 11, 86000.00, 3, NULL, 11),
(17, 'Quinn Taylor', 'San Diego', 12, 74000.00, 2, NULL, 12),
(18, 'Rajiv Mehta', 'Orlando', 13, 81000.00, 4, NULL, 13),
(19, 'Sara Nasser', 'Minneapolis', 14, 83000.00, 5, NULL, 14),
(20, 'Tomoko Sato', 'Portland', 15, 76000.00, 3, NULL, 15),
(21, 'Umar Hassan', 'Las Vegas', 16, 79000.00, 4, NULL, 16),
(22, 'Valerie Cruz', 'Salt Lake City', 17, 88000.00, 6, NULL, 17),
(23, 'William Lee', 'Indianapolis', 18, 91000.00, 7, NULL, 18),
(24, 'Xinyi Zhao', 'Charlotte', 19, 94000.00, 8, NULL, 19),
(25, 'Yusuf Ali', 'Denver', 20, 87000.00, 5, NULL, 20);

-- Insert departments
INSERT INTO Department(ID, DepartmentName, MainCity, DepartmentManagerID) VALUES
(1, 'Engineering', 'New York', 1),
(2, 'Marketing', 'Chicago', 2),
(3, 'Finance', 'Miami', 3),
(4, 'Human Resources', 'Boston', 7),
(5, 'Product Development', 'Austin', 9),
(6, 'Sales', 'Dallas', 11),
(7, 'Customer Support', 'Phoenix', 12),
(8, 'Legal', 'Washington D.C.', 13),
(9, 'Research & Development', 'San Jose', 14),
(10, 'IT Services', 'Atlanta', 15),
(11, 'Operations', 'Philadelphia', 16),
(12, 'Quality Assurance', 'San Diego', 17),
(13, 'Public Relations', 'Orlando', 18),
(14, 'Procurement', 'Minneapolis', 19),
(15, 'Training & Development', 'Portland', 20),
(16, 'Security', 'Las Vegas', 21),
(17, 'Data Analytics', 'Salt Lake City', 22),
(18, 'Regulatory Affairs', 'Indianapolis', 23),
(19, 'Corporate Strategy', 'Charlotte', 24),
(20, 'Environmental Compliance', 'Denver', 25);

-- Insert projects
INSERT INTO Projects(ID, ProjectName, ProjectManagerID, DepartmentID, TimeRemaining) VALUES
(1, 'Apollo Platform', 1, 1, 120),
(2, 'Brand Boost', 2, 2, 90),
(3, 'Budget Tracker', 3, 3, 60),
(4, 'Talent Portal', 7, 4, 45),
(5, 'NextGen App', 9, 5, 150),
(6, 'Sales Accelerator', 11, 6, 75),
(7, 'Support Chatbot', 12, 7, 30),
(8, 'Compliance Tracker', 13, 8, 90),
(9, 'AI Research Hub', 14, 9, 180),
(10, 'Network Overhaul', 15, 10, 60),
(11, 'Logistics Optimizer', 16, 11, 120),
(12, 'QA Automation Suite', 17, 12, 45),
(13, 'Media Outreach', 18, 13, 50),
(14, 'Vendor Portal', 19, 14, 40),
(15, 'Training LMS', 20, 15, 70),
(16, 'Security Audit', 21, 16, 35),
(17, 'Data Warehouse', 22, 17, 150),
(18, 'Regulatory Dashboard', 23, 18, 90),
(19, 'Strategic Planner', 24, 19, 110),
(20, 'Eco Compliance Suite', 25, 20, 100);


SELECT 
    e.EmployeeName, 
    e.City, 
    p.ProjectName
FROM 
    Employee e
INNER JOIN 
    Projects p ON e.ProjectID = p.ID
WHERE 
    e.ProjectID IS NOT NULL
ORDER BY 
    p.ProjectName ASC, 
    e.EmployeeName ASC;