|
More Resources by
Google: |
|
|
|
|
Topics: Hands-On 02 - Advanced Data Selection in Oracle
Displaying Data
From Multiple Tables
Select
Statements That Join Data from More than One Table
Creating Outer
Joins
Joining a Table
to Itself
Group Functions and
their uses
Identifying
Available Group Functions
Using Group
Functions
Using the GROUP
BY Clause
Excluding GROUP
BY Data with HAVING
Using sub queries
Nested Sub
queries
Using Runtime
Variables
Entering
Variables at Run Time
Using Runtime
Variables
Before we join the
department table and the employee table.
Let’s first query
them individually.
>>
select * from dept;
>>
select * from emp;
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.
This is an example
of an ambiguous column.
>>
select ename, deptno
from emp, dept
where deptno = deptno;
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.
>>
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.
>>
select ename, dname, e.deptno
from emp e, dept d
where e.deptno =
d.dpetno;
Avoid a Cartesian
product. A “Cartesian” product is caused by joining “N” number of tables while
you have less than “N-1” join conditions in the query.
>>
select * from dept, emp;
This is an example
of a Cartesian product.
Query the
department table again.
>> 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.
>> insert
into dept values (40, 'Finance', 'Ohio');
Save the insert
transaction.
>>
commit;
Query all the
employee names and their department names including all of the 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.
>>
select ename, dname
from emp e, dept d
where e.deptno (+) =
d.deptno;
Query the manager
names with their employees sorted by the manager names. This is an example of a
selfjoin.
>>
select mgr.ename “Manager”, e.ename “Employee”
from emp mgr, emp e
where mgr.empno =
e.mgr
order by mgr.ename;
Query the
department number and their total, average, min, and max salaries for each
department.
>>
select deptno, sum(sal), avg(sal), min(sal), max(sal)
from emp
group by deptno;
Query the
department number and their total salaries that have more than 5 employees in
their department.
>> select
deptno, sum(sal)
from emp
group by deptno
having count(*) > 5;
Query the employee
names that work for the Research or Sales departments (the department number 20
or 30).
>> select
ename, deptno
from emp
where deptno in (20,
30);
Query the employee
names that work in the "accounting" department. Assuming the department number
is unknown.
>>
select ename
from emp
where deptno in
(select deptno from dept where dname = "accounting";
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.
>> select
ename
from emp
where deptno = &deptno;
Query the customer
names which have more than four orders.
>>
select name
from customer c
where exists
(select 'T' from ord
where custid = c.custid
group by custid
having count(*) > 4);
Now, you should
practice this over and over, until you become a master at it.
Good Luck!
|