Untitled
unknown
sql
2 years ago
2.7 kB
5
Indexable
/*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
Editor is loading...