# Solve the SQL mini-challenge and attend the November 15 NoCOUG conference for free‏

 From: NoCOUG Sent on: Monday, November 12, 2012 11:34 AM

SQL comes in two distinct flavors—“relational calculus” and “relational algebra.” Without sweating the technical details, let’s just say that the relational calculus flavor is characterized by correlated subqueries—subqueries that refer to outside values—while the relational algebra flavor is characterized by set operations such as JOIN, UNION, MINUS, and INTERSECT. And, as you have probably noticed, these flavors are often mixed. The SQL mini-challenge is to use the pure relational algebra flavor of SQL to list all students who have enrolled in all the courses required by their declared major. Here are the table definitions and sample data. Send your entry to [address removed]. The first 25 correct entries will receive a free admission code to the November 15 NoCOUG conference.

`CREATE TABLE students  (    student_id INTEGER NOT NULL,    major_id INTEGER NOT NULL,    CONSTRAINT students_pk      PRIMARY KEY (student_id)  );INSERT INTO students VALUES (1, 1);INSERT INTO students VALUES (2, 1);INSERT INTO students VALUES (3, 1);INSERT INTO students VALUES (4, 1);CREATE TABLE requirements  (    major_id INTEGER NOT NULL,    course_id INTEGER NOT NULL,    CONSTRAINT requirements_pk      PRIMARY KEY (major_id, course_id)  );INSERT INTO requirements VALUES (1, 1);INSERT INTO requirements VALUES (1, 2);CREATE TABLE enrollments  (    student_id INTEGER NOT NULL,    course_id INTEGER NOT NULL,    CONSTRAINT enrollments_pk      PRIMARY KEY (student_id, course_id),    CONSTRAINT enrollments_fk1      FOREIGN KEY (student_id) REFERENCES students  );INSERT INTO enrollments VALUES (1, 1);INSERT INTO enrollments VALUES (1, 2);INSERT INTO enrollments VALUES (2, 1);INSERT INTO enrollments VALUES (3, 3);INSERT INTO enrollments VALUES (4, 1);INSERT INTO enrollments VALUES (4, 3);`

Here are three solutions using the relational calculus flavor of SQL.

`-- Select students for whom the count of enrolled required courses equals the count of required coursesSELECT s.student_idFROM students sWHERE  (    SELECT COUNT(*)    FROM requirements r, enrollments e    WHERE r.major_id = s.major_id    AND e.student_id = s.student_id    AND e.course_id = r.course_id  ) =  (    SELECT COUNT(*)    FROM requirements r    WHERE r.major_id = s.major_id  );-- Use double negation-- Select students such that there does not exist a required course in which they have not enrolledSELECT s.student_idFROM students sWHERE NOT EXISTS  (    SELECT *    FROM requirements r    WHERE r.major_id = s.major_id    AND NOT EXISTS    (      SELECT *      FROM enrollments e      WHERE e.student_id = s.student_id      AND e.course_id = r.course_id    )  );-- Use object-relational techniques-- Select students for whom the set of required courses is a subset of the set of enrolled coursesCREATE TYPE list_type AS TABLE OF INTEGER;/SELECT s.student_idFROM students sWHERE  CAST(MULTISET(    SELECT r.course_id    FROM requirements r    WHERE r.major_id = s.major_id  ) AS list_type)  SUBMULTISET OF  CAST(MULTISET(    SELECT e.course_id    FROM enrollments e    WHERE e.student_id = s.student_id  ) AS list_type);`

Here is a solution that uses a mixed flavor of SQL. Notice the use of the MINUS operation.

`-- Select students for whom the set of required courses is a subset of the set of enrolled coursesSELECT s.student_idFROM students sWHERE NOT EXISTS  (    SELECT r.course_id    FROM requirements r    WHERE r.major_id = s.major_id    MINUS    SELECT e.course_id    FROM enrollments e    WHERE e.student_id = s.student_id  );`

### San Jose, CA

Founded Jun 14, 2012

#### People in this Meetup are also in:

• ##### Silicon Valley Hands On Programming Events

5,438 Members

• ##### Silicon Valley Cloud Computing

8,690 Cloudsters

• ##### Hive User Group Meeting

1,302 Hive Users

901 Members

• ##### Silicon Valley Machine Learning

7,876 Data Coders

• ##### Silicon Valley NewTech (SVNewTech)

13,804 SVNewTech'rs