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

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:

2. 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:

2. 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):

2. 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;

---