CIS 350 -- Assignments for Week 2

CIS 350 -- Assignments for Week 2 - Assignments for Week 2...

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

View Full Document Right Arrow Icon
Assignments for Week 2 Chapter Five Exercise 5.1 – Student( snum: integer, sname: string, major: string, level: string, age: integer) Class( name: string, meets at: string, room: string, fid: integer) Enrolled( snum: integer, cname: string) Faculty( fid : integer, fname: string, deptid: integer) The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class. Write the following queries in SQL. No duplicates should be printed in any of the answers. 1. Find the names of all Juniors (level = JR) who are enrolled in a class taught by Teach. 2. Find the age of the oldest student who is either a History major or enrolled in a course taught by I. Teach. 3. Find the names of all classes that either meet in room R128 or have five or more students enrolled. 4. Find the names of all students who are enrolled in two classes that meet at the same time. 5. Find the names of faculty members who teach in every room in which some class is taught. 6. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five. 7. For each level, print the level and the average age of students for that level. 8. For all levels except JR, print the level and the average age of students for that level. 9. For each faculty member that has taught classes only in room R128, print the faculty member’s name and the total number of classes she or he has taught. 10. Find the names of students enrolled in the maximum number of classes. 11. Find the names of students not enrolled in any class. 12. For each age value that appears in Students, find the level value that appears most often. For example, if there are more FR level students aged 18 than SR, JR, or SO students aged 18, you should print the pair (18, FR). Asnwer: 1. SELECT DISTINCT S.Sname FROM Student S, Class C, Enrolled E, Faculty F WHERE S.snum = E.snum AND E.cname = C.name AND C.fid = F.fid AND F.fname = ‘I.Teach’ AND S.level = ‘JR’ 2. SELECT MAX(S.age) FROM Student S WHERE (S.major = ‘History’) OR S.snum IN (SELECT E.snum FROM Class C, Enrolled E, Faculty F WHERE E.cname = C.name AND C.fid = F.fid AND F.fname = ‘I.Teach’ ) 3. SELECT C.name FROM Class C WHERE C.room = ‘R128’ OR C.name IN (SELECT E.cname FROM Enrolled E GROUP BY E.cname HAVING COUNT (*) > = 5) SQL: Queries, Constraints, Triggers 47 4. SELECT DISTINCT S.sname FROM Student S WHERE S.snum IN (SELECT E1.snum CIS – 350 Database Management – Week 2 |Martin Ramirez Student ID: 020027677 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
FROM Enrolled E1, Enrolled E2, Class C1, Class C2 WHERE E1.snum = E2.snum AND E1.cname <> E2.cname AND E1.cname = C1.name AND E2.cname = C2.name AND C1.meets at = C2.meets at) 5. SELECT DISTINCT F.fname FROM Faculty F WHERE NOT EXISTS (( SELECT * FROM Class C ) EXCEPT (SELECTC1.room FROM Class C1 WHERE C1.fid = F.fid )) 6. SELECT DISTINCT F.fname
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.

Page1 / 6

CIS 350 -- Assignments for Week 2 - Assignments for Week 2...

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