/*Create database t1821

Create table dept
(deptno numeric(2)not null,
 dname char(14),
 loc char(13)
);

Create table emp 
(empno numeric(4) not null,
 ename char(10),
 job char(9),
 mgr numeric(4),
 hiderate datetime,
 sal numeric(7,2),
 com numeric(7,2),
 deptno numeric(2) not null
);

Create table bonus
(ename char(10),
 job char(9),
 sal numeric,
 comm numeric
);

create table salgrade
(grade numeric,
 losal numeric,
 hisal numeric
);

create table customer
(zip char(9),
 state char(2),
 repid numeric(4) not null,
 phone char(9),
 name char(45),
 custid numeric(6) not null,
 creditlimit numeric(9,2),
 city char(90),
 area numeric(3),
 address char(40),
 comments text
);

create table ord
(total numeric(8,2),
 shipdate datetime,
 ordid numeric(4) not null,
 orderdate datetime,
 custid numeric(6) not null,
 commplan char(1)
);

create table item
(qty numeric(8),
 prodid numeric(6),
 ordid numeric(4) not null,
 itemtot numeric(8,2),
 itemid numeric(4) not null,
 actualprice numeric(8,2)
);

create table product
(prodid numeric(6),
 descrp char(30),
);

create table price
(stdprice numeric(8,2),
 startdate datetime,
 prodid numeric(6) not null,
 minprice numeric(8,2),
 enddate datetime
);


insert into dept 
values (10,'Accounting','new york')

insert into dept 
values (20,'research','dallas')

insert into dept 
values (30,'sales','chicago')

insert into dept 
values (40,'operation','boston')

insert into emp
values (7839,'KING', 'PRESIDENT', NULL,'17-NOV-81', 5000, NULL, 10)

insert into emp
values (7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-81', 2850, NULL, 30)

insert into emp
values (7782, 'CLARK', 'MANAGER' ,7839, '9-JUN-81', 2450, NULL, 10)

insert into emp
values (7566, 'JONES', 'MANAGER', 7839, '2-APR-81', 2975, NULL, 20)

insert into emp
values (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-81', 1250, 1400, 30) 

insert into emp
values (7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-81' ,1600,300,30 )

insert into emp
values (7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-81', 1500, 0, 30)

insert into emp
values (7900, 'JAMES', 'CLERK', 7698, '3-DEC-81', 950, NULL, 30) 

insert into emp
values (7521, 'WARD', 'SALESMAN',7698, '22-FEB-81', 1250, 500, 30)

insert into emp
values (7902, 'FORD', 'ANALYST', 7566, '3-DEC-81', 3000, NULL, 20) 

insert into emp
values (7369, 'SMITH', 'CLERK', 7902, '17-DEC-80', 800, NULL, 20)

insert into emp
values (7788, 'SCOTT','ANALYST',7566, '09-DEC-82', 3000, NULL, 20)

insert into emp
values (7876, 'ADAMS', 'CLERK', 7788, '12-JAN-83', 1100, NULL, 20)

insert into emp
values (7934, 'MILLER','CLERK', 7782, '23-JAN-82',1300, NULL, 10)

insert into salgrade
values(1, 700, 1200) 

insert into salgrade
values(2, 1201, 1400)

insert into salgrade
values(3, 1401, 2000) 

insert into salgrade
values(4, 2001, 3000)

insert into salgrade
values(5, 3001, 9999)


insert into customer
values(96711, 'CA', 7844, 598-6609, 'JOCKSPORTS', 100, 5000, 'BELMONT', 415, '345 VIEWRIDGE', 'Very friendly people to work with -- sales rep likes to be called Mike')

insert into customer
values(94061, 'CA', 7521,368-1223, 'TKB SPORT SHOP', 101, 10000, 'REDWOOD CITY', 415, '490 BOLI RD.', 'Rep called 5/8 about change in order - contact shipping')

insert into customer
values(95133, 'CA', 7654, 644-3341, 'VOLLYRITE', 102, 7000, 'BURLINGAME', 415, '9722 HAMILTON', 'Company doing heavy promotion beginning 10/89. Prepare for large orders during winter')

insert into customer
values(97544, 'CA', 7521, 677-9312, 'JUST TENNIS', 103, 3000, 'BURLINGAME' ,415, 'HILLVIEW MALL', 'Contact rep about new line of tennis rackets')

insert into customer
values(93301, 'CA',7499, 996-2323, 'EVERY MOUNTAIN',104, 10000, 'CUPERTINO' ,408, '574 SURRY RD.', 'Customer with high market share (23%) due to aggressive advertising.')

insert into customer
values(91003, 'CA',7844, 376-9966, 'K + T SPORTS',105, 5000, 'SANTA CLARA', 408, '3476 EL PASEO' ,'Tends to order large amounts of merchandise at once. Accounting is considering raising their credit limit.')


insert into ord
values(101.4, '08-JAN-87', 610, '07-JAN-87', 101, 'A') 

insert into ord
values(45, '11-JAN-87', 611,'11-JAN-87', 102, 'B') 

insert into ord
values(5860, '20-JAN-87', 612, '15-JAN-87', 104, 'C')

insert into ord
values(2.4, '30-MAY-86', 601, '01-MAY-86', 106, 'A') 

insert into ord
values(56, '20-JUN-86', 602, '05-JUN-86', 102, 'B') 

insert into ord
values(698, '30-JUN-86',604, '15-JUN-86', 106, 'A') 

insert into ord
values(8324, '30-JUL-86', 605, '14-JUL-86', 106,'A') 

insert into ord
values(3.4, '30-JUL-86', 606, '14-JUL-86',100, 'A') 

insert into ord
values(97.5, '15-AUG-86', 609, '01-AUG-86', 100, 'B') 

insert into ord
values(5.6, '18-JUL-86', 607, '18-JUL-86', 104, 'C')


insert into item
values(1,100890,610,58,3,58) 

insert into item
values(1, 100861, 611, 45, 1, 45)

insert into item
values(100, 100860, 612, 3000, 1, 30) 

insert into item
values(1, 200376, 601, 2.4, 1, 2.4)

insert into item
values(20, 100870, 602, 56, 1, 2.8)

insert into item
values(3, 100890, 604, 174, 1, 58)

insert into item
values(2, 100861, 604, 84, 2, 42)

insert into item
values(10, 100860, 604, 440, 3, 44)
 
insert into item
values(4, 100860, 603, 224, 2, 56)

insert into item
values(1, 100860, 610, 35, 1, 35)
 
insert into item
values(3, 100870, 610, 8.4, 2, 2.8)
 
insert into item
values(200, 200376, 613, 440, 4, 2.2) 

insert into item
values(444, 100860, 614, 15540, 1, 35)
 
insert into item
values(1000, 100870, 614, 2800, 2, 2.8) 

insert into item
values(20, 100861, 612, 810, 2, 40.5)


INSERT INTO PRODUCT
VALUES(100860, 'ACE TENNIS RACKET I')

INSERT INTO PRODUCT
VALUES(100861, 'ACE TENNIS RACKET II')

INSERT INTO PRODUCT
VALUES(100870, 'ACE TENNIS BALLS-3 PACK')

INSERT INTO PRODUCT
VALUES(100871, 'ACE TENNIS BALLS-6 PACK')

INSERT INTO PRODUCT
VALUES(100890, 'ACE TENNIS NET' )

INSERT INTO PRODUCT
VALUES(101860, 'SP TENNIS RACKET')

INSERT INTO PRODUCT
VALUES(101863, 'SP JUNIOR RACKET')
 

alter table bonus
add startdate datetime

alter table bonus
drop column startdate 


alter table dept
add constraint dept_primary_key
primary key (deptno)


alter table emp
add constraint emp_primary_key
primary key (empno)

update price
set enddate='31-OCT-99'
where startdate='01-JAN-86';


delete from ord
where total=97.50

delete from item
where (ordid=602 OR ordid=603);


alter table bonus
add startdate datetime;

alter table bonus
drop column startdate ;


alter table bonus
add startdate datetime;

alter table bonus
alter column startdate char(12)


update ord
set custid=105
where custid=106;
*/