Mega Code Archive
Join two tables and combine three tables
<![CDATA[
SQL>
SQL> create table salgrades
2 ( grade NUMBER(2)
3 , lowerlimit NUMBER(6,2)
4 , upperlimit NUMBER(6,2)
5 , bonus NUMBER(6,2)
6 ) ;
Table created.
SQL>
SQL> insert into salgrades values (1, 700,1200, 0);
1 row created.
SQL> insert into salgrades values (2, 1201,1400, 50);
1 row created.
SQL> insert into salgrades values (3, 1401,2000, 100);
1 row created.
SQL> insert into salgrades values (4, 2001,3000, 200);
1 row created.
SQL> insert into salgrades values (5, 3001,9999, 500);
1 row created.
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL> insert into employees values(1,'Jason', 'N', 'TRAINER', 2, date '1965-12-18', 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,'Jerry', 'J', 'SALESREP',3, date '1966-11-19', 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,'Jord', 'T' , 'SALESREP',4, date '1967-10-21', 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,'Mary', 'J', 'MANAGER', 5, date '1968-09-22', 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,'Joe', 'P', 'SALESREP',6, date '1969-08-23', 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,'Black', 'R', 'MANAGER', 7, date '1970-07-24', 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,'Red', 'A', 'MANAGER', 8, date '1971-06-25', 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,'White', 'S', 'TRAINER', 9, date '1972-05-26', 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,'Yellow', 'C', 'DIRECTOR',10, date '1973-04-27', 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,'Pink', 'J', 'SALESREP',null,date '1974-03-28', 2400, 0, 30);
1 row created.
SQL>
SQL> create table departments
2 ( deptno NUMBER(2)
3 , dname VARCHAR2(10)
4 , location VARCHAR2(20)
5 , mgr NUMBER(4)
6 ) ;
Table created.
SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK' , 2);
1 row created.
SQL> insert into departments values (20,'TRAINING', 'VANCOUVER', 3);
1 row created.
SQL> insert into departments values (30,'SALES', 'CHICAGO', 4);
1 row created.
SQL> insert into departments values (40,'HR', 'BOSTON', 5);
1 row created.
SQL>
SQL> select e.ename employee
2 , 12*e.msal+s.bonus total_salary
3 , d.dname department
4 from employees e
5 , salgrades s
6 , departments d
7 where e.msal between s.lowerlimit
8 and s.upperlimit
9 and e.deptno = d.deptno;
EMPLOYEE TOTAL_SALARY DEPARTMENT
-------- ------------ ----------
Jason 9600 ACCOUNTING
Jerry 19300 ACCOUNTING
Jord 20500 TRAINING
Mary 21700 TRAINING
Joe 22900 SALES
Black 24100 SALES
Red 25400 HR
White 26600 HR
Yellow 27800 TRAINING
Pink 29000 SALES
10 rows selected.
SQL>
SQL> drop table salgrades;
Table dropped.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL> drop table departments;
Table dropped.
SQL>
]]>