# SQL form of project database from DB Systems Using Oracle, Shah, p 6

# paste this into the SQL reader of your choice
# to use the Oracle web interface (instead of the SQL*Plus reader),
# I had to select one line at a time and press the RUN button for each line.

# if you paste into one window and run commands in another, you may need to COMMIT

create table employee (
   empno integer,
   ename varchar(255),
   deptno       integer,
   projno       integer,
   salary	integer,
   primary key (empno)
);

create table parts (
   partno	integer,
   partdesc	varchar(255),
   vendor	varchar(255),
   cost		float,
   primary key (partno)
);

create table department(
   deptno	integer,
   deptname	varchar(255),
   primary key(deptno)
);

create table prjparts (
   projno	integer,
   partno	integer,
   quantity	integer,
   primary key (projno, partno)
);

create table proj2002 (
   projno	integer,
   location	varchar(255),
   customer	varchar(255),
   primary key(projno)
);

create table proj2003 (
   projno	integer,
   location	varchar(255),
   customer	varchar(255),
   primary key(projno)
);


insert into employee values(101, 'Carter', 10, 1, 25000);
insert into employee values(102, 'Albert', 20, 3, 37000);
insert into employee values(103, 'Breen',  30, 6, 50500);
insert into employee values(104, 'Gould',  20, 5, 23700);
insert into employee values(105, 'Barker', 10, 7, 75000);

insert into parts values(11, 'nut',    'Richards', 19.95);
insert into parts values(22, 'bolt',   'Black'   ,  5.00);
insert into parts values(33, 'washer', 'Mobley'  , 55.99);

insert into department values(10, 'production');
insert into department values(20, 'supplies');
insert into department values(30, 'marketing');

insert into prjparts values(1, 11, 20);
insert into prjparts values(2, 33,  5);
insert into prjparts values(3, 11,  7);
insert into prjparts values(1, 22, 10);
insert into prjparts values(2, 11,  3);

insert into proj2002 values(1, 'Miami',   'Stocks');
insert into proj2002 values(3, 'Trenton', 'Smith');
insert into proj2002 values(5, 'Phoenix', 'Robins');
insert into proj2002 values(6, 'Edison',  'Shaw');
insert into proj2002 values(7, 'Seattle', 'Douglas');

insert into proj2003 values(1, 'Miami',     'Stocks');
insert into proj2003 values(2, 'Orlando',   'Allen');
insert into proj2003 values(3, 'Trenton',   'Smith');
insert into proj2003 values(4, 'Charlotte', 'Jones');





# some things to try

select * from employee;

select * from employee where deptno = 10;
select * from employee where 101 <= empno and empno <= 103;

select * from employee, department;
select * from employee, department where employee.deptno = department.deptno;


select * from proj2002, prjparts, parts 
where proj2002.projno = prjparts.projno and prjparts.partno = parts.partno;