CREATE TABLE Departments ( 
    DepartmentID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    DepartmentName VARCHAR(50) NOT NULL
    ); 

CREATE TABLE Employees ( 
    EmployeeID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    FirstName VARCHAR(50) NOT NULL, 
    LastName VARCHAR(50) NOT NULL, 
    DepartmentID INT NOT NULL, 
    Salary DECIMAL(10, 2) NOT NULL, 
    HireDate TIMESTAMP NOT NULL, 
    FOREIGN KEY(DepartmentID) REFERENCES Departments(DepartmentID)
    ); 

CREATE TABLE Sales ( 
    SaleID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    EmployeeID INT NOT NULL, 
    SalesAmount DECIMAL(10,2) NOT NULL, 
    SaleDate TIMESTAMP NOT NULL, 
    FOREIGN KEY(EmployeeID) REFERENCES Employees(EmployeeID)
    ); 

INSERT INTO Departments (DepartmentName) VALUES 
('Sales'), 
('Engineering'), 
('HR'), 
('Marketing'), 
('Fiance'); 

INSERT INTO Employees(FirstName, LastName, DepartmentID, Salary, HireDate) VALUES 

('Alice', 'Johnson', 1, 55000.00, '2023-06-15 09:00:00'),
('Bob', 'Smith', 2, 72000.00, '2024-01-10 10:30:00'),
('Carol', 'Davis', 3, 48000.00, '2025-03-22 14:45:00'),
('David', 'Lee', 1, 60000.00, '2023-08-01 08:15:00'),
('Eva', 'Martinez', 4, 51000.00, '2024-11-05 11:00:00'),
('Frank', 'Brown', 2, 75000.00, '2025-02-18 13:20:00'),
('Grace', 'Wilson', 5, 67000.00, '2023-12-12 16:00:00'),
('Henry', 'Taylor', 1, 58000.00, '2024-07-07 09:45:00');

INSERT INTO Sales(EmployeeID, SalesAmount, SaleDate) VALUES 

(1, 1200.50, '2023-07-15 14:30:00'),
(4, 2300.00, '2023-08-10 10:00:00'),
(1, 1800.75, '2024-01-05 15:45:00'),
(8, 950.00, '2024-07-20 09:30:00'),
(4, 3000.00, '2025-03-01 11:15:00'),
(1, 2100.00, '2025-05-22 13:00:00'),
(8, 1750.25, '2025-10-01 08:00:00');

-- Show the number of employees in each department 
SELECT COUNT(*) AS TotalNumberOfEmployees
FROM Employees; 

-- Find the average salary for each department 
SELECT AVG(Salary) AS AverageSalary 
FROM Employees; 

-- List Employees hired in the last 12 months 
SELECT * 
FROM Employees 
WHERE HireDate >= NOW() - INTERVAL 12 MONTH; 

-- Show the top 3 highest paid employees 
SELECT EmployeeID, FirstName, LastName, Salary 
FROM Employees 
ORDER BY Salary DESC 
LIMIT 3; 

-- List all employees in the engineering department 
SELECT EmployeeID, FirstName, LastName, Salary, HireDate
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentName = 'Engineering';


-- Show the total sales amount for each employee
SELECT EmployeeID, SUM(SalesAmount) AS TotalSales
FROM Sales 
GROUP BY EmployeeID
ORDER BY TotalSales DESC; 


-- Find the average sales amount per month 
SELECT 
    DATE_FORMAT(SaleDate, '%Y-%m') AS SaleMonth, 
    AVG(SalesAmount) AS AverageSalesAmount 
FROM Sales 
GROUP BY SaleMonth 
ORDER BY SaleMonth; 

-- Show the employee with the highest total sales 
SELECT EmployeeID , SUM(SalesAmount) AS HighestSales 
FROM Sales 
GROUP BY EmployeeID 
ORDER BY HighestSales DESC 
LIMIT 1; 

-- List all sales greater than $2000. 
SELECT* 
FROM Sales 
WHERE SalesAmount > 2000;

SELECT * 
FROM Sales 
WHERE SalesAmount > 100000;