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

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

View Full Document Right Arrow Icon
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
Background image of page 1

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

View Full DocumentRight Arrow Icon
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;
Background image of page 2
Image of page 3
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 Right Arrow Icon
Ask a homework question - tutors are online