Lesson 1: SELECT Basics

In Lesson 0, you ran SELECT id, name, email, grade FROM customers LIMIT 3. In this lesson, we will learn SELECT in depth.

SELECT is the most frequently used command in SQL. It retrieves specific columns from a table.

Retrieving All Columns -- SELECT *

SELECT * retrieves all columns from the table. It is useful for quickly scanning what data exists in a table.

SELECT * FROM categories;

Result:

Retrieving Specific Columns

By listing column names directly, you can view only the columns you want in a clean format.

Let's revisit the query from Lesson 0:

SELECT name, price, stock_qty
FROM products;

Result:

The result is much easier to read than SELECT *. The column order in the result also follows the order listed in SELECT.

-- Changing the order changes the result order
SELECT stock_qty, name, price
FROM products;

Column Aliases (AS)

Why Aliases Are Needed

If the result column is named stock_qty, someone who doesn't know the code might not understand what it means. Using AS, you can change the column name displayed in the result.

SELECT
    name      AS product_name,
    price     AS sale_price,
    stock_qty AS inventory
FROM products;

Result:

Adding Aliases to Calculations

Aliases are particularly useful for naming calculation results. Without an alias, the column name becomes the expression itself, like price * 0.9.

SELECT
    name,
    price,
    price * 0.9 AS discounted_price
FROM products;

Result:

String Literal Columns

You can also add fixed values to the result that don't exist in the actual table:

SELECT
    name,
    price,
    'KRW' AS currency
FROM products;

Table Aliases

AS can be used to assign aliases to tables as well as columns. It may seem unnecessary now since we are using only one table, but it becomes essential in Lesson 7 (JOIN) when combining multiple tables.

-- Assign the alias p to the products table
SELECT p.name, p.price
FROM products AS p;

You can write p.name instead of products.name for brevity. AS can be omitted:

-- AS omitted (same result)
SELECT p.name, p.price
FROM products p;

Arithmetic Operations

You can perform arithmetic operations inside SELECT. They only appear in the result and do not change the original data.

SELECT
    name,
    price,
    cost_price,
    price - cost_price AS margin
FROM products;

The first product's margin is negative (-99200). Cases where the cost exceeds the selling price do exist in real data.

DISTINCT -- Removing Duplicates

When to Use It

Use it when you want to know "what values exist in this column." For example, to find out the types of membership grades at TechShop:

SELECT DISTINCT grade
FROM customers;

Result:

You can see there are 4 grades. Without DISTINCT, all 5,230 rows would be output.

NULL Is Treated as One Value

SELECT DISTINCT gender
FROM customers;

The NULL we learned about in Lesson 0 appears here. Since some customers did not enter their gender, NULL is included as one unique value.

DISTINCT on Multiple Columns

DISTINCT removes duplicates based on the combination of the listed columns:

SELECT DISTINCT grade, gender
FROM customers;

Only rows with unique combinations of grade and gender remain.

Summary

Problem 1

Retrieve all columns from the categories table.

SELECT * FROM categories;

Problem 2

Retrieve only name and price from the products table.

SELECT name, price FROM products;

Problem 3

Retrieve department, role, name in that order from the staff table.

SELECT department, role, name
FROM staff;

Problem 4

Retrieve name, email, grade from the customers table with aliases customer_name, email_address, tier.

SELECT
    name  AS customer_name,
    email AS email_address,
    grade AS tier
FROM customers;

Problem 5

Retrieve name, price from the products table, and add a 10% discounted price with the alias discounted_price.

SELECT
    name,
    price,
    price * 0.9 AS discounted_price
FROM products;

Problem 6

Retrieve name, price, cost_price from the products table, and add the margin (price - cost_price) and margin rate ((price - cost_price) * 100 / price) with aliases margin and margin_rate.

SELECT
    name,
    price,
    cost_price,
    price - cost_price                  AS margin,
    (price - cost_price) * 100 / price  AS margin_rate
FROM products;

Problem 7

Retrieve the unique status values from the orders table.

SELECT DISTINCT status
FROM orders;

Problem 8

Retrieve the unique method values from the payments table to see what payment methods TechShop supports.

SELECT DISTINCT method
FROM payments;

Problem 9

Retrieve name, price, stock_qty from the products table, and add price * stock_qty with the alias inventory_value.

SELECT
    name,
    price,
    stock_qty,
    price * stock_qty AS inventory_value
FROM products;

Problem 10

Retrieve the unique combinations of grade and gender from the customers table. How many combinations are there?

SELECT DISTINCT grade, gender
FROM customers;

Scoring Guide

Problem Areas:

Next: Lesson 2: Filtering with WHERE