mysql

USE master
GO

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

IF  EXISTS (
        SELECT name
                FROM sys.databases
                WHERE name = N'ElenaBeklenishcheva'
)
DROP DATABASE [ElenaBeklenishcheva]
GO

CREATE DATABASE [ElenaBeklenishcheva]
GO

USE [ElenaBeklenishcheva]
GO

IF EXISTS(
  SELECT *
    FROM sys.schemas
   WHERE name = N'Scheme'
)
 DROP SCHEMA Scheme
GO

CREATE SCHEMA Scheme
GO

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

CREATE TABLE station (
        id_station int PRIMARY KEY not NULL,
        name_station varchar(10) NULL,
        address_station varchar(10) NULL,
)
GO

INSERT INTO station (id_station, name_station, address_station) VALUES
(0, 'Cтанция 1', 'Адрес 1'),
(1, 'Станция 2', 'Адрес 2'),
(2, 'Станция 3', 'Адрес 3')

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

CREATE TABLE measure_type (
        id_type int PRIMARY KEY not NULL,
        name_measure varchar(30) NULL,
        measure_unit char(5) NULL,
)
GO

INSERT INTO measure_type(id_type, name_measure, measure_unit) VALUES
(0, 'Температура', 'C'),
(1, 'Скорость ветра', 'км/ч'),
(2, 'Давление', 'мм'),
(3, 'Осадки', NULL)

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

CREATE TABLE measures (
		id_measure int PRIMARY KEY not NULL,
        id_type int not NULL,
        id_station int not NULL,
        measure_time date not NULL,
        measure_result int not NULL
		FOREIGN KEY (id_type) REFERENCES measure_type(id_type),
		FOREIGN KEY (id_station) REFERENCES station(id_station),
)
GO

INSERT INTO measures VALUES
(0, 0, 0, '2015/10/15', 20),
(1, 0, 0, '2015/10/10', 30),
(2, 0, 1, '2015/10/15', 10),
(3, 0, 2, '2015/10/10', -10),
(4, 1, 2, '2015/06/23', 15),
(5, 1, 1, '2015/07/23', 3),
(6, 1, 0, '2015/08/23', 1),
(7, 2, 0, '2015/08/23', 740),
(8, 2, 1, '2015/07/23', 750),
(9, 2, 2, '2015/06/23', 720),
(10, 3, 0, '2015/07/23', 0),
(11, 3, 1, '2015/08/23', 1),
(12, 3, 2, '2015/06/23', 10)

/*SELECT id_type AS 'Номер типа измерений',
id_station AS 'Номер станции',
measure_time AS 'Время измерения',
measure_result AS 'Результат измерения'
FROM measures*/

SELECT CONVERT(varchar, measures.measure_time, 104) as 'Дата измерения',
	station.name_station as 'Имя станции',
	AVG(measures.measure_result)AS 'Средняя температура'
FROM measures, station
WHERE id_type < 1 AND measures.id_station = 0
GROUP BY  measures.measure_time, station.name_station
GO

SELECT CONVERT(varchar, measures.measure_time, 104) as 'Дата измерения',
	station.name_station as 'Имя станции',
	AVG(measures.measure_result)AS 'Средняя скорость ветра'
FROM measures, station
WHERE measures.id_type=1 AND measures.id_station = 0
GROUP BY  measures.measure_time, station.name_station
GO

SELECT CONVERT(varchar, measures.measure_time, 104) as 'Дата измерения',
	station.name_station as 'Имя станции',
	AVG(measure_result)AS 'Среднее давление'
FROM measures, station
WHERE measures.id_type=2 AND measures.id_station = 0
GROUP BY  measures.measure_time, station.name_station
GO

SELECT CONVERT(varchar, measures.measure_time, 104) as 'Дата измерения',
	station.name_station as 'Имя станции',
	AVG(measure_result)AS 'Среднее количество осадков на первой станции'
FROM measures, station
WHERE measures.id_type=3 AND measures.id_station = 0
GROUP BY  measures.measure_time, station.name_station
GO