Each completely correct, and fairly acceptable (with a different execution/expla

Question

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

a) Given a QITEM table, from the QSALE DB, copy its content into NEWITEM table. b) 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 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. Notes: 1. Assuming potentially large tables select * from each will not be acceptable! 2. You can use ether SQL Server.

Explanation / Answer

Answer :

a)

create table NEWITEM as select * from QITEM

b)

One record update:

Let's update itemtype to 'n' for the item name 'boots-snakeproof'

update qitem set itemtype='n' where itemname='boots-snakeproof'

One new record insert:

insert into QITEM(ITEMNAME,ITEMTYPE,ITEMCOLOR) VALUES('IRONMAN Movie','M','RED');

One old record delete:

Let's delete the record having itemname as 'camel saddle'

delete from QITEM where ITEMNAME='camel saddle'

Removing duplicates from table:

delete from QITEM where ROWID not in (select max(ROWID) from QITEM group by ITEMNAME,ITEMTYPE,ITEMCOLOR)

c)

compare the tables QITEM and NEWITEM

Method 1:

SQL:

(select * from qitem
minus
select * from NEWITEM)
union all
(
select * from NEWITEM
minus
select * from qitem
)

This will give all mismatching records in both table.

Another Method:

select *
from QITEM
where (ITEMNAME,ITEMTYPE,ITEMCOLOR) not in
(select ITEMNAME,ITEMTYPE,ITEMCOLOR from NEWITEM)
union all
select ITEMNAME,ITEMTYPE,ITEMCOLOR from NEWITEM
where (ITEMNAME,ITEMTYPE,ITEMCOLOR) not in
(select ITEMNAME,ITEMTYPE,ITEMCOLOR from QITEM);

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.