Your First SQL Query: SELECT, WHERE and ORDER BY in Action

You have a MySQL server and a practice database. Now let us ask it some real questions. This lesson is a quick tour. Each idea gets its own chapter later.

First, create a small table of products to ask questions about.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL
);

INSERT INTO products (name, category, price) VALUES
    ('Notebook',   'stationery',  80.00),
    ('Gel Pen',    'stationery',  15.50),
    ('Backpack',   'bags',       999.00),
    ('Desk Lamp',  'home',       650.00),
    ('Marker Set', 'stationery', 145.00);

Question 1: show me everything

query.sql

SQL

SELECT * FROM products;

Output

OUTPUT

+----+------------+------------+--------+
| id | name       | category   | price  |
+----+------------+------------+--------+
|  1 | Notebook   | stationery |  80.00 |
|  2 | Gel Pen    | stationery |  15.50 |
|  3 | Backpack   | bags       | 999.00 |
|  4 | Desk Lamp  | home       | 650.00 |
|  5 | Marker Set | stationery | 145.00 |
+----+------------+------------+--------+
5 rows in set

SELECT chooses what to show. * means every column. FROM products says which table.

Question 2: just the names and prices

query.sql

SQL

SELECT name, price FROM products;

Output

OUTPUT

+------------+--------+
| name       | price  |
+------------+--------+
| Notebook   |  80.00 |
| Gel Pen    |  15.50 |
| Backpack   | 999.00 |
| Desk Lamp  | 650.00 |
| Marker Set | 145.00 |
+------------+--------+
5 rows in set

You listed the columns you wanted, in the order you wanted them.

Question 3: only the cheap things

query.sql

SQL

1
2
3

SELECT name, price
FROM products
WHERE price < 100;

Output

OUTPUT

+----------+-------+
| name     | price |
+----------+-------+
| Notebook | 80.00 |
| Gel Pen  | 15.50 |
+----------+-------+
2 rows in set

WHERE keeps only the rows where the condition is true. Rows that fail it are left out.

Question 4: the most expensive first

query.sql

SQL

1
2
3

SELECT name, price
FROM products
ORDER BY price DESC;

Output

OUTPUT

+------------+--------+
| name       | price  |
+------------+--------+
| Backpack   | 999.00 |
| Desk Lamp  | 650.00 |
| Marker Set | 145.00 |
| Notebook   |  80.00 |
| Gel Pen    |  15.50 |
+------------+--------+
5 rows in set

ORDER BY price DESC sorts from the highest price to the lowest. ASC (the default) goes the other way.

Question 5: just the top two

query.sql

SQL

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 2;

Output

OUTPUT

+-----------+--------+
| name      | price  |
+-----------+--------+
| Backpack  | 999.00 |
| Desk Lamp | 650.00 |
+-----------+--------+
2 rows in set

LIMIT 2 keeps only the first two rows of the sorted result.

Question 6: a calculation

query.sql

SQL

1
2
3

SELECT name, price, price * 1.18 AS price_with_tax
FROM products
WHERE category = 'stationery';

Output

OUTPUT

+------------+--------+----------------+
| name       | price  | price_with_tax |
+------------+--------+----------------+
| Notebook   |  80.00 |        94.4000 |
| Gel Pen    |  15.50 |        18.2900 |
| Marker Set | 145.00 |       171.1000 |
+------------+--------+----------------+
3 rows in set

You can calculate in a query. AS price_with_tax gives the new column a name. The condition category = 'stationery' uses text in single quotes.

What you just used

Part	Job
`SELECT`	Which columns to show
`FROM`	Which table
`WHERE`	Which rows to keep
`ORDER BY`	The order of the rows
`LIMIT`	How many rows to return

They always appear in this order in the statement. That is the skeleton of most queries you will ever write.

Common first mistakes

Forgetting the semicolon. The client waits for it.
Double quotes around text. MySQL accepts them by default, but standard SQL uses single quotes for text. Use single quotes.
Misspelling a column. The error says Unknown column 'nmae' in 'field list'. Read it: it tells you which name MySQL could not find.

Exercise: your own question

Using the products table above, write a query that shows the name and category of products that cost more than 100, cheapest first.

Show a possible solution

query.sql

SQL

SELECT name, category
FROM products
WHERE price > 100
ORDER BY price ASC;

Output

OUTPUT

+------------+------------+
| name       | category   |
+------------+------------+
| Marker Set | stationery |
| Desk Lamp  | home       |
| Backpack   | bags       |
+------------+------------+
3 rows in set