Overview:
The purpose of this assignment is to explore the use of SELECT statement to retrieve tuples from Oracle database, and how to use various clauses for filtering, grouping and sorting data.
Instructions:
1. You must work on this assignment individually.
2. Submit your assignment in a script file Assig1_script.sql. The assignment must be submitted on Omnivox (Lea).
What to do:
A simple database composed of the following tables: PATIENT, DOCTOR, DRUG, and PRESCRIPTION, is now loaded with this assignment and you are ready to start. Please explore the database and then write the appropriate SQL command to answer the following questions.
1. List all the rows in which the prescription dates occur on or after November 14.
2. How many doctors provide prescriptions with 30 tablets in total?
3. List doctor’s details where speciality is either Dermatology or Neurology. (Use IN operator). List the results by the first name in ascending order and the last name in descending order
4. Find all patients rows whose first names include ‘George’ (Assume case sensitivity).
5. List number of patients that do not have a phone number.
6. How many drugs with price < 40$?
7. How many prescriptions are generated for each patient, limit the listing to patients who have more than 2 prescriptions?
8. List the drug codes and prices (rounded to one decimal place).
9. How many doctors prescribe the drug code KO15?
10. List all drugs with their expiration date (one year from the date of manufacturer)
11. List all drugs whose prices are between $50 and $100.
12. List number of female patients born in 1970
