everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

SQL

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19
<< Previous

Chapter # 03

Next >>


 

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.

 

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.