## Solutions Chapter 9: Complex Joins Test Your Thinking

1. 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.

2. 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)
```
3. 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)
```
4. 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
```
5. 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(+)
```