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 courses

SELECT s.student_id
FROM students s
WHERE
(
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 enrolled

SELECT s.student_id
FROM students s
WHERE 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 courses

CREATE TYPE list_type AS TABLE OF INTEGER;
/

SELECT s.student_id
FROM students s
WHERE

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 courses

SELECT s.student_id
FROM students s
WHERE 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
);

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy