forked from rlawrenc/cosc_304
-
Notifications
You must be signed in to change notification settings - Fork 0
/
lab3_db_ques.txt
48 lines (24 loc) · 3.29 KB
/
lab3_db_ques.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Q1: Return all students (name only) with 'SO' or 'JR' standing and a 'Computer Science' major.
SELECT sname FROM student WHERE (standing = 'JR' OR standing = 'SO') AND major = 'Computer Science'
Q2: Return all courses (name and room only) taught by 'Richard Jackson'
SELECT * FROM faculty F, course C WHERE F.fid = C.fid and F.fname = 'Richard Jackson'
Q3: Return all students (number and name only) that are enrolled in a course offered in room 'R15' taught by a faculty member in department '68' and have 'SR' standing.
SELECT S.snum, sname FROM student S, course C, faculty F, enrolled E WHERE S.snum = E.snum AND C.cname = E.cname AND C.fid = F.fid AND C.room = 'R15' AND F.deptId = '68' AND S.standing = 'SR'
Q4: Return all courses that have 'Data' in their title. Sort them by course name.
SELECT * FROM course WHERE cname LIKE '%Data%' ORDER BY cname;
Q5: For each department, return its id and the number of courses offered by faculty associated with that department. Sort the list in descending order of number of courses offered. Rename this count field as 'CoursesOffered'.
SELECT deptId, COUNT(cname) AS CoursesOffered FROM faculty F, course C WHERE F.fid = C.fid GROUP by deptId ORDER BY CoursesOffered DESC
Q6: For each student, return their number, name, and number of courses they are enrolled in. Students must appear in the list even if they are not registered for any courses. Sort in descending order of the number of courses enrolled in.
SELECT S.snum, sname, COUNT(*) FROM student S LEFT OUTER JOIN enrolled E ON S.snum = E.snum GROUP BY S.snum, sname ORDER BY COUNT(*) DESC
Q7: Return a list of courses (name only and number of students enrolled) that are either offered by faculty in deptId = '11' OR by 'Ivana Teach' and have at least 2 students enrolled in them.
SELECT C.cname, COUNT(*) FROM course C, enrolled E, faculty F WHERE C.cname = E.cname AND C.fid = F.fid AND (F.deptId = '11' OR F.fname = 'Ivana Teach') GROUP BY C.cname HAVING COUNT(*) >= 2;
Q8: Return the students who are above the average student age.
SELECT * FROM student WHERE age > (SELECT AVG(age) FROM student)
Q9: Return all students that are not taking any courses.
SELECT * FROM student WHERE snum NOT IN (SELECT snum FROM enrolled)
Q10: Return all Computer Science' majors who are younger than all 'Psychology' majors.
SELECT * FROM student WHERE major = 'Computer Science' AND age < ALL (SELECT age FROM student WHERE major = 'Psychology')
My challenge: Return the maximum, minimum, and average number of courses students are enrolled in. For example, if one student is enrolled in 10 courses, that is the maximum. The minimum is 1 (not including 0 registrations). Take the average based on students who are enrolled in at least one course.
SELECT MAX(NumCourses), MIN(NumCourses), AVG(NumCourses) FROM (SELECT S.snum, sname, COUNT(*) as NumCourses FROM student S JOIN enrolled E ON S.snum = E.snum GROUP BY S.snum, sname) AS T
My challenge 2: Return all faculty members (fid, fname, and number of courses taught) who teach more courses than all other faculty. If more than 1 faculty member teaches the maximum, then show all that teach the maximum number of courses.
SELECT F.fid, F.fname, COUNT(*) FROM faculty F, course C WHERE C.fid = F.fid GROUP BY F.fid, F.fname HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM course GROUP BY fid)