Question

Create the database and tables for the database. Show all SQL statements. Include primary and foreign keys. Insert data into each table. Show select statements and display the output of each table. Note:Student’s name must be inserted into table as part of the data! Perform the SQL below: Query one table and use WHERE to filter the results. The SELECT clause should have a column list, not an asterisk (*). State the purpose of the query; show the query and the output. Get information from at least 3 tables in one statement, and provide the output using the Join operator. Use ANSI Join syntax. State the purpose of the query; show the query and the output. Add a screen shot of SS Management Studio showing the query and results. Get information from 2 tables in one statement, and provide the output using the Left Outer Join operator. State the purpose of the query; show the query and the output. The outer join should be designed to retrieve information from the left table that has no matches in the right table. If that is not possible for your database, explain why. Create a query using the IN keyword with a subquery. State the purpose of the query; show the query and the output. Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY command. State the purpose of the query; show the query and the output. Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY command using the HAVING clause to filter the aggregate results. State the purpose of the query; show the query and the output. Update one row. State the purpose of the query; show the result set for the row(s) before the update; show the query; show the row(s) after the update. Delete one row. State the purpose of the query; show the result set before the delete; show the query; show the result set after the delete.

Dr. ANB Associates Doctor Doctor ID PK speciality patient ID FK test/procedure Doctor ID FK Patient ID FK Name Test date Patient Patient ID PK Name results Address phone number ry phone number DOB visit dates Doctor ID FK Appointments appt ID PK appointment date me Patient In EK Employee Procedure employee ID PK Patient ID App ID FK Emplovee ID FK

Answer 1

TABLE1:

CREATE TABLE doctor (

D_id int NOT NULL,

speciality varchar(20),

pid int,

   dob date,

   ssn int,

   primary key(D_id)

);

INSERT INTO doctor
VALUES (101,'Surgeon; 1091,'1988-05-13',11);

INSERT INTO doctor
VALUES (96,'Cardiologist',2981,'1994-10-15',6);

INSERT INTO doctor
VALUES (5,'General physician',97,'1980-12-30',17);

OUTPUT:

$sqlite3 database.sdb < main.sql
101|Surgeon|1091|1988-05-13|11

96|Cardiologist|2981|1994-10-15|6

5|General physician|97|1980-12-30|17

TABLE2:

CREATE TABLE appointments (

a_id int NOT NULL,

adate date;,

atime timestamp,

   pid int;

   primary key(a_id)

);

INSERT INTO appointments
VALUES (2975,'2019-05-13','12:00:00',1091);

INSERT INTO appointments
VALUES (2976,'2019-05-10','13:19:25',2981);

INSERT INTO appointments VALUES (2971,'2019-05-14','01:45:23',97);

OUTPUT:

2975|2019-05-13|12:00:00|1091

2976|2019-05-10|13:19:25|2981

2971|2019-05-14|01:45:23|97

TABLE 3:

CREATE TABLE test(

D_id int,

pid int,

tname varchar(20),

tdate date,

ttime timestamp,

price int,

result varchar(20),

foreign key (D_id) references doctor(D_id)

);

INSERT INTO test
VALUES (101,1091,'Sugar','2019-05-14','18:20:00',100,'HIGH');

INSERT INTO test
VALUES (96,2981,'Full body','2019-05-18','10:50:16',2000,'NORMAL');

INSERT INTO test
VALUES (5,97,'UTI,'2019-05-14','11:55:00',500,'POSITIVE');

OUTPUT:

101|1091|Sugar|2019-05-14|18:20:00|100|HIGH

96|2981|Full body|2019-05-18|10:50:16|2000|NORMAL

5|97|UTI|2019-05-14|11:55:00|500|POSITIVE

TABLE 4:

CREATE TABLE employee(

E_id int NOT NULL,

a_id int,

pid int,

primary key(E_id),

foreign key (a_id) references appointments(a_id)

);

INSERT INTO employee
VALUES (109,2971,97);

INSERT INTO employee
VALUES (103,2976,2981);

INSERT INTO employee
VALUES (10,2975,1091);

OUTPUT:

109|2971|97

103|2976|2981

10|2975|1091

TABLE 5:

CREATE TABLE patient(

pid int NOT NULL,

pname varchar(20),

pno int,

paddress varchar(20),

dob date,

pvisit date,

D_id int,

primary key(pid),

foreign key (D_id) references doctor(D_id)

);

INSERT INTO patient
VALUES (1091,'James',9870473810,'New york','1997-03-03','2019-05-13',101);

INSERT INTO patient
VALUES (97,'Emily',7892053182,'Chicago','1980-09-17','2019-05-14',5);

INSERT INTO patient
VALUES (2981,'Jasmine',8906541230,'New york','1966-12-10','2019-5-10',96);

OUTPUT:

1091|James|9870473810|New york|1997-03-03|2019-05-13|101

97|Emily|7892053182|Chicago|1980-09-17|2019-05-14|5

2981|Jasmine|8906541230|New york|1966-12-10|2019-5-10|96

//To add foreign key pid to all the tables

Alter table doctor add foreign key(pid) references patient(pid);

alter table appointments add foreign key(pid) references patient(pid);

alter table test add foreign key pid references patient(pid);

alter table employee add foreign key(pid) references patient(pid);

OPERATIONS ON TABLES:

QUERY 1.) select using field names;

select d_id,speciality,pid,dob,ssn from doctor;

OUTPUT:

101|Surgeon|1091|1988-05-13|11

96|Cardiologist|2981|1994-10-15|6

5|General physician|97|1980-12-30|17

QUERY 2.) select * from patient where pid=(select pid from employee where E_id=109);

OUTPUT:

97|Emily|7892053182|Chicago|1980-09-17|2019-05-14|5

QUERY3.) SELECT *
FROM patient
LEFT OUTER JOIN test
ON patient.pid = test.pid;

OUTPUT:

1091|James|9870473810|New york|1997-03-03|2019-05-13|101|101|1091|Sugar|2019-05-14|18:20:00|100|HIGH 97|Emily|7892053182|Chicago|1980-09-17|2019-05-14|5|5|97|UTI|2019-05-14|11:55:00|500|POSITIVE 2981|Jasmine|8906541230|New york|1966-12-10|2019-5-10|96|96|2981|Full body|2019-05-18|10:50:16|2000|NORMAL

QUERY4.) select sum(price) as total_sum_of_prices from test group by pid;

OUTPUT:

total_sum_of_prices

500

100

2000

QUERY 5.) select sum(price) as total_sum_of_prices from test group by pid having result='PSOTIVE';

OUTPUT:

500