/*
Example Queries for CS360

NOTE: These queries are designed to be run with
The Student, Course, Schedule tables whose initializtion script
is demos/StudentCourseScheduleDML.sql.

Cut and paste individual examples into mysqlWorkbench after
initializing the tables
*/

-- eliminate duplicate rows in the output
-- find Course id's that are present in Schedule 
-- try removing keyword DISTINCT and see what happens
SELECT DISTINCT crn
FROM Schedule
WHERE crn = 1;

-- Aggregate function to sum Course credits:
SELECT Sum(credits) AS TotalCredits
FROM Course
WHERE name like '%CS%';

-- an Enumeration of literal values:
SELECT *
FROM Schedule
WHERE id in (1, 2);

-- an Enumeration whose values are formed by doing a subquery:
SELECT *
FROM Schedule
WHERE crn in
	(Select crn
	from Course
	Where name like '%7%');

-- make groups of records using group by
-- and perform an aggregate function on each of the groups
SELECT crn, Count(crn) AS numberEnrolled
FROM Schedule
WHERE crn<2
GROUP BY crn;

-- JOINS:
-- Course join Schedule
SELECT C.*, H.*
FROM Course AS C, Schedule AS H
WHERE C.crn = H.crn;

-- Student join Schedule
SELECT S.*, H.*
FROM Student AS S, Schedule AS H
WHERE S.id=H.id;

-- Joining more than 2 tables
-- Underneath, you are always joining two tables, 
-- then joining that result to another table.
-- See Join ON syntax below
SELECT S.name, C.name
FROM Student AS S, Schedule AS H, Course AS C
WHERE S.id = H.id and C.crn = H.crn;

-- Join ON syntax -- longer, but more clear as to what is happening
SELECT S.name, C.name
FROM (Student AS S INNER JOIN Schedule AS H ON S.id = H.id)
	INNER JOIN Course AS C ON C.crn = H.crn;


