Question

I'm using livesql for my database class to try and do these

A medical clinic has doctors, along with patients assigned to one of the doctors.

Create 2 tables that include fields below with primary key chosen below. Use appropriate data types and simplified field names, eg: -

Doctor’s Table:
DKey ID          
Last name
First name
Home phone
Medical specialty
Monthly Salary

Patient’s Table:
PKey ID          
Last name
First name
Address (Use Address as column name)
Home Phone  
Age (in years)      
DoctorID Assigned
Medical History
Add 3 doctors and about 8 patients. Values will be provided in a text file to save time.

Create 3 select statements
List all doctors,
List all patients,
Using where clause, show which doctors have which patients. (Make sure your result indicates who is Doctor and who is Patient.

List the ID num, first and last name of doctors who has a salary that is greater than 16000.

Create the following aggregate select statements.
The monthly sum of all doctors’ salaries
The average age of the patients

Write a SQL statement to show the total number of rows in Patient table. Sort by last name.

Select the lastname, firstname, assigned doctor of patients where the patient’s medical history is Diabetic.

Write a SQL statement to change one of the patient’s name “Kasuma” who recently got married and which to use her married name “Chutaw.” Patient ID number is 103. (10pts)

Write a SQL statement to increase all Doctors salary by 20% and rename column as Salary Increase

List all the patients with high blood pressure and are older than 49 years old.

text file with values are:

Doctor Table


('AM01','Abrams','Miles','617-555-6032','MD', 20000);

('BR01','Boyers','Rita','603-555-2134','MD', 15000);

('DH01','Devon','Harley','781-555-7767','MD', 17000);



Patient Table


('101','Northfold','Liam','9 Old Mill Rd. Londonderry NH','603-555-7563', '45', 'BR01', 'Breat Cancer');

('102','Ocean','Arnold','2332 South St. Apt 3 Springfield MA','413-555-3212', '55','AM01', 'Diabetic');

('103','Kasuma','Sujata','132 Main St. 1 East Hartford CT','860-555-0703','64', 'DH01', 'High Blood Pressure');

('104','Goff','Ryan','164A South Bend Rd.Lowell MA','781-555-8423','50','DH01','Left Renal abnormality');

('105','McLean','Kyle','345 Lower Ave. Wolcott NY','585-555-5321', '60','BR01', 'Diabetic');

('106','Morontoia','Joseph','156 Scholar St. Johnston RI','401-555-4848', '52','DH01','High Blood Pressure');

('107','Marchand','Quinn','76 Cross Rd. Bath NH','603-555-0456', '40','AM01','Diabetic');

('108','Rulf','Uschi','32 Sheep Stop St. Edinboro PA','814-555-5521', '75','BR01', 'Low Blood Count');

i'm stuck at creating a second table "create table patient" and livesql keeps telling me im missing a parenthesis >_<

Answer 1

This demonstration is using Oracle Live SQL.

1.Table Name :Doctor
create table Doctor(
ID varchar(10) primary key ,   
Lastname varchar(10),
Firstname varchar(10),
Homephone varchar(15),
Medicalspecialty varchar(5),
MonthlySalary number);

/*inserting records into Doctor table*/
insert into Doctor values ('AM01','Abrams','Miles','617-555-6032','MD', 20000);
insert into Doctor values ('BR01','Boyers','Rita','603-555-2134','MD', 15000);
insert into Doctor values ('DH01','Devon','Harley','781-555-7767','MD', 17000);

/*selecting records*/
select * from Doctor;

Screen in Oracle Live SQL :

*************************************************

2.Table Name :Patient
create table Patient(
ID varchar(10) primary key,
Lastname varchar(10),
Firstname varchar(10),
Address varchar(100),
HomePhone varchar(15) ,
Age varchar(5),
DoctorID varchar(10),
MedicalHistory varchar(100),
foreign key (DoctorID) references Doctor(ID));

/*inserting records into Patient table*/

insert into Patient values ('101','Northfold','Liam','9 Old Mill Rd. Londonderry NH','603-555-7563', '45', 'BR01', 'Breat Cancer');
insert into Patient values ('102','Ocean','Arnold','2332 South St. Apt 3 Springfield MA','413-555-3212', '55','AM01', 'Diabetic');
insert into Patient values ('103','Kasuma','Sujata','132 Main St. 1 East Hartford CT','860-555-0703','64', 'DH01', 'High Blood Pressure');
insert into Patient values ('104','Goff','Ryan','164A South Bend Rd.Lowell MA','781-555-8423','50','DH01','Left Renal abnormality');
insert into Patient values ('105','McLean','Kyle','345 Lower Ave. Wolcott NY','585-555-5321', '60','BR01', 'Diabetic');
insert into Patient values ('106','Morontoia','Joseph','156 Scholar St. Johnston RI','401-555-4848', '52','DH01','High Blood Pressure');
insert into Patient values ('107','Marchand','Quinn','76 Cross Rd. Bath NH','603-555-0456', '40','AM01','Diabetic');
insert into Patient values ('108','Rulf','Uschi','32 Sheep Stop St. Edinboro PA','814-555-5521', '75','BR01', 'Low Blood Count');

/*selecting records*/
select * from Patient;

Screen in Oracle Live SQL :

**************************************************

show which doctors have which patients :

select Doctor.ID as DoctorID,Doctor.FirstName || ' ' || Doctor.LastName as DoctorName,
Patient.Id as PatientID,Patient.FirstName || ' ' || Patient.LastName as PatientName
from doctor, patient where doctor.ID=Patient.DoctorID;

Query result :

********************************************

List the ID num, first and last name of doctors who has a salary that is greater than 16000 :

select ID,firstName,lastName from doctor where MonthlySalary>16000;

Query result :

******************************************

The monthly sum of all doctors’ salaries :

select sum(MonthlySalary) as monthlySumOfSalary from doctor;

Query result :

*******************************************

The average age of the patients :

select avg(age) as AverageAge from patient;

Query result :

********************************************

a SQL statement to show the total number of rows in Patient table. Sort by last name.:

select count(ID) as RowCount from patient order by lastName;

Query result :

**********************************************

Select the lastname, firstname, assigned doctor of patients where the patient’s medical history is Diabetic :

select patient.lastname ||' '||patient.firstName as PatientName,
Doctor.FirstName || ' ' || Doctor.LastName as DoctorName
from doctor, patient where doctor.ID=Patient.DoctorID
and medicalHistory like 'Diabetic';

Query result :

******************************************
a SQL statement to change one of the patient’s name “Kasuma” who recently got married and which to use her married name “Chutaw.” Patient ID number is 103 :

update patient set lastname='Chutaw' where ID='103';

Query result :

******************************************

a SQL statement to increase all Doctors salary by 20% and rename column as Salary Increase :

select monthlySalary,monthlySalary+monthlySalary*0.20 as SalaryIncrease from doctor;

Query result :

***************************************

List all the patients with high blood pressure and are older than 49 years old :

select * from patient where MEDICALHISTORY like 'High Blood Pressure' and
age>49;

Query result :