Inner join primary key and foreign key : Inner Joins « Table Joins « Oracle PL / SQL






Inner join primary key and foreign key

   
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', 'YourName@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, 'Dillon', '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                                               YourName@a.com                 13-JUN-98 MGR                                        4      25000       1005
       1005 Dillon                                             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                                             oracle1@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 inner join departments d
  3          on e.department_id = d.department_id
  4       where e.job_id = 'DBA'
  5      /

EMPLOYEE_ID LAST_NAME                                          DEPARTMENT_NA
----------- -------------------------------------------------- -------------
       1002 Wells                                              Purchasing
       1008 Oracle                                             Communication

SQL>
SQL>
SQL> drop table employees cascade constraints;

Table dropped.

SQL> drop table departments cascade constraints;

Table dropped.

   
    
  








Related examples in the same category

1.Inner joins use join conditions in the FROM clause just like natural joins
2.Three inner joins query
3.Inner join with null foreign key
4.Reference inner query column in outter select statement
5.An inner join to
6.Inner and Outer Joins
7.Using Inner Joins