{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "SQL-modulo_2_aula.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KJqp9AANOCtf"
      },
      "source": [
        "<img src=\"https://raw.githubusercontent.com/andre-marcos-perez/ebac-course-utils/main/media/logo/newebac_logo_black_half.png\" alt=\"ebac-logo\">\n",
        "\n",
        "---\n",
        "\n",
        "# **Módulo** | SQL: Trabalhando com Tabelas\n",
        "Caderno de **Aula**<br> \n",
        "Professor [Mariane Neiva](www.linkedin.com/in/mariane-neiva)\n",
        "\n",
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d9jDtUbDOE1-"
      },
      "source": [
        "# **Tópicos**\n",
        "\n",
        "<ol type=\"1\">\n",
        "  <li>Criando tabelas;</li>\n",
        "  <li>Alterando e descartando tabelas;</li>\n",
        "  <li>Inserindo, atualizando e apagando dados;</li>\n",
        "</ol>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SmoHgt-lwkpD"
      },
      "source": [
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GABI6OW8OfQ2"
      },
      "source": [
        "# **Aulas**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "muD1vxozykSC"
      },
      "source": [
        "## 1\\. Criando tabelas"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5a3_syBIP6yO"
      },
      "source": [
        "### **1.1. Tipos de dados** "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xF1tFQyRQag2"
      },
      "source": [
        "*   **INT** - Valores numéricos exatos que usam dados inteiros.\n",
        "\n",
        "\n",
        "| Tipo de dados        | Intervalo | Amazenamento | \n",
        "|-----------|-------|------|\n",
        "| bigint | -2^63 (-9.223.372.036.854.775.808) a 2^63-1 (9.223.372.036.854.775.807)    | 8 bytes    | \n",
        "| int | -2^31 (-2.147.483.648) a 2^31-1 (2.147.483.647)    | 4 bytes    | \n",
        "| smallint | -2^15 (-32.768) a 2^15-1 (32.767)    | 2 bytes    | \n",
        "| tinyint | 0 a 255    | 1 byte    | \n",
        "\n",
        "<br>\n",
        "\n",
        "*   **FLOAT** e **DOUBLE** - Valores numéricos aproximados para uso com dados numéricos de ponto flutuante.\n",
        "\n",
        "*   **BOOLEAN** - Valores verdadeiro(1) e falso(0).\n",
        "\n",
        "*   **CHAR**, **VARCHAR** e **STRING** -  Valores de caractere.\n",
        "\n",
        "*   **DATE** e **TIMESTAMP** -  Valores de cadeia de caractere pré-formatado (data/hora).\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Y3G8CBk-1w6L"
      },
      "source": [
        "Consulte os detalhes [aqui](https://docs.aws.amazon.com/athena/latest/ug/data-types.html)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VkIgtLmhQRDV"
      },
      "source": [
        "### **1.2. Comando CREATE** "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Irt2Ji_1Lj7N"
      },
      "source": [
        "```sql\n",
        "CREATE TABLE <nome_da_tabela> (\n",
        "  <nome_da_coluna_1> <tipo_da_coluna_1>, \n",
        "  <nome_da_coluna_2> <tipo_da_coluna_2>, \n",
        "  ...\n",
        ");\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tREDswZoedI1"
      },
      "source": [
        "**Query 1:** Criar a tabela de `transacoes`.\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-nt5mclf7vSs"
      },
      "source": [
        "```sql\n",
        "CREATE TABLE transacoes (\n",
        "  id_transacao INT,\n",
        "  id_cliente INT, \n",
        "  data_compra DATE,\n",
        "  valor FLOAT, \n",
        ");\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zC2XR1GRaFl5"
      },
      "source": [
        "## 2\\. Alterando e descartando tabelas"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GsA0OqRRdBzi"
      },
      "source": [
        "### **2.1. Comando ALTER TABLE** "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yI9fUZPd9nEi"
      },
      "source": [
        "**Para adicionar uma coluna:**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nmWUu2E7JzZo"
      },
      "source": [
        "```sql\n",
        "ALTER TABLE <nome_da_tabela>\n",
        "ADD <nome_da_coluna> <tipo_da_coluna>;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gfS4k76RJf6L"
      },
      "source": [
        "Query 1: Adicionar coluna `id_loja` na tabela `transacoes`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pu8VRN4bO1JP"
      },
      "source": [
        "```sql\n",
        "ALTER TABLE transacoes\n",
        "ADD id_loja STRING;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6YKB8DX-9-c6"
      },
      "source": [
        "**Para alterar uma coluna:**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uHHo2Y6XAdIT"
      },
      "source": [
        "```sql\n",
        "ALTER TABLE <nome_da_tabela>\n",
        "ALTER COLUMN <nome_da_coluna> <tipo_da_coluna>;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "j5DnEANtAre3"
      },
      "source": [
        "Query 2: Alterar tipo da coluna `valor` na tabela `transacoes` para 'DOUBLE'"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YyHf8sryAr3Y"
      },
      "source": [
        "```sql\n",
        "ALTER TABLE transacoes\n",
        "ALTER COLUMN valor DOUBLE;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ock0DAXQBfAd"
      },
      "source": [
        "**Importante:** alter column é o comando do padrão do SQL Server/MS Access. Caso utilize outras ferramentas, como mySQL/Oracle ou Oracle 10G, utilize MODIFY COLUMN ou MODIFY respectivamente.\n",
        "\n",
        "Cada framework funciona de uma maneira, mas em geral, as mudanças são pequenas! Veremos também o exemplo da AWS Athena nos exercícios."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wSL3hsj8EZAD"
      },
      "source": [
        "**Para deletar uma coluna:**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MgwFh-y0EilU"
      },
      "source": [
        "```sql\n",
        "ALTER TABLE <nome_da_tabela>\n",
        "DROP COLUMN <nome_da_coluna>;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kVS7JUnNCqwU"
      },
      "source": [
        "Query 3: Deletar coluna `id_cliente` na tabela `transacoes`\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nJ7CmTSTComE"
      },
      "source": [
        "```sql\n",
        "ALTER TABLE transacoes\n",
        "DROP COLUMN id_cliente;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sV1otxQVhcaA"
      },
      "source": [
        "### **2.2. Comando DROP TABLE** "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xH5R9yQDC3kD"
      },
      "source": [
        "Esse comando remove uma tabela completamente do seu banco de dados."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8hZ3XTwIJs9e"
      },
      "source": [
        "```sql\n",
        "DROP TABLE <nome_da_tabela>;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Fde_pKuuDFN9"
      },
      "source": [
        "No comando SQL: "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RZi-sjuUDFg2"
      },
      "source": [
        "```sql\n",
        "DROP TABLE transacoes;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZtOHwcxEaGGp"
      },
      "source": [
        "## 3\\. Inserindo, atualizando e apagando dados"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "w3SQ9phkRxS0"
      },
      "source": [
        "### **3.1. Comando SELECT** "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QC-fpTLYf8H_"
      },
      "source": [
        "Para visualizar todos os dados da tabela, utilize asterisco (*):\n",
        "```sql\n",
        "SELECT * FROM <nome_tabela>;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FsRdfaOsShwj"
      },
      "source": [
        "Para visualizar colunas específicas da tabela:\n",
        "```sql\n",
        "SELECT <nome_coluna_1>,<nome_coluna_2> FROM <nome_tabela>;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZjwHJnl1eYzN"
      },
      "source": [
        "**Query 1:** Selecionar todos os dados da tabela `clientes`."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OXK6MyQrd-cC"
      },
      "source": [
        "```sql\n",
        "SELECT * FROM clientes;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ubiOD7fyNB9t"
      },
      "source": [
        "| id        | idade | sexo | dependentes | escolaridade | tipo_cartao | limite_credito | valor_transacoes_12m | qtd_transacoes_12m |\n",
        "|-----------|-------|------|---|--------------|-|----------------|----------|----|\n",
        "| 768805383 | 45    | M    | 3 | ensino medio | blue | 12.691,51      | 1.144,90 | 42 |\n",
        "| 818770008 | 49    | F    | 5 | mestrado     | blue | 8.256,96      | 1.291,45 | 33 |\n",
        "| 713982108 | 51    | M    | 3 | mestrado     | blue | 3.418,56      | 1.887,72 | 20 |"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s_Dj-BgThLg5"
      },
      "source": [
        "### **3.2. Comando INSERT** "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-WX56uZlEVEb"
      },
      "source": [
        "```sql\n",
        "INSERT INTO <nome_da_tabela> VALUES (<valor_1>,<valor_2>);\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ly2dlZejedI8"
      },
      "source": [
        "**Query 1:** Inserir os dados na tabela de `transacoes`.\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FNYhrIPqedI9"
      },
      "source": [
        "```sql\n",
        "INSERT INTO transacoes VALUES (1,768805383,2021-06-10,50.74,'magalu');\n",
        "INSERT INTO transacoes VALUES (2,768805399,2021-06-13,30.90,'giraffas');\n",
        "INSERT INTO transacoes VALUES (3,818770008,2021-06-05,110.00,'postoshell');\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YrLajEHaYc--"
      },
      "source": [
        "| id_transacao | id_cliente | data_compra |valor | id_loja |\n",
        "|------------|----------|----|-----| ------- |\n",
        "| 1| 768805383    | 2021-06-10    | 50.74 |magalu \n",
        "| 2 | 768805399    | 2021-06-13    |  30.90 | giraffas \n",
        "| 3 | 818770008    |2021-06-05   |  110.00 |  postoshell    "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tkc8mssVQxvL"
      },
      "source": [
        "### **3.3. Comando UPDATE** "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HIOrHXf_OrPV"
      },
      "source": [
        "```sql\n",
        "UPDATE <nome_da_tabela>\n",
        "SET <coluna_1> = <valor_1>, <coluna_2> = <valor_2>, ...\n",
        "WHERE <condição>;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fxc8xu0SaGgo"
      },
      "source": [
        "Importante: caso você não utilize a comando WHERE todos os valores serão alterados!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TYC7I8ZlO88l"
      },
      "source": [
        "**Query 1:** Atualizar o valor da compra com id =  `818770008`. "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jxzYZOvvO_l1"
      },
      "source": [
        "```sql\n",
        "UPDATE transacoes\n",
        "SET valor = 250.30\n",
        "WHERE id = 818770008;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5Auu6l_Lavwj"
      },
      "source": [
        "| id_transacao | id_cliente | data_compra |valor | id_loja |\n",
        "|------------|----------|----|-----| ------- |\n",
        "| 1| 768805383    | 2021-06-10    | 50.74 |magalu \n",
        "| 2 | 768805399    | 2021-06-13    |  30.90 | giraffas \n",
        "| 3 | 818770008    |2021-06-05   |  **250.30** |  postoshell    \n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vECGC45aQy1i"
      },
      "source": [
        "### **3.3. Comando DELETE** "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CroV2tJJRAzM"
      },
      "source": [
        "```sql\n",
        "DELETE FROM <nome_da_tabela> WHERE <condição>;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-goFDY58wTgo"
      },
      "source": [
        "**Query 1:** Remover os dados do cliente 768805383 na tabela de transações\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LJoXJ4_nwIPz"
      },
      "source": [
        "```sql\n",
        "DELETE FROM transacoes WHERE id_cliente = 768805383;\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MXlWQszbbTyi"
      },
      "source": [
        "| id_transacao | id_cliente | data_compra |valor | id_loja |\n",
        "|------------|----------|----|-----| ------- |\n",
        "| 2 | 768805399    | 2021-06-13    |  30.90 | giraffas \n",
        "| 3 | 818770008    |2021-06-05   |  **250.30** |  postoshell    "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aDfWVnF2bTTZ"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}