lab3

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('commands', 'U') IS NOT NULL
    DROP TABLE commands
GO

CREATE TABLE commands (
    command_id INT PRIMARY KEY NOT NULL,
    command_name VARCHAR(50) UNIQUE NOT NULL
)
GO

INSERT INTO commands VALUES
(1, 'Урал'),
(2, 'Спартак'),
(3, 'Рубин')
GO


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

CREATE TABLE consist (
    command_id INT NOT NULL,
    player_id INT PRIMARY KEY NOT NULL,
    player_surname VARCHAR(50) NOT NULL,
    player_name VARCHAR(50) NOT NULL

    FOREIGN KEY (command_id) REFERENCES commands(command_id)
)
GO

INSERT INTO consist VALUES
(1, 1, 'Арапов', 'Дмитрий'),
(1, 2, 'Заболотный', 'Николай'),
(1, 3, 'Жевнов', 'Юрий'),
(1, 4, 'Белозеров', 'Александр'),
(1, 5, 'Фомин', 'Денис'),

(2, 6, 'Ребров', 'Артем'),
(2, 7, 'Гранат', 'Владимир'),
(2, 8, 'Кутепов', 'Илья'),
(2, 9, 'Глушаков', 'Денис'),

(3, 10, 'Кузьмин', 'Олег'),
(3, 11, 'Устинов', 'Виталий'),
(3, 12, 'Сорокин', 'Егор'),
(3, 13, 'Жестоков', 'Максим')
GO


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

CREATE TABLE games_info (
    id_game INT PRIMARY KEY NOT NULL,
    game_date DATE NOT NULL,
    id_command_owner INT NOT NULL,
    id_command_guest INT NOT NULL,
    id_kipper_own INT NOT NULL,
    id_kipper_g INT NOT NULL,
    score_command_own INT NOT NULL,
    score_command_guest INT NOT NULL

    FOREIGN KEY (id_kipper_own) REFERENCES consist(player_id),
    FOREIGN KEY (id_kipper_g) REFERENCES consist(player_id)
)
GO

INSERT INTO games_info VALUES
(1, '20151125', 1, 2, 1, 6, 1, 1),
(2, '20151124', 2, 3, 7, 10, 2, 0),
(3, '20151123', 3, 1, 11, 2, 0, 3),
(4, '20160322', 2, 1, 7, 1, 3, 2),
(5, '20160321', 3, 2, 11, 6, 1, 1),
(6, '20160320', 1, 3, 1, 10, 1, 0)
GO



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

CREATE TABLE goals(
    id_game INT NOT NULL,
    id_player INT NOT NULL,
    id_goal INT PRIMARY KEY NOT NULL

    FOREIGN KEY (id_player) REFERENCES consist(player_id),
    FOREIGN KEY (id_game) REFERENCES games_info(id_game)
)
GO

INSERT INTO goals VALUES
(1, 2, 1),
(1, 7, 2),

(2, 8, 3),
(2, 8, 4),

(3, 2, 5),
(3, 3, 6),
(3, 4, 7),

(4, 6, 8),
(4, 8, 9),
(4, 9, 10),
(4, 3, 11),
(4, 4, 12),

(5, 12, 13),
(5, 8, 14),

(6, 3, 15)
GO

CREATE FUNCTION raiting_table()
RETURNS @ret_raiting_table TABLE (
    POSITION INT PRIMARY KEY NOT NULL,
    Team VARCHAR(50) UNIQUE NOT NULL,
    Score INT NOT NULL,
    Scored INT NOT NULL,
    Missing INT NOT NULL
)
AS
BEGIN
    --заполнить таблицу
    DECLARE @temp_table TABLE(tteam VARCHAR(50),
                              tscore INT,
                              tscored INT,
                              tmissing INT)

    DECLARE @temp_str VARCHAR(2000);

    DECLARE @teams_count INT;
    SET @teams_count = (SELECT COUNT(commands.command_id)
                        FROM commands)
    DECLARE @counter INT = 0;
    WHILE(@counter < @teams_count)
        SET @temp_str = (SELECT commands.command_name, games_info.score_command_own, games_info.score_command_own, games_info.score_command_guest
                            FROM commands, games_info
                            WHERE commands.command_id = games_info.id_command_owner AND commands.command_id = @counter)
        print(@temp_str);
        SET @counter = @counter + 1;
    END;

    INSERT @ret_raiting_table
    SELECT games_info.id_game, commands.command_name, games_info.score_command_guest, games_info.score_command_guest,games_info.score_command_guest
    FROM games_info, commands
    WHERE games_info.id_command_guest =commands.command_id

    RETURN;
END
GO