202. Get a list of all of the courses offered (by name), the
department that offered it (by DeptName) the semester they were offered (by
SemName), who taught it (by InstName), where it was offered (by BuildName and
RoomNumber), what the room capacity was, and what the department Cap was (while
you’re at it, clean up the labels a bit) (Remember, there were only seven
classes offered)
NOTE: Because the output tends to look a little sloppy, I spooled the output and did some reformatting
SQL> select
distinct class.classid,coursename as
"Course",
2 deptname as "Dept", semname
as "Semester",
3 instname as "Instructor",
buildname as "Building",
4 roomnum as "Room", capacity
as "Capacity", cap as "Cap"
5 from building, classrooms, semester,
course, instructor, class, department, enrollment
6 where semester = semID
7 and course.courseID = class.courseID
8 and course.depart = department.deptID
9 and building = buildid
10 and classroom = room
11 and class.instID = instructor.instID
12 and class.classID =
enrollment.classID;
CLASSID Course Dept Semester Instructor Building Room Capacity Cap
----------
------------- ---- -----------
---------- -------- ---- -------- ---
5000 Database IDS Fall 2002 Kirs
COBA 301 40
40
5001 Systems Analysis IDS Fall 2002 Udo
COBA 302 35
50
5002 Database IDS Fall 2002 Kirs
COBA 301 40
30
5003 DataStructures IDS Fall 2002 Kirs
COBA 303 35 35
5004 Database IDS Spring 2003 Kirs
COBA 302 35
25
5005 Systems Analysis IDS Spring 2003 Bagchi
COBA 321 20
40
5006 Data Comm. IDS Spring 2003 Joseph
COBA 320 30
50
7 rows selected.
SQL>