1-Given a QITEM table, from the QSALE DB, copy its content into NEWITEM table. 2
ID: 3756910 • Letter: 1
Question
1-Given a QITEM table, from the QSALE DB, copy its content into NEWITEM table.
2-Make three unrelated but elementary changes (one record update, one new record insert, and one old record delete) making sure you do not leave any duplicates.
Provide FIVE (queries) using only plain SQL (no triggers, no Transact or PL/SQL etc.) to accomplish the following task.
Here is the database:
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;
Explanation / Answer
If you have any doubts, please give me comment...
INSERT INTO NEWITEM SELECT * FROM QITEM;
INSERT INTO qitem VALUES('tiger claws', 'd', 'brown');
UPDATE qitem SET itemcolor='yellow' WHERE itemname = 'compass';
DELETE FROM qitem WHERE itemname = 'camel saddle';
SELECT * FROM qemp;
SELECT * FROM qitem;
SELECT * FROM qdel;
SELECT * FROM qdept;
SELECT * FROM qspl;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.