CS 122
Spring 2004
Lesson 4
Author: Jen Chen
Copyrighted by Jen Chen
===============================
Database:  Student.mdb
===============================
select  C.Course_Name, C.Course_Number, C.Credit_Hours, S.Semester, S.Year, 
        S.Instructor
from
   Course C,
   Section S
where
   C.Course_Number = S.Course_Num
   Order by C.Course_Name;
=============================================

/* Joining three tables. *./
select  C.Course_Name, C.Course_Number, C.Credit_Hours, S.Semester, S.Year, 
        S.Instructor, R.Bldg, R.Room
from
   Course C,
   Section S,
   Room R
where
   C.Course_Number = S.Course_Num
   AND S.BLDG = R.BLDG
   AND S.Room = R.Room
   Order by C.Course_Name;
=============================================

/* Left join */
select  C.Course_Name, C.Course_Number, C.Credit_Hours, S.Semester, S.Year, 
        S.Instructor
from
   Course C LEFT JOIN  Section S
ON
   C.Course_Number = S.Course_Num
   Order by C.Course_Name;
============================

select  C.Course_Name, C.Course_Number, C.Credit_Hours, S.Semester, S.Year, 
        S.Instructor
from
   Course C RIGHT JOIN  Section S
ON
   C.Course_Number = S.Course_Num
   Order by C.Course_Name;
===============================================

/* Note: Full Join = Left Join UNION Right Join.  */
select  C.Course_Name, C.Course_Number, C.Credit_Hours, S.Semester, S.Year, 
        S.Instructor
from
   Course C LEFT JOIN  Section S
ON
   C.Course_Number = S.Course_Num
   Order by C.Course_Name
UNION
select  C.Course_Name, C.Course_Number, C.Credit_Hours, S.Semester, S.Year, 
S.Instructor
from
   Course C RIGHT JOIN  Section S
ON
   C.Course_Number = S.Course_Num
   Order by C.Course_Name;
================================================

/* Task: Find all teachers who taugh in Fall 99 */
select  C.Course_Name, C.Course_Number, C.Credit_Hours, S.Semester, S.Year, 
        S.Instructor, R.BLDG, R.Room
from
   Section S,
   Course C,
   Room R
WHERE
   C.Course_Number = S.Course_Num
   AND  R.Room = S.Room
   AND S.Semester='Fall'
   Order by C.Course_Name;
================================================

select  C.Course_Name, C.Course_Number, C.Credit_Hours, S.Semester, S.Year, 
        S.Instructor, R.BLDG, R.Room
from
   Section S,
   Course C,
   Room R
WHERE
   C.Course_Number = S.Course_Num
   AND  R.Room = S.Room
   AND S.Semester='Fall'
   AND S.Instructor <> null
ORDER BY INSTRUCTOR;

================================================
/* Task: List the names of all instructors who taught in Fall '99. */

select  C.Credit_Hours, S.Instructor, S.Year, S.Semester
from
   Section S,
   Course C,
   Room R
WHERE
   C.Course_Number = S.Course_Num
   AND  R.Room = S.Room
   AND S.Semester='Fall'
   AND S.YEAR='99'
   AND S.Instructor <> null
   AND S.INSTRUCTOR IN (select S.Instructor from Section)
ORDER BY S.Instructor;

================================================

/* Task: Find the total number of units taugh by all instructor in Fall '99. */

select   S.Instructor, sum(C.Credit_Hours) AS [Total Units], S.Year, S.Semester
from
   Section S,
   Course C,
   Room R
WHERE
   C.Course_Number = S.Course_Num
   AND  R.Room = S.Room
   AND S.Semester='Fall'
   AND S.YEAR='99'
   AND S.Instructor <> null
   AND S.INSTRUCTOR IN (select S.Instructor from Section)
GROUP BY Instructor, Year, Semester

================================================