
/*
 * Part I  Constructing and Populating a New Example
 */

-- DROP SCHEMA HW_UNIV;
CREATE SCHEMA HW_UNIV;

USE HW_UNIV;

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)
);

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');

/*
 * Part II - A First Look at Conditions
 */
 
UPDATE Department SET Head = 'aturing' WHERE Code = 'MATH';

UPDATE Department SET Head = 'bgates' WHERE Code = 'CS' OR Code = 'CYBR';

SELECT Login FROM STUDENT WHERE 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;

SELECT Login FROM STUDENT WHERE Name LIKE 'Ava%';

SELECT Name FROM PROF WHERE Login LIKE '_aubert';