Initialization create database Hospital; use Hospital; set FOREIGN_KEY_CHECKS=0;
ID: 3877778 • Letter: I
Question
Initialization
create database Hospital;
use Hospital;
set FOREIGN_KEY_CHECKS=0;
drop table if exists INS;
drop table if exists Patients;
drop table if exists MDS;
drop table if exists Medical;
drop table if exists Treatments;
-- 1. Create a Table called INS...
create table INS (
INS_code varchar(5) not null primary key,
Insurance_name varchar(30),
Address varchar(30),
City varchar(30),
State varchar(2),
Zipcode varchar(5)
);
-- Add the following records to the table
insert into INS values
('GHI', 'Group Health Insurance', '1st street', 'Albany', 'NY', '12201'),
('BC', 'Blue Cross', '2nd street', 'Alexandria', 'IN', '46001'),
('AHS', 'American Health System', '3rd street', 'Abington', 'PA', '19001');
-- 2. Create a Table called Patients containing information regarding patients in a Hospital
create table Patients (
Code varchar(5) not null primary key,
Name varchar(30),
Ins_company_Code varchar(5),
Address_line_1 varchar(30),
Address_line_2 varchar(30),
City varchar(15),
State varchar(2),
Zipcode varchar(5),
foreign key fk1 (Ins_company_Code) references INS(INS_code) on delete cascade on update cascade
);
-- Add the following records to the table:
insert into Patients (Code, Name, Ins_company_Code) values
('P1', 'Frank Pena', 'GHI'),
('P2', 'Rosi Torres', 'BC'),
('P3', 'Alex Guerrero', 'BC'),
('P4', 'Albert Guerra', 'BC'),
('P5', 'Sue Villa', 'GHI'),
('P6', 'Richard Espinal', 'AHS');
-- 3. Create a Table called MDS containing information regarding doctors and dentists:
create table MDS (
Code varchar(5) not null primary key,
Name varchar(30),
Address varchar(30),
City varchar(15),
State varchar(2),
Zip varchar(5)
);
-- Add the following records to the table:
insert into MDS values
('M1', 'Dr. Anthony Kirby', '1st street, 1', 'Los Angeles', 'CA', '90001'),
('M2', 'Dr. Jack Galen', '1st street, 1', 'Bartow ', 'FL', '33831'),
('M3', 'Dr. Jane Jung', '2nd street, 2', 'Abbeville', 'AL', '36310'),
('M4', 'Dr. Louis Blackwell', '3rd street, 3', 'Adak', 'AK', '99546'),
('M5', 'Dr. Kate Crichton', '4th street, 4', 'Agua Linda', 'AZ', '85640');
-- 4. Create a Table called Medical containing the following:
create table Medical (
Mp_code varchar(5) not null primary key,
description varchar(30),
Price numeric(5, 2)
);
-- Add the following records to the table:
insert into Medical values
('1111', 'Throat culture', '1.11'),
('2222', 'Routine blood test', '2.22'),
('3333', 'In rown toenail removal', '3.33'),
('4444', 'Fill a cavity', '4.44'),
('5555', 'Dental x-ray', '5.55'),
('6666', 'Dental cleaning', '6.66'),
('7777', 'Nose bleed treatment', '7.77'),
('8888', 'S linter removal', '8.88');
-- 5. Create a Table called Treatments containing information regarding treatments received by patients.
create table Treatments (
Patient_code varchar(5),
Md_code varchar(5),
Mp_code varchar(5),
Date_of_treatment date
);
-- 6. You cannot have the same treatment twice in a given date for a specific patient
alter table Treatments add constraint primary key (Patient_code, Date_of_treatment);
-- 7. A patient can have the same medical procedure on different days.
-- NO ACTION
-- 8. There are 6 patients. The Patient codes are: PI, P2, P3, P4, P5, P6
alter table Treatments add constraint foreign key (Patient_code) references Patients(Code) on delete cascade on update cascade;
-- 9. There are 5 Doctors. The Doctor codes are: Ml, M2, M3, M4, M5
alter table Treatments add constraint foreign key (Md_code) references MDS(Code) on delete cascade on update cascade;
insert into Treatments values
('P1', 'M1', '1111', '2017-01-01'),
('P1', 'M1', '2222', '2017-01-02'),
('P2', 'M1', '1111', '2017-01-03'),
('P2', 'M1', '2222', '2017-01-04'),
('P2', 'M2', '3333', '2017-01-05'),
('P2', 'M2', '4444', '2017-01-06'),
('P2', 'M2', '5555', '2017-01-07'),
('P4', 'M1', '1111', '2017-01-08'),
('P4', 'M1', '2222', '2017-01-09'),
('P4', 'M1', '3333', '2017-01-10'),
('P4', 'M1', '1111', '2017-01-11'),
('P5', 'M1', '1111', '2017-01-12'),
('P5', 'M1', '2222', '2017-01-13'),
('P5', 'M2', '3333', '2017-01-14'),
('P5', 'M3', '4444', '2017-01-15'),
('P5', 'M3', '5555', '2017-01-16'),
('P6', 'M1', '1111', '2017-01-01');
Explanation / Answer
query 3:
select name, COUNT(Patient_code) from patient ,Treatements order by Patient_code order by count(Patient_code) DESC limit 1;
or
selct name from patient where code = (select Patient_code from Treatements order by Patient_Code order by count (Patient_code) DESC limit 1);
query 4:
3 and 4 query are equal just in most treatements and most money spend
selct name from patient where code = (select Patient_code from Treatements order by Patient_Code order by count (Patient_code) DESC limit 1);
query 5:
select name from patient where NOT EXISTS (select Patient_code from Treatements,patients where patient.code=Treatements.Patient_code and mp_code='1111' )
query 6:
select name from patients where Ins_company_Code=(select Ins_company_Code,count(Ins_company_Code)from patients order by count(Ins_company_Code)desc limit 1 )
query 7:
select name from MDS where code =(select md_code from treatements order by count(md_code) desc limit1)
I hope you understand the answer. Thank you.