Assignment 3
http://www.uncg.edu/~akdippel/csc671/assignment3.htm
On-paper assignment Note: These queries are the same as the queries in assignment 2. Here you need to write the queries in
SQL.Consider the following relation schemes:
Patients = {p_id, p_name, street, city, state, zip}
Doctors = {d_name, d_phone, specialty}
Visits = {d_name, p_id, time, day, month, year, fee}
Accounts = {p_id, date_in, date_out, amount}
Write the following queries in SQL.
Q1 (5%) List id and names of patients who live in High Point.
select p_id, p_name
from patients
where city = "High Point"
Q2 (5%) List the details of all visits for which a fee of $400 or more was charged.
select *
from visits
where fee >= 400
Q3 (5%) List the account details for patient with id ``123456''. Can the total (i.e. sum of amounts) be calculated in SQL?
Write the query if it is possible.
select *
from accounts
where p_id = "123456"
Yes, to get the total just use the sum function on the amount field.
select sum(amount)
from accounts
where p_id = "123456"
Q4 (5%) List all patients (id and name) visited by Dr. Smith in either July or August of 1998.
Select distinct patients.p_id, p_name
from patients, visits
where patients.p_id = visits.p_id and (month = "July" or month = "August") and
year = "1998"
Q5 (5%) List doctors who have not visited ``Tom Holiday''.
select d_name
from doctors
where d_name not in ( select d_name
from patients, visits
where patients.p_id = visits.p_id and p_name = "Tom
Holiday ")
Q6 (5%) List pairs of doctors (names) with the same specialty.
select d.d_name, e.d_name
from doctors as d, doctors as e
Where d.speciality = e.speciality and d.d_name < e.d_name
Q7 (5%) List patients (id) who have been visited by all doctors.
Select distinct V.p_id
from visits as V
where not exists ((select d_name
from doctors)
except
(select T.d_name
from visits as T
where T.p_id = V.p_id))
Q8 (5%) List patients admitted on or before 08/15/98 who were visited by Dr. Smith during their hospitalization. (Assume
comparison is possible on dates.)
select distinct p_name
from (patients natural inner join accounts) natural inner join visits)
where date_in <= "08/15/98" and d_name = "Smith"
Q9 (5%) List doctors who have been themselves hospitalized. Are you making any assumptions in formulating your query?
select distinct p_name
from patients, accounts, doctors
where patients.p_id = accounts.p_id and p_name = d_name
Q10 (5%) List the doctor who has charged the highest fee (ever).
select distinct d_name, fee
from visits
where d_name, fee not in (select visits.d_name, visits.fee
from visits, visits as V
where visits.fee < V.fee)
10% of the assignment mark is for typesetting/typing
Hands-on assignment (40%)
make tables for the above relations in the database, and enter some tuples for each relation. Then write and execute SQL
queries for Q1, Q3, Q4, Q5, Q6, Q7, Q8, and Q10. Print and hand-in the tables, queries, and the result of each query.