Assignment 4 - David Bozarth - CIS 134B - 24 June 2002 ..................................................................... /* qry01 */ SQL> select location 2 from section 3 where capacity = 4 (select max(capacity) 5 from section) 6 / LOCATION -------------------------------------------------- L509 L509 L210 /* OR */ SQL> edit Wrote file afiedt.buf 1 select distinct location 2 from section 3 where capacity = 4 (select max(capacity) 5* from section) SQL> / LOCATION -------------------------------------------------- L210 L211 L214 L507 L509 L511 M311 M500 8 rows selected. ..................................................................... /* qry02 */ SQL> col location format a10 SQL> col description format a20 SQL> edit Wrote file afiedt.buf 1 select s.location, c.course_no course_number, c.description, s.capacity 2 from section s, course c 3 where s.capacity = 4 (select max(capacity) 5 from section) 6* and rownum < 5 SQL> / LOCATION COURSE_NUMBER DESCRIPTION CAPACITY ---------- ------------- -------------------- ---------- L509 10 DP Overview 25 L509 10 DP Overview 25 L210 10 DP Overview 25 M311 10 DP Overview 25 ..................................................................... /* qry03 */ SQL> edit Wrote file afiedt.buf 1 select s.course_no, s.section_no, c.description 2 from course c, section s 3 where trunc(s.start_date_time) = 4 (select max(trunc(start_date_time)) 5 from section 6 ) 7 and c.course_no = s.course_no 8* order by c.course_no SQL> / COURSE_NO SECTION_NO DESCRIPTION ---------- ---------- ----------------------------------- 134 3 Advanced Unix Admin ..................................................................... /* qry04 */ SQL> edit Wrote file afiedt.buf 1 select distinct c.course_no, c.description 2 from course c, section s 3 where s.location in 4 (select location 5 from section 6 where location like 'L%' 7 ) 8 and c.course_no = s.course_no 9* order by course_no SQL> / COURSE_NO DESCRIPTION ---------- ----------------------------------- 10 DP Overview 20 Intro to Computers 25 Intro to Programming 27 rows selected. ..................................................................... /* qry05 */ SQL> edit Wrote file afiedt.buf 1 select distinct c.course_no, c.description 2 from course c, section s 3 where 5 <= 4 (select count(section_no) 5 from section 6 where course_no = c.course_no 7 ) 8 and c.course_no = s.course_no 9* order by course_no SQL> / COURSE_NO DESCRIPTION ---------- ----------------------------------- 25 Intro to Programming 100 Hands-On Windows 120 Intro to Java Programming 122 Intermediate Java Programming 125 JDeveloper ..................................................................... /* qry06 */ SQL> edit Wrote file afiedt.buf 1 select st.last_name last_name, c.course_no, s.section_no, 2 c.description description, g.numeric_grade 3 from course c, section s, grade g, student st 4 where g.grade_type_code = 'FI' 5 and g.numeric_grade >= all 6 (select numeric_grade 7 from grade 8 where section_id = g.section_id 9 and student_id = g.student_id 10 ) 11 and c.course_no = s.course_no 12 and g.section_id = s.section_id 13 and g.student_id = st.student_id 14* order by g.numeric_grade desc SQL> / LAST_NAME COURSE_NO SECTION_NO DESCRIPTION NUMERIC_GRADE ---------- ---------- ---------- ------------------------------ ------------- Radicola 25 3 Intro to Programming 99 Wicelinski 20 7 Intro to Computers 99 Reed 130 1 Intro to Unix 99 Essner 122 1 Intermediate Java Programming 99 Wicelinski 140 1 Structured Analysis 99 49 rows selected. ..................................................................... /* qry07 */ SQL> select zip from student 2 union 3 select zip from instructor 4 / ZIP ----- 01247 02124 02155 149 rows selected. ..................................................................... /* qry08 */ SQL> select zip from zipcode 2 minus 3 (select zip from instructor 4 union 5 select zip from student 6 ) 7 / ZIP ----- 00914 06401 06455 79 rows selected. ..................................................................... /* qry09 */ SQL> edit Wrote file afiedt.buf 1 select z.state from zipcode z, student s where z.zip = s.zip 2 intersect 3* select z.state from zipcode z, instructor i where z.zip = i.zip SQL> / ST -- NY