Oracle PL/SQL/Table Joins/Outer Joins
Содержание
Demo the ANSI outer join
SQL>
SQL>
SQL> create table ord_item(
2 order_no integer
3 ,product_id integer
4 ,quantity number(4,0)
5 ,item_price number(7,2)
6 ,total_order_item_price number(9,2)
7 ,primary key (order_no, product_id)
8 );
Table created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(1, 2, 10, 23.00 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(2, 1, 1, 23.00 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(2, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(2, 8, 1, 17.48 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(3, 8, 1, 35.99 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(4, 7, 1, 19.95 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(5, 5, 1, 10.95 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(6, 8, 1, 22.95 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(7, 1, 6, 15.00 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(7, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(7, 8, 1, 10.45 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(8, 8, 1, 35.95 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(9, 8, 1, 65.45 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(9, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(10, 3, 1, 19.95 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(11, 8, 1, 30.00);
1 row created.
SQL>
SQL> create table product(
2 product_id integer primary key
3 ,price number(7,2)
4 ,description varchar2(75)
5 ,onhand number(5,0)
6 ,reorder number(5,0)
7 ,supplier_no integer
8 );
Table created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,"Oracle",100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,"SQL Server",null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,"MySQL",null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,"DB2",50,10);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (5,10.50,"Java",100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (6,45.00,"C++",null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (7,19.99,"Javascript",3,5);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (8,4.50,"Ruby",null,null);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT description, nvl(quantity,0)
2 FROM product LEFT OUTER JOIN ord_item
3 USING (product_id);
DESCRIPTION NVL(QUANTITY,0)
--------------------------------------------------------------------------- ---------------
SQL Server 10
Oracle 1
Java 1
Ruby 1
Ruby 1
Javascript 1
Java 1
Ruby 1
Oracle 6
Java 1
Ruby 1
Ruby 1
Ruby 1
Java 1
MySQL 1
Ruby 1
C++ 0
DB2 0
18 rows selected.
SQL>
SQL> -- Same result with this query:
SQL> SELECT p.description, nvl(o.quantity,0)
2 FROM product p LEFT OUTER JOIN ord_item o
3 ON p.product_id = o.product_id
4 /
DESCRIPTION NVL(O.QUANTITY,0)
--------------------------------------------------------------------------- -----------------
SQL Server 10
Oracle 1
Java 1
Ruby 1
Ruby 1
Javascript 1
Java 1
Ruby 1
Oracle 6
Java 1
Ruby 1
Ruby 1
Ruby 1
Java 1
MySQL 1
Ruby 1
C++ 0
DB2 0
18 rows selected.
SQL> --Eqivalent traditional syntax:
SQL> SELECT p.description, nvl(o.quantity,0)
2 FROM product p, ord_item o
3 WHERE p.product_id = o.product_id(+)
4 /
DESCRIPTION NVL(O.QUANTITY,0)
--------------------------------------------------------------------------- -----------------
SQL Server 10
Oracle 1
Java 1
Ruby 1
Ruby 1
Javascript 1
Java 1
Ruby 1
Oracle 6
Java 1
Ruby 1
Ruby 1
Ruby 1
Java 1
MySQL 1
Ruby 1
C++ 0
DB2 0
18 rows selected.
SQL>
SQL> drop table product;
Table dropped.
SQL> drop table ord_item;
Table dropped.
SQL>
SQL> --
Inner and Outer Joins(room and class)
SQL>
SQL> CREATE TABLE Room (
2 RoomID INT NOT NULL PRIMARY KEY,
3 Comments VARCHAR(50),
4 Capacity INT);
Table created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (1,"Main hall",500);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (2,"Science Department",200);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (3,"Science Room 1",100);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (4,"Languages Block",300);
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE Class (
2 ClassID INT NOT NULL PRIMARY KEY,
3 CourseID INT NOT NULL,
4 InstructorID INT NOT NULL,
5 RoomID INT NOT NULL,
6 Time VARCHAR(50));
Table created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (1,1,1,6,"Mon 09:00-11:00");
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (2,2,1,5,"Mon 11:00-12:00, Thu 09:00-11:00");
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (3,3,2,3,"Mon 14:00-16:00");
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (4,4,3,2,"Tue 10:00-12:00, Thu 14:00-15:00");
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (5,5,2,9,"Tue 14:00-16:00");
1 row created.
SQL>
SQL> SELECT Class.ClassID,
2 Class.CourseID,
3 Class.Time,
4 Room.ruments AS RoomName
5 FROM Class LEFT OUTER JOIN Room
6 ON Class.RoomID = Room.RoomID
7 ORDER BY ClassID
8
SQL>
SQL> drop table class;
Table dropped.
SQL> drop table room;
Table dropped.
SQL>
Outer Joins demo
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
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 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 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>
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
SQL>
SQL>
SQL>
SQL> -- Outer Joins
SQL>
SQL>
SQL> select e.empno, e.ename, d.dname
2 from emp e left outer join dept d
3 on e.deptno = d.deptno
4 where e.job = "MANAGER";
EMPNO ENAME DNAME
---------- ---------- --------------
7782 CLARK ACCOUNTING
7566 JONES RESEARCH
7698 BLAKE SALES
SQL>
SQL>
SQL> drop table dept;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>
SQL>
Right outer join
SQL>
SQL> create table ord_item(
2 order_no integer
3 ,product_id integer
4 ,quantity number(4,0)
5 ,item_price number(7,2)
6 ,total_order_item_price number(9,2)
7 ,primary key (order_no, product_id)
8 );
Table created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(1, 2, 10, 23.00 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(2, 1, 1, 23.00 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(2, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(2, 8, 1, 17.48 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(3, 8, 1, 35.99 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(4, 7, 1, 19.95 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(5, 5, 1, 10.95 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(6, 8, 1, 22.95 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(7, 1, 6, 15.00 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(7, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(7, 8, 1, 10.45 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(8, 8, 1, 35.95 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(9, 8, 1, 65.45 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(9, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(10, 3, 1, 19.95 );
1 row created.
SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(11, 8, 1, 30.00);
1 row created.
SQL>
SQL> create table product(
2 product_id integer primary key
3 ,price number(7,2)
4 ,description varchar2(75)
5 ,onhand number(5,0)
6 ,reorder number(5,0)
7 ,supplier_no integer
8 );
Table created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,"Oracle",100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,"SQL Server",null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,"MySQL",null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,"DB2",50,10);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (5,10.50,"Java",100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (6,45.00,"C++",null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (7,19.99,"Javascript",3,5);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (8,4.50,"Ruby",null,null);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT description, nvl(quantity,0)
2 FROM ord_item RIGHT OUTER JOIN product
3 USING (product_id);
DESCRIPTION NVL(QUANTITY,0)
--------------------------------------------------------------------------- ---------------
SQL Server 10
Oracle 1
Java 1
Ruby 1
Ruby 1
Javascript 1
Java 1
Ruby 1
Oracle 6
Java 1
Ruby 1
Ruby 1
Ruby 1
Java 1
MySQL 1
Ruby 1
C++ 0
DB2 0
18 rows selected.
SQL>
SQL> drop table product;
Table dropped.
SQL> drop table ord_item;
Table dropped.
SQL>
SQL> --
Understanding Outer Joins: e.empno (+) = j.empno
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID
3 jobtitle VARCHAR2(10 BYTE) -- Employee job title
4 )
5 /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, 48000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, 36000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, 85000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
2 values(101, "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(122, "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(123, "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(104, "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(105, "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(116, "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(117, "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(108, "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL>
SQL> select * from job;
EMPNO JOBTITLE
---------- ----------
101 Painter
122 Tester
123 Dediator
104 Chemist
105 Accountant
116 Manager
117 Programmer
108 Developer
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- Understanding Outer Joins: e.empno (+) = j.empno
SQL>
SQL> SELECT e.ename, j.jobtitle
2 FROM employee e, job j
3 WHERE e.empno (+) = j.empno ;
ENAME JOBTITLE
---------- ----------
Alison Tester
James Dediator
Celia Chemist
Robert Accountant
Linda Manager
David Programmer
Jode Developer
Painter
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Understanding Outer Joins: j.empno = e.empno (+)
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID
3 jobtitle VARCHAR2(10 BYTE) -- Employee job title
4 )
5 /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values (101,"Jason",to_date("19960725","YYYYMMDD"), 34000, 44000 "W")
3 /
values (101,"Jason",to_date("19960725","YYYYMMDD"), 34000, 44000 "W")
*
ERROR at line 2:
ORA-00917: missing comma
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, 48000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, 36000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, 85000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
2 values(101, "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(122, "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(123, "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(104, "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(105, "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(116, "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(117, "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(108, "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL>
SQL> select * from job;
EMPNO JOBTITLE
---------- ----------
101 Painter
122 Tester
123 Dediator
104 Chemist
105 Accountant
116 Manager
117 Programmer
108 Developer
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- Understanding Outer Joins
SQL>
SQL> SELECT e.ename, j.jobtitle
2 FROM employee e, job j
3 WHERE j.empno = e.empno (+);
ENAME JOBTITLE
---------- ----------
Alison Tester
James Dediator
Celia Chemist
Robert Accountant
Linda Manager
David Programmer
Jode Developer
Painter
8 rows selected.
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Use an ANSI outer join and use the NVL function to convert nulls to the string "N/A".
SQL>
SQL>
SQL> create table product(
2 product_id integer primary key
3 ,price number(7,2)
4 ,description varchar2(75)
5 ,onhand number(5,0)
6 ,reorder number(5,0)
7 ,supplier_no integer
8 );
Table created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,"Oracle",100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,"SQL Server",null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,"MySQL",null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,"DB2",50,10);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (5,10.50,"Java",100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (6,45.00,"C++",null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (7,19.99,"Javascript",3,5);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (8,4.50,"Ruby",null,null);
1 row created.
SQL>
SQL> select * from product;
PRODUCT_ID PRICE DESCRIPTION ONHAND REORDER SUPPLIER_NO
---------- ---------- --------------------------------------------------------------------------- ---------- ---------- -----------
1 2.5 Oracle 100 20
2 23 SQL Server
3 MySQL
4 1.5 DB2 50 10
5 10.5 Java 100 20
6 45 C++
7 19.99 Javascript 3 5
8 4.5 Ruby
8 rows selected.
SQL>
SQL> create table product_supplier(
2 product_id integer
3 ,supplier_no integer
4 ,price number(7,2)
5 ,primary key (product_id, supplier_no)
6 );
Table created.
SQL> insert into product_supplier values(1,10,2.25);
1 row created.
SQL> insert into product_supplier values(1,11,2.10);
1 row created.
SQL> insert into product_supplier values(1,12,2.85);
1 row created.
SQL> insert into product_supplier values(2,10,22.25);
1 row created.
SQL> insert into product_supplier values(2,11,22.00);
1 row created.
SQL> insert into product_supplier values(2,12,21.25);
1 row created.
SQL>
SQL> select * from product_supplier;
PRODUCT_ID SUPPLIER_NO PRICE
---------- ----------- ----------
1 10 2.25
1 11 2.1
1 12 2.85
2 10 22.25
2 11 22
2 12 21.25
6 rows selected.
SQL>
SQL> create table supplier(
2 supplier_no integer primary key
3 ,supplier_name varchar2(50)
4 ,address varchar(30)
5 ,city varchar(20)
6 ,state varchar2(2)
7 ,area_code varchar2(3)
8 ,phone varchar2(8)
9 );
Table created.
SQL> insert into supplier(supplier_no, supplier_name)values(10,"ABC Gift Supply Co.");
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(11,"BCD Gift Supply Co.");
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(12,"WWW Gift Supply Co.");
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(13,"XYZ Gift Supply Co.");
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(14,"R Gift Supply Co.");
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(15,"D Gift Supply Co.");
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(16,"B Gift Supply Co.");
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(17,"W Gift Supply Co.");
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(18,"P Gift Supply Co.");
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(19,"R Gift Supply Co.");
1 row created.
SQL>
SQL> select * from supplier;
SUPPLIER_NO SUPPLIER_NAME ADDRESS CITY ST ARE PHONE
----------- -------------------------------------------------- ------------------------------ -------------------- -- --- --------
10 ABC Gift Supply Co.
11 BCD Gift Supply Co.
12 WWW Gift Supply Co.
13 XYZ Gift Supply Co.
14 R Gift Supply Co.
15 D Gift Supply Co.
16 B Gift Supply Co.
17 W Gift Supply Co.
18 P Gift Supply Co.
19 R Gift Supply Co.
10 rows selected.
SQL>
SQL>
SQL>
SQL> select supplier_name, nvl(description, "N/A") AS description
2 from supplier left outer join product_supplier using (supplier_no)
3 left outer join product using (product_id);
SUPPLIER_NAME DESCRIPTION
-------------------------------------------------- ---------------------------------------------------------------------------
WWW Gift Supply Co. Oracle
BCD Gift Supply Co. Oracle
ABC Gift Supply Co. Oracle
WWW Gift Supply Co. SQL Server
BCD Gift Supply Co. SQL Server
ABC Gift Supply Co. SQL Server
B Gift Supply Co. N/A
R Gift Supply Co. N/A
P Gift Supply Co. N/A
W Gift Supply Co. N/A
R Gift Supply Co. N/A
D Gift Supply Co. N/A
XYZ Gift Supply Co. N/A
13 rows selected.
SQL>
SQL>
SQL>
SQL> drop table supplier;
Table dropped.
SQL> drop table product_supplier;
Table dropped.
SQL> drop table product;
Table dropped.
SQL>
SQL> --