SQL

Connect to SQLPLUS as the oracle user.
SQL> CONNECT oracle/learning
 

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'
/
 

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.
 

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.
 

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.
 

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.
 

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.
 

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'
/
 

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,

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,

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,

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.

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?