· 6 years ago · Nov 26, 2019, 01:10 PM
1USE master;
2GO
3IF DB_ID (N'SteamDB') IS NOT NULL
4DROP DATABASE SteamDB;
5GO
6CREATE DATABASE SteamDB
7ON ( NAME = Sales_dat, FILENAME =
8'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SteamDB.mdf',
9SIZE = 10, MAXSIZE = UNLIMITED, FILEGROWTH = 5% )
10LOG ON ( NAME = Sales_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SteamDB.ldf',
11SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB );
12GO
13CREATE SCHEMA SteamDB;
14GO
15CREATE TABLE SteamDB.Game
16(
17 GameID INT PRIMARY KEY IDENTITY (1,1),
18 GameName VARCHAR (50) NOT NULL,
19 Price INT NULL,
20 Description NVARCHAR (1000) NULL,
21 Genre Char (15) NOT NULL
22 CHECK (Genre IN ('Shooter', 'Strategy', 'MOBA', 'Racing', 'Adventure', 'MMORPG', 'Fighting', 'Horror', 'Survival'))
23);
24GO
25
26CREATE UNIQUE NONCLUSTERED INDEX IX_Games ON SteamDB.Game
27 (
28 GameName
29 ) WITH(IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
30GO
31INSERT INTO SteamDB.Game(GameName, Price, Description, Genre)
32VALUES ('DOTA2', 0, N'Самая популярная игра в Steam
33Ежедневно миллионы игроков по всему миру вступают в битву от лица одного из более сотни героев Dota 2. Неважно, будет это десятый или тысячный час в игре — в ней всегда есть место чему-то новому. Регулярные обновления не дают стоять на месте игровому процессу, возможностям и героям, отчего Dota 2 поистине живёт собственной жизнью.', 'MOBA'),
34('RainbowSix: Siege', 999, N'Освойте искусство разрушения и использования устройств в Rainbow Six Осада. Игроков ждут напряженные бои на коротких дистанциях и открытом пространстве. Новая часть серии игр Tom Clancy’s Rainbow Six выводит жаркие перестрелки, сноровку и опыт, тактическое планирование и соревновательный аспект на новый уровень.', 'Shooter'),
35('Age of Empires II: Definitive Edition', 435, N'Отпразднуйте 20-летие одной из самых популярных стратегических игр! Встречайте Age of Empires II: Definitive Edition — с потрясающим разрешением Ultra HD 4K, новым саундтреком после цифрового ремастеринга, а также расширением «Последние ханы» с 3 кампаниями и 4 новыми цивилизациями.
36', 'Strategy'),
37('STAR WARS Jedi: Fallen Order', 3499, N'В «Звёздные Войны Джедаи: Павший Орден», боевике с видом от третьего лица от Respawn Entertainment, вас ждут приключения галактического масштаба. Действие этой сюжетной одиночной игры разворачивается после фильма «Эпизод III — Месть ситхов». Вам предстоит очутиться в роли джедая-падавана, которому едва удалось избежать уничтожения, санкционированного Приказом 66. В стремлении восстановить Орден джедаев вам придётся собрать воедино осколки прошлого, чтобы завершить обучение, обрести новые способности Силы и овладеть мастерством боя на легендарных световых мечах, а главное — оставаться при этом на шаг впереди Империи и её беспощадных инквизиторов.',
38'Adventure'),
39('Counter-Strike: Global Offensive', 0, N'Counter-Strike: Global Offensive (CS: GO) возрождает ураганную командную игру, впервые представленную еще 19 лет назад.
40
41CS: GO включает в себя новые карты, персонажей, оружие, игровые режимы, а также улучшенную версию классической составляющей CS (de_dust и т.п.).',
42'Shooter')
43
44GO
45
46CREATE TABLE SteamDB.Users
47(
48 UserID INT PRIMARY KEY IDENTITY (1,1),
49 UserName VARCHAR (25) NOT NULL,
50 FirstName VARCHAR (25) NULL,
51 SecondName VARCHAR (25) NULL,
52 PhoneNumber VARCHAR (15) NOT NULL
53);
54
55GO
56
57CREATE UNIQUE NONCLUSTERED INDEX IX_Users ON SteamDB.Users
58 (
59 UserName
60 ) WITH(IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
61GO
62INSERT INTO SteamDB.Users(UserName, FirstName, SecondName, PhoneNumber)
63VALUES ('SpeedyGonzaless', 'Ivan', 'Filonenko', '89266751764'),
64('EA-2', 'Artem', 'Egorichev', '89163451221'),
65('Dubinich', 'Dmitriy', 'Dubina', '89105553399'),
66('LoseYourselft', 'Alan', 'Yarakhmedov', '89269097525')
67
68GO
69INSERT INTO SteamDB.Game_User_INT(UserID, GameID)
70VALUES (1, 1),
71(1, 2),
72(1, 3),
73(1, 5),
74(2, 5),
75(3, 1),
76(3, 3),
77(3, 5)
78
79GO
80CREATE TABLE SteamDB.Game_Overview
81(
82 UserID INT NOT NULL,
83 CONSTRAINT FK_UserID_GameOVerview
84 FOREIGN KEY(UserID)
85 REFERENCES SteamDB.Users(UserID),
86 GameID INT NOT NULL,
87 CONSTRAINT FK_GameID_GameOverview
88 FOREIGN KEY(GameID)
89 REFERENCES SteamDB.Game(GameID),
90 OverviewText NVARCHAR(1000) NULL,
91 Rate INT NOT NULL
92 CHECK (Rate BETWEEN 0 AND 5)
93);
94GO
95INSERT INTO SteamDB.Game_Overview(UserID, GameID, OverviewText, Rate)
96VALUES (1, 2, 'Noice shooter', 5)
97INSERT INTO SteamDB.Game_Overview(UserID, GameID, Rate)
98VALUES (1, 2, 4)
99
100GO
101CREATE TABLE SteamDB.Game_Item
102(
103 ItemID INT PRIMARY KEY,
104 ItemName VARCHAR (50) NOT NULL,
105 isTradable INT NOT NULL
106 CHECK (isTradable = 0 OR isTradable = 1),
107 UserID INT NOT NULL,
108 CONSTRAINT FK_UserID_GameItem
109 FOREIGN KEY(UserID)
110 REFERENCES SteamDB.Users(UserID),
111 GameID INT NOT NULL,
112 CONSTRAINT FK_GameID_GameItem
113 FOREIGN KEY(GameID)
114 REFERENCES SteamDB.Game(GameID),
115);
116GO
117CREATE SEQUENCE SteamDB.ItemSequence
118 START WITH 1
119 INCREMENT BY 1;
120GO
121CREATE TABLE SteamDb.ItemTradable
122(
123 ItemID INT NOT NULL,
124 CONSTRAINT FK_GameItem_Tradable
125 FOREIGN KEY(ItemID)
126 REFERENCES SteamDB.Game_Item(ItemID),
127 Price INT NOT NULL
128);
129GO
130CREATE TABLE SteamDb.ItemUntradable
131(
132 ItemID INT NOT NULL,
133 CONSTRAINT FK_GameItem_Untradable
134 FOREIGN KEY(ItemID)
135 REFERENCES SteamDB.Game_Item(ItemID),
136 Unblockdate DATE NOT NULL
137);
138Use SteamDB
139GO
140CREATE VIEW SteamDB.TradableView
141AS
142SELECT i.ItemID,
143i.ItemName,
144u.UserName,
145g.GameName,
146t.Price
147FROM SteamDB.Game_Item i
148RIGHT JOIN SteamDB.ItemTradable as t ON t.ItemID = i.ItemID,
149SteamDB.Game g, SteamDB.Users u WHERE i.UserID = u.UserID AND i.GameID = g.GameID;
150GO
151
152GO
153IF OBJECT_ID('trig_INSERT_TradableView', 'TR') IS NOT NULL
154 DROP TRIGGER trig_INSERT_TradableView
155GO
156CREATE TRIGGER trig_INSERT_TradableView
157 ON SteamDB.TradableView
158 INSTEAD OF INSERT
159 AS
160 BEGIN
161 SET NOCOUNT ON
162 INSERT INTO SteamDB.Game_Item(
163 ItemID,
164 ItemName,
165 isTradable,
166 UserID,
167 GameID
168 )
169 SELECT
170 i.ItemID,
171 i.ItemName,
172 1,
173 (SELECT u.UserID FROM SteamDB.Users u WHERE u.UserName = i.UserName),
174 (SELECT g.GameID FROM SteamDB.Game g WHERE g.GameName = i.GameName)
175 FROM inserted i
176 INSERT INTO ItemTradable(
177 ItemID,
178 Price
179 )
180 SELECT i.ItemID,
181 i.Price
182 FROM inserted i
183 END
184GO
185
186GO
187IF OBJECT_ID('trig_DELETE_TradableView', 'TR') IS NOT NULL
188 DROP TRIGGER trig_DELETE_TradableView
189GO
190CREATE TRIGGER trig_DELETE_TradableView
191 ON SteamDB.TradableView
192 INSTEAD OF DELETE
193 AS
194 BEGIN
195 MERGE SteamDB.ItemTradable AS Item
196 USING (SELECT ItemID FROM deleted) AS ItemView (ID)
197 ON Item.ItemID = ItemView.ID
198 WHEN MATCHED THEN DELETE;
199
200 MERGE SteamDB.Game_Item AS Item
201 USING (SELECT ItemID FROM deleted) AS ItemView (ID)
202 ON Item.ItemID = ItemView.ID
203 WHEN MATCHED THEN DELETE;
204 END
205GO
206
207GO
208IF OBJECT_ID('trig_UPDATE_TradableView', 'TR') IS NOT NULL
209 DROP TRIGGER trig_UPDATE_TradableView
210GO
211CREATE TRIGGER trig_UPDATE_TradableView
212 ON SteamDB.TradableView
213 INSTEAD OF UPDATE
214 AS
215 BEGIN
216 IF (UPDATE(Price))
217 BEGIN
218 UPDATE SteamDB.ItemTradable
219 SET Price = inserted.Price
220 FROM inserted
221 JOIN SteamDB.ItemTradable
222 ON inserted.ItemID = SteamDB.ItemTradable.ItemID
223 END
224 END
225GO
226
227CREATE VIEW SteamDB.UntradableView
228AS
229SELECT i.ItemID,
230i.ItemName,
231u.UserName,
232g.GameName,
233un.UnblockDate
234FROM SteamDB.ItemUntradable un
235LEFT JOIN SteamDB.Game_Item as i ON un.ItemID = i.ItemID,
236SteamDB.Game g, SteamDB.Users u WHERE i.UserID = u.UserID AND i.GameID = g.GameID;
237GO
238
239
240IF OBJECT_ID('trig_INSERT_UntradableView', 'TR') IS NOT NULL
241 DROP TRIGGER trig_INSERT_UntradableView
242GO
243CREATE TRIGGER trig_INSERT_UntradableView
244 ON SteamDB.UntradableView
245 INSTEAD OF INSERT
246 AS
247 BEGIN
248 SET NOCOUNT ON
249 INSERT INTO SteamDB.Game_Item(
250 ItemID,
251 ItemName,
252 isTradable,
253 UserID,
254 GameID
255 )
256 SELECT
257 i.ItemID,
258 i.ItemName,
259 0,
260 (SELECT u.UserID FROM SteamDB.Users u WHERE u.UserName = i.UserName),
261 (SELECT g.GameID FROM SteamDB.Game g WHERE g.GameName = i.GameName)
262 FROM inserted i
263 INSERT INTO ItemUntradable(
264 ItemID,
265 UnblockDate
266 )
267 SELECT i.ItemID,
268 i.UnblockDate
269 FROM inserted i
270 END
271GO
272
273GO
274IF OBJECT_ID('trig_DELETE_UntradableView', 'TR') IS NOT NULL
275 DROP TRIGGER trig_DELETE_UntradableView
276GO
277CREATE TRIGGER trig_DELETE_UntradableView
278 ON SteamDB.UntradableView
279 INSTEAD OF DELETE
280 AS
281 BEGIN
282 MERGE SteamDB.ItemUntradable AS Item
283 USING (SELECT ItemID FROM deleted) AS ItemView (ID)
284 ON Item.ItemID = ItemView.ID
285 WHEN MATCHED THEN DELETE;
286
287 MERGE SteamDB.Game_Item AS Item
288 USING (SELECT ItemName FROM deleted) AS ItemView (ID)
289 ON Item.ItemID = ItemView.ID
290 WHEN MATCHED THEN DELETE;
291 END
292GO
293
294GO
295IF OBJECT_ID('trig_UPDATE_UntradableView', 'TR') IS NOT NULL
296 DROP TRIGGER trig_UPDATE_UntradableView
297GO
298CREATE TRIGGER trig_UPDATE_UntradableView
299 ON SteamDB.UntradableView
300 INSTEAD OF UPDATE
301 AS
302 BEGIN
303 IF (UPDATE(UnblockDate))
304 BEGIN
305 UPDATE SteamDB.ItemUntradable
306 SET UnblockDate = inserted.UnblockDate
307 FROM inserted
308 JOIN SteamDB.ItemUntradable
309 ON inserted.ItemID = SteamDB.ItemUntradable.ItemID
310 END
311 END
312GO
313
314GO
315CREATE VIEW SteamDB.Game_User_View
316AS
317SELECT
318g.GameName AS NameOfGame,
319u.UserName
320FROM SteamDB.Game_User_INT gu
321INNER JOIN SteamDB.Game as g ON gu.GameID = g.GameID,
322SteamDB.Users u WHERE gu.UserID = u.UserID;
323GO
324
325GO
326SELECT DISTINCT NameOfGame FROM SteamDB.Game_User_View
327
328GO
329SELECT * FROM SteamDB.TradableView t
330FULL OUTER JOIN SteamDB.UntradableView u ON t.ItemName = u.ItemName
331
332GO
333SELECT * FROM SteamDB.Game g
334WHERE GameName LIKE '%-%'
335
336GO
337SELECT * FROM SteamDB.Game_OVerview
338WHERE OverviewText is NULL
339
340GO
341SELECT * FROM SteamDB.Game_Item i
342WHERE EXISTS
343(SELECT * FROM SteamDB.Game g WHERE GameName = 'DOTA2' AND i.GameID = g.GameID)
344
345GO
346SELECT Genre, SUM(Price) AS SumPrice FROM SteamDB.Game
347GROUP BY Genre
348HAVING Genre != 'MOBA'
349ORDER BY SumPrice ASC
350
351GO
352SELECT Genre, AVG(Price) AS SumPrice FROM SteamDB.Game
353GROUP BY Genre
354HAVING Genre != 'MOBA'
355ORDER BY SumPrice ASC
356
357GO
358SELECT Genre, Count(GameName) AS GamesNum FROM SteamDB.Game
359GROUP BY Genre
360HAVING Genre = 'Shooter'
361
362GO
363SELECT Genre, Min(Price) AS SumPrice FROM SteamDB.Game
364GROUP BY Genre
365HAVING Genre != 'Horror'
366ORDER BY SumPrice Desc
367
368GO
369SELECT Genre, Max(Price) AS SumPrice FROM SteamDB.Game
370GROUP BY Genre
371HAVING Genre != 'Horror'
372ORDER BY SumPrice Desc
373
374GO
375SELECT * FROM SteamDB.Game
376WHERE Price > 0
377UNION
378SELECT * FROM SteamDB.Game
379ORDER BY Price
380GO
381
382SELECT * FROM SteamDB.Game
383WHERE Price > 0
384UNION ALL
385SELECT * FROM SteamDB.Game
386ORDER BY Price
387GO
388
389SELECT * FROM SteamDB.Game
390EXCEPT
391SELECT * FROM SteamDB.Game
392WHERE Price > 0
393ORDER BY Price
394GO
395
396SELECT * FROM SteamDB.Game
397INTERSECT
398SELECT * FROM SteamDB.Game
399WHERE Price > 0
400ORDER BY Price