Gmail   Atnes Ness <atnesness@gmail.com>
(no subject)
Atnes Ness <atnesness@gmail.com>    14 October 2015 at 16:11
TO: galinka95.95@mail.ru

---------- Forwarded message ----------
FROM: Atnes Ness <atnesness@gmail.com>
DATE: 7 October 2015 at 17:39
Subject:
TO: Atnes Ness <atnesness@gmail.com>


CREATE DATABASE [Nesmianov.Artem]

GO

USE [Nesmianov.Artem];

GO

CREATE TABLE measure_table (

id INTEGER PRIMARY KEY,

name VARCHAR(100) );



CREATE TABLE station_table (

id INTEGER PRIMARY KEY,

name VARCHAR(100));



CREATE TABLE stat_measure_table (

id INTEGER PRIMARY KEY,

datestamp DATE,

DATA FLOAT,

station_id INTEGER,

measure_id INTEGER );

ALTER TABLE stat_measure_table

 ADD CONSTRAINT fk_station

 FOREIGN KEY (station_id)

 REFERENCES station_table(id);







ALTER TABLE stat_measure_table

 ADD CONSTRAINT fk_measure

 FOREIGN KEY (measure_id)

 REFERENCES measure_table(id);

ALTER TABLE stat_measure_table

ADD timestmp TIME;



ALTER TABLE station_table ADD addr VARCHAR(100)

 DEFAULT 'Россия, г.';



INSERT INTO station_table (id, name)

VALUES (0, N'station1');

INSERT INTO station_table (id, name)

VALUES (1, 'station2');



INSERT INTO station_table (id, name)

VALUES (2, 'station3');



INSERT INTO station_table (id, name)

VALUES (3, 'station4');



INSERT INTO station_table (id, name)

VALUES (4, 'station5');

INSERT INTO station_table (id, name)

VALUES (5, 'station6');



INSERT INTO station_table (id, name)

VALUES (6, 'station7');



INSERT INTO station_table (id, name)

VALUES (7, 'station8');



INSERT INTO station_table (id, name)

VALUES (8, 'station9');



INSERT INTO station_table (id, name)

VALUES (9, 'station10');





INSERT INTO measure_table (id, name)

VALUES (0, 'Давление');

INSERT INTO measure_table (id, name)

VALUES (1, 'Температура');

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (0, 0, 0, '2015-08-12', '12:00', 111);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (1, 1, 0, '2015-08-12', '12:00', 24);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (2, 0, 1, '2015-08-12', '13:00', 130);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (3, 1, 1, '2015-08-12', '13:00', 27);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (4, 0, 2, '2015-08-12', '14:00', 101);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (5, 1, 2, '2015-08-12', '14:00', 30);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (6, 0, 2, '2015-08-12', '15:00', 98);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (7, 1, 2, '2015-08-12', '15:00', 21);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (8, 0, 2, '2015-08-12', '16:00', 111);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (9, 1, 2, '2015-08-12', '16:00', 25);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (10, 0, 2, '2015-08-12', '17:00', 110);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (11, 1, 2, '2015-08-12', '17:00', 32);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (12, 0, 2, '2015-08-12', '18:00', 100);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (13, 1, 2, '2015-08-12', '18:00', 28);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (14, 0, 2, '2015-08-12', '19:00', 110);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (15, 1, 2, '2015-08-12', '19:00', 24);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (16, 0, 2, '2015-08-12', '20:00', 98);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (17, 1, 2, '2015-08-12', '20:00', 20);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (18, 0, 2, '2015-08-12', '21:00', 95);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (19, 1, 2, '2015-08-12', '21:00', 18);





INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (20, 0, 2, '2015-08-13', '18:00', 100);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (21, 1, 2, '2015-08-14', '18:00', 28);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (22, 0, 2, '2015-08-13', '19:00', 110);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (23, 1, 2, '2015-08-13', '19:00', 24);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (24, 0, 2, '2015-08-13', '20:00', 98);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (25, 1, 2, '2015-08-14', '20:00', 20);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (26, 0, 2, '2015-08-14', '21:00', 95);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (27, 1, 2, '2015-08-13', '21:00', 18);



INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (28, 1, 2, '2015-08-13', '21:00', 18);

INSERT INTO stat_measure_table (id, measure_id, station_id, datestamp, timestmp, DATA)

VALUES (29, 0, 2, '2015-08-13', '21:00', 18);



 CREATE TABLE measure_type_table (

id INTEGER PRIMARY KEY,

name VARCHAR(10)

 );

INSERT INTO measure_type_table (id, name)

VALUES (0, 'C');

INSERT INTO measure_type_table (id, name)

VALUES (1, 'Па');

ALTER TABLE measure_type_table

ADD measure_id INTEGER;



ALTER TABLE measure_type_table

 ADD CONSTRAINT fk_tmeasure

 FOREIGN KEY (measure_id)

 REFERENCES measure_table(id);

 SELECT datestamp FROM stat_measure_table GROUP BY datestamp;





 SELECT measure_table.name AS 'Измерение', MAX(DATA) AS 'MAX', MIN(DATA) AS 'MIN'

 FROM stat_measure_table LEFT JOIN measure_table

    ON measure_table.id = stat_measure_table.measure_id

    GROUP BY (measure_table.name)



UPDATE measure_type_table SET measure_id = 1 WHERE id = 0;

UPDATE measure_type_table SET measure_id = 0 WHERE id = 1;

SELECT * FROM measure_type_table;

SELECT DATA AS 'C' FROM stat_measure_table WHERE measure_id IN (

    SELECT measure_id FROM measure_type_table WHERE measure_table.id = 0

);

SELECT name AS 'измерение' , DATA AS 'данные', datestamp AS 'дата'

FROM stat_measure_table LEFT JOIN measure_table

    ON stat_measure_table.measure_id = measure_table.id WHERE DATA IN (

    SELECT MIN(DATA) FROM stat_measure_table GROUP BY(measure_id)

    )



SELECT datestamp AS 'дата', measure_table.name AS 'измерение' ,

 AVG(DATA) AS 'ср знач', measure_type_table.name AS 'ед изм'

 FROM

stat_measure_table LEFT JOIN measure_table

    ON stat_measure_table.measure_id = measure_table.id

    LEFT JOIN measure_type_table

    ON measure_table.id = measure_type_table.measure_id

    GROUP BY datestamp, measure_table.name, measure_type_table.name;