IFS 320 - Database Management Systems
Dr. Vijay V. Raghavan
Assignment 1 - SQL Statements
A hospital subsystem is being designed to keep track of the charges incurred by patients during their stay. Let us call this "Hospital Database." Patients typically incur charges on a number of items, and it is necessary to keep record these charges to produce bills and various reports associated with the charges. PATIENT and ITEM entities have a M:N relationship. CHARGE is the bridge table. Create the tables and enter the data provided. Impose a constraint at the table level that the item_price can never be the more than three times the cost and also can never be below 120% of the cost ("CHECK CONSTRAINT" statement)
PATIENT
patient_id char(3) Not Null
patient_last_name varchar(20)
patient_first_name varchar(10)
pat_age number
pat_city varchar(20)
ITEM
item_id char(3) Not Null
item_name varchar(20)
item_price number(5.2)
item_cost number(5.2)
cost_center char(3) Not null
CHARGE
patient_id char(3) Not Null
item_id char(3) Not Null
charge_date date
quantity Integer
PATIENT
patient_id patient_last_name patient_first_name pat_age pat_city
001 Jones Larry 42 Blue Ash
002 Johnston Mary 26 Montgomery
003 Smith Robert 57 Fairlawn
004 Mackey June 33 Sharonville
005 Watts Fred 46 Blue Ash
006 James Alfred 87 Milford
007 Cook Sam 12 Montgomery
008 Wright Kurt 03 Blue Ash
009 Fox Kathleen 21 Sharonville
010 Murray Pam 46 Miami
011 Wong Kim 29 Dayton
012 Thomas Mary 45 Maryland
013 Gifford John 56 Blue Ash
014 Matthews Phil 54 Sharonville
ITEM
item_id item_name item_price item_cost cost_center
010 Telephone 2.95 1.25 200
020 Aspirin 1.25 0.33 201
030 Bandage 0.95 0.44 201
040 TV 8.99 3.12 200
050 Extra Meal 7.34 4.23 202
CHARGE
patient_id item_id charge_date quantity
001 020 04/12/2004 2
006 050 05/15/2004 1
004 010 05/12/2004 3
005 030 04/26/2004 5
011 020 06/15/2004 3
003 040 04/12/2004 2
001 050 04/15/2004 1
007 050 06/02/2004 3
010 020 06/04/2004 2
004 040 05/04/2004 1
002 020 04/12/2004 1
011 040 06/16/2004 4
012 030 06/16/2004 2
013 040 06/13/2004 5
012 040 06/16/2004 2
014 010 06/16/2004 3
QUESTIONS
Write the appropriate SQL
statements that will provide the answer to the following
questions or requests (Note: Each question or request can be
answered with a single SQL statement.) Verify the correctness of
your responses by typing them in an interactive SQL environment.
1. List the name and cost of all
items that have been charged along with the last name of the
patient who charged the item. List in cost (descending) and last
name (ascending) order.
2. List the id and full name of
all patients who have charged item id 020.
3. Change the price of TV to
10.95.
4. What is the average price of
those items that have been charged by at least one patient?
5. What is the total number of
extra meals that have been charged?
6. What would be the total cost
to a patient who orders one of every item?
7. List the name and profit of
each item? (profit = price - cost)
8. List the first name, last
name , item name, price, and cost of all items that have been
charged with a price of more than $5.00 but less than $8.00.
9. What is the name and price of
the item with the largest price?
10. List each item name that has
been charged without showing duplicate item names. List should be
in alphabetical order.
11. What is the average amount of a charge per transaction?
Please send your comments to: Raghavan
© Dr. Vijay V. Raghavan
BACK TO Table of contents
BACK TO DATABASE Main Page