Project 1 Solution

Description

(There will be a 10% penalty for each late calendar day. After five calendar days, the project will not be accepted.)

Given the following entities, provide the SQL queries corresponding to the questions below:

Note:

1. The schema definition of these tables and sample test data are provided in tables.sql and data.sql, respectively.

2. You should finish all your work in answer.sql.

3. We provide test.sh script to test your sql queries on sample test data, but we will use different data when grading. Feel free to modify/add your sample test data in order to polish your queries. (You need to verify your answer by yourselves if you change the sample test data. In this case, test.sh may not work.)

4. How to use test.sh. First, set the variables “username” and “pword” in the script with your oracle account with “@csora” and your password. Second, execute the tables.sql and data.sql. Then

1. 1. “./test.sh” test all 10 queries;

1. 2. “./test.sh $(Query_number)” test one specific query. For example, “./test.sh 1”

5. Submit your answers via Blackboard.

6. Do not use PL/SQL for this homework, just a main SQL select statement per question (subqueries, i.e., nested queries, are allowed).

7. DO NOT delete/change the “– Query[0-9]*” comment in the answer.sql file.

8. Grading: We will use script to grade your projects. There will be no partial credit for each query.

9. For those “select top X” problems, assume that there is no tie condition that will influence the result. That is, the values in the column that may affect the result of selecting top X are distinct.

Write SQL queries for questions 1-10 using the following University schema.

Classroom(building, room_number, capacity)

Department(dept_name, building, budget)

Course(course_id, title, dept_name, credits)

Instructor(i_id, name, dept_name, salary)

Section(course_id, sec_id, semester, year, building, room_number, time_slot_id)

Teaches(i_id, course_id, sec_id, semester, year)

Student(s_id, name, dept_name, tot_cred)

Takes(s_id, course_id, sec_id, semester, year, grade)

Advisor(s_id, i_id)

Time_slot(time_slot_id, day, start_hr, start_min, end_hr, end_min)

Prereq(course_id, prereq_id)

Grade_points(grade, points)

1. (10 points) Find the s_ids and names of all students who were taught by an

instructor named ‘Katz’.

Output columns: Name

Sort by: Name in ascending order

S_ID Name

2. (10 points) Calculate the grade-point average of every student. Output columns: S_ID, GradePointAverage

Sort by: GradePointAverage in descending order

Note: The GradePointAverage should round up to 2 digits after decimal point. (e.g. 3.42857 should be shown as 3.43)

S_ID GradePointAverage

3. (10 pts) Find the enrollment of each section that was offered in the Fall of 2009. Display the Course_id, sec_id and the count (which is the number of students enrolled in this section.)

Output columns: Course_id, sec_id, Count

Sort by: Count in descending order

Course_id sec_id Count

4. (10 pts) Find the sections that had the maximum enrollment in the Fall of 2009.

(There could be more than one course section which has the maximum enrollment.) Output columns: Course_id, sec_id

Sort by: Course_id in ascending order

Course_id sec_id

5. (10 pts) Find the names of the top 4 instructors who have taught the most number of distinct courses. Display also the total number of courses taught.

Output columns: InstructorName, NumberOfCoursesTaught

Sort by: NumberOfCoursesTaught in descending order (in case of ties order by the InstructorName)

InstructorName NumberOfCoursesTaught

6. (10 pts) Find the top 3 semesters in which the most number of courses were offered. (Treat Spring of 2009 and Spring of 2010 as two different semesters)

Output columns: Semester, Year, NumberOfCourses

Sort by: NumberOfCourses in descending order

Semester Year NumberOfCourses

7. (10 pts) Find the top 2 students who have taken the most number of courses. Output columns: S_ID, StudentName, NumberOfCourses

Sort by: NumberOfCourses in descending order

S_ID StudentName NumberOfCourses

8. (10 pts) Find the top 4 instructors whose courses have the maximum enrollment in all of their courses combined.

Output columns: InstructorName, TotalEnrollment

Sort by: TotalEnrollment in descending order

InstructorName TotalEnrollment

9. (10 pts) List all the courses offered by the departments ‘Comp. Sci.’ and ‘History’. Output should not contain any duplicates.

Output columns: DepartmentName, CourseID

Sort by: CourseID in ascending order.

DepartmentName CourseID

10. (10 pts) List all the courses that have prerequisites offered by a different department.

Output columns: Course_id, Course_department, Prereq_id, Prereq_department

Sort by: Course_id in ascending order

Course_id Course_dept Prereq_id Prereq_dept

Submission instructions:

Please submit via Blackboard the following:

Your SQL script (answer.sql). It should contain the 10 SQL queries and look like the following:

-‐Query1

Select…….

………

— Query10

Select……..

A README file containing your first name, last name, and your Purdue email address.