|
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:
SQL006(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.
|