SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,'Tom','N', 'Coder', 13,date '1965-12-17', 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,'Jack','JAM', 'Tester',6,date '1961-02-20', 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,'Wil','TF' , 'Tester',6,date '1962-02-22', 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,'Jane','JM', 'Designer', 9,date '1967-04-02', 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,'Mary','P', 'Tester',6,date '1956-09-28', 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,'Black','R', 'Designer', 9,date '1963-11-01', 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,'Chris','AB', 'Designer', 9,date '1965-06-09', 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,'Smart','SCJ', 'Coder', 4,date '1959-11-26', 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,'Peter','CC', 'Designer',NULL,date '1952-11-17', 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,'Take','JJ', 'Tester',6,date '1968-09-28', 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,'Ana','AA', 'Coder', 8,date '1966-12-30', 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,'Jane','R', 'Manager', 6,date '1969-12-03', 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,'Fake','MG', 'Coder', 4,date '1959-02-13', 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,'Mike','TJA','Manager', 7,date '1962-01-23', 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> create table salary
2 ( grade NUMBER(2) constraint S_PK
3 primary key
4 , lowerlimit NUMBER(6,2)
5 , upperlimit NUMBER(6,2)
6 , bonus NUMBER(6,2)
7 , constraint S_LO_UP_CHK check
8 (lowerlimit <= upperlimit)
9 ) ;
Table created.
SQL>
SQL> insert into salary values (1, 700,1200, 0);
1 row created.
SQL> insert into salary values (2, 1201,1400, 50);
1 row created.
SQL> insert into salary values (3, 1401,2000, 100);
1 row created.
SQL> insert into salary values (4, 2001,3000, 200);
1 row created.
SQL> insert into salary values (5, 3001,9999, 500);
1 row created.
SQL>
SQL> select e.ename emp
2 , 12*e.sal+s.bonus total_salary
3 from emp e
4 , salary s
5 where e.sal between s.lowerlimit
6 and s.upperlimit;
EMP TOTAL_SALARY
-------- ------------
Tom 9600
Ana 13200
Jane 9600
Wil 15050
Mary 15050
Mike 15650
Jack 19300
Take 18100
Jane 35900
Black 34400
Chris 29600
Smart 36200
Fake 36200
Peter 60500
14 rows selected.
SQL> drop table emp;
Table dropped.
SQL> drop table salary;
Table dropped.