"It is possible to
fail in many ways...while to succeed is possible only in one
way." Aristotle (384 BC - 322 BC), Nichomachean Ethics |
Read
first then play the video:
SQL-VIDEO
-Oracle9i
and up SQL New Features
Oracle9i: SQL ANSI
statements
CONNECT command
Connect to SQLPLUS as the
iself user.
SQL> CONNECT iself/schooling
SET command
Let's begin by setting the
linesize to 100 and the pagesize to 55.
SQL> SET LINESIZE 100 PAGESIZE 55
Now, let's compare the
Oracle9i ANSI (American National Standards Institute) standard JOIN
syntax with the original join syntax. Query the employee name,
department number, and department name columns from the EMP table and
DEPT table.
Join command using
original syntax
First, let us exclude all
of the sales department information by using the original Oracle join
syntax.
SQL> SELECT ename, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dname <> 'SALES'
/
JOIN syntax (ANSI)
And now, use the Oracle9i
ANSI standard JOIN syntax.
SQL> SELECT ename, dept.deptno, dname
FROM emp JOIN dept
ON emp.deptno = dept.deptno
AND dname <> 'SALES'
/
Notice, that you get the same result.
NATURAL JOIN syntax (ANSI)
Use the Oracle9i ANSI
standard NATURAL JOIN syntax to join the EMP and DEPT tables where the
employees' salary is greater than 3000 dollars.
SQL> 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.
USING clause
Use the USING clause to
join the EMP and DEPT tables where employees' salary is greater than
3000 dollars.
SQL> 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.
CROSS JOIN syntax (ANSI)
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).
SQL> 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.
OUTER JOIN syntax (ANSI)
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.
RIGHT OUTER JOIN syntax
(ANSI)
Let's demostrate the RIGHT
OUTER JOIN syntax usage.
First, add a Finance department to the department table with no
associated employees.
SQL> INSERT INTO dept
VALUES (40,'FINANCE','WASHINGTON,DC')
/
SQL> COMMIT;
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.
SQL> 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.
Write the same above query,
using the Oracle9i ANSI standard RIGHT OUTER JOIN syntax.
SQL> 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'
/
LEFT OUTER JOIN syntax
(ANSI)
Add an employee who doesn't
work in any of the departments yet.
Make sure to disable the foreign key before inserting a record.
SQL> INSERT INTO emp
VALUES (9900,'KAZ','CLERK',7902,TO_DATE(sysdate),1000,NULL,NULL)
/
COMMIT
/
Query all of the employee
names including those who don't work for any department.
Use the original Oracle syntax.
SQL> 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.
List the same above query
by using the Oracle8i ANSI standard LEFT OUTER JOIN syntax.
SQL> 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
/
FULL OUTER JOIN syntax
(ANSI)
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.
SQL> 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.
Delete the FINANCE
department and the KAZ employee.
SQL> DELETE FROM dept
WHERE deptno = 40
/
DELETE FROM emp
WHERE empno = 9900
/
SQL> COMMIT
/
You have deleted the records so that you can repeat these
steps over and over again.
"I'm living so
far beyond my income that we may almost be said to be living
apart." - e e cummings (1894-1962) |
Questions:
Q: What is the SQL ANSI
statement?
Q: What is the difference
between the SQL ANSI statement and Original Oracle statement?
Q: Is the SET command a SQL
statement?
Q: How do you change your
workstation"s page size or line size?
Q: What does the JOIN
syntax in the Oracle SQL (DML) statement?
Q: What is the difference
between the JOIN syntax and the NATURAL JOIN syntax?
Q: What does the USING
clause in the Oracle SQL statement?
Q: When can you not use the
USING clause?
Q: What is the advantage of
the NATURAL JOIN syntax?
Q: What does the CROSS JOIN
syntax in the Oracle SQL statement?
Q: What does the IN clause
in the Oracle SQL statement?
Q: What do the OUTER JOIN,
RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN syntax in the
Oracle SQL statement?
Q: How can you perform the
FULL OUTER JOIN syntax using the Original Oracle syntax?
|