CreatedDataBaseAndQuiries.sql

CREATE DATABASE AnalysisCountSteps/*ÑÎÇÄÀÍÈÅ ÁÀÇÛ ÄÀÍÍÛÕ*/
GO 

USE AnalysisCountSteps
CREATE TABLE Users /*ÑÎÇÄÀÍÈÅ ÒÀÁËÈÖÛ*/
(
	Rank_ INT,
	User_ NVARCHAR(50),
	Status_ VARCHAR(50),
	Steps INT
);
GO 

CREATE TABLE Statistics_  /*ÑÎÇÄÀÍÈÅ ÒÀÁËÈÖÛ ÑÒÀÒÈÑÒÈÊÀ*/
(
	ÔÈÎ NVARCHAR(50),
	ÑðåäíååÊîëÂîØàãîâ FLOAT,
	Ëó÷øèéÐåçóëüòàò INT,
	ÕóäøèéÐåçóëüòàò INT
);
GO 

/*ÎÁÚßÂËÅÍÈÅ,ÈÍÈÖÈÀËÈÇÀÖÈß ÄÀÍÍÛÕ Â ÏÅÐÅÌÅÍÍÛÅ Ñ ÏÎÑËÅÄÓÞÙÅÉ ÂÑÒÀÂÊÎÉ Â ÒÀÁËÈÖÓ*/
DECLARE @Name NVARCHAR(50), @Average FLOAT, @TheBest INT, @TheWorst INT
SET @Name='Íàðàñòàþùèé Ãàâð'

/*ÇÀÏÈÑÜ ÏÎËÓ×ÅÍÍÛÕ ÇÀÏÐÎÑΠ ÏÅÐÅÌÅÍÍÛÅ*/
SET @Average=(SELECT AVG(Steps) FROM Users WHERE User_='Íàðàñòàþùèé Ãàâð')
SET @TheBest=(SELECT MAX(Steps) FROM Users WHERE User_='Íàðàñòàþùèé Ãàâð')
SET @TheWorst=(SELECT MIN(Steps) FROM Users WHERE User_='Ðàçíóëèêîâ Ïàâåë')

/*ÇÀÏÈÑÜ ÇÍÀ×ÅÍÈÉ Â ÒÀÁËÈÖÓ*/
INSERT INTO Statistics_ (ÔÈÎ,ÑðåäíååÊîëÂîØàãîâ,Ëó÷øèéÐåçóëüòàò,ÕóäøèéÐåçóëüòàò) VALUES(@Name,@Average,@TheBest,@TheWorst)

/*ÇÀÏÐÎÑ ÍÀ ÊÎË-ÂÎ ÇÀÏÈÑÅÉ Â ÍÎÂÎÉ ÒÀÁËÈÖÅ*/
SELECT COUNT(*) FROM Statistics_

/*ÄÎÁÀÂËÅÍÈÅ ÄÂÓÕ ÑÒÎËÁÖÅÂ Ñòàòóñ è Ðàíã*/
ALTER TABLE Statistics_ 
ADD [Ñòàòóñ] VARCHAR(15) NOT NULL DEFAULT 'Finished'

ALTER TABLE Statistics_ 
ADD [Ðàíã] INT  

/*ÎÁÚßÂËÅÍÈÅ,ÈÍÈÖÈÀËÈÇÀÖÈß ÄÀÍÍÛÕ Â ÏÅÐÅÌÅÍÍÓÞ Ñ ÏÎÑËÅÄÓÞÙÈÌ ÄÎÁÀÂÀËÅÍÈÅÌ*/
DECLARE @Rank INT

/*ÇÀÏÈÑÜ ÏÎËÓ×ÅÍÍÛÕ ÇÀÏÐÎÑΠ ÏÅÐÅÌÅÍÍÛÅ*/
SET @Rank=(SELECT AVG(Rank_) AS 'Ðàíã' FROM Users WHERE User_='Íàðàñòàþùèé Ãàâð')

/*ÇÀÏÈÑÜ ÇÍÀ×ÅÍÈÉ Â ÒÀÁËÈÖÓ*/
UPDATE Statistics_ SET Ðàíã=@Rank WHERE ÔÈÎ='Íàðàñòàþùèé Ãàâð'