SQL_lab_4_2024.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": [
        "cahsIYPgWbbc"
      ],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/vn322/RUDN_Lab_Works/blob/main/SQL_lab_4_2024.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **Оператор UNION объединения нескольких таблиц**"
      ],
      "metadata": {
        "id": "N8SdbDpwKHG2"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Предположим, у нас имеются две таблицы с одинаковой структурой:\n",
        "\n",
        "tab1 поля: score- числовое, from - строка указывющая что это поле из tab1\n",
        "\n",
        "100 tab1\n",
        "\n",
        "200 tab1\n",
        "\n",
        "300 tab1\n",
        "\n",
        "tab2 поля:\n",
        "score- числовое, from - строка указывющая что это поле из tab2\n",
        "\n",
        "200 tab2\n",
        "\n",
        "300 tab2\n",
        "\n",
        "400 tab2\n",
        "\n",
        "Все эти записи можно объединить в один сводный отчет с помощью оператора UNION:\n",
        "(помнитм про from в обратных кавычках, потому что в colab в текстовом поле они не отбражаются, как вот в следующем запросе, но они там есть!)\n",
        "\n"
      ],
      "metadata": {
        "id": "dNZ0nqKAMTIH"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "SELECT score, `from` FROM tab1 UNION SELECT val, type FROM tab2"
      ],
      "metadata": {
        "id": "W6x74onWI4PN"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "поле `from` в обратных одинарных кавычках, чтобы указать, что это не оператор FROM, а поле с именем from\n",
        "\n",
        "**SELECT score FROM tab1 UNION SELECT val FROM tab2**\n",
        "\n",
        " UNION оставляет только уникальные значения записей\n",
        "\n",
        "(100  (200  300)  400)"
      ],
      "metadata": {
        "id": "uZaRr-ZyJACU"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "Даже если в первую таблицу добавить еще одну строку со значением 100:\n",
        "\n",
        "100   tab1\n",
        "\n",
        "**SELECT score FROM tab1**\n",
        "\n",
        "у первой таблицы во всех полях from укажем имя tab2 (помнитм про from в обратных ковычках):\n"
      ],
      "metadata": {
        "id": "Wl-BfQFyQIfR"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "UPDATE tab1 SET `from` = 'tab2'"
      ],
      "metadata": {
        "id": "rlIodqW2JSC2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Вернем записям первой таблицы значения tab1(помнитм про from в обратных ковычках):\n",
        "\n",
        "**UPDATE tab1 SET `from` = 'tab1'**\n",
        "\n"
      ],
      "metadata": {
        "id": "w1RLxTSdQQ1b"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "UPDATE tab1 SET `from` = 'tab1'"
      ],
      "metadata": {
        "id": "lk9cLQr5JYtg"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "и выполним\n",
        "\n",
        "**SELECT score, 'table 1' as tbl FROM tab1 UNION**\n",
        "\n",
        " **SELECT val, 'table 2' FROM tab2**"
      ],
      "metadata": {
        "id": "YYtxXeZJJeVf"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Здесь явно указали значение второго поля как 'table 1' для первой таблицы и 'table 2' – для второй. И чтобы СУБД «знала» какое имя присвоить второму столбцу, оно указано в виде алиаса tbl"
      ],
      "metadata": {
        "id": "yQ0kRcSVQYXX"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Также можно добавить сортировку сводной таблицы, например, по полю score:\n",
        "\n",
        "SELECT score, 'table 1' as tbl FROM tab1\n",
        "UNION SELECT val, 'table 2' FROM tab2\n",
        "ORDER BY score DESC"
      ],
      "metadata": {
        "id": "kqeYb_dHQdZe"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Или, добавить фильтр и ограничение максимального числа записей:\n"
      ],
      "metadata": {
        "id": "mPUJBELRQi6O"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "SELECT score, 'table 1' as tbl FROM tab1 WHERE score IN(300, 400)\n",
        "UNION SELECT val, 'table 2' FROM tab2\n",
        "ORDER BY score DESC\n",
        "LIMIT 3"
      ],
      "metadata": {
        "id": "vW456MivJ2v4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "# **Вложенные SQL-запросы**"
      ],
      "metadata": {
        "id": "HOSrwoKhTt_I"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## **Задание 1**\n",
        "\n",
        "Создайте таблицу с именем students и полями id name sex old и marks с полями id (id студента из таблицы students), subject, mark.\n",
        "Какие типы полей надо выбрать? какие должны быть дипазоны значений?\n",
        "\n",
        "Заполните их для 4-5 студентов и предметов Си Химия ДифУры"
      ],
      "metadata": {
        "id": "hQKdm6ZLTx89"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "\n",
        "try:\n",
        "    sqlite_connection = sqlite3.connect('uni.db')\n",
        "    cursor = sqlite_connection.cursor()\n",
        "    print(\"База данных создана и успешно подключена к SQLite\")\n",
        "\n",
        "    sqlite_select_query = \"select sqlite_version();\" #создается запрос для получения версии базы данны\n",
        "    cursor.execute(sqlite_select_query)\n",
        "    record = cursor.fetchall()\n",
        "    print(\"Версия базы данных SQLite: \", record)\n",
        "    cursor.close()\n",
        "\n",
        "except sqlite3.Error as error:\n",
        "    print(\"Ошибка при подключении к sqlite\", error)\n",
        "finally:\n",
        "    if (sqlite_connection):\n",
        "        sqlite_connection.close()\n",
        "        print(\"Соединение с SQLite закрыто\")"
      ],
      "metadata": {
        "id": "FY_W2MqiH50S",
        "outputId": "8905d712-92c3-4e1e-e42a-6f98f94bdf07",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": 69,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "База данных создана и успешно подключена к SQLite\n",
            "Версия базы данных SQLite:  [('3.37.2',)]\n",
            "Соединение с SQLite закрыто\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "try:\n",
        "    sqlite_connection = sqlite3.connect('uni.db')\n",
        "    sqlite_create_table_query = '''CREATE TABLE students (\n",
        "                                id INTEGER PRIMARY KEY,\n",
        "                                name TEXT,\n",
        "                                sex INTEGER,\n",
        "                                old INTEGER\n",
        "                                );'''\n",
        "\n",
        "    cursor = sqlite_connection.cursor()\n",
        "    print(\"База данных подключена к SQLite\")\n",
        "    cursor.execute(sqlite_create_table_query)\n",
        "    sqlite_connection.commit()\n",
        "    print(\"Таблица SQLite создана\")\n",
        "\n",
        "    cursor.close()\n",
        "\n",
        "except sqlite3.Error as error:\n",
        "    print(\"Ошибка при подключении к sqlite\", error)\n",
        "finally:\n",
        "    if (sqlite_connection):\n",
        "        sqlite_connection.close()\n",
        "        print(\"Соединение с SQLite закрыто\")"
      ],
      "metadata": {
        "id": "QCAuczIbIDL1",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c7840499-9128-4bbd-b68c-2cc884811fcd"
      },
      "execution_count": 70,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "База данных подключена к SQLite\n",
            "Таблица SQLite создана\n",
            "Соединение с SQLite закрыто\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "try:\n",
        "    sqlite_connection = sqlite3.connect('uni.db')\n",
        "    sqlite_create_table_query = '''CREATE TABLE marks (\n",
        "                                id INTEGER,\n",
        "                                subject TEXT,\n",
        "                                mark INTEGER\n",
        "                                );'''\n",
        "\n",
        "    cursor = sqlite_connection.cursor()\n",
        "    print(\"База данных подключена к SQLite\")\n",
        "    cursor.execute(sqlite_create_table_query)\n",
        "    sqlite_connection.commit()\n",
        "    print(\"Таблица SQLite создана\")\n",
        "\n",
        "    cursor.close()\n",
        "\n",
        "except sqlite3.Error as error:\n",
        "    print(\"Ошибка при подключении к sqlite\", error)\n",
        "finally:\n",
        "    if (sqlite_connection):\n",
        "        sqlite_connection.close()\n",
        "        print(\"Соединение с SQLite закрыто\")"
      ],
      "metadata": {
        "id": "-lCI-N3iblmY",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "57351c2a-11f7-4a00-b056-7f56e88f838a"
      },
      "execution_count": 71,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "База данных подключена к SQLite\n",
            "Таблица SQLite создана\n",
            "Соединение с SQLite закрыто\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "import traceback\n",
        "import sys\n",
        "\n",
        "try:\n",
        "    sqlite_connection = sqlite3.connect('uni.db')\n",
        "    cursor = sqlite_connection.cursor()\n",
        "    print(\"База данных подключена к SQLite\")\n",
        "\n",
        "    sqlite_insert_query = \"\"\"INSERT INTO students (id, name, sex, old )\n",
        "                            VALUES (1, 'Аnri', 1, 22),\n",
        "                                    (2, 'Serg', 1, 20),\n",
        "                                    (3, 'Linda', 2, 19),\n",
        "                                    (4, 'Anne', 2, 19),\n",
        "                                    (5, 'Fred', 1, 21)\"\"\"\n",
        "\n",
        "    count = cursor.execute(sqlite_insert_query)\n",
        "    sqlite_connection.commit()\n",
        "    print(\"Запись успешно вставлена ​​в таблицу sqlitedb_developers \", cursor.rowcount)\n",
        "    cursor.close()\n",
        "\n",
        "except sqlite3.Error as error:\n",
        "    print(\"Не удалось вставить данные в таблицу sqlite\")\n",
        "    print(\"Класс исключения: \", error.__class__)\n",
        "    print(\"Исключение\", error.args)\n",
        "    print(\"Печать подробноcтей исключения SQLite: \")\n",
        "    exc_type, exc_value, exc_tb = sys.exc_info()\n",
        "    print(traceback.format_exception(exc_type, exc_value, exc_tb))\n",
        "finally:\n",
        "    if (sqlite_connection):\n",
        "        sqlite_connection.close()\n",
        "        print(\"Соединение с SQLite закрыто\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "32d05084-41a7-4548-b0c2-37f084d8d0db",
        "id": "gGPsfm8ng428"
      },
      "execution_count": 72,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "База данных подключена к SQLite\n",
            "Запись успешно вставлена ​​в таблицу sqlitedb_developers  5\n",
            "Соединение с SQLite закрыто\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "# Устанавливаем соединение с базой данных\n",
        "connection = sqlite3.connect('uni.db')\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# Выбираем всех\n",
        "cursor.execute('SELECT * FROM students')\n",
        "users = cursor.fetchall()\n",
        "\n",
        "# Выводим результаты\n",
        "for name in users:\n",
        "  print(name)\n",
        "\n",
        "# Закрываем соединениеimport sqlite3\n",
        "\n",
        "# Закрываем соединение\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "f8cdc11e-6f4d-4cd3-d74e-24f558f2027b",
        "id": "4mdAZe_khH0j"
      },
      "execution_count": 73,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Аnri', 1, 22)\n",
            "(2, 'Serg', 1, 20)\n",
            "(3, 'Linda', 2, 19)\n",
            "(4, 'Anne', 2, 19)\n",
            "(5, 'Fred', 1, 21)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "import traceback\n",
        "import sys\n",
        "\n",
        "try:\n",
        "    sqlite_connection = sqlite3.connect('uni.db')\n",
        "    cursor = sqlite_connection.cursor()\n",
        "    print(\"База данных подключена к SQLite\")\n",
        "\n",
        "    sqlite_insert_query = \"\"\"INSERT INTO marks (id, subject, mark)\n",
        "                            VALUES (1,'Си', 5),\n",
        "\n",
        "                                    (2,'Си', 4),\n",
        "\n",
        "                                    (3,'Си', 3),\n",
        "\n",
        "                                    (4,'Химия', 4),\n",
        "\n",
        "                                    (5, 'ДифУры', 5)\"\"\"\n",
        "\n",
        "    count = cursor.execute(sqlite_insert_query)\n",
        "    sqlite_connection.commit()\n",
        "    print(\"Запись успешно вставлена ​​в таблицу sqlitedb_developers \", cursor.rowcount)\n",
        "    cursor.close()\n",
        "\n",
        "except sqlite3.Error as error:\n",
        "    print(\"Не удалось вставить данные в таблицу sqlite\")\n",
        "    print(\"Класс исключения: \", error.__class__)\n",
        "    print(\"Исключение\", error.args)\n",
        "    print(\"Печать подробноcтей исключения SQLite: \")\n",
        "    exc_type, exc_value, exc_tb = sys.exc_info()\n",
        "    print(traceback.format_exception(exc_type, exc_value, exc_tb))\n",
        "finally:\n",
        "    if (sqlite_connection):\n",
        "        sqlite_connection.close()\n",
        "        print(\"Соединение с SQLite закрыто\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5EXku-1mcY2d",
        "outputId": "40ed8d48-ed7f-4267-a055-4b43b0038111"
      },
      "execution_count": 74,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "База данных подключена к SQLite\n",
            "Запись успешно вставлена ​​в таблицу sqlitedb_developers  5\n",
            "Соединение с SQLite закрыто\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "# Устанавливаем соединение с базой данных\n",
        "connection = sqlite3.connect('uni.db')\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# Выбираем всех\n",
        "cursor.execute('SELECT * FROM marks')\n",
        "users = cursor.fetchall()\n",
        "\n",
        "# Выводим результаты\n",
        "for name in users:\n",
        "  print(name)\n",
        "\n",
        "# Закрываем соединениеimport sqlite3\n",
        "\n",
        "# Закрываем соединение\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PQ9Y2GYDfCS-",
        "outputId": "ef99b001-92ff-44b4-e15e-c88b1974300e"
      },
      "execution_count": 75,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Си', 5)\n",
            "(2, 'Си', 4)\n",
            "(3, 'Си', 3)\n",
            "(4, 'Химия', 4)\n",
            "(5, 'ДифУры', 5)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## **Задание 2**\n",
        "\n",
        "Что делают запросы?\n"
      ],
      "metadata": {
        "id": "M4myDrfOVL6x"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "SELECT mark FROM marks\n",
        "WHERE id = 2 AND subject LIKE 'Си'"
      ],
      "metadata": {
        "id": "vwPJb9G6OpD5"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "запрос должен показать оценку по языку Си как у студента с id = 2"
      ],
      "metadata": {
        "id": "IwHkJzIhomI6"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "# Устанавливаем соединение с базой данных\n",
        "connection = sqlite3.connect('uni.db')\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# Выбираем всех\n",
        "cursor.execute(\"SELECT mark FROM marks WHERE id = 2 AND subject LIKE 'Си'\")\n",
        "users = cursor.fetchall()\n",
        "\n",
        "# Выводим результаты\n",
        "for name in users:\n",
        "  print(name)\n",
        "\n",
        "# Закрываем соединениеimport sqlite3\n",
        "\n",
        "# Закрываем соединение\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b0822f0a-5063-48dc-9428-68d07fee3b13",
        "id": "zV-tU2tGh7wL"
      },
      "execution_count": 76,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(4,)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "SELECT name, subject, mark FROM marks\n",
        "JOIN students ON students.rowid = marks.id\n",
        "WHERE mark > 3 AND subject LIKE 'Си'"
      ],
      "metadata": {
        "id": "vk7sOQQCOo7C"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "запрос должен выбрать всех студентов, у которых оценка по языку Си выше 3"
      ],
      "metadata": {
        "id": "c-VdKFiWo0ey"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "# Устанавливаем соединение с базой данных\n",
        "connection = sqlite3.connect('uni.db')\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# Выбираем всех\n",
        "cursor.execute(\"SELECT name, subject, mark FROM marks JOIN students ON students.rowid = marks.id WHERE mark > 3 AND subject LIKE 'Си'\")\n",
        "users = cursor.fetchall()\n",
        "\n",
        "# Выводим результаты\n",
        "for name in users:\n",
        "  print(name)\n",
        "\n",
        "# Закрываем соединениеimport sqlite3\n",
        "\n",
        "# Закрываем соединение\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "0002265f-cecb-4b6a-e5da-18a018860ea4",
        "id": "oSG2GxbQiJx7"
      },
      "execution_count": 77,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('Аnri', 'Си', 5)\n",
            "('Serg', 'Си', 4)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## **Задание 3**\n",
        "Что делают запросы?\n",
        "\n"
      ],
      "metadata": {
        "id": "sGCd40S4VcDc"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "SELECT name, subject, mark FROM marks\n",
        "JOIN students ON students.rowid = marks.id\n",
        "WHERE mark > (SELECT mark FROM marks\n",
        "WHERE id = 2 AND subject LIKE 'Си')\n",
        "AND subject LIKE 'Си'"
      ],
      "metadata": {
        "id": "hKRaBTcNOcyF"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "запрос должен выбрать всех студентов, у которых оценка по языку Си выше, чем у студента с id = 2"
      ],
      "metadata": {
        "id": "SEMTCwofpP57"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "# Устанавливаем соединение с базой данных\n",
        "connection = sqlite3.connect('uni.db')\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# Выбираем всех\n",
        "cursor.execute(\"SELECT name, subject, mark FROM marks JOIN students ON students.rowid = marks.id WHERE mark > (SELECT mark FROM marks WHERE id = 2 AND subject LIKE 'Си') AND subject LIKE 'Си'\")\n",
        "users = cursor.fetchall()\n",
        "\n",
        "# Выводим результаты\n",
        "for name in users:\n",
        "  print(name)\n",
        "\n",
        "# Закрываем соединениеimport sqlite3\n",
        "\n",
        "# Закрываем соединение\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "dc67b8e1-ce4b-457e-dd37-1ece2a60d5a3",
        "id": "GigBYPJ3iYfT"
      },
      "execution_count": 78,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('Аnri', 'Си', 5)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "SELECT name, subject, mark FROM marks\n",
        "JOIN students ON students.rowid = marks.id\n",
        "WHERE mark > (SELECT mark FROM marks WHERE id = 2 )\n",
        "AND subject LIKE 'Си'"
      ],
      "metadata": {
        "id": "sma9SWhJOinb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "В этом случае будет использован только первый полученный результат, другие попросту проигнорируются и результат будет тем же"
      ],
      "metadata": {
        "id": "ic6xc1Fbp1Ui"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "# Устанавливаем соединение с базой данных\n",
        "connection = sqlite3.connect('uni.db')\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# Выбираем всех\n",
        "cursor.execute(\"SELECT name, subject, mark FROM marks JOIN students ON students.rowid = marks.id WHERE mark > (SELECT mark FROM marks WHERE id = 2 ) AND subject LIKE 'Си'\")\n",
        "users = cursor.fetchall()\n",
        "\n",
        "# Выводим результаты\n",
        "for name in users:\n",
        "  print(name)\n",
        "\n",
        "# Закрываем соединениеimport sqlite3\n",
        "\n",
        "# Закрываем соединение\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "a167aeb7-b496-43df-b055-863e13a7581e",
        "id": "WhsZgwU1iylY"
      },
      "execution_count": 79,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('Аnri', 'Си', 5)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **Возвращение нескольких записей**"
      ],
      "metadata": {
        "id": "m0L93KzGKkSF"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "\n",
        "Если вложенный запрос вернет несколько записей (оценок), то  этом случае будет использован только первый полученный результат, другие попросту проигнорируются и результат будет тем же .\n",
        "\n",
        "Если же вложенный SELECT ничего не находит (возвращает значение NULL), то внешний запрос не будет возвращать никаких записей."
      ],
      "metadata": {
        "id": "cahsIYPgWbbc"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "следует обращать внимание, что подзапросы не могут обрабатывать свои результаты, поэтому в них нельзя указывать, например, оператор GROUP BY. Но агрегирующие функции вполне можно использовать, например, так:\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "ZFU5Bh8PWjDY"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "SELECT name, subject, mark FROM marks\n",
        "JOIN students ON students.rowid = marks.id\n",
        "WHERE mark > (SELECT avg(mark) FROM marks WHERE id = 2 )\n",
        "AND subject LIKE 'Си'"
      ],
      "metadata": {
        "id": "XaSAQ1KhPJZu"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## **Задание 4**\n",
        "Приведите пример запроса с вложенным подзапросом, который возвращает несколько записей"
      ],
      "metadata": {
        "id": "USlnCQtEP1mp"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "# Устанавливаем соединение с базой данных\n",
        "connection = sqlite3.connect('uni.db')\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# Выбираем всех\n",
        "cursor.execute(\"SELECT name, subject, mark FROM marks JOIN students ON students.rowid = marks.id WHERE mark < (SELECT avg(mark) FROM marks WHERE id = 1 ) AND subject LIKE 'Си'\")\n",
        "users = cursor.fetchall()\n",
        "\n",
        "# Выводим результаты\n",
        "for name in users:\n",
        "  print(name)\n",
        "\n",
        "# Закрываем соединениеimport sqlite3\n",
        "\n",
        "# Закрываем соединение\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "5829ba39-8f93-4e81-8656-b3b7fecfed01",
        "id": "FyUnGVIJjTdT"
      },
      "execution_count": 80,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('Serg', 'Си', 4)\n",
            "('Linda', 'Си', 3)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **Вложения в команде INSERT**"
      ],
      "metadata": {
        "id": "wXugdlnfWq-x"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Если создать таблицу female с полями идентичными students. Вот таким образом:\n",
        "SELECT * FROM students WHERE sex = 2\n",
        "INSERT INTO female SELECT * FROM students WHERE sex = 2\n",
        "."
      ],
      "metadata": {
        "id": "FGXo78V4Wui5"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "SELECT * FROM students WHERE sex = 2\n",
        "INSERT INTO female SELECT * FROM students WHERE sex = 2"
      ],
      "metadata": {
        "id": "A9gMfYViPbdA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "Но если выполнить запрос еще раз, то возникнет ошибка, т.к. пытаемся добавить строки с уже существующими id, что запрещено по структуре этого поля – оно определено как главный ключ и содержит только уникальные значения"
      ],
      "metadata": {
        "id": "E_LM6a5uPd-X"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "try:\n",
        "    sqlite_connection = sqlite3.connect('uni.db')\n",
        "    sqlite_create_table_query = '''CREATE TABLE female (\n",
        "                                id INTEGER,\n",
        "                                name TEXT,\n",
        "                                sex INTEGER,\n",
        "                                old INTEGER\n",
        "                                );'''\n",
        "\n",
        "    cursor = sqlite_connection.cursor()\n",
        "    print(\"База данных подключена к SQLite\")\n",
        "    cursor.execute(sqlite_create_table_query)\n",
        "    sqlite_connection.commit()\n",
        "    print(\"Таблица SQLite создана\")\n",
        "\n",
        "    cursor.close()\n",
        "\n",
        "except sqlite3.Error as error:\n",
        "    print(\"Ошибка при подключении к sqlite\", error)\n",
        "finally:\n",
        "    if (sqlite_connection):\n",
        "        sqlite_connection.close()\n",
        "        print(\"Соединение с SQLite закрыто\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "f914de43-48b4-43db-a208-ffd426ab0212",
        "id": "iBUR--LBkLte"
      },
      "execution_count": 81,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "База данных подключена к SQLite\n",
            "Таблица SQLite создана\n",
            "Соединение с SQLite закрыто\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "import traceback\n",
        "import sys\n",
        "\n",
        "try:\n",
        "    sqlite_connection = sqlite3.connect('uni.db')\n",
        "    cursor = sqlite_connection.cursor()\n",
        "    print(\"База данных подключена к SQLite\")\n",
        "\n",
        "    sqlite_insert_query = \"\"\"INSERT INTO female SELECT * FROM students WHERE sex = 2\"\"\"\n",
        "\n",
        "    count = cursor.execute(sqlite_insert_query)\n",
        "    sqlite_connection.commit()\n",
        "    print(\"Запись успешно вставлена ​​в таблицу sqlitedb_developers \", cursor.rowcount)\n",
        "    cursor.close()\n",
        "\n",
        "except sqlite3.Error as error:\n",
        "    print(\"Не удалось вставить данные в таблицу sqlite\")\n",
        "    print(\"Класс исключения: \", error.__class__)\n",
        "    print(\"Исключение\", error.args)\n",
        "    print(\"Печать подробноcтей исключения SQLite: \")\n",
        "    exc_type, exc_value, exc_tb = sys.exc_info()\n",
        "    print(traceback.format_exception(exc_type, exc_value, exc_tb))\n",
        "finally:\n",
        "    if (sqlite_connection):\n",
        "        sqlite_connection.close()\n",
        "        print(\"Соединение с SQLite закрыто\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "f6429ec8-ba0c-4b51-9435-263582f5886a",
        "id": "GIRezTvUke_V"
      },
      "execution_count": 82,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "База данных подключена к SQLite\n",
            "Запись успешно вставлена ​​в таблицу sqlitedb_developers  2\n",
            "Соединение с SQLite закрыто\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "# Устанавливаем соединение с базой данных\n",
        "connection = sqlite3.connect('uni.db')\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# Выбираем всех\n",
        "cursor.execute('SELECT * FROM female')\n",
        "users = cursor.fetchall()\n",
        "\n",
        "# Выводим результаты\n",
        "for name in users:\n",
        "  print(name)\n",
        "\n",
        "# Закрываем соединениеimport sqlite3\n",
        "\n",
        "# Закрываем соединение\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "4ba6c7a8-de0a-461a-db24-9bad082fad8a",
        "id": "E8V0FXv7kLtm"
      },
      "execution_count": 83,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(3, 'Linda', 2, 19)\n",
            "(4, 'Anne', 2, 19)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Чтобы поправить ситуацию, можно вложенный запрос написать так:\n"
      ],
      "metadata": {
        "id": "EyvDm-GuXqV1"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "INSERT INTO female\n",
        "SELECT NULL, name, sex, old FROM students WHERE sex = 2"
      ],
      "metadata": {
        "id": "GrsvyAaEPgtq"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "import traceback\n",
        "import sys\n",
        "\n",
        "try:\n",
        "    sqlite_connection = sqlite3.connect('uni.db')\n",
        "    cursor = sqlite_connection.cursor()\n",
        "    print(\"База данных подключена к SQLite\")\n",
        "\n",
        "    sqlite_insert_query = \"\"\"INSERT INTO female\n",
        "                            SELECT NULL, name, sex, old FROM students WHERE sex = 2\"\"\"\n",
        "\n",
        "    count = cursor.execute(sqlite_insert_query)\n",
        "    sqlite_connection.commit()\n",
        "    print(\"Запись успешно вставлена ​​в таблицу sqlitedb_developers \", cursor.rowcount)\n",
        "    cursor.close()\n",
        "\n",
        "except sqlite3.Error as error:\n",
        "    print(\"Не удалось вставить данные в таблицу sqlite\")\n",
        "    print(\"Класс исключения: \", error.__class__)\n",
        "    print(\"Исключение\", error.args)\n",
        "    print(\"Печать подробноcтей исключения SQLite: \")\n",
        "    exc_type, exc_value, exc_tb = sys.exc_info()\n",
        "    print(traceback.format_exception(exc_type, exc_value, exc_tb))\n",
        "finally:\n",
        "    if (sqlite_connection):\n",
        "        sqlite_connection.close()\n",
        "        print(\"Соединение с SQLite закрыто\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "01972157-3ca6-4bb2-8db8-694bb870306f",
        "id": "Z8yjpMz8lXjk"
      },
      "execution_count": 84,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "База данных подключена к SQLite\n",
            "Запись успешно вставлена ​​в таблицу sqlitedb_developers  2\n",
            "Соединение с SQLite закрыто\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "# Устанавливаем соединение с базой данных\n",
        "connection = sqlite3.connect('uni.db')\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# Выбираем всех\n",
        "cursor.execute('SELECT * FROM female')\n",
        "users = cursor.fetchall()\n",
        "\n",
        "# Выводим результаты\n",
        "for name in users:\n",
        "  print(name)\n",
        "\n",
        "# Закрываем соединениеimport sqlite3\n",
        "\n",
        "# Закрываем соединение\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "04b274b2-582d-4856-d5c4-b694c02d268c",
        "id": "X_OFxUrglXjr"
      },
      "execution_count": 85,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(3, 'Linda', 2, 19)\n",
            "(4, 'Anne', 2, 19)\n",
            "(None, 'Linda', 2, 19)\n",
            "(None, 'Anne', 2, 19)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "в качестве значения первого поля указали NULL и, соответственно, СУБД вместо него сгенерирует уникальный ключ для добавляемых записей."
      ],
      "metadata": {
        "id": "boXLdK1KPl0Q"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **Вложения в команде UPDATE**"
      ],
      "metadata": {
        "id": "2YTS6XhoX0-y"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## **Задание 5**\n",
        "\n",
        "Что делает запрос?\n",
        "\n",
        "UPDATE marks SET mark = 0\n",
        "WHERE mark <= (SELECT min(mark) FROM marks WHERE id = 1)"
      ],
      "metadata": {
        "id": "B998TJK4X5nQ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "import traceback\n",
        "import sys\n",
        "\n",
        "try:\n",
        "    sqlite_connection = sqlite3.connect('uni.db')\n",
        "    cursor = sqlite_connection.cursor()\n",
        "    print(\"База данных подключена к SQLite\")\n",
        "\n",
        "    sqlite_insert_query = \"\"\"UPDATE marks SET mark = 0 WHERE mark <= (SELECT min(mark) FROM marks WHERE id = 1)\"\"\"\n",
        "\n",
        "    count = cursor.execute(sqlite_insert_query)\n",
        "    sqlite_connection.commit()\n",
        "    print(\"Запись успешно вставлена ​​в таблицу sqlitedb_developers \", cursor.rowcount)\n",
        "    cursor.close()\n",
        "\n",
        "except sqlite3.Error as error:\n",
        "    print(\"Не удалось вставить данные в таблицу sqlite\")\n",
        "    print(\"Класс исключения: \", error.__class__)\n",
        "    print(\"Исключение\", error.args)\n",
        "    print(\"Печать подробноcтей исключения SQLite: \")\n",
        "    exc_type, exc_value, exc_tb = sys.exc_info()\n",
        "    print(traceback.format_exception(exc_type, exc_value, exc_tb))\n",
        "finally:\n",
        "    if (sqlite_connection):\n",
        "        sqlite_connection.close()\n",
        "        print(\"Соединение с SQLite закрыто\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "774b1c11-eb6d-43d5-ba85-b89ee442332a",
        "id": "0mbKWabWlr5c"
      },
      "execution_count": 47,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "База данных подключена к SQLite\n",
            "Запись успешно вставлена ​​в таблицу sqlitedb_developers  5\n",
            "Соединение с SQLite закрыто\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "# Устанавливаем соединение с базой данных\n",
        "connection = sqlite3.connect('uni.db')\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# Выбираем всех\n",
        "cursor.execute('SELECT * FROM marks')\n",
        "users = cursor.fetchall()\n",
        "\n",
        "# Выводим результаты\n",
        "for name in users:\n",
        "  print(name)\n",
        "\n",
        "# Закрываем соединениеimport sqlite3\n",
        "\n",
        "# Закрываем соединение\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "3555fc0c-169a-40e2-b9c9-dff4c990501a",
        "id": "2JwZHJI-lr5l"
      },
      "execution_count": 48,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('1', 'Си', 0)\n",
            "('2', 'Си', 0)\n",
            "('3', 'Си', 0)\n",
            "('4', 'Химия', 0)\n",
            "('5', 'ДифУры', 0)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        " Запрос обнуляет все оценки в таблице marks, которые меньше или равны минимальной оценки студента с id = 1"
      ],
      "metadata": {
        "id": "EJDZJoDsmqtR"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **Вложения в команде DELETE**"
      ],
      "metadata": {
        "id": "6bPKF7gSYDVu"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## **Задание 6**\n",
        "\n",
        "Что делает запрос?\n",
        "\n",
        "DELETE FROM students\n",
        "WHERE old < (SELECT old FROM students WHERE id = 2)"
      ],
      "metadata": {
        "id": "JtH6WKQ0YIm9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "import traceback\n",
        "import sys\n",
        "\n",
        "try:\n",
        "    sqlite_connection = sqlite3.connect('uni.db')\n",
        "    cursor = sqlite_connection.cursor()\n",
        "    print(\"База данных подключена к SQLite\")\n",
        "\n",
        "    sqlite_insert_query = \"\"\"DELETE FROM students WHERE old < (SELECT old FROM students WHERE id = 2)\"\"\"\n",
        "\n",
        "    count = cursor.execute(sqlite_insert_query)\n",
        "    sqlite_connection.commit()\n",
        "    print(\"Запись успешно вставлена ​​в таблицу sqlitedb_developers \", cursor.rowcount)\n",
        "    cursor.close()\n",
        "\n",
        "except sqlite3.Error as error:\n",
        "    print(\"Не удалось вставить данные в таблицу sqlite\")\n",
        "    print(\"Класс исключения: \", error.__class__)\n",
        "    print(\"Исключение\", error.args)\n",
        "    print(\"Печать подробноcтей исключения SQLite: \")\n",
        "    exc_type, exc_value, exc_tb = sys.exc_info()\n",
        "    print(traceback.format_exception(exc_type, exc_value, exc_tb))\n",
        "finally:\n",
        "    if (sqlite_connection):\n",
        "        sqlite_connection.close()\n",
        "        print(\"Соединение с SQLite закрыто\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "32be70e3-001f-4d20-9570-b0bfe7cc9282",
        "id": "R2nltFBel4cx"
      },
      "execution_count": 49,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "База данных подключена к SQLite\n",
            "Запись успешно вставлена ​​в таблицу sqlitedb_developers  2\n",
            "Соединение с SQLite закрыто\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "# Устанавливаем соединение с базой данных\n",
        "connection = sqlite3.connect('uni.db')\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# Выбираем всех\n",
        "cursor.execute('SELECT * FROM students')\n",
        "users = cursor.fetchall()\n",
        "\n",
        "# Выводим результаты\n",
        "for name in users:\n",
        "  print(name)\n",
        "\n",
        "# Закрываем соединениеimport sqlite3\n",
        "\n",
        "# Закрываем соединение\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "ab41d0dc-42ca-4c06-c587-c263f757dd00",
        "id": "TaPZeVOil4cy"
      },
      "execution_count": 50,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('1', 'Аnri', 1, 22)\n",
            "('2', 'Serg', 1, 20)\n",
            "('5', 'Fred', 1, 21)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Удаляет из таблицы students всех студентов, возраст которых меньше, чем у студента с id = 2"
      ],
      "metadata": {
        "id": "d7XAioY5mXmK"
      }
    }
  ]
}