Solutions Chapter 9 | Oracle SQL Interactive Workbook, 2nd Edition

Solutions Chapter 9: Complex Joins Test Your Thinking

Write a query that shows all the instructors that live in the same zip code.

Solution:

SELECT DISTINCT i1.first_name, i1.last_name, i1.zip
  FROM instructor i1, instructor i2
 WHERE i1.zip = i2.zip
   AND i1.instructor_id <> i2.instructor_id
 ORDER BY i1.zip
FIRST_NAME LAST_NAME       ZIP
---------- --------------- -----
Rick       Chow            10015
Fernand    Hanks           10015
Anita      Morris          10015
Charles    Lowry           10025
Nina       Schorin         10025
Todd       Smythe          10025
Tom        Wojick          10025

7 rows selected.

Or the query can also be written as an ANSI join as follows:

SELECT DISTINCT i1.first_name, i1.last_name, i1.zip
  FROM instructor i1 JOIN instructor i2
    ON (i1.zip = i2.zip)
 WHERE i1.instructor_id <> i2.instructor_id
 ORDER BY i1.zip

Note: You can also move the WHERE conditions into the ON clause and it will yield the same result as they are all AND conditions that need to be met for the records to be returned in the result set.

Are any of the rooms overbooked? Determine if any sections meet at the same date, time, and location.

Solution:

SELECT DISTINCT s.section_id,
       TO_CHAR(s.start_date_time, 'DD-MON-YYYY HH24:MI'),
       s.location
  FROM section s, section b
 WHERE s.location = b.location
   AND s.start_date_time = b.start_date_time
   AND s.section_id <> b.section_id
 ORDER BY 2, 3
SECTION_ID TO_CHAR(S.START_D LOCAT
---------- ----------------- -----
       128 09-APR-1999 09:30 L214
       132 09-APR-1999 09:30 L214
       101 16-APR-1999 09:30 L509
       140 16-APR-1999 09:30 L509

4 rows selected.

Alternatively, the query can be written as follows:

SELECT section_id, TO_CHAR(start_date_time,
       'DD-MON-YYYY HH24:MI'), location
  FROM section
 WHERE (location, start_date_time) IN
       (SELECT location, start_date_time
          FROM section
         GROUP BY start_date_time, location
        HAVING COUNT(*) > 1)

Determine if there is any scheduling conflict between instructors: Are any instructors scheduled to teach one or more sections at the same date and time? Order the result by the INSTRUCTOR_ID and the starting date and time of the sections.

Solution:

SELECT DISTINCT s1.instructor_id,
       TO_CHAR(s1.start_date_time, 'DD-MON-YYYY HH24:MI'),
       s1.section_id
  FROM section s1, section s2
 WHERE s1.instructor_id = s2.instructor_id
   AND s1.start_date_time = s2.start_date_time
   AND s1.section_id <> s2.section_id
INSTRUCTOR_ID TO_CHAR(S1.START_ SECTION_ID
------------- ----------------- ----------
          101 16-APR-1999 09:30        101
          101 16-APR-1999 09:30        140
          102 04-MAY-1999 09:30         88
          102 04-MAY-1999 09:30        149
          103 14-JUL-1999 09:30        107
          103 14-JUL-1999 09:30        119
          103 15-MAY-1999 09:30         89
          103 15-MAY-1999 09:30        150
          103 24-JUL-1999 09:30         81
          103 24-JUL-1999 09:30        127
          103 24-JUL-1999 09:30        142
          104 12-JUN-1999 09:30         90
          104 12-JUN-1999 09:30        151
          105 07-MAY-1999 09:30         97
          105 07-MAY-1999 09:30        129
          107 07-MAY-1999 09:30         99
          107 07-MAY-1999 09:30        115
          107 21-MAY-1999 09:30        138
          107 21-MAY-1999 09:30        154
          108 09-JUN-1999 09:30        100
          108 09-JUN-1999 09:30        139

21 rows selected.

Alternatively, you can write the SQL statement as follows:

SELECT instructor_id, start_date_time, section_id
  FROM section
 WHERE (instructor_id, start_date_time) IN
       (SELECT instructor_id, start_date_time
          FROM section
         GROUP BY instructor_id, start_date_time
        HAVING COUNT(*) > 1)

Show the course number, description, course cost, and section ID for courses that cost 1195 or more. Include courses that have no corresponding section.

Solution:

SELECT c.course_no, description, section_id, cost
  FROM course c LEFT OUTER JOIN section s
    ON (c.course_no = s.course_no)
 WHERE cost >= 1195
 ORDER BY 1
COURSE_NO DESCRIPTION               SECTION_ID      COST
--------- ------------------------- ---------- ---------
       10 DP Overview                       80      1195
...
       80 Structured Programming Te                 1595
          chniques
      100 Hands-On Windows                 141      1195
...
      124 Advanced Java Programming        126      1195
      124 Advanced Java Programming        127      1195
...
      430 JDeveloper Techniques                     1195

71 rows selected.

Note courses 80 and 430 do not have a corresponding section assigned.

Or you can write the query using the traditional syntax, with the comma between the tables in the FROM clause.

SELECT c.course_no, description, section_id, cost
  FROM course c, section s
 WHERE c.course_no = s.course_no(+)
   AND cost >= 1195
 ORDER BY 1

Write a query that lists the section numbers and students IDs of students enrolled in classes held in location 'L210'. Include sections for which no students are enrolled.

Solution:

SELECT s.section_id, e.section_id, e.student_id
  FROM section s LEFT OUTER JOIN enrollment e
    ON s.section_id = e.section_id
 WHERE location = 'L210'
SECTION_ID SECTION_ID STUDENT_ID
---------- ---------- ----------
        81         81        103
        81         81        104
        81         81        240
        84         84        158
...
       124
       129
...
       155        155        248
       155        155        241
       155        155        127

31 rows selected.

You can also write the query as follows:

SELECT s.section_id, e.section_id, e.student_id
  FROM section s, enrollment e
 WHERE location = 'L210'
   AND s.section_id = e.section_id(+)

Solutions Chapter 9: Complex Joins Test Your Thinking

Select Another Chapter