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