SQL Fundamentals: Querying Data

The Anatomy of a Query

In this lesson, we move from creating data to querying it using Structured Query Language (SQL). Think of a query as a request to a filing cabinet: you specify which folders (tables) you want and exactly which pieces of paper (columns) you need to see.

• SELECT: Which columns to show.
• FROM: Which table to look in.

Welcome to the world of data retrieval, where we learn to extract meaningful information from our databases. Imagine our database is a massive filing cabinet. To get data, we use the SELECT statement to pick our columns and the FROM statement to pick our table. It's like asking for specific folders from a specific drawer.

• SELECT defines the specific columns to retrieve.
• FROM specifies the source table.
• SQL acts as a structured request for data.

The Power of Querying

Why Querying Matters

In previous lessons, we structured our data into tables like filing cabinets. But a database is only useful if we can pull out specific information.

Querying is the process of asking the database questions to turn raw data into meaningful insights—like finding low-stock products or identifying top spenders.

Welcome to SQL Querying. Think of your database as a massive filing cabinet. Without a way to search it, it's just a pile of papers. Querying is the process of asking specific questions to find exactly what you need to make business decisions.

• Querying turns raw data into insights.
• Databases act as organized storage (filing cabinets).
• SQL is the language used to ask these questions.

The Foundation: SELECT and FROM

SELECT and FROM

Every SQL query begins with two fundamental pieces:

• SELECT: Which columns do you want?
• FROM: Which table are they in?

Let's look at our Customers table. If we only want names and emails, we use the SELECT keyword followed by the column names. Then, we use FROM to tell the database to look in the Customers table.

• SELECT identifies columns.
• FROM identifies the table.
• Syntax follows a specific order.

Filtering with WHERE

Often, you don't want every single row in a table. The WHERE clause acts as a filter to narrow down results based on specific criteria, such as price, date, or name.

What if you only want specific rows? Here is our full list of orders. By adding a WHERE clause, we can filter this list. Notice how only the orders with a total amount over one hundred dollars remain. This is how we find exactly what we need.

• WHERE filters rows based on logical conditions.
• It prevents data overload by returning only relevant records.

Reconnecting Data with INNER JOIN

Because of normalization, data is often split into multiple tables. To get a complete picture, we use an INNER JOIN to reconnect them using a shared key.

In our e-commerce store, customer names are in one table and their orders are in another. To find out who placed which order, we use an INNER JOIN. We link them using the Customer ID, which is the common thread between them. An INNER JOIN only shows us rows where a match exists in both tables.

• INNER JOIN returns rows only when there is a match in both tables.
• Normalization splits data; JOINs bring it back together.
• The 'ON' clause defines the link between tables.

Interactive Query Builder

Build a query to retrieve the ProductName and Price from the Products table.

It's your turn. Try to build a query by clicking the correct components to see how the code and the resulting data change. Great job! You've selected the right columns and table. Notice how the preview table now only shows the product details you requested.

• Identify correct columns.
• Select the correct source table.

Case Study: High-Value Customers

Let's find the names of customers who placed orders over $150. Drag the SQL components into the correct order to build the query.

Time for a challenge. We need to send thank-you notes to our high-value customers. Drag the SQL blocks into the workspace to build a query that selects customer names and order amounts for orders over one hundred and fifty dollars. Excellent! You've correctly identified the source tables, established the link with the ON clause, filtered the high-value rows, and selected the display columns.

• Combining SELECT, JOIN, and WHERE in one query.
• Identifying Primary and Foreign Keys.

Filtering with WHERE

The WHERE Clause

Without a filter, a query returns every single row. The WHERE clause sets conditions to narrow down results.

SELECT OrderID, TotalAmount 
FROM Orders 
WHERE TotalAmount > 100;

Imagine an e-commerce store with millions of orders. Returning all of them would be overwhelming. The WHERE clause acts like a funnel. By setting a condition, like 'TotalAmount greater than 100', we only see the high-value orders we care about.

• WHERE limits the number of rows returned.
• Conditions can use operators like >, <, or =.
• Crucial for performance in large databases.

Connecting Tables: INNER JOIN

The Bridge Between Tables

Because of normalization, related data is often split. To see who placed which order, we use an INNER JOIN.

This 'bridge' is built on a shared column, usually a Foreign Key (like CustomerID) that exists in both tables.

We have names in the Customers table and purchases in the Orders table. Notice they both share a 'CustomerID' column. The INNER JOIN acts as a bridge, matching rows where these IDs are identical to give us a complete picture.

• JOINs connect normalized tables.
• INNER JOIN returns rows with matching values in both tables.
• Uses a shared key (Primary/Foreign) to link data.

Avoiding Common Pitfalls

Querying can be tricky. Avoid these three common mistakes to keep your database running smoothly:

• Overusing SELECT *: It's inefficient.
• Missing JOIN Conditions: It creates a 'Cartesian Product' mess.
• Ambiguous Columns: Always specify which table a column belongs to.

Even pros make mistakes. First, avoid using SELECT star; it's like grabbing the whole cabinet when you only need one folder. Second, never forget your ON clause, or the database will try to match every row with every other row. Finally, if two tables share a column name, always specify which one you mean, like Customers dot ID.

• Specify columns instead of using '*' for better performance.
• Always include an ON clause in a JOIN.
• Use Table.Column syntax for clarity.

The Case Study: Joining Tables

Querying Order History

To see customer names alongside their order dates, we use the Table.Column syntax to avoid confusion.

SELECT Customers.FirstName, 
       Orders.OrderDate
FROM Customers
INNER JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID;

Here is the full query for our e-commerce store. We use 'Customers dot FirstName' to tell the database exactly which table to look in. The 'ON' clause is the most important part—it defines the rules for our bridge.

• Use Table.Column syntax for clarity.
• The ON clause specifies the link.
• INNER JOIN is the most common join type.

Query Writing Challenge

Write a query to find the FirstName of customers who placed an order with a TotalAmount greater than 200.

Time for a real challenge. You need to combine what you've learned about JOINs and WHERE clauses. Write your query in the box, and I'll give you feedback.

• Combine JOIN and WHERE logic.
• Correct use of table aliases or names.

Workflow and Common Pitfalls

How to Query Like a Pro

1. Identify the Goal
2. Locate the Data (Which tables?)
3. Check Relationships (Shared IDs?)
4. Write the Query

Watch out for SELECT * (it's slow) and forgetting the ON clause (it creates messy data).

Before you start typing, always follow this workflow. And be careful! Forgetting a single 'ON' clause or misspelling a column name can lead to incorrect data or a complete crash.

• Always follow a logical workflow.
• Avoid SELECT * in production.
• Never forget the ON clause in a JOIN.
• SQL is strict about typos.

Code Review: The Broken Query

A junior developer wrote the query below, but it's causing an error. Diagnose the problem and explain how to fix it.

Look at this query. It's trying to join Customers and Orders, but something is missing. Type your diagnosis in the box and tell me what's wrong.

• Identifying missing JOIN conditions.
• Applying best practices.