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 03

I don't know the key to success, but the key to failure is trying to please everybody. Bill Cosby (1937 - )

 

Read first then play the video:

   SQL-VIDEO -Advanced Data Selection in Oracle

 

Advanced Data Selection

Introduction

In this section, you will learn about an ambiguous column and how you can prevent it. You will learn how to query against two (such as the department and employee tables) or more tables by joining them together. You also learn what a sub-query is and how to use it.

Now, let’s before you join the department table and the employee table, first query them individually.

SQL> SELECT * FROM dept;

SQL> SELECT * FROM emp;

 

INNER Join or EQUIJOIN

Joining two or more tables together is the best relational database usage. You relate the tables using the WHERE clause. The equal sign (=) in a query only retrieves records that have exact match.

Query the employees name and their department name using user schemas to avoid the ambiguous column problem. Also, this is an example of the inner join or equijoin.

SQL> SELECT ename, dname

              FROM emp, dept

              WHERE emp.deptno = dept.deptno;

 

Use the previous query and use the table aliases to avoid the ambiguous column. Use the letter “e” for the employee table alias and the letter “d” for the department table alias.

SQL> SELECT ename, dname, e.deptno

              FROM emp e, dept d

              WHERE e.deptno = d.dpetno;

 

Ambiguous Column

An ambiguous column is a column that is not defined clearly. Having two tables with the same column name, you should reference them such that there is no ambiguity on their ownerships. The column name should be identified by alias to make it clear that to what table that column is belong.

If you join two tables that contain a common column name, you should use the table aliases or the user schema. Otherwise the column would be an ambiguous column.

The following is an example of an ambiguous column.

SQL> SELECT ename, deptno

              FROM emp, dept

              WHERE deptno = deptno;

In the above example the ‘DEPTNO’ column is an ambiguous column since there is no identifier to indicate that what table the DEPTNO column belongs.

 

Cartesian product

Avoid a Cartesian product. A “Cartesian” product is caused by joining “N” number of tables while you have less than “N-1” joins condition in the query.

SQL> SELECT * FROM dept, emp;

This is an example of a Cartesian product.

 

OUTER JOIN

By joining two or more tables using OUTER join, not only you retrieve all matching records but also you retrieve the records that do not match. For example that you may have an employee that you did not assign any department number for him or via versa.

 

Try to query the department table again.

SQL> SELECT * FROM dept;

Notice, we have only three records.

 

Insert a record to the department. Remember that we have no employee in the department table.

SQL> INSERT INTO dept VALUES (40, 'Finance', 'Ohio');

 

Save the insert transaction.

SQL> COMMIT;

 

Now, query all the employee names and their department names including the entire department name with no employees.

 

Use outer join. As you know there is no employee in the "Finance" department.

Use + sign next to the column that has no match in it.

SQL> SELECT ename, dname

              FROM emp e, dept d

              WHERE e.deptno (+) = d.deptno;

 

SELF JOIN

If a table refers to itself in the WHERE clause, we say that join is a selfjoin.

Query the manager names with their employees sorted by the manager names. This is an example of a selfjoin.

SQL> SELECT mgr.ename “Manager Name”, e.ename “Employee Name”

              FROM emp mgr, emp e

              WHERE mgr.empno = e.mgr

              ORDER BY mgr.ename;

 

SUM, AVG, MIN, and MAX functions

Query the department number and their total, average, min, and max salaries for each department.

SQL> SELECT deptno, SUM(sal), AVG(sal), MIN(sal), MAX(sal)

              FROM emp

              GROUP BY deptno;

 

GROUP BY statement

Query the department number and their total salaries that have more than 5 employees in their department.

SQL> SELECT deptno, SUM(sal)

              FROM emp

              GROUP BY deptno

              HAVING count(*) > 5;

 

IN clause

Use the IN clause, when you are going to use OR.

Query the employee names that work for the Research or Sales departments (the department number 20 or 30).

SQL> SELECT ename, deptno

              FROM emp

              WHERE deptno IN (20, 30);

 

Sub-query

If you write a query within a query, you are using sub-query. In the WHERE clause, not all the time, you have a constant value to compare. If you have to query a value from table, then you need to write a sub-query. Query the employee names that work in the "accounting" department. Assuming the department number is unknown.

SQL> SELECT ename

              FROM emp

              WHERE deptno IN

                           (SELECT deptno

                                FROM dept

                                WHERE dname = "ACCOUNTING");

 

Runtime variable

If you have a query that you have to change its WHERE clause constants or column names over and over, you may use the runtime variables. You can substitute those values without changing or modifying the query script.

Query the employee names of each department. Use the runtime variable to substitute the department number.

Run to query department number 10.

Run to query department number 20.

Run to query department number 30.

 

SQL> SELECT ename

              FROM emp

              WHERE deptno = &deptno;

SQL> /

Enter value 10.

 

SQL> run

Enter value 20.

 

SQL> /

Enter value 30.

 

EXISTS statement

You use the EXISTS clause to test existence of rows in the results of a sub-query. When you specify the exits operation in a WHERE clause, you must include a sub-query that satisfies the EXISTS operation. If the sub-query returns data, then the EXISTS operation will return TRUE and a record from the parent query will be returned.

 

Query the customer names which have more than four orders.

SQL> SELECT name

              FROM customer c

              WHERE exists

              (SELECT 'T'

                   FROM ord

                   WHERE custid = c.custid

                   GROUP BY custid

                   HAVING count(*) > 4);

 

"If a man does his best, what else is there?" - General George S. Patton (1885-1945)

 

Questions:

Q: What is an ambiguous column?

Q: How can you resolve an ambiguous column problem?

Q: What is a Cartesian product?

Q: How can you avoid a Cartesian product?

Q: What is an inner join or equi-join?

Q: What is an outer join?

Q: What is a self join?

Q: Query all the employee names and their department including all the departments with no employees.

Q: Query the managers’ name with their employees sorted by the manager name.

Q: Query the department number and their total, average, min, and max salaries for each department.

Q: Query the department no and their total salaries that have more than 5 employees working in their department.

Q: Query the employees name that work for the Research or Sales department (the department number 20 or 30).

Q: Query the employees name that work in the "accounting" department. Assuming the department number is unknown.

Q: Query the employees name and use the runtime variable to substitute the department number? Then run it for following department no 10, 20, and 30.

Q: Query the customer names which have more than four orders.