COMS4111, Spring 2013
Homework 2

• Due date: Tuesday March 5, 5:00pm.
• You should submit your answers electronically as described below. We will not accept hard copies.
• This is a homework assignment, not a project; you should work on this assignment individually.

This assignment uses a database with data about a university. The schema of the database is provided below. Keys are in bold face and underlined, field types are omitted; assume that appropriate foreign key constraints have been defined in the full version of the schema (for example, a dname in the prof table does indeed references a department that exists in the dept table):

• student(sid, sname, sex, age, year, gpa)
• dept(dname, numphds)
• prof(pname, dname)
• course(cno, cname, dname)
• major(dname, sid)
• section(dname, cno, sectno, pname)
• enroll(sid, grade, dname, cno, sectno)

We ask that you write the following queries in SQL. We do not care if your answer contains duplicate rows or not. Each question is worth 2 points.
 

1. Print the sid, name and gpa of students who have a gpa that is greater than the gpa of every student majoring in 'Computer Sciences'.                        
    
2. Print the sid, name and gpa of students with a single major in 'Civil Engineering' who have enrolled in at least one course offered by the 'Chemical Engineering' department and have not enrolled in courses offered by any other department besides the one the student is majoring in.
    
3. For each department with at least two professors working for the department, print the department name, the number of PhD students in the department, the number of professors working for the department, and the PhD students to professor ratio.
    
4. Rising stars. We want to collect statistics about rising stars: these are students who are younger than the average age of students majoring in the same department and who have a GPA that is at least 20% higher than the average GPA of students majoring in the same department.  For each department with at least 3 students majoring in the department, print the department name, the total number of students majoring in the department and the average age and average GPA of all students majoring in this department, followed by the same information for the rising stars of this department (i.e., the number of rising stars in the department as well as their average age and average GPA). Consider only those departments that have at least one rising star majoring in the department. 
    
5. Popular courses. Print the department name, course number, and course enrollment of each course that has an enrollment of at least 20% higher than the average enrollment of all courses offered by the same department. The enrollment of a course is the sum of the enrollment of all its sections. Note: for this query, you should consider all courses, including those with zero enrollment. [Hint: You will need to use outer joins for this query; see class notes on the subject and for more the Oracle's online manual on Outer Joins and Using Outer Joins: Examples.]
    

What to do

• Write one SQL query for each question above. 
• Run your SQL queries on one our Oracle servers (they are all identical) using the web interface that we have set up at
  
  http://w4111a.cs.columbia.edu/~hc2361/hw2.php
  http://w4111d.cs.columbia.edu/~hc2361/hw2.php
  http://w4111e.cs.columbia.edu/~hc2361/hw2.php
  http://w4111h.cs.columbia.edu/~hc2361/hw2.php
  
  
  
• The SQL interpreter in Oracle is not quite the same as the one described in the textbook. If the query you write is not accepted by the server, try different equivalent ways until you get one that works with Oracle. For example, Oracle doesn't accept EXCEPT, use MINUS instead. You can find the SQL commands supported by Oracle in the online manual.
• You will not be able to store your queries on the server in any way. You will have to cut and paste your queries and the query results into your submission (see next item).
• Do not spend time on nicely formating the resulting tables - we do not care and you will not be graded on this.

Submission Instructions

1. Use a single plain-text file to include your answers for each of the 5 questions. For each of the 5 questions, your answer should include  (a) the SQL query and (b) the results of the execution of the query on the Oracle server described above, which you should  cut and paste from the web interface into your plain-text file. Please format your submission so that each query is followed by its corresponding query results (i.e., <query1> <results1>; <query2> <results2>; ...; <query5> <results5>). 
2. Login to CourseWorks at https://courseworks.columbia.edu/ and select the site for our class.
3. From the left pane, select "Assignments" and then "HW2".
4. Go to the "Submission" section; copy and paste the contents of the plain-text file you created above in the "Assignment Text" text box.
5. Hit the "Submit" button.

Notes: The day/time of your submission is recorded in courseworks. You will not be able to delete your submission. However, you may revise and resubmit your solutions as many times as you like. We will grade the latest version only and use its timestamp to deduct grace late days, as appropriate. You should receive an email confirmation from courseworks after each submission; alternatively, you can see the status (date and time) of your submission at the top section of the assignment.