· 8 years ago · Dec 22, 2017, 09:36 AM
1USE master
2GO
3
4IF EXISTS (
5 SELECT name
6 FROM sys.databases
7 WHERE name = N'Merzlyakov'
8)
9ALTER DATABASE [Merzlyakov] set single_user with rollback immediate
10GO
11
12IF EXISTS (
13 SELECT name
14 FROM sys.databases
15 WHERE name = N'Merzlyakov'
16)
17DROP DATABASE [Merzlyakov]
18GO
19
20CREATE DATABASE [Merzlyakov]
21GO
22
23USE [Merzlyakov]
24GO
25
26CREATE SCHEMA Firm
27GO
28
29CREATE TABLE Firm.Auctions(
30 auct_number int IDENTITY PRIMARY KEY,
31 date_ Date,
32 location_ nvarchar(20),
33 auct_description nvarchar(100)
34)
35GO
36
37CREATE TABLE Firm.Lots(
38 auct_number int FOREIGN KEY REFERENCES Firm.Auctions(auct_number),
39 num_lot smallint,
40 lot nvarchar(40),
41 seller nvarchar(40),
42 price_1 int,
43 lot_description nvarchar(100)
44)
45GO
46
47INSERT INTO Firm.Auctions values
48('20171221', N'Екатеринбург',N'картины'),
49('20171221', N'МоÑква',N'19 век'),
50('20171223', N'Санкт-Петербург',N'предметы интерьера'),
51('20171224', N'МоÑква',N'драгоценноÑти')
52GO
53
54INSERT INTO Firm.Lots values
55(1, 1, N'Картина да Винчи', N'Булычев', 3460, N'опиÑание' ),
56(1, 2, N'Картина ван Гога', N'Ðшарапов', 2388, N'опиÑание'),
57(1, 3, N'Картина ПиÑарро', N'Макаров', 1900, N'опиÑание'),
58(3, 1, N'КитайÑÐºÐ°Ñ Ð¼Ñ€Ð°Ð¼Ð¾Ñ€Ð½Ð°Ñ Ð²Ð°Ð·Ð°', N'Сафин', 2775, N'опиÑание'),
59(2, 1, N'Кольцо Ñ Ð±Ñ€Ð¸Ð»Ð»Ð¸Ð°Ð½Ñ‚Ð¾Ð¼', N'Макаров', 1220, N'опиÑание'),
60(3, 2, N'Ожерелье', N'Макаров', 2300, N'опиÑание'),
61(4, 1, N'Серьги', N'Макаров', 1630, N'опиÑание')
62GO
63
64IF OBJECT_ID ('Firm.oneAuction','TR') IS NOT NULL
65 DROP TRIGGER Firm.oneAuction;
66GO
67-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
68-- when the credit rating of the specified vendor is set to 5 (below average).
69
70CREATE TRIGGER Firm.oneAuction ON Firm.Lots
71 AFTER INSERT
72AS
73IF EXISTS (SELECT *
74 FROM Firm.Lots l
75 JOIN inserted i ON l.lot = i.lot
76 JOIN Firm.Auctions auct1 ON auct1.auct_number = l.auct_number
77 JOIN Firm.Auctions auct2 ON auct2.auct_number = i.auct_number
78 WHERE auct1.date_= auct2.date_
79 )
80BEGIN
81RAISERROR ('Товар не может быть на двух аукционах в один день!', 16, 1);
82ROLLBACK TRANSACTION;
83RETURN
84END;
85GO
86
87
88CREATE TABLE Firm.Sold(
89 auct_number int FOREIGN KEY REFERENCES Firm.Auctions(auct_number),
90 lot nvarchar(40),
91 buyer nvarchar(40),
92 price_2 int
93)
94GO
95
96INSERT INTO Firm.Sold values
97(1, N'Картина да Винчи', N'Купцов', 123000),
98(1, N'Картина ван Гога', N'Зайкин', 219000),
99(1, N'Картина ПиÑарро', N'Штамп', 37000),
100(3, N'КитайÑÐºÐ°Ñ Ð¼Ñ€Ð°Ð¼Ð¾Ñ€Ð½Ð°Ñ Ð²Ð°Ð·Ð°', N'Купцов', 453000),
101(4, N'Кольцо Ñ Ð±Ñ€Ð¸Ð»Ð»Ð¸Ð°Ð½Ñ‚Ð¾Ð¼', N'Купцов', 333000)
102
103GO
104
105
106--1
107SELECT auct.auct_description[Ðукцион], SUM(sold.price_2)[Доходы]
108FROM Firm.Sold sold
109 JOIN Firm.Auctions auct ON auct.auct_number = sold.auct_number
110GROUP BY auct_description
111ORDER BY Доходы DESC
112GO
113
114--2
115--SELECT sold.lot[Лот]
116--FROM Firm.Sold sold
117-- JOIN Firm.Auctions auct ON auct.auct_number = sold.auct_number
118--WHERE auct.date_ > '20171101' AND auct.date_ < '20171231'
119--GO
120
121SELECT sold.lot[Лот], auct.auct_description[Ðукцион]
122FROM Firm.Sold sold
123 JOIN Firm.Lots lots ON lots.lot = sold.lot
124 JOIN Firm.Auctions auct ON auct.auct_number = lots.auct_number
125WHERE auct.date_ > '20171121' AND auct.date_ < '20171231'
126GO
127
128
129--3
130
131SELECT lots.seller[Продавец], SUM(sold.price_2)[Доходы]
132FROM Firm.Lots lots
133 JOIN Firm.Sold sold ON lots.lot = sold.lot
134 JOIN Firm.Auctions auct ON auct.auct_number = sold.auct_number
135WHERE auct.date_ > '20171121' AND auct.date_ < '20171222'
136GROUP BY seller
137ORDER BY Доходы DESC
138GO
139
140--4
141SELECT auct.auct_description[Ðукцион], COUNT(lots.num_lot)[КоличеÑтво лотов]
142FROM Firm.Auctions auct
143 JOIN Firm.Lots lots ON auct.auct_number = lots.auct_number
144WHERE auct.location_ = 'МоÑква'
145GROUP BY auct_description
146ORDER BY [КоличеÑтво лотов] DESC
147GO
148
149--5
150SELECT lots.seller[Продавец], lots.lot[Лот]
151FROM Firm.Lots lots
152 JOIN Firm.Auctions auct ON auct.auct_number = lots.auct_number
153WHERE auct.date_ > '20171121' AND auct.date_ < '20171222'
154GO
155
156--6
157SELECT sold.buyer[Покупатель], COUNT(sold.lot)[КоличеÑтво лотов]
158FROM Firm.Sold sold
159 JOIN Firm.Auctions auct ON auct.auct_number = sold.auct_number
160WHERE auct.date_ > '20171121' AND auct.date_ < '20171231'
161GROUP BY buyer
162GO