gistfile1.txt

create database healthcare;

\connect healthcare;

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 diethy', 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;

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;


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;

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;

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

select A.Drug, B.Drug from Prescription AS A CROSS JOIN (select * from Prescription) as B WHERE (
    A.PatientID=B.PatientID AND
    A.PrescNo!=B.PrescNo AND (
        A.IssueDate <= B.ExpDate AND
        B.IssueDate <= A.ExpDate AND
        A.ExpDate >= B.IssueDate AND
        B.ExpDate >= A.IssueDate
    )
);