{ "cells": [ { "cell_type": "code", "execution_count": 2, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "/home/danielavancini/Projects/eng-analytics/chapters/05/01\n" ] } ], "source": [ "# HIDDEN\n", "%cd chapters/05/01/" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 5.1 Consultando dados com SELECT, WHERE e ORDER BY\n", "\n", "Praticamente, qualquer consulta em SQL inicia-se com o comando SELECT (é um equivalente ao “=” de uma função de EXCEL). Ele permite recuperar os dados de um objeto do banco de dados e na sua forma mais simples tem a seguinte sintaxe:\n", "\n", "```sql\n", "SELECT \n", "FROM nome_da_tabela\n", "```\n", "\n", "Ao fazer uma consulta, podemos passar o nome das colunas que queremos retornar ou usar o caractere * (leia-se star) que significa “todas as colunas”. Primeiramente vamos consultar 10 nomes de produtos e seus preços da tabela Product (o comando LIMIT abaixo limita os resultados nas 10 primeiras linhas da consulta):\n", "\n" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The sql extension is already loaded. To reload it, use:\n", " %reload_ext sql\n" ] } ], "source": [ "# HIDDEN\n", "from sqlalchemy import create_engine\n", "path_data = '../../assets/data/'\n", "%load_ext sql\n", "%config SqlMagic.displaycon=False\n", "%config SqlMagic.feedback=False\n", "# Agora dizemos para a extensão do ipython-sql onde estão os dados\n", "%sql sqlite:///../../../assets/data/northwind.sqlite" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ProductNameUnitPrice
Chai18
Chang19
Aniseed Syrup10
Chef Anton's Cajun Seasoning22
Chef Anton's Gumbo Mix21.35
Grandma's Boysenberry Spread25
Uncle Bob's Organic Dried Pears30
Northwoods Cranberry Sauce40
Mishi Kobe Niku97
Ikura31
" ], "text/plain": [ "[('Chai', 18),\n", " ('Chang', 19),\n", " ('Aniseed Syrup', 10),\n", " (\"Chef Anton's Cajun Seasoning\", 22),\n", " (\"Chef Anton's Gumbo Mix\", 21.35),\n", " (\"Grandma's Boysenberry Spread\", 25),\n", " (\"Uncle Bob's Organic Dried Pears\", 30),\n", " ('Northwoods Cranberry Sauce', 40),\n", " ('Mishi Kobe Niku', 97),\n", " ('Ikura', 31)]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT ProductName,UnitPrice FROM 'Product' LIMIT 10;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "No segundo caso, queremos todas as informações sobre os produtos usando o caractere “*”:" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///../../../assets/data/northwind.sqlite\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
IdProductNameSupplierIdCategoryIdQuantityPerUnitUnitPriceUnitsInStockUnitsOnOrderReorderLevelDiscontinued
1Chai1110 boxes x 20 bags18390100
2Chang1124 - 12 oz bottles191740250
3Aniseed Syrup1212 - 550 ml bottles101370250
4Chef Anton's Cajun Seasoning2248 - 6 oz jars2253000
5Chef Anton's Gumbo Mix2236 boxes21.350001
6Grandma's Boysenberry Spread3212 - 8 oz jars251200250
7Uncle Bob's Organic Dried Pears3712 - 1 lb pkgs.30150100
8Northwoods Cranberry Sauce3212 - 12 oz jars406000
9Mishi Kobe Niku4618 - 500 g pkgs.9729001
10Ikura4812 - 200 ml jars3131000
" ], "text/plain": [ "[(1, 'Chai', 1, 1, '10 boxes x 20 bags', 18, 39, 0, 10, 0),\n", " (2, 'Chang', 1, 1, '24 - 12 oz bottles', 19, 17, 40, 25, 0),\n", " (3, 'Aniseed Syrup', 1, 2, '12 - 550 ml bottles', 10, 13, 70, 25, 0),\n", " (4, \"Chef Anton's Cajun Seasoning\", 2, 2, '48 - 6 oz jars', 22, 53, 0, 0, 0),\n", " (5, \"Chef Anton's Gumbo Mix\", 2, 2, '36 boxes', 21.35, 0, 0, 0, 1),\n", " (6, \"Grandma's Boysenberry Spread\", 3, 2, '12 - 8 oz jars', 25, 120, 0, 25, 0),\n", " (7, \"Uncle Bob's Organic Dried Pears\", 3, 7, '12 - 1 lb pkgs.', 30, 15, 0, 10, 0),\n", " (8, 'Northwoods Cranberry Sauce', 3, 2, '12 - 12 oz jars', 40, 6, 0, 0, 0),\n", " (9, 'Mishi Kobe Niku', 4, 6, '18 - 500 g pkgs.', 97, 29, 0, 0, 1),\n", " (10, 'Ikura', 4, 8, '12 - 200 ml jars', 31, 31, 0, 0, 0)]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT * FROM 'Product' LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Em muitos casos não queremos consultar todos os dados de uma tabela, mas apenas um subconjunto desses dados ou mesmo um único dado. Para isso usamos a cláusula WHERE que funciona retornando todos os registros onde a condição é verdadeira:\n", "\n", "```sql\n", "SELECT \n", "FROM nome_da_tabela\n", "WHERE condicao \n", "```\n", "\n", "Agora vamos utilizar a cláusula WHERE para listar todos os clientes do Brasil:" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///../../../assets/data/northwind.sqlite\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
IdCompanyNameAddressCityRegionPostalCode
COMMIComércio MineiroAv. dos Lusíadas, 23Sao PauloSouth America05432-043
FAMIAFamilia ArquibaldoRua Orós, 92Sao PauloSouth America05442-030
GOURLGourmet LanchonetesAv. Brasil, 442CampinasSouth America04876-786
HANARHanari CarnesRua do Paço, 67Rio de JaneiroSouth America05454-876
QUEDEQue DelíciaRua da Panificadora, 12Rio de JaneiroSouth America02389-673
QUEENQueen CozinhaAlameda dos Canàrios, 891Sao PauloSouth America05487-020
RICARRicardo AdocicadosAv. Copacabana, 267Rio de JaneiroSouth America02389-890
TRADHTradição HipermercadosAv. Inês de Castro, 414Sao PauloSouth America05634-030
WELLIWellington ImportadoraRua do Mercado, 12ResendeSouth America08737-363
" ], "text/plain": [ "[('COMMI', 'Comércio Mineiro', 'Av. dos Lusíadas, 23', 'Sao Paulo', 'South America', '05432-043'),\n", " ('FAMIA', 'Familia Arquibaldo', 'Rua Orós, 92', 'Sao Paulo', 'South America', '05442-030'),\n", " ('GOURL', 'Gourmet Lanchonetes', 'Av. Brasil, 442', 'Campinas', 'South America', '04876-786'),\n", " ('HANAR', 'Hanari Carnes', 'Rua do Paço, 67', 'Rio de Janeiro', 'South America', '05454-876'),\n", " ('QUEDE', 'Que Delícia', 'Rua da Panificadora, 12', 'Rio de Janeiro', 'South America', '02389-673'),\n", " ('QUEEN', 'Queen Cozinha', 'Alameda dos Canàrios, 891', 'Sao Paulo', 'South America', '05487-020'),\n", " ('RICAR', 'Ricardo Adocicados', 'Av. Copacabana, 267', 'Rio de Janeiro', 'South America', '02389-890'),\n", " ('TRADH', 'Tradição Hipermercados', 'Av. Inês de Castro, 414', 'Sao Paulo', 'South America', '05634-030'),\n", " ('WELLI', 'Wellington Importadora', 'Rua do Mercado, 12', 'Resende', 'South America', '08737-363')]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT Id,CompanyName,Address,City,Region,PostalCode \n", "FROM 'Customer' \n", "WHERE Country = 'Brazil'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Podemos tornar os filtros mais poderosos usando outros operadores além da igualdade:\n", "\n", "\n", "| Operador | Descrição | Exemplo Sintaxe |\n", "|----------|------------------------------|------------------------------------------------------------------------|\n", "| = | Igual a | SELECT * FROM Customers WHERE Country = ‘Brazil’ |\n", "| > | Maior que | SELECT * FROM Products WHERE UnitPrice > 10 |\n", "| < | Menor que | SELECT * FROM Products WHERE UnitPrice < 10 |\n", "| >= | Maior ou igual a | SELECT * FROM Products WHERE UnitPrice >= 10 |\n", "| <= | Menor ou igual a | SELECT * FROM Products WHERE UnitPrice <= 10 |\n", "| <> ou != | Diferente de | SELECT * FROM Customers WHERE Country <> ‘Brazil’ |\n", "| AND | E | SELECT * FROM Customers WHERE Country = ‘Brazil’ AND City = ‘Campinas’ |\n", "| OR | OU | SELECT * FROM Customers WHERE City = ‘Campinas’ OR City = ‘Resende’ |\n", "| IN | Contém algum de | SELECT * FROM Customers WHERE City IN (‘Campinas’, ‘Resende’) |\n", "| NOT | Não contém nenhum de | SELECT * FROM Customers WHERE City NOT IN (‘Campinas’, ‘Resende’) |\n", "| BETWEEN | Entre | SELECT * FROM Products WHERE UnitPrice BETWEEN 10 AND 20 |\n", "| LIKE | Corresponde a padrão textual | SELECT * FROM Products WHERE ProductName LIKE ‘a%’ |" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Um terceiro comando fundamental do SQL é o de ordenação: ORDER BY. Com ele conseguimos obter respostas a perguntas como “Quais os 5 produtos mais caros?” ou “Quais foram os pedidos com maior valor de compra?” e muito mais. A sua sintaxe é muito simples:\n", "\n", "```sql\n", "SELECT \n", "FROM nome_da_tabela\n", "ORDER BY \n", "```\n", "\n", "Retomando nosso primeiro exemplo, queremos consultar os 5 primeiros produtos em ordem alfabética de nosso catálogo:" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///../../../assets/data/northwind.sqlite\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ProductNameUnitPrice
Alice Mutton39
Aniseed Syrup10
Boston Crab Meat18.4
Camembert Pierrot34
Carnarvon Tigers62.5
" ], "text/plain": [ "[('Alice Mutton', 39),\n", " ('Aniseed Syrup', 10),\n", " ('Boston Crab Meat', 18.4),\n", " ('Camembert Pierrot', 34),\n", " ('Carnarvon Tigers', 62.5)]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT ProductName,UnitPrice\n", "FROM 'Product' \n", "ORDER BY ProductName \n", "LIMIT 5;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Para inverter a ordem usamos o operador `DESC`, necessário se quisermos consultar os 10 produtos com maior preço do catálogo:" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///../../../assets/data/northwind.sqlite\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ProductNameUnitPrice
Côte de Blaye263.5
Thüringer Rostbratwurst123.79
Mishi Kobe Niku97
Sir Rodney's Marmalade81
Carnarvon Tigers62.5
Raclette Courdavault55
Manjimup Dried Apples53
Tarte au sucre49.3
Ipoh Coffee46
Rössle Sauerkraut45.6
" ], "text/plain": [ "[('Côte de Blaye', 263.5),\n", " ('Thüringer Rostbratwurst', 123.79),\n", " ('Mishi Kobe Niku', 97),\n", " (\"Sir Rodney's Marmalade\", 81),\n", " ('Carnarvon Tigers', 62.5),\n", " ('Raclette Courdavault', 55),\n", " ('Manjimup Dried Apples', 53),\n", " ('Tarte au sucre', 49.3),\n", " ('Ipoh Coffee', 46),\n", " ('Rössle Sauerkraut', 45.6)]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT ProductName,UnitPrice\n", "FROM 'Product' \n", "ORDER BY UnitPrice \n", "DESC \n", "LIMIT 10;" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3.9.5 ('venv': venv)", "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.9.5" }, "vscode": { "interpreter": { "hash": "1f018ec1ba574647514081dcb490c63f68e342ee5f5450c4501934abf117bfd5" } } }, "nbformat": 4, "nbformat_minor": 2 }