Introdução
Neste laboratório, você aprenderá como resumir e analisar dados em SQLite usando funções agregadas e cláusulas de agrupamento. Você explorará COUNT e SUM para cálculos, agrupará dados por colunas individuais, filtrará grupos com HAVING e ordenará a saída agrupada. Esta experiência prática fornecerá habilidades essenciais de manipulação de dados em SQLite.
Criar a Tabela 'Orders' e Inserir Dados
Nesta etapa, você criará um banco de dados chamado sales.db e uma tabela orders dentro dele. Em seguida, você inserirá dados de exemplo na tabela. Esta tabela será usada ao longo do laboratório para praticar técnicas de agrupamento de dados.
Primeiro, abra seu terminal na VM do LabEx. Seu caminho padrão é /home/labex/project.
Para começar, crie o banco de dados sales.db e conecte-se a ele usando o seguinte comando:
sqlite3 sales.db
Este comando abrirá o shell SQLite, e você verá um prompt como sqlite>.
Em seguida, crie a tabela orders com colunas para order_id, customer_id, product_name, quantity e price. Execute o seguinte comando SQL:
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
product_name TEXT,
quantity INTEGER,
price REAL
);
Este comando cria a tabela orders com as colunas e tipos de dados especificados. A coluna order_id é definida como a chave primária (primary key).
Agora, insira dados de exemplo na tabela orders. Execute as seguintes instruções INSERT uma por uma:
INSERT INTO orders (customer_id, product_name, quantity, price) VALUES
(1, 'Laptop', 1, 1200.00),
(1, 'Mouse', 2, 25.00),
(2, 'Keyboard', 1, 75.00),
(2, 'Monitor', 1, 300.00),
(3, 'Laptop', 1, 1200.00),
(3, 'Headphones', 1, 100.00),
(1, 'Keyboard', 1, 75.00);
Esses comandos inserem sete linhas de dados na tabela orders, representando diferentes pedidos de clientes.
Para verificar se os dados foram inseridos corretamente, você pode executar uma consulta SELECT simples:
SELECT * FROM orders;
Este comando exibirá todas as linhas e colunas na tabela orders.
Agregação com COUNT e SUM
Nesta etapa, você usará as funções agregadas COUNT e SUM para realizar cálculos nos dados da tabela orders. Funções agregadas permitem que você resuma dados de várias linhas em um único resultado.
Você ainda deve estar conectado ao banco de dados sales.db da etapa anterior. Caso contrário, reconecte-se usando:
sqlite3 sales.db
Primeiro, vamos usar a função COUNT para determinar o número total de pedidos na tabela. Execute o seguinte comando SQL:
SELECT COUNT(*) FROM orders;
Esta consulta retornará o número total de linhas na tabela orders, que representa o número total de pedidos.
A função COUNT(*) conta todas as linhas na tabela, independentemente de alguma coluna conter valores NULL.
Em seguida, vamos usar a função SUM para calcular a quantidade total de todos os produtos pedidos. Execute o seguinte comando SQL:
SELECT SUM(quantity) FROM orders;
Esta consulta retornará a soma da coluna quantity para todas as linhas na tabela orders.
A função SUM soma os valores na coluna especificada.
Finalmente, vamos calcular a receita total gerada por todos os pedidos. Execute o seguinte comando SQL:
SELECT SUM(quantity * price) FROM orders;
Esta consulta multiplica as colunas quantity e price para cada linha e, em seguida, soma os resultados, fornecendo a receita total.
Agrupar por Colunas Únicas
Nesta etapa, você aprenderá como usar a cláusula GROUP BY para agrupar linhas com base nos valores em uma ou mais colunas. Isso é frequentemente usado em conjunto com funções agregadas para calcular estatísticas de resumo para cada grupo.
Você ainda deve estar conectado ao banco de dados sales.db da etapa anterior. Caso contrário, reconecte-se usando:
sqlite3 sales.db
Vamos agrupar a tabela orders por customer_id e contar o número de pedidos para cada cliente. Execute o seguinte comando SQL:
SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id;
Esta consulta retornará o customer_id e o número de pedidos (order_count) para cada cliente único. A cláusula GROUP BY customer_id informa ao SQLite para agrupar as linhas com base nos valores na coluna customer_id. A função COUNT(*) então conta o número de linhas em cada grupo.
Em seguida, vamos agrupar a tabela orders por product_name e calcular a quantidade total pedida para cada produto. Execute o seguinte comando SQL:
SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name;
Esta consulta retornará o product_name e a quantidade total pedida (total_quantity) para cada produto único. A cláusula GROUP BY product_name informa ao SQLite para agrupar as linhas com base nos valores na coluna product_name. A função SUM(quantity) então calcula a soma da coluna quantity para cada grupo.
Finalmente, vamos agrupar a tabela orders por customer_id e calcular a receita total gerada por cada cliente. Execute o seguinte comando SQL:
SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id;
Esta consulta retornará o customer_id e a receita total (total_revenue) gerada por cada cliente.
Aplicar HAVING a Grupos
Nesta etapa, você aprenderá como usar a cláusula HAVING para filtrar grupos após eles terem sido criados pela cláusula GROUP BY. A cláusula HAVING é semelhante à cláusula WHERE, mas opera em grupos em vez de linhas individuais.
Você ainda deve estar conectado ao banco de dados sales.db da etapa anterior. Caso contrário, reconecte-se usando:
sqlite3 sales.db
Vamos agrupar a tabela orders por customer_id e contar o número de pedidos para cada cliente. Em seguida, usaremos a cláusula HAVING para filtrar os resultados e incluir apenas os clientes que fizeram mais de um pedido. Execute o seguinte comando SQL:
SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;
Esta consulta retornará o customer_id e o número de pedidos (order_count) para cada cliente que fez mais de um pedido. A cláusula GROUP BY customer_id agrupa as linhas por customer_id, e a cláusula HAVING COUNT(*) > 1 filtra os grupos para incluir apenas aqueles em que a contagem de pedidos é maior que 1.
Em seguida, vamos agrupar a tabela orders por product_name e calcular a quantidade total pedida para cada produto. Em seguida, usaremos a cláusula HAVING para filtrar os resultados e incluir apenas os produtos em que a quantidade total pedida é maior que 1. Execute o seguinte comando SQL:
SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name HAVING SUM(quantity) > 1;
Esta consulta retornará o product_name e a quantidade total pedida (total_quantity) para cada produto em que a quantidade total pedida é maior que 1.
Finalmente, vamos agrupar a tabela orders por customer_id e calcular a receita total gerada por cada cliente. Em seguida, usaremos a cláusula HAVING para filtrar os resultados e incluir apenas os clientes que geraram mais de $1000 em receita. Execute o seguinte comando SQL:
SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id HAVING SUM(quantity * price) > 1000;
Esta consulta retornará o customer_id e a receita total (total_revenue) gerada por cada cliente que gerou mais de $1000 em receita.
Ordenar a Saída Agrupada
Nesta etapa, você aprenderá como usar a cláusula ORDER BY para ordenar a saída de uma consulta que inclui a cláusula GROUP BY. Ordenar a saída agrupada pode facilitar a análise e a compreensão dos dados.
Você ainda deve estar conectado ao banco de dados sales.db da etapa anterior. Caso contrário, reconecte-se usando:
sqlite3 sales.db
Vamos agrupar a tabela orders por customer_id e contar o número de pedidos para cada cliente. Em seguida, usaremos a cláusula ORDER BY para ordenar os resultados em ordem decrescente com base no número de pedidos. Execute o seguinte comando SQL:
SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ORDER BY order_count DESC;
Esta consulta retornará o customer_id e o número de pedidos (order_count) para cada cliente, ordenados em ordem decrescente com base em order_count. A cláusula GROUP BY customer_id agrupa as linhas por customer_id, e a cláusula ORDER BY order_count DESC ordena os resultados em ordem decrescente com base no alias order_count.
Em seguida, vamos agrupar a tabela orders por product_name e calcular a quantidade total pedida para cada produto. Em seguida, usaremos a cláusula ORDER BY para ordenar os resultados em ordem crescente com base no nome do produto. Execute o seguinte comando SQL:
SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name ORDER BY product_name ASC;
Esta consulta retornará o product_name e a quantidade total pedida (total_quantity) para cada produto, ordenados em ordem crescente com base em product_name.
Finalmente, vamos agrupar a tabela orders por customer_id e calcular a receita total gerada por cada cliente. Em seguida, usaremos a cláusula ORDER BY para ordenar os resultados em ordem decrescente com base na receita total. Execute o seguinte comando SQL:
SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id ORDER BY total_revenue DESC;
Esta consulta retornará o customer_id e a receita total (total_revenue) gerada por cada cliente, ordenados em ordem decrescente com base em total_revenue.
Para sair do shell SQLite, execute:
.exit
Resumo
Neste laboratório, você aprendeu como usar funções agregadas como COUNT e SUM para resumir dados no SQLite. Você criou um banco de dados sales.db com uma tabela orders e inseriu dados de exemplo. Em seguida, você usou COUNT(*) para determinar o número total de pedidos e SUM(quantity * price) para calcular a receita total. Você também aprendeu como agrupar dados usando a cláusula GROUP BY, filtrar grupos usando a cláusula HAVING e ordenar a saída usando a cláusula ORDER BY. Essas habilidades fornecem uma base sólida para análise de dados no SQLite.
