/* code/sql/HW_ProfExample.sql */
DROP SCHEMA IF EXISTS HW_ProfExample;

CREATE SCHEMA HW_ProfExample;

USE HW_ProfExample;

-- start snippet tables-1
/* code/sql/HW_ProfExample.sql */
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);

-- end snippet tables-1
-- start snippet tables-2
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)
);

-- end snippet tables-2
-- start snippet insert-1
INSERT INTO DEPARTMENT
VALUES (
  "MATH",
  "Mathematics",
  NULL),
(
  "CS",
  "Computer
    Science",
  NULL);

-- end snippet insert-1
-- start snippet insert-2
INSERT INTO DEPARTMENT (
  Code,
  Name)
VALUES (
  "CYBR",
  "Cyber Secturity");

-- end snippet insert-2
-- start snippet insert-3
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 insert-3
-- start snippet select-update
SELECT LOGIN
FROM STUDENT;

UPDATE
  DEPARTMENT
SET Head = "aturing"
WHERE Code = "MATH";

UPDATE
  DEPARTMENT
SET Head = "bgates"
WHERE Code = "CS"
  OR Code = "CYBR";

SELECT LOGIN
FROM STUDENT
WHERE NOT Major = "CYBR";

SELECT LOGIN,
  Name
FROM PROF
WHERE Department = "CS";

SELECT LOGIN
FROM STUDENT
WHERE Major = "CYBR"
  AND Registered > DATE "20121001";

SELECT LOGIN
FROM STUDENT
WHERE Name LIKE "Ava%";

SELECT Name
FROM PROF
WHERE LOGIN LIKE "_aubert";

-- end snippet select-update
-- start snippet null
INSERT INTO DEPARTMENT
VALUES (
  "Hist",
  "History",
  NULL);

SELECT *
FROM DEPARTMENT
WHERE Head IS NULL;

SELECT *
FROM DEPARTMENT
WHERE Head IS NOT NULL;

SELECT COUNT(*)
FROM GRADE
WHERE Grade IS NULL;

-- end snippet null
-- start snippet order-by
SELECT LOGIN
FROM GRADE
WHERE Grade > 2.0
ORDER BY Grade;

SELECT LOGIN
FROM GRADE
WHERE Grade > 2.0
ORDER BY Grade DESC;

SELECT LOGIN,
  Major
FROM STUDENT
ORDER BY Major,
  Name;

-- end snippet order-by
-- start snippet select-project-join-1
SELECT LOGIN
FROM PROF,
  DEPARTMENT
WHERE DEPARTMENT.Name = "Mathematics"
  AND Department = Code;

-- end snippet select-project-join-1
--		    start snippet select-project-join-2
SELECT Name
FROM STUDENT,
  GRADE
WHERE Grade > 3.0
  AND STUDENT.Login = GRADE.Login;

-- end snippet select-project-join-2
--		    start snippet select-project-join-3
SELECT PROF.Name
FROM PROF,
  DEPARTMENT,
  STUDENT
WHERE STUDENT.Name = "Ava Alyx"
  AND STUDENT.Major = DEPARTMENT.Code
  AND DEPARTMENT.Head = PROF.Login;

-- end snippet select-project-join-3
-- start snippet alias-1
SELECT PROF.Name
FROM PROF,
  DEPARTMENT,
  STUDENT AS B
WHERE B.Name = "Ava Alyx"
  AND B.Major = DEPARTMENT.Code
  AND DEPARTMENT.Head = PROF.Login;

-- end snippet alias-1
-- start snippet alias-2
SELECT A.Name
FROM PROF AS A,
  DEPARTMENT AS B,
  STUDENT AS C
WHERE C.Name = "Ava Alyx"
  AND C.Major = B.Code
  AND B.Head = A.Login;

-- end snippet alias-2
-- start snippet alias-3
SELECT Other.Login
FROM GRADE AS Mine,
  GRADE AS Other
WHERE Mine.Login = "aalyx"
  AND Mine.Grade < Other.Grade;

-- end snippet alias-3
-- start snippet alias-4
SELECT JOINT.Login
FROM PROF AS PROJECT,
  PROF AS JOINT
WHERE PROJECT.Login = "caubert"
  AND PROJECT.Department = JOINT.Department;

-- end snippet alias-4
-- start snippet alias-5
SELECT Fellow.Name AS "Fellow of Ava"
FROM STUDENT AS Ava,
  STUDENT AS Fellow
WHERE Ava.Name = "Ava Alyx"
  AND Fellow.Major = Ava.Major
  AND NOT Fellow.Login = Ava.Login;

-- end snippet alias-5
-- start snippet nested-1
SELECT LOGIN
FROM GRADE
WHERE Grade > (
    SELECT AVG(Grade)
    FROM GRADE);

-- end snippet nested-1
-- start snippet nested-2
SELECT LOGIN
FROM GRADE
WHERE Grade >= ALL (
    SELECT Grade
    FROM GRADE
    WHERE Grade IS NOT NULL);

-- end snippet nested-2
-- start snippet max
SELECT LOGIN
FROM GRADE
WHERE Grade >= (
    SELECT MAX(Grade)
    FROM GRADE);

-- end snippet max
-- start snippet whodunit
SELECT LOGIN
FROM PROF
WHERE DEPARTMENT IN (
    SELECT Major
    FROM STUDENT
    WHERE LOGIN LIKE "%a");

-- end snippet whodunit
-- start snippet transf-1a
SELECT LOGIN
FROM PROF
WHERE DEPARTMENT = (
    SELECT Major
    FROM STUDENT
    WHERE LOGIN = "cjoella");

-- end snippet transf-1a
-- start snippet transf-1b
SELECT PROF.Login
FROM PROF,
  STUDENT
WHERE DEPARTMENT = Major
  AND STUDENT.Login = "cjoella";

-- end snippet transf-1b
-- start snippet transf-2a
SELECT Name
FROM STUDENT,
  GRADE
WHERE Grade > 3.0
  AND STUDENT.Login = GRADE.Login;

-- end snippet transf-2a
-- start snippet transf-2b
SELECT Name
FROM STUDENT
WHERE LOGIN IN (
    SELECT LOGIN
    FROM GRADE
    WHERE Grade > 3.0);

-- end snippet transf-2b