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.
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@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.
Comments