Tables

1. Tables

Now that we've seen SQL queries and prompts in action, let's look at how SQL identifies which table we're looking for.

2. The FROM clause

The FROM clause runs first in any SQL query and tells the server what table we want to explore. To query a different table, we specify it in our prompt to the AI assistant. This is because databases often contain multiple tables. We've explored the products table in our database, let's see what other tables we have.

3. The data

The PoweredHomes database we'll use throughout the course is modeled after an online store.

4. The data

It includes three tables: customers, products, and orders. These tables work together and are linked

5. Relational database

by shared information, such as the customer_id that appears in both the customers and orders tables. This interconnected setup is called a relational database.

6. Relational database

Understanding the structure of our database allows us to write better prompts and more easily verify that the AI's SQL targets the right data.

7. Tables

Each table is structured like a spreadsheet with rows and columns.

8. Records

In SQL terms, rows are called records, and each one holds details for an individual item, like a specific product or customer.

9. Fields

Columns are often called fields, and they represent specific parts of the data, like name, price, or category.

10. Querying the orders table

Let's try to look at the orders table. We can prompt: "What are the order IDs in the orders table?" The AI assistant will interpret this request and update the SQL code to include FROM orders, directing our query to the correct table. Another suitable prompt could be: "Show me the order ids from the orders table". Notice how different prompts can lead to the same result, whether we phrase something as a question or a statement. While this gives us flexibility in how we request information, it's important to build the habit of verifying that the SQL output matches our expectations.

11. Relational database advantages

This verification is especially important when working with relational databases. The structure of a relational database is what makes complex analysis possible. When data is organized across multiple connected tables, it stays clean and avoids duplication.

12. Relational database advantages

If everything lived in one massive table, we'd see repeated customer information for every order they placed,

13. Relational database advantages

or we'd have gaps and empty spaces for customers who haven't ordered yet.

14. Relational database advantages

By splitting related information across connected tables,

15. Relational database advantages

we create a more efficient, organized system that's easier to maintain and query. Understanding these table relationships helps us guide the AI assistant and verify it's targeting the right data. This is the foundation that makes more complex analysis possible, helping us answer questions like "what products did customer 2 order?".

16. Let's practice!

For now, it's time for some more practice.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.