dbs.sql

CREATE DATABASE HOSPITAL;
USE HOSPITAL;

CREATE TABLE PATIENT (
            Name VARCHAR(255) NOT NULL,
            ID INTEGER PRIMARY KEY,
            PhoneNo INT NOT NULL UNIQUE
        );

CREATE TABLE DOCTOR (
            Name VARCHAR(255) NOT NULL,
            ID INTEGER PRIMARY KEY,
            Dept VARCHAR(255) NOT NULL,
            PatNo INTEGER DEFAULT 0 NOT NULL 
        );

CREATE TABLE PATIENTAPPOINTMENTS (
            PatID INTEGER,
            DocID INTEGER,
            Symptoms VARCHAR(255),
            FOREIGN KEY (PatID) references PATIENT(ID),
            FOREIGN KEY (DocID) references DOCTOR(ID),
            PRIMARY KEY (PatID, DocID)
        );

CREATE TABLE MEDICALRECORDS (
            PatID INTEGER,
            DocID INTEGER,
            Ailments VARCHAR(255),
            Medication VARCHAR(255),
            Diagnosis VARCHAR(255),
            Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (PatID) references PATIENT(ID),
            FOREIGN KEY (DocID) references DOCTOR(ID),
            PRIMARY KEY (PatID, Date)
        );

DROP VIEW IF EXISTS PATIENT_VIEW
CREATE VIEW PATIENT_VIEW AS SELECT * FROM PATIENT;

DROP VIEW IF EXISTS DOCTOR_VIEW
CREATE VIEW DOCTOR_VIEW AS SELECT * FROM DOCTOR;

DROP VIEW IF EXISTS APPOINTMENT_VIEW
CREATE VIEW APPOINTMENT_VIEW AS SELECT * FROM PATIENTAPPOINTMENTS;

DROP VIEW IF EXISTS MEDICALRECORD_VIEW
CREATE VIEW MEDICALRECORD_VIEW AS SELECT * FROM MEDICALRECORDS;
    
INSERT INTO DOCTOR (Name, Dept) VALUES ('Mohan', 'Cardiology');
INSERT INTO DOCTOR (Name, Dept) VALUES ('Suresh', 'Physiology');
INSERT INTO PATIENT(Name, PhoneNo) VALUES(?, ?);
INSERT INTO PATIENTAPPOINTMENTS(PatID, DocID, Symptoms) VALUES (?, ?, ?)
INSERT INTO MEDICALRECORDS(PatID, DocID, Ailments, Medication, Diagnosis) VALUES (?, ?, ?, ?, ?)

Select * from PATIENT_VIEW;
Select * from DOCTOR_VIEW;
Select * from DOCTOR;
SELECT * FROM PATIENT ORDER BY ID DESC LIMIT 1;
SELECT * FROM DOCTOR where (PatNo <= 5 AND PatNo <> 0) order by ID DESC LIMIT 1;
Select * from APPOINTMENT_VIEW;
Select * from MEDICALRECORD_VIEW;
Select * from MEDICALRECORDS where PatID = (SELECT ID FROM PATIENT WHERE PhoneNo = ?);
Select * from DOCTOR WHERE ID = (Select DocID from PATIENTAPPOINTMENTS as pt, PATIENT as p where (p.ID = pt.PatID AND p.PhoneNo = ?));            

UPDATE DOCTOR SET PatNo = ? WHERE ID = (Select DocID from PATIENTAPPOINTMENTS as pt, PATIENT as p where (p.ID = pt.PatID AND p.PhoneNo = ?))
UPDATE DOCTOR SET PatNo = ? where ID = ?;
UPDATE MEDICALRECORDS SET DATE = ? WHERE PatID = (SELECT ID FROM PATIENT WHERE PhoneNo = ?);

DELETE FROM PATIENT WHERE PhoneNo = ?

-- commented out the code to drop tables

/* 

DROP TABLE IF EXISTS DOCTOR
DROP TABLE IF EXISTS PATIENTAPPOINTMENTS
DROP TABLE IF EXISTS MEDICALRECORDS
DROP VIEW IF EXISTS PATIENT_VIEW
DROP VIEW IF EXISTS DOCTOR_VIEW
DROP VIEW IF EXISTS APPOINTMENT_VIEW
DROP VIEW IF EXISTS MEDICALRECORD_VIEW
DROP TABLE IF EXISTS PATIENT
DROP DATABASE IF EXISTS HOSPITAL
*/