-- ITEC 3335 Fall 2019 Homework #1

-- Start with toyu.

-- Q1. Set the number of alerts (n_alerts) of every
-- student enrollment to NULL if it is 
-- originally 0.

SELECT * FROM Enroll;

UPDATE ENROLL
SET n_alerts = NULL
WHERE n_alerts = 0;

SELECT * FROM Enroll;

-- Q2. Three students (id, 100001, 100003 and 100005) are now 
-- enrolled in the class with id (11002). There are no grades 
-- or n_alerts yet. Provide the SQL statement to update toyu accordingly.

INSERT INTO Enroll(stuId, classId)
VALUES (100001, 11002), (100003, 11002), (100005, 11002);

SELECT * FROM enroll;

-- Q3. Provide the SQL statement to undo the effect of (2). 

DELETE FROM Enroll
WHERE classId = 11002
AND stuId in (100001, 100003, 100005);

SELECT * FROM enroll;

-- Q4. Create a temporary table student_2 that has all columns and
-- keys of the table student, plus two additional columns
-- advisor_deptCode and advisor_rank, which are the department
-- code and the rank of the student's advisor.

DROP TABLE IF EXISTS student_2;

-- Note that temporary table cannot have foreign key constraint.
CREATE TEMPORARY TABLE IF NOT EXISTS student_2(
    stuId        INT NOT NULL,
    fname        VARCHAR(20) NOT NULL,
    lname        VARCHAR(20) NOT NULL,
    major        VARCHAR(4) NULL,
    minor        VARCHAR(4) NULL,
    credits      integer(3) DEFAULT 0,
    advisor      INT NULL,
    advisor_deptCode    VARCHAR(4),
    advisor_rank        VARCHAR(25),
    CONSTRAINT Student_2_stuId_pk PRIMARY KEY(stuId),
    CONSTRAINT Student_2_credits_cc CHECK ((credits>=0) AND (credits < 250))
); 

-- If the table is permanent, then foreign constraints can
-- be added.
CREATE TABLE IF NOT EXISTS student_2(
    stuId        INT NOT NULL,
    fname        VARCHAR(20) NOT NULL,
    lname        VARCHAR(20) NOT NULL,
    major        VARCHAR(4) NULL,
    minor        VARCHAR(4) NULL,
    credits      integer(3) DEFAULT 0,
    advisor      INT NULL,
    advisor_deptCode    VARCHAR(4),
    advisor_rank        VARCHAR(25),
    CONSTRAINT Student_2_stuId_pk PRIMARY KEY(stuId),
    CONSTRAINT Student_2_credits_cc CHECK ((credits>=0) AND (credits < 250)),
    CONSTRAINT Student_2_major_fk FOREIGN KEY (major) 
        REFERENCES Department(deptCode) ON DELETE CASCADE,
    CONSTRAINT Student_2_minor_fk FOREIGN KEY (minor) 
        REFERENCES Department(deptCode) ON DELETE CASCADE,
    CONSTRAINT Student_2_advisor_fk FOREIGN KEY (advisor) 
        REFERENCES Faculty(facId),
    CONSTRAINT Student_2_advisor_deptCode_fk FOREIGN KEY (advisor_deptCode) 
        REFERENCES Department(deptCode) ON DELETE CASCADE       
); 

DESC student_2;

-- Q5. Populate student_2 using the contents of existing tables.

INSERT INTO student_2
SELECT s.stuId, s.fname, s.lname, s.major, s.minor, s.credits,
    s.advisor, f.deptCode AS advisor_deptCode, f.`rank` AS advisor_rank
FROM student s LEFT JOIN faculty f ON (s.advisor = f.facId);

SELECT * FROM student_2;

-- Q6. List the student names, their majors, minors, credits, their
-- advisor names for those majoring or minoring in ITEC.
-- Note the names of the result columns. 

SELECT DISTINCT s.stuId AS id,
    s.fname AS `first name`,
    s.lname AS `last name`,
    major,
    minor,
    credits,
    concat(f.fname, ' ', f.lname) AS `faculty advisor`
FROM student s LEFT JOIN faculty f ON (s.advisor = f.facId)
WHERE s.major = 'ITEC' or s.minor = 'ITEC';

-- Q7. List the names of the faculty who advise a CSCI 
-- major student in the following manner.

SELECT DISTINCT concat(f.fname, ' ', f.lname) AS `CSCI advisor`
FROM faculty f INNER JOIN student s ON (f.facId = s.advisor)
WHERE s.major = 'CSCI';

-- Q8. List the names of the faculty who do not advise a CSCI 
-- major student in the following manner.

SELECT DISTINCT concat(f.fname, ' ', f.lname) AS `Not CSCI advisor`
FROM faculty f
WHERE f.facId NOT IN
   (SELECT s.advisor
    FROM student s 
    WHERE s.major = 'CSCI');

-- Q9. List the student names, class names and their enrollment grades
-- for all CSCI classes. Show the results only for students majoring in
-- 'CSCI'. Show the result in ascending order of student names.

SELECT DISTINCT DISTINCT concat(s.fname, ' ', s.lname) AS student,
    co.name AS course,
    e.grade
FROM 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 co.rubric = 'CSCI'
AND s.major = 'CSCI'
ORDER BY student ASC;

-- Q10. List the names of the faculty who have taught both
-- 'Design of Database Systems' and 'Data Structures'.

SELECT DISTINCT concat(f.fname, ' ', f.lname) AS faculty
FROM faculty f INNER JOIN class c1 ON (f.facId = c1.facId)
    INNER JOIN course co1 ON (c1.courseId = co1.courseId)
    INNER JOIN class c2 ON (f.facId = c2.facId)
    INNER JOIN course co2 ON (c2.courseId = co2.courseId)
WHERE co1.name = 'Design of Database Systems'
AND co2.name = 'Data Structures';