SQL COMMANDS - JOIN TABLES

sqlJOIN.sql

-- Student name: Monali Kamleshbhai Suthar
-- Date: 13/10/2017
-- Statement of authourship: I, Monali Kamleshbhai Suthar, 000740485, certify that this material is my original work.No other person's work has been used without due acknowledgement 
-- and I have not made my work available to anyone else.

-- Group : 1 Basic Queries --
PRINT  'GROUP 1 SELECT  B'
SELECT COUNT(PatientID) FROM Patients WHERE  Gender='M' AND PatientWeight < (SELECT AVG(PatientWeight) FROM Patients WHERE Gender='F');
PRINT  'GROUP 1 SELECT  C'
SELECT FirstName,LastName,PatientHeight FROM Patients WHERE PatientHeight = (SELECT MAX(PatientHeight)  FROM Patients WHERE Gender='F');
PRINT  'GROUP 1 SELECT  D'
SELECT FirstName,LastName,Allergies,PatientWeight FROM Patients WHERE ProvinceID not like '%ON%' AND Allergies IS NULL AND  PatientWeight <75;


-- Group : 2 Sub totals using GROUP BY --
PRINT  'GROUP 2 SELECT  B'
SELECT COUNT(PatientID)  FROM Admissions WHERE DischargeDate IS NULL GROUP BY AttendingPhysicianID;
PRINT  'GROUP 2 SELECT  C'
SELECT COUNT(PatientID)  FROM Patients WHERE ProvinceID not like '%ON%' GROUP BY ProvinceID;
PRINT  'GROUP 2 SELECT  D'
SELECT COUNT(PatientID)  FROM Patients WHERE PatientHeight >175 GROUP BY Gender;
 

-- Group : 3 Two table JOINS --
PRINT  'GROUP 3 SELECT  A'
SELECT  Patients.PatientID,Patients.FirstName,Patients.LastName,Admissions.Room,Admissions.Bed FROM Patients INNER JOIN Admissions ON Patients.PatientID = Admissions.PatientID  WHERE (Admissions.DischargeDate IS NULL AND Admissions.NursingUnitID = '2SOUTH') ORDER BY Patients.LastName;
PRINT  'GROUP 3 SELECT  D'
SELECT Departments.DepartmentID,Departments.DepartmentName,Departments.ManagerFirstName,Departments.ManagerLastName,PurchaseOrders.PurchaseOrderID,PurchaseOrders.TotalAmount FROM Departments INNER JOIN PurchaseOrders ON Departments.DepartmentID = PurchaseOrders.DepartmentID WHERE (PurchaseOrders.TotalAmount >= 1500.00) ORDER BY DepartmentID;

-- Group : 4 Three table JOINS --
PRINT  'GROUP 4 SELECT  A'
SELECT Physicians.PhysicianID,Physicians.FirstName,Physicians.LastName,Patients.PatientID FROM Physicians
 INNER JOIN Admissions ON Physicians.PhysicianID = Admissions.AttendingPhysicianID 
 INNER JOIN Patients ON Patients.PatientID = Admissions.PatientID 
 WHERE  Patients.FirstName='Harry' AND Patients.LastName='Sullivan';
PRINT  'GROUP 4 SELECT  B'
SELECT Patients.PatientID,Patients.FirstName,Patients.LastName, Admissions.NursingUnitID,Admissions.PrimaryDiagnosis FROM Patients 
 INNER JOIN  Admissions ON  Patients.PatientID = Admissions.PatientID 
 INNER JOIN  Physicians ON Physicians.PhysicianID = Admissions.AttendingPhysicianID
 WHERE Admissions.DischargeDate IS NULL AND Physicians.Specialty ='Internist';

-- Group : 5 Four table JOINS -- 
PRINT  'GROUP 5 SELECT  A'
SELECT  Physicians.FirstName AS PhysiciansFirstName,Physicians.LastName AS PhysiciansLastName,Physicians.Specialty,
 NursingUnits.NursingUnitID,
 Patients.FirstName AS PatientsFirstName,Patients.LastName AS PatientsLastName,
 Admissions.PrimaryDiagnosis FROM Physicians
 INNER JOIN Admissions ON AdmissionS.AttendingPhysicianID = Physicians.PhysicianID
 INNER JOIN NursingUnits ON NursingUnits.NursingUnitID = Admissions.NursingUnitID
 INNER JOIN Patients ON Patients.PatientID = AdmissionS.PatientID
 WHERE Admissions.DischargeDate IS NULL AND Admissions.SecondaryDiagnoses IS NULL;

PRINT  'GROUP 5 SELECT  B'
SELECT Patients.FirstName,Patients.LastName,Admissions.NursingUnitID,Admissions.Room, Medications.MedicationDescription  FROM Patients
 INNER JOIN Admissions ON Admissions.PatientID = Patients.PatientID
 INNER JOIN UnitDoseOrders ON UnitDoseOrders.PatientID = Patients.PatientID
 INNER JOIN Medications ON Medications.MedicationID = UnitDoseOrders.MedicationID
 WHERE Admissions.DischargeDate IS NULL AND Patients.Allergies ='Penicillin';

-- Group : 6 WHERE [NOT] EXISTS --
PRINT  'GROUP 6 SELECT  A'
SELECT Patients.PatientID,Admissions.PrimaryDiagnosis,Admissions.AttendingPhysicianID
 FROM Patients
 INNER JOIN Admissions ON Admissions.PatientID = Patients.PatientID
 WHERE Admissions.DischargeDate IS NULL AND Patients.PatientID NOT IN (SELECT Encounters.PatientID FROM Encounters);
PRINT  'GROUP 6 SELECT  B'
SELECT PurchaseOrders.PurchaseOrderID, PurchaseOrders.OrderDate, Departments.DepartmentID 
 FROM PurchaseOrders
 INNER JOIN Departments ON Departments.DepartmentID = PurchaseOrders.DepartmentID
 WHERE PurchaseOrders.PurchaseOrderID NOT IN (SELECT PurchaseOrderID FROM PurchaseOrderLines);