2025-03-25
Introduction to SQL - Basic Queries
So far so good, we have been learning the basics of databases and SQL. Now we want to enter one of the most important topics in SQL: the queries. A query is really a question or request for data. For example, ''Tell me how many products there are on stock in my products table'' or ''How many User we’ve registered on our site?'' When we query databases, we can use a common language to get the information. There are some different flavors, but once you know the basics you can easily adapt your questions.
Note: this post will continue working with databases, tables and rows created on our last blog post. Please follow it here.
So lets recap:
- A query is to fetch information stored in the database.
- Queries allow us to communicate with the database by asking questions and obtaining results relevant to these questions.
Select
The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set. We can select one or more columns or all columns with the key (*)
marvel_tshirts=# SELECT name FROM users;
name
--------
Justin
Ana
Viktor
(3 rows)
marvel_tshirts=# SELECT id, name FROM users;
id | name
----+--------
1 | Justin
2 | Ana
4 | Viktor
(3 rows)
marvel_tshirts=# SELECT * FROM users;
id | name | age
----+--------+-----
1 | Justin | 22
2 | Ana | 21
4 | Viktor | 39
(3 rows)
AS
AS is a reserved word that allows you to rename a column or table using an alias.
- The new name can be anything you want.
- It is not necessary to create an Alias.
- When using AS, the columns as such are not renamed in the table, the Alias will only appear in the result.
marvel_tshirts=# SELECT name AS username
marvel_tshirts-# FROM users;
username
----------
Justin
Ana
Viktor
(3 rows)
Can we create an alias to multiple columns in a single query? Yes
marvel_tshirts=# SELECT name AS username, age AS old
marvel_tshirts-# FROM users;
username | old
----------+-----
Justin | 22
Ana | 21
Viktor | 39
(3 rows)
DISTINCT
- - It is used to query the different values of a particular column.
- - DISTINCT is used to return unique values in the response.
- - It filters out all duplicate values in the specified columns.
- - Filtering results is an important skill in SQL. It is easy to see unique values
Let’s create 2 other equal names in table friends we were working on in our last blog post.
marvel_tshirts=# SELECT * FROM friends;
id | name | birthday | email
----+----------+------------+---------------------
2 | Emiliano | 2006-09-26 | emilianitogmail.com
3 | Maria | 1986-09-02 | [email protected]
(2 rows)
marvel_tshirts=# INSERT INTO friends (id, name, birthday) VALUES (4, 'Maria', '1986-09-02');
INSERT 0 1
marvel_tshirts=# INSERT INTO friends (id, name, birthday) VALUES (5, 'Maria', '1986-09-02');
INSERT 0 1
marvel_tshirts=# SELECT * FROM friends; id | name | birthday | email
----+----------+------------+---------------------
2 | Emiliano | 2006-09-26 | emilianitogmail.com
3 | Maria | 1986-09-02 | [email protected]
4 | Maria | 1986-09-02 |
5 | Maria | 1986-09-02 |
(4 rows)
Now we select distinct values
marvel_tshirts=# SELECT DISTINCT name
marvel_tshirts-# FROM friends;
name
----------
Emiliano
Maria
WHERE
- It is used to restrict the query in order to obtain the information we want.
- The WHERE clause filters the result to include the rows where the where condition is true.
marvel_tshirts=# SELECT * FROM users;
id | name | age
----+--------+-----
1 | Justin | 22
2 | Ana | 21
4 | Viktor | 39
(3 rows)
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# WHERE age > 30;
id | name | age
----+--------+-----
4 | Viktor | 39
(1 row)
The symbol > is an operator. Operators create a condition that can be evaluated as true or false. The comparison operators that can be used with WHERE are:
= equal to
!= not equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# WHERE name = 'Justin';
id | name | age
----+--------+-----
1 | Justin | 22
(1 row)
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# WHERE age < 30;
id | name | age
----+--------+-----
1 | Justin | 22
2 | Ana | 21
(2 rows)
LIKE
- It is useful when you want to compare similar values
- A movies table could contain two movies with similar titles "Se7ven" and "Seven".
- How can we select all movies starting with "Se" and ending in "en" and have a character in the middle?
- It is a special operator used with the WHERE clause to search for a specific pattern in a column.
- Se_en represents a pattern with a "wildcard" character.
- The _ means that you can substitute any individual character here without damaging the pattern
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# WHERE name LIKE 'Ju__in';
id | name | age
----+--------+-----
1 | Justin | 22
(1 row)
- The percent sign % is another "wildcard" character that can be used with LIKE
- The % is a wildcard that matches zero or more letters in the pattern.
- A% matches all movies starting with the name A
- %a matches all movies with names ending in "a".
- We can also use % before and after a pattern, such as %man%.
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# WHERE name LIKE 'J%';
id | name | age
----+--------+-----
1 | Justin | 22
(1 row)
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# WHERE name LIKE '%n';
id | name | age
----+--------+-----
1 | Justin | 22
(1 row)
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# WHERE name LIKE '%st%';
id | name | age
----+--------+-----
1 | Justin | 22
(1 row)
BETWEEN
- The BETWEEN operator can be used with WHERE to filter a result set with a given range.
- These values can be numbers, text or dates
- e.g. BETWEEN 'A' AND 'J'; include name A through J but exclude J
- BETWEEN two letters is exclusive of the second letter
- BETWEEN two numbers is inclusive of the second numb
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# WHERE name BETWEEN 'A' AND 'J';
id | name | age
----+------+-----
2 | Ana | 21
(1 row)
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# WHERE age BETWEEN 20 and 30;
id | name | age
----+--------+-----
1 | Justin | 22
2 | Ana | 21
(2 rows)
AND
- Sometimes we might want to combine multiple conditions in a WHERE clause to make the result more specific and useful.
- One way to do this is with the AND operator.
- Both conditions must evaluate to True to be included in the result.
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# WHERE age BETWEEN 20 AND 30
marvel_tshirts-# AND name = 'Ana';
id | name | age
----+------+-----
2 | Ana | 21
(1 row)
OR
The OR operator can be used to combine multiple conditions in WHERE, but these are the differences with AND
- AND returns a result if all conditions are true.
- OR returns a result if only one of the conditions is true.
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# WHERE age = 20
marvel_tshirts-# OR name = 'Ana';
id | name | age
----+------+-----
2 | Ana
ORDER BY
Often we will need to list the data in a particular order. We can do this alphabetically or numerically. The ORDER BY clause always goes after WHERE (if WHERE is present in the query). We can sort in descending order, for example, if we want to order by age from highest to lowest.
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# ORDER BY age DESC;
id | name | age
----+--------+-----
4 | Viktor | 39
1 | Justin | 22
2 | Ana | 21
(3 rows)
Can we apply ORDER BY multiple columns? Yes, it will take the order by according to the column position
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# ORDER BY name ASC, age DESC;
id | name | age
----+--------+-----
2 | Ana | 21
1 | Justin | 22
4 | Viktor | 39
(3 rows)
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# ORDER BY age DESC, name ASC;
id | name | age
----+--------+-----
4 | Viktor | 39
1 | Justin | 22
2 | Ana | 21
(3 rows)
LIMIT
- LIMIT is a clause that allows you to specify the maximum number of rows the result should have.
- This makes queries run faster
- This clause always goes at the end of queries.
- It is not supported in all SQL databases.
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# LIMIT 2;
id | name | age
----+--------+-----
1 | Justin | 22
2 | Ana | 21
(2 rows)
marvel_tshirts=# SELECT * FROM users
marvel_tshirts-# ORDER BY age DESC
marvel_tshirts-# LIMIT 2;
id | name | age
----+--------+-----
4 | Viktor | 39
1 | Justin | 22
(2 rows)
As you can see, we’ve a lot of different queries we can use to retrieve data in different ways and according to the questions we have asked. There are plenty more of query statements, but for now I think is enough to have a basic understand of what we want to do
I hope you learned a lot
Thanks for reading
Daniel Morales