CREATE TABLE Taken (Student TEXT, Course TEXT);
INSERT INTO Taken values('Robert', 'Databases');
INSERT INTO Taken values('Robert', 'Programming Languages');
INSERT INTO Taken values('Susie', 'Databases');
INSERT INTO Taken values('Susie', 'Operating Systems');
INSERT INTO Taken values('Julie', 'Programming Languages');
INSERT INTO Taken values('Julie', 'Machine Learning');
INSERT INTO Taken values('Emilie', 'Operating Systems');

CREATE TABLE Required (Course TEXT);
INSERT INTO Required values('Databases');
INSERT INTO Required values('Programming Languages');

CREATE TABLE AllStudents AS 
SELECT Student
FROM Taken ;

CREATE TABLE StudentsAndRequired AS 
SELECT distinct AllStudents.student, Required.course
FROM AllStudents, Required ;

CREATE TABLE StudentsAndRequiredNotTaken AS
SELECT * FROM StudentsAndRequired
WHERE NOT EXISTS (SELECT *
FROM Taken
WHERE StudentsAndRequired.student = Taken.student AND StudentsAndRequired.Course = Taken.Course);

CREATE TABLE CannotGraduate AS
SELECT Student FROM StudentsAndRequiredNotTaken;

CREATE TABLE CanGraduate AS
SELECT * FROM AllStudents 
WHERE NOT EXISTS (SELECT *
FROM CannotGraduate
WHERE CannotGraduate.Student = AllStudents.Student);

/* Getting the result from the intermediate tables */
SELECT * FROM CanGraduate;    

/* Traditional version of Relational Division using nested subqueries and doubly negated EXISTS functions */
SELECT DISTINCT x.Student
FROM taken AS x
WHERE NOT EXISTS (
    SELECT *
    FROM required AS y
    WHERE NOT EXISTS (
        SELECT *
        FROM taken 
        WHERE (taken.Student=x.Student) 
        AND (taken.Course=y.Course)));


/* Traditional version using EXCEPT instead of NOT EXISTS */
SELECT Student FROM Taken
EXCEPT
SELECT Student FROM (
SELECT Student,Course
FROM (select Student FROM Taken), Required
EXCEPT
SELECT Student,Course FROM Taken);

/* Version of Relational Division using set membership, group-by, counting, and having */
SELECT Student 
From Taken
WHERE Course IN (SELECT Course FROM Required)
GROUP BY Student
HAVING COUNT(*) = (SELECT COUNT(*) FROM Required);

/*



SELECT DISTINCT Student 
FROM Taken
WHERE Course = 'Databases' 
   or Course = 'Programming Languages';

SELECT DISTINCT Student 
FROM Taken
WHERE Course IN (SELECT Course FROM Required);

SELECT DISTINCT x.Student
FROM taken AS x
WHERE NOT EXISTS (
    SELECT *
    FROM required AS y
    WHERE NOT EXISTS (
        SELECT *
        FROM taken 
        WHERE (taken.Student=x.Student) 
        AND (taken.Course=y.Course)));

*/
