Let’s tackle one of the most fundamental concepts in SQL: SELECT queries. With them, you can extract data, filter results, sort them, and even run calculations.
Basic syntax
-- Get some columns
SELECT name, city FROM user;
-- Get every column
SELECT * FROM user;
Both queries return every row of the table. To filter, organize or group, we add clauses.
Clauses
| Clause | Role | Example |
|---|---|---|
WHERE | Filter rows | WHERE city = 'Paris' |
GROUP BY | Group rows that share a value | GROUP BY city |
HAVING | Filter groups, after grouping | HAVING COUNT(*) > 30 |
ORDER BY | Sort, ascending (ASC) or descending (DESC) | ORDER BY name ASC, birthday DESC |
LIMIT | Cap the number of results | LIMIT 5 |
OFFSET | Skip a number of rows | LIMIT 5 OFFSET 15 |
For example, to show 5 users starting from the 16th row:
SELECT * FROM user LIMIT 5 OFFSET 15;
Aggregate functions
They run calculations on a column and go in the SELECT:
| Function | Role | Example |
|---|---|---|
COUNT() | Count rows | SELECT COUNT(*) FROM user; |
AVG() | Average | SELECT AVG(nb_stars) FROM user; |
SUM() | Sum | SELECT SUM(nb_like) FROM post; |
MAX() | Maximum value | SELECT MAX(nb_stars) FROM user; |
MIN() | Minimum value | SELECT MIN(nb_stars) FROM user; |
A complete example
SELECT categorie, AVG(prix) AS prix_moyen
FROM produit
WHERE prix > 50
GROUP BY categorie
HAVING AVG(prix) > 100
ORDER BY prix_moyen DESC
LIMIT 5;
Let’s read it line by line (produit = product, prix = price, categorie = category):
SELECT categorie, AVG(prix) AS prix_moyen: show the category and the average price.ASgives the column an alias, so it readsprix_moyeninstead ofAVG(prix).FROM produit: read theproduittable.WHERE prix > 50: keep only products priced above 50.GROUP BY categorie: group the results by category.HAVING AVG(prix) > 100: keep only categories whose average price is above 100.ORDER BY prix_moyen DESC: sort by average price, highest first, reusing the alias.LIMIT 5: keep the first 5 rows.
WHEREorHAVING?WHEREfilters rows before grouping,HAVINGfilters groups after.
Indexes and performance
On a table with thousands of rows, searches need to stay fast. Indexes speed up searches on a column:
CREATE INDEX idx_user_city ON user (city);
The primary key is itself a special index that guarantees every value is unique. As we saw, it can also span several columns.
Hands-on
Take a tuto_sql database with two tables:
user:id,name,birthday,citypost:id,title,content,user_id,created_at,nb_like
A user can write several posts, and a post belongs to a single user (one-to-many through user_id).
Users who live in Paris:
SELECT * FROM user WHERE city = 'Paris';
How many are there?
SELECT COUNT(*) AS nb_user FROM user WHERE city = 'Paris';
-- nb_user: 34
Number of users per city:
SELECT city, COUNT(*) AS nb_user
FROM user
GROUP BY city;
-- Madrid 33, Paris 34, Tunis 33
Only cities with 33 users:
SELECT city, COUNT(*) AS nb_user
FROM user
GROUP BY city
HAVING COUNT(*) = 33;
-- Madrid, Tunis
Average likes on user 1’s posts:
SELECT AVG(nb_like), SUM(nb_like), COUNT(*)
FROM post
WHERE user_id = 1;
-- 583.0000, 1749, 3
The average is indeed the sum divided by the number of posts: 1749 / 3 = 583. MySQL always returns an average with decimals (583.0000). For a result without decimals, add ROUND():
SELECT ROUND(AVG(nb_like)) FROM post WHERE user_id = 1;
-- 583
phpMyAdmin also lets you run simple searches without writing SQL. But in this course we write real queries: that’s what you’ll use in your code.
Exercise · The users' podium
Using the post table, show the 3 users (their user_id) with the most likes in total, with that total, from highest to lowest.
Show the solution
SELECT user_id, SUM(nb_like) AS total_likes
FROM post
GROUP BY user_id
ORDER BY total_likes DESC
LIMIT 3;
We group by user, add up each group’s likes, sort on the alias, and keep the first three. To show their name instead of their user_id, you’ll need a join: see chapter 7.
What’s Next
In the next chapter, we move on to advanced SQL with subqueries. And we’ll do many more examples in the exercise chapters.
This is Chapter 5 of the SQL from Zero to Pro course. Follow along to learn SQL with MySQL, from the basics to joins.