CS101b - Assignment 3

SQL Joins, Views, and NULLs

Please keep track of the total time you spend on this assignment, and make a note of that on what you turn in. This way we can tune the assignments for future classes.

Book Exercises

Do these problems from the "Exercises" section of Chapter 3. These problems require you to write SQL statements or views. Follow good SQL coding style. Also, feel free to use any valid SQL syntax covered in the book; you do not have to restrict yourself to only valid MySQL syntax.

You can save your answers into a plain-text file, and send me the file.

Problems:

Each part of a problem is worth three points. There are 13 parts in all, for a total of 39 points.

Problem 3.12, part d. Write complete SQL statements, not just fragments.
Problem 3.14
Problem 3.18
Problem 3.19
Problem 3.20
Problem 3.21
Problem 3.22

Problem 3.21 is probably the hardest of the group.

MySQL Database Exercises

These exercises use the SQL schema for the bank database, and a new schema from a course management system. SQL files to use are provided below. If you changed the bank data in last week's lab, you should probably drop and re-create the database for the bank data.

These queries may utilize any valid SQL join syntax, including Cartesian products, outer joins, natural joins, etc. However, do not use nested subqueries in the SELECT clause. That feature is not widely supported yet, and it often leads to slow queries anyway. Feel free to consult the MySQL 5.0 online documentation, if you have any questions.

(Each separate query or operation is worth 3 points, as in the previous section. There are 12 parts = 36 points total.)

As you complete each of these exercises, save the commands you write into a file, marking them with the particular exercises they are for. You can use the MySQL client's "tee" feature to do this very easily. Just run the client as follows (assuming you want to be the root user in your database):

    mysql -u root --tee=hw3.txt

Now, as you use the MySQL client, every single command you type, and its output, will be stored into the file hw3.txt. You can then easily edit this into a form suitable for turning in.

Exercise 1

Take the view definitions you created in Problem 3.19, and load them into MySQL. You may need to edit the syntax a bit. Do a select against each of the views and make sure the results are correct.

Exercise 2 (3 parts)

Here are some general exercises to try out. Write a SQL query for each of these questions. Make sure your query runs against the database and generates a correct result. You can include the result if you like, but the main point is to turn in the SQL query itself.

Generate a result that lists, for each branch, the number of accounts and the number of loans at that branch. Make sure that branches with no accounts and/or loans appear in the results.
Write a SQL query that lists every single bank customer's name, and the total deposits they have in all accounts at the bank. Customers with no deposits may have NULL values, or they may have 0.
For each city that some customer lives in, compute the number of bank branches that are in that city. Include customer cities where there are no branches; their count should be 0. (Note that customer cities should not appear more than once in the results!)

Exercise 3 (4 parts)

The following questions use the schema and data for a homework management system used for a popular CS course (properly sanitized, of course). The schema represents recitation sections, student accounts, homework assignments, homework submissions, and the files submitted by the student. However, the database schema design is not ideal; every student has a submission record for every assignment, whether they did the assignment or not! One must tell whether the student has submitted an assignment by whether the submission record has any associated filesets.

The schema and data for the homework system are contained in the file make-grades.sql. Download this SQL file, create a database (perhaps named grades), and run the SQL file against that database. (The SQL file uses a nonstandard MySQL syntax for importing the data, since using simple INSERTs would make the import take several minutes.)

Once the data is imported, try these queries to get a feel for what the schema and data are like:

Compute what would be a "perfect score" in the course, by adding up the perfect score values for all assignments, quizzes, and exams.
Write a query that lists every section's name, and how many students are in that section.
Create a view named totalscores, that computes each student's total score, over all assignments in the course. The view's result should contain each student's username, and the total score for that student.
This course happens to be pass/fail, and a passing grade is a total score of 40 or better. Using the previous view, create two more views, one called passing and the other called failing, which lists the usernames and scores of all students that are passing or failing.

Exercise 4

Write a SQL query that lists the short name of every assignment in the database, along with the total submissions for that assignment. (Students can submit work multiple times in this system, but you can simply count all submissions.) Remember that because of this database's schema, all students have a record in the submission relation, whether they have submitted work for an assignment or not. Thus, you must look at the fileset relation for actual student submissions.

Once you have this query working, define a view called submit_counts for this query.

Exercise 5

Write a SQL query that lists the short name of every assignment in the database, the perfect score for that assignment, and the average student score for that assignment. Note that in the submission relation, the grade value can still be specified for ungraded submissions! So, be sure to limit your computation to submissions that are actually graded.

Define a view for this query called average_scores.

Exercise 6 (2 parts)

Write a SQL query that lists all students that failed to submit work for at least one lab assignment. (Lab assignments have a short-name starting with the characters "lab" in the assignment table.) Again, you must use the fileset relation to tell whether a student has made a submission or not; simply looking at the submission relation is not sufficient.

Once you have this query working, list all students that failed to submit at least one lab assignment, but still managed to pass the course.

And, for kicks, rerun this query to show all students that failed to submit either the midterm or the final and still managed to pass the course.