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: