· 6 years ago · Apr 09, 2019, 11:52 AM
1-- 1
2
3update Orders SET EmployeeID = 4 where EmployeeID = 1
4
5-- 2
6
7-- wer A
8update [Order Details] SET Quantity = CAST( round(0.8*Quantity,0) AS int)
9where exists (
10 select * from Orders O, Products P
11 where O.OrderDate > '1997/05/15'
12 and P.ProductName = 'Ikura'
13 and O.OrderID = [Order Details].OrderID
14 and P.ProductID = [Order Details].ProductID)
15
16-- wer B
17update OD SET OD.Quantity = CAST( round(0.8*Quantity,0) AS int)
18from [Order Details] OD
19Join Orders O ON O.OrderID = OD.OrderID
20Join Products P ON P.ProductID = OD.OrderID
21where O.OrderDate > '1997/05/15'
22 and P.ProductName = 'Ikura'
23
24-- 3
25declare @prodName varchar(40);
26declare @custId nchar(5);
27
28SET @prodName = 'Chocolade'
29SET @custId = 'ALFKI'
30
31-- with zmienne as ( select ProductID, UnitPrice, CustomerID from Products, Customers where ProductName = '' and CompanyName = '')
32
33insert into [Order Details]
34select top 1 O.OrderID,
35 P2.ProductID,
36 P2.UnitPrice,
37 1 Quantity,
38 0 Discount from Orders O, Products P2
39where P2.ProductName = @prodName and
40O.CustomerID = @custId and not exists ( select * from [Order Details] OD
41Join Products P ON P.ProductID = OD.ProductID
42where P.ProductName = @prodName
43and O.OrderID = OD.OrderID)
44order by O.OrderDate DESC
45
46--4
47declare @prodName varchar(40);
48declare @custId nchar(5);
49
50SET @prodName = 'Chocolade'
51SET @custId = 'ALFKI'
52insert into [Order Details]
53select O.OrderID,
54 P2.ProductID,
55 P2.UnitPrice,
56 1 Quantity,
57 0 Discount from Orders O, Products P2
58where P2.ProductName = @prodName and
59O.CustomerID = @custId and not exists ( select * from [Order Details] OD
60Join Products P ON P.ProductID = OD.ProductID
61where P.ProductName = @prodName
62and O.OrderID = OD.OrderID)
63order by O.OrderDate DESC
64
65-- 5
66
67delete from Customers
68where not exists (select * from Orders O where O.CustomerID = Customers.CustomerID)
69
70--wer b
71delete from C
72from Customers C
73LEFT JOIN Orders O on C.CustomerID = O.CustomerID
74where O.CustomerID is NULL -- wersja z wykorzystaniem dialektu MSSQL
75
76-- Scenario #1 --
77
78create table ArchiveOrders (
79OrderID int NOT NULL ,
80CustomerID nchar(5) NULL ,
81EmployeeID int NULL ,
82OrderDate datetime NULL ,
83RequiredDate datetime NULL ,
84ShippedDate datetime NULL ,
85ShipVia int NULL ,
86Freight money NULL ,
87ShipName nvarchar(40) NULL ,
88ShipAddress nvarchar(60) NULL ,
89ShipCity nvarchar(15) NULL ,
90ShipRegion nvarchar(15) NULL ,
91ShipPostalCode nvarchar(10) NULL ,
92ShipCountry nvarchar(15) NULL ,
93
94)
95--
96IF Object_id('ArchiveOrders') IS NOT NULL
97drop table ArchiveOrders;
98
99select * into ArchiveOrders from Orders where 1=0;
100--
101select * from ArchiveOrders
102
103Alter table ArchiveOrders
104 add ArchiveDate datetime,
105 constraint PKC_OrderID primary key (OrderID) ,
106 constraint FKC_Employees foreign key (EmployeeID) references Employees(EmployeeID),
107 constraint FKC_Customers foreign key (CustomerID) references Customers(CustomerID)
108
109IF Object_id('ArchiveOrderDetails') IS NOT NULL
110drop table ArchiveOrderDetails;
111
112select * into ArchiveOrderDetails from [Order Details] where 1=0;
113
114Alter table ArchiveOrderDetails
115 add ArchiveDate datetime,
116 constraint PKC_ArchOrderDetails primary key Clustered (OrderID asc , ProductID asc)
117 -- add FK ProductID
118
119--step 2
120set identity_insert dbo.ArchiveOrders ON;
121declare @year int;
122set @year = 1996;
123
124begin transaction
125begin try
126INSERT INTO ArchiveOrders(OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, ArchiveDate)
127select * , getdate() from Orders where Year(OrderDate) = @year;
128
129insert into ArchiveOrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount, ArchiveDate)
130select od.*, getdate() from [Order Details] od join Orders O on o.OrderID = od.OrderID and Year(OrderDate) = @year
131
132delete from [Order Details]
133from [Order Details] od join Orders O on o.OrderID = od.OrderID and Year(OrderDate) = @year
134
135delete from Orders from Orders O where Year(OrderDate) = @year
136
137commit;
138print 'transation has been committed'
139
140end try
141
142begin catch
143 print error_message();
144 rollback ;
145 print 'transaction has been rolled back'
146end catch