Join three table to find out which employee sold that gift : Table Join Column « Table Joins « Oracle PL / SQL

1. Oracle PL / SQL
2. Table Joins
3. Table Join Column

Join three table to find out which employee sold that gift

  
SQL>
SQL>
SQL> create table emp(
  2           emp_id                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          ,shortZipCode                   varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,company_name           varchar2(50));

Table created.

SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (1,'Jones','Joe','J','10 Park Ave','New York','NY','11202','3898','212', '221-4333','Big Company');

1 row created.

SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (2,'Smith','Sue','J','20 Rise Ave','New York','NY','11444','3898','212', '436-6773','Little Company');

1 row created.

SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (3,'X','Peggy','J','500 June St','New York','NY','45502','3668','212', '234-4444','Medium Company');

1 row created.

SQL>
SQL> create table gift(
  2           gift_id                integer         primary key
  3          ,emp_id                integer
  4          ,register_date              date not null
  5          ,total_price        number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment        varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,message        varchar2(100)
 12  );

Table created.

SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
  2                 (1,1,'14-Feb-1999', 123.12, '14-Feb-1999', '12 noon', 'CA',1, null, 'Happy Birthday to you');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
  2                 (2,1,'14-Feb-1999', 50.98, '14-feb-1999', '1 pm', 'CA',7, 'name1', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (3, 2,'14-Feb-1999', 35.99, '14-feb-1999', '1 pm', 'VS',2, 'Tom', 'Happy Birthday');

1 row created.

SQL>
SQL> create table sale(
  2           gift_id            integer
  3          ,item_id            integer
  4          ,quantity               number(4,0)
  5          ,price                 number(7,2)
  6          ,primary key (gift_id ,item_id)
  7  );

Table created.

SQL> insert into sale(gift_id, item_id, quantity, price) values(1, 2, 10, 23.00 );

1 row created.

SQL> insert into sale(gift_id, item_id, quantity, price) values(2, 1, 1, 23.11 );

1 row created.

SQL>
SQL>
SQL> select c.firstname || ' ' || c.lastname as "Name", '(' || c.area_code || ')' ||
  2   c.phone as "Telephone", o.gift_id, o.deliver_date, oi.item_id,
  3   oi.quantity
  4    from gift o, emp c, sale oi
  5   where o.emp_id = c.emp_id
  6     and o.gift_id = oi.gift_id
  7     and oi.item_id = 8;

no rows selected

SQL>
SQL>
SQL> drop table sale;

Table dropped.

SQL> drop table gift;

Table dropped.

SQL> drop table emp;

Table dropped.

   
    
  

Related examples in the same category