[Main Page]      [Literature]      [Software]      [People]      [Conferences]      [Courses]      [Web-DB]

Homework Assignment #9:    SQL

Reading Assignment

Please read the following chapter from the book by Elmasri and Navathe (3rd Edition):

• Chapter 8, "SQL - The Relational Database Standard"

Exercise 9 (12 Points)

Your results for homeworks, midterm and final exam are stored in a relational database with the following schema:

• STUDENTS(STUD_ID, FIRST_NAME, LAST_NAME, EMAIL, PASSWORD, LAST_TERM, REGISTERED, GRADE)

  STUD_ID is simply a sequential number assigned when the student submits the registration form. REGISTERED is the date when this row was inserted into the table. LAST_NAME and FIRST_NAME together are declared as secondary key. LAST_TERM is 'Y' if this is the last term of the student, otherwise it is 'N'. All attributes except EMAIL, LAST_TERM and GRADE are not null.
   

• CATEGORIES(CAT, CAT_NAME, OUTPUT_ORDER)

  This contains three rows, one for homeworks, one for the midterm exam, and one for the final exam. CAT contains a letter (e.g. 'H'), CAT_NAME the full name (e.g. 'Homeworks'), and OUTPUT_ORDER is 1 for homeworks, 2 for the midterm exam, and 3 for the final exam.
   

• EXERCISES(CAT -> CATEGORY, EX_NO, TOPIC, MAX_POINTS)

  CAT identifies the category of the exercise, e.g. 'H' if this is a homework exercise, 'M' if this is a midterm exercise, and 'F' if this exercise is from the final exam. EX_NO is the number of the exercise.
   

• RESULTS((CAT,EX_NO) -> EXERCISES, STUD_ID -> STUDENTS, POINTS, ENTERED)

  This table contains how many points a student has got for which exercise. ENTERED is the date when the result was entered into the database. Note that all attributes are not null. There is only an entry for a student X and and exercise Y if X submitted a solution to Y.

• http://www2.sis.pitt.edu/~sbrass/db/grades.sql (UNIX line ends)
• http://www2.sis.pitt.edu/~sbrass/db/grades_w.sql (DOS/Windows line ends)

Please write the following queries in SQL. You must actually run them in SQL*Plus (or some other SQL system). If your solution contains a syntax error which any DBMS would have found, you get 0 points. In SQL*Plus, you can create a spool file and submit this (see Appendix B). You do not need to submit the query results. You are allowed to use views.

    a)

Which students have done Homework 5 and 6? Try to solve this question without aggregations. Print first and last name of every such student.

    b)

Which students have not yet done Homework 6?

    c)

Print first and last name of a student and the exercise number of a homework, such that the student has not yet done that homework. (Of course, all such results are required, not only one student and one homework.) I can use the output of this query to check whether any homework results are missing in the database.

    d)

Print for every student the total number of points for homeworks, including students who have not done any homework yet (so their total score is 0). Order the result by the total number of points.

    e)

Print all students (first and last name) who have the perfect score for their homeworks. I.e. they got (at least) 100% for all homeworks in the database.

    f)

Who did best in the midterm exam, i.e. has the most points? Print first and last name of that student (it could be more than one, if two students have the same points).

Due Date:

Please submit your solution on next Wednesday (April 5) before the lecture.

Stefan Brass (sbrass@sis.pitt.edu), March 29, 2000

Original URL: http://www2.sis.pitt.edu/~sbrass/db00_2/h9_sql.html   [HTML 3.2 Checked]