· 8 years ago · Mar 03, 2018, 05:22 PM
1USE AdventureWorks2014
2
3-- Zadanie 1
4IF (
5 NOT EXISTS (
6 SELECT *
7 FROM sys.schemas
8 WHERE name = 'Lab2'
9 )
10 )
11BEGIN
12 EXEC ('CREATE SCHEMA [Lab2] AUTHORIZATION [dbo]')
13END
14
15IF NOT EXISTS (
16 SELECT *
17 FROM sysobjects
18 WHERE name = 'Sprzedaz'
19 AND xtype = 'U'
20 )
21 CREATE TABLE Lab2.Sprzedaz (
22 pracID INT CONSTRAINT FK_Sprzedaz_SalesPerson REFERENCES Sales.SalesPerson(BusinessEntityID)
23 ,prodID INT CONSTRAINT FK_Sprzedaz_Product REFERENCES Production.Product(ProductID)
24 ,nazwa Name NOT NULL
25 ,rok INT NOT NULL
26 ,ilosc INT NOT NULL
27 ,CONSTRAINT PK_Sprzedaz PRIMARY KEY (
28 pracID
29 ,prodID
30 ,rok
31 )
32 )
33GO
34
35INSERT INTO Lab2.Sprzedaz (
36 pracID
37 ,prodID
38 ,nazwa
39 ,rok
40 ,ilosc
41 )
42SELECT soh.SalesPersonID
43 ,sod.ProductID
44 ,prod.Name
45 ,YEAR(soh.OrderDate)
46 ,SUM(sod.OrderQty)
47FROM Sales.SalesOrderHeader soh
48JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
49JOIN Production.Product prod ON prod.ProductID = sod.ProductID
50WHERE soh.SalesPersonID IS NOT NULL
51GROUP BY soh.SalesPersonID
52 ,sod.ProductID
53 ,prod.Name
54 ,YEAR(soh.OrderDate)
55ORDER BY SalesPersonID
56
57SELECT *
58FROM Lab2.Sprzedaz
59
60SELECT *
61FROM Lab2.Sprzedaz
62PIVOT(SUM(ilosc) FOR rok IN (
63 [2011]
64 ,[2012]
65 ,[2013]
66 ,[2014]
67 )) piv
68
69SELECT TOP 5 pracId
70 ,SUM(ilosc)
71FROM Lab2.Sprzedaz
72GROUP BY pracId
73ORDER BY 2 DESC
74
75SELECT *
76FROM Lab2.Sprzedaz
77PIVOT(SUM(ilosc) FOR pracId IN (
78 [276]
79 ,[277]
80 ,[289]
81 ,[275]
82 ,[279]
83 )) piv
84
85-- Zadanie 2
86SELECT YEAR(soh.OrderDate) Rok
87 ,MONTH(soh.OrderDate) MiesiÄ…c
88 ,COUNT(DISTINCT soh.CustomerID) Klientów
89FROM Sales.SalesOrderHeader soh
90GROUP BY YEAR(soh.OrderDate)
91 ,MONTH(soh.OrderDate)
92ORDER BY 1
93 ,2
94
95SELECT years.Rok
96 ,ISNULL([1], 0) [1]
97 ,ISNULL([2], 0) [2]
98 ,ISNULL([3], 0) [3]
99 ,ISNULL([4], 0) [4]
100 ,ISNULL([5], 0) [5]
101 ,ISNULL([6], 0) [6]
102 ,ISNULL([7], 0) [7]
103 ,ISNULL([8], 0) [8]
104 ,ISNULL([9], 0) [9]
105 ,ISNULL([10], 0) [10]
106 ,ISNULL([11], 0) [11]
107 ,ISNULL([12], 0) [12]
108FROM (
109 SELECT DISTINCT YEAR(soh.OrderDate) Rok
110 FROM Sales.SalesOrderHeader soh
111 ) years
112LEFT JOIN (
113 SELECT YEAR(soh.OrderDate) Rok
114 ,COUNT(DISTINCT soh.CustomerID) [1]
115 FROM Sales.SalesOrderHeader soh
116 WHERE MONTH(soh.OrderDate) = 1
117 GROUP BY YEAR(soh.OrderDate)
118 ) m1 ON years.Rok = m1.Rok
119LEFT JOIN (
120 SELECT YEAR(soh.OrderDate) Rok
121 ,COUNT(DISTINCT soh.CustomerID) [2]
122 FROM Sales.SalesOrderHeader soh
123 WHERE MONTH(soh.OrderDate) = 2
124 GROUP BY YEAR(soh.OrderDate)
125 ) m2 ON years.Rok = m2.rok
126LEFT JOIN (
127 SELECT YEAR(soh.OrderDate) Rok
128 ,COUNT(DISTINCT soh.CustomerID) [3]
129 FROM Sales.SalesOrderHeader soh
130 WHERE MONTH(soh.OrderDate) = 3
131 GROUP BY YEAR(soh.OrderDate)
132 ) m3 ON years.Rok = m3.rok
133LEFT JOIN (
134 SELECT YEAR(soh.OrderDate) Rok
135 ,COUNT(DISTINCT soh.CustomerID) [4]
136 FROM Sales.SalesOrderHeader soh
137 WHERE MONTH(soh.OrderDate) = 4
138 GROUP BY YEAR(soh.OrderDate)
139 ) m4 ON years.Rok = m4.rok
140LEFT JOIN (
141 SELECT YEAR(soh.OrderDate) Rok
142 ,COUNT(DISTINCT soh.CustomerID) [5]
143 FROM Sales.SalesOrderHeader soh
144 WHERE MONTH(soh.OrderDate) = 5
145 GROUP BY YEAR(soh.OrderDate)
146 ) m5 ON years.Rok = m5.rok
147LEFT JOIN (
148 SELECT YEAR(soh.OrderDate) Rok
149 ,COUNT(DISTINCT soh.CustomerID) [6]
150 FROM Sales.SalesOrderHeader soh
151 WHERE MONTH(soh.OrderDate) = 6
152 GROUP BY YEAR(soh.OrderDate)
153 ) m6 ON years.Rok = m6.rok
154LEFT JOIN (
155 SELECT YEAR(soh.OrderDate) Rok
156 ,COUNT(DISTINCT soh.CustomerID) [7]
157 FROM Sales.SalesOrderHeader soh
158 WHERE MONTH(soh.OrderDate) = 7
159 GROUP BY YEAR(soh.OrderDate)
160 ) m7 ON years.Rok = m7.rok
161LEFT JOIN (
162 SELECT YEAR(soh.OrderDate) Rok
163 ,COUNT(DISTINCT soh.CustomerID) [8]
164 FROM Sales.SalesOrderHeader soh
165 WHERE MONTH(soh.OrderDate) = 8
166 GROUP BY YEAR(soh.OrderDate)
167 ) m8 ON years.Rok = m8.rok
168LEFT JOIN (
169 SELECT YEAR(soh.OrderDate) Rok
170 ,COUNT(DISTINCT soh.CustomerID) [9]
171 FROM Sales.SalesOrderHeader soh
172 WHERE MONTH(soh.OrderDate) = 9
173 GROUP BY YEAR(soh.OrderDate)
174 ) m9 ON years.Rok = m9.rok
175LEFT JOIN (
176 SELECT YEAR(soh.OrderDate) Rok
177 ,COUNT(DISTINCT soh.CustomerID) [10]
178 FROM Sales.SalesOrderHeader soh
179 WHERE MONTH(soh.OrderDate) = 10
180 GROUP BY YEAR(soh.OrderDate)
181 ) m10 ON years.Rok = m10.rok
182LEFT JOIN (
183 SELECT YEAR(soh.OrderDate) Rok
184 ,COUNT(DISTINCT soh.CustomerID) [11]
185 FROM Sales.SalesOrderHeader soh
186 WHERE MONTH(soh.OrderDate) = 11
187 GROUP BY YEAR(soh.OrderDate)
188 ) m11 ON years.Rok = m11.rok
189LEFT JOIN (
190 SELECT YEAR(soh.OrderDate) Rok
191 ,COUNT(DISTINCT soh.CustomerID) [12]
192 FROM Sales.SalesOrderHeader soh
193 WHERE MONTH(soh.OrderDate) = 12
194 GROUP BY YEAR(soh.OrderDate)
195 ) m12 ON years.Rok = m12.rok
196ORDER BY 1
197
198SELECT *
199FROM (
200 SELECT DISTINCT YEAR(soh.OrderDate) Rok
201 ,MONTH(soh.OrderDate) [Month]
202 ,soh.CustomerID CustomerID
203 FROM Sales.SalesOrderHeader soh
204 ) src
205PIVOT(COUNT(CustomerID) FOR [Month] IN (
206 [1]
207 ,[2]
208 ,[3]
209 ,[4]
210 ,[5]
211 ,[6]
212 ,[7]
213 ,[8]
214 ,[9]
215 ,[10]
216 ,[11]
217 ,[12]
218 )) piv
219ORDER BY 1
220
221-- Zadanie 3
222SELECT [name] [Imie i nazwisko]
223 ,[2012]
224 ,[2014]
225FROM (
226 SELECT p.BusinessEntityID [pID]
227 ,CONCAT (
228 p.FirstName
229 ,' '
230 ,p.MiddleName
231 ,' '
232 ,p.LastName
233 ) [name]
234 ,YEAR(soh.OrderDate) [year]
235 ,soh.SalesOrderID [sohID]
236 FROM Sales.SalesOrderHeader soh
237 INNER JOIN Sales.Customer c ON c.CustomerID = soh.CustomerID
238 INNER JOIN Person.Person p ON p.BusinessEntityID = c.PersonID
239 WHERE YEAR(soh.OrderDate) NOT IN (
240 2011
241 ,2013
242 )
243 ) src
244PIVOT(COUNT(sohID) FOR [year] IN (
245 [2012]
246 ,[2014]
247 )) piv
248
249-- Zadanie 4
250SELECT YEAR(soh.OrderDate)
251 ,SUM(sod.LineTotal)
252 ,COUNT(sod.SalesOrderDetailID)
253FROM Sales.SalesOrderHeader soh
254INNER JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID
255GROUP BY YEAR(soh.OrderDate)
256ORDER BY 1
257
258SELECT MONTH(soh.OrderDate)
259 ,SUM(sod.LineTotal)
260 ,COUNT(sod.SalesOrderDetailID)
261FROM Sales.SalesOrderHeader soh
262INNER JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID
263GROUP BY MONTH(soh.OrderDate)
264ORDER BY 1
265
266SELECT DAY(soh.OrderDate)
267 ,SUM(sod.LineTotal)
268 ,COUNT(sod.SalesOrderDetailID)
269FROM Sales.SalesOrderHeader soh
270INNER JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID
271GROUP BY DAY(soh.OrderDate)
272ORDER BY 1