-- John Aromando -- SQL Manipulating Data --

use ogcPubLev2

select * from Titles

--Create a backup of target tbl
select *
Into TitlesBkp --creates target table
from Titles

select * from TitlesBkp

--insert records into table
--maps following data to correct fields / not required if fields are in same sequence as in table 
INSERT titles (partnum, bktitle, devcost, slprice, pubdate)
VALUES
	('39906', 'VC++ Made Easy', null, 50, '2008/06/01'),
	('39907', 'History of the Internet', null, 25, '2008-07-01')

select * from Titles order by partnum

--null field missing and date is out of sequence, mapping is required here as data is out of sequence 
INSERT titles (pubdate, partnum, bktitle, slprice)
VALUES
	('2008-08-01', '39908', 'Flash Tips and Tricks', 35)

select * from Titles order by partnum

INSERT titles
VALUES
	(39908, 'Flash Tips and Tricks', 35, null, '2008-08-01'), --record 39908 has error -> slprice in devcost field
	('39909', 'eComerce: The Future of the Internet', 19000, 30, '2008-10-01'),
	('39910', 'Y2K Programming Made Easy', 22000, 40, '2008-10-01'),
	('39911', 'The Essence of Java Programming', 20000, 29, '2008-10-01');

select * from Titles order by partnum

delete Titles where partnum = '39908' and devcost = 35

select * from Titles order by partnum

--display results / note 3 records have null
--devcost / each needs to be updated to $2,100
update Titles
set devcost = 2100
output deleted.partnum as partnum,
		inserted.devcost as 'Revised Cost',
		deleted.devcost as 'Original Cost'
where partnum between 39906 and 39908

select * from Titles order by partnum

select * from Titles where partnum = '39911'

update Titles
set bktitle = 'Java Programming Made Easy',
	devcost = 25000,
	slprice = 32,
	pubdate = '2008-11-01'
where partnum = '39911'

select * from Titles order by partnum

select * from Titles order by bktitle

--raise price on books in the Learning Languages series
update Titles
set slprice = slprice * 1.2
output deleted.partnum as PartNum,
		deleted.bktitle as 'Titles Being Updated',
		deleted.slprice as 'Original Price',
		inserted.slprice as 'Revised Price'
from titles
where bktitle like 'learning%' and bktitle <> 'learning to crochet'

select * from Titles order by bktitle

--update Titlesbkp from Titles table
merge into titlesbkp as tgt
using titles as src on tgt.partnum = src.partnum
when matched then update
set tgt.slprice = src.slprice;

select * from TitlesBkp;

--delete records from tablebkp
truncate table TitlesBkp;

select * from TitlesBkp;

--delete backup table
Drop table TitlesBkp;

--Duplicates
--inserts statements to create duplicate records
INSERT titles
VALUES
	(39908, 'Flash Tips and Tricks', 35, null, '2008-08-01'),
	('39909', 'eComerce: The Future of the Internet', 19000, 30, '2008-10-01'),
	('39910', 'Y2K Programming Made Easy', 22000, 40, '2008-10-01'),
	('39911', 'The Essence of Java Programming', 20000, 29, '2008-10-01');

select * from Titles order by partnum

--Removes duplicates
WITH Numbered AS
	(SELECT ROW_NUMBER() OVER(PARTITION BY partnum ORDER BY partnum) AS DupeNum
		FROM Titles WHERE 1=1)
DELETE FROM Numbered WHERE DupeNum > 1 

select * from Titles order by partnum