Banner

 

Oracle

Code for creating tables

Notes:

Chapter 3

SHR
create table shr
(shrcode char(3) not null,
shrfirm char(20),
shrprice number(6,2),
shrqty number(8),
shrdiv number(5,2),
shrpe number(2),
constraint pk_shr primary key (shrcode));
/* Inserts */
insert into shr values ('FC','Freedonia Copper',27.5,10529,1.84,16);
insert into shr values ('PT','Patagonian Tea',55.25,12635,2.5,10);
insert into shr values ('AR','Abyssinian Ruby',31.82,22010,1.32,13);
insert into shr values ('SLG','Sri Lankan Gold',50.37,32868,2.68,16);
insert into shr values ('ILZ','Indian Lead & Zinc',37.75,6390,3,12);
insert into shr values ('BE','Burmese Elephant',0.07,154713,0.01,3);
insert into shr values ('BS','Bolivian Sheep',12.75,231678,1.78,11);
insert into shr values ('NG','Nigerian Geese',35,12323,1.68,10);
insert into shr values ('CS','Canadian Sugar',52.78,4716,2.5,15);
insert into shr values ('ROF','Royal Ostrich Farms',33.75,1234923,3,6);

ALIEN
create table alien
(alnum integer not null,
alname char(20),
alheads integer,
alcolor char(15),
alsmell char(15),
constraint pk_alien primary key (alnum));
/* Inserts */
insert into alien values (1,'Foozelgiga',1,'vermillion','ambrosial');
insert into alien values (2,'Koofoopsiepus',3,'amethyst','putrid');
insert into alien values (3,'Bunyippa',1,'chartreuse','perfumed');
insert into alien values (4,'Meekamuncha',7,'lavender','old cabbage');
insert into alien values (5,'Eop',0,'chartreuse','new car');

Chapter 4

NATION
create table nation
(natcode char(3) not null,
natname char(20),
exchrate number(9,5),
constraint pk_nation primary key (natcode));
/* Inserts */
insert into nation values ('UK','United Kingdom',1);
insert into nation values ('USA','United States',0.67);
insert into nation values ('AUS','Australia',0.46);
insert into nation values ('IND','India',0.0228);

STOCK
create table stock
(stkcode char(3) not null,
stkfirm char(20),
stkprice number(6,2),
stkqty number(8),
stkdiv number(5,2),
stkpe number(5),
natcode char(3),
constraint pk_stock primary key (stkcode),
constraint fk_hasnation foreign key (natcode) references nation(natcode));
/* Inserts */
insert into stock values ('FC','Freedonia Copper',27.5,10529,1.84,16,'UK');
insert into stock values ('PT','Patagonian Tea',55.25,12635,2.5,10,'UK');
insert into stock values ('AR','Abyssinian Ruby',31.82,22010,1.32,13,'UK');
insert into stock values ('SLG','Sri Lankan Gold',50.37,32868,2.68,16,'UK');
insert into stock values ('ILZ','Indian Lead & Zinc',37.75,6390,3,12,'UK');
insert into stock values ('BE','Burmese Elephant',0.07,154713,0.01,3,'UK');
insert into stock values ('BS','Bolivian Sheep',12.75,231678,1.78,11,'UK');
insert into stock values ('NG','Nigerian Geese',35,12323,1.68,10,'UK');
insert into stock values ('CS','Canadian Sugar',52.78,4716,2.5,15,'UK');
insert into stock values ('ROF','Royal Ostrich Farms',33.75,1234923,3,6,'UK');
insert into stock values ('MG','Minnesota Gold',53.87,816122,1,25,'USA');
insert into stock values ('GP','Georgia Peach',2.35,387333,0.2,5,'USA');
insert into stock values ('NE','Narembeen Emu',12.34,45619,1,8,'AUS');
insert into stock values ('QD','Queensland Diamond',6.73,89251,0.5,7,'AUS');
insert into stock values ('IR','Indooroopilly Ruby',15.92,56147,0.5,20,'AUS');
insert into stock values ('BD','Bombay Duck',25.55,167382,1,12,'IND');

PERSON
create table person
(personid integer not null,
persfname char(25),
perslname char(25),
constraint pk_person primary key (personid));
/* Inserts */
insert into person values (21,'Sheila','O''Hara');
insert into person values (2,'Gigi','Garfield');
insert into person values (63,'Barbara','Capelli');
insert into person values (74,'James','Haley');
insert into person values (5,'Nolan','Haley');
insert into person values (16,'Kwok-Kee','Tan');
insert into person values (7,'Macedonio','Gomez');
insert into person values (8,'Bruce','Bush');
insert into person values (99,'Sue','Lim');
insert into person values (10,'Kendra','Haley');


CAR
create table car
(carid integer not null,
carcost number(7,2),
carsell number(7,2),
cartype char(10),
personid integer,
constraint pk_car primary key (carid),
constraint fk_soldby foreign key (personid) references person(personid));
/* Inserts */
insert into car values (1,500,795,'sedan',5);
insert into car values (2,15500,14750,'coupe',8);
insert into car values (3,1255,1355,'sports',8);
insert into car values (4,950,2000,'sedan',8);
insert into car values (5,7500,9000,'sports',2);
insert into car values (6,5400,6000,'sedan',99);
insert into car values (7,10300,12000,'sedan',99);
insert into car values (8,5700,9000,'coupe',99);
insert into car values (9,9000,12000,'sports',74);
insert into car values (10,6000,6500,'sedan',63);

Chapter 5


SALE
create table sale
(saleno integer not null,
saledate date,
saletext char(50),
constraint pk_sale primary key (saleno));
/* Inserts */
insert into sale values (1,'15-Jan-95','Scruffy Australian - called himself Bruce');
insert into sale values (2,'15-Jan-95','Man. Rather fond of hats.');
insert into sale values (3,'15-Jan-95','Woman. Planning to row Atlantic - lengthwise!');
insert into sale values (4,'15-Jan-95','Man. Trip to New York - thinks NY is a jungle!');
insert into sale values (5,'16-Jan-95','Expedition leader for African safari.');

ITEM
create table item
(itemno integer not null,
itemname char(30),
itemtype char(1),
itemcolor char(10),
constraint pk_item primary key (itemno));
/* Inserts */
insert into item values (1,'Pocket knife - Nile','E','Brown');
insert into item values (2,'Pocket knife - Avon','E','Brown');
insert into item values (3,'Compass','N','-');
insert into item values (4,'Geo positioning system','N','-');
insert into item values (5,'Map measure','N','-');
insert into item values (6,'Hat - Polar explorer','C','Red');
insert into item values (7,'Hat - Polar explorer','C','White');
insert into item values (8,'Boots - snake proof','C','Green');
insert into item values (9,'Boots - snake proof','C','Black');
insert into item values (10,'Safari chair','F','Khaki');
insert into item values (11,'Hammock','F','Khaki');
insert into item values (12,'Tent - 8 person','F','Khaki');
insert into item values (13,'Tent - 2 person','F','Khaki');
insert into item values (14,'Safari cooking kit','E','-');
insert into item values (15,'Pith helmet','C','Khaki');
insert into item values (16,'Pith helmet','C','White');
insert into item values (17,'Map case','N','Brown');
insert into item values (18,'Sextant','N','-');
insert into item values (19,'Stetson','C','Black');
insert into item values (20,'Stetson','C','Brown');


LINEITEM
create table lineitem
(lineno integer not null,
lineqty integer,
lineprice number(7,2),
saleno integer not null,
itemno integer not null,
constraint pk_lineitem primary key (lineno,saleno),
constraint fk_hassale foreign key (saleno) references sale (saleno),
constraint fk_hasitem foreign key (itemno) references item (itemno));
/* Inserts */
insert into lineitem values (1,1,4.5,1,2);
insert into lineitem values (1,1,25,2,6);
insert into lineitem values (2,1,20,2,16);
insert into lineitem values (3,1,25,2,19);
insert into lineitem values (4,1,2.25,2,2);
insert into lineitem values (1,1,500,3,4);
insert into lineitem values (2,1,2.25,3,2);
insert into lineitem values (1,1,500,4,4);
insert into lineitem values (2,1,65,4,9);
insert into lineitem values (3,1,60,4,13);
insert into lineitem values (4,1,75,4,14);
insert into lineitem values (5,1,10,4,3);
insert into lineitem values (6,1,2.25,4,2);
insert into lineitem values (1,50,36,5,10);
insert into lineitem values (2,50,40.5,5,11);
insert into lineitem values (3,8,153,5,12);
insert into lineitem values (4,1,60,5,13);
insert into lineitem values (5,1,0,5,2);

DONOR
create table donor
(donorno integer not null,
dlname char(15),
dfname char(15),
dphone number(4),
dstate char(2),
dcity char(15),
constraint pk_donor primary key (donorno));
/* Inserts */
insert into donor values (101,'Abrams','Louis',9018,'GA','London');
insert into donor values (102,'Aldinger','Dmitry',1521,'GA','Paris');
insert into donor values (103,'Beckman','Gulsen',8247,'WA','Sao Paulo');
insert into donor values (104,'Berdahl','Samuel',8149,'WI','Sydney');
insert into donor values (105,'Borneman','Joanna',1888,'MD','Bombay');
insert into donor values (106,'Brock','Scott',2142,'AL','London');
insert into donor values (107,'Buyert','Aylin',9355,'AK','New York');
insert into donor values (108,'Cetinsoy','Girwan',6346,'AZ','Rome');
insert into donor values (109,'Chisholm','John',4482,'MA','Oslo');
insert into donor values (110,'Crowder','Anthony',6513,'NC','Stockholm');
insert into donor values (111,'Dishman','Michelle',3903,'NC','Helsinki');
insert into donor values (112,'Duke','Peter',4939,'FL','Tokyo');
insert into donor values (113,'Evans','Ann',4336,'GA','Singapore');
insert into donor values (114,'Frawley','Todd',4785,'MN','Perth');
insert into donor values (115,'Guo','John',6247,'MN','Moscow');
insert into donor values (116,'Hammann','John',5369,'ND','Kabaul');
insert into donor values (117,'Hays','Cami',1352,'SD','Lima');
insert into donor values (118,'Herskowitz','Thomas',6872,'MT','London');
insert into donor values (119,'Jefts','Robert',8103,'ME','Oslo');

YEAR
create table year
(year integer not null,
yeargoal number(9),
constraint pk_year primary key (year));
/* Inserts */
insert into year values (1992,5000);
insert into year values (1993,5000);
insert into year values (1994,5500);
insert into year values (1995,5000);

GIFT
create table gift
(amount number(8) not null,
year integer not null,
donorno integer not null,
constraint pk_gift primary key (year,donorno),
constraint fk_donatedin foreign key (year) references year(year),
constraint fk_donatedby foreign key (donorno) references donor(donorno));
/* Inserts */
insert into gift values (939,1993,101);
insert into gift values (899,1993,102);
insert into gift values (111,1994,102);
insert into gift values (373,1992,101);
insert into gift values (543,1992,102);
insert into gift values (1185,1992,103);
insert into gift values (1362,1993,103);
insert into gift values (5208,1994,103);
insert into gift values (1865,1995,103);
insert into gift values (667,1993,105);
insert into gift values (60,1995,106);
insert into gift values (332,1994,107);
insert into gift values (674,1993,108);
insert into gift values (155,1994,108);
insert into gift values (838,1992,109);
insert into gift values (499,1994,109);
insert into gift values (582,1992,110);
insert into gift values (297,1993,110);
insert into gift values (84,1994,110);
insert into gift values (823,1995,110);
insert into gift values (887,1992,111);
insert into gift values (332,1993,111);
insert into gift values (882,1994,111);
insert into gift values (666,1992,112);
insert into gift values (812,1995,112);
insert into gift values (560,1994,113);
insert into gift values (223,1992,114);
insert into gift values (835,1994,114);
insert into gift values (558,1993,115);
insert into gift values (268,1993,116);
insert into gift values (345,1994,116);
insert into gift values (265,1995,116);
insert into gift values (82,1992,117);
insert into gift values (657,1995,117);
insert into gift values (17,1995,118);
insert into gift values (186,1992,119);
insert into gift values (772,1993,119);

Chapter 6

DEPT
create table dept (deptname char(15) not null,
deptfloor smallint not null,
deptphone smallint not null,
empno smallint not null,
constraint pk_dept primary key(deptname));
/* Inserts */
insert into dept values ('Management',5,2001,1);
insert into dept values ('Marketing',1,2002,2);
insert into dept values ('Accounting',4,2003,5);
insert into dept values ('Purchasing',4,2004,7);
insert into dept values ('Personnel',1,2005,9);

EMP
create table emp
(empno integer not null,
empfname char(10),
empsalary number(7,0),
deptname char(15),
bossno integer,
constraint pk_emp primary key(empno),
constraint fk_belongdept foreign key (deptname) references dept(deptname));
/* Inserts */
/* Use column names to handle the null for bossno */
insert into emp (empno, empfname, empsalary, deptname)
values (1,'Alice',75000,'Management');
insert into emp values (2,'Ned',45000,'Marketing',1);
insert into emp values (3,'Andrew',25000,'Marketing',2);
insert into emp values (4,'Clare',22000,'Marketing',2);
insert into emp values (5,'Todd',38000,'Accounting',1);
insert into emp values (6,'Nancy',22000,'Accounting',5);
insert into emp values (7,'Brier',43000,'Purchasing',1);
insert into emp values (8,'Sarah',56000,'Purchasing',7);
insert into emp values (9,'Sophie',35000,'Personnel',1);

MONARCH
create table monarch
(monname char(15) not null,
monnum char(5) not null,
montype char(5) not null,
rgnbeg date,
premonname char(15),
premonnum char(5),
sucmonname char(15),
sucmonnum char(5),
constraint pk_monarch primary key(monname,monnum));
/* Inserts */
/* use oracle's TO_DATE function to handle four digit dates correctly */
insert into monarch values
('Victoria','I','Queen',TO_DATE('20-Jun-1837','DD-MON-YYYY'),'William','IV','Edward','VII');insert into monarch values ('Edward','VII','King',TO_DATE('22-Jan-1901','DD-MON-YYYY'),'Victoria','I','George','V');
insert into monarch values ('George','V','King',TO_DATE('6-May-1910','DD-MON-YYYY'),'Edward','VII','Edward','VIII');
insert into monarch values ('Edward','VIII','King',TO_DATE('20-Jan-1936','DD-MON-YYYY'),'George','V','George','VI');
insert into monarch values('George','VII','King',TO_DATE('11-Dec-1936','DD-MON-YYYY'),'Edward','VIII','Elizabeth','II');
/* Use column names to handle the null for succeeding monarch */
insert into monarch (monname, monnum, montype, rgnbeg, premonname, premonnum) values ('Elizabeth','II','Queen',TO_DATE('06-Feb-1952','DD-MON-YYYY'),'George','VI');

PRODUCT
create table product
(prodid integer not null,
proddesc char(30),
prodcost number(9,2),
prodprice number(9,2),
constraint pk_product primary key(prodid));
/* Insert values */
/* Use column names to handle the null for prodcost */
insert into product (prodid, proddesc, prodprice)
values (1000,'Animal photography kit',725);
insert into product values (101,'35mm camera','150',300);
insert into product values (102,'Camera case','10',15);
insert into product values (103,'70-210 zoom lens','125',200);
insert into product values (104,'28-85 zoom lens','115',185);
insert into product values (105,'Photographers vest','25',40);
insert into product values (106,'Lens cleaning cloth','1',1);
insert into product values (107,'Tripod','35',45);
insert into product values (108,'24/100 ASA 35mm color neg film','0.85',1);

ASSEMBLY
create table assembly
(quantity integer not null,
prodid integer,
subprodid integer,
constraint pk_assembly primary key(prodid, subprodid),
constraint fk_isproduct foreign key (prodid) references product(prodid),
constraint fk_iscomponent foreign key (subprodid) references
product(prodid));
insert into assembly values (1,1000,101);
insert into assembly values (1,1000,102);
insert into assembly values (1,1000,103);
insert into assembly values (1,1000,104);
insert into assembly values (1,1000,105);
insert into assembly values (2,1000,106);
insert into assembly values (1,1000,107);
insert into assembly values (10,1000,108);

Chapter 10

QITEM
create table qitem
(itemname char(30) not null,
itemtype char(1),
itemcolor char(10),
constraint pk_qitem primary key (itemname));
/* Insert values */
insert into qitem values ('Boots - snakeproof','C','Green');
insert into qitem values ('Camel saddle','R','Brown');
insert into qitem values ('Compass','N','-');
insert into qitem values ('Elephant polo stick','R','Bamboo');
insert into qitem values ('Exploring in 10 Easy Lessons','B','-');
insert into qitem values ('Geo positioning system','N','-');
insert into qitem values ('Hammock','F','Khaki');
insert into qitem values ('Hat - polar explorer','C','White');
insert into qitem values ('How to Win Foreign Friends','B','-');
insert into qitem values ('Map case','E','Brown');
insert into qitem values ('Map measure','N','-');
insert into qitem values ('Pith helmet','C','Khaki');
insert into qitem values ('Pocket knife - Avon','E','Brown');
insert into qitem values ('Pocket knife - Nile','E','Brown');
insert into qitem values ('Safari chair','F','Khaki');
insert into qitem values ('Safari cooking kit','F','-');
insert into qitem values ('Sextant','N','-');
insert into qitem values ('Stetson','C','Black');
insert into qitem values ('Tent - 2 person','F','Khaki');
insert into qitem values ('Tent - 8 person','F','Khaki');

QEMP
create table qemp
(empno integer not null,
empfname char(15),
empsalary number(6),
deptname char(15),
bossno integer,
constraint pk_qemp primary key (empno));
/* Insert values */
insert into qemp values (1,'Alice',75000,'Management',0);
insert into qemp values (2,'Ned',45000,'Marketing',1);
insert into qemp values (3,'Andrew',25000,'Marketing',2);
insert into qemp values (4,'Clare',22000,'Marketing',2);
insert into qemp values (5,'Todd',38000,'Accounting',1);
insert into qemp values (6,'Nancy',22000,'Accounting',5);
insert into qemp values (7,'Brier',43000,'Purchasing',1);
insert into qemp values (8,'Sarah',56000,'Purchasing',7);
insert into qemp values (9,'Sophie',35000,'Personnel',1);
insert into qemp values (10,'Sanjay',15000,'Navigation',3);
insert into qemp values (11,'Rita',15000,'Books',4);
insert into qemp values (12,'Gigi',16000,'Clothes',4);
insert into qemp values (13,'Maggie',16000,'Clothes',4);
insert into qemp values (14,'Paul',11000,'Equipment',3);
insert into qemp values (15,'James',15000,'Equipment',3);
insert into qemp values (16,'Pat',15000,'Furniture',3);
insert into qemp values (17,'Mark',15000,'Recreation',3);

QDEPT
create table qdept
(deptname char(15) not null,
deptfloor integer,
deptphone integer,
empno integer not null,
constraint pk_qdept primary key (deptname),
constraint fk_hasboss foreign key (empno) references qemp (empno));
/* Insert values */
insert into qdept values ('Management',5,34,1);
insert into qdept values ('Books',1,81,4);
insert into qdept values ('Clothes',2,24,4);
insert into qdept values ('Equipment',3,57,3);
insert into qdept values ('Furniture',4,14,3);
insert into qdept values ('Navigation',1,41,3);
insert into qdept values ('Recreation',2,29,4);
insert into qdept values ('Accounting',5,35,5);
insert into qdept values ('Purchasing',5,36,7);
insert into qdept values ('Personnel',5,37,9);
insert into qdept values ('Marketing',5,38,2);

QSALE
create table qsale
(saleno integer not null,
saleqty integer,
itemname char(30) not null,
deptname char(15) not null,
constraint pk_saleno primary key (saleno),
constraint fk_isitem foreign key (itemname) references qitem (itemname),
constraint fk_bydept foreign key (deptname) references qdept (deptname));
/* Insert values */
insert into qsale values (1001,2,'Boots - snakeproof','Clothes');
insert into qsale values (1002,1,'Pith helmet','Clothes');
insert into qsale values (1003,1,'Sextant','Navigation');
insert into qsale values (1004,3,'Hat - polar explorer','Clothes');
insert into qsale values (1005,5,'Pith helmet','Equipment');
insert into qsale values (1006,1,'Pocket knife - Nile','Clothes');
insert into qsale values (1007,1,'Pocket knife - Nile','Recreation');
insert into qsale values (1008,1,'Compass','Navigation');
insert into qsale values (1009,1,'Geo positioning system','Navigation');
insert into qsale values (1010,5,'Map measure','Navigation');
insert into qsale values (1011,1,'Geo positioning system','Books');
insert into qsale values (1012,1,'Sextant','Books');
insert into qsale values (1013,3,'Pocket knife - Nile','Books');
insert into qsale values (1014,1,'Pocket knife - Nile','Navigation');
insert into qsale values (1015,1,'Pocket knife - Nile','Equipment');
insert into qsale values (1016,1,'Sextant','Clothes');
insert into qsale values (1017,1,'Sextant','Equipment');
insert into qsale values (1018,1,'Sextant','Recreation');
insert into qsale values (1019,1,'Sextant','Furniture');
insert into qsale values (1020,1,'Pocket knife - Nile','Furniture');
insert into qsale values (1021,1,'Exploring in 10 Easy Lessons','Books');
insert into qsale values (1022,1,'How to Win Foreign Friends','Books');
insert into qsale values (1023,1,'Compass','Books');
insert into qsale values (1024,1,'Pith helmet','Books');
insert into qsale values (1025,1,'Elephant polo stick','Recreation');
insert into qsale values (1026,1,'Camel saddle','Recreation');

QSPL
create table qspl
(splno integer not null,
splname char(25),
constraint pk_qspl primary key (splno));
/* Insert values */
insert into qspl values (101,'Global Books & Maps');
insert into qspl values (102,'Nepalese Corp.');
insert into qspl values (103,'All Sports Manufacturing');
insert into qspl values (104,'Sweatshops Unlimited');
insert into qspl values (105,'All Points_ Inc.');
insert into qspl values (106,'Sao Paulo Manufacturing');

QDEL
create table qdel
(delno integer not null,
delqty integer not null,
itemname char(30) not null,
deptname char(15) not null,
splno integer not null,
constraint pk_qdel primary key (delno),
constraint fk_delitem foreign key (itemname) references qitem (itemname),
constraint fk_hasdept foreign key (deptname) references qdept (deptname),
constraint fk_hasspl foreign key (splno) references qspl (splno));
/* Insert values */
insert into qdel values (51,50,'Pocket knife - Nile','Navigation',105);
insert into qdel values (52,10,'Pocket knife - Nile','Books',105);
insert into qdel values (53,10,'Pocket knife - Nile','Clothes',105);
insert into qdel values (54,10,'Pocket knife - Nile','Equipment',105);
insert into qdel values (55,10,'Pocket knife - Nile','Furniture',105);
insert into qdel values (56,10,'Pocket knife - Nile','Recreation',105);
insert into qdel values (57,50,'Compass','Navigation',101);
insert into qdel values (58,10,'Geo positioning system','Navigation',101);
insert into qdel values (59,10,'Map measure','Navigation',101);
insert into qdel values (60,25,'Map case','Navigation',101);
insert into qdel values (61,2,'Sextant','Navigation',101);
insert into qdel values (62,1,'Sextant','Equipment',105);
insert into qdel values (63,20,'Compass','Equipment',103);
insert into qdel values (64,1,'Geo positioning system','Books',103);
insert into qdel values (65,15,'Map measure','Navigation',103);
insert into qdel values (66,1,'Sextant','Books',103);
insert into qdel values (67,5,'Sextant','Recreation',102);
insert into qdel values (68,3,'Sextant','Navigation',104);
insert into qdel values (69,5,'Boots - snakeproof','Clothes',105);
insert into qdel values (70,15,'Pith helmet','Clothes',105);
insert into qdel values (71,1,'Pith helmet','Clothes',101);
insert into qdel values (72,1,'Pith helmet','Clothes',102);
insert into qdel values (73,1,'Pith helmet','Clothes',103);
insert into qdel values (74,1,'Pith helmet','Clothes',104);
insert into qdel values (75,5,'Pith helmet','Navigation',105);
insert into qdel values (76,5,'Pith helmet','Books',105);
insert into qdel values (77,5,'Pith helmet','Equipment',105);
insert into qdel values (78,5,'Pith helmet','Furniture',105);
insert into qdel values (79,5,'Pith helmet','Recreation',105);
insert into qdel values (80,10,'Pocket knife - Nile','Navigation',102);
insert into qdel values (81,1,'Compass','Navigation',102);
insert into qdel values (82,1,'Geo positioning system','Navigation',102);
insert into qdel values (83,10,'Map measure','Navigation',102);
insert into qdel values (84,5,'Map case','Navigation',102);
insert into qdel values (85,5,'Compass','Books',102);
insert into qdel values (86,5,'Pocket knife - Avon','Recreation',102);
insert into qdel values (87,5,'Tent - 2 person','Recreation',102);
insert into qdel values (88,2,'Tent - 8 person','Recreation',102);
insert into qdel values (89,5,'Exploring in 10 Easy Lessons','Navigation',102);
insert into qdel values (90,5,'How to Win Foreign Friends','Navigation',102);
insert into qdel values (91,10,'Exploring in 10 Easy Lessons','Books',102);
insert into qdel values (92,10,'How to Win Foreign Friends','Books',102);
insert into qdel values (93,2,'Exploring in 10 Easy Lessons','Recreation',102);
insert into qdel values (94,2,'How to Win Foreign Friends','Recreation',102);
insert into qdel values (95,5,'Compass','Equipment',105);
insert into qdel values (96,2,'Boots - snakeproof','Equipment',105);
insert into qdel values (97,20,'Pith helmet','Equipment',106);
insert into qdel values (98,20,'Pocket knife - Nile','Equipment',106);
insert into qdel values (99,1,'Sextant','Equipment',106);
insert into qdel values (100,3,'Hat - polar explorer','Clothes',105);
insert into qdel values (101,3,'Stetson','Clothes',105);

Drop constraints and tables










ALTER TABLE CAR
	DROP CONSTRAINT FK_SOLDBY;
ALTER TABLE LINEITEM
	DROP CONSTRAINT FK_HASSALE;
ALTER TABLE LINEITEM
	DROP CONSTRAINT FK_HASITEM;
ALTER TABLE GIFT
	DROP CONSTRAINT FK_DONATEDIN;
ALTER TABLE GIFT
	DROP CONSTRAINT FK_DONATEDBY;
ALTER TABLE STOCK
	DROP CONSTRAINT FK_HASNATION;
ALTER TABLE EMP
	DROP CONSTRAINT FK_BELONGDEPT;
ALTER TABLE ASSEMBLY
	DROP CONSTRAINT FK_ISPRODUCT;
ALTER TABLE ASSEMBLY
	DROP CONSTRAINT FK_ISCOMPONENT;
ALTER TABLE QDEL
	DROP CONSTRAINT FK_DELITEM
ALTER TABLE QDEL
	DROP CONSTRAINT FK_HASDEPT
ALTER TABLE QDEL
	DROP CONSTRAINT FK_HASSPL;
ALTER TABLE QSALE
	DROP CONSTRAINT FK_ISITEM;
ALTER TABLE QSALE
	DROP CONSTRAINT FK_BYDEPT;
ALTER TABLE QDEPT
 	DROP CONSTRAINT FK_HASBOSS;
 
DROP TABLE SHR;
DROP TABLE ALIEN;
DROP TABLE NATION;
DROP TABLE STOCK;
DROP TABLE PERSON;
DROP TABLE CAR;
DROP TABLE SALE;
DROP TABLE ITEM_W;
DROP TABLE LINEITEM;
DROP TABLE DONOR;
DROP TABLE YEAR;
DROP TABLE DEPT;
DROP TABLE EMP;
DROP TABLE MONARCH;
DROP TABLE PRODUCT;
DROP TABLE ASSEMBLY;
DROP TABLE QDEL;
DROP TABLE QSALE;
DROP TABLE QDEPT;
DROP TABLE QEMP;
DROP TABLE QITEM;
DROP TABLE QSPL;

 

This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson
For questions and comments please contact the author
Date revised: 02-Dec-2022