---------------------------------------------------------------------- -- -- -- Template file for Homework Assignment #2 (CS348 FALL 08) -- -- -- echo echo Assignment 2 for CS348 (Fall '08) echo -- -- -- NOTE: You MUST use this template for full credit -- -- Do NOT change the template UNLESS INSTRUCTED TO DO SO -- -- Read the instructions carefully -- -- -- -- Please provide the following information: -- echo echo NAME : echo STUDENT ID: echo E-MAIL : echo -- -- -- All solutions must have the following format: -- -- -- -- echo Query : -- -- echo query in English to be written in SQL (provided) -- -- -- -- -- -- Notes: -- -- 1) all the queries have to be formulated with respect to -- -- the "class" database -- -- 2) Do not forget to make a copy of the tables (copy.db2) -- -- 3) The completed assignment MUST be cleanly accepted by DB2 -- -- 4) You must "submit" the completed assignment on 10/28 -- -- 6) This is an INDIVIDUAL assignment -- -- -- ---------------------------------------------------------------------- echo Example Query: echo List descriptions of all assignments in the database: SELECT description \ FROM assignment ---------------------------------------------------------------------- -- -- -- Here is the assignment itself: -- -- -- ---------------------------------------------------------------------- echo Query 1: echo List Name and SIN of every student that didn't submit at echo least one assignment (including exams, etc). echo Query 2: echo List all students (names and SINs) who submitted all echo assignments (including midterm, projects, etc.) but failed echo to show up for the "Final". echo Query 3: echo List all students (names and SINs) who passed both "Midterm" echo and "Final" (i.e., got more than 50% of the grade) but haven't echo submitted any other assignments in the class. echo Query 4: echo Find all pairs of students whose grades were within 5% of echo each other on every assignment. Return pairs of SINs as an echo answer. echo Query 5: echo List all students who did better on the "Final" (description of echo an ASSIGNMENT) than on the "Midterm". In the answer echo include the student's name, SIN, and the difference of grades echo converted to scale 0-100. Note that the actual grades might echo have been assigned on different scales. echo Query 6: echo For each student (SIN) compute the final grade for the class. echo For missing assignments, etc., you can assume a grade of 0. echo Query 7: echo For each assignment (etc.) compute class average on the scale 0-100. echo List TID, DESCRIPTION and the average. The resulting table's echo attributes have to be named TID, DESC, and AVERAGE.