ZIP

   CS348- Project1Solved

30.00 $

Category:
Click Category Button to View Your Next Assignment | Homework

You'll get a download link with a: zip solution files instantly, after Payment

Securely Powered by: Secure Checkout

Description

5/5 - (4 votes)

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 sql and data.sql, respectively.
  2. You should finish all your work in sql.
  3. We provide 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 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. “./test.sh” test all 10 queries;
    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 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

 

  1. (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

 

  1. (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

 

  1. (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

 

  1. (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

 

  1. (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

 

  1. (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

 

  1. (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

 

  1. (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

 

  1. (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……..

 

 

  • proj1-vi3iok.zip

Related products