-- 9/30





SELECT stuId
FROM enroll;


SELECT DISTINCT stuId
FROM enroll;

SELECT DISTINCT f.facId, f.lname
FROM faculty AS f
WHERE rank = 'Professor';

SELECT DISTINCT f.facId, f.lname
WHERE rank = 'Professor'
FROM faculty AS f;



DROP SCHEMA IF EXISTS tinker;
CREATE SCHEMA tinker;
USE tinker;

CREATE TABLE s2  -- creation + population.
SELECT * FROM toyu.student;

SELECT *
FROM s2;

CREATE TEMPORARY TABLE s3
SELECT * FROM toyu.student;

SELECT *
FROM s3;

-- CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 --   { LIKE old_tbl_name | (LIKE old_tbl_name) }
CREATE TABLE s4 LIKE toyu.student; -- creation of table

SELECT *
FROM s4;

INSERT INTO s4  -- population.
SELECT * FROM toyu.student;

SELECT *
FROM s4;

SHOW TABLES;

-- Note that keys and constraints of student are missing in s2 and S3.
DESC toyu.student;
DESC s2;
DESC s4;

DESC s3;

DROP TABLE s2;
DROP TABLE s3;
DROP TABLE s4;

SHOW TABLES;

DROP SCHEMA IF EXISTS tinker;

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
    
UPDATE Student
SET major = 'ITEC'
WHERE StuId = 100000;

UPDATE Student
SET major = 'CSCI';

-- student with credits in a range.
SELECT DISTINCT *
FROM Student
WHERE ach BETWEEN 30 AND 70;

SELECT DISTINCT *
FROM Student
WHERE ach BETWEEN 35 AND 66;


-- student with credits in a range.
SELECT DISTINCT *
FROM Student
WHERE ach>= 30 AND ach <= 70;

SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student AS s, enroll AS e, class AS c
WHERE s.stuId = e.stuId -- Join condition
AND e.classId = c.classId -- Join condition
AND c.semester = 'Fall' -- problem condition
AND c.year = 2019; -- problem condition

SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -- Join condition
    INNER JOIN class AS c ON (e.classId = c.classId) -- Join condition
WHERE c.semester = 'Fall' -- Problem condition
AND c.year = 2019; -- Problem condition

-- List the names of the students with their minors (in full name).
-- Student with no department not listed.
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`
FROM student AS s INNER JOIN department AS d ON (s.minor = d.deptCode);


SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`
FROM student AS s LEFT JOIN department AS d ON (s.minor = d.deptCode);


SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    IFNULL(d.deptName, 'Undeclared') AS `minor department`
FROM student AS s LEFT JOIN department AS d ON (s.minor = d.deptCode);