public static void ExecuteTransaction1()
        {
            var command =
    "SELECT te.team_id, te.team_name, te.description, SUM(bu.amount) AS team_budget, json_agg(CONCAT(us.first_name, ' ', us.nick, ' ', us.last_name)) as users_in_team, " +
    "sc.score_amount AS team_score, COUNT(ta.task_id) AS tasks_amount FROM teams te " +
    "INNER JOIN users us ON us.team_id = te.team_id " +
    "INNER JOIN tasks ta ON ta.user_id = us.user_id " +
    "INNER JOIN budget bu ON bu.budget_id = ta.budget_id " +
    "INNER JOIN scores sc ON sc.team_id = te.team_id " +
    "WHERE LOWER(te.team_name) LIKE '%a%' " +
    "GROUP BY te.team_id, te.team_name, te.description, sc.score_amount " +
    "ORDER BY sc.score_amount";
        }

        /// <summary>
        /// ExecuteTransaction2
        /// </summary>
        public static void ExecuteTransaction2()
        {
            var command =
    "SELECT pr.project_id, pr.project_name, pr.project_tag, json_agg(ta.task_name), COUNT(ta.task_id) as tasks_amount, (pr.deadline_date - current_date) AS days_left, " +
    "pr.deadline_date, current_date , pr.modification_date " +
    "FROM projects pr " +
    "JOIN tasks ta on ta.project_id = pr.project_id " +
    "WHERE pr.create_date BETWEEN '01.01.2000' AND '01.01.2019' " +
    "AND pr.deadline_date BETWEEN '01.01.2000' AND '01.01.2019' " +
    "AND pr.project_name LIKE '%File%' " +
    "GROUP BY pr.project_id, pr.project_name, pr.project_tag " +
    "ORDER BY pr.modification_date";
        }

        /// <summary>
        /// ExecuteTransaction3
        /// </summary>
        public static void ExecuteTransaction3()
        {
            int worstUserId = 0, worstTeamId = 0;
            int bestUserId = 0, bestTeamId = 0;
            string messagesIds = "";
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();
            var transaction = conn.BeginTransaction();
            //----------------------------------------------------------------------------------------
            var command =
                "SELECT us.user_id, us.team_id, sc.score_amount, COUNT(ta.task_id) AS tasks_amount FROM users us " +
                "INNER JOIN tasks ta ON ta.user_id = us.user_id " +
                "INNER JOIN scores sc ON sc.team_id = us.team_id " +
                "GROUP by us.user_id, sc.score_amount " +
                "ORDER BY tasks_amount " +
                "LIMIT 1 ";

            NpgsqlCommand npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            var reader = npgsqlCommmand.ExecuteReader();
            while (reader.Read())
            {
                worstUserId = reader.GetInt32(0);
                worstTeamId = reader.GetInt32(1);
            }
            reader.Close();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "" +
            "SELECT us.user_id, us.team_id, sc.score_amount, COUNT(ta.task_id) AS tasks_amount FROM users us " +
            "INNER JOIN tasks ta ON ta.user_id = us.user_id " +
            "INNER JOIN scores sc ON sc.team_id = us.team_id " +
            "GROUP by us.user_id, sc.score_amount " +
            "ORDER BY tasks_amount DESC " +
            "LIMIT 1 ";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            reader = npgsqlCommmand.ExecuteReader();
            while (reader.Read())
            {
                bestUserId = reader.GetInt32(0);
                bestTeamId = reader.GetInt32(1);
            }
            reader.Close();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "UPDATE users set first_name = 'edited', last_name = 'edited', nick = 'edited', modification_date = NOW() WHERE user_id = @user_id";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            npgsqlCommmand.Parameters.AddWithValue("user_id", worstUserId);
            npgsqlCommmand.ExecuteScalar();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "UPDATE scores set score_amount = (score_amount / 2) WHERE team_id = @team_id";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            npgsqlCommmand.Parameters.AddWithValue("team_id", worstTeamId);
            npgsqlCommmand.ExecuteScalar();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "UPDATE tasks SET user_id = NULL where user_id = @user_id RETURNING message_id";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            npgsqlCommmand.Parameters.AddWithValue("user_id", worstUserId);
            reader = npgsqlCommmand.ExecuteReader();
            while (reader.Read())
            {
                messagesIds += reader.GetInt32(0) + ",";
            }
            messagesIds = messagesIds.Remove(messagesIds.Length - 1);
            reader.Close();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "UPDATE messages set text = 'This task has been unpined' WHERE message_id IN ({0})";
            npgsqlCommmand = new NpgsqlCommand(String.Format(command, messagesIds), conn, transaction);
            npgsqlCommmand.ExecuteScalar();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "UPDATE scores set score_amount = (score_amount * 2) WHERE team_id = @team_id";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            npgsqlCommmand.Parameters.AddWithValue("team_id", bestTeamId);
            npgsqlCommmand.ExecuteScalar();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            transaction.Rollback();
            conn.Close();
        }

        /// <summary>
        /// ExecuteTransaction4
        /// </summary>
        public static void ExecuteTransaction4(int projectId)
        {
            string tasksIds = "", messagesIds = "", budgetIds = "";
            double extraBudget = 0;
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();
            var transaction = conn.BeginTransaction();
            //----------------------------------------------------------------------------------------
            var command = "DELETE FROM projects WHERE project_id = @project_id";
            NpgsqlCommand npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            npgsqlCommmand.Parameters.AddWithValue("project_id", projectId);
            npgsqlCommmand.ExecuteScalar();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "DELETE FROM tasks WHERE project_id = @project_id RETURNING task_id, message_id, budget_id";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            var reader = npgsqlCommmand.ExecuteReader();
            while (reader.Read())
            {
                tasksIds += reader.GetInt32(0) + ",";
                messagesIds += reader.GetInt32(1) + ",";
                budgetIds += reader.GetInt32(2) + ",";
            }
            tasksIds = tasksIds.Remove(tasksIds.Length - 1);
            messagesIds = messagesIds.Remove(messagesIds.Length - 1);
            budgetIds = budgetIds.Remove(budgetIds.Length - 1);
            reader.Close();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "DELETE FROM messages WHERE message_id IN ({0})";
            npgsqlCommmand = new NpgsqlCommand(String.Format(command, messagesIds), conn, transaction);
            npgsqlCommmand.ExecuteScalar();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "SELECT  ((SELECT budget_sum FROM " +
                "(SELECT SUM(amount) AS budget_sum FROM budget bu INNER JOIN tasks ta ON ta.budget_id = bu.budget_id WHERE task_id in ({0}) ) budget_sum) / " +
                "(SELECT tasks_amount FROM(SELECT COUNT(task_id) AS tasks_amount FROM tasks) tasks_amount) ) AS extra_budget";
            npgsqlCommmand = new NpgsqlCommand(String.Format(command, tasksIds), conn, transaction);
            reader = npgsqlCommmand.ExecuteReader();
            while (reader.Read())
            {
                extraBudget = 2.71;
            }
            reader.Close();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "UPDATE budget SET amount = amount + @extra_budget";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            npgsqlCommmand.Parameters.AddWithValue("extra_budget", extraBudget);
            npgsqlCommmand.ExecuteScalar();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "DELETE FROM budget WHERE budget_id IN ({0})";
            npgsqlCommmand = new NpgsqlCommand(String.Format(command, budgetIds), conn, transaction);
            npgsqlCommmand.ExecuteScalar();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            transaction.Rollback();
            conn.Close();
        }

        /// <summary>
        /// ExecuteTransaction5
        /// </summary>
        public static void ExecuteTransaction5()
        {
            int roleId1 = 0, roleId2 = 0, roleId3 = 0;
            int userId1 = 0, userId2 = 0, userId3 = 0;
            int teamId = 0;
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();
            var transaction = conn.BeginTransaction();
            //----------------------------------------------------------------------------------------
            var command = "INSERT INTO teams(team_name, description, leader_id) VALUES('new_team', 'new_team description', 0) RETURNING team_id";

            NpgsqlCommand npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            var reader = npgsqlCommmand.ExecuteReader();
            while (reader.Read())
            {
                teamId = reader.GetInt32(0);
            }
            reader.Close();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "INSERT INTO roles(role_name) VALUES('user1_role') RETURNING role_id;";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            reader = npgsqlCommmand.ExecuteReader();
            while (reader.Read())
            {
                roleId1 = reader.GetInt32(0);
            }
            reader.Close();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "INSERT INTO users(first_name, last_name, nick, role_id, team_id, create_date, modification_date, is_active) " +
                "VALUES('user1_first_name', 'user1_last_name', 'user1_nick', @role_id, @team_id, NOW(), NOW(), true) RETURNING user_id; ";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            npgsqlCommmand.Parameters.AddWithValue("role_id", roleId1);
            npgsqlCommmand.Parameters.AddWithValue("team_id", teamId);
            reader = npgsqlCommmand.ExecuteReader();
            while (reader.Read())
            {
                userId1 = reader.GetInt32(0);
            }
            reader.Close();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "INSERT INTO roles(role_name) VALUES('user2_role') RETURNING role_id;";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            reader = npgsqlCommmand.ExecuteReader();
            while (reader.Read())
            {
                roleId2 = reader.GetInt32(0);
            }
            reader.Close();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "INSERT INTO users(first_name, last_name, nick, role_id, team_id, create_date, modification_date, is_active) " +
                "VALUES('user2_first_name', 'user2_last_name', 'user2_nick', @role_id, @team_id, NOW(), NOW(), true) RETURNING user_id; ";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            npgsqlCommmand.Parameters.AddWithValue("role_id", roleId2);
            npgsqlCommmand.Parameters.AddWithValue("team_id", teamId);
            reader = npgsqlCommmand.ExecuteReader();
            while (reader.Read())
            {
                userId2 = reader.GetInt32(0);
            }
            reader.Close();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "INSERT INTO roles(role_name) VALUES('user3_role') RETURNING role_id; ";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            reader = npgsqlCommmand.ExecuteReader();
            while (reader.Read())
            {
                roleId3 = reader.GetInt32(0);
            }
            reader.Close();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "INSERT INTO users(first_name, last_name, nick, role_id, team_id, create_date, modification_date, is_active) " +
                "VALUES('user3_first_name', 'user3_last_name', 'user3_nick', @role_id, @team_id, NOW(), NOW(), true) RETURNING user_id; ";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            npgsqlCommmand.Parameters.AddWithValue("role_id", roleId3);
            npgsqlCommmand.Parameters.AddWithValue("team_id", teamId);
            reader = npgsqlCommmand.ExecuteReader();
            while (reader.Read())
            {
                userId3 = reader.GetInt32(0);
            }
            reader.Close();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "UPDATE teams SET leader_id = @user_id WHERE team_id = @team_id";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            npgsqlCommmand.Parameters.AddWithValue("user_id", userId1);
            npgsqlCommmand.Parameters.AddWithValue("team_id", teamId);
            npgsqlCommmand.ExecuteScalar();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            command = "INSERT INTO scores(team_id, score_amount, score_mark) VALUES(@team_id, 0.5, 'A');";
            npgsqlCommmand = new NpgsqlCommand(command, conn, transaction);
            npgsqlCommmand.Parameters.AddWithValue("team_id", teamId);
            npgsqlCommmand.ExecuteScalar();
            npgsqlCommmand.Dispose();
            //----------------------------------------------------------------------------------------
            transaction.Rollback();
            conn.Close();
        }