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]”, “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

Scroll to Top