For Queries 1-6, use the StudentRegistration database.
For the remaining queries, use the "Big" version of the Pine Valley
Database. It has more tables, some structural changes, and a
You copy and paste the DDL,
and there are two files. load1
to load the data. The tables in the second file (especially
rawMaterials table) have a lot of data. Only Query #11
requires the second data
fine (load2), so I am not requiring Query #11, and you don't have to
download load2. However, it's here if you want to play with it.
If you want to see if your query responses are correct, you can look at
shots of the answers. But you still have to write
- Display all courses for which Professor Berndt has been
qualified. This uses a very straightforward join.
- Display the class roster, including student name, for all
students enrolled in section 2714
of ISM 4212. The orster should be alphabetized.
This is also a straightforward join, but with more than two
- Which instructors are qualified to teach ISM
- Is any instructor qualified to teach ISM 3113 and not
to teach ISM 4930?
--> Here is an INCORRECT query, that will seem
FROM Faculty, Qualified
WHERE Qualified.FacultyID=Faculty. FacultyID
AND Qualified.CourseID='ISM 3113'
AND NOT (Qualified.CourseID='ISM 4930');
From the DDL and Insert files, it
looks like Birkin (facultyID
2143) and Collins (4756) are both qualified to teach 3113, and neither
are qualified to teach 4930. Berndt
(facultyID of 3467 is qualified to teach 4930.
You may want to add a record in IsQualified that
teach 3113, and this should test your query to see if it is running
You can get credit for this
if you can clearly explain (or demonstrate) WHY the above query is
is a difficult query.
You can try it with a join (easier) or a subquery (more
query is intended to be a challenge.
- How many students are enrolled in ISM 3113 during
- Which students were not enrolled
in any courses during
This query uses
- Find customers who have not placed any orders:
Use a nested subquery.
- List the name of the supervisor and number of employees supervised (label
for all the supervisors who supervise more than two
Caution: In addition
to a join, you are mixing a scalar and an aggregate here!!!
But if you GROUP BY correctly, you can use the value on which you are grouping in the
same SELECT clause as the aggregate function for that group.
- Names of employees, employee birthdate, manager name,
birthdate for those
employees born before their manager was born; label columns per problem
This is a self join. Give the tables the
aliases E1 and E2.
You may use the aliases to qualify the
field names in the SELECT, FROM and WHERE clauses.
- Display each item ordered for order #1, its standard
and total price for each item ordered: Name the
column with the total price calculation TotalPrice.
- Calculate the total raw material cost (label TotCost) for
product compared to its standard product price
and display product ID,
product description, standard price, and the total cost in the result:
This query uses the load2 table. You may skip it if
you are short on memory.
- Display the Employee ID and Employee Name for those
do not possess
the skill Router. Display the results in order by
Be careful where you place the subquery and the ORDER BY.
- Name of customer 16, and other customers in same
zipcode Self join.
- Display the customer ID, name, and order ID for all
For those customers who do not have any orders, include them in
the display once,
with a 0 value for OrderID: This is a UNION query. Not on test, but try it anyway.
- Challenge: Show the customer ID and name for all the customers who
both products with ProductIDs 3 and 4 on the same order: A little
confusing. Or, you could use subqueries with a derived table...
- List the order number and order quantity for all customer
for which the order quantity
is greater than the average order quantity
of that product:
(Hint: This involves a correlated subquery.)
- Monster challenge: For each product display in ascending order by product ID
product ID and description along
with the customer ID and name for the customer who has bought the most
of that product;
also show the total quantity ordered by that customer (who has bought
the most of that product).
Use a correlated subquery: This query is extremely difficult. Show me that you gave it a good effort.