Each completely correct, and acceptable (with a different execution/explain plan) query 10 points

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.

3)Provide as many different ways (queries) using only plain SQL (no triggers, no Transact or PL/SQL etc.) to accomplish the following task: compare the tables QITEM and NEWITEM to assess if they are now exactly the same or not. Assuming potentially large tables select * from each will not be acceptable!

---------------------

use [master]

go

IF DB_ID ('QSALE') IS NOT NULL

DROP DATABASE [QSALE]

GO

CREATE DATABASE [QSALE]

-- test following sections separately

use qsale

go

-- test (and adapt if necessary) for SQL Server

drop table qemp;

drop table qdel;

drop table qsale;

drop table qdept;

drop table qspl;

drop table qitem;

create table qitem

(itemname varchar(30) constraint qitem_itemname_pk primary key

constraint qitem_itemname_nn not null,

itemtype varchar(8) constraint qitem_itemtype_nn not null,

itemcolor varchar(10));

create table qspl

(splno int constraint qspl_splno_pk primary key

constraint qspl_splno_nn not null,

splname varchar(30) constraint qspl_splname_nn not null);

create table qdept

(deptname varchar(10) constraint qdept_deptname_pk primary key

constraint qdept_deptname_nn not null,

deptfloor int constraint qdept_deptfloor_nn not null,

deptphone int constraint qdept_deptphone_nn not null,

empno int constraint qdept_empno_nn not null);

create table qsale

(saleno int constraint qsale_saleno_pk primary key

constraint qsale_saleno_nn not null,

saleqty int constraint qsale_saleqty_nn not null,

itemname varchar(30) constraint qsale_itemname_fk references qitem(itemname)

constraint qsale_itemname_nn not null,

deptname varchar(10) constraint qsale_deptname_fk references qdept(deptname)

constraint qsale_deptname_nn not null);

create table qdel

(delno int constraint qdel_delno_pk primary key

constraint qdel_delno_nn not null,

delqty int constraint qdel_delqty_nn not null,

itemname varchar(30) constraint qdel_itemname_fk references qitem(itemname)

constraint qdel_itemname_nn not null,

deptname varchar(10) constraint qdel_deptname_fk references qdept(deptname)

constraint qdel_deptname_nn not null,

splno int constraint qdel_splno_fk references qspl(splno)

constraint qdel_splno_nn not null);

create table qemp

(empno int constraint qemp_empno_pk primary key

constraint qemp_empno_nn not null,

empfname varchar(10) constraint qemp_empfname_nn not null,

empsalary money constraint qemp_empsalary_nn not null,

deptname varchar(10) constraint qemp_deptname_fk references qdept(deptname)

constraint qemp_deptname_nn not null,

bossno int constraint qemp_bossno_fk references qemp(empno));

-- test separately

USE QSALE

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