Mohamed Idris

Posted on May 29

Part 02: SELECT, Your First Questions

#sql #database #learning

Part of the "SQL: Zero to Ninja" series.

You finally have a database full of users. Now you need to actually get
something out of it. Maybe the screen needs a list of names. Maybe the API needs
emails. How do you ask? You use SELECT, the most common word in all of SQL.
Once this clicks, you can start answering real questions about your data.

The idea in one line

SELECT is how you tell the database which columns you want and which
table to pull them from.

The metaphor: ordering from a menu

Walk into a restaurant. You do not shout "bring me everything in the kitchen!"
That would be silly. You read the menu and ask for exactly the dishes you want:
the soup and the bread, please.

SELECT is your order. The columns are the dishes. You name what you want, and
the database brings back just that. Keep this picture in your head, because the
biggest beginner mistake is forgetting it and ordering the whole kitchen.

The shape of a SELECT

SELECT name, email
FROM users;

Read it like a sentence: "select name and email, from users." Two parts:

SELECT  name, email     <-- the dishes you want (columns)
FROM    users           <-- the menu you order from (table)

The database goes to the users table and hands back only those two columns for
every row. Clean and simple.

The lazy way: SELECT *

You will see this everywhere:

SELECT * FROM users;

The * means "every column." It is the "bring me everything in the kitchen"
order. It feels easy, and for poking around by hand it is fine. But in real app
code it has two real problems.

Problem one: you grab data you do not need. Say users also has a
password_hash and a long bio. Your screen only shows the name. But * drags
all of it across the network anyway. That is wasted time and wasted memory.

SELECT *  -->  id, name, email, country, password_hash, bio, created_at ...
               (you wanted "name". you got a whole truckload.)

Problem two: column order can change. Someone adds a new column next month,
or moves one around. Code that trusted "the second column is the email" suddenly
breaks. Naming your columns protects you.

The right way: name your columns

-- Wrong way (lazy and fragile)
SELECT * FROM users;

-- Right way (clear and safe)
SELECT id, name, email
FROM users;

Now you get exactly three columns, in an order you decided. If someone adds a
bio column tomorrow, your query does not care. You asked for three dishes, you
get three dishes.

Renaming output with AS

Sometimes the column name in the database is not the name you want in your
result. Maybe your API wants full_name instead of name. You can rename the
output with AS:

SELECT name AS full_name,
       email AS contact_email
FROM users;

The result comes back with the new labels:

full_name | contact_email
----------+------------------
Sara      | sara@mail.com
Omar      | omar@mail.com

The table is untouched. AS only renames what shows up in the result. This is
super handy when the front end or an API expects a specific shape.

DISTINCT: drop the duplicates

Imagine you want a list of the countries your users come from. Your users
table might have many people from Egypt:

SELECT country
FROM users;

country
-------
Egypt
Egypt
Jordan
Egypt
Tunisia

That repeats Egypt three times. Ugly for a dropdown menu. Add DISTINCT and the
database removes the repeats:

SELECT DISTINCT country
FROM users;

country
-------
Egypt
Jordan
Tunisia

One row per unique value. DISTINCT is your tool whenever you want "the list of
different things" instead of "every single occurrence."

A real case

You are building a sign-up form with a "Where are you from?" dropdown. You want
it filled with the countries your users actually picked, no repeats. That is a
one-liner:

SELECT DISTINCT country
FROM users;

Or you are building a user profile API and the front end expects fields named
displayName and mail:

SELECT name AS displayName,
       email AS mail
FROM users;

Both are everyday tasks, and both are just SELECT doing its job.

Gotchas juniors hit

Leaning on SELECT * in app code. Fine for quick exploring, risky in real code. Name your columns so you stay fast and safe.
Forgetting the comma. SELECT name email is broken. The database thinks email is an alias for name. You need SELECT name, email.
Thinking DISTINCT works on one column only. SELECT DISTINCT country, name looks at the whole row (country plus name together), not just country. So the "uniqueness" covers every column you listed, not just the first one.

Recap

SELECT columns FROM table is how you ask for data.
SELECT * grabs everything, which is lazy and fragile in real code. Name your columns instead.
AS renames a column in the result, great for shaping API output.
DISTINCT removes duplicate rows so you get a clean list of unique values.

Next up: Part 03: WHERE, where you stop grabbing every row and start asking
"only the ones that match this."

Your turn

Using the products table (id, name, price, category), write a SELECT that
returns the list of unique categories, with the column renamed to
product_type in the result. If you can explain why you reached for DISTINCT
and AS, you have got it.

Top comments (1)

Mohamed Idris • May 29

Part 02 Practice: SELECT, Your First Questions

Let's put SELECT to work. These get a little harder as you go. Try writing
each query yourself before you open the solution.

We are using the series tables:

users (id, name, email, country, created_at)
orders (id, user_id, total, status, created_at)
products (id, name, price, category)
order_items (id, order_id, product_id, quantity)

1. Grab one column

Return the name of every user.

Solution

SELECT name
FROM users;

SELECT names the column, FROM names the table. The simplest query there is.

2. Grab a few columns

Return the name, email, and country of every user, in that order.

Solution

SELECT name, email, country
FROM users;

List the columns with commas between them. They come back in the order you
wrote them, so you control the shape of the result.

3. Fix the lazy query

A teammate wrote SELECT * FROM products; but the page only needs the product
name and price. Rewrite it the safe way.

Solution

SELECT name, price
FROM products;

Naming the columns means you only pull what you need, and the query does not
break if someone adds a new column to products later.

4. Rename for the API

The front end expects the product name in a field called title and the price
in a field called cost. Return those two columns with the new names.

Solution

SELECT name AS title,
       price AS cost
FROM products;

AS renames the column only in the result. The real table keeps its original
column names. This is how you match what an API or front end expects.

5. Unique list

Return the list of distinct categories in the products table, so each
category shows up only once.

Solution

SELECT DISTINCT category
FROM products;

DISTINCT removes repeated values. Perfect for filling a filter or a dropdown
menu without showing "tech" five times.

6. Unique countries, renamed

Return the unique countries your users come from, but show the column as
user_country in the result.

Solution

SELECT DISTINCT country AS user_country
FROM users;

You can stack ideas: DISTINCT strips the duplicates, and AS renames the
output. SQL lets you combine small tools to get exactly what you want.

Nice work. You can now pull the exact columns you need, rename them, and trim
duplicates. In Part 03 you will add WHERE, so you can ask for only the rows
that match a condition. Keep going, ninja.