Data Filtering and Simple Queries in PostgreSQL

Introduction

In this lab, you will learn how to perform data filtering and simple queries in PostgreSQL. You'll start by connecting to a PostgreSQL database and creating a sample employees table with data.

The lab then guides you through using the WHERE clause to filter data based on specific conditions, employing LIKE for pattern matching, sorting results with ORDER BY, and limiting the number of returned rows using LIMIT and OFFSET.

Filter Data with WHERE Clause

In this step, we will learn how to use the WHERE clause in PostgreSQL to filter data based on specific conditions. The WHERE clause is a powerful tool that allows you to retrieve only the rows that meet your criteria.

Before we begin, let's connect to the PostgreSQL database. Open a terminal in your LabEx VM. You can use the default Xfce terminal.

First, connect to the PostgreSQL database using the psql command. We'll connect to the postgres database with the postgres user. You might need to use sudo to execute the command.

sudo -u postgres psql

You should now see the PostgreSQL prompt (postgres=#).

Now, let's create a sample table named employees and insert some data into it. This table will store employee information, including their ID, name, department, and salary.

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);

To verify that the table has been created and populated correctly, execute the following SQL query:

SELECT * FROM employees;

You should see output similar to this:

 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)

Now, let's use the WHERE clause to filter the data. Suppose we want to retrieve only the employees who work in the 'Sales' department. We can use the following query:

SELECT * FROM employees WHERE department = 'Sales';

This query will return only the rows where the department column is equal to 'Sales'. The output should be:

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

You can also use other comparison operators in the WHERE clause, such as >, <, >=, <=, and <>. For example, to retrieve employees with a salary greater than 70000, you can use the following query:

SELECT * FROM employees WHERE salary > 70000;

The output should be:

 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)

You can also combine multiple conditions using logical operators such as AND and OR. For example, to retrieve employees who work in the 'Sales' department and have a salary greater than 58000, you can use the following query:

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

The output should be:

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

Finally, exit the psql shell:

\q

This will return you to the labex user's terminal.

Use LIKE for Pattern Matching

In this step, we will learn how to use the LIKE operator in PostgreSQL for pattern matching. The LIKE operator allows you to search for data that matches a specific pattern, which is particularly useful when you don't know the exact value you're looking for.

First, let's connect to the PostgreSQL database. Open a terminal in your LabEx VM.

Connect to the PostgreSQL database using the psql command:

sudo -u postgres psql

You should now see the PostgreSQL prompt (postgres=#).

We will continue using the employees table created in the previous step. If you haven't created it, please refer to the previous step to create the table and insert data.

Let's review the data in the employees table:

SELECT * FROM employees;

You should see the employee data as before.

The LIKE operator is used in a WHERE clause to find rows that match a specified pattern. The pattern can include wildcard characters:

• %: Represents zero or more characters.
• _: Represents a single character.

For example, to find all employees whose names start with 'A', you can use the following query:

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

This query will return all rows where the name column starts with 'A'. The output should be:

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

To find all employees whose names contain the letter 'o', you can use the following query:

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

This query will return all rows where the name column contains the letter 'o'. The output should be:

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

To find all employees whose names end with 'n', you can use the following query:

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

This query will return all rows where the name column ends with 'n'. The output should be:

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

You can also use the _ wildcard to match a single character. For example, to find all employees whose names have 'e ' as the second and third characters, you can use the following query:

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

This query will return all rows where the name column has 'e' as the second character. The output should be:

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

The LIKE operator is case-sensitive. If you want to perform a case-insensitive search, you can use the ILIKE operator. For example:

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

This query will return all rows where the name column starts with 'a' or 'A'.

Finally, exit the psql shell:

\q

This will return you to the labex user's terminal.

Sort Data with ORDER BY

In this step, we will learn how to use the ORDER BY clause in PostgreSQL to sort data. The ORDER BY clause allows you to sort the result set of a query based on one or more columns, either in ascending or descending order.

First, let's connect to the PostgreSQL database. Open a terminal in your LabEx VM.

Connect to the PostgreSQL database using the psql command:

sudo -u postgres psql

You should now see the PostgreSQL prompt (postgres=#).

We will continue using the employees table created in the previous steps. If you haven't created it, please refer to the previous steps to create the table and insert data.

Let's review the data in the employees table:

SELECT * FROM employees;

You should see the employee data as before.

The ORDER BY clause is used to sort the result set of a query. By default, the ORDER BY clause sorts the data in ascending order.

For example, to sort the employees by salary in ascending order, you can use the following query:

SELECT * FROM employees ORDER BY salary;

This query will return all rows from the employees table, sorted by the salary column in ascending order. The output should be:

 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)

To sort the data in descending order, you can use the DESC keyword after the column name. For example, to sort the employees by salary in descending order, you can use the following query:

SELECT * FROM employees ORDER BY salary DESC;

This query will return all rows from the employees table, sorted by the salary column in descending order. The output should be:

 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)

You can also sort the data by multiple columns. For example, to sort the employees by department in ascending order and then by salary in descending order, you can use the following query:

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

This query will first sort the data by the department column in ascending order. Within each department, the data will be sorted by the salary column in descending order. The output should be:

 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)

Finally, exit the psql shell:

\q

This will return you to the labex user's terminal.

Limit Results with LIMIT and OFFSET

In this step, we will learn how to use the LIMIT and OFFSET clauses in PostgreSQL to limit the number of rows returned by a query and to skip a certain number of rows, respectively. These clauses are useful for implementing pagination or retrieving a specific subset of data.

First, let's connect to the PostgreSQL database. Open a terminal in your LabEx VM.

Connect to the PostgreSQL database using the psql command:

sudo -u postgres psql

You should now see the PostgreSQL prompt (postgres=#).

We will continue using the employees table created in the previous steps. If you haven't created it, please refer to the previous steps to create the table and insert data.

Let's review the data in the employees table:

SELECT * FROM employees;

You should see the employee data as before.

The LIMIT clause is used to limit the number of rows returned by a query. For example, to retrieve only the first 3 employees, you can use the following query:

SELECT * FROM employees LIMIT 3;

This query will return the first 3 rows from the employees table. The output should be:

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

The OFFSET clause is used to skip a certain number of rows before starting to return rows. It is often used in conjunction with the LIMIT clause to implement pagination. For example, to retrieve the next 3 employees after skipping the first 2, you can use the following query:

SELECT * FROM employees LIMIT 3 OFFSET 2;

This query will skip the first 2 rows and then return the next 3 rows from the employees table. The output should be:

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

You can also combine LIMIT and OFFSET with the ORDER BY clause. For example, to retrieve the 2 highest-paid employees, you can use the following query:

SELECT * FROM employees ORDER BY salary DESC LIMIT 2;

This query will first sort the employees by salary in descending order and then return the first 2 rows. The output should be:

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

To retrieve the 3rd and 4th highest-paid employees, you can use the following query:

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

This query will first sort the employees by salary in descending order, skip the first 2 rows, and then return the next 2 rows. The output should be:

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

Finally, exit the psql shell:

\q

This will return you to the labex user's terminal.

Summary

In this lab, we began by connecting to a PostgreSQL database using the psql command and created a sample employees table with columns for ID, name, department, and salary. We then populated the table with employee data and verified its contents using a SELECT query.

The initial focus was on using the WHERE clause to filter data based on specific conditions. This allows for retrieving only the rows that meet the defined criteria, enabling targeted data retrieval from the employees table.