-- Q7, F18
-- [1] Output columns:
-- 1.	stuId: student.stuId
-- 2.	fname: student.fname
-- 3.	lname: student.lname
-- 4.	deptName: department.deptName
-- 5.	adv. fname: faculty.fname
-- 6.	adv. lname: faculty.lname
SELECT DISTINCT -- 1. output column (SELECT CLAUSE)
    s.stuId, s.fname, s.lname, d.deptName,
    f.fname AS `adv. fname`,
    f.lname AS `adv. lname`
-- 1.	student
-- 2.	department
-- 3.	faculty

FROM  -- 2. source table instances (FROM CLAUSE)
    student AS s,
    department AS d,
    faculty AS f
-- 1.	Join conditions:
-- a.	Student.minor = department.deptCode (e.g. Tony Hawk: CINF)
-- b.	Student.advisor = faculty.facId
-- 2.	Problem conditions:
-- a.	majoring in CSCI: student.major = 'CSCI'

WHERE -- WHERE Clause: conditions
    s.minor = d.deptCode
AND S.advisor = f.facId
AND s.major = 'CSCI';

-- Q3.
-- 1.	Fname: student.fname
-- 2.	LName: student.lname
-- 3.	Major: department.deptName

--
SELECT DISTINCT -- 1. output column
    s.fname, s.lname,
    d.deptName AS major
-- [2]
-- 1.	Student
-- 2.	Department  
   
FROM student AS s,  -- alias
    department AS d
    -- 2. source
-- [3]    1.	Join conditions: 
        student.major = department.deptCode
        
-- 2.	Problem condition: minor in ITEC
-- a.	Student.minor = 'ITEC'
WHERE s.major = d.deptCode
AND s.advisor = f.facId
AND s.minor = 'ITEC';
-- 3. conditions

SELECT DISTINCT 
    s.stuId, s.fname, s.lname, d.deptName,
    f.fname AS `adv. fname`,
    f.lname AS `adv. lname`
FROM  
    student AS s,
    department AS d,
    faculty AS f
WHERE s.major = d.deptCode
AND s.advisor = f.facId
AND s.minor = 'ITEC';

SELECT DISTINCT -- step [3] get columns
    s.stuId, s.fname, s.lname, d.deptName,
    f.fname AS `adv. fname`,
    f.lname AS `adv. lname`
FROM  -- step [1] a big table
    student AS s INNER JOIN department AS d
        ON (s.major = d.deptCode)  -- join condition
    INNER JOIN faculty AS f
        ON (s.advisor = f.facId)
WHERE s.minor = 'ITEC'; -- [2] get rows

SELECT DISTINCT s.*, d.*, f.*  -- [3]
FROM  -- step [1]
    student AS s INNER JOIN department AS d
        ON (s.major = d.deptCode)  -- join condition
    INNER JOIN faculty AS f
        ON (s.advisor = f.facId);
        
        
SELECT DISTINCT s.*, d.*, f.*  -- [3]
FROM  -- step [1]
    student AS s INNER JOIN department AS d
        ON (s.major = d.deptCode)  -- join condition
    INNER JOIN faculty AS f
        ON (s.advisor = f.facId)
WHERE s.minor = 'ITEC'; -- [2] get rows

SELECT major FROM student;

SELECT DISTINCT major FROM student;