cs411-sp07-hw2-part2-sol

# cs411-sp07-hw2-part2-sol - CS411 Database Systems Spring...

This preview shows pages 1–3. Sign up to view the full content.

CS411 Database Systems Spring 2007 HW #2 - Part 2 Due: 1:55pm CST, 03/06/07 Problem 5 SQL ( 30 points, 6 points each ) Consider the following relations: Students(sid , sname, department) Courses(cid , cname, department) Register(sid , cid , semester , year , grade) Express the following queries in SQL . (5.1) Find the names of all CS students; Answer: SELECT sname FROM Students WHERE department = ’CS’; (5.2) For each course, show its total number of registrations in “Spring” semester of 2006; Answer: SELECT cid, count(*) FROM Register WHERE semester=’Spring’ AND year=2006 GROUP BY cid; or SELECT cid, cname, count(*) FROM Course, Register WHERE Courses.cid=Register.cid AND semester=’Spring’ AND year=2006 GROUP BY Courses.cid, cname; or SELECT cid, count(*) FROM Course, Register WHERE Courses.cid=Register.cid AND semester=’Spring’ AND year=2006 GROUP BY Courses.cid; (5.3) Find the courses with the highest total number of students from departments di±erent from where the course is o±ered; Answer: 1

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
SELECT Courses.cid, Courses.cname, COUNT(DISTINCT Students.sid) AS count1 FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department <> Students.department GROUP BY Courses.cid, Courses.cname HAVING count1 = SELECT MAX(count2) FROM (SELECT COUNT(DISTINCT Students.sid) AS count2 FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department <> Students.department GROUP BY Courses.cid ); or SELECT Courses.cid, Courses.cname, COUNT(*) AS count1 FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department <> Students.department GROUP BY Courses.cid, Courses.cname HAVING count1 = SELECT MAX(count2) FROM (SELECT COUNT(*) AS count2 FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department <> Students.department GROUP BY Courses.cid ); Grading Notes: It is ok not to return Courses.cname. (5.4) Find the names of students who have taken courses in both CS and ECE;
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 09/14/2008 for the course CS 411 taught by Professor Winslett during the Spring '07 term at University of Illinois at Urbana–Champaign.

### Page1 / 7

cs411-sp07-hw2-part2-sol - CS411 Database Systems Spring...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online