Skip to main content

Setting Up Your SQL Environment

Now that you understand what databases and SQL are, it's time to get your hands dirty. In this lesson, you'll provision the tools you need and load the marketplace schema that powers every exercise in this course.

Learning Goals:

  • Install and configure a SQL database system
  • Choose and set up a SQL client/interface
  • Create the ecommerce_store database and load the shared schema
  • Run test queries against the seeded tables to confirm everything works

We'll use MySQL inside Docker so everyone gets the same environment. Create a docker-compose.yml file with the following contents:

docker-compose.yml
services:
  ecommerce-db:
    image: mysql:latest
    container_name: ecommerce-db
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: ecommerce_store
    ports:
      - "3306:3306"
    volumes:
      - mysql_ecommerce_data:/var/lib/mysql

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    container_name: phpmyadmin_gui
    restart: always
    depends_on:
      - ecommerce-db
    environment:
      PMA_HOST: ecommerce-db
      PMA_PORT: 3306
      # Direct login with root:root
      PMA_USER: root
      PMA_PASSWORD: root
    ports:
      - "8082:80"

volumes:
  mysql_ecommerce_data:

Start the containers:

docker compose up -d

Confirm everything is running:

docker compose ps

Access MySQL​

You can use either phpMyAdmin or the MySQL CLI.

Option 1: phpMyAdmin

  • Open http://localhost:8082
  • Login with user root and password root
  • Choose the ecommerce_store database from the left sidebar

Option 2: MySQL CLI

docker exec -it ecommerce-db mysql -u root -p

Enter the password: root, then select the database:

USE ecommerce_store;

Loading the Schema and Seed Data​

Save the DDL from the schema lesson into create_tables.sql, and the seed data into seed_data.sql.

Load via phpMyAdmin​

  1. Open ecommerce_store in phpMyAdmin.
  2. Go to the SQL tab.
  3. Paste the contents of create_tables.sql and run it.
  4. Paste the contents of seed_data.sql and run it.

Load via CLI​

docker exec -i ecommerce-db mysql -u root -proot ecommerce_store < create_tables.sql
docker exec -i ecommerce-db mysql -u root -proot ecommerce_store < seed_data.sql

Running Your First Query​

Now verify everything is wired up correctly by querying the seeded tables:

List categories
SELECT category_id, category_name
FROM category
ORDER BY category_id;
category_id  category_name
-----------  ----------------------
1            Electronics
2            Home & Kitchen
3            Fashion
4            Beauty & Personal Care
5            Sports & Outdoors
6            Books & Stationery

Try a join to confirm relationships are in place:

Customers and their preferred categories
SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS full_name,
       COALESCE(cat.category_name, 'No preference') AS preferred_category,
       c.signup_year
FROM customer AS c
LEFT JOIN category AS cat ON cat.category_id = c.preferred_category_id
ORDER BY c.customer_id;

You should see each customer alongside the category referenced by preferred_category_id, if any. Any future lessons that mention running a query assume this database and dataset are available.

note

The asterisk (*) in SELECT * means "all columns." While convenient for exploration, in production code you should specify exactly which columns you need for better performance and clarity.

Common Pitfalls​

  • Port conflicts: If port 3306 or 8082 is already in use, update the ports in docker-compose.yml.
  • Container not ready: MySQL can take a moment to initialize. Wait a few seconds before connecting.
  • Wrong database: Make sure you are connected to ecommerce_store before running queries.
  • Volume persistence: Data persists in mysql_ecommerce_data even after containers restart.

Summary​

You now have a working SQL environment. You've:

  • Started MySQL and phpMyAdmin with Docker Compose
  • Connected via phpMyAdmin or the MySQL CLI
  • Created the schema and loaded seed data
  • Run queries against the marketplace tables

Your environment is ready for the real SQL learning to begin in the next lesson.

Quiz​

Show quiz

  1. What command starts the MySQL containers in the background?

    • A) docker compose start
    • B) docker compose up -d
    • C) docker compose run mysql
    • D) docker compose build

  2. Which database name is created by the Docker Compose setup?

    • A) ecommerce
    • B) ecommerce_store
    • C) marketplace
    • D) store_db

  3. Where do you access phpMyAdmin in this setup?

    • A) http://localhost:3306
    • B) http://localhost:8082
    • C) http://localhost:8000
    • D) http://localhost:9000

  4. Which command loads the schema from a SQL file using the CLI?

    • A) mysql -u root -p ecommerce_store < create_tables.sql
    • B) docker exec -i ecommerce-db mysql -u root -proot ecommerce_store < create_tables.sql
    • C) docker compose exec mysql create_tables.sql
    • D) docker exec ecommerce-db mysql create_tables.sql

Answers:

  1. B - docker compose up -d
  2. B - ecommerce_store
  3. B - http://localhost:8082
  4. B - docker exec -i ecommerce-db mysql -u root -proot ecommerce_store < create_tables.sql