|
SQL>
SQL>
SQL> CREATE TABLE employees
2 ( employee_id number(10) not null,
3 last_name varchar2(50) not null,
4 email varchar2(30),
5 hire_date date,
6 job_id varchar2(30),
7 department_id number(10),
8 salary number(6),
9 manager_id number(6));
Table created.
SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 );
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, 'Kyte', 'tkyte@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, 'Viper', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, 'Oracle', 'oracle1@g.com', SYSDATE, 'DBA', 20000, 4, 1006);
1 row created.
SQL>
SQL> select * from employees;
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID
----------- -------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
1001 Lawson lawson@g.com 01-JAN-02 MGR 1 30000 1004
1002 Wells wells@g.com 01-JAN-02 DBA 2 20000 1005
1003 Bliss bliss@g.com 01-JAN-02 PROG 3 24000 1004
1004 Kyte tkyte@a.com 14-JUN-98 MGR 4 25000 1005
1005 Viper sdillon@a .com 11-JUN-08 PROG 1 20000 1006
1006 Beck clbeck@g.com 11-JUN-08 PROG 2 20000
1007 Java java01@g.com 11-JUN-08 PROG 3 20000 1006
1008 Oracle oracle1@g.com 11-JUN-08 DBA 4 20000 1006
8 rows selected.
SQL>
SQL> CREATE TABLE JOBS (
2 JOB_ID VARCHAR2 (10) NOT NULL,
3 JOB_TITLE VARCHAR2 (35) CONSTRAINT JOB_TITLE_NN NOT NULL,
4 MIN_SALARY NUMBER (6),
5 MAX_SALARY NUMBER (6),
6 CONSTRAINT JOB_ID_PK PRIMARY KEY ( JOB_ID ) ) ;
Table created.
SQL>
SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( 'DBA', 'President', 20000, 40000);
1 row created.
SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( 'PROG', 'Administration Vice President', 15000, 30000);
1 row created.
SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( 'MGR', 'Administration Assistant', 3000, 6000);
1 row created.
SQL>
SQL> declare
2 cursor programmers
3 is select e.last_name name, e.salary
4 from employees e, jobs j
5 where e.job_id = j.job_id
6 and j.job_title = 'Programmer'
7 order by salary;
8
9 name varchar2(200);
10 salary number(9,2);
11 begin
12 for c1 in (select j.job_title, j.min_salary, j.max_salary,
13 avg(e.salary) avg_salary
14 from employees e, jobs j
15 where e.job_id = j.job_id
16 group by j.job_title, j.min_salary, j.max_salary
17 order by j.job_title) loop
18 dbms_output.put_line(c1.job_title||'s, average $'||c1.avg_salary);
19 end loop;
20
21 open programmers;
22 fetch programmers into name, salary;
23 while programmers%FOUND loop
24 dbms_output.put_line(name || ' makes $' || salary);
25 fetch programmers into name, salary;
26 end loop;
27 close programmers;
28 end;
29 /
Administration Assistants, average $27500
Administration Vice Presidents, average $21000
Presidents, average $20000
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employees;
Table dropped.
SQL> drop table jobs;
Table dropped.
SQL>
|