Home > NoCOUG, Oracle, SQL > Solve the SQL mini-challenge and attend the November 15 NoCOUG conference for free‏

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


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 sqlchallenge@nocoug.org. 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. In the first solution, we select those 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
  );

In the second solution, we use double negation to 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
    )
  );

In the third solution, we use object-relational techniques to select those 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). We select those 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
  );

Updated on November 15: A number of people sent in the “textbook” solution, formed by converting the previous correlated subquery into an uncorrelated subquery:

SELECT student_id
FROM students

MINUS

SELECT student_id
FROM
  (
    SELECT s.student_id, r.course_id
    FROM requirements r, students s
    WHERE r.major_id = s.major_id

    MINUS

    SELECT e.student_id, e.course_id
    FROM enrollments e, students s
    WHERE e.student_id = s.student_id
  );

Peter Johnson from Humboldt State University sent in the following clever solution:

SELECT student_id FROM
(
  SELECT s.student_id, count(r.course_id)
  FROM students s, 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
  GROUP BY s.student_id

  INTERSECT

  SELECT s.student_id, count(e.course_id)
  FROM students s, requirements e
  WHERE e.major_id = s.major_id
  GROUP BY s.student_id
);

Fergal Taheny from Ireland had another twist:

SELECT s.student_id
FROM students s
INNER JOIN requirements r
  ON (r.major_id = s.major_id)
LEFT OUTER JOIN enrollments e
  ON (e.student_id = s.student_id AND e.course_id = r.course_id)
GROUP BY s.student_id
HAVING count(r.course_id) = count(e.course_id);

P.S. All the presentations from the NoCOUG fall conference are available at http://www.nocoug.org/presentations.html. I highly recommend Kyle Hailey’s presentation on cloning. He covers “CloneDB,” a free utility included in Oracle Database 11g Release 2 which gives you the ability to create an unlimited number of virtual clone databases from a backup. You can now give every developer their own test database even if your test databases are very large. The developers won’t guess that they’re not using “real” databases because the clones behave exactly like real databases.It makes perfect sense to me because a backup is a snapshot of a database and the clone databases could be regarded as “writable snapshots.”

About these ads
Categories: NoCOUG, Oracle, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 713 other followers

%d bloggers like this: