Oracle PL/SQL/Table Joins/Natural join
Natural join 2
<source lang="sql">
SQL> SQL> SQL> create table department(
2 dept_no integer primary key 3 ,dept_name varchar(20) not null 4 ,mgr_no integer 5 );
Table created. SQL> SQL> insert into department(dept_no, dept_name, mgr_no)values(1, "Design", 1); 1 row created. SQL> insert into department(dept_no, dept_name, mgr_no)values(2, "Sales", 1); 1 row created. SQL> insert into department(dept_no, dept_name, mgr_no)values(3, "Development", 1); 1 row created. SQL> SQL> create table employee(
2 emp_no integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,zip_4 varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,salary number(3) 14 ,birthdate date 15 ,hiredate date 16 ,title varchar2(20) 17 ,dept_no integer 18 ,mgr integer 19 ,region number 20 ,division number 21 ,total_sales number 22 );
Table created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","221","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created. SQL> SQL> SQL> select dept_name, lastname
2 from department natural inner join employee 3 /
DEPT_NAME LASTNAME
--------------------
Sales Anderson Sales Last Design Wash Design Bush Design Will Sales Pete Development Roke Development Horry Development Bar 9 rows selected. SQL> SQL> --Equivilant standard SQL join: SQL> select d.dept_name, e.lastname
2 from department d, employee e 3 where d.dept_no = e.dept_no 4 /
DEPT_NAME LASTNAME
--------------------
Sales Anderson Sales Last Design Wash Design Bush Design Will Sales Pete Development Roke Development Horry Development Bar 9 rows selected. SQL> SQL> drop table department; Table dropped. SQL> drop table employee; Table dropped. SQL> SQL> --
</source>
natural join Demo
<source lang="sql">
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 );
SQL> SQL> SQL> SQL> insert into departments ( department_id, department_name )
2 values( 1, "Data Group" );
SQL> SQL> insert into departments ( department_id, department_name )
2 values( 2, "Purchasing" );
SQL> SQL> insert into departments ( department_id, department_name )
2 values( 3, "Call Center" );
SQL> SQL> insert into departments ( department_id, department_name )
2 values( 4, "Communication" );
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.ru", "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.ru", "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.ru", "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.ru", 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 .ru", 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.ru", 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.ru", 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.ru", 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.ru 01-JAN-02 MGR 1 30000 1004
1002 Wells wells@g.ru 01-JAN-02 DBA 2 20000 1005
1003 Bliss bliss@g.ru 01-JAN-02 PROG 3 24000 1004
1004 Kyte tkyte@a.ru 13-JUN-98 MGR 4 25000 1005
1005 Viper sdillon@a .ru 10-JUN-08 PROG 1 20000 1006
1006 Beck clbeck@g.ru 10-JUN-08 PROG 2 20000
1007 Java java01@g.ru 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.ru 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 employee_id, last_name, department_name
2 from employees natural join departments 3 where department_name = "Purchasing" 4 /
EMPLOYEE_ID LAST_NAME DEPARTMENT_NA
-------------------------------------------------- -------------
1002 Wells Purchasing
1006 Beck Purchasing
SQL> SQL> SQL> drop table employees cascade constraints; Table dropped. SQL> drop table departments cascade constraints; Table dropped. SQL> SQL>
</source>
Using Natural Joins.sql
<source lang="sql">
SQL> 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> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (5,"Languages Room 1",75); 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 NATURAL JOIN Room 6 ORDER BY ClassID; CLASSID COURSEID TIME
---------- --------------------------------------------------
ROOMNAME
2 2 Mon 11:00-12:00, Thu 09:00-11:00
Languages Room 1
3 3 Mon 14:00-16:00
Science Room 1
4 4 Tue 10:00-12:00, Thu 14:00-15:00
Science Department
3 rows selected. SQL> SQL> SQL> drop table room; Table dropped. SQL> drop table class; Table dropped. SQL>
</source>
