· 9 years ago · Dec 26, 2016, 06:10 AM
1USE master
2GO
3
4IF EXISTS (
5 SELECT name
6 FROM sys.databases
7 WHERE name = N'Uliana_Kvashnina'
8)
9
10ALTER DATABASE [Uliana_Kvashnina] set single_user with rollback immediate
11GO
12
13IF EXISTS (
14 SELECT name
15 FROM sys.databases
16 WHERE name = N'Uliana_Kvashnina'
17)
18DROP DATABASE [Uliana_Kvashnina]
19GO
20
21CREATE DATABASE [Uliana_Kvashnina]
22GO
23
24USE [Uliana_Kvashnina]
25GO
26
27create table currency
28(
29 id int NOT NULL,
30 currency_name nvarchar(3) unique,
31 constraint PK_id primary key (id)
32)
33go
34
35create table purse
36(
37 currency_id int NOT NULL,
38 amount int null,
39 CONSTRAINT FK_currency_id FOREIGN KEY (currency_id) REFERENCES currency(id) ON UPDATE CASCADE,
40 CONSTRAINT amount CHECK (amount >= 0)
41)
42go
43
44create table course_of_exchange
45(
46 id_current_currency int NOT NULL,
47 id_exchanged_currency int NOT NULL,
48 tariff float NOT NULL,
49 CONSTRAINT FK_id_current_currency FOREIGN KEY (id_current_currency) REFERENCES currency(id) ON UPDATE NO ACTION,
50 CONSTRAINT FK_id_exchanged_currency FOREIGN KEY (id_exchanged_currency) REFERENCES currency(id) ON UPDATE NO ACTION
51)
52go
53
54CREATE UNIQUE INDEX id_unique ON course_of_exchange (id_current_currency, id_exchanged_currency)
55
56insert into currency values
57 (1, N'AUD'),
58 (2, N'EUR'),
59 (3, N'AZN'),
60 (4, N'RUB'),
61 (5, N'USD')
62go
63
64insert into purse values
65 (1, 50),
66 (2, 150),
67 (3, 100),
68 (4, 1000),
69 (5, 200)
70go
71
72insert into course_of_exchange values
73 (1, 1, 1),
74 (1, 2, 0.97908),
75 (1, 3, 1.05909),
76 (1, 4, 1.04631),
77 (1, 5, 0.73377),
78 (2, 1, 1.02099),
79 (2, 2, 1),
80 (2, 3, 1.08153),
81 (2, 4, 1.06843),
82 (2, 5, 0.74932),
83 (3, 1, 0.94376),
84 (3, 2, 0.92420),
85 (3, 3, 1),
86 (3, 4, 0.98761),
87 (3, 5, 0.69264),
88 (4, 1, 0.95538),
89 (4, 2, 0.93555),
90 (4, 3, 1.01199),
91 (4, 4, 1),
92 (4, 5, 0.70114),
93 (5, 1, 1.36255),
94 (5, 2, 1.33432),
95 (5, 3, 1.44335),
96 (5, 4, 1.42586),
97 (5, 5, 1)
98go
99
100CREATE PROCEDURE add_to_purse
101@currency_id int,
102@amount int
103AS
104 IF NOT EXISTS (SELECT * FROM purse WHERE currency_id = @currency_id)
105 INSERT purse(currency_id, amount) VALUES (@currency_id, 0)
106 UPDATE purse SET amount = amount + @amount WHERE currency_id = @currency_id
107GO
108
109exec add_to_purse 5, 0
110go
111
112exec add_to_purse 1, -5
113go
114
115
116CREATE FUNCTION convert_and_get_sum(@currency_name nvarchar(3))
117RETURNS float
118BEGIN
119 DECLARE @currency_id int
120 SET @currency_id = (SELECT id FROM currency WHERE currency_name = @currency_name)
121 RETURN (SELECT SUM(amount * course_of_exchange.tariff) FROM purse
122 INNER JOIN course_of_exchange ON course_of_exchange.id_current_currency = currency_id AND course_of_exchange.id_exchanged_currency = @currency_id)
123END;
124GO
125
126PRINT(dbo.convert_and_get_sum(N'USD'))