CIS 330 — SQL Queries Assignment #1
.
The following queries will be done using the Job Listings database that I uploaded to Canvas. I showed you these in class the first week.
To create and populate the database, you can use the SQL Plus* START command. This command will run a script file. The two script files you need to run are: HospitalDDL.sql (which drops the tables if they exist, then creates the tables), and HospitalDMLsql (which inserts data into the tables). The START command is illustrated in the Oracle SQL Plus* notes available on Canvas.
Note that START executes a script file. A script file can contain multiple SQL commands which will be executed one after the other.
DELIVERABLES: You will upload to Canvas a single script file that contains all of the following 10 queries. Before each query you should have a comment that gives the query number. For example:
REM Query #1 or — Query #1
1) Perform a query that returns certain fields from all rows of the Patients table. Rows and columns should be ordered and displayed as follows:
2) For each room stored in the Patients table, show the number of number or patients in that room, and the oldest patient’s birthdate. Column headers and ordering should look like this, with ten rows returned. However, note that because Baby Jane’s birthdate was inserted based on SYSDATE (see HospitalDML.sql file), your date for the nursery may not match the date shown. It will depend on when you ran the HospitalDML script.
- 3) Show all nurses who are supervisors. Only supervisors should be shown, with one exception. For psych nurses, you should include them in the list even if they are not supervisors. Results should look like this, properly ordered and formatted.
- 4) Perform a query that returns all the patients and their contact information. There should only be two columns in the result set. One contains the patient’s first initial and last name. The second contains both the email address and the phone number if it exists. The result set should be sorted alphabetically by last name. For people with the same last name, they should be ordered in reverse order by first names (so that Melania appears before Donald. The results should look like this, with proper column headers:
- 5) Perform a query that displays the oldest and youngest patients’ birthdates, including the day of the week. You should also show the difference in years between these two dates. The result should look like this, proper header and descriptive text. Only include patients who were born in the 20th century.
- 6) We want to see four things. First, the total number of patients. Second, number of patients who have a phone number. Third, the length in characters of the shortest hospitalization reason. Fourth, the length in characters of the longest hospitalization reason. The results should look like this:
- 7) How many visits were done in the morning vs. evening? Your result should look like this. Note: the numbers may differ by one, depending on when the nurse visit with CURRENT_TIMESTAMP was inserted in your database. Check your nurse visit times to make sure you are correct based on your own database values.
- 8) Perform a query that shows, for each unit in the nurses table, the number of nurses in that unit. Also display the minimum, maximum, and average salaries for nurses in that unit. Only include units where there are at least two nurses. Your output should be formatted, labeled, and sorted as shown below.
- 9) Show the names of all patients who either are hospitalized because they fell or whose birthdate is in the month of January, April or June. Ordering should be as shown below.
- 10) How many patients are from each email domain? The results should look like this: