#Written by Jen Chen
#Copyrighted by Jen Chen


#############################
Data base: CS122
#############################

select ISBN,A.AuID,A.AuName from Book_AuthorJC BA LEFT JOIN AuthorsJC A
ON
BA.AuID like concat(A.AuID,'_')


#  Create a temporary table from the 2 tables AuthorsJC and Book_AuthorJC
#with the following constraints.
#  Note that in Mysql, a temporary table exits only for the current session. A temporary table
#will be deleted from the memory once that session ends.
#  Also note that Mysql does not display a temporary table even if the user issue the sql
#command "show tables".

create temporary table JC3 AS
select ISBN,count(A.AuID) AS TTL,A.AuID,A.AuName from Book_AuthorJC BA LEFT JOIN AuthorsJC A
ON
BA.AuID like concat(A.AuID,'_')
group by A.AuID


select AuID, AuName from JC3
    where
    TTL = (select MAX(TTL) from JC3)
    
    
===============================
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