· 9 years ago · Dec 19, 2016, 07:04 AM
1use vikharev2
2go
3
4if object_id('deals') is not null
5 drop table deals
6go
7
8if object_id('lots') is not null
9 drop table lots
10go
11
12if object_id('auctions') is not null
13 drop table auctions
14go
15
16if object_id('stuff') is not null
17 drop table stuff
18go
19
20if object_id('people') is not null
21 drop table people
22go
23
24create table people
25(
26 id int primary key,
27 name varchar(50)
28)
29go
30
31create table stuff
32(
33 id int primary key,
34 name varchar(25)
35)
36go
37
38create table auctions
39(
40 id int primary key,
41 dt datetime,
42 place varchar(25),
43 specifics varchar(100)
44)
45go
46
47create table lots
48(
49 id int primary key,
50 thing int foreign key references stuff(id),
51 auction int foreign key references auctions(id),
52 seller int foreign key references people(id),
53 start_price float check (start_price >= 0),
54 description varchar(150)
55)
56go
57
58create table deals
59(
60 id int primary key,
61 lot int foreign key references lots(id),
62 buyer int foreign key references people(id),
63 final_price float check (final_price >= 0)
64)
65go
66
67if object_id('one_day') is not null
68 drop trigger one_day
69go
70
71create trigger one_day on lots
72 after insert, update
73as
74 if exists (
75 select *
76 from lots l1
77 join lots l2
78 on l1.id <> l2.id
79 join auctions a1
80 on l1.auction = a1.id
81 join auctions a2
82 on l2.auction = a2.id
83 where
84 l1.thing = l2.thing
85 and cast(a1.dt as date) = cast(a2.dt as date)
86 )
87 begin
88 raiserror ('NO non oo no no', 16, 1);
89 rollback transaction;
90 return;
91 end;
92go
93
94insert into people values
95 (1, 'First'),
96 (2, 'Second'),
97 (3, 'Third'),
98 (4, 'Fourth')
99go
100
101insert into stuff values
102 (1, 'Ball'),
103 (2, 'Sword'),
104 (3, 'Hat'),
105 (4, 'Cup'),
106 (5, 'Pen')
107go
108
109insert into auctions values
110 (1, '20161220 15:15:15', 'China', 'Some stuff'),
111 (2, '20161221 16:00:00', 'Russia', 'Other stuff')
112go
113
114insert into lots values
115 (1, 1, 1, 1, 100, 'This is a ball'),
116 (2, 2, 1, 2, 140, 'This is a sword'),
117 (3, 2, 2, 2, 156, 'This is a sword as well'),
118 (4, 3, 2, 4, 120, 'hat hat hat')
119go
120
121insert into deals values
122 (1, 1, 4, 150),
123 (2, 2, 4, 150),
124 (3, 3, 3, 150)
125go
126
127-- 1
128if object_id('revenue') is not null
129 drop function revenue
130go
131
132create function dbo.revenue(@auction int)
133 returns float
134as
135 begin
136 declare @rev float;
137 select @rev = sum(final_price - start_price)
138 from deals d
139 join lots l
140 on l.id = d.lot
141 and l.auction = @auction;
142
143 return @rev;
144 end
145go
146
147select convert(nvarchar(10), a.dt, 6) as date, a.id, a.place, a.specifics, dbo.revenue(a.id) as rev
148from auctions a
149order by rev desc
150go
151
152-- 2
153if object_id('get_auctions') is not null
154 drop function get_auctions
155go
156
157create function dbo.get_auctions(@thing int)
158 returns varchar(max)
159as
160 begin
161 declare @result varchar(max);
162 select @result = concat(coalesce(@result + ', ', ''), a.id)
163 from auctions a
164 join lots l
165 on l.auction = a.id
166 and l.thing = @thing;
167
168 return @result;
169 end
170go
171
172if object_id('sold_in_period') is not null
173 drop procedure sold_in_period
174go
175
176create procedure dbo.sold_in_period(@start date, @end date)
177as
178 begin
179 select name, a.id as auction, convert(nvarchar(10), a.dt, 6) as date, dbo.get_auctions(s.id) as auctions
180 from stuff s
181 join lots l
182 on l.thing = s.id
183 join deals d
184 on d.lot = l.id
185 join auctions a
186 on a.id = l.auction
187 where
188 cast(a.dt as date) >= @start
189 and cast(a.dt as date) <= @end
190 end
191go
192
193dbo.sold_in_period '2016/12/20', '2016/12/21';
194go
195
196-- 3
197if object_id('seller_revenue_in_period') is not null
198 drop procedure seller_revenue_in_period
199go
200
201create procedure dbo.seller_revenue_in_period(@start date, @end date)
202as
203 begin
204 select p.id, p.name, sum(final_price - start_price) as revenue
205 from people p
206 join lots l
207 on l.seller = p.id
208 join deals d
209 on d.lot = l.id
210 join auctions a
211 on a.id = l.auction
212 where cast(a.dt as date) >= @start
213 and cast(a.dt as date) <= @end
214 group by p.id, p.name
215 order by revenue desc
216 end
217go
218
219dbo.seller_revenue_in_period '2016/12/20', '2016/12/21';
220go
221
222-- 4
223if object_id('auctions_by_place') is not null
224 drop procedure auctions_by_place
225go
226
227create procedure dbo.auctions_by_place(@place varchar(25))
228as
229 begin
230 select a.id as auction, count(*) as selling
231 from auctions a
232 join lots l
233 on l.auction = a.id
234 where a.place like @place
235 group by a.id
236 end
237go
238
239dbo.auctions_by_place 'China'
240
241-- 5
242if object_id('selled_by_seller') is not null
243 drop function selled_by_seller
244go
245
246create function dbo.selled_by_seller(@seller int)
247 returns varchar(max)
248as
249 begin
250 declare @result varchar(max);
251 select @result = concat(coalesce(@result + ', ', ''), s.name)
252 from lots l
253 join people p
254 on p.id = l.seller
255 join stuff s
256 on s.id = l.thing
257 where p.id = @seller
258
259 return @result;
260 end
261go
262
263if object_id('sellers_by_period') is not null
264 drop procedure sellers_by_period
265go
266
267create procedure dbo.sellers_by_period(@start date, @end date)
268as
269 begin
270 select p.name, dbo.selled_by_seller(p.id) as selling
271 from people p
272 join lots l
273 on l.seller = p.id
274 join auctions a
275 on a.id = l.auction
276 join stuff s
277 on s.id = l.thing
278 where cast(a.dt as date) >= @start
279 and cast(a.dt as date) <= @end
280 group by p.id, p.name
281 end
282go
283
284dbo.sellers_by_period '2016/12/20', '2016/12/21';
285
286-- 6
287if object_id('buyers_by_period') is not null
288 drop procedure buyers_by_period
289go
290
291create procedure dbo.buyers_by_period(@start date, @end date)
292as
293 begin
294 select p.name, count(*) as bought
295 from people p
296 join deals d
297 on d.buyer = p.id
298 join lots l
299 on l.id = d.lot
300 join auctions a
301 on a.id = l.auction
302 where cast(a.dt as date) >= @start
303 and cast(a.dt as date) <= @end
304 group by p.id, p.name
305 end
306go
307
308dbo.buyers_by_period '2016/12/20', '2016/12/21';