SQL Riddle for Free NoCOUG admission Nov 15
Tom Kyte, Tim Gorman, Kellyn Pot’Vin, and Ben Prusinski are flying in to the Bay Area to present at NoCOUG Nov 15.
I’ll be presenting as well on how to make dozens of multi-terabyte database copies in seconds using various easily available technologies on the market now.
http://www.
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 . The first 25 correct entries will receive a free admission code to the November 15 conference.
(
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 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 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
);
Oracle L
Trackbacks
Comments