A Comprehensive Website for Oracle Learning

-- Introduction 01 ("Must to know" Oracle9i: SQL New Features)
-- Preparation
alter table oracle.emp drop (photo)
/
SET ECHO ON
CLEAR SCR
-- Connect to SQLPLUS as the oracle user.
--
pause

CONNECT oracle/learning
pause

CLEAR SCR
-- Let's begin by setting the linesize to 100 and the pagesize to 55.
--
pause

SET LINESIZE 100 PAGESIZE 55
pause

CLEAR SCR
-- Now, let's compare the Oracle9i ANSI standard JOIN syntax
-- with the original join syntax.

CLEAR SCR
-- Query the employee name, department number, and department name columns
-- from the EMP table and DEPT table.

-- Exclude all of the sales department information.

-- Use the original Oracle join syntax.
--
pause

SELECT ename, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dname <> 'SALES'
/
pause

CLEAR SCR
-- And now, use the Oracle9i ANSI standard JOIN syntax.
--
pause

SELECT ename, dept.deptno, dname
FROM emp JOIN dept
ON emp.deptno = dept.deptno
AND dname <> 'SALES'
/
-- Notice, that you get the same result.
--
pause

CLEAR SCR
-- Use the Oracle9i ANSI standard NATURAL JOIN syntax
-- to join the EMP and DEPT tables where the employees'
-- salary is greater than 3000 dollars.
--
pause

SELECT ename, deptno, dname
FROM emp NATURAL JOIN dept
WHERE sal > 3000
/
-- Notice that in the NATURAL JOIN syntax,
-- you don't need a WHERE clause since the
-- department number is the same.
--
pause

CLEAR SCR
-- Use the USING clause to join the EMP and DEPT tables
-- where employees' salary is greater than 3000 dollars.
--
pause

SELECT ename, deptno, dname
FROM emp JOIN dept
USING (deptno)
WHERE sal > 3000
/
-- Notice that in the USING clause the join column names must be the same.
pause

CLEAR SCR
-- Use the Oracle9i ANSI standard CROSS JOIN syntax
-- with no WHERE clause to create a cartesian product.

-- Query the department name and employee name
-- where the department number is (10 or 30) and
-- the employees are (SMITH, ALLEN, and WARD).
pause

SELECT dname, ename
FROM dept d CROSS JOIN emp e
WHERE d.deptno IN (10,30) and
ename IN ('SMITH','ALLEN','WARD')
ORDER BY dname
/
-- This is an example of an Oracle9i ANSI standard CROSS JOIN syntax.
pause

CLEAR SCR
-- Using the OUTER JOIN option, not only will you
-- query records that have matches but you also
-- see the rows that have no matches.
--
-- The Oracle9i ANSI Join syntax provides three separate
-- capabilities: RIGHT, LEFT, and FULL OUTER JOIN.
-- The word OUTER is redundant and usually omitted.
--
pause
pause

CLEAR SCR
-- Let's demostrate the RIGHT OUTER JOIN syntax usage.

-- First, add a Finance department to the department
-- Table with no associated employees.
--
pause

INSERT INTO dept
VALUES (40,'FINANCE','WASHINGTON,DC')
/
COMMIT;
pause

CLEAR SCR
-- Query all employee names with their department's name.

-- With that in mind to:
-- Exclude all of the sales department information;
-- Include all of the departments that have no employees.

-- Use the original oracle join syntax to do the OUTER JOIN query.
pause

SELECT nvl(e.ename,'No Match'), d.deptno, d.dname
FROM dept d, emp e
WHERE d.deptno = e.deptno (+)
AND dname <> 'SALES'
/
-- Notice that FINANCE deptartment has no match.
pause

CLEAR SCR
-- Write the same above query, using the Oracle9i ANSI standard
-- RIGHT OUTER JOIN syntax.
--
pause

SELECT nvl(e.ename,'No Match'), d.deptno, d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON d.deptno = e.deptno
WHERE dname <> 'SALES'
/
pause

CLEAR SCR
-- Add an employee who doesn't work in any of the departments yet.

-- Make sure to disable the foreign key before inserting a record.
--
pause

INSERT INTO emp
VALUES (9900,'KAZ','CLERK',7902,TO_DATE(sysdate),1000,NULL,NULL)
/
COMMIT
/
pause

CLEAR SCR
-- Query all of the employee names including those who don't work for any department.

-- Use the original Oracle syntax.
pause

SELECT e.ename, d.deptno, nvl(d.dname,'No Match') as department
FROM dept d, emp e WHERE d.deptno (+) = e.deptno
/
-- Notice that employee KAZ does not work for any department.
pause

CLEAR SCR
-- List the same above query by using the Oracle8i ANSI standard
-- LEFT OUTER JOIN syntax.
--
pause

SELECT e.ename, d.deptno, nvl(d.dname,'No Match') as department
FROM emp e LEFT OUTER JOIN dept d
ON d.deptno = e.deptno
/
pause

CLEAR SCR
-- Let's perfom an excercise to use the FULL OUTER JOIN option to find
-- all of the records that have no matches in the two joined tables.
pause
SELECT nvl(e.ename,'No Match') as name, d.deptno,
nvl(d.dname,'No Match') as department
FROM emp e FULL OUTER JOIN dept d ON d.deptno = e.deptno
/
-- That would be difficult to do with an original Oracle syntax.
pause

CLEAR SCR
-- Delete the FINANCE department and the KAZ employee.
--
pause

DELETE FROM dept
WHERE deptno = 40
/
DELETE FROM emp
WHERE empno = 9900
/
COMMIT
/
-- You have deleted the records so that you can repeat these
-- steps over and over again.

pause

CLEAR SCR

-- Practice this Hands-on over and over
-- until you become a master at it.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck!
Pause