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!