For this problem please show me how to create the NEWITEM table, update a record

Question

For this problem please show me how to create the NEWITEM table, update a record, insert a record, and delete an old record. I also need at least 3 ways of comparing QITEM to NEWITEM to show they are no longer the same. Thank you

(Problem Below)

Given a QITEM table, from the QSALE DB, copy its content into NEWITEM table.

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 using only SQL (no triggers etc.) to accomplish the following task: compare the tables QITEM and NEWITEM to assess if they are now exactly the same (and they should not).

Explanation / Answer

(A) Syntax to create a table:

CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) )

NOTE: SQL is a case insensitive language.

To create newitem table:

Create table newitem(itemname varchar(30) constraint qitem_itemname_pk primary key constraint qitem_itemname_nn not null,

UPDATE A RECORD:

Syntax to update a table:

UPDATE table_name SET column_name = value [, column_name = value ...].

Therefore, to update newitem table:

Update table newitem set itemname = 'Pen'.

INSERT A NEW RECORD:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

To insert record in newitemtable:

Insert into newitem values(20,'ABC','20000,'EFG',4);

DELETE AN OLD RECORD:

Syntax to delete a record from table:

DELETE FROM table_name
WHERE condition;

To delete record from newitem table:

Delete from newitem where itemname = 'Pen'.

Three ways of comparing newitem table to qitem table:

(i)

This will give true if they are same and false if they are different.

(ii)

If we get data greater than double of any one of the table then both the tables do not have same data.

(iii) select * from newitem

MINUS

Select * from qitem

If no rows are returned, then both the tables have same data.

(b) Syntax to copy contents of one table to another:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

TO COPY CONTENTS FROM QITEM TO NEWITEM:

INSERT INTO NEWITEM
SELECT * FROM QITEM;

One record update with leaving duplicates:

update table newitem set itemname= 'EFG' where not exists (select * from newitem where itemname='EFG');

One record insert with leaving duplicates:

insert into newitem values(21,'DEF',30000,'ADC',4) where not exists (select * from newitem where

itemno=21;

itemname = 'DEF',

itemsal=30000,

itemtype='ADC';

items=4);

One record delete:

Delete from newitem where itemno=21;

To check if tables are now exactly same:

(i) select * from newitem

minus

select * from qitem

if no rows are returned, then tables are equal.

(ii) select

This will give true if they are same and false if they are different.

(iii)

If we get data greater than double of any one of the table then both the tables do not have same data.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

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