Mega Code Archive

Right outer join between department table and employee table

SQL> SQL> CREATE TABLE departments 2 (department_id number(10) not null, 3 department_name varchar2(50) not null, 4 CONSTRAINT departments_pk PRIMARY KEY (department_id) 5 ); Table created. SQL> SQL> SQL> SQL> insert into departments ( department_id, department_name ) 2 values( 1, 'Data Group' ); 1 row created. SQL> SQL> insert into departments ( department_id, department_name ) 2 values( 2, 'Purchasing' ); 1 row created. SQL> SQL> insert into departments ( department_id, department_name ) 2 values( 3, 'Call Center' ); 1 row created. SQL> SQL> insert into departments ( department_id, department_name ) 2 values( 4, 'Communication' ); 1 row created. SQL> 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), 10 CONSTRAINT employees_pk PRIMARY KEY (employee_id), 11 CONSTRAINT fk_departments FOREIGN KEY (department_id) REFERENCES departments(department_id) 12 ); 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', 'wvelasq@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 13-JUN-98 MGR 4 25000 1005 1005 Viper sdillon@a .com 10-JUN-08 PROG 1 20000 1006 1006 Beck clbeck@g.com 10-JUN-08 PROG 2 20000 1007 Java java01@g.com 10-JUN-08 PROG 3 20000 1006 EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID ----------- -------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ---------- 1008 Oracle wvelasq@g.com 10-JUN-08 DBA 4 20000 1006 8 rows selected. SQL> SQL> select * from departments; DEPARTMENT_ID DEPARTMENT_NA ------------- ------------- 1 Data Group 2 Purchasing 3 Call Center 4 Communication 4 rows selected. SQL> SQL> SQL> select e.employee_id, e.last_name, d.department_name 2 from employees e right outer join departments d 3 on e.department_id = d.department_id 4 where d.department_name = 'Purchasing' 5 order by d.department_name, e.last_name 6 / EMPLOYEE_ID LAST_NAME DEPARTMENT_NA ----------- -------------------------------------------------- ------------- 1006 Beck Purchasing 1002 Wells Purchasing SQL> SQL> SQL> drop table employees cascade constraints; Table dropped. SQL> drop table departments cascade constraints; Table dropped.