Free NoCOUG passes for correct SQL riddle answers

From: kyle Hailey <kylelf_at_gmail.com>
Date: Fri, 9 Nov 2012 09:43:21 -0800
Message-ID: <CADsdiQjuxG9j0MLeYR98jdvzX2Tsk6wb3ARj+NipiHe45YcSsg_at_mail.gmail.com>

Next week on Nov 15, Tom Kyte and Oracle Aces Tim Gorman, Kellyn Pot'Vin, Ben Prusinski and myself will be talking at the NoCOUG conference.

http://www.technicalconferencesolutions.com/pls/caat/caat_abstract_reports.schedule?conference_id6

NoCOUG is offering free admission to the first 25 people who answer the following SQL riddle:

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_at_nocoug.org. The first 25 correct entries will receive a free admission code to the November 15 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
  );

Please forward this message to your friends and colleagues. Our conferences are suitable for anybody with an interest in Oracle Database.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 09 2012 - 18:43:21 CET