-- 10/23/2019

SELECT DISTINCT s.*, e.*, c.*, co.*
FROM -- step #1
    student s INNER JOIN enroll e ON (s.stuId = e.stuId)
    INNER JOIN class c on (e.classId = c.classId)
    INNER JOIN course co on (c.courseId = co.courseId);


SELECT DISTINCT s.*, e.*, c.*, co.*  -- * wild card char
FROM -- step #1
    student s INNER JOIN enroll e ON (s.stuId = e.stuId)
    INNER JOIN class c on (e.classId = c.classId)
    INNER JOIN course co on (c.courseId = co.courseId)
WHERE -- step #2
    co.rubric = 'CSCI';

SELECT DISTINCT -- step #3
    s.stuId, co.name, e.grade
FROM -- step #1
    student s INNER JOIN enroll e ON (s.stuId = e.stuId)
    INNER JOIN class c on (e.classId = c.classId)
    INNER JOIN course co on (c.courseId = co.courseId)
WHERE -- step #2
    co.rubric = 'CSCI';

SELECT DISTINCT -- step #3
    s.stuId, s.credits, co.name, e.grade
FROM -- step #1
    student s INNER JOIN enroll e ON (s.stuId = e.stuId)
    INNER JOIN class c on (e.classId = c.classId)
    INNER JOIN course co on (c.courseId = co.courseId)
WHERE -- step #2
    co.rubric = 'CSCI';

SELECT DISTINCT -- step #3
    s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS student,
    s.credits + 10 AS `fake number of credits`, 
    co.name, e.grade
FROM -- step #1
    student s INNER JOIN enroll e ON (s.stuId = e.stuId)
    INNER JOIN class c on (e.classId = c.classId)
    INNER JOIN course co on (c.courseId = co.courseId)
WHERE -- step #2
    co.rubric = 'CSCI';
    
SELECT DISTINCT s.*, e.*
FROM -- step #1
    student s INNER JOIN enroll e ON (TRUE);
    
SELECT DISTINCT s.*, e.*
FROM -- step #1
    student s INNER JOIN enroll e ON (FALSE);
    
SELECT DISTINCT s.*, e.*
FROM -- step #1
    student s INNER JOIN enroll e ON (s.stuId = e.stuId);

CREATE TEMPORARY TABLE tem AS  -- no foreign key constraint
SELECT DISTINCT -- step #3
    s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS student,
    s.credits + 10 AS `fake number of credits`, 
    co.name, e.grade
FROM -- step #1
    student s INNER JOIN enroll e ON (s.stuId = e.stuId)
    INNER JOIN class c on (e.classId = c.classId)
    INNER JOIN course co on (c.courseId = co.courseId)
WHERE -- step #2
    co.rubric = 'CSCI';
    
SELECT * FROM tem;

SELECT * 
FROM tem
WHERE `fake number of credits` >= 50;


CREATE TABLE tem_1 AS
SELECT DISTINCT -- step #3
    s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS student,
    s.credits + 10 AS `fake number of credits`, 
    co.name, e.grade
FROM -- step #1
    student s INNER JOIN enroll e ON (s.stuId = e.stuId)
    INNER JOIN class c on (e.classId = c.classId)
    INNER JOIN course co on (c.courseId = co.courseId)
WHERE -- step #2
    co.rubric = 'CSCI';
    
-- house keeping 
DROP TABLE tem_1;

-- 8
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE e.classId = 10004;

SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE e.classId = 10000   -- 8a
AND s.stuId NOT IN 
		(SELECT DISTINCT e.stuId
		 FROM enroll e 
		 WHERE e.classId = 10004);  -- 8b
         
-- Q_X. Student names with a major in college CSE.

-- output: 
--    student: CONCAT(s.fname, ' ', s.lname) AS `student`
--    major: deptCode
-- source: student join department (major = deptCode)
-- condition: schoolCode = 'CSE';

SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`,
    s.major
FROM Student s INNER JOIN department d ON (s.major = d.deptCode)
WHERE d.schoolCode = 'CSE';

-- Q.X:
-- a. ALl deptCode in CSE: 4 rows: CINF, CSCI, ITEC, MATH
SELECT deptCode
FROM department
WHERE schoolCode = 'CSE';

-- b. Student name and major in Q.Xa
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`,
    s.major
FROM Student s
WHERE s.major IN 
    (SELECT deptCode
     FROM department
     WHERE schoolCode = 'CSE');

select distinct *
from Student
where lname = 'ng';  -- exact

select distinct *
from Student
where lname like 'ng'; -- inexact

select distinct *
from Student
where lname like '%ng%';

select distinct *
from Student
where lname REGEXP 'ng';