SELECT queries

[ databases ]

When working with a database, a QA engineer often needs to compare the expected changes in the database with the actual changes made by the server.

For example, we sent a request to add a cookie to the storage (see Integration testing) and expect a new record to appear in the database. It’s important to check that all cookie data ended up in the correct columns.

SELECT — a command for retrieving data from a database.

The SELECT command

A query for retrieving data can include the following keywords:

SELECT with a list of column names that should be shown in the result set; if you specify * all columns will be returned
FROM table name
WHERE condition for filtering by a column, for example:
ORDER BY the column names to sort by
- with ASC — ascending order (default)
- with DESC — descending order

It’s important to keep the keywords in the correct order!

SELECT id, name
FROM cat
WHERE name LIKE '%astronaut%'
ORDER BY name DESC;

Select cat IDs and names where the name contains the word 'astronaut', sorted by name in descending order

id	name
1	Cat-astronaut
3	Brother of Cat-astronaut

Query result

The LIKE operator

The LIKE operator lets you write WHERE conditions for searching strings by a pattern. The following characters can be used in a pattern:

The % symbol means zero, one, or more of any characters
The _ symbol means exactly one character
[ ] specifies a set of characters that must be present in the string
[^] specifies characters that must not be present in the string

SELECT *
FROM cat
WHERE name LIKE '____а%';

Select all cats from the cat table whose fifth letter in the name is — a

id	name
1	Cat-astronaut

Query result

Logical operators AND and OR

To combine multiple conditions in WHERE you use logical operators:

AND logical AND: rows are returned that satisfy both conditions

OR logical OR: rows are returned that satisfy at least one condition

SELECT *
FROM cat
WHERE is_ginger = true AND name LIKE 'К%';

For example, select ginger cats from the cat table AND with names that start with 'C'

Operators BETWEEN and IN

Here are two more comparison operators that you can replace with a combination of AND/OR conditions if you want:

BETWEEN lets you select values within the specified range inclusive

IN is used to compare a value with a list of specified values

SELECT *
FROM cat
WHERE age BETWEEN 1 AND 3 
OR color IN ('white', 'black');

Select cats from the cat table aged BETWEEN 1 AND 3 OR with a colour from the list ('white', 'black')

The DISTINCT keyword

The DISTINCT keyword is used when you need to select only unique values from a column or a combination of columns.

SELECT DISTINCT name
FROM cat;

Select unique cat names from the cat table

Task

Find the candy in the table candy.

Select all candies whose name starts with Plut, and sort the results by name in descending order.

When writing the query, you will need:

SELECT
FROM
WHERE + LIKE
ORDER BY

SQL simulator

Enter an SQL query:

Logical operators are not supported by the simulator