INFO125: Obligatory assignment 2–SQL

info125-oblig2-sql.sql

create database healthcare;

/* In PostgreSQL, we connect to it using the \connect healthcare; command */

create table Doctor (
    Name VARCHAR(20) NOT NULL,
    Doctorssn INT PRIMARY KEY NOT NULL,
    Location VARCHAR
);

insert into Doctor (Name, Doctorssn, Location) values
    ('Joe Kerwin', 001, 'Boston'),
    ('Jack Lousma', 104, 'San Fransisco'),
    ('Jim Lovell', 321, 'San Almos'),
    ('Deke Slayton', 533, 'Kentucky'),
    ('Fred Haise', 634, 'Maryland'),
    ('Jack Swigert', 995, 'Miami'),
    ('Charlie Duke', 923, 'New York'),
    ('Alfred Young', 925, 'Bergen')
;

create table Patient (
    SSN INT PRIMARY KEY NOT NULL,
    Name VARCHAR(20) NOT NULL,
    GP INT NOT NULL,
    RecordNo INT NOT NULL,
    FOREIGN KEY (GP) REFERENCES Doctor(Doctorssn)
);

insert into Patient (SSN, Name, GP, RecordNo) values
    (221, 'Neil McCauley', 001, 001),
    (251, 'Chris Shiherlis', 995, 002),
    (522, 'Lauren Gustafson', 634, 003),
    (866, 'Michael Cheritto', 923, 004),
    (877, 'Charlene Shiherlis', 533, 005),
    (836, 'Roger Van Zant', 321, 006),
    (878, 'Vincent Hannah', 001, 007),
    (939, 'Josiaha Bartlet', 001, 008),
    (935, 'Samuel Seaborn', 104, 009),
    (945, 'Tobias Ziegler', 533, 010),
    (975, 'Joey Lucas', 995, 011),
    (998, 'Josh Lyman', 923, 012),
    (989, 'Claudia Jean Cregg', 634, 013),
    (903, 'Leo McGarry', 321, 014)
;

create table Manufacturer (
    CompanyNo INT PRIMARY KEY NOT NULL,
    Name VARCHAR(20) NOT NULL,
    CEOName VARCHAR(20) NOT NULL,
    CEOssn INT NOT NULL,
    Location VARCHAR,
    FOREIGN KEY (CEOssn) REFERENCES Patient(SSN)
);

insert into Manufacturer (CompanyNo, Name, CEOName, CEOssn) values
    (001, 'Weifa', 'Neil McCauley', 221),
    (002, 'MSD', 'Chris Shiherlis', 251),
    (003, 'Merck', 'Lauren Gustafson', 522),
    (004, 'Sanus', 'Charlene Shiherlis', 877),
    (005, 'GlaxoSmithKlein', 'Vincent Hannah', 878),
    (006, 'McNeil', 'Michael Cheritto', 866),
    (007, 'Pfizer', 'Roger Van Zant', 836)
;

create table Drug (
    Name VARCHAR(20) NOT NULL,
    PatentNo INT PRIMARY KEY NOT NULL,
    Manufacturer INT NOT NULL,
    GenericName VARCHAR(20) NOT NULL,
    Cost INT,
    FOREIGN KEY (Manufacturer) REFERENCES Manufacturer(CompanyNo)
);

insert into Drug (Name, PatentNo, Manufacturer, GenericName, Cost) values
    ('Nasonex', 473, 001, 'Mometasone', 80),
    ('Livostin', 014, 002, 'Levokabastin', 215),
    ('Aerius', 030, 003, 'Desloratadin', 49),
    ('Paracet', 575, 004, 'Paracetamol', 46),
    ('Smertestillende', 594, 001, 'Paracetamol', 96),
    ('Glucophage', 374, 005, 'Metformin', 67),
    ('Glucophage Salve', 376, 005, 'Metformin', 37),
    ('Xylocain', 153, 006, 'Lidokain', 144),
    ('Flutide Nasal', 133, 007, 'Flutikason', 134),
    ('Nasonex Forte', 475, 001, 'Mometasone', 180),
    ('Livostin Forte', 016, 002, 'Levokabastin', 315),
    ('Aerius Forte', 031, 003, 'Desloratadin', 149),
    ('Paracet Forte', 572, 004, 'Paracetamol', 146),
    ('Paracet Piano', 576, 004, 'Metformin', 29),
    ('Glucophage Forte', 375, 005, 'Metformin', 167),
    ('Xylocain Forte', 151, 006, 'Lidokain', 244),
    ('Xylocain Salve', 154, 006, 'Lidokain', 99),
    ('Flutide Nasal Forte', 132, 007, 'Flutikason', 432),
    ('LSD', 666, 004, 'Lysergic acid', 1000)
;

create table Prescription (
    PrescNo INT PRIMARY KEY NOT NULL,
    DoctorID INT NOT NULL,
    PatientID INT NOT NULL,
    IssueDate INT NOT NULL,
    ExpDate INT,
    Collected BOOLEAN NOT NULL,
    Drug INT NOT NULL,
    FOREIGN KEY (Drug) REFERENCES Drug(PatentNo),
    FOREIGN KEY (DoctorID) REFERENCES Doctor(Doctorssn),
    FOREIGN KEY (PatientID) REFERENCES Patient(SSN)
);

insert into
    Prescription
    (PrescNo, DoctorId, PatientID, IssueDate, ExpDate, Collected, Drug) values
    (001, 001, 221, 20010512, 20030313, TRUE, 473),
    (002, 104, 522, 20030631, 20050613, TRUE, 014),
    (003, 321, 866, 20150315, 20150318, FALSE, 030),
    (004, 321, 866, 20000101, 20150711, FALSE, 030),
    (005, 533, 836, 20140122, NULL, FALSE, 575),
    (006, 923, 878, 20151110, 20160101, FALSE, 374),
    (007, 923, 878, 20151110, 20160101, TRUE, 374),
    (008, 321, 878, 20151110, NULL, FALSE, 153),
    (009, 634, 878, 20151110, 20160101, FALSE, 153),
    (010, 995, 866, 20150315, NULL, FALSE, 133),
    (011, 001, 866, 20150315, 20160101, FALSE, 133),
    (012, 925, 989, 20150315, 20160101, TRUE, 666),

    (013, 925, 903, 20150215, 20160214, FALSE, 575),
    (014, 925, 903, 20150315, 20160101, FALSE, 594)
;

CREATE DOMAIN SIDATE AS INT CHECK (
    (value / 10000) >= 2000 AND
    ((value % 10000) / 100 BETWEEN 1 AND 12) AND
    value % 100 BETWEEN 1 AND 31
);

ALTER TABLE Prescription ALTER COLUMN IssueDate TYPE SIDATE;

ALTER TABLE Prescription ALTER COLUMN ExpDate TYPE SIDATE;


ALTER TABLE Prescription ADD CONSTRAINT BadDoctor CHECK (
    DoctorID != PatientID
);
ALTER TABLE Prescription ADD FOREIGN KEY (DoctorID) REFERENCES Doctor(Doctorssn) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Prescription ADD FOREIGN KEY (PatientID) REFERENCES Patient(SSN) ON DELETE CASCADE ON UPDATE CASCADE;

/* Find the names of the doctors of the CEOs of Manufacturer. Order them in an ascending order. */

SELECT DISTINCT Name
FROM Doctor
INNER JOIN (
    SELECT GP
    FROM Manufacturer
    INNER JOIN Patient
    ON CEOSsn=SSN
)
AS result
ON Doctorssn=GP
ORDER BY Name ASC;

/* For each manufacturer, find the most expensive and the cheapest drugs they manufacture and their names. */

SELECT m.name AS Company, labeledmaxmin.name AS Drug
FROM (
    SELECT d.manufacturer, name
    FROM (
        SELECT manufacturer, MAX(cost) AS maxcost, MIN(cost) AS mincost
        FROM drug
        GROUP BY manufacturer
    ) AS maxmin
    LEFT JOIN drug AS d
    ON maxmin.manufacturer=d.manufacturer AND (maxmin.mincost=d.cost OR maxmin.maxcost=d.cost)
) AS labeledmaxmin
CROSS JOIN manufacturer AS m WHERE labeledmaxmin.manufacturer=m.companyno ORDER BY m.name;

/* Find the RecordNo of the patients who have been prescribed a medicine by a doctor whose name starts with an ``A'' and who have collected the prescription. */

SELECT RecordNo
FROM Patient
INNER JOIN (
    Prescription INNER JOIN (
        SELECT Doctorssn FROM Doctor WHERE Name like 'A%'
    ) AS ADoctors
    ON (
        Prescription.doctorid=ADoctors.Doctorssn AND prescription.collected
    )
) AS desired_prescriptions ON Patient.SSN=desired_prescriptions.PatientID;

/* Find the names of doctors who have made prescriptions that never expire. */

SELECT Name 
FROM Doctor
INNER JOIN (
    SELECT DoctorID
    FROM Prescription
    WHERE ExpDate IS NULL
) AS ForeverPrescriptions
ON Doctor.Doctorssn=ForeverPrescriptions.DoctorID;

/* Create a general constraint using ASSERTION that one patient is not prescribed two different drugs with the same generic name within an overlapping period of time. */

CREATE ASSERTION no_two_generics
CHECK (0 = (
    SELECT Count(*) FROM (
        SELECT p1.patientid AS patient1, p1.prescno AS prescno1, p1.drug AS drug1, p1.issuedate AS issuedate1, p1.expdate AS expdate1, p2.patientid AS patient2, p2.prescno AS prescno2, p2.drug AS drug2, p2.issuedate AS issuedate2, p2.expdate AS expdate2
        FROM prescription p1
        CROSS JOIN prescription p2
    ) AS all_prescs
    LEFT JOIN (
        SELECT genericname AS generic1, patentno AS patentno1
        FROM drug
    ) AS generics1
    ON all_prescs.drug1=generics1.patentno1
    LEFT JOIN (
        SELECT genericname AS generic2, patentno AS patentno2
        FROM drug
    ) AS generics2
    ON all_prescs.drug2=generics2.patentno2
    WHERE (
        patient1 = patient2 AND
        (
            (issuedate1 BETWEEN issuedate2 AND expdate2) OR
            (issuedate1 >= issuedate2 AND expdate2 IS null) OR
            (issuedate1 >= issuedate2 AND expdate1 IS null)
        ) AND
        prescno1 != prescno2 AND
        patentno1 != patentno2 AND
        generic1 = generic2
    )
));