Homework 2


Provide answers to the the following questions. If the question asks for an SQL query, give the query and the resulting table. If the table has more than 6 rows, just show the first 6. If you cannot figure out how to write a particular SQL query, you can get partial credit by writing a query that does part of the requested task. Per the norm, research on the internet is fine. But if you directly use information from the internet, properly cite your source.

You may submit your answers to the questions below as a single document or several documents. If there are several, be sure they are obviously named. All documents must be either in text of pdf format.

Part 1: Queries in the UNIV database

  1. Find the number of people who have taken each course in the comp sci department. Show only the course name and count. Show only the 2 most commonly taken courses. Sort this list by course name.
  2. What section of what course had the most students in fall of 2009?
  3. GPA at this university is defined to be function of the graded earned and the credits for each course. For example suppose, the grades earned in two classes are A and B, and those two classes are worth 2 and 5 credits respectively. Further suppose that A has a value of 4.0 and B has a value of 3.0. Then the GPA calculation is (4.0*2 + 3.0*5)/(2+5)=(8+15)/7=3.29 Show the name, student id and GPA of the three students that have the lowest GPA. (There is a grade_points table that gives letter grade to numeric mapping)
  4. Building from the previous query, in a single table, show the students with the three lowest, and 3 highest, GPAs. Order the resulting table by student name.
  5. Find the IDs of students who did not take a class before 2005 (but who have taken a course after 2005). Sort this list by ID and show only the top 5.
  6. Find the name of the instuructor and salary of the highest paid instructor in all departments such that the department name starts with A
  7. Find the name of the the instructor, and that person's salary and department, such that they have the highest salary in their department, but a lower salary than that of the highest paid person in any other department.
  8. Extra Credit: In the table definitions is buried a hint about what University this DB is based upon. Find the hint and name the university.

Part 2: Queries in the Rocket database

The questions below assume you are using the rocket3 database. (The rocket database does not have the launch_nonull table.) Both of the questions about this database require written answers, not just SQL queries.
  1. The following two queries both find all launches such that the launch apogee was higher than the launch apogee of any launch in 1957. The first is 15x slower than second!!! Explain.
                select apogee, date from launch where apogee > all ( select apogee from launch where date_part('year', date)=1957);
                select apogee, date from launch where apogee > ( select max(apogee) from launch where date_part('year', date)=1957);
  2. Consider a query in which you want to join the launch table to the launch_nonull table on the criteria launch_nonull.mission=launch.mission and launch_nonull.tag=launch.tag. Try every variation of join (inner join, natural join, cross join, right outer join, ....). Report the number of rows in the resulting table for each different join. Explain the differences between the sizes of the tables. (You may need to do some other queries to give good explanations.)

Part 3: Queries in the Hurricane database

  1. Find the first observation of the 5 most recent storms in the database whcih were not given a name (that is, their name is 'UNNAMED')
  2. Write a single query to recreate the observation table where longitude is negative in eastern hemisphere. That is eliminate the longitudehemi column and make any observation in which that column was 'E' have a value in the longitude column that is -1 times the original value
  3. Use the answer to the previous question to find the name, date, longitude and latitude of the easternmost observed storm in the database.

Part 4

Discuss: null values in a database require ternary logic; which is a pain. So why should you you allow them (give at least two reasons)? What are the alternatives (describe at least one)? Is your alternative to nulls better or worse, why?

How to submit

Use the process for submission from HW1 except put your document(s) in a directory named, for example, hw2. The execute
    /home/gtowell/bin/submit -c 383 -d hw2 -p 2 
There may be other versions of submit on the servers. Be sure to use mine. The arguments to submit have the following meanings: You may submit as many times as you like.