31_sql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import os\n",
    "import shutil"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'3.41.2'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlite3.sqlite_version"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "dir_name = 'db-temp-dir'\n",
    "if os.path.exists(dir_name):\n",
    "    shutil.rmtree(dir_name)\n",
    "os.makedirs(dir_name)\n",
    "con = sqlite3.connect(os.path.join(dir_name, 'intro1.db'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def select(sql):\n",
    "    return pd.read_sql(sql, con)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Интерактивный тренажёр на платформе Stepik\n",
    "\n",
    "Далее представлены SQL-запросы, созданные во время работы на [интерактивном тренажёре по SQL на платформе Stepik](https://stepik.org/course/63054/promo)."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Основы реляционной модели и SQL"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Выборка данных"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7faaa30c7ec0>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor = con.cursor()\n",
    "cursor.execute(\n",
    "    '''\n",
    "    CREATE TABLE book(\n",
    "        book_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        title VARCHAR(50),\n",
    "        author VARCHAR(30),\n",
    "        price DECIMAL(8, 2),\n",
    "        amount INT\n",
    "    );\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7faaa30c7ec0>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.executescript(\n",
    "    '''\n",
    "    INSERT INTO book(title,author,price,amount)\n",
    "    VALUES (\"Мастер и Маргарита\",\"Булгаков М.А.\", 670.99,3);\n",
    "\n",
    "    INSERT INTO book(title,author,price,amount)\n",
    "    VALUES (\"Белая гвардия\",\"Булгаков М.А.\", 540.50,5);\n",
    "\n",
    "    INSERT INTO book(title,author,price,amount)\n",
    "    VALUES (\"Идиот\",\"Достоевский Ф.М.\", 460.00,10);\n",
    "\n",
    "    INSERT INTO book(title,author,price,amount)\n",
    "    VALUES (\"Братья Карамазовы\",\"Достоевский Ф.М.\", 799.01,2);\n",
    "\n",
    "    INSERT INTO book(title,author,price,amount)\n",
    "    VALUES (\"Стихотворения и поэмы\",\"Есенин С.А.\", 650.00,15);\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select( \n",
    "    '''\n",
    "    SELECT * FROM book;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>author</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "      <td>670.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>540.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Идиот</td>\n",
       "      <td>460.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Братья Карамазовы</td>\n",
       "      <td>799.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>Стихотворения и поэмы</td>\n",
       "      <td>650.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             author                  title   price\n",
       "0     Булгаков М.А.     Мастер и Маргарита  670.99\n",
       "1     Булгаков М.А.          Белая гвардия  540.50\n",
       "2  Достоевский Ф.М.                  Идиот  460.00\n",
       "3  Достоевский Ф.М.      Братья Карамазовы  799.01\n",
       "4       Есенин С.А.  Стихотворения и поэмы  650.00"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT author, title, price FROM book;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Название</th>\n",
       "      <th>Автор</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Идиот</td>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Братья Карамазовы</td>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Стихотворения и поэмы</td>\n",
       "      <td>Есенин С.А.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Название             Автор\n",
       "0     Мастер и Маргарита     Булгаков М.А.\n",
       "1          Белая гвардия     Булгаков М.А.\n",
       "2                  Идиот  Достоевский Ф.М.\n",
       "3      Братья Карамазовы  Достоевский Ф.М.\n",
       "4  Стихотворения и поэмы       Есенин С.А."
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT title as Название, author AS Автор\n",
    "    FROM book;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>title</th>\n",
       "      <th>amount</th>\n",
       "      <th>pack</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "      <td>3</td>\n",
       "      <td>4.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>5</td>\n",
       "      <td>8.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Идиот</td>\n",
       "      <td>10</td>\n",
       "      <td>16.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Братья Карамазовы</td>\n",
       "      <td>2</td>\n",
       "      <td>3.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Стихотворения и поэмы</td>\n",
       "      <td>15</td>\n",
       "      <td>24.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   title  amount   pack\n",
       "0     Мастер и Маргарита       3   4.95\n",
       "1          Белая гвардия       5   8.25\n",
       "2                  Идиот      10  16.50\n",
       "3      Братья Карамазовы       2   3.30\n",
       "4  Стихотворения и поэмы      15  24.75"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT title,amount,amount*1.65 AS pack\n",
    "    FROM book;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>title</th>\n",
       "      <th>author</th>\n",
       "      <th>amount</th>\n",
       "      <th>new_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>3</td>\n",
       "      <td>469.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>5</td>\n",
       "      <td>378.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Идиот</td>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>10</td>\n",
       "      <td>322.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Братья Карамазовы</td>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>2</td>\n",
       "      <td>559.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Стихотворения и поэмы</td>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>15</td>\n",
       "      <td>455.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   title            author  amount  new_price\n",
       "0     Мастер и Маргарита     Булгаков М.А.       3     469.69\n",
       "1          Белая гвардия     Булгаков М.А.       5     378.35\n",
       "2                  Идиот  Достоевский Ф.М.      10     322.00\n",
       "3      Братья Карамазовы  Достоевский Ф.М.       2     559.31\n",
       "4  Стихотворения и поэмы       Есенин С.А.      15     455.00"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT title,author,amount,ROUND(price*0.7,2) AS new_price\n",
    "    FROM book;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>author</th>\n",
       "      <th>title</th>\n",
       "      <th>new_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "      <td>738.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>594.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Идиот</td>\n",
       "      <td>460.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Братья Карамазовы</td>\n",
       "      <td>799.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>Стихотворения и поэмы</td>\n",
       "      <td>682.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             author                  title  new_price\n",
       "0     Булгаков М.А.     Мастер и Маргарита     738.09\n",
       "1     Булгаков М.А.          Белая гвардия     594.55\n",
       "2  Достоевский Ф.М.                  Идиот     460.00\n",
       "3  Достоевский Ф.М.      Братья Карамазовы     799.01\n",
       "4       Есенин С.А.  Стихотворения и поэмы     682.50"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT author,title,\n",
    "    IIF(author = \"Булгаков М.А.\",ROUND(price*1.1,2),IIF(author = \"Есенин С.А.\", ROUND(price*1.05,2),price)) AS new_price\n",
    "    FROM book;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>author</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "      <td>670.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>540.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Братья Карамазовы</td>\n",
       "      <td>799.01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             author               title   price\n",
       "0     Булгаков М.А.  Мастер и Маргарита  670.99\n",
       "1     Булгаков М.А.       Белая гвардия  540.50\n",
       "2  Достоевский Ф.М.   Братья Карамазовы  799.01"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT author,title,price\n",
    "    FROM book\n",
    "    WHERE amount < 10;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>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>Стихотворения и поэмы</td>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>650</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   title       author  price  amount\n",
       "0  Стихотворения и поэмы  Есенин С.А.    650      15"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT title,author,price,amount\n",
    "    FROM book\n",
    "    WHERE (price<500 OR price>600) and amount*price >= 5000;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>title</th>\n",
       "      <th>author</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Братья Карамазовы</td>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                title            author\n",
       "0  Мастер и Маргарита     Булгаков М.А.\n",
       "1       Белая гвардия     Булгаков М.А.\n",
       "2   Братья Карамазовы  Достоевский Ф.М."
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT title,author\n",
    "    FROM book\n",
    "    WHERE (price BETWEEN 540.50 AND 800) AND amount IN (2,3,5,7);\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>author</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Братья Карамазовы</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Идиот</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Белая гвардия</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             author               title\n",
       "0  Достоевский Ф.М.   Братья Карамазовы\n",
       "1  Достоевский Ф.М.               Идиот\n",
       "2     Булгаков М.А.       Белая гвардия\n",
       "3     Булгаков М.А.  Мастер и Маргарита"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT author,title\n",
    "    FROM book\n",
    "    WHERE (amount BETWEEN 2 AND 14)\n",
    "    ORDER BY author DESC, title ASC;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>title</th>\n",
       "      <th>author</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Стихотворения и поэмы</td>\n",
       "      <td>Есенин С.А.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   title       author\n",
       "0  Стихотворения и поэмы  Есенин С.А."
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT title,author\n",
    "    FROM book\n",
    "    WHERE title LIKE \"_% _%\" and author LIKE \"%С.%\"\n",
    "    ORDER BY title ASC;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Автор книги</th>\n",
       "      <th>Название книги</th>\n",
       "      <th>Новая цена книги</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "      <td>805.188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>648.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Идиот</td>\n",
       "      <td>552.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Братья Карамазовы</td>\n",
       "      <td>958.812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>Стихотворения и поэмы</td>\n",
       "      <td>780.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Автор книги         Название книги  Новая цена книги\n",
       "0     Булгаков М.А.     Мастер и Маргарита           805.188\n",
       "1     Булгаков М.А.          Белая гвардия           648.600\n",
       "2  Достоевский Ф.М.                  Идиот           552.000\n",
       "3  Достоевский Ф.М.      Братья Карамазовы           958.812\n",
       "4       Есенин С.А.  Стихотворения и поэмы           780.000"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT author as \"Автор книги\",title as \"Название книги\",price*1.2 as \"Новая цена книги\"\n",
    "    FROM book\n",
    "    ORDER BY author ASC;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Запросы, групповые операции"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   amount\n",
       "0       2\n",
       "1       3\n",
       "2       5\n",
       "3      10\n",
       "4      15"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT amount\n",
    "    FROM book\n",
    "    GROUP BY amount;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Автор</th>\n",
       "      <th>Различных_книг</th>\n",
       "      <th>Количество_экземпляров</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Автор  Различных_книг  Количество_экземпляров\n",
       "0     Булгаков М.А.               2                       8\n",
       "1  Достоевский Ф.М.               2                      12\n",
       "2       Есенин С.А.               1                      15"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT author AS \"Автор\", COUNT(amount) AS Различных_книг, SUM(amount) AS Количество_экземпляров\n",
    "    FROM book\n",
    "    GROUP BY author;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>author</th>\n",
       "      <th>Минимальная_цена</th>\n",
       "      <th>Максимальная_цена</th>\n",
       "      <th>Средняя_цена</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>540.5</td>\n",
       "      <td>670.99</td>\n",
       "      <td>605.745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>460.0</td>\n",
       "      <td>799.01</td>\n",
       "      <td>629.505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>650.0</td>\n",
       "      <td>650.00</td>\n",
       "      <td>650.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             author  Минимальная_цена  Максимальная_цена  Средняя_цена\n",
       "0     Булгаков М.А.             540.5             670.99       605.745\n",
       "1  Достоевский Ф.М.             460.0             799.01       629.505\n",
       "2       Есенин С.А.             650.0             650.00       650.000"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT author, MIN(price) AS Минимальная_цена, MAX(price) AS Максимальная_цена, AVG(price) AS Средняя_цена\n",
    "    FROM book\n",
    "    GROUP BY author;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>author</th>\n",
       "      <th>Стоимость</th>\n",
       "      <th>НДС</th>\n",
       "      <th>Стоимость_без_НДС</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>4715.47</td>\n",
       "      <td>719.31</td>\n",
       "      <td>3996.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>6198.02</td>\n",
       "      <td>945.46</td>\n",
       "      <td>5252.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>9750.00</td>\n",
       "      <td>1487.29</td>\n",
       "      <td>8262.71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             author  Стоимость      НДС  Стоимость_без_НДС\n",
       "0     Булгаков М.А.    4715.47   719.31            3996.16\n",
       "1  Достоевский Ф.М.    6198.02   945.46            5252.56\n",
       "2       Есенин С.А.    9750.00  1487.29            8262.71"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT author, SUM(price*amount) AS Стоимость, ROUND((SUM(price*amount)*0.18)/1.18,2) AS НДС, ROUND(SUM(price*amount)/1.18,2) AS Стоимость_без_НДС\n",
    "    FROM book\n",
    "    GROUP BY author;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>Минимальная_цена</th>\n",
       "      <th>Максимальная_цена</th>\n",
       "      <th>Средняя_цена</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>460</td>\n",
       "      <td>799.01</td>\n",
       "      <td>624.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Минимальная_цена  Максимальная_цена  Средняя_цена\n",
       "0               460             799.01         624.1"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT MIN(price) AS Минимальная_цена, MAX(price) AS Максимальная_цена, ROUND(AVG(price),2) AS Средняя_цена\n",
    "    FROM book;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>Средняя_цена</th>\n",
       "      <th>Стоимость</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>500.25</td>\n",
       "      <td>7302.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Средняя_цена  Стоимость\n",
       "0        500.25     7302.5"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT ROUND(AVG(price),2) AS Средняя_цена, SUM(price*amount) AS Стоимость\n",
    "    FROM book\n",
    "    WHERE amount BETWEEN 5 and 14;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>author</th>\n",
       "      <th>Стоимость</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>9750</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        author  Стоимость\n",
       "0  Есенин С.А.       9750"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT author, SUM(price*amount) AS Стоимость\n",
    "    FROM book\n",
    "    WHERE title <> \"Идиот\" and title <> \"Белая гвардия\"\n",
    "    GROUP BY author\n",
    "    HAVING SUM(price*amount)>5000\n",
    "    ORDER BY Стоимость DESC;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>author</th>\n",
       "      <th>Стоимость</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>9750.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>2012.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>1598.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             author  Стоимость\n",
       "0       Есенин С.А.    9750.00\n",
       "1     Булгаков М.А.    2012.97\n",
       "2  Достоевский Ф.М.    1598.02"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT author, SUM(price*amount) AS Стоимость\n",
    "    FROM book\n",
    "    WHERE title <> \"Идиот\" and title <> \"Белая гвардия\"\n",
    "    GROUP BY author\n",
    "    HAVING SUM(price*amount)>1000 and MIN(price)<900\n",
    "    ORDER BY Стоимость DESC;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Вложенные запросы"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>author</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>540.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Идиот</td>\n",
       "      <td>460.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             author          title  price\n",
       "0     Булгаков М.А.  Белая гвардия  540.5\n",
       "1  Достоевский Ф.М.          Идиот  460.0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "\t'''\n",
    "\tSELECT author, title, price\n",
    "\tFROM book\n",
    "\tWHERE price <= (\n",
    "\t\t\tSELECT AVG(price)\n",
    "\t\t\tFROM book\n",
    "\t\t)\n",
    "\tORDER BY price DESC;\n",
    "\t'''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>author</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Идиот</td>\n",
       "      <td>460.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>540.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             author          title  price\n",
       "0  Достоевский Ф.М.          Идиот  460.0\n",
       "1     Булгаков М.А.  Белая гвардия  540.5"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT author, title, price\n",
    "    FROM book\n",
    "    WHERE price - (SELECT MIN(price) from book) <= 150\n",
    "    ORDER BY price ASC;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>author</th>\n",
       "      <th>title</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Идиот</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>Братья Карамазовы</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>Стихотворения и поэмы</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             author                  title  amount\n",
       "0     Булгаков М.А.     Мастер и Маргарита       3\n",
       "1     Булгаков М.А.          Белая гвардия       5\n",
       "2  Достоевский Ф.М.                  Идиот      10\n",
       "3  Достоевский Ф.М.      Братья Карамазовы       2\n",
       "4       Есенин С.А.  Стихотворения и поэмы      15"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "    '''\n",
    "    SELECT author, title, amount\n",
    "    FROM book\n",
    "    WHERE amount IN (\n",
    "            SELECT amount\n",
    "            FROM book\n",
    "            GROUP BY amount\n",
    "            HAVING COUNT(amount) = 1\n",
    "            );\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>title</th>\n",
       "      <th>author</th>\n",
       "      <th>amount</th>\n",
       "      <th>Заказ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Идиот</td>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Братья Карамазовы</td>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                title            author  amount  Заказ\n",
       "0  Мастер и Маргарита     Булгаков М.А.       3     12\n",
       "1       Белая гвардия     Булгаков М.А.       5     10\n",
       "2               Идиот  Достоевский Ф.М.      10      5\n",
       "3   Братья Карамазовы  Достоевский Ф.М.       2     13"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "'''\n",
    "SELECT title, author, amount, (SELECT MAX(amount) FROM book) - amount as Заказ\n",
    "FROM book\n",
    "WHERE amount <> (SELECT MAX(amount) FROM book);\n",
    "'''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>title</th>\n",
       "      <th>author</th>\n",
       "      <th>amount</th>\n",
       "      <th>Заказ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Мастер и Маргарита</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Идиот</td>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Братья Карамазовы</td>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Стихотворения и поэмы</td>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   title            author  amount  Заказ\n",
       "0     Мастер и Маргарита     Булгаков М.А.       3     13\n",
       "1          Белая гвардия     Булгаков М.А.       5     11\n",
       "2                  Идиот  Достоевский Ф.М.      10      6\n",
       "3      Братья Карамазовы  Достоевский Ф.М.       2     14\n",
       "4  Стихотворения и поэмы       Есенин С.А.      15      1"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(\n",
    "'''\n",
    "SELECT title, author, amount, (SELECT MAX(amount)+1 FROM book) - amount as Заказ\n",
    "FROM book;\n",
    "'''\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Запросы корректировки данных"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7faaa30c7ec0>"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\n",
    "    '''\n",
    "    CREATE TABLE supply(\n",
    "        supply_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        title VARCHAR(50),\n",
    "        author VARCHAR(30),\n",
    "        price DECIMAL(8, 2),\n",
    "        amount INT\n",
    "    );\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>supply_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>518.99</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Черный человек</td>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>570.20</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Белая гвардия</td>\n",
       "      <td>Булгаков М.А.</td>\n",
       "      <td>540.50</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Идиот</td>\n",
       "      <td>Достоевский Ф.М.</td>\n",
       "      <td>360.80</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   supply_id           title            author   price  amount\n",
       "0          1          Лирика    Пастернак Б.Л.  518.99       2\n",
       "1          2  Черный человек       Есенин С.А.  570.20       6\n",
       "2          3   Белая гвардия     Булгаков М.А.  540.50       7\n",
       "3          4           Идиот  Достоевский Ф.М.  360.80       3"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.executescript(\n",
    "    '''\n",
    "    INSERT INTO supply(title,author,price,amount)\n",
    "    VALUES\n",
    "        ('Лирика','Пастернак Б.Л.', 518.99,2),\n",
    "        ('Черный человек','Есенин С.А.',570.20,6),\n",
    "        ('Белая гвардия','Булгаков М.А.',540.50,7),\n",
    "        ('Идиот','Достоевский Ф.М.',360.80,3);\n",
    "    '''\n",
    ")\n",
    "\n",
    "select(\n",
    "    '''\n",
    "    SELECT * FROM supply;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Лирика</td>\n",
       "      <td>Пастернак Б.Л.</td>\n",
       "      <td>518.99</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Черный человек</td>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>570.20</td>\n",
       "      <td>6</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\n",
       "5        6                 Лирика    Пастернак Б.Л.  518.99       2\n",
       "6        7         Черный человек       Есенин С.А.  570.20       6"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\n",
    "    '''\n",
    "    INSERT INTO book(title,author,price,amount)\n",
    "    SELECT title,author,price,amount\n",
    "    FROM supply\n",
    "    WHERE author != 'Булгаков М.А.' AND author != 'Достоевский Ф.М.';\n",
    "    '''\n",
    ")\n",
    "\n",
    "select(\n",
    "    '''\n",
    "    SELECT * FROM book;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Лирика</td>\n",
       "      <td>Пастернак Б.Л.</td>\n",
       "      <td>518.99</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Черный человек</td>\n",
       "      <td>Есенин С.А.</td>\n",
       "      <td>570.20</td>\n",
       "      <td>6</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\n",
       "5        6                 Лирика    Пастернак Б.Л.  518.99       2\n",
       "6        7         Черный человек       Есенин С.А.  570.20       6"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\n",
    "    '''\n",
    "    INSERT INTO book(title,author,price,amount)\n",
    "    SELECT title,author,price,amount\n",
    "    FROM supply\n",
    "    WHERE author NOT IN (\n",
    "            SELECT author\n",
    "            FROM book\n",
    "            );\n",
    "    '''\n",
    ")\n",
    "\n",
    "select(\n",
    "    '''\n",
    "    SELECT * FROM book;\n",
    "    '''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "notebooks-source-env",
   "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.10.9"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}