20181CSE0041, Sql Server

20181CSE0041

CREATE TABLE Patient(PatientID INT,Name VARCHAR(20),DOB date,Phone INT ,Address VARCHAR(20),PRIMARY KEY(PatientID));
CREATE TABLE Doctor(DoctorID INT,Name VARCHAR(20),Phone INT ,Age INT,Address VARCHAR(20),PRIMARY KEY(DoctorID));
CREATE TABLE Medicine(MedicineID INT,BrandName VARCHAR(20),Price INT ,Quantity INT,ExpireDate DATE,PRIMARY KEY(MedicineID));
CREATE TABLE Prescription (PrescriptionID INT,PDate date,PatientID INT, PrescriberID INT, MedicineID INT,PRIMARY KEY(PrescriptionID),FOREIGN KEY(PatientID)REFERENCES Patient(PatientID) ON DELETE CASCADE,FOREIGN KEY(PrescriberID)REFERENCES Doctor(DoctorID) ON DELETE CASCADE,FOREIGN KEY(MedicineID)REFERENCES Medicine(MedicineID) ON DELETE CASCADE);
insert into Patient values(100,'akshy','2000-07-19',1234567890,'Bangalore');
insert into Patient values(101,'prasanth','2000-08-10',1234567880,'Mysore');
insert into Patient values(102,'akshath','2001-09-12',1234567790,'Kollam');
insert into Patient values(103,'aman','1996-07-11',1234566890,'Chennai');
insert into Patient values(104,'hamza','2000-02-12',1234557890,'Mumbai');
select * from Patient;
insert into Doctor values(200,'jagjeet',1234567899,37,'Bangalore');
insert into Doctor values(201,'pavan',1234567889,39,'Mysore');
insert into Doctor values(202,'darshan',1234568899,34,'Kollam');
insert into Doctor values(203,'goutham',1234557899,40,'Chennai');
insert into Doctor values(204,'arif',1233567899,41,'Mumbai');
select * from Doctor;
insert into Medicine values(300,'Paracetamol',100,5,'2020-12-31');
insert into Medicine values(301,'Acetaminophen',150,4,'2020-12-31');
insert into Medicine values(302,'Ativan',110,3,'2020-12-29');
insert into Medicine values(303,'Amlodipine',90,2,'2020-12-28');
insert into Medicine values(304,'Amoxicillin',80,1,'2020-12-13');
select * from Medicine;
insert into Prescription values(400,'2020-03-10',100,200,300);
insert into Prescription values(401,'2020-03-11',101,201,301);
insert into Prescription values(402,'2020-03-12',102,201,302);
insert into Prescription values(403,'2020-03-13',103,202,303);
insert into Prescription values(404,'2020-03-14',104,203,304);
select * from Prescription;
select Name from Patient where Address='Bangalore' or Address='Mysore';
SELECT DoctorID,NAME FROM Doctor WHERE DoctorID IN(SELECT P.PrescriberID FROM Doctor AS D, Prescription AS P WHERE P.PrescriberID = D.DoctorID GROUP BY P.PrescriberID HAVING COUNT(*)>1);
select  * from Medicine where ExpireDate>'2020-12-30' order by ExpireDate desc;
select count(*) as no_of_Patient from Prescription as p, Medicine as m, Patient as t where t.PatientID=p.PatientID and p.MedicineID=m.MedicineID and m.BrandName='Paracetamol';
delete from Patient  where PatientID=100;
select * from Patient;
select * from Prescription;


run \| edit \| history \| help	0