Database

USE [Basketball]

CREATE TABLE basketball_league (
        id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
        name nvarchar (100),
        number_of_teams INT,
        region nvarchar (100)
)

CREATE TABLE date_match (
        id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
        date_day INT,
        date_month INT,
        date_year INT,
        date_hour INT,
        date_minute INT
)

CREATE TABLE basketball_club (
        id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
        name nvarchar (50),
        country nvarchar (50),
        city nvarchar (50),
        club_budget INT,
        id_in_basketball_league INT FOREIGN KEY REFERENCES basketball_league(id)
)

CREATE TABLE color_team (
        id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
        home_color nvarchar(100),
        guest_color nvarchar(100),
        id_basketbal_club INT FOREIGN KEY REFERENCES basketball_club(id)
)

CREATE TABLE club_president (
        id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
        name nvarchar (100),
        wage INT,
        id_basketball_club INT FOREIGN KEY REFERENCES basketball_club(id)
)

CREATE TABLE club_coach (
        id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
        name nvarchar (100),
        wage INT,
        id_basketball_club INT FOREIGN KEY REFERENCES basketball_club(id)
)

CREATE TABLE club_players (
        id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
        name nvarchar (100),
        nation nvarchar(50),
        wage INT,
        id_basketball_club INT FOREIGN KEY REFERENCES basketball_club(id),
)

CREATE TABLE transfer_market (
        id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
        price INT,
        id_basketbal_club_from INT FOREIGN KEY REFERENCES basketball_club(id),
        id_basketbal_club_to INT FOREIGN KEY REFERENCES basketball_club(id),
        id_player INT FOREIGN KEY REFERENCES club_players(id),
        id_date INT FOREIGN KEY REFERENCES date_match(id)
)

CREATE TABLE schedule (
        id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
        id_basketball_club_home INT FOREIGN KEY REFERENCES basketball_club(id),
        id_basketball_club_guest INT FOREIGN KEY REFERENCES basketball_club(id),
        id_date INT FOREIGN KEY REFERENCES date_match(id),
        id_league INT FOREIGN KEY REFERENCES basketball_league(id)
)

CREATE TABLE arena (
        id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
        capacity INT,
        name nvarchar (100),
        id_basketbal_club INT FOREIGN KEY REFERENCES basketball_club(id)
)

INSERT INTO basketball_league VALUES
('Высшая лига', 17 , 'Russia'),
('VTB United League', 16,'Europe')

INSERT INTO basketball_club VALUES
('Nymburk', 'Czech Republic', 'Nymburk', 1000, 2),
('CSKA', 'Russia', 'Moscow', 10000, 2),
('Khimki', 'Russia', 'Moscow Region', 3453, 2),
('Avtodor', 'Russia', 'Saratov', 2131, 2),
('Krasnye Krylia', 'Russia', 'Samara', 4533, 2),
('Строитель', 'Russia', 'Энгельс', 2342, 1),
('Динамо', 'Russia', 'Челябинск', 3453, 1),
('Рускон', 'Russia', 'Мордовия', 2342, 1),
('Муссон', 'Russia', 'Севастополь', 5765, 1),
('Союз', 'Russia', 'Заречный', 2342, 1)

INSERT INTO arena VALUES
(11650, 'Tipsport Arena', 1),
(13126, 'Megasport Arena', 2),
(6200, 'Basketball Center', 3),
(10000, 'FOK Zvezdny', 4),
(3000, 'MTL Arena', 5),
(200, 'ДК Дружба', 6),
(300, 'ФОК Челяба', 7),
(200, 'ДС Мордовия', 8),
(400, 'ФОК Севастополь', 9),
(200, 'ФОК Заречный', 10)

INSERT INTO club_president VALUES
('Miroslav Jansta', 100, 1),
('Andrey Vatutin', 200, 2),
('Dmitry Golubkov', 300, 3),
('Vladimir Rodionov', 400, 4),
('Vladislav Kapustin', 200, 5),
('Колесниченко Дмитрий', 400, 6),
('Лебенков Дмитрий', 200, 7),
('Осипов Дмитрий ', 100, 8),
('Двинянинов Роман ', 200, 9),
('Дранков Александр', 400, 10),
('колян', 400, 10)

INSERT INTO color_team VALUES
('Red', 'White', 1),
('Red', 'Blue', 2),
('Blue', 'Yellow', 3),
('Black', 'White', 4),
('Red', 'White', 5),
('White', 'Blue', 6),
('White', 'Red', 7),
('Blue', 'Red', 8),
('Black', 'Red', 9),
('Green', 'Yellow', 10)

INSERT INTO club_coach VALUES
('Kestutis Kemzura', 10, 1),
('Dimitrios Itoudis', 100, 2),
('Rimas Kurtinaitis', 20, 3),
('Sergey Mokin', 30, 4),
('Sergei Bazarevich', 25, 5),
('Кобылинский Андрей', 30, 6),
('Михайлов Александр ', 120, 7),
('Осипов Дмитрий ', 200, 8),
('Двинянинов Роман ', 90, 9),
('Филин Олег Игоревич', 80, 10)

INSERT INTO club_players VALUES
(NULL, 'Cez', 50, 1),
('Stutz', 'USA', 70, 1),
('Burns', 'USA', 50, 1),
('Hruban', 'Cez', 40, 1),
('Svoboda', 'Cez', 50, 1),
('Raivo', 'Cez', 50, 1),

('Strebkov', 'Russia', 500, 2),
('Fridzon', 'Russia', 250, 2),
('Khryapa', 'Russia', 70, 2),
('Hines', 'USA', 70, 2),
('Weems', 'USA', 120, 2),

('Monia', 'Russia', 170, 3),
('Zakharov', 'Russia', 120, 3),
('Lauvergne', 'France', 80, 3),
('Pateev', 'Russia', 150, 3),
('Davis', 'USA', 60, 3),

('Downs', 'USA', 170, 4),
('Fortson', 'USA', 120, 4),
('Fesenko', 'Ukrain', 80, 4),
('Klimenko', 'Russia', 150, 4),
('Chappel', 'USA', 60, 4),

('Thomas', 'USA', 170, 5),
('Zuev', 'Russia', 120, 5),
('Kulagin', 'Russia', 80, 5),
('Wilson', 'Usa', 150, 5),
('Pushkov', 'Russia', 60, 5),

('Барсуков', 'Россия', 170, 6),
('Кобылинский', 'Россия', 120, 6),
('Кольцов', 'Россия', 80, 6),
('Кубанкин', 'Россия', 150, 6),
('Жуканенко', 'Россия', 60, 6),

('Баранов', 'Россия', 170, 7),
('Дедовец', 'Россия', 120, 7),
('Дубинин', 'Россия', 80, 7),
('Епанов', 'Россия', 150, 7),
('Зинченко', 'Россия', 60, 7),

('Алферов', 'Россия', 170, 8),
('Амелин', 'Россия', 120, 8),
('Зубов', 'Россия', 80, 8),
('Загнойко', 'Россия', 150, 8),
('Ильенко', 'Россия', 60, 8),

('Альмушев', 'Россия', 170, 9),
('Баранов', 'Россия', 120, 9),
('Кожетков', 'Россия', 80, 9),
('Макшев', 'Россия', 150, 9),
('Марченко', 'Россия', 60, 9),

('Пимков', 'Россия', 170, 10),
('Попов', 'Россия', 120, 10),
('Усенко', 'Россия', 80, 10),
('Глажкий', 'Россия', 150, 10),
('Сазонов', 'Россия', 60, 10)

INSERT INTO date_match VALUES
(14, 02, 2015, 13, 30),
(15, 02, 2015, 18, 00),
(14, 02, 2015, 16, 00),
(15, 02, 2015, 22, 00),
(21, 02, 2015, 14, 30),
(22, 02, 2015, 17, 00),
(21, 02, 2015, 20, 00),
(22, 02, 2015, 20, 00),
(17, 02, 2015, 20, 00),
(18, 02, 2015, 22, 30)

INSERT INTO schedule VALUES
(1, 2, 1, 1),
(3, 4, 2, 1),
(6, 7, 3, 2),
(8, 9, 4, 2),
(5, 1, 5, 1),
(2, 3, 6, 1),
(6, 9, 7, 2),
(8, 10, 8, 2),
(1, 7, 9, 1),
(8, 2, 10, 2)



SELECT * FROM dbo.club_players WHERE club_players.wage > 100 -- все игроки с зарплатой больше 100

SELECT name, color_team.guest_color AS guest, color_team.home_color AS home FROM dbo.basketball_club JOIN color_team ON basketball_club.id = color_team.id AND
(color_team.home_color = 'White' OR color_team.guest_color = 'White') -- клубы, у которых есть белая форма

SELECT * FROM basketball_club JOIN dbo.arena ON dbo.arena.id_basketbal_club = dbo.basketball_club.id AND
 dbo.arena.capacity BETWEEN 10000 AND 50000
 ORDER BY arena.capacity ASC -- все стадионы вместительности от 10000 до 50000

SELECT club_players.name, nation, club_players.wage
 FROM dbo.club_players WHERE club_players.id_basketball_club <= 5 AND club_players.nation = 'USA'
 ORDER BY  club_players.wage DESC, club_players.name ASC
-- сортировка игроков из ВТБ лиги из USA по именам и зарплате

DECLARE @a INT = 1;
WHILE @a < 11
   BEGIN;
          SELECT basketball_club.name, MAX(wage) AS MaxWage, MIN(wage) AS MinWage
          FROM   club_players INNER JOIN basketball_club ON
          club_players.id_basketball_club = @a AND basketball_club.id = @a
          GROUP BY
          basketball_club.name
          SET @a = @a + 1;
   END;
   -- Для каждого клуба выводит минимальную и максимальную зарплату игрока


SELECT name AS Player, nation AS 'National team'
FROM dbo.club_players
WHERE EXISTS(
        SELECT * FROM basketball_league
        WHERE basketball_league.name = 'VTB United League'
        )
 ORDER BY club_players.nation ASC
-- вывести два поля: игрок и сборная  для игроков выступающих в VTB United League

SELECT name FROM club_players WHERE name LIKE 'К%' -- все игроки на букву К

 SELECT TOP (3) club_coach.name, wage, basketball_club.name AS Club, city  FROM
  dbo.club_coach RIGHT JOIN basketball_club
  ON (basketball_club.country = 'Russia' OR basketball_club.country = 'Россия') AND
  basketball_club.id = club_coach.id_basketball_club ORDER BY wage DESC
-- топ-3 тренеров по зарплате в России

 SELECT date_day, date_month, date_year, date_hour, date_minute, club_home.name AS Home,
  club_guest.name AS Guest, club_home.city AS Place  FROM  date_match INNER JOIN
  schedule ON date_match.id = schedule.id_date INNER JOIN
  basketball_club AS club_home ON club_home.id = schedule.id_basketball_club_home INNER JOIN
  basketball_club AS club_guest ON club_guest.id = schedule.id_basketball_club_guest
  WHERE date_day > 1 OR date_month > 1 OR date_year >= 2015
 -- все матчи после 1 января


DECLARE @WageVTB INT, @WageHigh INT;
SELECT @WageHigh = AVG(wage) FROM club_coach WHERE club_coach.id_basketball_club < 6
SELECT @WageVTB = AVG(wage) FROM club_coach WHERE club_coach.id_basketball_club >= 6
IF @WageVTB > @WageHigh
        BEGIN;
                PRINT('Средняя зарплата в лиге ВТБ больше, чем в Высшей на ' +
                CAST(@WageVTB - @WageHigh AS nvarchar(10)) )
        END;
ELSE
        BEGIN;
                PRINT('Средняя зарплата в Высшей лиге больше, чем в лиге ВТБ на ' +
                 CAST(-@WageVTB + @WageHigh AS nvarchar(10)) )
        END;
 -- Сравнивание средней зарплаты тренеров в Англии и России

 SELECT club_coach.name AS Coach, basketball_club.name AS Club, city, club_budget, club_president.name AS President FROM club_coach
   INNER JOIN basketball_club ON dbo.basketball_club.id = club_coach.id_basketball_club INNER JOIN club_president
   ON club_president.id = basketball_club.id  ORDER BY club_budget DESC
   -- клуб + тренер + президент клуба, сортировка по бюджету
 GO


CREATE TRIGGER arena_change ON arena AFTER UPDATE
        AS
        BEGIN
        SET NOCOUNT ON;
        PRINT(N'Наибольшая арена');
        SELECT TOP(1) * FROM arena ORDER BY capacity DESC
        END;
GO

UPDATE arena SET capacity = 200000 WHERE arena.id = 1

SELECT * FROM transfer_market
DECLARE @x INT = 1;
SELECT TOP(1) @x += id FROM transfer_market ORDER BY id DESC
DECLARE @tran_name nvarchar(10) = 'tran';

BEGIN TRAN @tran_name
INSERT INTO transfer_market VALUES
(1, 3, 4, 13, 3);
GO

CREATE TRIGGER transfer_check ON transfer_market AFTER INSERT
        AS
        BEGIN
                SET NOCOUNT ON;
                DECLARE @pr INT = -1  , @bud INT = -1, @id_transfer INT;
                DECLARE @tran_name nvarchar(10) = 'tran';

                SELECT @pr = price FROM transfer_market WHERE transfer_market.id = @id_transfer
                SELECT @bud = club_budget FROM transfer_market
                JOIN basketball_club ON basketball_club.id = transfer_market.id_basketbal_club_to AND transfer_market.id = @id_transfer

                IF (@bud > @pr)
                        BEGIN
                                PRINT(N'Трансфер прошёл успешно!');
                        END
                ELSE
                        BEGIN;
                                PRINT(N'Трансфер невозможен, денег клуба-покупателя недостаточно!')
                                ROLLBACK TRAN @tran_name;
                        END;
        END;
GO


CREATE PROCEDURE Transfers
        @price INT,
        @club_from_id INT,
        @club_to_id INT,
        @player_id INT,
        @date_id INT
AS
        BEGIN
                IF((SELECT id_basketball_club FROM club_players WHERE id = @player_id) != @club_from_id)
                        PRINT(N'Трансфер невозможен! Игрок не принадлежит клубу!')
                ELSE
                        BEGIN
                                DECLARE @tran_name nvarchar(10) = 'tran';
                                BEGIN
                                        TRAN @tran_name
                                                INSERT INTO transfer_market VALUES
                                                (@price,@club_from_id,@club_to_id,@player_id,@date_id)
                                                COMMIT TRANSACTION;
                                                UPDATE basketball_club SET club_budget = club_budget + @price WHERE id = @club_from_id;
                                                UPDATE basketball_club SET club_budget = club_budget - @price WHERE id = @club_to_id;
                                                UPDATE club_players SET id_basketball_club = @club_to_id WHERE id = @player_id;
                        END;
        END;
GO


EXECUTE Transfers 10000, 1, 2, 4, 3;
SELECT * FROM basketball_club
SELECT * FROM transfer_market
SELECT * FROM club_players
GO


CREATE PROCEDURE add_coach
        @wage INT,
        @name nvarchar(100),
        @id_in_club INT
AS
    BEGIN
                DECLARE @avg_wage INT, @budget INT, @old_wage INT ;
                SELECT @old_wage = wage FROM club_coach WHERE id = @id_in_club;
                SELECT @avg_wage = AVG(wage), @budget = club_budget FROM
                        club_coach JOIN basketball_club ON
                        basketball_club.id = club_coach.id_basketball_club WHERE club_coach.id = @id_in_club GROUP BY club_budget

                        IF (@wage > @budget / 2)
                                BEGIN
                                        PRINT(N'Невозможно принять тренера!');
                                END
                        ELSE
                                IF (@wage <= @avg_wage * 2)
                                        BEGIN
                                                PRINT(N'Тренер принят на работу!');
                                                INSERT INTO club_coach VALUES (@name, @wage, @id_in_club)
                                        END
                                ELSE
                                        BEGIN
                                                PRINT(N'Принят со средней зараплатой по клубу!');
                                                INSERT INTO club_coach VALUES (@name, @avg_wage, @id_in_club)
                                        END
        END;
GO


EXECUTE  add_coach 500, 'CHADIN', 4;
GO

SELECT * FROM club_coach


CREATE VIEW people_in_club (People, Club)
AS
        SELECT 'Coach - ' + club_coach.name  AS People, basketball_club.name AS Club FROM club_coach
    INNER JOIN basketball_club ON basketball_club.id = club_coach.id_basketball_club
        UNION
        SELECT 'President - ' + club_president.name  AS People, basketball_club.name AS Club FROM club_president
    INNER JOIN basketball_club ON basketball_club.id = club_president.id_basketball_club
        UNION
        SELECT 'Player - ' + club_players.name  AS People, basketball_club.name AS Club FROM
         basketball_club JOIN club_players ON club_players.id_basketball_club = basketball_club.id
GO

SELECT * FROM people_in_club WHERE people_in_club.Club = 'Avtodor'
GO


CREATE VIEW russian_club
AS
        SELECT basketball_club.name AS name, club_coach.name AS coach, club_president.name AS president
        FROM  basketball_club JOIN  club_coach ON basketball_club.id = club_coach.id_basketball_club
        JOIN  club_president ON club_president.id_basketball_club = basketball_club.id AND basketball_club.country = 'Russia'
GO

SELECT * FROM russian_club
GO

SELECT * FROM basketball_club LEFT OUTER JOIN club_president ON basketball_club.id = club_president.id_basketball_club


SELECT * FROM basketball_club RIGHT OUTER JOIN club_president ON basketball_club.id = club_president.id_basketball_club


SELECT * FROM basketball_club FULL OUTER JOIN club_president ON basketball_club.id = club_president.id_basketball_club

SELECT club_coach.name FROM club_coach WHERE EXISTS (SELECT * FROM basketball_club WHERE club_coach.wage > 50)

SELECT club_coach.name, club_coach.wage FROM club_coach WHERE club_coach.wage IN (SELECT club_coach.wage FROM basketball_club WHERE club_coach.wage > 50)

SELECT club_coach.name, club_coach.wage FROM club_coach WHERE 100 = ANY (SELECT club_coach.wage FROM club_coach)

SELECT club_coach.name, club_coach.wage FROM club_coach WHERE club_coach.wage >=  ALL (SELECT club_coach.wage FROM club_coach)

SELECT club_players.name, club_players.wage FROM club_players WHERE club_players.wage BETWEEN 0 AND 50

SELECT * FROM club_coach WHERE club_coach.name LIKE 's%';

SELECT   club_players.name, club_players.wage, verdict =
      CASE
         WHEN club_players.wage = 0 THEN 'very little'
         WHEN club_players.wage < 10  THEN 'little'
         WHEN club_players.wage >= 10 AND club_players.wage < 50 THEN 'normal'
         WHEN club_players.wage > 50 AND club_players.wage < 100 THEN 'many'
         ELSE 'lots of'
      END
FROM club_players;

SELECT CAST(club_players.wage AS MONEY) / 12 FROM club_players WHERE club_players.wage > 100

SELECT CONVERT(MONEY, club_players.wage) / 12 FROM club_players WHERE club_players.wage < 100

SELECT ISNULL(CONVERT(MONEY, club_players.wage) - 50, 100) FROM club_players

SELECT NULLIF(club_players.wage, club_coach.wage) FROM club_players, club_coach

SELECT COALESCE(name, nation) FROM club_players

SELECT CHOOSE ( 3, '1', '2', '3', '4' )

SELECT club_players.name, club_coach.name, IIF ( club_coach.wage > club_players.wage, 'coach win', 'players win' ) AS RESULT FROM club_players, club_coach

SELECT club_players.name, REPLACE (club_players.name, 'М', 'м') AS RESULT FROM club_players

SELECT club_players.name, SUBSTRING (club_players.name, 1, 4) AS RESULT FROM club_players

SELECT club_players.name, STUFF (club_players.name, 1, 2, 'zz') AS RESULT FROM club_players

SELECT club_players.wage, STUFF(STR (club_players.wage), 10, 2, '000') AS RESULT FROM club_players

DECLARE @pos INT = 1, @s NCHAR(20);
SET @s = 'asdasdsadsa';
WHILE @pos <=  DATALENGTH(@s)
        BEGIN;
        SELECT @pos,
       SUBSTRING(@s, @pos, 1),
       UNICODE(SUBSTRING(@s, @pos, 1));
    SELECT @pos = @pos + 1;
    END;


SELECT LOWER(club_players.name) AS LOWER, UPPER(club_players.name) AS UPPER FROM club_players;

SELECT DATEPART(YEAR,'07.05.2015')

SELECT DATEADD(YEAR, 1, '07.05.2015');

SELECT DATEDIFF(MONTH, '07.05.2015', '07.05.2016');

SELECT GETDATE();

SELECT SYSDATETIMEOFFSET();

SELECT name FROM dbo.basketball_league  GROUP BY basketball_league.name

SELECT wage FROM club_players GROUP BY wage HAVING club_players.wage > 100

SELECT club_players.name, club_players.wage FROM club_players
WHERE club_players.wage = (SELECT MAX(club_players.wage) FROM club_players);  --автономный подзапрос

SELECT name, wage FROM club_players WHERE wage IN
(SELECT wage FROM club_players WHERE club_players.wage = 120)  -- коррелированный подзапрос

SELECT basketball_club.name, club_players.name
FROM basketball_club JOIN club_players ON basketball_club.id = club_players.id_basketball_club
WHERE club_players.name IN ( SELECT name FROM club_players WHERE id_in_basketball_league = 2) --набор

CREATE TABLE #Temp_Table (
            id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
            player VARCHAR (100),
            club VARCHAR(100),
            nation VARCHAR(100)
        )

INSERT INTO #Temp_Table VALUES
        ('James', 'Heat', 'America'),
        ('Downs', 'Avtodor', 'America'),
        ('Koponen', 'Khimki', 'Russia')

MERGE #Temp_Table AS TT
        USING (SELECT club_players.name AS player, basketball_club.name AS club, club_players.nation AS nation
         FROM club_players JOIN basketball_club
        ON club_players.id_basketball_club = basketball_club.id ) AS New_TT ON
        TT.club COLLATE DATABASE_DEFAULT = New_TT.club COLLATE DATABASE_DEFAULT AND TT.player COLLATE DATABASE_DEFAULT = New_TT.player COLLATE DATABASE_DEFAULT
         AND TT.nation COLLATE DATABASE_DEFAULT = New_TT.nation COLLATE DATABASE_DEFAULT
        WHEN NOT MATCHED THEN
            INSERT (player, club, nation) VALUES
            (New_TT.player, New_TT.club, New_TT.nation );

SELECT * FROM #Temp_Table ;-- merge и создание временной таблицы



WITH Temp (player, nation, coach) AS
         ( SELECT club_players.name AS player, club_players.nation, club_coach.name FROM club_players JOIN club_coach
         ON club_players.id_basketball_club = club_coach.id_basketball_club JOIN basketball_club ON basketball_club.id = club_players.id_basketball_club
          WHERE basketball_club.id = 2 )

SELECT * FROM Temp WHERE nation = 'Russia' --обобщенные выражения



SELECT 'Coach ' + club_coach.name  AS Team, basketball_club.name AS Club FROM club_coach
    INNER JOIN basketball_club ON basketball_club.id = club_coach.id_basketball_club AND basketball_club.id = 1 INNER JOIN club_players ON
    club_players.id_basketball_club = basketball_club.id
    UNION ALL
SELECT 'Player ' + club_players.name  AS Team, basketball_club.name AS Club FROM club_coach
    INNER JOIN basketball_club ON basketball_club.id = club_coach.id_basketball_club AND basketball_club.id = 1 INNER JOIN club_players ON
    club_players.id_basketball_club = basketball_club.id
    -- Union All

SELECT 'Coach ' + club_coach.name  AS Team, basketball_club.name AS Club FROM club_coach
    INNER JOIN basketball_club ON basketball_club.id = club_coach.id_basketball_club AND basketball_club.id = 1 INNER JOIN club_players ON
    club_players.id_basketball_club = basketball_club.id
    INTERSECT
SELECT 'Player ' + club_players.name  AS Team, basketball_club.name AS Club FROM club_coach
    INNER JOIN basketball_club ON basketball_club.id = club_coach.id_basketball_club AND basketball_club.id = 1 INNER JOIN club_players ON
    club_players.id_basketball_club = basketball_club.id
    -- Intersect

SELECT 'Coach ' + club_coach.name  AS Team, basketball_club.name AS Club FROM club_coach
    INNER JOIN basketball_club ON basketball_club.id = club_coach.id_basketball_club AND basketball_club.id = 1 INNER JOIN club_players ON
    club_players.id_basketball_club = basketball_club.id
    EXCEPT
SELECT 'Player ' + club_players.name  AS Team, basketball_club.name AS Club FROM club_coach
    INNER JOIN basketball_club ON basketball_club.id = club_coach.id_basketball_club AND basketball_club.id = 1 INNER JOIN club_players ON
    club_players.id_basketball_club = basketball_club.id
    -- Except

SELECT Club, club_players, Number_players

FROM (
SELECT t AS 'Club',  [Russia], [Cez], [France], [USA]
     FROM (SELECT club_players.name AS n, club_players.nation AS nat, (club_players.id_basketball_club) AS id, basketball_club.name AS t
     FROM club_players INNER JOIN basketball_club ON basketball_club.id = club_players.id_basketball_club
     ) AS T
    PIVOT
    (
         COUNT(T.n)  FOR T.nat IN ([Russia], [Cez], [France], [USA])
    ) AS S

    ) AS A
    UNPIVOT
    (
         Number_players  FOR club_players IN (Russia, Cez, France, USA)
    ) AS B
    -- Pivot, Unpivot


SELECT club_players.name AS Player, club_coach.name AS Coach, basketball_club.name AS Club,
 SUM(club_players.wage) AS wage FROM club_players  JOIN basketball_club
ON basketball_club.id = club_players.id_basketball_club JOIN club_coach ON
club_coach.id_basketball_club = basketball_club.id
GROUP BY ROLLUP ( club_players.name, club_coach.name , basketball_club.name)

SELECT club_players.name AS Player, club_coach.name AS Coach, basketball_club.name AS Club,
 SUM(club_players.wage) AS wage FROM club_players JOIN basketball_club
ON basketball_club.id = club_players.id_basketball_club JOIN club_coach ON
club_coach.id_basketball_club = basketball_club.id
GROUP BY CUBE (club_players.name, club_coach.name, basketball_club.name)

SELECT club_players.name AS Player, club_coach.name AS Coach, basketball_club.name AS Club,
 SUM(club_players.wage) AS wage FROM club_players JOIN basketball_club
ON basketball_club.id = club_players.id_basketball_club JOIN club_coach ON
club_coach.id_basketball_club = basketball_club.id
GROUP BY GROUPING SETS ((club_players.name, club_coach.name), basketball_club.name)
--GROUP

SELECT ROW_NUMBER() OVER ( ORDER BY wage DESC) AS 'ROW_NUMBER',
RANK() OVER (ORDER BY wage DESC) AS 'RANK', DENSE_RANK() OVER ( ORDER BY wage DESC) AS 'DENSE_RANK',
NTILE(5) OVER ( ORDER BY wage DESC) AS 'NTILE', NTILE(5) OVER (PARTITION BY nation ORDER BY wage DESC) AS 'NTILE partition',
 name, nation, wage FROM club_players ORDER BY 'ROW_NUMBER'
--Ранжирующие оконные функции


SELECT basketball_club.name, MAX(wage) AS 'Max', MIN(wage) AS 'Min', SUM(wage) AS 'Sum',
AVG(wage) AS 'AVG', COUNT(wage) AS 'Count', CHECKSUM_AGG(wage) AS 'CHECKSUM_AGG',
ROWCOUNT_BIG() AS 'ROWCOUNT_BIG'  FROM
club_players INNER JOIN basketball_club ON
club_players.id_basketball_club = basketball_club.id  GROUP BY basketball_club.name
--Статистические оконные функции

SELECT TOP(20) name, wage, Lead(name) OVER(ORDER BY wage) AS 'Lead', LAG(name)  OVER(ORDER BY wage) AS 'Lag' FROM club_players
--Lag and lead


BEGIN TRY
    SELECT SQRT(-322);
END TRY
BEGIN CATCH
    SELECT
       ERROR_NUMBER() AS 'Номер ошибки'
       ,ERROR_SEVERITY() AS 'Cтепень серьезности ошибки'
       ,ERROR_STATE() AS 'Код состояния ошибки'
       ,ERROR_PROCEDURE() AS 'Имя хранимой процедуры'
       ,ERROR_LINE() AS 'Номер строки'
       ,ERROR_MESSAGE() AS 'Полный текст сообщения об ошибке';
END CATCH;
-- Try/Catch and Error

SET XACT_ABORT OFF;

BEGIN TRANSACTION;
BEGIN TRY
    DELETE FROM basketball_club WHERE basketball_club.id = 1
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT
       ERROR_NUMBER() AS 'Номер ошибки'
       ,ERROR_SEVERITY() AS 'Cтепень серьезности ошибки'
       ,ERROR_STATE() AS 'Код состояния ошибки'
       ,ERROR_PROCEDURE() AS 'Имя хранимой процедуры'
       ,ERROR_LINE() AS 'Номер строки'
       ,ERROR_MESSAGE() AS 'Полный текст сообщения об ошибке';
   IF (XACT_STATE()) = -1
   BEGIN
       PRINT
           N'Транзакция прошла с ошибкой'
       ROLLBACK TRANSACTION;
   END;
   IF (XACT_STATE()) = 1
   BEGIN
       PRINT
           N'Текущий запрос содержит активную пользовательскую транзакцию. Зафиксируем транзакцию'
       COMMIT TRANSACTION;
   END;
    THROW;

END CATCH;