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