Creating SQL Solutions

Understanding the Problem

We need to write SQL queries to manage a hardware store database that includes tools, departments, customers, and their purchases. The tests require us to:

• Create appropriate database tables
• Query tools based on names and prices
• Find customer information based on purchases
• Delete customers and departments with proper cascading

Devising a Plan

1. Create table schema with proper relationships
2. Write SELECT queries for tool searches
3. Develop customer lookup query
4. Implement deletion queries with cascade effects

Implementing the Solution

Creating Tables (03-find-quantity-purchased-by-tool-name.sql)

-- First create the departments table as it will be referenced by tools
CREATE TABLE departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL
);

-- Create tools table with department foreign key
CREATE TABLE tools (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Create customers table
CREATE TABLE customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone_number BIGINT
);

-- Create purchases table to track tool purchases
CREATE TABLE purchases (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tool_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    FOREIGN KEY (tool_id) REFERENCES tools(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);
    

Inserting Data

    -- Insert customers
    INSERT INTO customers (first_name, last_name, phone) VALUES
    ('John', 'Smith', '111-111-1111'),
    ('Jane', 'Doe', '222-222-2222');
    
    -- Insert tools
    INSERT INTO tools (name, price, department) VALUES
    ('Snow shovel', 16.50, 'Home & Garden'),
    ('Work light', 29.99, 'Electrical'),
    ('Wheelbarrow', 89.99, 'Home & Garden'),
    ('Pipe Wrench', 18.99, 'Plumbing'),
    ('Pipe Cutter', 36.36, 'Plumbing'),
    ('Rake', 15.45, 'Home & Garden'),
    ('Women\'s Gloves', 8.39, 'Home & Garden'),
    ('Men\'s Gloves', 8.39, 'Home & Garden');
    

Finding Tools by Name (03-find-quantity-purchased-by-tool-name.sql)

SELECT t.name, t.price, p.quantity
FROM tools t
JOIN purchases p ON p.tool_id = t.id
WHERE t.name LIKE 'Pipe%'
ORDER BY t.name, p.quantity;
    

This query satisfies the test by:

• Selecting tools starting with 'Pipe'
• Including the price and purchase quantity
• Ordering first by tool name, then by quantity

Finding Tools by Price Range (04-find-tools-by-price.sql)

SELECT name, price
FROM tools
WHERE price BETWEEN 14.67 AND 20.09
ORDER BY name;
    

This query matches the test requirements by:

• Finding tools within the specified price range
• Returning just name and price
• Ordering alphabetically by name

Finding Customer by Tool Purchase (05-find-a-customer-by-tool-purchased.sql)

SELECT c.first_name, c.last_name, c.phone_number
FROM customers c
JOIN purchases p ON p.customer_id = c.id
JOIN tools t ON t.id = p.tool_id
WHERE t.name = 'Pipe Cutter'
ORDER BY p.id DESC
LIMIT 1;
    

This query finds the last customer who bought a Pipe Cutter by:

• Joining customers with their purchases and tools
• Filtering for the specific tool
• Using ORDER BY and LIMIT to get the most recent purchase

Deleting Customer (06-delete-customer-by-name.sql)

DELETE FROM customers
WHERE first_name = 'John'
AND last_name = 'Smith';
    

The cascade delete works because:

• The purchases table has ON DELETE CASCADE for customer_id
• When a customer is deleted, all their purchases are automatically removed
• Tool and department records remain unchanged

Finding All Customers (06-find-all-customers.sql)

SELECT first_name, last_name
FROM customers
ORDER BY last_name, first_name;
    

Finding All Tools Purchased (06-find-all-tools.sql)

SELECT t.name, p.quantity
FROM tools t
JOIN purchases p ON p.tool_id = t.id
ORDER BY t.name, p.quantity;
    

Deleting Department (07-delete-department-by-name.sql)

DELETE FROM departments
WHERE name = 'Plumbing';
    

Finding All Departments (07-find-all-departments.sql)

SELECT name
FROM departments
ORDER BY name;
    

Finding All Tools (07-find-all-tools.sql)

SELECT name
FROM tools
ORDER BY name;
    

Testing the Solution

To verify these solutions:

1. Run the table creation scripts first
2. Insert test data
3. Run each query and compare with test expectations
4. Test delete operations and verify cascade effects

Real World Application

These queries represent common operations in retail systems:

• Finding products within price ranges (like filtering on e-commerce sites)
• Looking up customer purchase history (like in point-of-sale systems)
• Managing inventory across departments (like in warehouse management)
• Handling customer account deletion (like GDPR compliance)

Common Pitfalls to Avoid

• Forgetting to use proper JOIN syntax
• Incorrect ORDER BY clauses
• Missing CASCADE on foreign keys
• Not handling case sensitivity in WHERE clauses

Additional Practice

To reinforce your understanding, try modifying these queries to:

• Find tools in different price ranges
• Search for different tool name patterns
• Add additional sorting criteria
• Implement soft deletes instead of hard deletes