-- start snippet recreate
/* code/sql/HW_ProfExampleRevisitedRevisited.sql */
DROP SCHEMA IF EXISTS HW_ProfExampleRevisited;

CREATE SCHEMA HW_ProfExampleRevisited;

USE HW_ProfExampleRevisited;

CREATE TABLE PROF (
  Login VARCHAR(25) PRIMARY KEY,
  NAME VARCHAR(25),
  Department CHAR(5)
);

CREATE TABLE DEPARTMENT (
  Code CHAR(5) PRIMARY KEY,
  NAME VARCHAR(25),
  Head VARCHAR(25),
  FOREIGN KEY (Head) REFERENCES PROF (LOGIN) ON UPDATE CASCADE
);

ALTER TABLE PROF
  ADD FOREIGN KEY (Department) REFERENCES DEPARTMENT (Code);

CREATE TABLE STUDENT (
  Login VARCHAR(25) PRIMARY KEY,
  NAME VARCHAR(25),
  Registered DATE,
  Major CHAR(5),
  FOREIGN KEY (Major) REFERENCES DEPARTMENT (Code)
);

CREATE TABLE GRADE (
  Login VARCHAR(25),
  Grade INT,
  PRIMARY KEY (LOGIN, Grade),
  FOREIGN KEY (LOGIN) REFERENCES STUDENT (LOGIN)
);

INSERT INTO DEPARTMENT
VALUES (
  'MATH',
  'Mathematics',
  NULL),
(
  'CS',
  'Computer
    Science',
  NULL);

INSERT INTO DEPARTMENT (
  Code,
  Name)
VALUES (
  'CYBR',
  'Cyber Secturity');

INSERT INTO PROF (
  LOGIN,
  Department,
  Name)
VALUES (
  'caubert',
  'CS',
  'Clément Aubert');

INSERT INTO PROF (
  LOGIN,
  Name,
  Department)
VALUES (
  'aturing',
  'Alan Turing',
  'CS'),
(
  'perdos',
  'Paul
    Erdős',
  'MATH'),
(
  'bgates',
  'Bill Gates',
  'CYBR');

INSERT INTO STUDENT (
  LOGIN,
  Name,
  Registered,
  Major)
VALUES (
  'jrakesh',
  'Jalal Rakesh',
  DATE '2017-12-01',
  'CS'),
(
  'svlatka',
  'Sacnite Vlatka',
  '2015-03-12',
  'MATH'),
(
  'cjoella',
  'Candice Joella',
  '20120212',
  'CYBR'),
(
  'aalyx',
  'Ava Alyx',
  20121011,
  'CYBR'),
(
  'caubert',
  'Clément Aubert',
  NULL,
  'CYBR');

INSERT INTO GRADE
VALUES (
  'jrakesh',
  3.8),
(
  'svlatka',
  2.5);

-- end snippet recreate
-- start snippet lecture
CREATE TABLE HW_Lecture (
  NAME VARCHAR(25),
  Instructor VARCHAR(25),
  Year YEAR (4),
  Code CHAR(5),
  PRIMARY KEY (Year, Code),
  FOREIGN KEY (Instructor) REFERENCES PROF (LOGIN)
);

INSERT INTO HW_Lecture
VALUES (
  'Intro to CS',
  'caubert',
  2017,
  '1304'),
(
  'Intro
    to Algebra',
  'perdos',
  2017,
  '1405'),
(
  'Intro to
    Cyber',
  'aturing',
  2017,
  '1234');

-- end snippet lecture
ALTER TABLE GRADE
  ADD COLUMN LectureCode CHAR(5),
  ADD COLUMN LectureYear YEAR (4);

ALTER TABLE GRADE
  ADD FOREIGN KEY (LectureYear, LectureCode) REFERENCES
    HW_Lecture (Year, Code);

-- start snippet grade
DESCRIBE GRADE;

SELECT *
FROM GRADE;

-- end snippet grade
-- start snippet update
UPDATE
  GRADE
SET LectureCode = '1304',
  LectureYear = 2017
WHERE LOGIN = 'jrakesh'
  AND Grade = '2.85';

UPDATE
  GRADE
SET LectureCode = '1405',
  LectureYear = 2017
WHERE LOGIN = 'svlatka'
  OR (LOGIN = 'jrakesh'
    AND Grade = '3.85');

UPDATE
  GRADE
SET LectureCode = '1234',
  LectureYear = 2017
WHERE LOGIN = 'aalyx'
  OR LOGIN = 'cjoella';

-- end snippet update
-- start snippet select
SELECT LOGIN,
  Grade
FROM GRADE
WHERE Lecturecode = '1304'
  AND LectureYear = '2017';

SELECT DISTINCT Instructor
FROM HW_Lecture
WHERE Year = 2017;

SELECT Name,
  Grade
FROM STUDENT,
  GRADE
WHERE GRADE.LectureCode = 1405
  AND STUDENT.Login = GRADE.Login;

SELECT Year
FROM HW_Lecture
WHERE Code = '1234';

SELECT Name
FROM HW_Lecture
WHERE Year IN (
    SELECT Year
    FROM HW_Lecture
    WHERE CODE = '1234');

SELECT B.name
FROM STUDENT AS A,
  STUDENT AS B
WHERE A.Name = 'Ava Alyx'
  AND A.Registered > B.Registered;

SELECT COUNT(DISTINCT PROF.Name) AS 'Head Teaching This Year'
FROM HW_Lecture,
  DEPARTMENT,
  PROF
WHERE Year = 2017
  AND Instructor = Head
  AND Head = PROF.Login;

-- end snippet select