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

Homework Assignment #8:    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 8 (10 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.
   

• CATEGORY(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.

• 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 are strongly advised to really test them in SQL*Plus (or some other SQL system). In SQL*Plus, you can create a spool file and submit this (see Appendix B). You do not need to submit the query results.

    a)

Print the first and last name of all students who have got the maximal points for Exercise 2 in the midterm exam. "Maximal points" is the number stored in EXERCISES, not the maximum which was actually reached (so it is possible that the output is empty). You must write one query for this exercise. It is not allowed first to look up the maximal points and then put this value into the query.

    b)

What is the average percentage reached for Homework 2?

    c)

How many students have done any homework?

    d)

Print for every student (first name, last name) the total number of points he/she got for the midterm exam.

    e)

Print for all students who have solved Homework 1-5 the total number of points for these homeworks. It might be that the database contains already a results for other homeworks (e.g. Homework 6).

Due Date:

Please submit your solution on next Wednesday (March 29) before the lecture. You may submit Part e) one week later.

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

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