-- creating the clinic DataBase
CREATE DATABASE clinic;
USE clinic;
-- creating the doctor table with the doctor _id as a primary key
CREATE TABLE doctor(
doctor_id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(15),
specilization VARCHAR(15),
phone VARCHAR(10),
gender VARCHAR(10),
PRIMARY KEY (doctor_id)
);
-- creating the patient table with the patient_id as a primary key
CREATE TABLE patient(
patient_id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(15),
age INT,
gender VARCHAR(10),
weight INT,
cinic VARCHAR(10),
PRIMARY KEY (patient_id)
);
-- creating the apointment table with the id int as a primary key
CREATE TABLE appointments(
id INT NOT NULL AUTO_INCREMENT,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id)
);
-- creating the tests table with the test_id as a primary key
CREATE TABLE tests(
test_id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10),
duration VARCHAR(10),
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
PRIMARY KEY (test_id),
FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id)
);
-- creating the labortory table with the id int as a primary key
CREATE TABLE labortory(
id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10),
timing VARCHAR(15),
PRIMARY KEY (id)
);
-- creating the mediciones table with the medicines_id as a primary key
CREATE TABLE medicines(
medicine_id INT NOT NULL AUTO_INCREMENT,
medicine_name VARCHAR(10),
prescription VARCHAR(10),
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
PRIMARY KEY (medicine_id),
FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id)
);
-- creating the bill table with the bill_id as a primary key
CREATE TABLE bill(
bill_id INT NOT NULL AUTO_INCREMENT,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
bill_date DATETIME,
PRIMARY KEY (bill_id),
FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id)
);
-- ADDING total in bill using alter
ALTER TABLE bill ADD total INT;
-- Inserting a value In doctor,patient,medicines,bill
INSERT INTO doctor (`name`, specilization, phone, gender) VALUES('ibn alhajam','cardiologist','+931122','MALE');
INSERT INTO patient (`name`, age, gender, weight, cinic) VALUES('ali',20,'M',50,'34501699');
INSERT INTO medicines (medicine_name, prescription, patient_id, doctor_id) VALUES('leflox','2 a day',1,1);
INSERT INTO bill (patient_id, doctor_id, bill_date, total) VALUES(1,1,'2020-06-12',5000);
SELECT * FROM doctor;
SELECT * FROM patient;
SELECT * FROM medicines;
SELECT * FROM bill;
-- Displaying values from doctor where name is ALI
SELECT * FROM patient WHERE `name`='ali';
-- counting total number of patients
SELECT COUNT(patient_id) FROM patient;
-- Displaying values from doctor where name ends in a
SELECT * FROM doctor WHERE `name` LIKE '%a';
-- Displaying values of patient along with their medicines
SELECT m.medicine_name,m.prescription
FROM patient p
JOIN medicines m ON m.patient_id=p.patient_id
WHERE p.`name`='ali';
-- Displaying values of patient along with their medicines and total bill
SELECT m.medicine_name,m.prescription,b.total,p.`name`
FROM patient p
JOIN medicines m ON m.patient_id=p.patient_id
JOIN bill b ON b.patient_id=p.patient_id
WHERE p.`name`='ali';
-- Creating Views
CREATE VIEW disp AS
SELECT m.medicine_name,m.prescription
FROM patient p
JOIN medicines m ON m.patient_id=p.patient_id
WHERE p.`name`='ali';
CREATE VIEW display2 AS
SELECT m.medicine_name,m.prescription,b.total,p.`name`
FROM patient p
JOIN medicines m ON m.patient_id=p.patient_id
JOIN bill b ON b.patient_id=p.patient_id
WHERE p.`name`='ali';
-- CREATNG ROLES FOR THE VIEWS
CREATE ROLE IF NOT EXISTS admin1;
GRANT CREATE ON clinic.* TO admin1;
GRANT CREATE VIEW ON clinic.* TO admin1;
CREATE role IF NOT EXISTS admin2;
GRANT ALTER ON clinic.* TO admin2;