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>