set echo off
-- page formatting commands
set pagesize 10000
set linesize 200
set feedback 1
-- column formatting commands
col description format a35
col student format a30
col name format a25
col position format a20
set echo on
clear screen;
-- ----------------------------------------------------------------------
--
-- DATABASE SYSTEMS
--
--
-- LECTURE 4
--
--
-- Subqueries and Set Operators
-- (excluding Correlated Subqueries)
--
-- -----------------------------------------------------------------------
--
--
--
--
--
-- Instructions:
-- ------------
--
--
-- This database script should be run in conjunction
-- with the PowerPoint presentation for Lecture 4.
--
-- To continue running this demonstration database
-- script hit the enter key each time the Oracle logo
-- is displayed in the presentation (there may be
-- multiple logos which will require you to hit the
-- enter key multiple times).
pause;
clear screen;
-- -----------------------------------------------------------------------
-- SIMPLE SUBQUERY
-- -----------------------------------------------------------------------
-- ***** Want to show the courses with the lowest cost *****
-- ***** Query 1: *****
select min(cost)
from course;
pause;
-- ***** Query 2: (using the output from query 1) *****
select course_no,
description,
cost
from course
where cost = 1095;
pause;
-- ***** Alternatively use a subquery *****
select course_no,
description,
cost
from course
where cost = (select min(cost)
from course);
pause;
clear screen;
-- -----------------------------------------------------------------------
-- NESTING SUBQUERIES
-- -----------------------------------------------------------------------
-- ***** Want to see who is teaching the Unix Courses *****
select first_name||' '||last_name "Unix Instructors"
from instructor
where instructor_id in
(select instructor_id
from section
where course_no in
(select course_no
from course
where description like '%Unix%'));
pause;
clear screen;
-- -----------------------------------------------------------------------
-- SUBQUERIES RETURNING MULTIPLE ROWS
-- -----------------------------------------------------------------------
-- ***** Want to see what courses are on Unix *****
select course_no,
description,
cost
from course
where description =
(select description
from course
where description like '%Unix%');
pause;
-- ***** Error occurs because there are multiple Unix courses *****
-- ***** You need to change the = operator to the IN operator *****
select course_no,
description,
cost
from course
where description in
(select description
from course
where description like '%Unix%');
pause;
clear screen;
-- -----------------------------------------------------------------------
-- NEGATING A SUBQUERY
-- -----------------------------------------------------------------------
-- ***** Want to see what courses other than Unix are available *****
-- ***** You need to change the IN operator to the NOT IN operator *****
select course_no,
description,
cost
from course
where description not in
(select description
from course
where description like '%Unix%');
pause;
clear screen;
-- -----------------------------------------------------------------------
-- SUBQUERIES AND JOINS
-- -----------------------------------------------------------------------
-- ***** Using a subquery to find out what courses Anita Morris teaches *****
select course_no,
description,
cost
from course
where course_no in
(select course_no
from section
where instructor_id =
(select instructor_id
from instructor
where first_name = 'Anita'
and last_name = 'Morris'));
pause;
-- ***** Using joins to find out what courses Anita Morris teaches *****
select c.course_no,
c.description,
c.cost
from course c,
section s,
instructor i
where c.course_no = s.course_no
and s.instructor_id = i.instructor_id
and i.first_name = 'Anita'
and i.last_name = 'Morris'
order by c.course_no;
pause;
clear screen;
-- -----------------------------------------------------------------------
-- MULTIPLE COLUMNS IN SUBQUERIES
-- -----------------------------------------------------------------------
-- Which students have got 95% or higher on their first submitted homework
select first_name||' '||last_name students
from student
where student_id in
(select student_id
from enrollment
where (student_id, section_id) in
(select student_id, section_id
from grade
where grade_type_code = 'HM'
and grade_code_occurrence = 1
and numeric_grade >= 95));
pause;
clear screen;
-- -----------------------------------------------------------------------
-- THE IN OPERATOR
-- -----------------------------------------------------------------------
select first_name||' '||last_name instructor,
zip
from instructor
order by zip;
-- ***** Using the IN operator *****
-- (will choose all instuctors that have
-- a zip equal to 10020 or 10030)
select first_name||' '||last_name instructor,
zip
from instructor
where zip in ('10020','10030')
order by zip;
pause;
-- ***** Using the = ANY operator *****
select first_name||' '||last_name instructor,
zip
from instructor
where zip = any ('10020','10030')
order by zip;
pause;
clear screen;
-- ***** Using the NOT IN operator *****
select first_name||' '||last_name instructor,
zip
from instructor
where zip not in ('10020','10030')
order by zip;
-- ***** Using the <> ALL operator *****
select first_name||' '||last_name instructor,
zip
from instructor
where zip <> all ('10020','10030')
order by zip;
pause;
clear screen;
-- -----------------------------------------------------------------------
-- THE ANY OPERATOR
-- -----------------------------------------------------------------------
-- ***** Using a < comparison with the ANY operator *****
select first_name||' '||last_name instructor,
zip
from instructor
where zip < any ('10020','10030')
order by zip;
-- ***** Using a > comparison with the ANY operator *****
select first_name||' '||last_name instructor,
zip
from instructor
where zip > any ('10020','10030')
order by zip;
pause;
-- -----------------------------------------------------------------------
-- THE SOME OPERATOR
-- -----------------------------------------------------------------------
-- ***** Using a > comparison with the SOME operator *****
-- (this will return an identical result to the previous
-- statement using the any operator)
select first_name||' '||last_name instructor,
zip
from instructor
where zip > some ('10020','10030')
order by zip;
pause;
clear screen;
-- -----------------------------------------------------------------------
-- THE ALL OPERATOR
-- -----------------------------------------------------------------------
-- ***** Using a < comparison with the ALL operator *****
select first_name||' '||last_name instructor,
zip
from instructor
where zip < all ('10020','10030')
order by zip;
-- ***** Using a > comparison with the ALL operator *****
select first_name||' '||last_name instructor,
zip
from instructor
where zip > all ('10020','10030')
order by zip;
pause;
clear screen;
-- -----------------------------------------------------------------------
-- THE UNION OPERATOR
-- -----------------------------------------------------------------------
select first_name||' '||last_name name,
'Student' position,
phone
from student
where first_name like 'K%'
or first_name like 'P%'
union
select first_name||' '||last_name,
'Instructor',
phone
from instructor
where first_name like 'M%'
or first_name like 'C%';
pause;
clear screen;
-- ***** Counting how many records are in each select statement *****
-- (Note the previous statement with the union operator returned 26 rows
-- as any duplicate rows were not included in the final result set)
select count(first_name||' '||last_name) students
from student
where first_name like 'K%'
or first_name like 'P%';
select count(first_name||' '||last_name) instructors
from instructor
where first_name like 'M%'
or first_name like 'C%';
-- (When you look at these results you can see there are actually 27 rows
-- that would be returned if duplicates were included)
-- ***** Identifying duplicate student records *****
select first_name,
last_name,
phone,
count(*)
from student
where first_name like 'K%'
or first_name like 'P%'
group by first_name, last_name, phone
having count(*) > 1;
pause;
clear screen;
-- -----------------------------------------------------------------------
-- THE UNION ALL OPERATOR
-- -----------------------------------------------------------------------
select first_name||' '||last_name name,
'Student' position,
phone
from student
where first_name like 'K%'
or first_name like 'P%'
union all
select first_name||' '||last_name,
'Instructor',
phone
from instructor
where first_name like 'M%'
or first_name like 'C%';
pause;
select first_name||' '||last_name name,
'Student' position,
phone
from student
where first_name like 'K%'
or first_name like 'P%'
union all
select first_name||' '||last_name,
'Instructor',
phone
from instructor
where first_name like 'M%'
or first_name like 'C%'
order by 1;
pause;
clear screen;
-- -----------------------------------------------------------------------
-- THE MINUS OPERATOR
-- -----------------------------------------------------------------------
-- ***** Find all students who don't have an enrollment *****
select student_id
from student
where student_id < 300
minus
select student_id
from enrollment
where student_id < 300;
pause;
-- ***** Checking the MINUS operator returns the correct results *****
select (select count(student_id)
from student
where student_id < 300) -
(select count(distinct student_id)
from enrollment
where student_id < 300) "Students not enrolled:"
from dual;
pause;
clear screen;
-- -----------------------------------------------------------------------
-- THE INTERSECT OPERATOR
-- -----------------------------------------------------------------------
-- ***** Find all students who have an enrollment *****
select student_id
from student
where student_id < 120
intersect
select student_id
from enrollment
where student_id < 120;
pause;
-- ***** Checking the INTERSECT operator returns the correct results *****
select count(distinct student_id) "Students Enrolled"
from enrollment
where student_id < 120 ;
pause;
clear screen;
-- -----------------------------------------------------------------------
-- EQUIJOINS AND THE INTERSECT OPERATOR
-- -----------------------------------------------------------------------
-- ***** Find all students who have an enrollment using INTERSECT *****
select student_id
from student
where student_id < 120
intersect
select student_id
from enrollment
where student_id < 120;
-- ***** Find all students who have an enrollment using an EQUIJOIN *****
select distinct s.student_id
from student s,
enrollment e
where s.student_id = e.student_id
and s.student_id < 120;
pause;
clear screen;
--
-- This completes:
--
--
--
-- ----------------------------------------------------------------------
--
-- DATABASE SYSTEMS
--
--
-- LECTURE 4
--
--
-- Subqueries and Set Operators
-- (excluding Correlated Subqueries)
--
-- -----------------------------------------------------------------------
--
--
--
-- If you have been spooling the output to a file you should spool off now.
--