01-select-basics.md

Chapter 01: SELECT Basics

In this chapter, you will learn the fundamental building blocks of SQL: how to retrieve data from a database. We'll cover everything from simple data extraction to basic arithmetic and string operations.

1.1 Your First Query — SELECT ALL COLUMNS

The asterisk (*) is a wildcard that tells the database to return all columns from the specified table. While convenient, it's important to be cautious with SELECT * on large tables to avoid performance issues.

SELECT *
FROM customers
LIMIT 10;

What to expect: You'll see the first 10 rows of the customers table, including columns like customer_id, customer_unique_id, customer_zip_code_prefix, customer_city, and customer_state.

1.2 Selecting Specific Columns

In production environments, it's best practice to only request the data you actually need. This reduces network load and improves query performance.

SELECT
    customer_city,
    customer_state
FROM customers
LIMIT 10;

1.3 Column Aliases

You can rename columns in your output using the AS keyword. This is especially useful for making calculations more readable or for simplifying complex column names.

SELECT
    customer_city    AS city,
    customer_state   AS state
FROM customers
LIMIT 10;

1.4 Exploring Other Tables

Let's take a quick look at the other tables in our dataset to understand their structure.

Orders Table

SELECT *
FROM orders
LIMIT 5;

Products Table

SELECT *
FROM products
LIMIT 5;

Sellers Table

SELECT *
FROM sellers
LIMIT 5;

1.5 Counting Rows

The COUNT(*) function allows you to quickly determine how many records are in a table.

SELECT COUNT(*) AS total_customers FROM customers; -- ~99,441 rows
SELECT COUNT(*) AS total_orders FROM orders;       -- ~99,441 rows
SELECT COUNT(*) AS total_products FROM products;   -- ~32,951 rows
SELECT COUNT(*) AS total_sellers FROM sellers;     -- ~3,095 rows

1.6 Expressions and Calculations

SQL isn't just for fetching data; it can also perform arithmetic. You can calculate values on the fly within your SELECT statement.

SELECT
    order_id,
    price,
    freight_value,
    price + freight_value AS total_cost
FROM order_items
LIMIT 10;

1.7 String Concatenation

You can combine multiple string columns into one using the || operator (ANSI SQL standard).

SELECT
    customer_city || ', ' || customer_state AS location
FROM customers
LIMIT 10;

---

Exercises

Try these on your own to reinforce what you've learned:

1. Select the first 20 rows from the order_payments table. What columns does it have?
2. Select only the order_id and payment_value columns from order_payments. Limit to 15 rows.
3. Show order_items with a column called "item_total" that is price + freight_value. Show only 10 rows.
4. How many rows are in the order_reviews table?
5. Show seller_city and seller_state from the sellers table, aliased as "city" and "state". Limit to 10 rows.

Solutions

-- Exercise 1
SELECT * FROM order_payments LIMIT 20;

-- Exercise 2
SELECT order_id, payment_value
FROM order_payments
LIMIT 15;

-- Exercise 3
SELECT
    order_id,
    price,
    freight_value,
    price + freight_value AS item_total
FROM order_items
LIMIT 10;

-- Exercise 4
SELECT COUNT(*) AS total_reviews FROM order_reviews;

-- Exercise 5
SELECT
    seller_city  AS city,
    seller_state AS state
FROM sellers
LIMIT 10;