Multiple tables, subqueries etc.

Use any database table or tables you want to accomplish the tasks that do not specify a table. For some you will need to use the emp, dept, and salgrade tables that come with Oracle.

 
SQL> select * from emp;
 
 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
 7369 SMITH CLERK 7902 17-DEC-80 800 20
 7499 ALLEN  SALESMAN 7698 20-FEB-81 1600 300 30
 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
 7566 JONES MANAGER 7839 02-APR-81 2975 20
 7654 MARTIN SALESMAN  7698 28-SEP-81 1250 1400 30
 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
 7839 KING PRESIDENT 17-NOV-81 5000 10
 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
 7900 JAMES CLERK 7698 03-DEC-81 950 30
 7902 FORD ANALYST 7566 03-DEC-81 3000 20
 7934 MILLER CLERK 7782 23-JAN-82 1300  10
 
14 rows selected.
 
SQL> select * from dept;
 
 DEPTNO DNAME LOC
--------- -------------- -------------
 10 ACCOUNTING NEW YORK
 20 RESEARCH DALLAS
 30 SALES CHICAGO
 40 OPERATIONS BOSTON
 
SQL> select * from salgrade;
 
 GRADE LOSAL HISAL
--------- --------- ---------
 1 700 1200
 2 1201 1400
 3 1401 2000
 4 2001 3000
 5 3001 9999
 
SQL> desc emp
 Name Null? Type
 ------------------------------- -------- ----
 EMPNO NOT NULL NUMBER(4)
 ENAME VARCHAR2(10)
 JOB VARCHAR2(9)
 MGR NUMBER(4)
 HIREDATE DATE
 SAL NUMBER(7,2)
 COMM NUMBER(7,2)
 DEPTNO NUMBER(2)
 
SQL> desc dept
 Name Null? Type
 ------------------------------- -------- ----
 DEPTNO NOT NULL NUMBER(2)
 DNAME VARCHAR2(14)
 LOC  VARCHAR2(13)
 
SQL> desc salgrade
 Name Null? Type
 ------------------------------- -------- ----
 GRADE NUMBER
 LOSAL NUMBER
 HISAL  NUMBER

Understanding types of joins and subqueries:

1. Do a query using an equijoin.
2. Do a query using an outer join.
3. Write a problem that will involve a pairwise subquery and then show me the solution to your problem. (Note: you must write out what the problem is that you are solving)
4. Write a problem that will involve a non pairwise subquery and then show me the solution to your problem. (Note: you must write out what the problem is that you are solving)

Problem solving:

1. For this example, use the sample emp table shown above. Use a subquery to accomplish the task. Show the information for all employees that are in the same department as JONES, but do not show JONES.
2. Find all of the employees from the sample emp table where the salary is greater than the average of the losal column in the salgrade table. Use a subquery to accomplish the task.
3. Find all of the employees from the emp table whose dept is in CHICAGO. Use a subquery to accomplish the task.
4. For this example, use the emp table. Display all of the employees that earn a salary that is higher than the salary of any of the employees that have a job of CLERK or SALESMAN.
5. Determine what grade each employee is at based on their salary.
6. Use the oil tables that you have developed. You may have to add records to make the queries work. You need to show me the data in all of your tables before doing the queries. Show the customer id, name, and tank capacity for all customers that received deliveries of more than 100 gallons.