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:
- Do a query using an equijoin.
- Do a query using an outer join.
- 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)
- 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:
- 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.
- 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.
- Find all of the employees from the emp table whose dept is in CHICAGO.
Use a subquery to accomplish the task.
- 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 SALESMAN.
- Determine what grade each employee is at based on their salary.