Filtragem de Dados e Consultas Simples no PostgreSQL

Introdução

Neste laboratório, você aprenderá como realizar filtragem de dados e consultas simples no PostgreSQL. Você começará conectando-se a um banco de dados PostgreSQL e criando uma tabela de exemplo employees com dados.

O laboratório então o guia através do uso da cláusula WHERE para filtrar dados com base em condições específicas, empregando LIKE para correspondência de padrões, ordenando resultados com ORDER BY e limitando o número de linhas retornadas usando LIMIT e OFFSET.

Filtrar Dados com a Cláusula WHERE

Nesta etapa, aprenderemos como usar a cláusula WHERE no PostgreSQL para filtrar dados com base em condições específicas. A cláusula WHERE é uma ferramenta poderosa que permite recuperar apenas as linhas que atendem aos seus critérios.

Antes de começarmos, vamos nos conectar ao banco de dados PostgreSQL. Abra um terminal em sua VM LabEx. Você pode usar o terminal Xfce padrão.

Primeiro, conecte-se ao banco de dados PostgreSQL usando o comando psql. Conectaremos ao banco de dados postgres com o usuário postgres. Pode ser necessário usar sudo para executar o comando.

sudo -u postgres psql

Agora você deve ver o prompt do PostgreSQL (postgres=#).

Agora, vamos criar uma tabela de exemplo chamada employees e inserir alguns dados nela. Esta tabela armazenará informações dos funcionários, incluindo seu ID, nome, departamento e salário.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INTEGER
);

INSERT INTO employees (name, department, salary) VALUES
('Alice Smith', 'Sales', 60000),
('Bob Johnson', 'Marketing', 75000),
('Charlie Brown', 'Sales', 55000),
('David Lee', 'Engineering', 90000),
('Eve Wilson', 'Marketing', 80000),
('Frank Miller', 'Engineering', 95000);

Para verificar se a tabela foi criada e preenchida corretamente, execute a seguinte consulta SQL:

SELECT * FROM employees;

Você deve ver uma saída semelhante a esta:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  1 | Alice Smith     | Sales       |  60000
  2 | Bob Johnson     | Marketing   |  75000
  3 | Charlie Brown   | Sales       |  55000
  4 | David Lee       | Engineering |  90000
  5 | Eve Wilson      | Marketing   |  80000
  6 | Frank Miller    | Engineering |  95000
(6 rows)

Agora, vamos usar a cláusula WHERE para filtrar os dados. Suponha que queremos recuperar apenas os funcionários que trabalham no departamento de 'Vendas'. Podemos usar a seguinte consulta:

SELECT * FROM employees WHERE department = 'Sales';

Esta consulta retornará apenas as linhas onde a coluna department é igual a 'Sales'. A saída deve ser:

 id |     name      | department | salary
----+-----------------+------------+--------
  1 | Alice Smith     | Sales      |  60000
  3 | Charlie Brown   | Sales      |  55000
(2 rows)

Você também pode usar outros operadores de comparação na cláusula WHERE, como >, <, >=, <=, e <>. Por exemplo, para recuperar funcionários com um salário maior que 70000, você pode usar a seguinte consulta:

SELECT * FROM employees WHERE salary > 70000;

A saída deve ser:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  2 | Bob Johnson     | Marketing   |  75000
  4 | David Lee       | Engineering |  90000
  5 | Eve Wilson      | Marketing   |  80000
  6 | Frank Miller    | Engineering |  95000
(4 rows)

Você também pode combinar várias condições usando operadores lógicos como AND e OR. Por exemplo, para recuperar funcionários que trabalham no departamento de 'Vendas' e têm um salário maior que 58000, você pode usar a seguinte consulta:

SELECT * FROM employees WHERE department = 'Sales' AND salary > 58000;

A saída deve ser:

 id |    name     | department | salary
----+---------------+------------+--------
  1 | Alice Smith | Sales      |  60000
(1 row)

Finalmente, saia do shell psql:

\q

Isso o retornará ao terminal do usuário labex.

Usar LIKE para Correspondência de Padrões

Nesta etapa, aprenderemos como usar o operador LIKE no PostgreSQL para correspondência de padrões. O operador LIKE permite que você pesquise dados que correspondam a um padrão específico, o que é particularmente útil quando você não sabe o valor exato que está procurando.

Primeiro, vamos nos conectar ao banco de dados PostgreSQL. Abra um terminal em sua VM LabEx.

Conecte-se ao banco de dados PostgreSQL usando o comando psql:

sudo -u postgres psql

Agora você deve ver o prompt do PostgreSQL (postgres=#).

Continuaremos usando a tabela employees criada na etapa anterior. Se você não a criou, consulte a etapa anterior para criar a tabela e inserir dados.

Vamos revisar os dados na tabela employees:

SELECT * FROM employees;

Você deve ver os dados dos funcionários como antes.

O operador LIKE é usado em uma cláusula WHERE para encontrar linhas que correspondam a um padrão especificado. O padrão pode incluir caracteres curinga:

• %: Representa zero ou mais caracteres.
• _: Representa um único caractere.

Por exemplo, para encontrar todos os funcionários cujos nomes começam com 'A', você pode usar a seguinte consulta:

SELECT * FROM employees WHERE name LIKE 'A%';

Esta consulta retornará todas as linhas onde a coluna name começa com 'A'. A saída deve ser:

 id |    name     | department | salary
----+---------------+------------+--------
  1 | Alice Smith | Sales      |  60000
(1 row)

Para encontrar todos os funcionários cujos nomes contêm a letra 'o', você pode usar a seguinte consulta:

SELECT * FROM employees WHERE name LIKE '%o%';

Esta consulta retornará todas as linhas onde a coluna name contém a letra 'o'. A saída deve ser:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  2 | Bob Johnson     | Marketing   |  75000
  3 | Charlie Brown   | Sales       |  55000
  5 | Eve Wilson      | Marketing   |  80000
(3 rows)

Para encontrar todos os funcionários cujos nomes terminam com 'n', você pode usar a seguinte consulta:

SELECT * FROM employees WHERE name LIKE '%n';

Esta consulta retornará todas as linhas onde a coluna name termina com 'n'. A saída deve ser:

 id |     name      | department | salary
----+---------------+------------+--------
  2 | Bob Johnson   | Marketing  |  75000
  3 | Charlie Brown | Sales      |  55000
  5 | Eve Wilson    | Marketing  |  80000
(3 rows)

Você também pode usar o curinga _ para corresponder a um único caractere. Por exemplo, para encontrar todos os funcionários cujos nomes têm 'e ' como o segundo e terceiro caracteres, você pode usar a seguinte consulta:

SELECT * FROM employees WHERE name LIKE '_e%';

Esta consulta retornará todas as linhas onde a coluna name tem 'e' como o segundo caractere. A saída deve ser:

 id | name | department | salary
----+------+------------+--------
(0 rows)

O operador LIKE é sensível a maiúsculas e minúsculas. Se você deseja realizar uma pesquisa que não diferencia maiúsculas de minúsculas, pode usar o operador ILIKE. Por exemplo:

SELECT * FROM employees WHERE name ILIKE 'a%';

Esta consulta retornará todas as linhas onde a coluna name começa com 'a' ou 'A'.

Finalmente, saia do shell psql:

\q

Isso o retornará ao terminal do usuário labex.

Ordenar Dados com ORDER BY

Nesta etapa, aprenderemos como usar a cláusula ORDER BY no PostgreSQL para ordenar dados. A cláusula ORDER BY permite que você ordene o conjunto de resultados de uma consulta com base em uma ou mais colunas, seja em ordem crescente ou decrescente.

Primeiro, vamos nos conectar ao banco de dados PostgreSQL. Abra um terminal em sua VM LabEx.

Conecte-se ao banco de dados PostgreSQL usando o comando psql:

sudo -u postgres psql

Agora você deve ver o prompt do PostgreSQL (postgres=#).

Continuaremos usando a tabela employees criada nas etapas anteriores. Se você não a criou, consulte as etapas anteriores para criar a tabela e inserir dados.

Vamos revisar os dados na tabela employees:

SELECT * FROM employees;

Você deve ver os dados dos funcionários como antes.

A cláusula ORDER BY é usada para ordenar o conjunto de resultados de uma consulta. Por padrão, a cláusula ORDER BY ordena os dados em ordem crescente.

Por exemplo, para ordenar os funcionários por salário em ordem crescente, você pode usar a seguinte consulta:

SELECT * FROM employees ORDER BY salary;

Esta consulta retornará todas as linhas da tabela employees, ordenadas pela coluna salary em ordem crescente. A saída deve ser:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  3 | Charlie Brown   | Sales       |  55000
  1 | Alice Smith     | Sales       |  60000
  2 | Bob Johnson     | Marketing   |  75000
  5 | Eve Wilson      | Marketing   |  80000
  4 | David Lee       | Engineering |  90000
  6 | Frank Miller    | Engineering |  95000
(6 rows)

Para ordenar os dados em ordem decrescente, você pode usar a palavra-chave DESC após o nome da coluna. Por exemplo, para ordenar os funcionários por salário em ordem decrescente, você pode usar a seguinte consulta:

SELECT * FROM employees ORDER BY salary DESC;

Esta consulta retornará todas as linhas da tabela employees, ordenadas pela coluna salary em ordem decrescente. A saída deve ser:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  6 | Frank Miller    | Engineering |  95000
  4 | David Lee       | Engineering |  90000
  5 | Eve Wilson      | Marketing   |  80000
  2 | Bob Johnson     | Marketing   |  75000
  1 | Alice Smith     | Sales       |  60000
  3 | Charlie Brown   | Sales       |  55000
(6 rows)

Você também pode ordenar os dados por várias colunas. Por exemplo, para ordenar os funcionários por departamento em ordem crescente e, em seguida, por salário em ordem decrescente, você pode usar a seguinte consulta:

SELECT * FROM employees ORDER BY department ASC, salary DESC;

Esta consulta primeiro ordenará os dados pela coluna department em ordem crescente. Dentro de cada departamento, os dados serão ordenados pela coluna salary em ordem decrescente. A saída deve ser:

 id |     name      | department  | salary
----+---------------+-------------+--------
  6 | Frank Miller  | Engineering |  95000
  4 | David Lee     | Engineering |  90000
  5 | Eve Wilson    | Marketing   |  80000
  2 | Bob Johnson   | Marketing   |  75000
  1 | Alice Smith   | Sales       |  60000
  3 | Charlie Brown | Sales       |  55000
(6 rows)

Finalmente, saia do shell psql:

\q

Isso o retornará ao terminal do usuário labex.

Limitar Resultados com LIMIT e OFFSET

Nesta etapa, aprenderemos como usar as cláusulas LIMIT e OFFSET no PostgreSQL para limitar o número de linhas retornadas por uma consulta e para pular um certo número de linhas, respectivamente. Essas cláusulas são úteis para implementar paginação ou recuperar um subconjunto específico de dados.

Primeiro, vamos nos conectar ao banco de dados PostgreSQL. Abra um terminal em sua VM LabEx.

Conecte-se ao banco de dados PostgreSQL usando o comando psql:

sudo -u postgres psql

Agora você deve ver o prompt do PostgreSQL (postgres=#).

Continuaremos usando a tabela employees criada nas etapas anteriores. Se você não a criou, consulte as etapas anteriores para criar a tabela e inserir dados.

Vamos revisar os dados na tabela employees:

SELECT * FROM employees;

Você deve ver os dados dos funcionários como antes.

A cláusula LIMIT é usada para limitar o número de linhas retornadas por uma consulta. Por exemplo, para recuperar apenas os primeiros 3 funcionários, você pode usar a seguinte consulta:

SELECT * FROM employees LIMIT 3;

Esta consulta retornará as primeiras 3 linhas da tabela employees. A saída deve ser:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  1 | Alice Smith     | Sales       |  60000
  2 | Bob Johnson     | Marketing   |  75000
  3 | Charlie Brown   | Sales       |  55000
(3 rows)

A cláusula OFFSET é usada para pular um certo número de linhas antes de começar a retornar linhas. Ela é frequentemente usada em conjunto com a cláusula LIMIT para implementar paginação. Por exemplo, para recuperar os próximos 3 funcionários após pular os primeiros 2, você pode usar a seguinte consulta:

SELECT * FROM employees LIMIT 3 OFFSET 2;

Esta consulta pulará as primeiras 2 linhas e, em seguida, retornará as próximas 3 linhas da tabela employees. A saída deve ser:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  3 | Charlie Brown   | Sales       |  55000
  4 | David Lee       | Engineering |  90000
  5 | Eve Wilson      | Marketing   |  80000
(3 rows)

Você também pode combinar LIMIT e OFFSET com a cláusula ORDER BY. Por exemplo, para recuperar os 2 funcionários mais bem pagos, você pode usar a seguinte consulta:

SELECT * FROM employees ORDER BY salary DESC LIMIT 2;

Esta consulta primeiro ordenará os funcionários por salário em ordem decrescente e, em seguida, retornará as primeiras 2 linhas. A saída deve ser:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  6 | Frank Miller    | Engineering |  95000
  4 | David Lee       | Engineering |  90000
(2 rows)

Para recuperar o 3º e 4º funcionários mais bem pagos, você pode usar a seguinte consulta:

SELECT * FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 2;

Esta consulta primeiro ordenará os funcionários por salário em ordem decrescente, pulará as primeiras 2 linhas e, em seguida, retornará as próximas 2 linhas. A saída deve ser:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  5 | Eve Wilson      | Marketing   |  80000
  2 | Bob Johnson     | Marketing   |  75000
(2 rows)

Finalmente, saia do shell psql:

\q

Isso o retornará ao terminal do usuário labex.

Resumo

Neste laboratório, começamos conectando-nos a um banco de dados PostgreSQL usando o comando psql e criamos uma tabela de exemplo employees com colunas para ID, nome, departamento e salário. Em seguida, preenchemos a tabela com dados de funcionários e verificamos seu conteúdo usando uma consulta SELECT.

O foco inicial foi o uso da cláusula WHERE para filtrar dados com base em condições específicas. Isso permite recuperar apenas as linhas que atendem aos critérios definidos, possibilitando a recuperação de dados direcionada da tabela employees.