6-1 Project One: Creating A Database And Querying Data: Exact Answer & Steps

Ever walked into a class where the instructor says, “Today we’re building a database from scratch,” and you stare at the screen wondering whether you’re about to code a spaceship or just a spreadsheet?
Turns out, the 6‑1 Project One assignment is exactly that: a hands‑on dive into creating a tiny relational database and then pulling data out of it with queries. It sounds simple, but the short version is that mastering these steps gives you a foundation you’ll keep using whether you’re building a blog, a retail site, or a data‑science pipeline.

Let’s skip the fluff and get into the nitty‑gritty of what the project expects, why it matters, and—most importantly—how to actually pull it off without pulling your hair out The details matter here. Less friction, more output..

What Is 6‑1 Project One: Creating a Database and Querying Data

At its core, the 6‑1 Project One is a classroom‑style exercise that asks you to:

1. Design a small relational schema – decide what tables you need, what columns each table will have, and how they relate.
2. Create the database – write the SQL CREATE TABLE statements, set primary keys, and add any foreign‑key constraints.
3. Populate it with sample data – insert a handful of rows so you have something to query.
4. Write SELECT statements – answer a set of questions (often “list all customers who bought more than $500”) using basic and intermediate SQL.

Think of it as building a miniature version of the kind of data store you’ll see in real‑world apps. The “6‑1” part just means it’s the first project in the sixth unit of most introductory database courses Small thing, real impact..

The typical tech stack

Most instructors let you pick your favorite RDBMS—MySQL, PostgreSQL, SQLite, even Microsoft SQL Server. The SQL syntax is nearly identical across them, so you can follow the same steps no matter which engine you spin up locally or in the cloud.

The deliverables

• A SQL script that creates the schema and inserts data.
• A set of query files (or a single file with comments) that answer the project questions.
• Sometimes a short write‑up explaining design choices.

That’s it. Simple on paper, but the devil is in the details Not complicated — just consistent..

Why It Matters / Why People Care

You might wonder, “Why do we need to hand‑code a tiny database for a class?” Because the skill set it builds is transferable across literally every software job. Here’s what you gain:

• Understanding of relational thinking – You learn to break a problem into entities (tables) and relationships (foreign keys). That mental model sticks when you design APIs or data pipelines later.
• SQL fluency – Writing SELECT, JOIN, GROUP BY, and subqueries becomes second nature. Those commands are the lingua franca of data analysis.
• Debugging practice – When a query doesn’t return what you expect, you learn to trace the issue back to schema design, missing indexes, or a typo in a WHERE clause.
• Portfolio material – A clean, well‑documented SQL script looks great on a GitHub repo when you’re job hunting.

In practice, the ability to spin up a small database and query it reliably is worth its weight in gold for startups, data‑driven marketers, and anyone who needs to turn raw numbers into insight.

How It Works (or How to Do It)

Below is a step‑by‑step walkthrough that covers everything you need to finish the project from scratch. Feel free to adapt the example to your own domain—whether it’s a library, a coffee shop, or a gaming leaderboard Small thing, real impact..

1. Sketch the schema on paper

Before you type a single line of SQL, draw a quick ER diagram (entity‑relationship). Identify:

• Entities – the nouns (e.g., Customer, Order, Product).
• Attributes – the columns each entity needs (e.g., customer_id, email).
• Relationships – how entities link (one‑to‑many, many‑to‑many).

For a classic “sales” example, you might end up with three tables:

2. Write the CREATE statements

Open your favorite SQL client (MySQL Workbench, pgAdmin, DB Browser for SQLite) and start a new script Practical, not theoretical..

-- customers table
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY AUTO_INCREMENT,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(100) UNIQUE NOT NULL
);

-- products table
CREATE TABLE products (
    product_id    INT PRIMARY KEY AUTO_INCREMENT,
    name          VARCHAR(100) NOT NULL,
    price         DECIMAL(10,2) NOT NULL
);

-- orders table
CREATE TABLE orders (
    order_id      INT PRIMARY KEY AUTO_INCREMENT,
    customer_id   INT NOT NULL,
    order_date    DATE NOT NULL,
    total_amount  DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- order_items table (junction)
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id      INT NOT NULL,
    product_id    INT NOT NULL,
    quantity      INT NOT NULL,
    unit_price    DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

A couple of things to note:

• AUTO_INCREMENT (or SERIAL in PostgreSQL) gives you a unique ID without manual input.
• UNIQUE on email prevents duplicate customers.
• FOREIGN KEY constraints keep the data consistent—no order can point to a non‑existent customer.

3. Seed the database with sample data

Insert a handful of rows for each table. You don’t need hundreds; ten customers, fifteen products, and a few orders are enough to showcase joins It's one of those things that adds up..

INSERT INTO customers (first_name, last_name, email) VALUES
('Alice', 'Smith', 'alice@example.com'),
('Bob',   'Jones', 'bob@example.com'),
('Cara',  'Lee',   'cara@example.com');

INSERT INTO products (name, price) VALUES
('Coffee Mug', 12.In real terms, 99),
('T‑Shirt',    19. 99),
('Notebook',    5.

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2024-03-01', 38.So 97),
(2, '2024-03-02', 12. 99),
(1, '2024-03-05', 25.

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 2, 12.And 99),   -- Alice bought 2 mugs
(1, 3, 2, 5. 49),    -- Alice bought 2 notebooks
(2, 1, 1, 12.99),   -- Bob bought 1 mug
(3, 2, 1, 19.

Run the script. If you get errors, double‑check that foreign‑key IDs actually exist—this is where the “what most people miss” lesson comes in.

### 4. Write the required queries  

The assignment typically asks for a handful of SELECT statements. Below are common examples and the thought process behind each.

#### a. List all customers with their total spend  

```sql
SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS full_name,
       SUM(o.total_amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, full_name
ORDER BY total_spent DESC;

Why LEFT JOIN? Because you want customers who haven’t placed an order to still appear (their spend shows as NULL → 0 after COALESCE if you prefer).

b. Find products that have never been ordered

SELECT p.product_id, p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id
);

Using NOT EXISTS is more efficient than a LEFT JOIN … WHERE oi.product_id IS NULL on large tables And that's really what it comes down to. Nothing fancy..

c. Show the top 3 orders by total amount

SELECT order_id, customer_id, total_amount, order_date
FROM orders
ORDER BY total_amount DESC
LIMIT 3;

Simple, but remember LIMIT works in MySQL and PostgreSQL; in SQL Server you’d use TOP 3.

d. Retrieve each order with a line‑item breakdown

SELECT o.order_id,
       c.first_name,
       c.last_name,
       p.name AS product,
       oi.quantity,
       oi.unit_price,
       (oi.quantity * oi.unit_price) AS line_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
ORDER BY o.order_id, p.name;

Notice the multiplication inside the SELECT—no need for a separate calculation later Worth keeping that in mind..

e. Calculate average order value per customer

SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS full_name,
       AVG(o.total_amount) AS avg_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, full_name;

If you want to include customers with no orders, swap JOIN for LEFT JOIN and wrap AVG with COALESCE.

5. Test and verify

Run each query and compare the output to what you expect based on the seed data. If something looks off, trace it:

1. Check data – SELECT * FROM order_items WHERE order_id = 1;
2. Validate joins – temporarily select only the join columns.
3. Look for NULLs – they often hide mismatched foreign keys.

Once every query returns the right result, you’re ready to submit.

Common Mistakes / What Most People Get Wrong

1. Skipping primary keys – Without a unique identifier, joins become ambiguous and updates turn into nightmares.
2. Hard‑coding IDs in INSERTs – If you rely on AUTO_INCREMENT but then manually insert customer_id = 1 later, you’ll hit duplicate‑key errors. Let the DB assign IDs and capture them with LAST_INSERT_ID() if you need them for subsequent inserts.
3. Using VARCHAR for numeric data – Storing prices as text prevents proper arithmetic and sorting. Always use DECIMAL (or NUMERIC) for money.
4. Forgetting foreign‑key constraints – It’s tempting to skip them for speed, but then you can end up with orphaned rows that break your queries.
5. Over‑using SELECT * – It works, but it hides which columns you actually need and can cause performance hits on larger tables.
6. Misunderstanding GROUP BY – Some DBMS (like MySQL in default mode) let you omit non‑aggregated columns, leading to nondeterministic results. Stick to the strict SQL standard: every column in the SELECT that isn’t aggregated must appear in the GROUP BY.

Avoiding these pitfalls not only gets you a higher grade but also builds habits you’ll thank yourself for later Small thing, real impact..

Practical Tips / What Actually Works

• Name conventions matter – Use snake_case or camelCase consistently. My personal favorite is snake_case for tables and columns; it reads cleanly in SQL.
• Add comments – A quick -- This table stores … right before a CREATE TABLE line saves future readers (including future‑you) a lot of head‑scratching.
• Use a version‑control repository – Even for a class project, push your .sql files to GitHub. It shows you can manage code and makes rollback painless.
• Validate with a small script – Write a tiny Python or Bash script that runs each query and checks the row count. Automation catches typos early.
• apply built‑in functions – DATE_FORMAT, EXTRACT(YEAR FROM ...), and STRING_AGG (or GROUP_CONCAT) can replace messy manual string handling.
• Keep it repeatable – Wrap your whole script in a transaction and ROLLBACK at the end when testing. That way you can re‑run the script without manually dropping tables each time.

FAQ

Q: Do I have to use MySQL, or can I pick PostgreSQL?
A: Most instructors accept any major RDBMS. The only thing that changes is syntax for auto‑increment (SERIAL vs AUTO_INCREMENT) and maybe the LIMIT clause (TOP in SQL Server). Stick to the one you’re most comfortable with No workaround needed..

Q: How many rows should I insert for the sample data?
A: Enough to demonstrate joins and aggregates—typically 5‑10 rows per table. If you go overboard, the script becomes harder to read and the grading rubric might penalize unnecessary complexity That's the whole idea..

Q: What if my query returns duplicate rows?
A: Check your joins. A many‑to‑many relationship without a proper junction table (or without distinct selection) will duplicate rows. Adding DISTINCT can mask the problem, but fixing the join logic is the real solution The details matter here..

Q: Should I create indexes on foreign‑key columns?
A: For a tiny project it’s optional, but adding an index (CREATE INDEX idx_orders_customer ON orders(customer_id);) demonstrates good practice and can speed up joins, especially if the dataset grows.

Q: How do I handle dates in SQL?
A: Store them as DATE (or DATETIME if you need time). Use the ISO format 'YYYY‑MM‑DD' when inserting. Functions like YEAR(order_date) let you filter by year without string gymnastics Surprisingly effective..

Wrapping it up

Building a database from scratch and pulling data with queries might feel like a lot of moving parts, but once you break it down—design, create, seed, query—you’ll see the process is repeatable and surprisingly logical. The 6‑1 Project One isn’t just a grade; it’s a micro‑bootcamp in relational thinking that will pay dividends every time you need to turn raw numbers into actionable insight And that's really what it comes down to..

So fire up your SQL client, sketch that schema, and start typing. The moment you see a table fill with rows and a query spit out exactly the answer you imagined—that’s the sweet spot where theory meets practice. Happy coding!