Building Simple Queries with SELECT, WHERE, and ORDER BY

Now it's time to put it all together and learn how to build simple but super useful queries using a combo of SELECT, WHERE, and ORDER BY. This skill will come in handy for all sorts of stuff, from getting user lists to making small reports.

Before we dive in, let's remind ourselves what a basic SQL query looks like:

SELECT column1, column2, column3
FROM table
WHERE condition
ORDER BY column ASC
LIMIT number_of_rows
OFFSET number_of_rows;

If SQL was a novel, SELECT would be the chapter title, FROM would be the content, and WHERE would be the plot explanation. And ORDER BY adds some order, so it's more fun to read!

The order of the operators is strictly fixed. You can skip some of them, but you can't change their order.

Example 1: Selecting Names of Students with Top Grades

Let's say we have a students table with the following structure and data:

You need to get a list of all students with a top grade (that is, grade = 'A'), sorted by name. Here's how we do it:

SELECT name
FROM students
WHERE grade = 'A'
ORDER BY name ASC;

Result:

Explanation:

1. SELECT name — we're only picking the name. No need to waste resources pulling data we don't need.
2. WHERE grade = 'A' — filter for those with a top grade.
3. ORDER BY name ASC — sort the student names alphabetically.

Example 2: Finding Products by Price

Now let's say we have a products table with info about products:

Our task is to get a list of products from the "Electronics" category, sorted by price in descending order. The query will look like this:

SELECT product_name, price
FROM products
WHERE category = 'Electronics'
ORDER BY price DESC;

Result:

Explanation:

1. SELECT product_name, price — select the product name and its price.
2. WHERE category = 'Electronics' — filter only products from the "Electronics" category.
3. ORDER BY price DESC — sort results by price descending (most expensive first).

Example 3: Complex Filters with Logic

Let's use the same students table. Suppose we need to find all students older than 21 who have a grade of B or C, and sort them by age. Here we're combining several conditions:

SELECT name, age, grade
FROM students
WHERE age > 21 AND (grade = 'B' OR grade = 'C')
ORDER BY age ASC;

Result:

Explanation:

1. WHERE age > 21 — pick only students older than 21.
2. AND (grade = 'B' OR grade = 'C') — filter those with grade B or C. Parentheses are used to group conditions.
3. ORDER BY age ASC — sort the result by age, youngest first.

Example 4: Combining Sorting and Filtering

Back to the products table. We want to find products cheaper than 50,000, order them by category, and inside each category — by price (from lowest to highest).

SELECT product_name, category, price
FROM products
WHERE price < 50000
ORDER BY category ASC, price ASC;

Result:

Explanation:

1. WHERE price < 50000 — first filter products, leaving only those cheaper than 50,000.
2. ORDER BY category ASC, price ASC — sort by category (alphabetically), and inside each category sort by price ascending.

Example 5: Common Mistake — Skipping Condition Grouping

Now let's see what can go wrong. What if we forget to group conditions, like with logical operators?

Incorrect query:

SELECT name, age, grade
FROM students
WHERE age > 21 AND grade = 'B' OR grade = 'C'
ORDER BY age ASC;

Here OR grade = 'C' will run without considering AND age > 21, and the query will return the wrong result. We'll get everyone with grade C, even if they're younger than 21.

So always use parentheses for clarity:

WHERE age > 21 AND (grade = 'B' OR grade = 'C')

2

Task

SQL SELF, level 3, lesson 2

Locked

Filter and sort products

Filter and sort products

Open

2

Task

SQL SELF, level 3, lesson 2

Locked

Complex student filter

Complex student filter

Open