USE master
GO

IF  EXISTS (
        SELECT name
                FROM sys.DATABASES
                WHERE name = N'AlexanderDubikovsky3'
)
ALTER DATABASE AlexanderDubikovsky3 SET single_user WITH ROLLBACK immediate
GO
DROP DATABASE AlexanderDubikovsky3
GO

CREATE DATABASE AlexanderDubikovsky3
GO

USE AlexanderDubikovsky3
GO

IF OBJECT_ID('Currensies', 'U') IS NOT NULL
        DROP TABLE Currensies
GO
CREATE TABLE Currensies
(
        id CHAR(3) NOT NULL,
        Name VARCHAR(30) NULL,
        CONSTRAINT PK_Currensies PRIMARY KEY (id)
)
GO

INSERT INTO Currensies
                VALUES
                ('RUB', N'Российский рубль'),
                ('USD', N'Американский доллар'),
                ('EUR', N'Евро'),
                ('GBP', N'Фунт стерлингов')
GO

IF OBJECT_ID('Rates', 'U') IS NOT NULL
        DROP TABLE Rates
GO
CREATE TABLE Rates
(
                id1 CHAR(3) NOT NULL,
                id2 CHAR(3) NOT NULL,
                Rate DECIMAL(5,1) NOT NULL,
                CONSTRAINT PK_Rates PRIMARY KEY (id1, id2),
                CONSTRAINT FK_Sold FOREIGN KEY (id1)
                REFERENCES Currensies(id),
                CONSTRAINT FK_Buy FOREIGN KEY (id2)
                REFERENCES Currensies(id)
)

INSERT INTO Rates
                VALUES
                ('USD', 'RUB', 45.6),
                ('RUB', 'USD', 0.0213),
                ('EUR', 'RUB', 59.31),
                ('RUB', 'EUR', 0.0171),
                ('GBP', 'RUB', 73.476),
                ('RUB', 'GBP', 0.0136),
                ('EUR', 'USD', 1.25),
                ('USD', 'EUR', 0.8),
                ('GBP', 'USD', 1.6),
                ('USD', 'GBP', 0.615),
                ('GBP', 'EUR', 1.229),
                ('EUR', 'GBP', 0.798),
                ('RUB', 'RUB', 1),
                ('USD', 'USD', 1),
                ('EUR', 'EUR', 1),
                ('GBP', 'GBP', 1)

GO

IF OBJECT_ID('Wallet', 'U') IS NOT NULL
        DROP TABLE Wallet
GO
CREATE TABLE Wallet
(
                CurID CHAR(3) NOT NULL,
                VALUE DECIMAL(38, 2) NULL,
                CONSTRAINT PK_Wallet PRIMARY KEY (CurID),
                CONSTRAINT FK_Wallet FOREIGN KEY (CurID)
                REFERENCES Currensies(id)
)
GO

INSERT INTO Wallet
        VALUES
        ('RUB', 1000),
        ('USD', 23.75),
        ('EUR', 205),
        ('GBP', 75.5)
GO

SELECT Currensies.Name AS 'Валюта', Wallet.VALUE AS 'Сумма в кошельке'
FROM Wallet
INNER JOIN Currensies
ON Currensies.id = Wallet.CurID
GROUP BY Currensies.Name, Wallet.VALUE
GO

IF OBJECT_ID (N'ConvertedValue') IS NOT NULL
DROP FUNCTION ConvertedValue
GO
CREATE FUNCTION ConvertedValue(@CurCode CHAR(3))
RETURNS DECIMAL(38,2)
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @VALUE DECIMAL(38,2)
    SET @VALUE = (
        SELECT SUM(Wallet.VALUE*Rates.Rate)
        FROM Wallet
        INNER JOIN Rates
        ON (Wallet.CurID = Rates.id1)
        WHERE Rates.id2 = @CurCode
    )
    RETURN @VALUE
END
GO

SELECT dbo.ConvertedValue('RUB') AS 'В кошельке рублей:'
SELECT dbo.ConvertedValue('USD') AS 'В кошельке долларов:'
SELECT dbo.ConvertedValue('EUR') AS 'В кошельке евро:'
SELECT dbo.ConvertedValue('GBP') AS 'В кошельке фунтов стерлингов:'
GO

SELECT id1,[EUR], [GBP],[RUB], [USD]
FROM (
    SELECT id1, id2, Rate
    FROM Rates
    GROUP BY id1, id2, Rate
    ) AS p
PIVOT
(
SUM(Rate)
FOR id2
IN([GBP], [USD], [EUR], [RUB])
) AS pvt
GO