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

ClauseRoleExample
WHEREFilter rowsWHERE city = 'Paris'
GROUP BYGroup rows that share a valueGROUP BY city
HAVINGFilter groups, after groupingHAVING COUNT(*) > 30
ORDER BYSort, ascending (ASC) or descending (DESC)ORDER BY name ASC, birthday DESC
LIMITCap the number of resultsLIMIT 5
OFFSETSkip a number of rowsLIMIT 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:

FunctionRoleExample
COUNT()Count rowsSELECT COUNT(*) FROM user;
AVG()AverageSELECT AVG(nb_stars) FROM user;
SUM()SumSELECT SUM(nb_like) FROM post;
MAX()Maximum valueSELECT MAX(nb_stars) FROM user;
MIN()Minimum valueSELECT 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):

  1. SELECT categorie, AVG(prix) AS prix_moyen: show the category and the average price. AS gives the column an alias, so it reads prix_moyen instead of AVG(prix).
  2. FROM produit: read the produit table.
  3. WHERE prix > 50: keep only products priced above 50.
  4. GROUP BY categorie: group the results by category.
  5. HAVING AVG(prix) > 100: keep only categories whose average price is above 100.
  6. ORDER BY prix_moyen DESC: sort by average price, highest first, reusing the alias.
  7. LIMIT 5: keep the first 5 rows.

WHERE or HAVING? WHERE filters rows before grouping, HAVING filters 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, city
  • post: 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.