|
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.
|
