Mega Code Archive

 

Categories / Oracle PLSQL / Table Joins

 

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.