drop table qemp;
drop table qdel;
drop table qsale;
drop table qdept;
drop table qspl;
drop table qitem;

create table qitem
(itemname varchar2(30) constraint qitem_itemname_pk primary key
 constraint qitem_itemname_nn not null,
itemtype varchar2(8) constraint qitem_itemtype_nn not null,
itemcolor varchar2(10));

create table qspl
(splno number(3) constraint qspl_splno_pk primary key
 constraint qspl_splno_nn not null,
splname varchar2(30) constraint qspl_splname_nn not null);

create table qdept
(deptname varchar2(10) constraint qdept_deptname_pk primary key
 constraint qdept_deptname_nn not null,
deptfloor number(1) constraint qdept_deptfloor_nn not null,
deptphone number(2) constraint qdept_deptphone_nn not null,
empno number(2) constraint qdept_empno_nn not null);

create table qsale
(saleno number(4) constraint qsale_saleno_pk primary key
  constraint qsale_saleno_nn not null,
saleqty number(3) constraint qsale_saleqty_nn not null,
itemname varchar2(30) constraint qsale_itemname_fk references qitem(itemname)
 constraint qsale_itemname_nn not null,
deptname varchar2(10) constraint qsale_deptname_fk references qdept(deptname)
 constraint qsale_deptname_nn not null);

create table qdel
(delno number(3) constraint qdel_delno_pk primary key
  constraint qdel_delno_nn not null,
delqty number(3) constraint qdel_delqty_nn not null,
itemname varchar2(30)  constraint qdel_itemname_fk references qitem(itemname)
 constraint qdel_itemname_nn not null,
deptname varchar2(10)  constraint qdel_deptname_fk references qdept(deptname)
 constraint qdel_deptname_nn not null,
splno number(3)  constraint qdel_splno_fk references qspl(splno)
 constraint qdel_splno_nn not null);

create table qemp
(empno number(2) constraint qemp_empno_pk primary key
  constraint qemp_empno_nn not null,
empfname varchar2(10) constraint qemp_empfname_nn not null,
empsalary number(7) constraint qemp_empsalary_nn not null,
deptname varchar2(10) constraint qemp_deptname_fk references qdept(deptname)
 constraint qemp_deptname_nn not null,
bossno number(2) constraint qemp_bossno_fk references qemp(empno));

-- insert data to qitem

INSERT INTO qitem VALUES
('boots-snakeproof', 'c', 'green');

INSERT INTO qitem VALUES
('camel saddle', 'r', 'brown');

INSERT INTO qitem VALUES
('compass', 'n', NULL);

INSERT INTO qitem VALUES
('elephant polo stick', 'r', 'bamboo');

INSERT INTO qitem VALUES
('exploring in 10 easy lessons', 'b', NULL);

INSERT INTO qitem VALUES
('geo positioning system', 'n', NULL);

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', NULL);

INSERT INTO qitem VALUES
('map case', 'e', 'brown');

INSERT INTO qitem VALUES
('map measure', 'n', NULL);

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', NULL);

INSERT INTO qitem VALUES
('sextant', 'n', NULL);

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');

-- insert data into qspl
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');

-- insert data into qdept

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);

-- insert data into qsale
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');

-- insert data into qdel
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);

-- insert data into qemp
INSERT INTO qemp VALUES
(1, 'Alice', 75000, 'management', NULL);

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);

commit;