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.
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'
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
AND percent_of_final_GRADE = 25
AND grade_type_code = 'PA'
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) JOIN grade_type_weight USING (section_id) WHERE percent_of_final_grade = 25 AND grade_type_code = 'PA' ORDER BY 3
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
AND numeric_grade >= 99
AND grade_type_code = 'PJ'
FIRST_NAME LAST_NAME NUMERIC_GRADE
---------- --------------- -------------
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) JOIN grade USING (student_id, section_id) WHERE numeric_grade >= 99 AND grade_type_code = 'PJ'
Select the grades for quizzes of students living in zip code 10956.
Solution:
SELECT s.student_id, s.last_name, s.first_name,
g.numeric_grade, s.zip
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 g.grade_type_code = 'QZ'
AND s.zip = '10956'
STUDENT_ID LAST_NAME FIRST_NAME NUMERIC_GRADE ZIP
---------- ----------- ----------- ------------- -----
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,
g.numeric_grade, s.zip
FROM student s, grade g
WHERE g.grade_type_code = 'QZ'
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,
g.numeric_grade, s.zip
FROM student s JOIN enrollment e
ON (s.student_id = e.student_id)
JOIN grade g
ON (e.student_id = g.student_id
AND e.section_id = g.section_id)
WHERE g.grade_type_code = 'QZ'
AND s.zip = '10956'
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
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.