CREATE TABLE Hospitals ( 
    HospitalID INT CHECK (HospitalID> 0), -- Positive Integer
    HospitalName VARCHAR(100), 
    EstablishedDate DATE, 
    AnnualBudget DECIMAL(8,2) CHECK (AnnualBudget>0), -- Positive decimal up to 999999.99(8), two decimal places
    Type CHAR(2), -- fixed length
    Director VARCHAR(50)
    ); 

CREATE TABLE Departments ( 
    DepartmentID INT CHECK (DepartmentID> 0),
    HospitalID INT CHECK (HospitalID > 0),
    DepartmentName VARCHAR(50),
    StaffCount INT CHECK(StaffCount> 0), 
    OperatingCost DECIMAL (8,2)
    );

INSERT INTO Hospitals (HospitalID, HospitalName, EstablishedDate, AnnualBudget, Type, Director)VALUES
(1, 'Green Valley Hospital', '1995-06-15', 750000.00, 'GH', 'Dr. Adams'),
(2, 'Riverbend Medical Center', '2001-09-20', 620000.00, 'CH', 'Dr. Lee'),
(3, 'Sunrise Health Institute', '2010-03-10', 480000.00, 'PH', 'Dr. Patel');

INSERT INTO Departments (DepartmentID, HospitalID, DepartmentName, StaffCount, OperatingCost)VALUES
(101, 1, 'Cardiology', 25, 200000.00),
(102, 1, 'Neurology', 20, 180000.00),
(103, 1, 'Pediatrics', 15, 150000.00),
(104, 2, 'Orthopedics', 18, 220000.00),
(105, 2, 'Emergency', 30, 190000.00),
(106, 3, 'Dermatology', 10, 120000.00);

-- Show each hospital’s name and total number of departments. 

SELECT  h.HospitalName, 
COUNT(d.DepartmentID) AS TotalDepartments 
FROM Hospitals h 

JOIN Departments d ON h.HospitalID = d.HospitalID 

GROUP BY 
h.HospitalName; 


-- - List hospitals with a combined department operating cost over $500,000.
SELECT h.HospitalName, 
SUM(d.OperatingCost) AS TotalOperatingCost

FROM Hospitals h 

JOIN Departments d ON h.HospitalID = d.HospitalID 

GROUP BY h.HospitalName
HAVING SUM(d.OperatingCost) > 500000; 

-- Find the average staff count per hospital
SELECT h.HospitalName,
    AVG(d.StaffCount) AS AverageStaffCount
FROM Hospitals h 

JOIN Departments d ON h.HospitalID = d.HospitalID 

GROUP BY h.HospitalName;