USE master
GO

IF  EXISTS (
        SELECT name
                FROM sys.databases
                WHERE name = N'AlexanderDubikovsky'
)
ALTER DATABASE AlexanderDubikovsky set single_user with rollback immediate
GO
DROP DATABASE AlexanderDubikovsky
GO

CREATE DATABASE AlexanderDubikovsky
GO

USE AlexanderDubikovsky
GO

IF OBJECT_ID('Meteostations', 'U') IS NOT NULL
        DROP TABLE Meteostations
GO

CREATE TABLE Meteostations
(
        StationID varchar(10) NOT NULL,
        StationName varchar(50) NULL,
        StationAddress varchar(100) NULL,
        CONSTRAINT PK_StationID PRIMARY KEY (StationID)
)
GO

IF OBJECT_ID('MeasuringTypes', 'U') IS NOT NULL
        DROP TABLE MeasuringTypes
GO

CREATE TABLE MeasuringTypes
(
        TypeID varchar(10) NOT NULL,
        Quantity varchar(30) NULL,
        Unit varchar(10) NULL,
        CONSTRAINT PK_Type_ID PRIMARY KEY (TypeID)
)
GO

IF OBJECT_ID('Measurings', 'U') IS NOT NULL
        DROP TABLE Measurings
GO

CREATE TABLE Measurings
(
        MStation varchar(10) NOT NULL,
        MType varchar(10) NOT NULL,
        MDate date NULL,
        MValue int NOT NULL,
        CONSTRAINT FK_Meteostations FOREIGN KEY (MStation)
        REFERENCES Meteostations(StationID),
        CONSTRAINT FK_MeasuringTypes FOREIGN KEY (MType)
        REFERENCES MeasuringTypes(TypeID)
)
GO

INSERT INTO Meteostations
        VALUES
        ('1', 'Lillehammer-Saetherengen', 'Norway'),
        ('2', 'Innsbruck-Flughafen', 'Austria'),
        ('3', 'Became Station 146540', 'Serbia'),
        ('4', 'Matsuyama', 'Japan'),
        ('5', 'Zadar Puntamika', 'Croatia'),
        ('6', N'Остров Преображения', 'Russia')
GO

INSERT INTO MeasuringTypes
        VALUES
        ('1', N'Температура воздуха', 'С°'),
        ('2', N'Атмосферное давление', 'мм рт.ст.'),
        ('3', N'Скорость ветра', 'м/с'),
        ('4', N'Влажность', '%')
GO

INSERT INTO Measurings
        VALUES
        ('1', '1', '20.09.2014', 15),
        ('1', '2', '20.09.2014', 723),
        ('4', '3', '20.09.2014', 4),
        ('3', '1', '20.09.2014', 24),
        ('1', '1', '20.09.2014', 17),
        ('1', '4', '20.09.2014', 63),
        ('6', '1', '20.09.2014', 21),
        ('4', '2', '20.09.2014', 734),
        ('1', '4', '20.09.2014', 77),
        ('3', '3', '20.09.2014', 4),
        ('5', '1', '20.09.2014', 8),
        ('6', '4', '20.09.2014', 34),
        ('4', '1', '20.09.2014', 9),
        ('2', '2', '20.09.2014', 754),
        ('3', '3', '20.09.2014', 7),
        ('2', '1', '20.09.2014', 19),
        ('1', '1', '21.09.2014', 24),
        ('5', '2', '21.09.2014', 715),
        ('4', '2', '21.09.2014', 751),
        ('1', '1', '21.09.2014', 20),
        ('6', '4', '21.09.2014', 66),
        ('2', '4', '21.09.2014', 57),
        ('1', '1', '21.09.2014', 20),
        ('5', '3', '21.09.2014', 1),
        ('4', '3', '21.09.2014', 8),
        ('3', '2', '21.09.2014', 65),
        ('1', '1', '21.09.2014', 18),
        ('5', '3', '21.09.2014', 3),
        ('3', '2', '21.09.2014', 43)
GO

SELECT Meteostations.StationName AS N'Станция', CONVERT(varchar(25), Measurings.MDate, 104) AS 'Дата',
    AVG(Measurings.MValue) AS 'Среднее значение', MeasuringTypes.Unit AS 'Единица измерения'
FROM Measurings
INNER JOIN Meteostations
ON Measurings.MStation=Meteostations.StationID
INNER JOIN MeasuringTypes
ON Measurings.MType=MeasuringTypes.TypeID
GROUP BY Meteostations.StationName, MeasuringTypes.Unit, Measurings.MDate
ORDER BY Measurings.MDate, Meteostations.StationName, MeasuringTypes.Unit
GO

SELECT Meteostations.StationName AS N'Станция', AVG(Measurings.MValue) AS 'Среднее значение в данный день', MeasuringTypes.Unit AS 'Единица измерения'
FROM Measurings
INNER JOIN Meteostations
ON Measurings.MStation=Meteostations.StationID
INNER JOIN MeasuringTypes
ON Measurings.MType=MeasuringTypes.TypeID
WHERE Measurings.MDate = '20.09.2014'
GROUP BY Meteostations.StationName, MeasuringTypes.Unit
ORDER BY Meteostations.StationName, MeasuringTypes.Unit
GO