A GRAFF A2.sql

CREATE DATABASE KHRV;

Use KHRV;

Create TABLE Location 
(Location_ID varchar(10) NOT NULL PRIMARY KEY,
Loc_Vacancy varchar(10));

INSERT INTO Location VALUES('BlockA', 'Occupied');
INSERT INTO Location VALUES('BlockB', 'Occupied');
INSERT INTO Location VALUES('BlockC', 'Occupied');
INSERT INTO Location VALUES('BlockD', 'Occupied');
INSERT INTO Location VALUES('BlockE', 'Unoccupied'); 


Create TABLE Staff
(Staff_ID int NOT NULL PRIMARY KEY,
Staff_FName varchar(20),
Staff_LName varchar(20),
Staff_DOB date,
Staff_JobTitle varchar(20), 
Staff_StartDate date,
Staff_Salary int,
Staff_Qual varchar(20),
Staff_DLicense varchar(10), 
Staff_TotalMerit int,
Staff_Contact int);

INSERT INTO Staff VALUES(6512, 'Martha', 'Stewart', '25-MAR-1994', 'Nurse', '20-OCT-2010', 58000, 'BN', 'YES', 120, 021609432);
INSERT INTO Staff VALUES(6023, 'John', 'Bowden', '27-JAN-1989', 'Carer', '20-OCT-2010', 45000, 'HWBL4', 'YES', 120, 027442211);
INSERT INTO Staff VALUES(6174, 'Stewart', 'Roger', '02-JUN-1990', 'Carer', '13-SEP-2019', 32000, 'HWBL4', 'YES', 120, 026321001);
INSERT INTO Staff VALUES(6231, 'Mary', 'Owens', '14-NOV-1950', 'Nurse', '12-NOV-2015', 75000, 'BN', 'NO', 50, 021500400);
INSERT INTO Staff VALUES(6001, 'Kate', 'Pierce', '17-JUL-1975', 'GP', '12-NOV-2015', 127000, 'MD', 'YES', 120, 027366951);


Create TABLE Resident 
(Resident_ID int NOT NULL PRIMARY KEY,
Res_Gender char(10) NOT NULL,
Res_FName varchar(20),
Res_LName varchar(20),
Res_DOB date,
Res_Contact char(8),
Res_Join date,
Res_Health varchar(20),
Location_ID varchar(10) Foreign Key References Location(Location_ID));

INSERT INTO Resident VALUES(7125, 'MALE', 'Jon', 'Jones','20-FEB-1971', 5241, '19-MAR-2015', 'Critical', 'BlockA');
INSERT INTO Resident VALUES(7595, 'MALE', 'Chael', 'Sonnen','5-DEC-1950', 5026, '10-FEB-2010', 'OK', 'BlockA');
INSERT INTO Resident VALUES(7310, 'FEMALE', 'Ronda', 'Rousey','24-APR-1969', 5024, '3-JULY-2020', 'OK', 'BlockB');
INSERT INTO Resident VALUES(7541, 'FEMALE', 'Katerina', 'Rowly','3-DEC-1940', 5113, '5-JULY-2011', 'Good', 'BlockB');
INSERT INTO Resident VALUES(7007, 'MALE', 'Michael', 'Jordan','10-SEP-1958', 5025, '7-MAY-2019', 'Moderate', 'BlockC');
INSERT INTO Resident VALUES(7124, 'MALE', 'Elon', 'Musk','09-NOV-1952', 5552, '1-JAN-2014', 'OK', 'BlockC');
INSERT INTO Resident VALUES(7782, 'FEMALE', 'Jennifer', 'Lawrence', '12-OCT-1965', 5962, '8-MAY-2020', 'Good', 'BlockC');
INSERT INTO Resident VALUES(7788, 'MALE', 'Dwayne', 'Johnson', '30-SEP-1974', 5688, '12-JUL-2023', 'Good', 'BlockD');
INSERT INTO Resident VALUES(7839, 'FEMALE', 'Sara', 'Saffari', '27-MAY-1960', 5801, '18-JUN-2020', 'Stable', 'BlockD');
INSERT INTO Resident VALUES(7844, 'FEMALE', 'Amanda', 'Nunes', '15-AUG-1961', 5831, '7-MAY-2019', 'OK', 'BlockD');

Create TABLE Contact 
(Contact_ID int NOT NULL PRIMARY KEY,
Contact_Type varchar(20),
Contact_Phone int,
Contact_Address varchar(50),
Resident_ID int Foreign Key References Resident(Resident_ID));

INSERT Contact VALUES(5241, 'Son', 021759331, '50 Plaza, Kenepuru', 7125); 
INSERT Contact VALUES(5026, 'Brother', 0254220011, '218 Brown Place, Porirua', 7595); 
INSERT Contact VALUES(5024, 'Aunt', 0214005212, '5 Campbell Street, Tawa', 7310); 
INSERT Contact VALUES(5113, 'Dad', 021440772, '70 Trophy Avenue, Aotea', 7541); 
INSERT Contact VALUES(5025, 'Sister', 0216001241, '124 Roger Terrace, Aotea', 7007); 
INSERT Contact VALUES(5552, 'Sister', 0217094082, '75 Victory Lane, Titahi Bay', 7124); 
INSERT Contact VALUES(5962, 'Sister-inlaw', 0216061241, '5 Trophy Avenue, Aotea', 7782); 
INSERT Contact VALUES(5688, 'Uncle', 0216617312, '127 Karori Street, Karori', 7788); 
INSERT Contact VALUES(5801, 'Daughter', 025002002, '1 Juliet Street, Titahi Bay', 7839); 
INSERT Contact VALUES(5831, 'Aunt', 0217502454, '12 Bellona Place, Porirua', 7844); 


Create TABLE AAI
(AAI_ID int NOT NULL PRIMARY KEY,
AAI_Date date, 
AAI_Time time,
AAI_AccidentorIncident varchar(100),
Location_ID varchar(10) Foreign Key References Location(Location_ID),
Resident_ID int Foreign Key References Resident(Resident_ID),
Staff_ID int Foreign Key References Staff(Staff_ID),
); 


INSERT INTO AAI VALUES(0001, '03-JUN-2023', '05:00:25', 'Accident', 'BlockA', 7125, 6512);
INSERT INTO AAI VALUES(0002, '14-APR-2023', '14:31:50', 'Accident', 'BlockC', 7839, 6512);
INSERT INTO AAI VALUES(0003, '05-MAR-2022', '07:30:10', 'Accident', 'BlockD', 7844, 6231);
INSERT INTO AAI VALUES(0004, '15-JUN-2023', '08:30:05', 'Incident', 'BlockA', 7125, 6512);
INSERT INTO AAI VALUES(0005, '03-JUN-2022', '13:02:01', 'Incident', 'BlockB', 7007, 6174);
INSERT INTO AAI VALUES(0006, '12-FEB-2022', '13:50:27', 'Incident', 'BlockA', 7310, 6023);
INSERT INTO AAI VALUES(0007, '31-OCT-2021', '12:01:40', 'Accident', 'BlockB', 7541, 6512);
INSERT INTO AAI VALUES(0008, '09-JUL-2023', '18:27:09', 'Accident', 'BlockA', 7595, 6174);
INSERT INTO AAI VALUES(0009, '03-AUG-2023', '06:00:21', 'Accident', 'BlockA', 7125, 6001);
INSERT INTO AAI VALUES(0010, '01-SEP-2023', '15:42:34', 'Accident', 'BlockB', 7124, 6001);


select * from Resident;
select * from Staff;
select * from Contact;
select * from Location;
select * from AAI; 

Select AAI_AccidentorIncident, Res_FName, Res_LName, Staff_FName, Staff_LName 
from AAI, Resident, Staff
where AAI.Resident_ID = Resident.Resident_ID and AAI.Staff_ID = Staff.Staff_ID and AAI_AccidentorIncident = 'Incident';

Select *
from Resident
Where Res_Gender = 'Female' and Res_DOB < '1958-01-01'; 

Select *
from AAI, Resident
where  AAI.Resident_ID = Resident.Resident_ID and AAI_AccidentorIncident = 'Accident';