Final-Exam Revision
Instructor: Mohamed Eltabakh
meltabakh@cs.wpi.edu
1
Reminder…..Final Exam
Dec. 13, at 8:15am – 9:30am (75 mins)
Closed book, open sheet
Answer in the same exam sheet
Material Included
ERD
SQL (Select, Insert, Update, Delete)
Views, Triggers, Assertions
Cursors, Stored Procedures/Functions
Material Excluded
Relational Model & Algebra
Normalization Theory
ODBC/JDBC
Indexes and Transactions
SQL Commands
3
Question 1
4
Question 2
Select the most expensive generic medicine
5
Question 2: Another way
Select the most expensive generic medicine
6
Question 3
7
Question 3: Another Way
8
Question 4
Delete prescription lines for prescriptions written on
date ‘Jan-01-2010’
9
Question 5
Delete prescriptions that have no lines (no records in
prescription_medicine)
10
Question 6
Select patients who have no primary doctors
11
Question 7
Report the prescription id and its total cost of prescriptions having
total cost between $100 and $200. Sort ascending based on the
total cost
12
Question 8
Double the number of units in prescription id 11111 and tradeName
‘Aspirin’.
13
Question 9
For medicines in prescription 11111, double their number of units if
is it currently below 5
14
Advanced SQL
Commands
15
Question 10
Create a view that reports the trade name, unit price, and the generic flag of
the most expensive and cheapest medicines.
16
Question 11
Create a view that reports the trade name, unit price, and the generic flag of
the most expensive and cheapest medicines. Each record should have an
indicator on whether it is the most expensive or cheapest.
17
Question 12
Using the view created in Question 11, count the number of trade names
having the most expensive price.
18
Question 13
Create an assertion that ensures that no prescription has a tradeName
having number of units > 10
19
Question 14
Create an assertion that ensures any primary doctor has at least 3 years of
experience
20
Question 15
Create a stored function that takes a date as a parameter and returns the
number of prescriptions on that date
21
Question 16
Using the function created in Question 15, report the prescriptions written on a
date in which more than 10 prescriptions have been written
22
Question 17
Create trigger that ensures that if the medicine is generic, then its unit price is
below $100, and if it is not generic then its unit price >= $100
23