---
title: "SQL Fundamentals – Chapter 1: Lab Exercises"
id: "2370"
type: "post"
slug: "sql-fundamentals-chapter-1-lab-exercises"
published_at: "2026-02-14T16:51:30+00:00"
modified_at: "2026-02-14T16:51:30+00:00"
url: "https://labs.postgreshelp.com/sql-fundamentals-chapter-1-lab-exercises/"
markdown_url: "https://labs.postgreshelp.com/sql-fundamentals-chapter-1-lab-exercises.md"
excerpt: "Chapter 01: SQL Basics & Data Retrieval – Lab Exercises Topic: Basic SELECT Statements Instructions: Read EACH exercise carefully Write […]"
taxonomy_category:
  - "Uncategorized"
---

# Chapter 01: SQL Basics & Data Retrieval – Lab Exercises

**Topic:** Basic SELECT Statements

**Instructions:**

- Read EACH exercise carefully
- Write your SQL query below EACH question
- Test your query to ensure it works
- CHECK solutions file AFTER completing all exercises

**Database:** sql_fundamentals  
 **Tables:** customers, orders, products

## SETUP: Run this first to CREATE sample data

```
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS customers CASCADE;
DROP TABLE IF EXISTS products CASCADE;

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    country VARCHAR(50),
    city VARCHAR(50),
    registration_date DATE DEFAULT CURRENT_DATE,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price NUMERIC(10,2),
    stock_quantity INT DEFAULT 0
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount NUMERIC(10,2),
    status VARCHAR(20)
);

-- INSERT sample data
INSERT INTO customers (customer_name, email, country, city, registration_date) VALUES
('John Smith', '[email protected]', 'USA', 'New York', '2025-01-15'),
('Maria Garcia', '[email protected]', 'Spain', 'Madrid', '2025-02-20'),
('Yuki Tanaka', '[email protected]', 'Japan', 'Tokyo', '2025-03-10'),
('Sarah Johnson', '[email protected]', 'USA', 'Los Angeles', '2025-04-05'),
('Ahmed Hassan', '[email protected]', 'Egypt', 'Cairo', '2025-05-12'),
('Li Wei', '[email protected]', 'China', 'Beijing', '2025-06-18'),
('Emma Brown', '[email protected]', 'UK', 'London', '2025-07-22'),
('Carlos Rodriguez', '[email protected]', 'Mexico', 'Mexico City', '2025-08-30'),
('Fatima Al-Sayed', '[email protected]', 'UAE', 'Dubai', '2025-09-14'),
('Hans Mueller', '[email protected]', 'Germany', 'Berlin', '2025-10-08');

INSERT INTO products (product_name, category, price, stock_quantity) VALUES
('Laptop Pro 15', 'Electronics', 1299.99, 45),
('Wireless Mouse', 'Electronics', 29.99, 150),
('Office Chair', 'Furniture', 249.99, 30),
('Desk Lamp', 'Furniture', 39.99, 75),
('USB-C Cable', 'Electronics', 12.99, 200),
('Monitor 27"', 'Electronics', 399.99, 25),
('Keyboard Mechanical', 'Electronics', 89.99, 60),
('Standing Desk', 'Furniture', 599.99, 15),
('Webcam HD', 'Electronics', 79.99, 40),
('Notebook Pack', 'Stationery', 15.99, 300);

INSERT INTO orders (customer_id, order_date, total_amount, status) VALUES
(1, '2025-11-01', 1329.98, 'Completed'),
(1, '2025-11-15', 89.99, 'Completed'),
(2, '2025-11-05', 399.99, 'Completed'),
(3, '2025-11-08', 249.99, 'Shipped'),
(4, '2025-11-10', 1899.97, 'Processing'),
(5, '2025-11-12', 42.98, 'Completed'),
(6, '2025-11-14', 599.99, 'Shipped'),
(7, '2025-11-16', 129.98, 'Completed'),
(8, '2025-11-18', 679.98, 'Processing'),
(1, '2025-11-20', 249.99, 'Completed');
```

## EXERCISES

### Exercise 1: SELECT All Customers

Write a query to retrieve all columns from the customers table  
 Expected output: All 10 customers with all their details

**YOUR QUERY HERE:**

### Exercise 2: SELECT Specific Columns

Retrieve only the customer_name and email from all customers  
 Expected output: 2 columns, 10 rows

**YOUR QUERY HERE:**

### Exercise 3: SELECT WITH Alias

Retrieve customer_name AS “Customer” and country AS “Location”  
 Use the AS keyword for clarity  
 Expected output: 2 columns with new names

**YOUR QUERY HERE:**

### Exercise 4: SELECT FROM Products

Retrieve all products showing product_name, category, and price  
 Expected output: 3 columns, 10 rows

**YOUR QUERY HERE:**

### Exercise 5: Simple Calculation

Retrieve product_name and calculate the total inventory value (price * stock_quantity)  
 Name the calculated column “Inventory Value”  
 Expected output: 2 columns, 10 rows

**YOUR QUERY HERE:**

### Exercise 6: Multiple Calculations

FOR EACH product, show:

- product_name
- price
- price with 20% discount (price * 0.8) AS “Sale Price”
- amount saved (price – sale_price) AS “Savings”

Expected output: 4 columns

**YOUR QUERY HERE:**

### Exercise 7: Concatenate Strings

CREATE a column that combines customer_name and country LIKE: “John Smith (USA)”  
 Name it “Customer Info”  
 Hint: Use || operator for concatenation  
 Expected output: 1 column with formatted text

**YOUR QUERY HERE:**

### Exercise 8: SELECT DISTINCT Values

Find all UNIQUE countries from the customers table  
 Use DISTINCT keyword  
 Expected output: 10 UNIQUE countries

**YOUR QUERY HERE:**

### Exercise 9: COUNT All Rows

COUNT the total number of customers  
 Name the result “Total Customers”  
 Hint: Use COUNT(*)  
 Expected output: 1 ROW, 1 column with value 10

**YOUR QUERY HERE:**

### Exercise 10: SELECT WITH Expression

Retrieve customer_name and calculate days since registration  
 Use: CURRENT_DATE – registration_date AS “Days Since Registration”  
 Expected output: 2 columns showing name and days

**YOUR QUERY HERE:**

### Exercise 11: Working WITH NULL

Retrieve all customers, showing their email  
 Also add a column “Has Email” that shows TRUE IF email EXISTS, FALSE otherwise  
 Hint: Use email IS NOT NULL  
 Expected output: 3 columns

**YOUR QUERY HERE:**

### Exercise 12: PostgreSQL RETURNING (Bonus)

INSERT a new customer and immediately RETURN their assigned customer_id  
 Customer: “Test User”, “[[email protected]](/cdn-cgi/l/email-protection)
”, “TestLand”, “TestCity”  
 Hint: Use INSERT … RETURNING  
 Expected output: The new customer_id

**YOUR QUERY HERE:**

### Exercise 13: SELECT WITH CAST

Retrieve order_id and total_amount AS an INTEGER (rounded down)  
 Name the rounded column “Amount (Rounded)”  
 Hint: Use CAST(column AS INTEGER) OR column::INTEGER  
 Expected output: 2 columns

**YOUR QUERY HERE:**

### Exercise 14: Current DATE AND Time

SELECT the current DATE, current time, and current TIMESTAMP  
 Name columns: “Today”, “Now (Time)”, “Now (FULL)”  
 Hint: Use CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP  
 Expected output: 1 ROW with 3 columns

**YOUR QUERY HERE:**

### Exercise 15: PostgreSQL Version (Bonus Challenge)

Display the PostgreSQL version you’re running  
 Hint: Use the version() FUNCTION  
 Expected output: 1 ROW with version string

**YOUR QUERY HERE:**

## CLEANUP (Run this IF you want TO remove the practice tables)

```
-- DROP TABLE IF EXISTS orders;
-- DROP TABLE IF EXISTS customers;
-- DROP TABLE IF EXISTS products;
```

## SELF-ASSESSMENT

BEFORE checking solutions, evaluate yourself:

- ✅ I can write basic SELECT queries
- ✅ I understand column aliases (AS keyword)
- ✅ I can perform calculations IN SELECT
- ✅ I understand DISTINCT FOR UNIQUE values
- ✅ I can use concatenation (||)
- ✅ I know basic aggregate functions (COUNT)
- ✅ I can use PostgreSQL-specific functions

IF you struggled WITH any exercise, review the theory section BEFORE moving TO the next chapter.

**Ready FOR solutions? CHECK: SQL Fundamentals – Chapter 1: Solutions**