set echo off
clear columns

-- page formatting commands

set pagesize 10000
set linesize 150
set feedback 1

-- column formatting commands

col instructor format a25
col course format a35

-- Creating tables to use for these exercises *****

drop table dog;
drop table owner;
drop table intro_course;


create table owner
(owner_id number(3) primary key,
 name varchar2(30),
 phone_no varchar2(12));

create table dog
(name  varchar2(15) not null,
 breed varchar2(20) not null,
 birth_date date,
 owner_id number(3) references owner(owner_id));

create table intro_course
as
select course_no, 
       description, 
       cost
  from course
 where 1 = 2;

set echo on
clear screen;


-- ----------------------------------------------------------------------
--
--                            DATABASE SYSTEMS
--
--
--                               LECTURE 5                   
--
--
--                     Complex Joins and Data Manipulation
--
-- -----------------------------------------------------------------------
--
--
--
--
--
-- Instructions:
-- ------------
--
--
-- This database script should be run in conjunction
-- with the PowerPoint presentation for Lecture 5.
-- 
-- 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;

-- -----------------------------------------------------------------------
--           OUTER JOIN                    
-- -----------------------------------------------------------------------


-- ***** Show instructors and their sections *****

select s.section_id section,
       i.instructor_id id, 
       i.first_name||' '||i.last_name instructor
  from instructor i,
       section s
 where i.instructor_id = s.instructor_id
   and (s.section_id > 150
        or s.section_id is null)
 order by s.section_id;


pause;


-- ***** Using an outer join shows instructors who are not assigned to a section *****
--       (look at the last two rows that are retrieved)

-- The outer join operator is on the foreign key (instructor_id in the section table)

select s.section_id section,
       i.instructor_id id, 
       i.first_name||' '||i.last_name instructor
  from instructor i,
       section s
 where i.instructor_id = s.instructor_id(+)
   and (s.section_id > 150
        or s.section_id is null)
 order by s.section_id;


pause;
clear screen;


-- -----------------------------------------------------------------------
--           ANSI OUTER JOIN
-- -----------------------------------------------------------------------


-- ***** Show instructors and their sections using an ANSI join *****

select s.section_id section,
       instructor_id id,
       i.first_name||' '||i.last_name instructor
  from instructor i join section s
 using (instructor_id)
 where (s.section_id > 150
        or s.section_id is null)
 order by s.section_id;


pause;


-- ***** Using an ANSI outer join shows instructors who are not assigned to a section *****
--       (look at the last two rows that are retrieved)

-- Using a left outer join because all rows in the table to the left of the join
-- should be listed

select s.section_id section,
       instructor_id id,
       i.first_name||' '||i.last_name instructor
  from instructor i left outer join section s
 using (instructor_id)
 where (s.section_id > 150
        or s.section_id is null)
 order by s.section_id;

pause;

-- Using a right outer join because all rows in the table to the right of the join
-- should be listed

select s.section_id section,
       instructor_id id,
       i.first_name||' '||i.last_name instructor
  from section s right outer join instructor i
 using (instructor_id)
 where (s.section_id > 150
        or s.section_id is null)
 order by s.section_id;

pause;
clear screen;


-- -----------------------------------------------------------------------
--           SELF JOIN
-- -----------------------------------------------------------------------


select c1.course_no,
       c1.description course,
       c1.prerequisite prereq_no,
       c2.description prerequisite
  from course c1,
       course c2
 where c1.prerequisite = c2.course_no
 order by 1;

pause;

-- ANSI join (note you can't use the USING clause because the column names
-- are not identical)

select c1.course_no,
       c1.description course,
       c1.prerequisite prereq_no,
       c2.description prerequisite
  from course c1 join course c2
    on (c1.prerequisite = c2.course_no)
 order by 1;


pause;
clear screen;


-- -----------------------------------------------------------------------
--           INSERTING DATA                    
-- -----------------------------------------------------------------------


-- ***** The following tables will be used in these exercises: *****

set lines 50

desc owner;

desc dog;

set lines 150
 
pause;
clear screen;


-- ***** Inserting a single row (look how numbers, text and dates are entered) *****

insert into owner
(owner_id, name, phone_no)
values
(1, 'Cheryl Taylor-Cox', '83032620');


insert into dog
(name, breed, birth_date, owner_id)
values
('Anna', 'German Shepherd', to_date('01-MAY-1992', 'DD-MON-YYYY'), 1);


pause;

select * from owner;

select * from dog;


pause;
clear screen;


-- ***** Not explicitly naming the columns *****

insert into dog
values
('Sophie', 'German Shepherd', to_date('14-APR-1993', 'DD-MON-YYYY'), 1);


pause;
clear screen;


-- ***** Explicitly naming the columns AND leaving out columns that are optional *****
--       (ie, the birth_date column)

insert into dog
(name, breed, owner_id)
values
('Bliss', 'German Shepherd', 1);


pause;
clear screen;


-- ***** Explicitly naming columns but changing the order of them *****
--       (Order when table created was: name, breed, birth_date, owner_id)

insert into dog
(owner_id, name, breed)
values
(1, 'Bella', 'German Shepherd');


pause;
clear screen;


-- ***** Not explicitly naming the columns and inserting null into an optional column *****
--       (Order when table created was: name, breed, birth_date, owner_id)

insert into dog
values
('Mischka', 'German Shepherd', null, 1);


pause;


select * from dog;


pause;
clear screen;


-- -----------------------------------------------------------------------
--           INSERTING MULTIPLE ROWS                    
-- -----------------------------------------------------------------------


-- ***** The following table will be used in the following exercise: *****

set lines 50

desc intro_course;

set lines 150

pause;

insert into intro_course
select course_no, 
       description, 
       cost
  from course
 where prerequisite is null;


select * from intro_course;


pause;
clear screen;


-- -----------------------------------------------------------------------
--           ROLLBACK AND COMMIT                    
-- -----------------------------------------------------------------------

commit;


-- ***** Inserting rows and then doing a rollback *****


insert into owner
(owner_id, name, phone_no)
values
(2, 'KT Lau', '82072866');


insert into owner
(owner_id, name, phone_no)
values
(3, 'Santi Ruiz', '82072867');


insert into owner
(owner_id, name, phone_no)
values
(4, 'Sam Hammer', '82072867');


select * from owner;


pause;


rollback;


select * from owner;


pause;
clear screen;


-- ***** Inserting rows and then doing a commit and rollback *****

insert into owner
(owner_id, name, phone_no)
values
(2, 'KT Lau', '82072866');


insert into owner
(owner_id, name, phone_no)
values
(3, 'Santi Ruiz', '82072227');


insert into owner
(owner_id, name, phone_no)
values
(4, 'Sam Hammer', '82072867');

select * from owner;


commit;


pause;


rollback;


select * from owner;


pause;
clear screen;


-- -----------------------------------------------------------------------
--           UPDATING DATA                    
-- -----------------------------------------------------------------------


select * from intro_course;


pause;


update intro_course
   set cost = 995;


select * from intro_course;


pause;


update intro_course
   set cost = 1500
 where description like '%Java%';


pause;


update intro_course
   set cost = 650,
       description = 'Introduction to Computers'
 where course_no = 20;


select * from intro_course;


pause;
clear screen;


-- -----------------------------------------------------------------------
--           DELETING DATA                    
-- -----------------------------------------------------------------------


delete from intro_course
 where cost = 1500;


select * from intro_course;


pause;


rollback;


select * from intro_course;


pause;


truncate table intro_course;


select * from intro_course;


pause;


rollback;


select * from intro_course;


pause;
clear screen;




--
-- This completes:
--
--
--
-- ----------------------------------------------------------------------
--
--                            DATABASE SYSTEMS
--
--
--                                LECTURE 5
--
--
--                     Complex Joins and Data Manipulation
--
--
-- -----------------------------------------------------------------------
--
--
--
-- If you have been spooling the output to a file you should spool off now.
--