COMP 353-453
Homework #4
Complex Queries

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 lot more data.

You copy and paste the DDL, and there are two files. load1 and load2, to load the data.  The tables in the second file (especially the 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.

Please submit screenshots of your queries and responses.  If the queries are very small on your screenshot, also copy and paste them in readable text.


If you want to see if your query responses are correct, you can look at the screen shots of the answers.  But you still have to write the queries...
  1. Display all courses for which Professor Berndt has been qualified.  This uses a very straightforward join.
  2. 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 tables.                
  3.  Which instructors are qualified to teach ISM 3113?               
  4. Is any instructor qualified to teach ISM 3113 and not qualified to teach ISM 4930?                       
    -->  Here is an INCORRECT query, that will seem correct...

    5. SELECT Faculty.FacultyName
    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 of them are qualified to teach 4930.  Berndt (facultyID of 3467 is qualified to teach 4930.  You may want to add a record in IsQualified that qualified Berndt to teach 3113, and this should test your query to see if it is running correctly. 

    You can get credit for this question if you can clearly explain (or demonstrate) WHY the above query is incorrect.

     This is a difficult query.  You can try it with a join (easier) or a subquery (more difficult).  This query is intended to be a challenge.

  5. How many students are enrolled in ISM 3113 during semester I-2008?    
  6. Which students were not enrolled in any courses during semester I-2008?     
    This query uses NOT EXISTS!!             
  7. Find customers who have not placed any orders:   Use a nested subquery.
  8. List the name of the supervisor and number of employees supervised (label this value HeadCount)                        
    for all the supervisors who supervise more than two employees:   
    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.
  9. Names of employees, employee birthdate, manager name, manager’s birthdate for those                        
    employees born before their manager was born; label columns per problem instructions:
    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.
  10. Display each item ordered for order #1, its standard price,                
    and total price for each item ordered:   Name the column with the total price calculation TotalPrice.
  11. Calculate the total raw material cost (label TotCost) for each 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.
  12. Display the Employee ID and Employee Name for those employees who do not possess                       
     the skill Router. Display the results in order by EmployeeName:     
    Be careful where you place the subquery and the ORDER BY.                  
  13. Name of customer 16, and other customers in same zipcode    Self join.           
  14. Display the customer ID, name, and order ID for all customer orders.
    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.
  15. Challenge:  Show the customer ID and name for all the customers who have ordered
    both products with ProductIDs 3 and 4 on the same order:  A little confusing.  Or, you could use subqueries with a derived table...
  16. List the order number and order quantity for all customer orders for which the order quantity
    is greater than the average order quantity of that product:
    (Hint: This involves a correlated subquery.)  
  17. Monster challenge:  For each product display in ascending order by product ID the 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.