Basic SQL Query Structure

Now that you've set up your SQL environment and loaded the marketplace schema, it's time to write your first queries. In this lesson, we'll explore the fundamental structure of SQL SELECT statements using the category, seller, customer, product, orders, and order_item tables you just created.

Learning Goals:

• Understand the basic components of a SQL query
• Learn how to use the SELECT and FROM clauses
• Practice writing queries to retrieve specific columns
• Execute your first SQL queries in your environment

The Anatomy of a SQL Query

At its core, a SQL query tells the database what data you want to retrieve. The most basic query structure looks like this:

Basic Query Structure

SELECT column1, column2, ...
FROM table_name;

Let's break this down:

• SELECT: Specifies which columns you want to retrieve
• FROM: Specifies which table contains the data
• Semicolon: Terminates the statement (optional in some databases, but good practice)

tip

Think of SQL as asking a question: "From this table, please select these columns." The database responds with the requested data.

The SELECT Clause

The SELECT clause determines which columns appear in your results. You can select specific columns, all columns, or even create calculated columns.

Selecting Specific Columns

Selecting Specific Columns

SELECT first_name, last_name, email
FROM customer;

This query returns only the first_name, last_name, and email columns from the customer table.

Selecting All Columns

Selecting All Columns

SELECT *
FROM customer;

The asterisk (*) is a wildcard that means "all columns." While convenient for exploration, it's better practice to specify only the columns you need in production code.

Creating Calculated Columns

Calculated Column Example

SELECT  customer_id,
        first_name,
        last_name,
        CONCAT(first_name, ' ', last_name) AS full_name
FROM customer;

Here we calculate a customer's full name.

The FROM Clause

The FROM clause specifies which table to query. Tables are the fundamental storage structures in databases where your data lives.

Querying Different Tables

-- From the seller table
SELECT store_name, first_name, last_name, email
FROM seller;

-- From the product table
SELECT sku, product_name, price, stock_qty
FROM product;

-- From the category table
SELECT category_id, category_name
FROM category;

warning

Table and column names are case-sensitive in some database systems (like PostgreSQL) and case-insensitive in others (like MySQL). It's best practice to be consistent with your naming conventions.

Putting It All Together

Let's work through complete examples using the marketplace schema:

Complete Query Examples

-- Review the product catalog
SELECT sku, product_name, price
FROM product
ORDER BY sku;

-- List sellers and the categories they currently list products in
SELECT DISTINCT s.seller_id,
       s.store_name,
       CONCAT(s.first_name, ' ', s.last_name) AS seller_contact,
       c.category_name
FROM seller AS s
JOIN product AS p ON p.seller_id = s.seller_id
JOIN category AS c ON c.category_id = p.category_id
ORDER BY s.store_name, c.category_name;

-- See which customers ordered which products recently
SELECT o.order_id,
       CONCAT(cust.first_name, ' ', cust.last_name) AS customer_name,
       p.sku,
       p.product_name,
       o.order_date,
       o.status
FROM orders AS o
JOIN customer AS cust ON cust.customer_id = o.customer_id
JOIN order_item AS oi ON oi.order_id = o.order_id
JOIN product AS p ON p.product_id = oi.product_id
ORDER BY o.order_date DESC, o.order_id;

Working with Multiple Tables

While we'll cover JOINs in detail later, it's helpful to see how you might query different tables in the same session:

Multiple Tables

-- Switch between different tables in the schema
SELECT category_id, category_name FROM category;
SELECT sku, price FROM product;
SELECT first_name, last_name, signup_year FROM customer;

Single Table Deep Dive

-- Explore one table thoroughly
SELECT order_item_id,
       order_id,
       product_id,
       quantity,
       unit_price
FROM order_item
ORDER BY order_id, order_item_id;

Common Pitfalls

• Missing commas: Forgetting commas between column names in SELECT lists
• Table name typos: Mixing up singular table names like product or customer
• Case sensitivity: Assuming case doesn't matter when it does in your database
• Missing semicolons: Forgetting to terminate statements (can cause issues with multiple queries)
• Selecting too much data: Using SELECT * when you only need specific columns

Summary

You've now learned the fundamental structure of SQL queries. Remember:

• Use SELECT to specify which columns to retrieve
• Use FROM to specify which table to query
• Be specific about the columns you need rather than using SELECT *
• Always test your queries with small datasets first
• Pay attention to your database's case sensitivity rules

Practice writing simple queries against category, customer, and product in your environment to become comfortable with the basic syntax before moving on to filtering and sorting.

Quiz

Show quiz

1. Which clause specifies what columns to return in a SQL query?
2. What does the * symbol represent in a SELECT statement?
3. In the query SELECT sku, price FROM product; which part indicates the table being queried?
4. Why is it generally better to specify column names instead of using SELECT *?
5. What is the purpose of the semicolon at the end of a SQL statement?

---

Answers:

1. The SELECT clause
2. It's a wildcard that means "all columns"
3. FROM product specifies the table
4. Better performance, clearer intent, and safety against schema changes
5. It terminates the SQL statement (required in some databases, good practice in all)