## Solutions Chapter 6: Equijoins Test Your Thinking

1. Select the course description, section number, and location for sections meeting in location L211.

Solution:

```SELECT description, section_no, location
FROM course c, section s
WHERE c.course_no = s.course_no
AND location = 'L211'
```

Using the ANSI JOIN syntax and the ON clause, it can also be written as:

```SELECT description, section_no, location
FROM course c JOIN section s
ON c.course_no = s.course_no
WHERE location = 'L211'
```

Or with the USING clause:

```SELECT description, section_no, location
FROM course c JOIN section s
USING (course_no)
WHERE location = 'L211'

DESCRIPTION                       SECTION_NO LOCAT
--------------------------------- ---------- -----
Project Management                         1 L211
JDeveloper                                 4 L211
Intermediate Java Programming              2 L211

3 rows selected.
```
2. Show the course description, section number, starting date and time of the courses Joseph German is taking.

Solution:

```SELECT description, section_no, start_date_time
FROM course c, section s, enrollment e, student s
WHERE c.course_no = s.course_no
AND s.section_id = e.section_id
AND e.student_id = s.student_id
AND s.last_name = 'German'
AND first_name = 'Joseph'
DESCRIPTION               SECTION_NO START_DAT
------------------------- ---------- ---------
Intro to Java Programming          2 24-JUL-99

1 row selected.
```

As always you can express this with the ANSI join syntax as follows which may look like this:

```SELECT c.description, s.section_no,
TO_CHAR(s.start_date_time, 'DD-MON-YYYY HH24:MI:SS')
FROM course c JOIN section s
ON (c.course_no = s.course_no)
JOIN enrollment e
ON (s.section_id = e.section_id)
JOIN student st
ON (e.student_id = st.student_id)
WHERE st.last_name = 'German'
AND st.first_name = 'Joseph'
```

Or you can write as follows with the USING clause.

```SELECT c.description, s.section_no,
TO_CHAR(s.start_date_time, 'DD-MON-YYYY HH24:MI:SS')
FROM course c JOIN section s
USING (course_no)
JOIN enrollment e
USING (section_id)
JOIN student st
USING (student_id)
WHERE st.last_name = 'German'
AND st.first_name = 'Joseph'
```
3. List the instructor ID, last name of the instructor, and section ID of sections where class participation contributes to 25% of the total grade. Order the result by the instructor's last name.

Solution:

```SELECT i.instructor_id, s.section_id, last_name
FROM instructor i, section s, grade_type_weight w
WHERE i.instructor_id = s.instructor_id
AND s.section_id = w.section_id
ORDER BY last_name
INSTRUCTOR_ID SECTION_ID LAST_NAME
------------- ---------- ----------
107        115 Frantzen
101        133 Hanks
108        155 Lowry
105        129 Morris
105        144 Morris
104         82 Pertez
106        137 Smythe
102        149 Wojick
102         88 Wojick

9 rows selected.
```

Or as an ANSI join with the USING clause:

```SELECT instructor_id, section_id, last_name
FROM instructor JOIN section
USING (instructor_id)
USING (section_id)
ORDER BY 3
```
4. Display the first and last names of students who received 99 or more points on their class project.

Solution:

```SELECT first_name, last_name, numeric_grade
FROM student s, enrollment e, grade g
WHERE s.student_id = e.student_id
AND e.student_id = g.student_id
AND e.section_id = g.section_id
---------- --------------- -------------
May        Jodoin                     99
Joel       Brendler                   99

2 rows selected.
```

Or as expressed with an ANSI join:

```SELECT first_name, last_name, numeric_grade
FROM student JOIN enrollment
USING (student_id)
USING (student_id, section_id)
```
5. Select the grades for quizzes of students living in zip code 10956.

Solution:

```SELECT s.student_id, s.last_name, s.first_name,
FROM student s, enrollment e, grade g
WHERE s.student_id = e.student_id
AND e.student_id = g.student_id
AND e.section_id = g.section_id
AND s.zip = '10956'
---------- ----------- ----------- ------------- -----
193 Jamerncy    Al                     91 10956
193 Jamerncy    Al                     90 10956

2 rows selected.
```

Alternatively, you can also join the GRADE table directly to the STUDENT table. (For more information on skipping this table, see Lab 6.2 and the paragraph titled "SKIPPING THE PRIMARY/FOREIGN KEY PATH"

```SELECT s.student_id, s.last_name, s.first_name,
AND g.student_id = s.student_id
AND s.zip = '10956'
```

Or expressed in ANSI join syntax with three tables:

```SELECT s.student_id, s.last_name, s.first_name,
FROM student s JOIN enrollment e
ON (s.student_id = e.student_id)
ON (e.student_id = g.student_id
AND e.section_id = g.section_id)
AND s.zip = '10956'
```
6. List the course number, section number, and instructor first and last names of classes with course number 350 as a prerequisite.

Solution:

```SELECT c.course_no, section_no, first_name,
last_name
FROM course c, section s, instructor i
WHERE c.course_no = s.course_no
AND s.instructor_id = i.instructor_id
AND prerequisite = 350
COURSE_NO SECTION_NO FIRST_NAME LAST_NAME
--------- ---------- ---------- -----------
450          1 Fernand    Hanks

1 row selected.
```

The solution can also be achieved using one of the ANSI join syntax variants:

```SELECT c.course_no, section_no, first_name,
last_name
FROM course c JOIN section s
ON (c.course_no = s.course_no)
JOIN instructor i
ON (s.instructor_id = i.instructor_id)
WHERE prerequisite = 350
```
7. Write the questions for the following two SELECT statements. Explain the difference between the two results.

```SELECT stud.student_id, i.instructor_id,
stud.zip, i.zip
FROM student stud, instructor i
WHERE stud.zip = i.zip

SELECT stud.student_id, i.instructor_id,
stud.zip, i.zip
FROM student stud, enrollment e, section sec,
instructor i
WHERE stud.student_id = e.student_id
AND e.section_id = sec.section_id
AND sec.instructor_id = i.instructor_id
AND stud.zip = i.zip
```

Solution: The first statement determines those instructors who live in the same zip code as students. It builds a Cartesian product, because there are multiple occurrences of the same zip code in both the INSTRUCTOR and STUDENT tables. The result looks like this:

```STUDENT_ID INSTRUCTOR_ID ZIP   ZIP
---------- ------------- ----- -----
223           102 10025 10025
399           102 10025 10025
163           102 10025 10025
223           103 10025 10025
399           103 10025 10025
163           103 10025 10025
223           106 10025 10025
399           106 10025 10025
163           106 10025 10025
223           108 10025 10025
399           108 10025 10025
163           108 10025 10025

12 rows selected.
```

The second statement shows the instructors who live in the same zip code as the student they teach. The result is as follows:

```STUDENT_ID INSTRUCTOR_ID ZIP   ZIP
---------- ------------- ----- -----
223           103 10025 10025
163           106 10025 10025

2 rows selected.
```