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, 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
SQL> set linesize 100;
SQL> select * from inven;
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- ---------- ---------- ---------- ---------- ---------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 48 51.99 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
8 rows selected.
SQL> desc inven;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ITEMNO VARCHAR2(4)
ITEMNAME VARCHAR2(15)
ONHAND NUMBER(5)
ONORDER NUMBER(5)
REORDPT NUMBER(5)
COST NUMBER(6,2)
PRICE NUMBER(6,2)
DEPT CHAR(2)
ITEMCLASS CHAR(2)
LOCATION VARCHAR2(4)
Problems:
- 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.
- 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. Use a subquery.
- 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 and their job from the emp table whose dept is in NEW YORK.
Use a subquery to accomplish the task.
- Using the inven table, find all the records
where the cost is greater then the cost of Heidi and the itemclass is CH. Use a subquery.
- Using the inven table and subqueries find all the records where
itemclass is equal to the itemclass for Heidi and the price is greater
than the average price.