Part 1/1.2 Data Sampling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9f1d852c",
   "metadata": {},
   "source": [
    "# 1.2 Выборка данных"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d9ebcfc",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf7887ef",
   "metadata": {},
   "source": [
    "## Инициализация БД"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "df1514b0",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: user@stepik'"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "import sqlalchemy\n",
    "engine = sqlalchemy.create_engine('mysql://user:pass@localhost:3306/stepik')\n",
    "%sql mysql://user:pass@localhost:3306/stepik"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "717c73d1",
   "metadata": {},
   "source": [
    "## Заполнение таблицы из CSV файла"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "4b367e3a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>book_id</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>price</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>670.99</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>540.50</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Идиот</td>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>460.00</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Братья Карамазовы</td>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>799.01</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Стихотворения и поэмы</td>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>650.00</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   book_id                  title            author   price  amount\n",
       "0        1     Мастер и Маргарита     Булгаков М.А.  670.99       3\n",
       "1        2          Белая гвардия     Булгаков М.А.  540.50       5\n",
       "2        3                  Идиот  Достоевский Ф.М.  460.00      10\n",
       "3        4      Братья Карамазовы  Достоевский Ф.М.  799.01       2\n",
       "4        5  Стихотворения и поэмы       Есенин С.А.  650.00      15"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Чтение файла в DataFrame\n",
    "import pandas as pd\n",
    "file = 'tables/book.csv'\n",
    "df = pd.read_csv(file)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ac0753a",
   "metadata": {},
   "source": [
    "Создание схемы таблицы:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "84c3ffc6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "0 rows affected.\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "  DROP TABLE IF EXISTS book;\n",
    "CREATE TABLE IF NOT EXISTS book (\n",
    "       book_id INT PRIMARY KEY AUTO_INCREMENT,\n",
    "       title   VARCHAR(50),\n",
    "       author  VARCHAR(30),\n",
    "       price   DECIMAL(8, 2), #\n",
    "       amount  INT\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "589c0198",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Запись данных в таблицу из DataFrame\n",
    "types = {\n",
    "    'book_id': sqlalchemy.Integer(),\n",
    "    'price'  : sqlalchemy.Numeric(precision=8, scale=2),\n",
    "    'amount' : sqlalchemy.Integer()\n",
    "}\n",
    "df.to_sql('book', con=engine, index=False, if_exists='append', dtype=types, method='multi')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12012e67",
   "metadata": {},
   "source": [
    "***"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0447ce94",
   "metadata": {},
   "source": [
    "# Упражнения"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "802cc8a6",
   "metadata": {},
   "source": [
    "### Выборка всех данных из таблицы"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "fa98d2d1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>book_id</th>\n",
       "            <th>title</th>\n",
       "            <th>author</th>\n",
       "            <th>price</th>\n",
       "            <th>amount</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>670.99</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>540.50</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Идиот</td>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>460.00</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>799.01</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>Есенин С.А.</td>\n",
       "            <td>650.00</td>\n",
       "            <td>15</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Мастер и Маргарита', 'Булгаков М.А.', Decimal('670.99'), 3),\n",
       " (2, 'Белая гвардия', 'Булгаков М.А.', Decimal('540.50'), 5),\n",
       " (3, 'Идиот', 'Достоевский Ф.М.', Decimal('460.00'), 10),\n",
       " (4, 'Братья Карамазовы', 'Достоевский Ф.М.', Decimal('799.01'), 2),\n",
       " (5, 'Стихотворения и поэмы', 'Есенин С.А.', Decimal('650.00'), 15)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "  FROM book;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "706a99ce",
   "metadata": {},
   "source": [
    "### Выборка отдельных столбцов"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "c1b66a7d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>author</th>\n",
       "            <th>title</th>\n",
       "            <th>price</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>670.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>540.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>Идиот</td>\n",
       "            <td>460.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>799.01</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Есенин С.А.</td>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>650.00</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Булгаков М.А.', 'Мастер и Маргарита', Decimal('670.99')),\n",
       " ('Булгаков М.А.', 'Белая гвардия', Decimal('540.50')),\n",
       " ('Достоевский Ф.М.', 'Идиот', Decimal('460.00')),\n",
       " ('Достоевский Ф.М.', 'Братья Карамазовы', Decimal('799.01')),\n",
       " ('Есенин С.А.', 'Стихотворения и поэмы', Decimal('650.00'))]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT author, title, price \n",
    "  FROM book;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5a550d2",
   "metadata": {},
   "source": [
    "### Выборка новых столбцов и присвоение им псевдонимов `AS`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "f9b24d92",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Название</th>\n",
       "            <th>Автор</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>Булгаков М.А.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>Булгаков М.А.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Идиот</td>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>Есенин С.А.</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Мастер и Маргарита', 'Булгаков М.А.'),\n",
       " ('Белая гвардия', 'Булгаков М.А.'),\n",
       " ('Идиот', 'Достоевский Ф.М.'),\n",
       " ('Братья Карамазовы', 'Достоевский Ф.М.'),\n",
       " ('Стихотворения и поэмы', 'Есенин С.А.')]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title  AS Название,\n",
    "       author AS Автор \n",
    "  FROM book;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fad5bc77",
   "metadata": {},
   "source": [
    "### Выборка данных с созданием вычисляемого столбца"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "d8ffa1c3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>amount</th>\n",
       "            <th>pack</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>3</td>\n",
       "            <td>4.95</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>5</td>\n",
       "            <td>8.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Идиот</td>\n",
       "            <td>10</td>\n",
       "            <td>16.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>2</td>\n",
       "            <td>3.30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>15</td>\n",
       "            <td>24.75</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Мастер и Маргарита', 3, Decimal('4.95')),\n",
       " ('Белая гвардия', 5, Decimal('8.25')),\n",
       " ('Идиот', 10, Decimal('16.50')),\n",
       " ('Братья Карамазовы', 2, Decimal('3.30')),\n",
       " ('Стихотворения и поэмы', 15, Decimal('24.75'))]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, amount, \n",
    "       amount * 1.65 AS pack        \n",
    "  FROM book;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a15ad90",
   "metadata": {},
   "source": [
    "### Выборка данных, вычисляемые столбцы, математические функции"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fb75fa5",
   "metadata": {},
   "source": [
    "В SQL реализовано множество  [математических функций](https://docs.microsoft.com/ru-ru/sql/t-sql/functions/mathematical-functions-transact-sql?view=sql-server-ver15) для работы с числовыми данными. В таблице приведены некоторые из них."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df744aa9",
   "metadata": {},
   "source": [
    "| Функция | Описание | Пример |\n",
    "|---|---|---|\n",
    "| `CEILING(x)` | Возвращает наименьшее целое число, большее или равное X<BR>(округляет до целого числа в большую сторону) | `CEILING(4.2)=5<br>CEILING(-5.8)=-5` |\n",
    "| `ROUND(x, k)` | Округляет значение X до K знаков после запятой,<BR>если K не указано – X округляется до целого | `ROUND(4.361)=4<br>ROUND(5.86592,1)=5.9` |\n",
    "| `FLOOR(x)` | Возвращает наибольшее целое число, меньшее или равное X<BR>(округляет до  целого числа в меньшую сторону) | `FLOOR(4.2)=4<br>FLOOR(-5.8)=-6` |\n",
    "| `POWER(x, y)` | Возведение X в степень Y | `POWER(3,4)=81.0` |\n",
    "| `SQRT(x)` | Квадратный корень из X | `SQRT(4)=2.0<br>SQRT(2)=1.41...` |\n",
    "| `DEGREES(x)` | Конвертирует значение X из радиан в градусы | `DEGREES(3) = 171.8...` |\n",
    "| `RADIANS(x)` | Конвертирует значение X из градусов в радианы | `RADIANS(180)=3.14...` |\n",
    "| `ABS(x)` | Модуль числа X | `ABS(-1) = 1<br>ABS(1) = 1` |\n",
    "| `PI()` | PI = 3.1415926... |  |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "087e35ab",
   "metadata": {},
   "source": [
    "**Пример**  \n",
    "Для каждой книги из таблицы book вычислим НДС (имя столбца _tax_) , который включен в цену и составляет _k_ = 18%,  а также цену книги (_price_tax_) без него.  \n",
    "Формула НДС:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f499d839",
   "metadata": {},
   "source": [
    "$$ tax = \\frac{price*\\frac{k}{100}}{1+\\frac{k}{100}} $$"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6604db92",
   "metadata": {},
   "source": [
    "Формула ниже отвечает на вопрос _\"Какую сумму увеличили на 18%, чтобы получить текущее значение\"_"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84cf04d3",
   "metadata": {},
   "source": [
    "$$ price\\_tax = \\frac{price}{1+\\frac{k}{100}} $$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2ba83a05",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>price</th>\n",
       "            <th>tax</th>\n",
       "            <th>price_tax</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>670.99</td>\n",
       "            <td>102.35</td>\n",
       "            <td>568.64</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>540.50</td>\n",
       "            <td>82.45</td>\n",
       "            <td>458.05</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Идиот</td>\n",
       "            <td>460.00</td>\n",
       "            <td>70.17</td>\n",
       "            <td>389.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>799.01</td>\n",
       "            <td>121.88</td>\n",
       "            <td>677.13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>650.00</td>\n",
       "            <td>99.15</td>\n",
       "            <td>550.85</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Мастер и Маргарита', Decimal('670.99'), Decimal('102.35'), Decimal('568.64')),\n",
       " ('Белая гвардия', Decimal('540.50'), Decimal('82.45'), Decimal('458.05')),\n",
       " ('Идиот', Decimal('460.00'), Decimal('70.17'), Decimal('389.83')),\n",
       " ('Братья Карамазовы', Decimal('799.01'), Decimal('121.88'), Decimal('677.13')),\n",
       " ('Стихотворения и поэмы', Decimal('650.00'), Decimal('99.15'), Decimal('550.85'))]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- Вычисление НДС\n",
    "SELECT title, price, \n",
    "       ROUND((price*18/100)/(1+18/100),2) AS tax, \n",
    "       ROUND(price/(1+18/100),2) AS price_tax \n",
    "  FROM book;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce131c38",
   "metadata": {},
   "source": [
    "**Задание**  \n",
    "В конце года цену всех книг на складе пересчитывают – снижают ее на 30%.  \n",
    "Написать SQL запрос, который из таблицы book выбирает названия, авторов, количества и вычисляет новые цены книг.  \n",
    "Столбец с новой ценой назвать new_price, цену округлить до 2-х знаков после запятой."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "8d28b92c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>author</th>\n",
       "            <th>amount</th>\n",
       "            <th>new_price</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>3</td>\n",
       "            <td>469.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>5</td>\n",
       "            <td>378.35</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Идиот</td>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>10</td>\n",
       "            <td>322.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>2</td>\n",
       "            <td>559.31</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>Есенин С.А.</td>\n",
       "            <td>15</td>\n",
       "            <td>455.00</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Мастер и Маргарита', 'Булгаков М.А.', 3, Decimal('469.69')),\n",
       " ('Белая гвардия', 'Булгаков М.А.', 5, Decimal('378.35')),\n",
       " ('Идиот', 'Достоевский Ф.М.', 10, Decimal('322.00')),\n",
       " ('Братья Карамазовы', 'Достоевский Ф.М.', 2, Decimal('559.31')),\n",
       " ('Стихотворения и поэмы', 'Есенин С.А.', 15, Decimal('455.00'))]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, author, amount,\n",
    "       ROUND(price * 0.7, 2) AS new_price\n",
    "  FROM book;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43319ba3",
   "metadata": {},
   "source": [
    "### Выборка данных, вычисляемые столбцы, логические функции `IF()`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85d749da",
   "metadata": {},
   "source": [
    "**Пример**  \n",
    "Для каждой книги из таблицы `book` установим скидку следующим образом:  \n",
    "если количество книг меньше 4, то скидка будет составлять 50% от цены, в противном случае 30%."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "e64554a7",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>amount</th>\n",
       "            <th>price</th>\n",
       "            <th>sale</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>3</td>\n",
       "            <td>670.99</td>\n",
       "            <td>335.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>5</td>\n",
       "            <td>540.50</td>\n",
       "            <td>378.35</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Идиот</td>\n",
       "            <td>10</td>\n",
       "            <td>460.00</td>\n",
       "            <td>322.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>2</td>\n",
       "            <td>799.01</td>\n",
       "            <td>399.51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>15</td>\n",
       "            <td>650.00</td>\n",
       "            <td>455.00</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Мастер и Маргарита', 3, Decimal('670.99'), Decimal('335.50')),\n",
       " ('Белая гвардия', 5, Decimal('540.50'), Decimal('378.35')),\n",
       " ('Идиот', 10, Decimal('460.00'), Decimal('322.00')),\n",
       " ('Братья Карамазовы', 2, Decimal('799.01'), Decimal('399.51')),\n",
       " ('Стихотворения и поэмы', 15, Decimal('650.00'), Decimal('455.00'))]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, amount, price,\n",
    "       ROUND(IF(amount < 4, price * 0.5, price * 0.7),2) AS sale\n",
    "  FROM book;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3797387",
   "metadata": {},
   "source": [
    "**Пример 2**  \n",
    "Усложним вычисление скидки в зависимости от количества книг.  \n",
    "Если количество книг меньше 4 – то скидка 50%, меньше 11 – 30%, в остальных случаях – 10%.  \n",
    "И еще укажем какая именно скидка на каждую книгу."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "deecc3eb",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>amount</th>\n",
       "            <th>price</th>\n",
       "            <th>sale</th>\n",
       "            <th>Ваша_скидка</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>3</td>\n",
       "            <td>670.99</td>\n",
       "            <td>335.50</td>\n",
       "            <td>50%</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>5</td>\n",
       "            <td>540.50</td>\n",
       "            <td>378.35</td>\n",
       "            <td>30%</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Идиот</td>\n",
       "            <td>10</td>\n",
       "            <td>460.00</td>\n",
       "            <td>322.00</td>\n",
       "            <td>30%</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>2</td>\n",
       "            <td>799.01</td>\n",
       "            <td>399.51</td>\n",
       "            <td>50%</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>15</td>\n",
       "            <td>650.00</td>\n",
       "            <td>585.00</td>\n",
       "            <td>10%</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Мастер и Маргарита', 3, Decimal('670.99'), Decimal('335.50'), '50%'),\n",
       " ('Белая гвардия', 5, Decimal('540.50'), Decimal('378.35'), '30%'),\n",
       " ('Идиот', 10, Decimal('460.00'), Decimal('322.00'), '30%'),\n",
       " ('Братья Карамазовы', 2, Decimal('799.01'), Decimal('399.51'), '50%'),\n",
       " ('Стихотворения и поэмы', 15, Decimal('650.00'), Decimal('585.00'), '10%')]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, amount, price,\n",
    "       ROUND(IF(amount < 11, \n",
    "                IF(amount < 4, \n",
    "                   price * 0.5, \n",
    "                   price * 0.7), \n",
    "                price * 0.9),2\n",
    "            ) AS sale,\n",
    "       IF(amount < 11, \n",
    "          IF(amount < 4, \n",
    "             '50%',\n",
    "             '30%'), \n",
    "          '10%'\n",
    "         ) AS Ваша_скидка\n",
    "  FROM book;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "970b2cde",
   "metadata": {},
   "source": [
    "**Задание**  \n",
    "При анализе продаж книг выяснилось, что наибольшей популярностью пользуются книги Михаила Булгакова, на втором месте книги Сергея Есенина.  \n",
    "Исходя из этого решили поднять цену книг Булгакова на 10%, а цену книг Есенина - на 5%.  \n",
    "Написать запрос, куда включить автора, название книги и новую цену, последний столбец назвать new_price.  \n",
    "Значение округлить до двух знаков после запятой."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "ab9cfcd4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>author</th>\n",
       "            <th>title</th>\n",
       "            <th>new_price</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>738.09</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>594.55</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>Идиот</td>\n",
       "            <td>460.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>799.01</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Есенин С.А.</td>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>682.50</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Булгаков М.А.', 'Мастер и Маргарита', Decimal('738.09')),\n",
       " ('Булгаков М.А.', 'Белая гвардия', Decimal('594.55')),\n",
       " ('Достоевский Ф.М.', 'Идиот', Decimal('460.00')),\n",
       " ('Достоевский Ф.М.', 'Братья Карамазовы', Decimal('799.01')),\n",
       " ('Есенин С.А.', 'Стихотворения и поэмы', Decimal('682.50'))]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT author, title,\n",
    "       ROUND(\n",
    "             IF(author = 'Булгаков М.А.', \n",
    "                price * 1.1,\n",
    "                IF(author = 'Есенин С.А.',\n",
    "                   price * 1.05,\n",
    "                   price)\n",
    "               ),2\n",
    "            ) AS new_price\n",
    "  FROM book;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95b9d24c",
   "metadata": {},
   "source": [
    "### Выборка данных по условию `WHERE`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6a5f9a4",
   "metadata": {},
   "source": [
    "**Пример**  \n",
    "Вывести название и цену тех книг, цены которых меньше 600 рублей."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "4850d8df",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>price</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>540.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Идиот</td>\n",
       "            <td>460.00</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Белая гвардия', Decimal('540.50')), ('Идиот', Decimal('460.00'))]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, price\n",
    "  FROM book\n",
    " WHERE price < 600;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9700c2e8",
   "metadata": {},
   "source": [
    "**Пример 2**  \n",
    "Вывести название, автора  и стоимость (цена умножить на количество) тех книг, стоимость которых больше 4000 рублей"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "e30f8c3f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>author</th>\n",
       "            <th>total</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Идиот</td>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>4600.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>Есенин С.А.</td>\n",
       "            <td>9750.00</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Идиот', 'Достоевский Ф.М.', Decimal('4600.00')),\n",
       " ('Стихотворения и поэмы', 'Есенин С.А.', Decimal('9750.00'))]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, author, price * amount AS total\n",
    "  FROM book\n",
    " WHERE price * amount > 4000;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3be681f4",
   "metadata": {},
   "source": [
    "> **Пояснение**  \n",
    "В логическом выражении после `WHERE` нельзя использовать названия столбцов, присвоенные им с помощью `AS`,  \n",
    "так как при выполнении запроса сначала вычисляется логическое выражение для каждой строки исходной таблицы,  \n",
    "выбираются строки, для которых оно истинно.  А только после этого формируется \"шапка запроса\" – столбцы, включаемые в запрос."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e5c4e45",
   "metadata": {},
   "source": [
    "**Задание**  \n",
    "Вывести автора, название  и цены тех книг, количество которых меньше 10."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "8db1b709",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>author</th>\n",
       "            <th>title</th>\n",
       "            <th>price</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>670.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>540.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>799.01</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Булгаков М.А.', 'Мастер и Маргарита', Decimal('670.99')),\n",
       " ('Булгаков М.А.', 'Белая гвардия', Decimal('540.50')),\n",
       " ('Достоевский Ф.М.', 'Братья Карамазовы', Decimal('799.01'))]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT author, title, price\n",
    "  FROM book\n",
    " WHERE amount < 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63bc80b9",
   "metadata": {},
   "source": [
    "### Выборка данных, логические операции `NOT`, `AND`, `OR`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a884ab6b",
   "metadata": {},
   "source": [
    "**Задание**  \n",
    "Вывести название, автора,  цену  и количество всех книг, цена которых меньше 500 или больше 600, а стоимость всех экземпляров этих книг больше или равна 5000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "b40ba82a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>author</th>\n",
       "            <th>price</th>\n",
       "            <th>amount</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>Есенин С.А.</td>\n",
       "            <td>650.00</td>\n",
       "            <td>15</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Стихотворения и поэмы', 'Есенин С.А.', Decimal('650.00'), 15)]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, author, price, amount\n",
    "  FROM book\n",
    " WHERE (price < 500 OR price > 600) \n",
    "   AND amount * price >= 5000;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "161aa52f",
   "metadata": {},
   "source": [
    "### Выборка данных,  операторы `BETWEEN`, `IN`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7917935",
   "metadata": {},
   "source": [
    "**Задание**  \n",
    "Вывести название и авторов тех книг, цены которых принадлежат интервалу от 540.50 до 800 (включая границы),  \n",
    "а количество или 2, или 3, или 5, или 7 ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "df674231",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>author</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>Булгаков М.А.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>Булгаков М.А.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Мастер и Маргарита', 'Булгаков М.А.'),\n",
       " ('Белая гвардия', 'Булгаков М.А.'),\n",
       " ('Братья Карамазовы', 'Достоевский Ф.М.')]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, author\n",
    "  FROM book\n",
    " WHERE price BETWEEN 540.50 AND 800\n",
    "   AND amount IN (2, 3, 5, 7);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b186925",
   "metadata": {},
   "source": [
    "### Выборка данных с сортировкой `ORDER BY`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbacee9e",
   "metadata": {},
   "source": [
    "Если указан второй столбец, сортировка осуществляется только для тех строк, у которых значения первого столбца одинаковы.  \n",
    "По умолчанию `ORDER BY` выполняет сортировку по возрастанию.  \n",
    "Чтобы управлять направлением сортировки вручную, после имени столбца указывается ключевое слово `ASC` (по возрастанию) или `DESC` (по убыванию). "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebc5da57",
   "metadata": {},
   "source": [
    "Логический порядок операций для запроса SQL следующий:\n",
    "1. FROM\n",
    "1. WHERE\n",
    "1. SELECT\n",
    "1. ORDER BY\n",
    "\n",
    "Поскольку сортировка выполняется позже `SELECT`, для указания столбцов, по которым выполняется сортировка,  \n",
    "**можно** использовать имена, присвоенные им после `SELECT`, а также порядковый номер столбца в перечислении."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aae65641",
   "metadata": {},
   "source": [
    "**Пример**  \n",
    "Вывести название, автора и цены книг.  \n",
    "Информацию  отсортировать по названиям книг в алфавитном порядке."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "5e385cb3",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>author</th>\n",
       "            <th>price</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>540.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>799.01</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Идиот</td>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>460.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>670.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>Есенин С.А.</td>\n",
       "            <td>650.00</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Белая гвардия', 'Булгаков М.А.', Decimal('540.50')),\n",
       " ('Братья Карамазовы', 'Достоевский Ф.М.', Decimal('799.01')),\n",
       " ('Идиот', 'Достоевский Ф.М.', Decimal('460.00')),\n",
       " ('Мастер и Маргарита', 'Булгаков М.А.', Decimal('670.99')),\n",
       " ('Стихотворения и поэмы', 'Есенин С.А.', Decimal('650.00'))]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, author, price\n",
    "  FROM book\n",
    " ORDER BY title;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4a5f6eb",
   "metadata": {},
   "source": [
    "Аналогичный результат получится при использовании запроса  \n",
    "`ORDER BY 1;`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08b688ae",
   "metadata": {},
   "source": [
    "**Задание**  \n",
    "Вывести  автора и название  книг, количество которых принадлежит интервалу от 2 до 14 (включая границы).  \n",
    "Информацию  отсортировать сначала по авторам (в обратном алфавитном порядке), а затем по названиям книг (по алфавиту)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "98426879",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>author</th>\n",
       "            <th>title</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>Братья Карамазовы</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>Идиот</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>Белая гвардия</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Достоевский Ф.М.', 'Братья Карамазовы'),\n",
       " ('Достоевский Ф.М.', 'Идиот'),\n",
       " ('Булгаков М.А.', 'Белая гвардия'),\n",
       " ('Булгаков М.А.', 'Мастер и Маргарита')]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT author, title\n",
    "  FROM book\n",
    " WHERE amount BETWEEN 2 AND 14\n",
    " ORDER BY author DESC, title;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb0a5314",
   "metadata": {},
   "source": [
    "### Выборка данных, оператор` LIKE`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a24727e2",
   "metadata": {},
   "source": [
    "| Символ-шаблон | Описание | Пример |\n",
    "|---|---|---|\n",
    "| `%` | Любая строка, <br>содержащая ноль или более символов | `SELECT * FROM book WHERE author LIKE '%М.%'`<br>выполняет поиск и выдает все книги, <br>инициалы авторов которых содержат _М._ |\n",
    "| `_` (подчеркивание) | Любой одиночный символ | `SELECT * FROM book WHERE title LIKE 'Поэм_'`<br>выполняет поиск и выдает все книги, <br>названия которых либо _Поэма_, либо _Поэмы_ и пр. |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c4d9fbe",
   "metadata": {},
   "source": [
    "**Пример 1**  \n",
    "Вывести названия книг, начинающихся с буквы «Б».  \n",
    "_Строчные и прописные буквы в строках эквивалентны._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "03699ade",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Белая гвардия</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Братья Карамазовы</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Белая гвардия',), ('Братья Карамазовы',)]"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title\n",
    "  FROM book\n",
    " WHERE title LIKE 'б%';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12182061",
   "metadata": {},
   "source": [
    "**Пример 2**  \n",
    "Вывести название книг, состоящих ровно из 5 букв."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "1f039b88",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Идиот</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Идиот',)]"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title\n",
    "  FROM book\n",
    " WHERE title LIKE '_____';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34658679",
   "metadata": {},
   "source": [
    "**Пример 3**  \n",
    "Вывести книги, название которых длиннее 5 символов:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "3e78f63c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Белая гвардия</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Братья Карамазовы</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Мастер и Маргарита',),\n",
       " ('Белая гвардия',),\n",
       " ('Братья Карамазовы',),\n",
       " ('Стихотворения и поэмы',)]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title\n",
    "  FROM book\n",
    " WHERE title LIKE '______%';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a788a80",
   "metadata": {},
   "source": [
    "**Пример 4**  \n",
    "Вывести названия книг, которые содержат букву \"и\" как отдельное слово, если считать,  \n",
    "что слова в названии отделяются друг от друга пробелами и не содержат знаков препинания.\n",
    "\n",
    ">В качестве обязательного символа ( \"_\"), может быть и пробел,  \n",
    "но, к сожалению, шаблоны для `LIKE` не позволяют исключить какой-то символ.  \n",
    "Это можно сделать только с помощью регулярных выражений (будут рассмотрены в уроке 3.5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "8f9b43e0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Мастер и Маргарита',), ('Стихотворения и поэмы',)]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title\n",
    "  FROM book\n",
    " WHERE title LIKE '_% и _%'\n",
    "    OR title LIKE 'и _%'\n",
    "    OR title LIKE '_% и'\n",
    "    OR title LIKE 'и';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3199c7bb",
   "metadata": {},
   "source": [
    "**Пример 5**  \n",
    "Вывести названия книг, которые состоят ровно из одного слова,  \n",
    "если считать, что слова в названии отделяются друг от друга пробелами ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "992c6dc7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Идиот</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Идиот',)]"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title\n",
    "  FROM book\n",
    " WHERE title NOT LIKE '% %';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abf32d28",
   "metadata": {},
   "source": [
    "### Задание  \n",
    "*Для следующего задания необходимо добавить новые записи. (book_id: 6-10)*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df9da461",
   "metadata": {},
   "source": [
    "#### Создание дополнительных записей"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "5029f17c",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>book_id</th>\n",
       "            <th>title</th>\n",
       "            <th>author</th>\n",
       "            <th>price</th>\n",
       "            <th>amount</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>Мастер и Маргарита</td>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>670.99</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>Белая гвардия</td>\n",
       "            <td>Булгаков М.А.</td>\n",
       "            <td>540.50</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Идиот</td>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>460.00</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Братья Карамазовы</td>\n",
       "            <td>Достоевский Ф.М.</td>\n",
       "            <td>799.01</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>Есенин С.А.</td>\n",
       "            <td>650.00</td>\n",
       "            <td>15</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>None</td>\n",
       "            <td>Иванов С.С.</td>\n",
       "            <td>50.00</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "            <td>Дети полуночи</td>\n",
       "            <td>Рушди Салман</td>\n",
       "            <td>950.00</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</td>\n",
       "            <td>Лирика</td>\n",
       "            <td>Гумилев Н.С.</td>\n",
       "            <td>460.00</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9</td>\n",
       "            <td>Поэмы</td>\n",
       "            <td>Бехтерев С.С.</td>\n",
       "            <td>460.00</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>Капитанская дочка</td>\n",
       "            <td>Пушкин А.С.</td>\n",
       "            <td>520.50</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Мастер и Маргарита', 'Булгаков М.А.', Decimal('670.99'), 3),\n",
       " (2, 'Белая гвардия', 'Булгаков М.А.', Decimal('540.50'), 5),\n",
       " (3, 'Идиот', 'Достоевский Ф.М.', Decimal('460.00'), 10),\n",
       " (4, 'Братья Карамазовы', 'Достоевский Ф.М.', Decimal('799.01'), 2),\n",
       " (5, 'Стихотворения и поэмы', 'Есенин С.А.', Decimal('650.00'), 15),\n",
       " (6, None, 'Иванов С.С.', Decimal('50.00'), 10),\n",
       " (7, 'Дети полуночи', 'Рушди Салман', Decimal('950.00'), 5),\n",
       " (8, 'Лирика', 'Гумилев Н.С.', Decimal('460.00'), 10),\n",
       " (9, 'Поэмы', 'Бехтерев С.С.', Decimal('460.00'), 10),\n",
       " (10, 'Капитанская дочка', 'Пушкин А.С.', Decimal('520.50'), 7)]"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Чтение файла в DataFrame\n",
    "import pandas as pd\n",
    "file = 'tables/book_extra.csv'\n",
    "df = pd.read_csv(file)\n",
    "\n",
    "# Запись данных в таблицу из DataFrame\n",
    "types = {\n",
    "    'book_id': sqlalchemy.Integer(),\n",
    "    'price'  : sqlalchemy.Numeric(precision=8, scale=2),\n",
    "    'amount' : sqlalchemy.Integer()\n",
    "}\n",
    "df.to_sql('book', con=engine, index=False, if_exists='append', dtype=types, method='multi')\n",
    "\n",
    "%sql SELECT * FROM book;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "917708ca",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8271e668",
   "metadata": {},
   "source": [
    "Вывести название и автора тех книг, название которых состоит из двух и более слов, а инициалы автора содержат букву «С».  \n",
    "Считать, что в названии слова отделяются друг от друга пробелами и не содержат знаков препинания,  \n",
    "между фамилией автора и инициалами обязателен пробел,  \n",
    "инициалы записываются без пробела в формате: буква, точка, буква, точка.  \n",
    "Информацию отсортировать по названию книги в алфавитном порядке."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "442071f3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>author</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Капитанская дочка</td>\n",
       "            <td>Пушкин А.С.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Стихотворения и поэмы</td>\n",
       "            <td>Есенин С.А.</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Капитанская дочка', 'Пушкин А.С.'),\n",
       " ('Стихотворения и поэмы', 'Есенин С.А.')]"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, author\n",
    "  FROM book\n",
    " WHERE title  LIKE '_% _%'\n",
    "   AND author LIKE '_% %С.%'\n",
    " ORDER BY title;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2cc4788",
   "metadata": {},
   "source": [
    "#### Удаление дополнительных записей"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "97c45c04",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://user:***@localhost:3306/stepik\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DELETE FROM book\n",
    " WHERE book_id >= 6;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}