natural join Demo : Natural join « Table Joins « Oracle PL / SQL






natural join Demo

  
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.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 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>

   
  








Related examples in the same category

1.Natural join 2
2.Using Natural Joins.sql