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.
Skills Graph
%%%%{init: {'theme':'neutral'}}%%%%
flowchart RL
postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"])
postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table")
postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row")
postgresql/PostgreSQLGroup -.-> postgresql/row_drop("Delete One Row")
postgresql/PostgreSQLGroup -.-> postgresql/func_call("Call Stored Function")
subgraph Lab Skills
postgresql/table_init -.-> lab-550947{{"Data Filtering and Simple Queries in PostgreSQL"}}
postgresql/row_add -.-> lab-550947{{"Data Filtering and Simple Queries in PostgreSQL"}}
postgresql/row_drop -.-> lab-550947{{"Data Filtering and Simple Queries in PostgreSQL"}}
postgresql/func_call -.-> lab-550947{{"Data Filtering and Simple Queries in PostgreSQL"}}
end
Filter Data with WHERE Clause
In this step, you 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.
First, open a terminal in your LabEx VM. You can use the default Xfce terminal.
Connect to the PostgreSQL database using the psql 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. Execute the following SQL statements:
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. Execute 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, execute 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, execute 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)
Use LIKE for Pattern Matching
In this step, you 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.
We will continue using the employees table created in the previous step.
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', execute 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', execute 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', execute 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, execute 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'.
Sort Data with ORDER BY
In this step, you 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.
We will continue using the employees table.
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, execute 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, execute 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, execute 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
----+-----------------+-------------+--------
4 | David Lee | Engineering | 90000
6 | Frank Miller | Engineering | 95000
2 | Bob Johnson | Marketing | 75000
5 | Eve Wilson | Marketing | 80000
1 | Alice Smith | Sales | 60000
3 | Charlie Brown | Sales | 55000
(6 rows)
Limit Results with LIMIT and OFFSET
In this step, you 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.
We will continue using the employees table.
The LIMIT clause is used to limit the number of rows returned by a query. For example, to retrieve only the first 3 employees, execute 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, execute 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, execute 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, execute 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)
Summary
In this lab, you learned how to perform data filtering and simple queries in PostgreSQL. You started by connecting to a PostgreSQL database using the psql command and created a sample employees table.
You then learned how to use the WHERE clause to filter data based on specific conditions, the LIKE operator for pattern matching, the ORDER BY clause for sorting data, and the LIMIT and OFFSET clauses to control the result set size. These are fundamental skills for working with relational databases like PostgreSQL.
