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