Comprehensive Tutorial: Completing the DAD-220 Module 2 Lab (Step-by-Step for Beginners)

Posted on

Welcome to this detailed, beginner-friendly tutorial. We’ll cover each step in-depth, ensuring you understand exactly what each command does and how to use it effectively.

Step 1: Prepare Your Database Environment

  1. Open MySQL Workbench (or your preferred MySQL tool).
  2. Connect to your MySQL server:
  • Click on your database connection or create a new connection if needed.
  • Confirm successful connection (you will see a status indicator).

Step 2: Create Your Database

  1. Open a new SQL tab and type the following command:
CREATE DATABASE sales_db;
  • CREATE DATABASE: Command to create a new database.
  • sales_db: The name chosen for your database.
  1. Run the command (press the lightning bolt icon or Ctrl+Enter).
  2. Verify the database creation by typing:
SHOW DATABASES;
  • Lists all databases. Ensure you see sales_db in the results.
  1. Select your new database to work with:
USE sales_db;
  • Now any tables or data you create will be stored here.

Step 3: Creating the Tables

Creating the customers Table:

  1. Type and execute the following:
CREATE TABLE customers (
  customer_id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100)
);
  • INT: Holds numeric integer values.
  • PRIMARY KEY: Ensures each customer has a unique ID.
  • AUTO_INCREMENT: Automatically assigns a new ID number.
  • VARCHAR(n): Stores text (letters and numbers), up to n characters.

Creating the orders Table:

  1. Similarly, execute:
CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT,
  order_date DATE,
  amount DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
  • DATE: Stores dates (YYYY-MM-DD format).
  • DECIMAL(10,2): Holds precise numeric values, suitable for money (up to 10 digits total, 2 decimal places).
  • FOREIGN KEY: Links orders table to customers, ensuring each order references a valid customer ID.
  1. Confirm tables exist:
SHOW TABLES;
  • You should see both customers and orders listed.

Step 4: Adding Data to Tables

Insert data into customers:

  1. Enter the following SQL:
INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com');
  • INSERT INTO: Adds new data to a table.
  • VALUES: Specifies the actual data to insert.

Insert data into orders:

  1. Next, enter:
INSERT INTO orders (customer_id, order_date, amount) VALUES
(1, '2023-01-15', 250.50),
(2, '2023-01-20', 125.75);
  • Ensure customer_id matches entries in the customers table.
  1. Verify data entry:
SELECT * FROM customers;
SELECT * FROM orders;
  • You should see data rows you just inserted.

Step 5: Running and Understanding Queries

Basic Selection:

  1. Find all orders greater than $150:
SELECT * FROM orders WHERE amount > 150;
  • SELECT: Retrieves data.
  • WHERE: Filters data based on conditions.

Combining Tables (Join):

  1. Display each customer’s name alongside their orders:
SELECT customers.first_name, customers.last_name, orders.order_date, orders.amount
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;
  • JOIN: Combines two tables based on related fields.

Step 6: Documentation

  • Clearly record each query, what it achieves, and the result.
  • Use comments (lines starting with --) to explain your SQL code:
-- This query selects all customers
SELECT * FROM customers;

Leave a Reply