sqljupyter.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1 style='background-color: #FAF9F6; color: #11557C; text-align: left; font-family: 'Arial', sans-serif; padding: 20px; border-bottom: 1px solid #11557C;'>\n",
    "    SQL com Jupyter Notebook  \n",
    "</h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Bibliotecas Utilizadas\n",
    "\n",
    " - pandas\n",
    " - ipython-sql\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "#### Preparando o Banco de Dados\n",
    "\n",
    "Para começar, execute o código a seguir. Ele é necessário para criar e preparar o banco de dados e o ambiente para podermos utilizar comandos **SQL** no **Jupyter Notebook**.  \n",
    "Não se preocupe em entender e interpretar esta parte, o objetivo deste notebook são os comandos SQL das próximas células!\n",
    "\n",
    "Como requisito, você deve adicionar **```%%sql```** na célula de código antes de executar um comando SQL, ok?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "Banco de Dados 'bancodados.db' OK.\n",
      "Tabelas 'tbClientes' e 'tbProdutos' OK.\n"
     ]
    }
   ],
   "source": [
    "# Preparando o Banco de Dados\n",
    "\n",
    "import pandas as pd\n",
    "import sqlite3 \n",
    "import os\n",
    "\n",
    "\n",
    "# --- Criação dos DataFrames ---\n",
    "# DataFrame de Clientes\n",
    "dados_clientes = {\n",
    "    'id_cliente': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
    "    'nome': ['João', 'Maria', 'Pedro', 'Ana', 'Carlos', 'Clara', 'Luiz', 'Graziela', 'Beatriz', 'Mariana'],\n",
    "    'idade': [20, 25, 30, 35, 40, 51, 44, 47, 22, 18],\n",
    "    'email': ['joao@email.com', 'maria@email.com', 'pedro@email.com', 'ana@email.com', 'carlos@email.com', 'clara@email.com', 'luiz@email.com', 'grazi@email.com', 'bia@email.com', 'mari@email.com'],\n",
    "    'cidade': ['São Paulo', 'Rio de Janeiro', 'Porto Alegre', 'Belo Horizonte', 'Salvador', 'Recife', 'Santos', 'São Paulo', 'Salvador', 'Guarulhos'],\n",
    "    'estado': ['SP', 'RJ', 'RS', 'MG', 'BA', 'PE', 'SP', 'SP', 'BA', 'SP']\n",
    "}\n",
    "\n",
    "# DataFrame de Pedidos\n",
    "dados_pedidos = {\n",
    "    'id_pedido': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15],\n",
    "    'id_cliente': [1, 1, 1, 1, 2, 2, 3, 3, 3, 3, 3, 3, 4, 6, 6],\n",
    "    'produto': ['celular', 'videogame', 'computador', 'celular', 'videogame', 'televisão', 'computador', 'celular', 'videogame', 'televisão', 'televisão', 'computador', 'celular', 'videogame', 'televisão'],\n",
    "    'marca': ['ACME', 'XPTO', 'ICOMP', 'ACME', 'XPTO', 'ICOMP', 'ACME', 'XPTO', 'ICOMP', 'ACME','ACME','ACME','XPTO','XPTO','XPTO'],\n",
    "    'quantidade': [1, 2, 1, 1, 1, 2, 3, 1, 1, 2, 3, 1, 1, 2, 1],\n",
    "    'preco': [1250.00, 4250.50, 1750.99, 3750.25, 2250.00, 5250.50, 2750.00, 4750.25, 3250.00, 1400.00, 1250.00, 4250.50, 1750.99, 3750.25, 2250.00]\n",
    "}\n",
    "\n",
    "\n",
    "clientes_df = pd.DataFrame(dados_clientes)\n",
    "pedidos_df = pd.DataFrame(dados_pedidos)\n",
    "\n",
    "print(\"\\n\")\n",
    "\n",
    "# criando a conexão\n",
    "if os.path.exists('bancodados.db'):   \n",
    "    print(\"Banco de Dados 'bancodados.db' OK.\")\n",
    "    try:\n",
    "        clientes_df.to_sql('tbClientes', conexao)\n",
    "        pedidos_df.to_sql('tbPedidos', conexao)\n",
    "    except:\n",
    "        print(\"Tabelas 'tbClientes' e 'tbProdutos' OK.\")\n",
    "    %reload_ext sql\n",
    "else:\n",
    "    conexao = sqlite3.connect('bancodados.db')\n",
    "\n",
    "    # criando as  tabelas no banco de dados\n",
    "    clientes_df.to_sql('tbClientes', conexao)\n",
    "    pedidos_df.to_sql('tbPedidos', conexao)\n",
    "\n",
    "    print(\"Banco de Dados 'bancodados.db' OK.\")\n",
    "    print(\"Tabelas 'tbClientes' e 'tbProdutos' OK.\")\n",
    "    \n",
    "    # load the sql module to iPython\n",
    "    %load_ext sql\n",
    "\n",
    "# apontando para o banco de dados que vamos usar\n",
    "%sql sqlite:///bancodados.db"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='indice'></a>\n",
    "----------------\n",
    "#### Índíce\n",
    "\n",
    "- [Consultar todos os dados de uma tabela](#secao-1)\n",
    "- [Consultar os dados de uma ou mais colunas da tabela](#secao-2)\n",
    "- [Filtrar a pesquisa](#secao-3)\n",
    "- [Consultar as primeiras linhas](#secao-4)\n",
    "- [Valores distintos](#secao-5)\n",
    "- [Contagem de valores distintos](#secao-6)\n",
    "- [Agrupamento](#secao-7)\n",
    "- [Ordenação](#secao-8)\n",
    "- [Junções](#secao-9)\n",
    "- [INNER JOIN](#secao-10)\n",
    "- [LEFT JOIN](#secao-11)\n",
    "- [Valor Mínimo](#secao-12)\n",
    "- [Valor Máximo](#secao-13)\n",
    "- [Média](#secao-14)\n",
    "- [Filtrar agregações](#secao-15)\n",
    "- [Criar tabela](#secao-16)\n",
    "- [Inserir dados em uma tabela](#secao-17)\n",
    "- [Atualizar dados em uma tabela](#secao-18)\n",
    "- [Apagar uma tabela](#secao-19)\n",
    "- [Agora é sua vez!](#secao-20)\n",
    "\n",
    "\n",
    "  \n",
    "**Estrutura do Banco de Dados:**  \n",
    "\n",
    "![image.png](https://res.cloudinary.com/ddrdmilou/image/upload/w_750/v1699096417/tabelas_vpmlxm.png)\n",
    "\n",
    "\n",
    "Vamos trabalhar com duas **tabelas**: a tabela de *Clientes* (**tbClientes**) e a tabela de *Pedidos* (**tbPedidos**)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-1'></a>Consultar todos os dados de uma tabela  \n",
    "</h3>\n",
    "\n",
    "Comando: **```SELECT```** * **```FROM```** *tabela*\n",
    "\n",
    "A cláusula 'SELECT' é utilizada para recuperar dados de um banco de dados.  \n",
    "A cláusula 'FROM' é usada para especificar a tabela da qual você deseja recuperar os dados. \n",
    "\n",
    "\n",
    "Quem são nossos clientes? Vamos executar o comando a seguir para descobrir!\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>index</th>\n",
       "            <th>id_cliente</th>\n",
       "            <th>nome</th>\n",
       "            <th>idade</th>\n",
       "            <th>email</th>\n",
       "            <th>cidade</th>\n",
       "            <th>estado</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>0</td>\n",
       "            <td>1</td>\n",
       "            <td>João</td>\n",
       "            <td>20</td>\n",
       "            <td>joao@email.com</td>\n",
       "            <td>São Paulo</td>\n",
       "            <td>SP</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>2</td>\n",
       "            <td>Maria</td>\n",
       "            <td>25</td>\n",
       "            <td>maria@email.com</td>\n",
       "            <td>Rio de Janeiro</td>\n",
       "            <td>RJ</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>30</td>\n",
       "            <td>pedro@email.com</td>\n",
       "            <td>Porto Alegre</td>\n",
       "            <td>RS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>4</td>\n",
       "            <td>Ana</td>\n",
       "            <td>35</td>\n",
       "            <td>ana@email.com</td>\n",
       "            <td>Belo Horizonte</td>\n",
       "            <td>MG</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>5</td>\n",
       "            <td>Carlos</td>\n",
       "            <td>40</td>\n",
       "            <td>carlos@email.com</td>\n",
       "            <td>Salvador</td>\n",
       "            <td>BA</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>6</td>\n",
       "            <td>Clara</td>\n",
       "            <td>51</td>\n",
       "            <td>clara@email.com</td>\n",
       "            <td>Recife</td>\n",
       "            <td>PE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>7</td>\n",
       "            <td>Luiz</td>\n",
       "            <td>44</td>\n",
       "            <td>luiz@email.com</td>\n",
       "            <td>Santos</td>\n",
       "            <td>SP</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "            <td>8</td>\n",
       "            <td>Graziela</td>\n",
       "            <td>47</td>\n",
       "            <td>grazi@email.com</td>\n",
       "            <td>São Paulo</td>\n",
       "            <td>SP</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</td>\n",
       "            <td>9</td>\n",
       "            <td>Beatriz</td>\n",
       "            <td>22</td>\n",
       "            <td>bia@email.com</td>\n",
       "            <td>Salvador</td>\n",
       "            <td>BA</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9</td>\n",
       "            <td>10</td>\n",
       "            <td>Mariana</td>\n",
       "            <td>18</td>\n",
       "            <td>mari@email.com</td>\n",
       "            <td>Guarulhos</td>\n",
       "            <td>SP</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 1, 'João', 20, 'joao@email.com', 'São Paulo', 'SP'),\n",
       " (1, 2, 'Maria', 25, 'maria@email.com', 'Rio de Janeiro', 'RJ'),\n",
       " (2, 3, 'Pedro', 30, 'pedro@email.com', 'Porto Alegre', 'RS'),\n",
       " (3, 4, 'Ana', 35, 'ana@email.com', 'Belo Horizonte', 'MG'),\n",
       " (4, 5, 'Carlos', 40, 'carlos@email.com', 'Salvador', 'BA'),\n",
       " (5, 6, 'Clara', 51, 'clara@email.com', 'Recife', 'PE'),\n",
       " (6, 7, 'Luiz', 44, 'luiz@email.com', 'Santos', 'SP'),\n",
       " (7, 8, 'Graziela', 47, 'grazi@email.com', 'São Paulo', 'SP'),\n",
       " (8, 9, 'Beatriz', 22, 'bia@email.com', 'Salvador', 'BA'),\n",
       " (9, 10, 'Mariana', 18, 'mari@email.com', 'Guarulhos', 'SP')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "SELECT * FROM tbClientes \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-2'></a> Consultar os dados de uma ou mais colunas da tabela\n",
    "</h3>\n",
    "\n",
    "Comando: **```SELECT```** *coluna1, coluna2, colunaN* **```FROM```** *tabela*\n",
    "\n",
    "Quais são os nomes dos clientes? E a idade deles?\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>nome</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>João</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Maria</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Pedro</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ana</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Carlos</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Clara</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luiz</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Graziela</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Beatriz</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mariana</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('João',),\n",
       " ('Maria',),\n",
       " ('Pedro',),\n",
       " ('Ana',),\n",
       " ('Carlos',),\n",
       " ('Clara',),\n",
       " ('Luiz',),\n",
       " ('Graziela',),\n",
       " ('Beatriz',),\n",
       " ('Mariana',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    nome \n",
    "FROM \n",
    "    tbClientes\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>nome</th>\n",
       "            <th>idade</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>João</td>\n",
       "            <td>20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Maria</td>\n",
       "            <td>25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Pedro</td>\n",
       "            <td>30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ana</td>\n",
       "            <td>35</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Carlos</td>\n",
       "            <td>40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Clara</td>\n",
       "            <td>51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luiz</td>\n",
       "            <td>44</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Graziela</td>\n",
       "            <td>47</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Beatriz</td>\n",
       "            <td>22</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mariana</td>\n",
       "            <td>18</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('João', 20),\n",
       " ('Maria', 25),\n",
       " ('Pedro', 30),\n",
       " ('Ana', 35),\n",
       " ('Carlos', 40),\n",
       " ('Clara', 51),\n",
       " ('Luiz', 44),\n",
       " ('Graziela', 47),\n",
       " ('Beatriz', 22),\n",
       " ('Mariana', 18)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    nome, idade \n",
    "FROM \n",
    "    tbClientes \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-3'></a> Filtrar a pesquisa\n",
    "</h3>\n",
    "\n",
    "\n",
    "cláusula: **```WHERE```** \n",
    "\n",
    "A cláusula 'WHERE' é usada em consultas SQL para filtrar os resultados de uma consulta\n",
    "\n",
    "Exemplo: Quais clientes têm acima de 30 anos? Quais são do estado de SP?\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>nome</th>\n",
       "            <th>idade</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Ana</td>\n",
       "            <td>35</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Carlos</td>\n",
       "            <td>40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Clara</td>\n",
       "            <td>51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luiz</td>\n",
       "            <td>44</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Graziela</td>\n",
       "            <td>47</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Ana', 35), ('Carlos', 40), ('Clara', 51), ('Luiz', 44), ('Graziela', 47)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    nome,\n",
    "    idade\n",
    "FROM \n",
    "    tbClientes \n",
    "WHERE \n",
    "    idade > 30\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>nome</th>\n",
       "            <th>idade</th>\n",
       "            <th>cidade</th>\n",
       "            <th>estado</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Luiz</td>\n",
       "            <td>44</td>\n",
       "            <td>Santos</td>\n",
       "            <td>SP</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Graziela</td>\n",
       "            <td>47</td>\n",
       "            <td>São Paulo</td>\n",
       "            <td>SP</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Luiz', 44, 'Santos', 'SP'), ('Graziela', 47, 'São Paulo', 'SP')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    nome,\n",
    "    idade,\n",
    "    cidade,\n",
    "    estado\n",
    "FROM \n",
    "    tbClientes \n",
    "WHERE \n",
    "    idade > 30\n",
    "    AND estado = \"SP\"\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-4'></a> Consultar as primeiras linhas\n",
    "</h3>\n",
    "\n",
    "cláusula: **```LIMIT```**\n",
    "\n",
    "A cláusula 'LIMIT' é usada em consultas SQL para restringir o número de linhas retornadas em um resultado de consulta. \n",
    "\n",
    "Exemplo: Verificando uma amostra dos 5 primeiros registros da tabela.\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>nome</th>\n",
       "            <th>idade</th>\n",
       "            <th>cidade</th>\n",
       "            <th>estado</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>João</td>\n",
       "            <td>20</td>\n",
       "            <td>São Paulo</td>\n",
       "            <td>SP</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Maria</td>\n",
       "            <td>25</td>\n",
       "            <td>Rio de Janeiro</td>\n",
       "            <td>RJ</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Pedro</td>\n",
       "            <td>30</td>\n",
       "            <td>Porto Alegre</td>\n",
       "            <td>RS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ana</td>\n",
       "            <td>35</td>\n",
       "            <td>Belo Horizonte</td>\n",
       "            <td>MG</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Carlos</td>\n",
       "            <td>40</td>\n",
       "            <td>Salvador</td>\n",
       "            <td>BA</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('João', 20, 'São Paulo', 'SP'),\n",
       " ('Maria', 25, 'Rio de Janeiro', 'RJ'),\n",
       " ('Pedro', 30, 'Porto Alegre', 'RS'),\n",
       " ('Ana', 35, 'Belo Horizonte', 'MG'),\n",
       " ('Carlos', 40, 'Salvador', 'BA')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    nome,\n",
    "    idade,\n",
    "    cidade,\n",
    "    estado\n",
    "FROM \n",
    "    tbClientes \n",
    "LIMIT 5\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-5'></a> Valores distintos\n",
    "</h3>\n",
    "\n",
    "\n",
    "cláusula: **```DISTINCT```** \n",
    "\n",
    "A cláusula 'DISTINCT' é usada em consultas SQL para retornar apenas valores distintos, ou seja, valores únicos, de uma determinada coluna ou de um conjunto de colunas.\n",
    "\n",
    "\n",
    "Exemplo: De quais estados são meus clientes?\n",
    "\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>estado</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>SP</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RJ</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MG</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BA</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PE</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SP',), ('RJ',), ('RS',), ('MG',), ('BA',), ('PE',)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    DISTINCT estado \n",
    "FROM \n",
    "    tbClientes \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-6'></a> Contagem de valores distintos\n",
    "</h3>\n",
    "\n",
    "\n",
    "cláusulas: **```COUNT```** e **```DISTINCT```**\n",
    "\n",
    "A cláusula 'COUNT' é usada para contar o número de linhas que atendem a uma determinada condição em uma tabela. Quando combinada com 'DISTINCT', o COUNT conta o número de valores únicos em uma coluna ou conjunto de colunas.\n",
    "\n",
    "Exemplo: Quantos estados distintos têm minha tabela de clientes?\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>qt_estados</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6,)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    COUNT(DISTINCT estado) as qt_estados\n",
    "FROM \n",
    "    tbClientes \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-7'></a> Agrupamento\n",
    "</h3>\n",
    "\n",
    "\n",
    "cláusula: **```GROUP BY```**\n",
    "\n",
    "A cláusula 'GROUP BY' é utilizada em consultas SQL para agrupar linhas que possuem valores iguais em uma ou mais colunas, permitindo realizar operações de agregação, como COUNT, SUM, AVG, entre outras, em cada grupo formado. Ela ajuda a criar resumos ou análises agrupadas dos dados com base em valores semelhantes em uma ou mais colunas.\n",
    "\n",
    "Exemplo: Quantos clientes tenho em cada cidade?\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>cidade</th>\n",
       "            <th>qt_clientes</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Belo Horizonte</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Guarulhos</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Porto Alegre</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Recife</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rio de Janeiro</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Salvador</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Santos</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>São Paulo</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Belo Horizonte', 1),\n",
       " ('Guarulhos', 1),\n",
       " ('Porto Alegre', 1),\n",
       " ('Recife', 1),\n",
       " ('Rio de Janeiro', 1),\n",
       " ('Salvador', 2),\n",
       " ('Santos', 1),\n",
       " ('São Paulo', 2)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    cidade, \n",
    "    COUNT(id_cliente) as qt_clientes\n",
    "FROM \n",
    "    tbClientes\n",
    "GROUP BY \n",
    "    Cidade\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>cidade</th>\n",
       "            <th>qt_clientes</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Recife</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Santos</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>São Paulo</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Recife', 1), ('Santos', 1), ('São Paulo', 1)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    cidade, \n",
    "    COUNT(id_cliente) as qt_clientes\n",
    "FROM \n",
    "    tbClientes\n",
    "WHERE \n",
    "    idade > 40\n",
    "GROUP BY \n",
    "    Cidade\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-8'></a> Ordenação\n",
    "</h3>\n",
    "\n",
    "\n",
    "cláusula: **```ORDER BY```**\n",
    "\n",
    "A cláusula 'ORDER BY' é utilizada em consultas SQL para ordenar os resultados de uma consulta de acordo com os valores de uma ou mais colunas. Ela permite classificar os dados retornados em ordem ascendente 'ASC' (do menor para o maior) ou em ordem descendente 'DESC' (do maior para o menor) com base nos valores encontrados em uma ou mais colunas.\n",
    "\n",
    "Vamos colocar as coisas em ordem?\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>nome</th>\n",
       "            <th>idade</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Mariana</td>\n",
       "            <td>18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>João</td>\n",
       "            <td>20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Beatriz</td>\n",
       "            <td>22</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Mariana', 18), ('João', 20), ('Beatriz', 22)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    nome, \n",
    "    idade \n",
    "FROM \n",
    "    tbClientes \n",
    "WHERE \n",
    "    idade < 25\n",
    "ORDER BY \n",
    "    idade\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>nome</th>\n",
       "            <th>idade</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Beatriz</td>\n",
       "            <td>22</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>João</td>\n",
       "            <td>20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mariana</td>\n",
       "            <td>18</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Beatriz', 22), ('João', 20), ('Mariana', 18)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    nome, \n",
    "    idade \n",
    "FROM \n",
    "    tbClientes \n",
    "WHERE \n",
    "    idade < 25\n",
    "ORDER BY \n",
    "    idade DESC\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-9'></a> Junções\n",
    "</h3>\n",
    "\n",
    "Vamos juntar agora a tabela '**tbPedidos**' com a tabela '**tbClientes**'!\n",
    "\n",
    "Consultando todos os registros da tabela de Pedidos:\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>index</th>\n",
       "            <th>id_pedido</th>\n",
       "            <th>id_cliente</th>\n",
       "            <th>produto</th>\n",
       "            <th>marca</th>\n",
       "            <th>quantidade</th>\n",
       "            <th>preco</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>0</td>\n",
       "            <td>1</td>\n",
       "            <td>1</td>\n",
       "            <td>celular</td>\n",
       "            <td>ACME</td>\n",
       "            <td>1</td>\n",
       "            <td>1250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "            <td>videogame</td>\n",
       "            <td>XPTO</td>\n",
       "            <td>2</td>\n",
       "            <td>4250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>3</td>\n",
       "            <td>1</td>\n",
       "            <td>computador</td>\n",
       "            <td>ICOMP</td>\n",
       "            <td>1</td>\n",
       "            <td>1750.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>4</td>\n",
       "            <td>1</td>\n",
       "            <td>celular</td>\n",
       "            <td>ACME</td>\n",
       "            <td>1</td>\n",
       "            <td>3750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>5</td>\n",
       "            <td>2</td>\n",
       "            <td>videogame</td>\n",
       "            <td>XPTO</td>\n",
       "            <td>1</td>\n",
       "            <td>2250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>6</td>\n",
       "            <td>2</td>\n",
       "            <td>televisão</td>\n",
       "            <td>ICOMP</td>\n",
       "            <td>2</td>\n",
       "            <td>5250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>7</td>\n",
       "            <td>3</td>\n",
       "            <td>computador</td>\n",
       "            <td>ACME</td>\n",
       "            <td>3</td>\n",
       "            <td>2750.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "            <td>8</td>\n",
       "            <td>3</td>\n",
       "            <td>celular</td>\n",
       "            <td>XPTO</td>\n",
       "            <td>1</td>\n",
       "            <td>4750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</td>\n",
       "            <td>9</td>\n",
       "            <td>3</td>\n",
       "            <td>videogame</td>\n",
       "            <td>ICOMP</td>\n",
       "            <td>1</td>\n",
       "            <td>3250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9</td>\n",
       "            <td>10</td>\n",
       "            <td>3</td>\n",
       "            <td>televisão</td>\n",
       "            <td>ACME</td>\n",
       "            <td>2</td>\n",
       "            <td>1400.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>11</td>\n",
       "            <td>3</td>\n",
       "            <td>televisão</td>\n",
       "            <td>ACME</td>\n",
       "            <td>3</td>\n",
       "            <td>1250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>12</td>\n",
       "            <td>3</td>\n",
       "            <td>computador</td>\n",
       "            <td>ACME</td>\n",
       "            <td>1</td>\n",
       "            <td>4250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>12</td>\n",
       "            <td>13</td>\n",
       "            <td>4</td>\n",
       "            <td>celular</td>\n",
       "            <td>XPTO</td>\n",
       "            <td>1</td>\n",
       "            <td>1750.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>13</td>\n",
       "            <td>14</td>\n",
       "            <td>6</td>\n",
       "            <td>videogame</td>\n",
       "            <td>XPTO</td>\n",
       "            <td>2</td>\n",
       "            <td>3750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>14</td>\n",
       "            <td>15</td>\n",
       "            <td>6</td>\n",
       "            <td>televisão</td>\n",
       "            <td>XPTO</td>\n",
       "            <td>1</td>\n",
       "            <td>2250.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 1, 1, 'celular', 'ACME', 1, 1250.0),\n",
       " (1, 2, 1, 'videogame', 'XPTO', 2, 4250.5),\n",
       " (2, 3, 1, 'computador', 'ICOMP', 1, 1750.99),\n",
       " (3, 4, 1, 'celular', 'ACME', 1, 3750.25),\n",
       " (4, 5, 2, 'videogame', 'XPTO', 1, 2250.0),\n",
       " (5, 6, 2, 'televisão', 'ICOMP', 2, 5250.5),\n",
       " (6, 7, 3, 'computador', 'ACME', 3, 2750.0),\n",
       " (7, 8, 3, 'celular', 'XPTO', 1, 4750.25),\n",
       " (8, 9, 3, 'videogame', 'ICOMP', 1, 3250.0),\n",
       " (9, 10, 3, 'televisão', 'ACME', 2, 1400.0),\n",
       " (10, 11, 3, 'televisão', 'ACME', 3, 1250.0),\n",
       " (11, 12, 3, 'computador', 'ACME', 1, 4250.5),\n",
       " (12, 13, 4, 'celular', 'XPTO', 1, 1750.99),\n",
       " (13, 14, 6, 'videogame', 'XPTO', 2, 3750.25),\n",
       " (14, 15, 6, 'televisão', 'XPTO', 1, 2250.0)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    * \n",
    "FROM \n",
    "    tbPedidos \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-10'></a> INNER JOIN\n",
    "</h3>\n",
    "\n",
    "cláusula: **```INNER JOIN```** ou **```JOIN```**\n",
    "\n",
    "A cláusula 'INNER JOIN', ou apenas 'JOIN', é utilizada em consultas SQL para combinar linhas de duas ou mais tabelas com base em uma condição especificada, resultando em um conjunto de resultados que contém apenas as linhas que possuem correspondências entre as tabelas envolvidas na junção.\n",
    "\n",
    "Exemplo: Quais clientes fizeram pedidos?\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>id_cliente</th>\n",
       "            <th>nome</th>\n",
       "            <th>id_pedido</th>\n",
       "            <th>produto</th>\n",
       "            <th>quantidade</th>\n",
       "            <th>preco</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>João</td>\n",
       "            <td>1</td>\n",
       "            <td>celular</td>\n",
       "            <td>1</td>\n",
       "            <td>1250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>João</td>\n",
       "            <td>2</td>\n",
       "            <td>videogame</td>\n",
       "            <td>2</td>\n",
       "            <td>4250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>João</td>\n",
       "            <td>3</td>\n",
       "            <td>computador</td>\n",
       "            <td>1</td>\n",
       "            <td>1750.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>João</td>\n",
       "            <td>4</td>\n",
       "            <td>celular</td>\n",
       "            <td>1</td>\n",
       "            <td>3750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>Maria</td>\n",
       "            <td>5</td>\n",
       "            <td>videogame</td>\n",
       "            <td>1</td>\n",
       "            <td>2250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>Maria</td>\n",
       "            <td>6</td>\n",
       "            <td>televisão</td>\n",
       "            <td>2</td>\n",
       "            <td>5250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>7</td>\n",
       "            <td>computador</td>\n",
       "            <td>3</td>\n",
       "            <td>2750.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>8</td>\n",
       "            <td>celular</td>\n",
       "            <td>1</td>\n",
       "            <td>4750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>9</td>\n",
       "            <td>videogame</td>\n",
       "            <td>1</td>\n",
       "            <td>3250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>10</td>\n",
       "            <td>televisão</td>\n",
       "            <td>2</td>\n",
       "            <td>1400.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>11</td>\n",
       "            <td>televisão</td>\n",
       "            <td>3</td>\n",
       "            <td>1250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>12</td>\n",
       "            <td>computador</td>\n",
       "            <td>1</td>\n",
       "            <td>4250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Ana</td>\n",
       "            <td>13</td>\n",
       "            <td>celular</td>\n",
       "            <td>1</td>\n",
       "            <td>1750.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>Clara</td>\n",
       "            <td>14</td>\n",
       "            <td>videogame</td>\n",
       "            <td>2</td>\n",
       "            <td>3750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>Clara</td>\n",
       "            <td>15</td>\n",
       "            <td>televisão</td>\n",
       "            <td>1</td>\n",
       "            <td>2250.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'João', 1, 'celular', 1, 1250.0),\n",
       " (1, 'João', 2, 'videogame', 2, 4250.5),\n",
       " (1, 'João', 3, 'computador', 1, 1750.99),\n",
       " (1, 'João', 4, 'celular', 1, 3750.25),\n",
       " (2, 'Maria', 5, 'videogame', 1, 2250.0),\n",
       " (2, 'Maria', 6, 'televisão', 2, 5250.5),\n",
       " (3, 'Pedro', 7, 'computador', 3, 2750.0),\n",
       " (3, 'Pedro', 8, 'celular', 1, 4750.25),\n",
       " (3, 'Pedro', 9, 'videogame', 1, 3250.0),\n",
       " (3, 'Pedro', 10, 'televisão', 2, 1400.0),\n",
       " (3, 'Pedro', 11, 'televisão', 3, 1250.0),\n",
       " (3, 'Pedro', 12, 'computador', 1, 4250.5),\n",
       " (4, 'Ana', 13, 'celular', 1, 1750.99),\n",
       " (6, 'Clara', 14, 'videogame', 2, 3750.25),\n",
       " (6, 'Clara', 15, 'televisão', 1, 2250.0)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT \n",
    "    tbClientes.id_cliente, \n",
    "    tbClientes.nome, \n",
    "    tbPedidos.id_pedido, \n",
    "    tbPedidos.produto, \n",
    "    tbPedidos.quantidade, \n",
    "    tbPedidos.preco \n",
    "FROM tbClientes \n",
    "    INNER JOIN tbPedidos ON (tbClientes.id_cliente = tbPedidos.id_cliente)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-11'></a> LEFT JOIN\n",
    "</h3>\n",
    "\n",
    "cláusula: **```LEFT JOIN```** \n",
    "\n",
    "A cláusula 'LEFT JOIN' é usada em consultas SQL para combinar as linhas de duas tabelas, retornando todos os registros da tabela à esquerda (tabela base) e apenas os registros correspondentes da tabela à direita, baseado em uma condição de junção.\n",
    "\n",
    "Ao contrário do INNER JOIN, o LEFT JOIN garante que todas as linhas da tabela à esquerda (primeira tabela) sejam incluídas no resultado, independentemente de existirem ou não correspondências na tabela à direita (segunda tabela). Se não houver correspondência na tabela à direita, os valores correspondentes serão nulos no resultado da consulta.\n",
    "\n",
    "\n",
    "Exemplo: Mostre todos os clientes, mesmo os que não fizeram pedidos.\n",
    "\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>id_cliente</th>\n",
       "            <th>nome</th>\n",
       "            <th>id_pedido</th>\n",
       "            <th>produto</th>\n",
       "            <th>quantidade</th>\n",
       "            <th>preco</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>João</td>\n",
       "            <td>1</td>\n",
       "            <td>celular</td>\n",
       "            <td>1</td>\n",
       "            <td>1250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>João</td>\n",
       "            <td>2</td>\n",
       "            <td>videogame</td>\n",
       "            <td>2</td>\n",
       "            <td>4250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>João</td>\n",
       "            <td>3</td>\n",
       "            <td>computador</td>\n",
       "            <td>1</td>\n",
       "            <td>1750.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>João</td>\n",
       "            <td>4</td>\n",
       "            <td>celular</td>\n",
       "            <td>1</td>\n",
       "            <td>3750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>Maria</td>\n",
       "            <td>5</td>\n",
       "            <td>videogame</td>\n",
       "            <td>1</td>\n",
       "            <td>2250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>Maria</td>\n",
       "            <td>6</td>\n",
       "            <td>televisão</td>\n",
       "            <td>2</td>\n",
       "            <td>5250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>7</td>\n",
       "            <td>computador</td>\n",
       "            <td>3</td>\n",
       "            <td>2750.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>8</td>\n",
       "            <td>celular</td>\n",
       "            <td>1</td>\n",
       "            <td>4750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>9</td>\n",
       "            <td>videogame</td>\n",
       "            <td>1</td>\n",
       "            <td>3250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>10</td>\n",
       "            <td>televisão</td>\n",
       "            <td>2</td>\n",
       "            <td>1400.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>11</td>\n",
       "            <td>televisão</td>\n",
       "            <td>3</td>\n",
       "            <td>1250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>12</td>\n",
       "            <td>computador</td>\n",
       "            <td>1</td>\n",
       "            <td>4250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Ana</td>\n",
       "            <td>13</td>\n",
       "            <td>celular</td>\n",
       "            <td>1</td>\n",
       "            <td>1750.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>Carlos</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>Clara</td>\n",
       "            <td>14</td>\n",
       "            <td>videogame</td>\n",
       "            <td>2</td>\n",
       "            <td>3750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>Clara</td>\n",
       "            <td>15</td>\n",
       "            <td>televisão</td>\n",
       "            <td>1</td>\n",
       "            <td>2250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "            <td>Luiz</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</td>\n",
       "            <td>Graziela</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9</td>\n",
       "            <td>Beatriz</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>Mariana</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'João', 1, 'celular', 1, 1250.0),\n",
       " (1, 'João', 2, 'videogame', 2, 4250.5),\n",
       " (1, 'João', 3, 'computador', 1, 1750.99),\n",
       " (1, 'João', 4, 'celular', 1, 3750.25),\n",
       " (2, 'Maria', 5, 'videogame', 1, 2250.0),\n",
       " (2, 'Maria', 6, 'televisão', 2, 5250.5),\n",
       " (3, 'Pedro', 7, 'computador', 3, 2750.0),\n",
       " (3, 'Pedro', 8, 'celular', 1, 4750.25),\n",
       " (3, 'Pedro', 9, 'videogame', 1, 3250.0),\n",
       " (3, 'Pedro', 10, 'televisão', 2, 1400.0),\n",
       " (3, 'Pedro', 11, 'televisão', 3, 1250.0),\n",
       " (3, 'Pedro', 12, 'computador', 1, 4250.5),\n",
       " (4, 'Ana', 13, 'celular', 1, 1750.99),\n",
       " (5, 'Carlos', None, None, None, None),\n",
       " (6, 'Clara', 14, 'videogame', 2, 3750.25),\n",
       " (6, 'Clara', 15, 'televisão', 1, 2250.0),\n",
       " (7, 'Luiz', None, None, None, None),\n",
       " (8, 'Graziela', None, None, None, None),\n",
       " (9, 'Beatriz', None, None, None, None),\n",
       " (10, 'Mariana', None, None, None, None)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    tbClientes.id_cliente, \n",
    "    tbClientes.nome, \n",
    "    tbPedidos.id_pedido, \n",
    "    tbPedidos.produto, \n",
    "    tbPedidos.quantidade, \n",
    "    tbPedidos.preco \n",
    "FROM tbClientes \n",
    "    LEFT JOIN tbPedidos ON (tbClientes.id_cliente = tbPedidos.id_cliente)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>id_cliente</th>\n",
       "            <th>nome</th>\n",
       "            <th>id_pedido</th>\n",
       "            <th>produto</th>\n",
       "            <th>quantidade</th>\n",
       "            <th>preco</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>Maria</td>\n",
       "            <td>6</td>\n",
       "            <td>televisão</td>\n",
       "            <td>2</td>\n",
       "            <td>5250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>8</td>\n",
       "            <td>celular</td>\n",
       "            <td>1</td>\n",
       "            <td>4750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>João</td>\n",
       "            <td>2</td>\n",
       "            <td>videogame</td>\n",
       "            <td>2</td>\n",
       "            <td>4250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>12</td>\n",
       "            <td>computador</td>\n",
       "            <td>1</td>\n",
       "            <td>4250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>João</td>\n",
       "            <td>4</td>\n",
       "            <td>celular</td>\n",
       "            <td>1</td>\n",
       "            <td>3750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>Clara</td>\n",
       "            <td>14</td>\n",
       "            <td>videogame</td>\n",
       "            <td>2</td>\n",
       "            <td>3750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Pedro</td>\n",
       "            <td>9</td>\n",
       "            <td>videogame</td>\n",
       "            <td>1</td>\n",
       "            <td>3250.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Maria', 6, 'televisão', 2, 5250.5),\n",
       " (3, 'Pedro', 8, 'celular', 1, 4750.25),\n",
       " (1, 'João', 2, 'videogame', 2, 4250.5),\n",
       " (3, 'Pedro', 12, 'computador', 1, 4250.5),\n",
       " (1, 'João', 4, 'celular', 1, 3750.25),\n",
       " (6, 'Clara', 14, 'videogame', 2, 3750.25),\n",
       " (3, 'Pedro', 9, 'videogame', 1, 3250.0)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "\n",
    "SELECT \n",
    "    tbClientes.id_cliente, \n",
    "    tbClientes.nome, \n",
    "    tbPedidos.id_pedido, \n",
    "    tbPedidos.produto, \n",
    "    tbPedidos.quantidade, \n",
    "    tbPedidos.preco \n",
    "FROM tbClientes \n",
    "    LEFT JOIN tbPedidos ON (tbClientes.id_cliente = tbPedidos.id_cliente)\n",
    "WHERE\n",
    "    tbPedidos.preco > 3000\n",
    "ORDER BY\n",
    "    tbPedidos.preco DESC\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-12'></a> Valor mínimo\n",
    "</h3>\n",
    "\n",
    "cláusula: **```MIN```**\n",
    "\n",
    "A cláusula 'MIN' retorna o valor mínimo de uma coluna.\n",
    "\n",
    "Exemplo: Consulta para encontrar o menor preço de cada produto.\n",
    "\n",
    "[- voltar -](#indice)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>produto</th>\n",
       "            <th>menor_preco</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>celular</td>\n",
       "            <td>1250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>computador</td>\n",
       "            <td>1750.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>televisão</td>\n",
       "            <td>1250.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>videogame</td>\n",
       "            <td>2250.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('celular', 1250.0),\n",
       " ('computador', 1750.99),\n",
       " ('televisão', 1250.0),\n",
       " ('videogame', 2250.0)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "SELECT \n",
    "    produto, \n",
    "    MIN(preco) AS menor_preco\n",
    "FROM \n",
    "    tbPedidos\n",
    "GROUP BY \n",
    "    produto\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-13'></a> Valor máximo\n",
    "</h3>\n",
    "\n",
    "cláusula: **```MAX```**\n",
    "\n",
    "A cláusula 'MAX' retorna o valor máximo de uma coluna.\n",
    "\n",
    "Exemplo: Consulta para encontrar o maior preço de cada produto.\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>produto</th>\n",
       "            <th>maior_preco</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>celular</td>\n",
       "            <td>4750.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>computador</td>\n",
       "            <td>4250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>televisão</td>\n",
       "            <td>5250.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>videogame</td>\n",
       "            <td>4250.5</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('celular', 4750.25),\n",
       " ('computador', 4250.5),\n",
       " ('televisão', 5250.5),\n",
       " ('videogame', 4250.5)]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "SELECT \n",
    "    produto, \n",
    "    MAX(preco) AS maior_preco\n",
    "FROM \n",
    "    tbPedidos\n",
    "GROUP BY \n",
    "    produto\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-14'></a> Média\n",
    "</h3>\n",
    "\n",
    "cláusula: **```AVG```**\n",
    "\n",
    "A cláusula 'AVG' retorna o cálculo da média de uma coluna.\n",
    "\n",
    "Exemplo: Consulta para calcular a média de preços por produto.\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>produto</th>\n",
       "            <th>media_preco</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>celular</td>\n",
       "            <td>2875.3725</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>computador</td>\n",
       "            <td>2917.1633333333334</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>televisão</td>\n",
       "            <td>2537.625</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>videogame</td>\n",
       "            <td>3375.1875</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('celular', 2875.3725),\n",
       " ('computador', 2917.1633333333334),\n",
       " ('televisão', 2537.625),\n",
       " ('videogame', 3375.1875)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "SELECT \n",
    "    produto, \n",
    "    AVG(preco) AS media_preco\n",
    "FROM \n",
    "    tbPedidos\n",
    "GROUP BY \n",
    "    produto\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-15'></a> Filtrar agregações\n",
    "</h3>\n",
    "\n",
    "cláusula: **```HAVING```**\n",
    "\n",
    "A cláusula 'HAVING' é utilizada em consultas SQL, normalmente em conjunto com a cláusula GROUP BY, para impor condições às agregações resultantes. \n",
    "\n",
    "Exemplo: Listar clientes com mais de 2 pedidos.\n",
    "\n",
    "[- voltar -](#indice)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>id_cliente</th>\n",
       "            <th>nome</th>\n",
       "            <th>total_pedidos</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>João</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'João', 4)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "SELECT \n",
    "    tbClientes.id_cliente, \n",
    "    tbClientes.nome, \n",
    "    COUNT(*) as total_pedidos\n",
    "FROM tbClientes \n",
    "    LEFT JOIN tbPedidos ON (tbClientes.id_cliente = tbPedidos.id_cliente)\n",
    "WHERE\n",
    "    tbClientes.estado = \"SP\"\n",
    "GROUP BY \n",
    "    tbClientes.id_cliente\n",
    "HAVING \n",
    "    COUNT(*) > 2\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>produto</th>\n",
       "            <th>media_preco</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>videogame</td>\n",
       "            <td>3375.1875</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('videogame', 3375.1875)]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "SELECT \n",
    "    produto, \n",
    "    AVG(preco) as media_preco\n",
    "FROM \n",
    "    tbPedidos\n",
    "GROUP BY \n",
    "    produto\n",
    "HAVING \n",
    "    AVG(preco) > 3000\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-16'></a> Criar tabela\n",
    "</h3>\n",
    "\n",
    "comando: **```CREATE TABLE```** *tabela*\n",
    "\n",
    "O comando 'CREATE TABLE' é uma instrução do SQL usada para criar uma nova tabela em um banco de dados.\n",
    "\n",
    "Exemplo: criar a tabela 'tbFornecedores' no nosso banco de dados.\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "CREATE TABLE tbFornecedores (\n",
    "    id_fornecedor INT PRIMARY KEY,\n",
    "    nome_fornecedor VARCHAR(50),\n",
    "    cidade VARCHAR(50),\n",
    "    estado VARCHAR(2)\n",
    ")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>id_fornecedor</th>\n",
       "            <th>nome_fornecedor</th>\n",
       "            <th>cidade</th>\n",
       "            <th>estado</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM tbFornecedores"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-17'></a> Inserir dados em uma tabela\n",
    "</h3>\n",
    "\n",
    "comando: **```INSERT INTO```** *tabela*\n",
    "\n",
    "O comando 'INSERT INTO' é uma instrução SQL utilizada para adicionar novos registros ou linhas a uma tabela existente em um banco de dados. Ele permite inserir novos valores nas colunas de uma tabela, especificando os dados que você deseja adicionar.\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "\n",
    "INSERT INTO tbFornecedores (id_fornecedor, nome_fornecedor, cidade, estado)\n",
    "VALUES (1, 'ACME', 'São Paulo', 'SP')\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>id_fornecedor</th>\n",
       "            <th>nome_fornecedor</th>\n",
       "            <th>cidade</th>\n",
       "            <th>estado</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>ACME</td>\n",
       "            <td>São Paulo</td>\n",
       "            <td>SP</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'ACME', 'São Paulo', 'SP')]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT * FROM tbFornecedores\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-18'></a> Atualizar dados em uma tabela\n",
    "</h3>\n",
    "\n",
    "comando: **```UPDATE```** *tabela*\n",
    "\n",
    "A cláusula 'UPDATE' é usada em SQL para modificar os valores de uma ou mais colunas em registros existentes em uma tabela. Esta cláusula permite alterar os dados já presentes em uma tabela, atualizando informações específicas em um ou mais registros que correspondam a determinadas condições.\n",
    "\n",
    "Exemplo: atualizar o nome de um fornecedor da tabela tbFornecedores\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "UPDATE tbFornecedores\n",
    "SET nome_fornecedor = 'XPTO'\n",
    "WHERE id_fornecedor = 1\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>id_fornecedor</th>\n",
       "            <th>nome_fornecedor</th>\n",
       "            <th>cidade</th>\n",
       "            <th>estado</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>XPTO</td>\n",
       "            <td>São Paulo</td>\n",
       "            <td>SP</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'XPTO', 'São Paulo', 'SP')]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT * FROM tbFornecedores\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #EB5E28; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-19'></a> Apagar uma tabela\n",
    "</h3>\n",
    "\n",
    "comando: **```DROP TABLE```** *tabela*\n",
    "\n",
    "O comando 'DROP TABLE' é uma instrução do SQL utilizada para excluir uma tabela existente em um banco de dados. Essa instrução remove completamente a estrutura da tabela, juntamente com todos os dados armazenados nela.\n",
    "\n",
    "Exemplo: apagar a tabela tbFornecedores do banco de dados.\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "DROP TABLE tbFornecedores\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"background-color: #FAF9F6; color: #0197F6; text-align: left; font-family: 'Arial', sans-serif; padding-top: 10px; padding-bottom: 10px; border-bottom: 1px solid #11557C;\">\n",
    "    <a id='secao-20'></a> Agora é sua vez!\n",
    "</h3>\n",
    "\n",
    "**Realize as suas consultas no banco de dados na célula abaixo!**\n",
    "\n",
    "[- voltar -](#indice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///bancodados.db\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "--------------\n",
    "#### Encerrar conexão e apagar banco de dados\n",
    "\n",
    "Execute a célula abaixo para encerrar a conexão e apagar o banco de dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Acesso Negado: Não foi possível realizar a exclusão do bando de dados.\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    # Fecha a conexão com o banco de dados e apaga o banco de dados\n",
    "    if os.path.exists('bancodados.db'):     \n",
    "        conexao.close()  \n",
    "        os.remove('bancodados.db')\n",
    "        print(\"Banco de dados 'bancodados.db' foi encerrado com sucesso.\")\n",
    "    else:\n",
    "        print(\"O banco de dados 'bancodados.db' não existe.\")\n",
    "except:\n",
    "    print(\"Acesso Negado: Não foi possível realizar a exclusão do bando de dados.\")\n"
   ]
  }
 ],
 "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}