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