SQL SELECT Query Examples for Beginners

By Ravindra Bagale, trainer · LinkedIn · Last updated: 18 October 2026

1. Practice data

Why: every query below shows its result. If you load the same ten rows, you get exactly the same answers and can check yourself.

How:

1. Log in with mysql -u root -p.

2. Create a fresh database for practice and switch to it:

   CREATE DATABASE practice;
   USE practice;
   

3. Create the users table you know from the first SQL chapter:

   CREATE TABLE users (id INT, name VARCHAR(100), city VARCHAR(50), age INT);
   

4. Load ten rows in one INSERT:

   INSERT INTO users VALUES
   (1, 'ravi', 'solapur', 30), (2, 'zoya', 'pune', NULL), (3, 'siya', 'pune', 27),
   (4, 'maya', 'mumbai', 25), (5, 'amit', 'nashik', 20), (6, 'neha', 'mumbai', 25),
   (7, 'kiran', 'delhi', 37), (8, 'priya', 'solapur', 22), (9, 'arjun', 'dubai', 37),
   (10, 'ravi', 'nashik', 28);
   

5. SELECT * FROM users; shows all ten rows.

Notice two things we will use later: there are two users named ravi (one in Solapur, one in Nashik), and zoya's age is NULL.

2. DISTINCT: each value once

Why: SELECT city FROM users; returns one city per row, so pune, mumbai, solapur and nashik each appear twice. To answer "which cities do our users live in?", you want each city once.

What: DISTINCT removes repeated rows from the result. The table itself is not changed.

How:

1. SELECT city FROM users; returns 10 rows.
2. SELECT DISTINCT city FROM users; returns 6 rows: solapur, pune, mumbai, nashik, delhi, dubai.
3. SELECT DISTINCT name FROM users; returns 9 names: ravi appears once.
4. SELECT DISTINCT age FROM users; returns 8 values, and NULL counts as one of them.
5. DISTINCT goes right after SELECT and works on the whole row you select. SELECT DISTINCT city, age FROM users; returns 9 rows: only the pair mumbai | 25 appeared twice (maya and neha), so only that pair is shown once.

3. AS: a nickname for a column

Why: column headings like city or SUM(salary) are fine for you, but a report or an app may need clearer names.

What: AS gives a column a new name in the output of this one query. The table keeps its real column names.

How:

1. SELECT city AS gaav, name AS naav FROM users; shows the headings gaav and naav.
2. DESC users; still shows city and name. Nothing in the table changed.
3. An alias with a space needs quotes: SELECT name AS 'full name' FROM users;
4. Aliases are most useful with calculations, for example SELECT MAX(salary) AS highest FROM emp; later in this chapter.

4. ORDER BY: sort the result

Why: without ORDER BY, MariaDB returns rows in whatever order is convenient for it. To show users A to Z, or the oldest first, you must ask.

What: ORDER BY is a clause of SELECT. It comes after FROM (and after WHERE, when you have one).

1. ASC means ascending: A to Z, smallest number first. It is the default, so you rarely type it.
2. DESC means descending: Z to A, biggest number first.
3. NULL counts as the smallest value: it comes first with ASC and last with DESC.

How:

1. SELECT name, age FROM users ORDER BY age; gives zoya (NULL) first, then 20, 22, 25 ... up to 37.
2. SELECT name, age FROM users ORDER BY age DESC; gives the 37-year-olds first and zoya last.
3. SELECT * FROM users ORDER BY name; sorts the names A to Z.
4. SELECT * FROM users ORDER BY id DESC; shows the newest ids first.

5. Two users are named ravi. To decide which ravi comes first, add a second column:

   SELECT name, city FROM users ORDER BY name, city;
   

6. Now ravi | nashik comes before ravi | solapur. The second column is used only when the first one is equal.

7. Each column can have its own direction: ORDER BY name, city DESC sorts names A to Z and, for the two ravis, cities Z to A.
8. Rows with exactly the same sort value (the two 37-year-olds) have no fixed order between them. Add another column if the order matters.

5. WHERE: keep only the rows you want

Why: an app almost never needs every row. "Show users from Mumbai" or "users aged 27 or less" means filtering.

What: WHERE comes after FROM and keeps only the rows for which the condition is true.

1. Text values go in quotes: city = 'mumbai'.
2. Numbers go without quotes: age = 37.

How: the comparison operators, each with an example on our data:

1. = equal: SELECT * FROM users WHERE city = 'mumbai'; gives maya and neha.
2. != not equal: SELECT * FROM users WHERE city != 'mumbai'; gives the other 8 rows.
3. <> is another way to write not equal: WHERE city <> 'mumbai' gives the same 8 rows.
4. NOT in front of a condition also works: WHERE NOT city = 'mumbai'.
5. < less than: WHERE age < 25 gives amit (20) and priya (22).
6. > greater than: WHERE age > 30 gives kiran and arjun (37).
7. <= less than or equal: WHERE age <= 27 gives siya, maya, amit, neha and priya.
8. >= greater than or equal: WHERE age >= 30 gives ravi (30), kiran and arjun.
9. Write the two-character operators in this order: <= and >=, never =< or =>.

6. AND and OR: more than one condition

Why: Real filters rarely have one condition: "users from Pune older than 25". AND narrows the result, OR widens it, and mixing them up returns the wrong rows.

What:

1. AND: both conditions must be true.
2. OR: at least one condition must be true.

How:

1. SELECT * FROM users WHERE city = 'pune' AND age <= 27; gives only siya. zoya is from Pune too, but her age is NULL, so age <= 27 is not true for her.
2. SELECT * FROM users WHERE city = 'nashik' OR age > 30; gives amit and ravi (Nashik) plus kiran and arjun (over 30).

3. Users from Mumbai or Pune:

   SELECT * FROM users WHERE city = 'mumbai' OR city = 'pune';
   

4. Repeat the column name after OR. WHERE city = 'mumbai' OR 'pune' is accepted, but 'pune' alone is not a condition: MariaDB returns only the Mumbai rows (with warnings).

5. WHERE city = 'mumbai' AND city = 'pune' returns an empty set. One row has only one city, so it can never be both.

7. IN: one column, a list of values

Why: three cities with OR means typing city = three times. IN says the same thing in one short list.

How:

1. SELECT * FROM users WHERE city IN ('mumbai', 'pune', 'solapur'); gives the users from those three cities.
2. Numbers work the same way, without quotes: WHERE age IN (20, 25, 27) gives siya, maya, amit and neha.
3. NOT IN is the opposite: WHERE city NOT IN ('mumbai', 'pune') gives everyone else.

8. BETWEEN: a range

Why: Ranges are everywhere: ages 18 to 25, prices from 500 to 1000, orders this month. BETWEEN says it in one short condition.

What: BETWEEN a AND b keeps values from a to b, including both ends.

How:

1. SELECT * FROM users WHERE age BETWEEN 20 AND 27; gives siya (27), maya (25), amit (20), neha (25) and priya (22). Both 20 and 27 are included.
2. It is the same as WHERE age >= 20 AND age <= 27, just shorter.
3. Always write the smaller value first. BETWEEN 27 AND 20 returns nothing.
4. BETWEEN also works on text, in alphabetical order: WHERE name BETWEEN 'neha' AND 'siya' gives neha, priya, both ravis and siya.

9. NULL: IS NULL and IS NOT NULL

What: NULL means "no value at all". It is not 0 and not the text 'NULL'. Because it is no value, comparing it with = is never true.

How:

1. SELECT * FROM users WHERE age = NULL; returns Empty set, even though zoya has no age.
2. SELECT * FROM users WHERE age = 'NULL'; also returns Empty set; it compares with the text NULL.

3. The correct way is IS NULL:

   SELECT * FROM users WHERE age IS NULL;
   

4. That returns zoya.

5. SELECT * FROM users WHERE age IS NOT NULL; returns the other 9 users.

10. LIKE: search inside text

Why: a search box ("names starting with r", "products containing phone") needs patterns, not exact values.

What: LIKE compares text with a pattern that can contain two wildcards:

1. % means any number of characters, even none.
2. _ means exactly one character.

How:

1. SELECT name FROM users WHERE name LIKE 'r%'; gives names that start with r: ravi, ravi.
2. WHERE name LIKE '%ya' gives names that end with ya: zoya, siya, maya, priya.
3. WHERE name LIKE '%ir%' gives names that contain ir: kiran.
4. WHERE name LIKE '____' (four underscores) gives names of exactly 4 characters: ravi, zoya, siya, maya, amit, neha, ravi.
5. WHERE name LIKE '_____' (five underscores) gives kiran, priya, arjun.
6. 's%a' means starts with s and ends with a: siya.
7. Without % or _, LIKE 'ravi' behaves like = 'ravi'.
8. With MariaDB's default settings, LIKE 'R%' also finds ravi: the comparison ignores upper and lower case.

11. LIMIT and OFFSET

Why: a table can have millions of rows. You may want to peek at a few, show "top 3", or show results page by page.

What:

1. LIMIT n returns at most n rows.
2. OFFSET m first skips m rows. Without it, the offset is 0.

How:

1. SELECT * FROM users LIMIT 2; gives the first 2 rows.
2. SELECT * FROM users LIMIT 3 OFFSET 2; skips 2 rows and gives the next 3 (ids 3, 4 and 5).
3. Page 1 of a list with 3 per page is LIMIT 3 OFFSET 0, page 2 is LIMIT 3 OFFSET 3, page 3 is LIMIT 3 OFFSET 6.

4. For a "top N", sort first. The three oldest users:

   SELECT name, age FROM users ORDER BY age DESC LIMIT 3;
   

5. Without ORDER BY, "the first 3 rows" has no guaranteed meaning; always sort when the order matters.

12. A table for salaries: emp, and DECIMAL for money

Why: to practise calculations we need numbers worth adding up. An employee table with departments and salaries is the classic example.

What: for money, use DECIMAL(10,2): up to 10 digits in total, 2 of them after the decimal point. DECIMAL stores the exact value. FLOAT stores an approximation, which can round amounts such as 0.10 slightly; that is fine for a weight, not for money.

How:

1. Create the table:

   CREATE TABLE emp (id INT, name VARCHAR(20), dept VARCHAR(20), salary DECIMAL(10,2));
   

2. Add five employees:

   INSERT INTO emp VALUES (1, 'ravi', 'IT', 100000), (2, 'neha', 'Account', 250000),
   (3, 'govinda', 'IT', 270000), (4, 'pooja', 'HR', 10000), (6, 'ram', 'HR', 25000);
   

3. SELECT * FROM emp; shows the salaries with two decimals, for example 100000.00.

13. Aggregate functions: MAX, MIN, SUM, AVG and COUNT

Why: "What's the highest salary?", "What do we pay in total?" These questions need one answer calculated from many rows.

How:

1. SELECT MAX(salary) FROM emp; gives the highest: 270000.00.
2. SELECT MIN(salary) FROM emp; gives the lowest: 10000.00.
3. SELECT SUM(salary) FROM emp; gives the total: 655000.00.
4. SELECT AVG(salary) FROM emp; gives the average: 131000 (printed with six decimals).
5. SELECT ROUND(AVG(salary), 2) FROM emp; rounds it to 131000.00.
6. SELECT COUNT(salary) FROM emp; counts the salaries: 5.
7. Several at once, with aliases: SELECT MAX(salary) AS highest, MIN(salary) AS lowest FROM emp;

The numbers in the screenshots below are the same; only the number of decimals shown depends on the column type.

13.1 COUNT(*) versus COUNT(column)

1. Add an employee with only an id and a salary:

   INSERT INTO emp (id, salary) VALUES (7, 10000);
   

2. name and dept are NULL for this row.

3. SELECT COUNT(*) FROM emp; gives 6: COUNT(*) counts rows.
4. SELECT COUNT(name) FROM emp; gives 5: COUNT(column) counts only the values that are not NULL.
5. SELECT COUNT(id) FROM emp; gives 6, because every row has an id.
6. So "how many employees do we have?" is COUNT(*). This is a very common interview question.

14. Subquery: a query inside a query

Why: MAX(salary) tells you the highest salary, but not who earns it. You need a second query that uses the first one's answer.

What: a subquery is a SELECT inside brackets, inside another query. The inner query runs first, and the outer query uses its result.

How:

1. Who earns the most?

   SELECT name, salary FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
   

2. The inner query returns 270000.00, so the outer query becomes WHERE salary = 270000.00 and returns govinda.

3. Use the same table name inside and outside: the salaries are in emp, so both parts say FROM emp.
4. Who earns the least? Replace MAX with MIN: you get two rows, pooja and the row with the NULL name, because both earn 10000.00. A subquery returns every row that matches.

5. The second-highest salary, a favourite interview question:

   SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp);
   

6. The inner query finds 270000.00; the outer one finds the highest salary below that: 250000.00 (neha).

15. GROUP BY: one answer per group

Why: SUM(salary) gives one total for the whole company. "Total salary per department" needs one total for each department.

What: GROUP BY dept puts the rows with the same dept together, and the aggregate function runs once per group.

How:

1. SELECT SUM(salary) FROM emp GROUP BY dept; gives four totals, but you can't tell which is which.

2. Add the group column to the SELECT:

   SELECT dept, SUM(salary) FROM emp GROUP BY dept;
   

3. The result: NULL 10000.00, Account 250000.00, HR 35000.00, IT 370000.00.

4. Rows with no department form their own NULL group.
5. Other functions work per group too: SELECT dept, COUNT(*) AS people, AVG(salary) FROM emp GROUP BY dept;
6. Put in SELECT only the grouped column and aggregate functions. A column like name has many values per group, so it has no single answer.

16. HAVING: filter the groups

Why: "Cities with more than one user" is a condition on groups, not on rows. WHERE runs before grouping, so it can't do this; HAVING can.

What: HAVING is a condition on the groups, after GROUP BY. WHERE is a condition on the rows, before grouping.

How:

1. Departments whose total salary is more than 100000:

   SELECT dept, SUM(salary) FROM emp GROUP BY dept HAVING SUM(salary) > 100000;
   

2. The result: Account 250000.00 and IT 370000.00.

3. SELECT * FROM emp WHERE SUM(salary) > 100000; fails with ERROR 1111 ... Invalid use of group function: WHERE looks at one row at a time, before any totals exist.

4. You can use both: WHERE first removes rows, then HAVING removes groups:

   SELECT dept, SUM(salary) AS total FROM emp
   WHERE dept IS NOT NULL
   GROUP BY dept HAVING total > 100000
   ORDER BY total DESC;
   

5. That returns IT (370000.00) then Account (250000.00).

17. UPDATE: change data that exists

Why: a user changes city, or a name was typed wrong. You change the rows that exist; you don't delete and re-insert them.

What:

1. UPDATE changes the data in rows. It is the U in CRUD.
2. ALTER TABLE changes the structure of a table: it adds, changes or removes columns. The rows stay as they are. For example, ALTER TABLE users ADD phone VARCHAR(15); adds an empty phone column. The next SQL chapter covers ALTER TABLE properly.

The form of UPDATE:

UPDATE table SET column = value WHERE condition;

How: the second ravi (id 10, from Nashik) should be called ravindra. The ravi from Solapur must not change.

1. First run a SELECT with the condition you plan to use: SELECT * FROM users WHERE name = 'ravi';
2. It returns two rows. UPDATE users SET name = 'ravindra' WHERE name = 'ravi'; would change both.

3. Use a column that picks exactly one row. Here that is id:

   UPDATE users SET name = 'ravindra' WHERE id = 10;
   

4. MariaDB answers Query OK, 1 row affected and Rows matched: 1 Changed: 1.

5. Check: SELECT * FROM users WHERE name LIKE 'ravi%'; shows ravi (Solapur) and ravindra (Nashik).
6. Change several columns at once with commas: UPDATE users SET age = 26, city = 'pune' WHERE id = 5;
7. Without WHERE, UPDATE changes every row in the table. Always write the WHERE first.
8. Read the "rows affected" number every time. If it is bigger than you expected, your condition matched more rows than you meant.

When you're done practising, type exit; and stop the EC2 instance.

18. The whole class on one page

1. SELECT DISTINCT city FROM users; each value once. SELECT city AS gaav ... renames the heading.
2. ORDER BY col ascending (default), ORDER BY col DESC, ORDER BY name, city for ties.
3. WHERE with =, != / <>, <, >, <=, >=; text in quotes, numbers without.
4. AND both true, OR at least one; repeat the column after OR.
5. IN (...) a list; BETWEEN a AND b a range including both ends, also on text.
6. IS NULL / IS NOT NULL; = NULL never matches.
7. LIKE with % (any characters) and _ (exactly one).
8. LIMIT n OFFSET m; sort with ORDER BY first for a top N.
9. Money in DECIMAL(10,2). MAX, MIN, SUM, AVG, COUNT(*) (rows) and COUNT(col) (non-NULL values).
10. Subquery: WHERE salary = (SELECT MAX(salary) FROM emp); second highest: MAX below the MAX.
11. GROUP BY dept one answer per group; HAVING filters groups, WHERE filters rows.
12. UPDATE t SET col = val WHERE ...; changes data; ALTER TABLE changes structure.

19. Hands-on lab: ten queries on the practice table

Why this lab. Each query below answers a question an app really asks. Use the ten rows from section 1, so your results match exactly.

1. Each city once
   • Command: SELECT DISTINCT city FROM users;
   • You should see: 6 rows: solapur, pune, mumbai, nashik, delhi, dubai.
   • Check: 6 rows in set, not 10.
   • Why: A city dropdown needs each value once. DISTINCT removes repeats from the result only; the table is unchanged.
2. Users from Pune
   • Command: SELECT name, age FROM users WHERE city = 'pune';
   • You should see: zoya | NULL and siya | 27.
   • Check: 2 rows in set.
   • Why: WHERE keeps only matching rows. Text values need quotes.
3. Two cities at once
   • Command: SELECT name FROM users WHERE city IN ('pune', 'mumbai');
   • You should see: zoya, siya, maya, neha.
   • Check: 4 rows in set.
   • Why: IN is a short way to write city = 'pune' OR city = 'mumbai'.
4. An age range
   • Command: SELECT name, age FROM users WHERE age BETWEEN 25 AND 30;
   • You should see: ravi 30, siya 27, maya 25, neha 25, ravi 28.
   • Check: 5 rows in set; 25 and 30 are included.
   • Why: BETWEEN includes both ends. Zoya's NULL age is in no range.
5. Missing values
   • Command: SELECT name FROM users WHERE age IS NULL;
   • You should see: zoya
   • Check: WHERE age = NULL returns Empty set.
   • Why: NULL means no value, so = can never match it. Always use IS NULL.
6. Names starting with r
   • Command: SELECT id, name FROM users WHERE name LIKE 'r%';
   • You should see: 1 ravi and 10 ravi.
   • Check: 2 rows in set.
   • Why: % means any number of characters. This is how a search box finds names as you type.
7. The three oldest
   • Command: SELECT name, age FROM users ORDER BY age DESC LIMIT 3;
   • You should see: kiran and arjun (37), then ravi (30).
   • Check: 3 rows in set.
   • Why: DESC sorts from largest; LIMIT 3 keeps the first three. Without ORDER BY, "the first three" means nothing.
8. Count rows and values
   • Command: SELECT COUNT(*), COUNT(age), MAX(age), MIN(age) FROM users;
   • You should see: 10 | 9 | 37 | 20
   • Check: COUNT(age) is one less because of Zoya's NULL.
   • Why: COUNT(*) counts rows; COUNT(column) skips NULLs. Use COUNT(*) for "how many users".
9. Who is the oldest?
   • Command: SELECT name FROM users WHERE age = (SELECT MAX(age) FROM users);
   • You should see: kiran and arjun.
   • Check: 2 rows in set.
   • Why: MAX gives the value, not the person. The inner query finds 37; the outer one finds who has it.
10. Cities with more than one user
    • Command: SELECT city, COUNT(*) AS total FROM users GROUP BY city HAVING COUNT(*) > 1;
    • You should see: mumbai 2, nashik 2, pune 2, solapur 2.
    • Check: delhi and dubai (1 each) are filtered out.
    • Why: GROUP BY makes one row per city; HAVING filters those groups. WHERE can't, because it runs before the grouping.
11. Change one row
    • Command: UPDATE users SET city = 'pune' WHERE id = 8;
    • You should see: Query OK, 1 row affected and Rows matched: 1 Changed: 1 Warnings: 0.
    • Check: SELECT name, city FROM users WHERE id = 8; → priya | pune.
    • Why: Run the matching SELECT first and check the count. Without WHERE, every row would change.

20. Try at home

Recap

Quick revision cards

Revise this lesson in two minutes. Save the cards on your phone or download them as PNG files. The QR code on each card opens its lesson.

More cards: Ravi Sir's Cards.