Basic Queries

SELECT

SELECT * FROM table;
SELECT c1, c2 FROM table;
SELECT c1 AS alias FROM table;
SELECT * FROM table LIMIT 1;

DISTINCT

SELECT DISTINCT name FROM grades;

WHERE

Adding a WHERE clause restricts query results. Only rows that match the condition are returned.

SELECT * FROM table WHERE condition;

SELECT grade
FROM grades
WHERE courseid = 123 AND name = 'Teppo';

Returns grades for rows where:

• courseid is 123
• name is 'Teppo'

Comparison operators

• = equal
• < less than
• > greater than
• <= less than or equal
• >= greater than or equal

Reference: https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html

Logical operators

Logical operators combine multiple conditions:

• AND or && True if both conditions are true
• OR or || True if either condition is true
• NOT or ! Negates the condition
• XOR True if exactly one condition is true

Reference: https://dev.mysql.com/doc/refman/5.7/en/logical-operators.html

LIKE

simple string matching

SELECT * FROM names WHERE firstname LIKE '%eppo';

IN

Value exists in a list.

SELECT * FROM grades WHERE grade IN (1, 3, 5);

This will show the grades where the grade is 1, 3 or 5.

BETWEEN

Filters rows where a value falls within a given range. The range is inclusive — both the start and end values are included.

SELECT * FROM grades WHERE grade BETWEEN 1 AND 3;

This returns rows where grade is 1, 2, or 3.

BETWEEN also works with dates and text:

SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

IS NULL / IS NOT NULL

Checks whether a value is NULL (missing/unknown). You cannot use = to compare with NULL — you must use IS NULL or IS NOT NULL.

SELECT * FROM grades WHERE grade IS NULL;

Returns rows where grade has no value.

SELECT * FROM grades WHERE grade IS NOT NULL;

Returns rows where grade has a value.

LIMIT / OFFSET

LIMIT restricts how many rows are returned. OFFSET skips a number of rows before starting to return results.

SELECT * FROM grades LIMIT 10;

Returns only the first 10 rows.

SELECT * FROM grades LIMIT 10 OFFSET 20;

Skips the first 20 rows, then returns the next 10. Useful for pagination — for example, showing page 3 with 10 results per page.

Aggregate functions

Aggregate functions perform a calculation on a set of rows and return a single value. Often used together with GROUP BY.

• COUNT() — number of rows
• SUM() — total of numeric values
• AVG() — average of numeric values
• MIN() — smallest value
• MAX() — largest value

SELECT COUNT(*) FROM grades;

Returns the total number of rows in the grades table.

SELECT AVG(grade) FROM grades;

Returns the average grade.

SELECT MIN(grade), MAX(grade) FROM grades;

Returns the lowest and highest grade.

SELECT name, SUM(profit) FROM sales GROUP BY name;

Returns the total profit for each name.

GROUP BY

SELECT year, SUM(profit) FROM sales GROUP BY year;

HAVING

SELECT kunta, COUNT(asukas_id)
FROM asukkaat
GROUP BY kunta
HAVING COUNT(asukas_id) > 5000;

ORDER BY

Query results can be sorted in ascending or descending order.

Keywords:

• ASC — ascending Numbers increase, text follows alphabetical order
• DESC — descending Numbers decrease, text follows reverse alphabetical order

Sorting by a single column

SELECT *
FROM grades
ORDER BY grade DESC;

Sorts grades from highest to lowest.

Sorting by multiple columns

You can define multiple sorting criteria.

SELECT grade, name
FROM grades
ORDER BY grade DESC, name ASC;

Explanation:

• First sorts by grade (descending)
• If grades are equal, sorts by name (ascending)

TIP

• Sorting happens after filtering (WHERE).
• Sorting affects only the result set, not stored data.

UNION

SELECT city FROM customers
UNION
SELECT city FROM suppliers
ORDER BY city;

Subqueries

IN subquery

SELECT * FROM items
WHERE categoryid IN (
  SELECT id FROM categories WHERE department = 'Technology'
);

Scalar subquery

SELECT * FROM invoices
WHERE total = (SELECT MAX(total) FROM invoices);

Exercises

1. Write a query that selects all rows from grades where grade is greater than 3.
2. Select rows using IN with at least three values.
3. Combine conditions using AND and OR.
4. Use NOT to exclude certain rows.
5. Write a query that behaves differently with AND vs XOR.
6. Use DISTINCT and ORDER BY in the same query.
7. Use LIKE to find names that start with 'T'.
8. Select rows where grade is BETWEEN 2 and 4.
9. Find all rows where a column value IS NULL.
10. Return only the first 5 rows using LIMIT.
11. Use LIMIT and OFFSET to get rows 11–20.
12. Use COUNT to find the number of rows in a table.
13. Use AVG, MIN, and MAX on a numeric column.
14. Use SUM with GROUP BY to get totals per group.
15. Sort grades by grade descending and name ascending.
16. Combine WHERE and ORDER BY in the same query.
17. Filter groups with HAVING to show only groups with more than 3 rows.
18. Combine result sets using UNION.
19. Write IN subqueries
20. Write scalar subqueries