1 / 24

Final-Exam Revision

Final-Exam Revision. Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu. 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

vhaywood
Download Presentation

Final-Exam Revision

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Final-Exam Revision Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu

  2. 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

  3. SQL Commands

  4. Question 1

  5. Question 2 Select the most expensive generic medicine • Select * • From Medicine • Where UnitPrice = ( • Select max(unitPrice) • From Medicine • Where genericFlag = ‘T’) • And genericFlag = ‘T’;

  6. Question 2: Another way Select the most expensive generic medicine • Select * • From ( Select * • From Medicine • Where genericFlag = ‘T’ • Order By UnitPrice Desc) • Where rownum = 1; Use of rownum (Oracle specific) Must be in outer select after sorting

  7. Question 3

  8. Question 3: Another Way Use of rownum (Oracle specific)

  9. Question 4 Delete prescription lines for prescriptions written on date ‘Jan-01-2010’ Delete From Prescription_Medicine Where prescription_id in ( Select id From Prescription Where date = ‘Jan-01-2010’);

  10. Question 5 Delete prescriptions that have no lines (no records in prescription_medicine) Delete From Prescription Where id not in ( Select prescription_id From prescription_medicine);

  11. Question 6 Select patients who have no primary doctors Select * From Patient Where primaryDoctor_SSN is null;

  12. 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 Select prescription_id, sum(unitPrice * NumOfUnits) As totalCost From Medicine M, Prescription_Medicine PM Where M.TradeName = PM.TradeName Group By prescription_id Having totalCost > 100 And totalCost < 200 Order By totalCost;

  13. Question 8 Double the number of units in prescription id 11111 and tradeName ‘Aspirin’. Update Prescription_Medicine Set NumOfUnits = NumOfUnits * 2 Where Prescription_id = 11111 And tradeName = ‘Aspirin’;

  14. Question 9 For medicines in prescription 11111, double their number of units if is it currently below 5 Update Prescription_Medicine Set NumOfUnits = NumOfUnits * 2 Where Prescription_id = 11111 And tradeName in (Select tradeName From prescription_medicine Where prescription_id = 11111 And numOfUnits < 5);

  15. Advanced SQL Commands

  16. Question 10 Create a view that reports the trade name, unit price, and the generic flag of the most expensive and cheapest medicines.

  17. 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. • Create View MedicineView2 AS • Select TradeName, unitPrice, genericFlag, ‘Max’ as type • From Medicine • Where unitPrice in (Select max(unitPrice) price From Medicine) • Union • Select TradeName, unitPrice, genericFlag, ‘Min’ as type • From Medicine • Where unitPrice in (Select min(unitPrice) price From Medicine);

  18. Question 12 Using the view created in Question 11, count the number of trade names having the most expensive price. Select Count(*) From MedicineView2 Where type = ‘Max’;

  19. Question 13 Create an assertion that ensures that no prescription has a tradeName having number of units > 10 • Create Assertion NumUnitsBelowTen As • ( 10 >= All • (Select NumOfUnits • From Prescription_Medicine) • );

  20. Question 14 Create an assertion that ensures any primary doctor has at least 3 years of experience • Create Assertion NumUnitsBelowTen As • ( Not Exists • (Select * • From doctor D, patient P • Where D.SSN = P.PrimaryDoctor_SSN • And YearsOfExperience < 3) • );

  21. Question 15 Create a stored function that takes a date as a parameter and returns the number of prescriptions on that date • Create Function NumPrescriptions (inDate IN date) Return int As • temp int; • Begin • Select count(*) into temp • From prescription • where date = inDate; • return temp; • End;

  22. 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 Select * From prescription Where NumPrescriotions(date) > 10;

  23. 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 Create Trigger UnitPrice Before Insert Or Update On Medicine For Each Row Begin IF (:new.GenericFlag = ‘T’ and :new.UnitPrice >= 100) Then RAISE_APPLICATION_ERROR(-20004, ‘Price should be < $100’); ELSIF (:new.GenericFlag = ‘F’ and :new.UnitPrice < 100) Then RAISE_APPLICATION_ERROR(-20004, ‘Price should be >= $100’); END IF; End;

  24. Evaluation Forms • Course Number: CS3431 • Course Name: Database Systems I • Term: B11 • Instructor Name: Mohamed Eltabakh • TAs • Name: Kenneth Loomis • Name: Chiying Wang

More Related