Chapter 01: SQL Basics & Data Retrieval – SOLUTIONS

Topic: Basic SELECT Statements

Note: These are example solutions. Some exercises may have multiple correct approaches.

EXERCISE SOLUTIONS

Exercise 1: SELECT All Customers

SELECT * FROM customers;

Explanation:

  • SELECT * means “SELECT all columns”
  • FROM customers specifies the TABLE
  • Should RETURN all 10 customers WITH all 7 columns

Exercise 2: SELECT Specific Columns

SELECT customer_name, email FROM customers;

Explanation:

  • List specific columns separated BY commas
  • ORDER OF columns IN SELECT determines output ORDER
  • RETURNS 10 rows, 2 columns

Exercise 3: SELECT WITH Alias

SELECT
    customer_name AS "Customer",
    country AS "Location"
FROM customers;

Explanation:

  • AS keyword creates an alias (temporary column name)
  • Quotes are needed FOR aliases WITH spaces OR mixed CASE
  • Without quotes: customer_name AS Customer (lowercase IN output)
  • WITH quotes: customer_name AS “Customer” (preserves CASE)

Alternative (without quotes):

SELECT customer_name AS customer, country AS location FROM customers;

Exercise 4: SELECT FROM Products

SELECT product_name, category, price
FROM products;

Explanation:

  • Straightforward column selection
  • RETURNS 10 products WITH 3 columns EACH

Exercise 5: Simple Calculation

SELECT
    product_name,
    price * stock_quantity AS "Inventory Value"
FROM products;

Explanation:

  • Arithmetic operations work directly IN SELECT
  • price * stock_quantity performs multiplication
  • Result IS calculated FOR EACH ROW
  • AS renames the calculated column

Example output FOR “Laptop Pro 15”:
1299.99 * 45 = 58,499.55

Exercise 6: Multiple Calculations

SELECT
    product_name,
    price,
    price * 0.8 AS "Sale Price",
    price - (price * 0.8) AS "Savings"
FROM products;

Explanation:

  • Multiple calculations IN one query
  • price * 0.8 = price minus 20% (i.e., 80% OF original)
  • Alternative FOR 20% discount: price * 0.2
  • Parentheses ensure correct ORDER OF operations

Better alternative:

SELECT
    product_name,
    price,
    ROUND(price * 0.8, 2) AS "Sale Price",
    ROUND(price * 0.2, 2) AS "Savings"
FROM products;

ROUND FUNCTION ensures 2 decimal places

Exercise 7: Concatenate Strings

SELECT
    customer_name || ' (' || country || ')' AS "Customer Info"
FROM customers;

Explanation:

  • || IS PostgreSQL’s concatenation operator
  • Combines strings together
  • ‘ (‘ AND ‘)’ are literal strings
  • Result: “John Smith (USA)”

Alternative USING CONCAT FUNCTION:

SELECT CONCAT(customer_name, ' (', country, ')') AS "Customer Info"
FROM customers;

CONCAT automatically handles NULLs better than ||

Exercise 8: SELECT DISTINCT Values

SELECT DISTINCT country
FROM customers;

Explanation:

  • DISTINCT removes duplicate values
  • RETURNS EACH country only once
  • Should RETURN 10 rows (all countries are UNIQUE IN sample data)

IF there were duplicates, LIKE multiple customers FROM USA:

SELECT country FROM customers;  -- Might RETURN USA 3 times
SELECT DISTINCT country FROM customers;  -- RETURNS USA once

Exercise 9: COUNT All Rows

SELECT COUNT(*) AS "Total Customers"
FROM customers;

Explanation:

  • COUNT(*) counts all rows
  • RETURNS single number: 10
  • AS names the result column

Alternative:

SELECT COUNT(customer_id) AS "Total Customers" FROM customers;

Difference:

  • COUNT(*) counts all rows (including NULLs)
  • COUNT(column) counts non-NULL values IN that column

Exercise 10: Days Since Registration

SELECT
    customer_name,
    CURRENT_DATE - registration_date AS "Days Since Registration"
FROM customers;

Explanation:

  • CURRENT_DATE RETURNS today’s DATE
  • Subtracting dates gives number OF days (AS INTEGER)
  • Result varies depending ON WHEN you run the query

Example: IF today IS 2026-02-14 AND registration was 2025-01-15:
Days = 2026-02-14 – 2025-01-15 = 395 days

Alternative WITH age FUNCTION:

SELECT customer_name,
       AGE(CURRENT_DATE, registration_date) AS "Time Since Registration"
FROM customers;

AGE RETURNS an interval LIKE “1 year 1 month 0 days”

Exercise 11: Has Email CHECK

SELECT
    customer_name,
    email,
    email IS NOT NULL AS "Has Email"
FROM customers;

Explanation:

  • email IS NOT NULL RETURNS TRUE OR FALSE
  • IS NOT NULL IS the correct way TO CHECK FOR non-NULL values
  • DO NOT use: email != NULL (this IS always NULL)

Output:

  • Has Email will be TRUE FOR all rows IN our sample (all have emails)
  • IF any customer had NULL email, it would show FALSE

Exercise 12: INSERT WITH RETURNING (PostgreSQL-specific)

INSERT INTO customers (customer_name, email, country, city)
VALUES ('Test User', '[email protected]', 'TestLand', 'TestCity')
RETURNING customer_id, customer_name;

Explanation:

  • RETURNING clause IS PostgreSQL-specific (NOT standard SQL)
  • RETURNS values FROM the inserted ROW
  • customer_id IS auto-generated (SERIAL type)
  • Eliminates need FOR separate SELECT AFTER INSERT

Output example:

customer_id | customer_name
-----------+---------------
         11 | Test User

Without RETURNING, you’d need:

INSERT INTO customers (...) VALUES (...);
SELECT customer_id FROM customers WHERE email = '[email protected]';

Exercise 13: Cast TO INTEGER

SELECT
    order_id,
    total_amount,
    CAST(total_amount AS INTEGER) AS "Amount (Rounded)"
FROM orders;

Explanation:

  • CAST converts one data type TO another
  • Casting NUMERIC TO INTEGER truncates decimals (doesn’t round)
  • 1329.98 becomes 1329 (NOT 1330)

PostgreSQL shorthand:

SELECT order_id, total_amount::INTEGER AS "Amount (Rounded)"
FROM orders;

TO actually ROUND (NOT truncate):

SELECT order_id, ROUND(total_amount)::INTEGER AS "Amount (Rounded)"
FROM orders;

Exercise 14: Current DATE AND Time

SELECT
    CURRENT_DATE AS "Today",
    CURRENT_TIME AS "Now (Time)",
    CURRENT_TIMESTAMP AS "Now (FULL)";

Explanation:

  • CURRENT_DATE: Just the DATE (e.g., 2026-02-14)
  • CURRENT_TIME: Just the time WITH timezone (e.g., 15:30:45.123456-05:00)
  • CURRENT_TIMESTAMP: DATE AND time (e.g., 2026-02-14 15:30:45.123456-05:00)

Alternative functions:

  • NOW(): Same AS CURRENT_TIMESTAMP
  • LOCALTIMESTAMP: Without timezone
  • LOCALTIME: Time without timezone

Example:

SELECT NOW(), LOCALTIMESTAMP;

Exercise 15: PostgreSQL Version

SELECT version();

Explanation:

  • version() IS a system FUNCTION
  • RETURNS FULL version string
  • Includes OS, compiler info

Example output:
“PostgreSQL 17.0 ON x86_64-pc-linux-gnu, compiled BY gcc (GCC) 11.2.0, 64-bit”

Other useful system functions:

  • current_database(): Name OF current database
  • current_schema(): Current schema
  • pg_backend_pid(): Your connection’s process ID

BONUS: Additional Practice Solutions

Bonus 1: Show all products under $100

SELECT product_name, price
FROM products
WHERE price < 100;

Bonus 2: COUNT products BY category

SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

Bonus 3: Show customer name AND total orders (requires JOIN - preview)

SELECT
    c.customer_name,
    COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name
ORDER BY total_orders DESC;

Note: This uses concepts FROM future chapters:

  • JOIN (Chapter 05)
  • GROUP BY (Chapter 04)
  • ORDER BY (Chapter 03)

Don't worry IF you don't understand it yet!

COMMON MISTAKES TO AVOID

MISTAKE 1: USING = FOR NULL comparison

❌ WRONG:

SELECT * FROM customers WHERE email = NULL;

✅ CORRECT:

SELECT * FROM customers WHERE email IS NULL;

MISTAKE 2: Forgetting quotes FOR aliases WITH spaces

❌ WRONG:

SELECT customer_name AS Customer Name FROM customers;
-- (Syntax error)

✅ CORRECT:

SELECT customer_name AS "Customer Name" FROM customers;

MISTAKE 3: USING SELECT * IN production code

❌ AVOID:

SELECT * FROM customers;

✅ BETTER:

SELECT customer_id, customer_name, email FROM customers;

Why?

  • SELECT * IS bad FOR performance
  • Retrieves unnecessary data
  • Breaks IF TABLE structure changes
  • Use it only FOR exploration/learning

MISTAKE 4: NOT USING semicolons IN scripts

❌ RISKY:

SELECT * FROM customers
SELECT * FROM orders

✅ SAFE:

SELECT * FROM customers;
SELECT * FROM orders;

IN psql, multiple statements without semicolons get concatenated!

PERFORMANCE TIPS (Preview)

TIP 1: SELECT only columns you need

SLOW: SELECT * FROM large_table;
FAST: SELECT id, name FROM large_table;

TIP 2: Use LIMIT FOR testing queries

SELECT * FROM million_row_table LIMIT 10;

(Stops AFTER 10 rows, even IF millions exist)

TIP 3: DISTINCT can be slow ON large tables

IF you need UNIQUE values, consider IF an INDEX EXISTS

NEXT STEPS

Congratulations ON completing Chapter 01!

You now understand:

  • ✅ Basic SELECT syntax
  • ✅ Column aliases
  • ✅ Simple calculations
  • ✅ String concatenation
  • ✅ DISTINCT FOR UNIQUE values
  • ✅ Aggregate functions (COUNT)
  • ✅ Type casting
  • ✅ PostgreSQL-specific features (RETURNING)

Next Chapter: Data Manipulation LANGUAGE (DML)

  • INSERT, UPDATE, DELETE operations
  • Bulk operations WITH COPY
  • UPSERT WITH ON CONFLICT
  • Transactions basics

Keep practicing! Try creating your own tables AND queries.

Scroll to Top