CREATE TEMPORARY TABLE IF NOT EXISTS School_2 (
    schoolCode VARCHAR(3),
    schoolName VARCHAR(30),
    CONSTRAINT School_schoolCode_pk PRIMARY KEY (schoolCode), 
    CONSTRAINT School_name_ck UNIQUE (schoolName)
);

SELECT *
FROM School_2;


CREATE TABLE IF NOT EXISTS School_3 
	SELECT * FROM School;
	
ALTER TABLE School_3
	ADD COLUMN building VARCHAR(30) AFTER schoolCode;
	
	
CREATE TABLE IF NOT EXISTS Grade_2
	SELECT g.*, 0 AS passing FROM Grade AS g;
	
UPDATE Grade_2
	SET passing = IF(gradePoint >= 2.5, 1, 0);
	
UPDATE student
SET major = 'CINF'
WHERE major IN ('CSCI', 'ITEC');

SELECT COUNT(*)
FROM student;

SET @countStudents = 0;


SELECT COUNT(*) INTO @countStudents
FROM student;

SELECT @countStudents := @countStudents + 1;

SET @row = 0;

SELECT @row := @row + 1 AS `row`, s.*
FROM student AS s;


SELECT *
FROM student
WHERE major IS NULL;



SELECT s.stuId, s.major -- [3] columns.
FROM student AS s  -- [1] 'universal bit table'
WHERE credits > 10  -- [2] row
;


SELECT s.major, s.stuId -- [4] columns; s.stuId semantically incorrect.
FROM student AS s  -- [1] 'universal bit table'
WHERE credits > 10  -- [2] row
GROUP BY s.major -- [3] one group for each s.major value.
;

SELECT s.major, COUNT(s.stuId) AS `Number of students`, -- [4] columns. COUNT: aggregate function
	MAX(credits) AS `Max # of credits`
FROM student AS s  -- [1] 'universal bit table'
WHERE credits > 10  -- [2] row
GROUP BY s.major -- [3] one group for each s.major value.
;

-- show only those with 2 or more majors.
SELECT s.major, COUNT(s.stuId) AS `Number of students`, -- [4] columns. COUNT: aggregate function
	MAX(credits) AS `Max # of credits`
FROM student AS s  -- [1] 'universal bit table'
WHERE credits > 10,  -- [2] row
	`Number of students` >= 2  -- error: no group yet.
GROUP BY s.major -- [3] one group for each s.major value.
;

-- show only those with 2 or more majors.
SELECT s.major, COUNT(s.stuId) AS `Number of students`, -- [4] columns. COUNT: aggregate function
	MAX(credits) AS `Max # of credits`
FROM student AS s  -- [1] 'universal bit table'
WHERE credits > 10  -- [2] row
GROUP BY s.major -- [3] one group for each s.major value.
HAVING `Number of students` >= 2  -- error: no group yet.
;

SELECT DISTINCT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student,
	d.deptName AS major
FROM Student AS s INNER JOIN department AS d ON (s.major = d.deptCode);
-- miss Ben ZIco: null in major.


SELECT DISTINCT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student,
	IF(ISNULL(d.deptName), 'Undeclared', d.deptName) AS major
FROM Student AS s LEFT JOIN department AS d ON (s.major = d.deptCode);




SELECT s.major AS deptCode, COUNT(s.stuId) AS `Number of majors`, -- [4] columns. COUNT: aggregate function
	MAX(credits) AS `Max # of credits`
FROM student AS s  -- [1] 'universal bit table'
WHERE credits > 10  -- [2] row
AND s.major IS NOT NULL
GROUP BY s.major;

SELECT d1.deptCode, d1.deptName, temp.`Number of majors`, temp.`Max # of credits`
FROM (SELECT s.major AS deptCode, COUNT(s.stuId) AS `Number of majors`, -- [4] columns. COUNT: aggregate function
		MAX(credits) AS `Max # of credits`
	  FROM student AS s  -- [1] 'universal bit table'
	  WHERE credits > 10  -- [2] row
      AND s.major IS NOT NULL
      GROUP BY s.major) AS temp, -- Step [1]: subquery
	  department AS d1
WHERE d1.deptCode = temp.deptCode;
	  
WITH temp AS
	(SELECT s.major AS deptCode, COUNT(s.stuId) AS `Number of majors`, -- [4] columns. COUNT: aggregate function
		MAX(credits) AS `Max # of credits`
	  FROM student AS s  -- [1] 'universal bit table'
	  WHERE credits > 10  -- [2] row
      AND s.major IS NOT NULL
      GROUP BY s.major) -- step [1]
SELECT d1.deptCode, d1.deptName, temp.`Number of majors`, temp.`Max # of credits`
FROM temp INNER JOIN department AS d1 ON (d1.deptCode = temp.deptCode);