1 / 9

Midterm Queries and Announcements

This announcement includes queries on midterm, HW3 cancellation, project phase 3 release, SQL course and faculty management.

vidrine
Download Presentation

Midterm Queries and Announcements

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Announcements • End of SQL today (Hopefully) • Comments on midterm • HW3 called off • Project (phase 3) will be out today

  2. Midterm Queries Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu

  3. Department(ID, name, address) ----------------------------------------- short name D Faculty(ID, deptID, firstName, lastName, joinYear) ---------------- short name F Course(ID, deptID, name, numOfCredits) ----------------------------- short name C Student(ID, deptID, firstName, LastName, joinYear) --------------- short name S Teaching(FacultyID, courseID, Year) ------------------------------------ short name T Registration(studentID, courseID, Year, grade) --------------------- short name R (Q2) Report faculty members (Ids and names) who have taught courses outside their departments (that is, the course and the faculty belong to different departments) Select F.ID, F.firstName, F.lastName From T, C, F WhereT.facultyID = F.ID AndT.courseID = C.ID AndF.deptID<> C.deptID;

  4. Department(ID, name, address) ----------------------------------------- short name D Faculty(ID, deptID, firstName, lastName, joinYear) ---------------- short name F Course(ID, deptID, name, numOfCredits) ----------------------------- short name C Student(ID, deptID, firstName, LastName, joinYear) --------------- short name S Teaching(FacultyID, courseID, Year) ------------------------------------ short name T Registration(studentID, courseID, Year, grade) --------------------- short name R (Q3) Report the department DIs that have more than 20 faculty members and offer more than 10 courses with numOfCredits = 3. SelectdeptID From F Group By deptID Having count(*) > 20 Intersect SelectdeptID From C WherenumOfCredits = 3 Group By deptID Having count(*) > 10

  5. Department(ID, name, address) ----------------------------------------- short name D Faculty(ID, deptID, firstName, lastName, joinYear) ---------------- short name F Course(ID, deptID, name, numOfCredits) ----------------------------- short name C Student(ID, deptID, firstName, LastName, joinYear) --------------- short name S Teaching(FacultyID, courseID, Year) ------------------------------------ short name T Registration(studentID, courseID, Year, grade) --------------------- short name R (Q4) Report the distinct course IDs that have been taught in 3 consecutive years between 2000 and 2010 (inclusive). 2 years consecutive 3 years consecutive Select distinct t1.courseID From T t1, T t2 Where t1.courseID = t2.courseID And t1.year = t2.year -1 And t1.year >= 2000 And t2.year <= 2010; Select distinct t1.courseID From T t1, T t2 , T t3 Where t1.courseID = t2.courseID And t2.courseID = t3.courseID And t1.year = t2.year -1 And t2.year = t3.year -1 And t1.year >= 2000 And t3.year <= 2010;

  6. Department(ID, name, address) ----------------------------------------- short name D Faculty(ID, deptID, firstName, lastName, joinYear) ---------------- short name F Course(ID, deptID, name, numOfCredits) ----------------------------- short name C Student(ID, deptID, firstName, LastName, joinYear) --------------- short name S Teaching(FacultyID, courseID, Year) ------------------------------------ short name T Registration(studentID, courseID, Year, grade) --------------------- short name R (Q5) Report the course IDs that have more than 50 registered students in year 2010 SelectcourseID From R Where year = 2010 Group By course ID Having count(*) > 50;

  7. Department(ID, name, address) ----------------------------------------- short name D Faculty(ID, deptID, firstName, lastName, joinYear) ---------------- short name F Course(ID, deptID, name, numOfCredits) ----------------------------- short name C Student(ID, deptID, firstName, LastName, joinYear) --------------- short name S Teaching(FacultyID, courseID, Year) ------------------------------------ short name T Registration(studentID, courseID, Year, grade) --------------------- short name R (Q6) Update the join year for faculty ID = 12345 to be 2007. Update Faculty SetjoinYear = 2007 Where ID = 12345;

  8. Department(ID, name, address) ----------------------------------------- short name D Faculty(ID, deptID, firstName, lastName, joinYear) ---------------- short name F Course(ID, deptID, name, numOfCredits) ----------------------------- short name C Student(ID, deptID, firstName, LastName, joinYear) --------------- short name S Teaching(FacultyID, courseID, Year) ------------------------------------ short name T Registration(studentID, courseID, Year, grade) --------------------- short name R (Q9) Report the student names (first and last) registered in the last offering of course ID = ‘CS3431’ • Select S.firstName, S.lastName • From R, S • Where R.studentID = S.ID • And R.courseID = ‘CS3431’ • And R.year = ( • (Select max(year) • From R • Where courseID = ‘CS3431’); Get the max year Select max(year) From R Where courseID = ‘CS3431’

  9. Midterm Statistics

More Related