Untitled

/*zad 1*/
/*set identity_insert [dbo].[BuildVersion] on

insert into [dbo].[BuildVersion]
	([SystemInformationID], [Database Version], [VersionDate])
values (33, 'db ver 4', '20230412')

set identity_insert [dbo].[BuildVersion] off
select * from [dbo].[BuildVersion]
*/


/*zad 2*/
insert into [SalesLT].ProductCategory ([Name])
values ('nowa kategoria 1'), ('nowa kategoria 2'), ('nowa kategoria 3')

/*zad 3*/
select [ProductID], name, ListPrice
 into RedProducts
from [SalesLT].[Product]
where color = 'White'


/*zad 4*/
set identity_insert RedProducts on
insert into RedProducts ([ProductID], name, ListPrice)
select [ProductID], name, ListPrice
from [SalesLT].[Product]
where color = 'White'
set identity_insert RedProducts off

/*zad 5*/
delete from [dbo].RedProducts
where ProductId = 707

/*zad 6*/
update dbo.RedProducts
set name = name + '*'

update dbo.RedProducts
set name = CONCAT (name, '*')

update dbo.RedProducts
set name += '*'

/*zad 7 Promoicja na jeden produkt*/
update dbo.RedProducts
set name += '*',
 ListPrice = 0.99
 where ProductID = 706

 /*zad 8*/
update dbo.RedProducts
set ListPrice = 0.99
from dbo.RedProducts as t
join SalesLT.Product as src
 on t.ProductID = src.ProductID
 where size = '58'

 /*zad 9*/
 set identity_insert [dbo].[RedProducts] on
 merge [dbo].[RedProducts] as t
 using [SalesLT].[Product] as s
	on t.productId = s.productID
when not matched then 
	insert ([ProductID], [Name], [ListPrice])
	values (s.[ProductID], upper(s.name), s.[ListPrice])
when matched then 
	update set name = s.name, [ListPrice] = s.[ListPrice]/2
output $action,deleted.*, inserted.*;
 set identity_insert [dbo].[RedProducts] off


/*Zadania*/
/*zad 1*/
select [CustomerID], OrderDate, TotalDue
 into SalesUSA
from [SalesLT].[SalesOrderHeader] as soh
join [SalesLT].[Address] as ad
on ad.AddressID = soh.ShipToAddressID
where CountryRegion = 'United States'

/*zad 2*/
delete [dbo].SalesUSA from [dbo].SalesUSA as sa
join [SalesLT].[Customer] as cus
on cus.CustomerID = sa.CustomerID 
where FirstName like '%e' or FirstName like '%a'

/*zad3*/
insert into [SalesLT]. ([Name])
join 
values ('Sport-100 Helmet, Red')









/*zad4*/
update top (5) dbo.SalesUSA
set TotalDue = 0.01

/*zad5*/
set identity_insert [dbo].SalesUSA on
 merge [dbo].SalesUSA as su
 using [SalesLT].[SalesOrderHeader] as soh
	on su.customerID = soh.customerID
when not matched then 
	insert ([CustomerID], OrderDate, TotalDue)
	values (soh.[CustomerID], soh.OrderDate, soh.TotalDue)
when matched then 
	update set CustomerID = soh.CustomerID, OrderDate = soh.OrderDate, TotalDue = soh.TotalDue
output $action,deleted.*, inserted.*;
 set identity_insert [dbo].[RedProducts] off