---
title: "SQL Fundamentals – Chapter 1: Solutions"
id: "2373"
type: "post"
slug: "sql-fundamentals-chapter-1-solutions"
published_at: "2026-02-14T16:52:49+00:00"
modified_at: "2026-02-14T16:52:49+00:00"
url: "https://labs.postgreshelp.com/sql-fundamentals-chapter-1-solutions/"
markdown_url: "https://labs.postgreshelp.com/sql-fundamentals-chapter-1-solutions.md"
excerpt: "Chapter 01: SQL Basics & Data Retrieval – SOLUTIONS Topic: Basic SELECT Statements Note: These are example solutions. Some exercises […]"
taxonomy_category:
  - "Uncategorized"
---

# 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 
```

### 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.