01-select-basics.sql

-- ============================================================
-- SQL Masterclass — Chapter 01: SELECT Basics
-- ============================================================
-- 🟢 BEGINNER
--
-- In this chapter you will learn:
--   • How to retrieve data from a table with SELECT
--   • How to select specific columns
--   • How to limit the number of rows returned
--   • How to create column aliases
--   • How to use comments in SQL
-- ============================================================


-- ============================================================
-- 1.1  YOUR FIRST QUERY — SELECT ALL COLUMNS
-- ============================================================
-- The asterisk (*) means "all columns".
-- Always be careful with SELECT * on large tables!

SELECT *
FROM customers
LIMIT 10;

-- Expected: 10 rows showing customer_id, customer_unique_id,
-- customer_zip_code_prefix, customer_city, customer_state


-- ============================================================
-- 1.2  SELECTING SPECIFIC COLUMNS
-- ============================================================
-- Best practice: Always select only the columns you need.

SELECT
    customer_city,
    customer_state
FROM customers
LIMIT 10;


-- ============================================================
-- 1.3  COLUMN ALIASES
-- ============================================================
-- Use AS to rename columns in the output for readability.

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


-- ============================================================
-- 1.4  EXPLORING OTHER TABLES
-- ============================================================
-- Let's see what data we have in the orders table.

SELECT *
FROM orders
LIMIT 5;

-- Take note of the columns:
-- order_id, customer_id, order_status, order_purchase_timestamp,
-- order_approved_at, order_delivered_carrier_date,
-- order_delivered_customer_date, order_estimated_delivery_date


-- Let's look at products too.

SELECT *
FROM products
LIMIT 5;

-- And the sellers:

SELECT *
FROM sellers
LIMIT 5;


-- ============================================================
-- 1.5  COUNTING ROWS
-- ============================================================
-- COUNT(*) tells you how many rows are in a table.

SELECT COUNT(*) AS total_customers
FROM customers;

-- Expected: ~99,441 rows

SELECT COUNT(*) AS total_orders
FROM orders;

-- Expected: ~99,441 rows

SELECT COUNT(*) AS total_products
FROM products;

-- Expected: ~32,951 rows

SELECT COUNT(*) AS total_sellers
FROM sellers;

-- Expected: ~3,095 rows


-- ============================================================
-- 1.6  EXPRESSIONS AND CALCULATIONS
-- ============================================================
-- You can perform arithmetic directly in SELECT.

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


-- ============================================================
-- 1.7  STRING CONCATENATION
-- ============================================================
-- Combine columns into a single output.
-- ANSI SQL uses || for concatenation.

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


-- ============================================================
-- EXERCISES
-- ============================================================
-- Try these on your own before looking at the solutions below!

-- Exercise 1: Select the first 20 rows from the order_payments table.
--             What columns does it have?

-- Exercise 2: Select only the order_id and payment_value columns
--             from order_payments. Limit to 15 rows.

-- Exercise 3: Show order_items with a column called "item_total"
--             that is price + freight_value. Show only 10 rows.

-- Exercise 4: How many rows are in the order_reviews table?

-- Exercise 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;