iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |

 

Lesson 08

“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?