Cluster package assessment work
create database Hospital1
use Hospital1
create table Hospital(Hospid varchar(6) primary key,Hospname varchar(88) not
null,
region varchar(50),city varchar(30))
create table patient(Patid varchar(10) primary key check(patid like 'pat___'),
pfname varchar(30) not null,
pmname varchar(30),plname varchar(30),sex varchar(1),town char(88),
diagnosis varchar(77),
Hospid varchar(6) foreign key references Hospital(Hospid) on delete cascade on
update cascade)
create table medical_record(Recordid varchar(10) primary key,date_of_exam
DATE,
problem varchar(100),Patid varchar(10) foreign key references patient(patid)
on update cascade on delete cascade)
create table Doctor(Docid varchar(10) primary key,Dfname varchar(67),
Dmname varchar(30),sex varchar(1),Qualification varchar(20),salary money,
Hospid varchar(6) foreign key references Hospital(Hospid) on delete cascade on
update cascade)
create table Room(RoomNo varchar(10),BedNo varchar(77),
Roomtype varchar(66),
dateoccupied datetime,
Patid varchar(10) foreign key references patient(patid) on delete cascade on
update cascade,
primary key(RoomNo,BedNo))
select * from Hospital
insert into Hospital values('GH01','Adama general hospital','oromia','adama')
insert into Hospital values('RH01','Adama Medical college','oromia','adama')
insert into Hospital values('RH02','Nekemte referal
Hospital','oromia','nekemte')
insert into Hospital values('GH02','Rift valley general
hospital','finfinne','finfinne')
insert into Hospital values('RH03','Dire Dawa referal','Dire dawa','Dire
dawa')
insert into Hospital values('RH04','Black lion','finfinne','finfinne')
select * from patient
insert into patient
values('pat001','yared','girma','itafa','m','adama','blood','RH01')
insert into patient values('pat002','tufa','wayu','tolera','m','adama','urine
test','GH01')
insert into patient values('pat003','seada','muhe','tola','f','adama','CT
scan','GH01')
insert into patient values('pat004','Ebise','taka','alemu','f','finfine','CT
scan','GH02')
insert into patient
values('pat005','sena','Adugna','geleta','f','nekemte','ultrasound ','RH02')
insert into patient
values('pat006','seifu','kedir','umer','m','finfine','urine and blood
','RH04')
insert into patient values('pat007','sumeya','Tolcha','chala','f','dire
dewa','x-ray ','RH03')
select * from medical_record
insert into medical_record values('rec01','2021-12-05','diabate','pat001')
insert into medical_record values('rec02','1/18/2022','diabate','pat002')
insert into medical_record values('rec03','1/20/2022','insulin','pat003')
insert into medical_record values('rec04','12/5/2021','gastric','pat004')
insert into medical_record values('rec05','2/2/2022','hypert','pat005')
select * from Doctor
insert into Doctor values('SDr001','marta','moti','f','dr of
surgery',50000,'RH01')
insert into Doctor values('DDr001','meron','motuma','f','dr of
daibate',40000,'GH01')
insert into Doctor values('CDr001','monera','sanyi','m','dr of
eye',450000,'RH02')
insert into Doctor values('SDr002','simbo','gudata','f','dr of eye
surgery',50000,'GH02')
insert into Doctor
values('IDr002','Hacalu','Bekema','m','Internist',50000,'RH03')
insert into Doctor
values('IDr001','milki','olana','m','Internist',50000,'RH04')
insert into Doctor values('IDr005','Dinsa','Bona','m','dr of eye
surgery',50000,'RH02')
select * from Room
insert into Room values('R01','B01','Emergency','12/5/2021','pat001')
insert into Room values('R02','B03','Emergency','1/18/2022','pat002')
insert into Room values('R03','B02','Delivery','1/20/2022','pat003')
insert into Room values('R04','B04','OPD','2/2/2022','pat004')
insert into Room values('R05','B05','OPD','2/12/2022','pat005')
SELECT patient.pfname, medical_record.problem, Doctor.Dfname,
Hospital.Hospname
FROM Doctor INNER JOIN
Hospital ON Doctor.Hospid = Hospital.Hospid INNER JOIN
patient ON Hospital.Hospid = patient.Hospid INNER JOIN
medical_record ON patient.Patid = medical_record.Patid
WHERE (medical_record.problem = 'diabate') AND (Hospital.Hospname = 'Adama
general hospital')
SELECT Doctor.Dfname+' '+Doctor.Dmname as'fullname',
Doctor.Qualification, Room.RoomNo, Hospital.Hospname
FROM Doctor INNER JOIN
Hospital ON Doctor.Hospid = Hospital.Hospid INNER JOIN
patient ON Hospital.Hospid = patient.Hospid INNER JOIN
Room ON patient.Patid = Room.Patid
WHERE Room.RoomNo in('R03','R04','R05') AND (Hospital.Hospname = 'Rift
valley general hospital')
SELECT Hospital.Hospname, count(Room.BedNo), Hospital.region
FROM Hospital INNER JOIN
patient ON Hospital.Hospid = patient.Hospid INNER JOIN
Room ON patient.Patid = Room.Patid group by
Hospital.Hospname,Hospital.region
SELECT patient.Patid, patient.pfname+' '+ patient.pmname as full_name,
patient.sex, patient.diagnosis, Hospital.Hospname, medical_record.date_of_exam
FROM patient INNER JOIN
medical_record ON patient.Patid = medical_record.Patid
INNER JOIN
Hospital ON patient.Hospid = Hospital.Hospid where
medical_record.date_of_exam between '1/1/2022' and '1/30/2022'
update Doctor set salary=salary+salary*0.5
where Qualification ='dr of surgery' and Hospid in(select hospid from Hospital
where Hospname ='Nekemte referal Hospital')
SELECT COUNT (Dfname) as noofdr, Qualification
FROM Doctor group by Qualification
SELECT Doctor.Docid, Doctor.Dfname, Doctor.Qualification, Doctor.sex,
Hospital.city
FROM Doctor INNER JOIN
Hospital ON Doctor.Hospid = Hospital.Hospid
WHERE (Doctor.sex = 'f') AND (Hospital.city = 'adama')
Institution package assessment work
create database super_market
use super_market
create table Sale_persons(sid varchar(77) primary key,fname varchar(33),mname
char(44),lname varchar(12),
sex char(33),DoB datetime,occupation varchar(22),salary money,hired_date
datetime,phone int)
create table login(username varchar(77),password char(32),privilege
varchar(55),usedid char(22) primary key,
sid varchar(77) foreign key references Sale_persons (sid) on delete cascade on
update cascade)
create table customer(cid char(22) primary key,cfname char(22),Cmname
char(44),sex char(6),city varchar(20),
phone int)
create table product(pid char(55) primary key,pname char(44),quantity
int,unit_price int,muf_date date,exp_date int,
sid varchar(77) foreign key references Sale_persons (sid) on delete cascade on
update cascade,
cid char(22)foreign key references customer(cid) on delete cascade on update
cascade)
select * from Sale_persons
insert into Sale_persons
values('S01','Abdi','kenesa','keneni','m',1990,'Accounting',3432,2021,+2511945
51)
insert into Sale_persons
values('S02','Chaltu','kuma','Damasa','f','1992','marketig',3732,'2021',
+25112321)
insert into Sale_persons
values('S03','Bekele','Hika','kadir','m',1994,'Economics',4232,2022,+25192551)
insert into Sale_persons
values('S04','Sifan','keresa','mamud','f',1998,'Economics',5632,2019,+2512551)
insert into Sale_persons
values('S05','Rome','Endale','Bona','F',1990,'supply',4732,2020,+25134551)
select * from login
insert into login values('Abdi','abdik','standard','s01','s01')
insert into login values('chaltu','chaltu','admin','s02','s02')
select * from customer
insert into customer values('c002','bontu','keneni','f','adama',+251985653)
insert into customer values('c004','Fayisa','Tullu','m','ambo',+251987653)
insert into customer values('c003','Kedir','Tola','m','nekemte',+251989653)
insert into customer values('c005','Yosen','Endale','f','Bushoftu',+251987653)
select * from product
insert into product values('m101','Buskuit',1,35,'2020',2021,'S01','c002')
insert into product values('m102','candy',2,15,'2021',2022,'S02','c004')
insert into product values('m103','colgate',4,50,'2020',2021,'S03','c003')
insert into product values('m104','Brush',3,25,'2021',2022,'S04','c005')
insert into product values('m105','cake',4,75,'2022',2023,'S05','c002')
select pname,cfname+' '+cmname 'C_name',sum(unit_price)'total price' from
product p,customer c on (c.cid= p.cid)
SELECT customer.cfname+' '+ customer.Cmname'c_Name', product.pname,
sum(product.unit_price)
FROM customer INNER JOIN
product ON customer.cid = product.cid group by
customer.cfname,customer.Cmname,product.pname
SELECT product.pname, customer.cfname, customer.Cmname,
sum(product.unit_price)
FROM customer INNER JOIN
product ON customer.cid = product.cid group by
customer.cfname,customer.Cmname,product.pname
update Sale_persons set salary =salary +salary*0.2 where salary >4000
select fname+' '+ mname+' '+lname as full_name,sex,occupation from
Sale_persons where sex='f' and occupation ='supply' or occupation='marketing'
select COUNT(*),min(salary),MAX(salary),AVG(salary),SUM(salary) from
Sale_persons
SELECT product.pname, customer.city, count(product.quantity)
FROM customer INNER JOIN
product ON customer.cid = product.cid group by
pname,city,quantity having product.quantity >=3
select top 3 salary ,fname+' '+ mname+' '+lname as
full_name,occupation,sum(salary) from Sale_persons group by
fname,mname,lname ,occupation,salary order by salary desc
