Schema and data types

1. Schema and data types

To really understand the data, we need to read the database schema.

2. Schema

It describes each table, the relationships between them, and the data type assigned to every field.

3. Schema

The schema may not be visible to an AI assistant, so it's important we review it before writing prompts. We can usually find the schema in the database documentation. We can typically check any notes provided with the database to find this information. In this course, we've given the AI assistant the relevant schema information. In real-world settings, be mindful of sharing schema details, especially when data includes sensitive or private information. Always get permission before doing so.

4. Schema

In our schema, we can see the three tables we've been querying, along with their relationships. For example, the orders table

5. Schema

is linked to both the customers

6. Schema

and the products table.

7. Schema

All fields are also listed

8. Schema

along with their data types.

9. Introduction to data types

Each field is assigned a data type when the table is created. The data type depends on the kind of information stored and will determine the operations we can perform. While AI assistants can help generate queries, they often won't know each field's data type, meaning they may suggest incorrect operations.

10. Field operations

For example, it makes sense to multiply two numbers, like price, but not words like product names. An AI assistant may still generate a faulty query, but by reviewing the schema, we can now catch these errors.

11. Integer

SQL has several data types. We'll cover three common ones, starting with integer.

12. Integer

Integer, or `INT`, stores whole numbers,

13. Integer

like the id field in our products table. It can hold values from approximately negative to positive two billion.

14. String

Text fields like product_name contain values like "Laptop". This kind of text is known as a string, a sequence of letters, numbers, or symbols.

15. String

Fields storing strings typically use the `VARCHAR` data type. This stands for variable-length character string,

16. String

meaning it can hold long or short text values.

17. Decimals

Finally, we'll look at decimal values. In our database, the unit_price field stores numbers with decimal values, such as 649.99.

18. Decimals

Here, the `NUMERIC` data type is used. SQL has several decimal types, each offering different levels of precision.

19. Decimals

Since we don't require precision beyond a few decimals, `NUMERIC` is sufficient.

20. Understanding data types with AI

Understanding data types helps us interpret and verify results. It transforms us from someone who accepts AI output to someone who guides it toward better answers. As we've learned, AI assistants rarely see the schema information unless it's provided, and they may guess incorrectly. Sometimes, a field name like "price" hints at a numeric type, but it's often best to specify the fields in our prompts.

21. Let's practice!

Let's 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.