SQL for AI 101

Acknowledgement: This post is based mainly on Dr. Vinh Dinh Nguyen’s presentation and slides and partly on on TA, STA, and peers’ responses from the course AIO2025 offered by AI VIETNAM.

Relevance: The audience of this lecture were those who might know nothing about database or have had some experience working with data. It is recommended that complete newbies should spend time reading and practicing the basics without the assistance of AI over and over again to familiarize themselves with the materials. Note that only necessary SQL knowledge is introduced here.

Data, specifically precise and standardized data, plays a critical role in AI. Without data, AI is powerless.
-Dr. Vinh Dinh Nguyen

In today’s post, we’ll explore databases, SQL, and MySQL. If you want to use Python (without SQL) to perform data processing, check out PySpark.

Introduction to Database

Microsoft Excel, Google Sheets, or Airtable are tools used to arrange data and perform basic calculations mainly for individual purposes. But they have certain drawbacks when it comes to managing enormous amounts of data.

Take an example of managing a shopping store. On a spreadsheet, if one customer places an order with two different products, there need be at least two rows to store them for clarity. This means the customer’s relevant details are unnecessarily duplicated. When their information changes, updating it for all rows can be an extremely inconvenient and error-prone.

Is there a quick and precise structure allowing efficient data storage and manipulation?

It is a database. From my viewpoint, the main strategy a database provides to interact with data is to divide, relate, and conquer.

If the above example is approached using a database, the shopping system can be divided into several tables, for instance,

• customers’ information
• products’ information
• orders’ information.

The separation allows tables to contain unique details, for example, one customer cannot appear in two separate rows in the “Customers” table. Updating content barely takes any work. Moreover, since each table has their own purpose, modifying one’s content should not have impact on another’s.

Look at this database closer. The last table is created as a connection between the first two. Each of the first two tables has its own unique index column named primary key. The “Order” table doesn’t have its own primary key instead has a foreign key which is made up of the others’ primary keys. This is called a relational database, that is, tables in the database have relations with each other.

This answer helps solving some existing issues. But, just like many problems you face at school, at school, or in life, there are always more than one solutions. Here an “Order Item” table can be added so that each order has its own primary index rather than just foreign ones. The more complicated a problem is, the more tables there may be.

Database Management System (DBMS)

Formally, a database is an organized collection of data stored electronically so that information can be easily accessed, managed, and retrieved. Some common examples that utilize databases are

• friend and post lists on social media,
• patient data at hospitals,
• video suggestions on Youtube, or
• product arrangement at stores.

To be specific, Youtube collects videos you have watched to a database and uses its algorithm to show you suggestions based on your preferences. Or stores analyze customers’ shopping behaviors to arrange and rearrange their products so they can increase their revenues. These procedures are called Data Mining.

We only focus on relational database here. We’ll talk more about nonrelational databases in NoSQL for AI.

In order for users to interact with databases easily and efficiently, softwares called Database Management Systems (DBMS) were invented. Some examples for relational DBMS are MySQL, Microsoft SQL Server, and Oracle Database. Using these applications, we communicate with databases by Structured Query Language (SQL).

MySQL works for smaller databases, so that’s what we use here, but you may use any suitable application. In addition, it’s recommended we install and use MySQL Workbench as a user interface to work with MySQL.

How to Create a Database

When storing data in MySQL, we generally follow the following process:

1. Design the database schema
2. Create tables
3. Insert data
4. Retrieve data
5. Update or Delete Data

The steps in this process can be refered to CRUD which consists of our basic operations in data management: create, read, update, and delete.

Database Schema

A database schema is a comprehensive blueprint of the database layout, representing how data is organized and they are related as groups. You may link it with the idea of class in Python (discussed in Python for AI 105). Since it is like a template, there is no data attached. The schema helps you know the solution as a whole. We’ll learn more about how to draw a schema in the next post SQL for AI 102.

Note that SQL is a case-insensitive language, but it’s conventional to capitalize keywords. The syntax to create a database schema is

CREATE DATABASE DatabaseName;
-- or
CREATE SCHEMA DatabaseName;

After this, creating tables automatically generates a physical schema in the interface so users can keep track of what has been done compared to the design. To export this drawing in MySQL, go to Database then Reverse Engineer and follow the given steps.

It is possible to delete a schema, but be very careful with using the following statement since you may not be able to restore the database.

DROP DATABASE [IF EXISTS] DatabaseName;

Without IF EXISTS, an error is raised when the given database doesn’t exists.

We use the online shopping system example we’ve mentioned.

DROP DATABASE IF EXISTS ShoppingDB;
CREATE DATABASE ShoppingDB;

Create a Table

USE DatabaseName;
CREATE TABLE table_name (
    column_name_1 DATATYPE,
    ...
    column_name_k DATATYPE,
    ...
    column_name_n DATATYPE
);

In practice, there are many databases present in the system. So to specifiy which database you want to create a table in, it’s best to place USE DatabaseName; on top. Also, you can check out SQL data types in MySQL to learn more about them.

DEMONSTRATION OF A REAL TABLE

USE ShoppingDB;
CREATE TABLE customers (
    customerID INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(15)
);

Beside core syntax, some other keywords can also be added to columns.

• PRIMARY KEY is a must-have to designate this column’s role, that is, there is only one such column and all its indices are unique. So it’s best practice to set it to something that cannot be changed.
• The uniqueness here is guaranteed by AUTO_INCREMENT which is for the numbers to automatically go up by 1 when go to the next row rather than manually typing them.
• The numbers inside parentheses indicates the maximum size of the corresponding datatype, for instance, Phone has at most 15 characters.

Insert Data Into a Table

INSERT INTO table_name (column_name_1, ..., column_name_n)
VALUES 
(value_11, ..., value_1n),
...
(value_k1, ..., value_kn),
...
(value_n1, ..., value_nn);

Each (value_k1, ..., value_kn) inserted is a row in a table. When a column is of AUTO_INCREMENT, it should be excluded when values are added.

INSERT INTO customers (first_name, last_name, email, phone)
VALUES 
('Homer', 'Simpson', 'hsimps@thesimpsons.com', '777-555-1234'),
('Ned', 'Flanders', 'nfland@thesimpsons.com', '777-555-3456'),
('Barney', 'Gumble', 'bgumbl@thesimpsons.com', '777-555-5678');

Again, the indices in PRIMARY KEY are unique. If you try to insert data with the same primary index, the system will raise an error instead of duplicating information.

You may concern, Do we always have to type data line by line? Is there a more efficient way to do so?

If there is a spreadsheet file available, you can import it into MySQL rather than insert data manually. Practically, we create a database and data is automatically added via interacting with software from accounting or cameras. As such, data is inserted as soon as clients type in their information.

Read/Query Data

SELECT * FROM table_name;
-- or
SELECT column_name_1, ..., column_name_k FROM table_name;

The former syntax is used when you want to read all the columns while the latter displays the given ones.

Relate Tables

CREATE TABLE table_name (
    column_name_1 DATATYPE,
    ...
    column_name_n DATATYPE,
    FOREIGN KEY (column_name_k1) REFERENCES related_table_name_1(related_column_name_x1),
    ...
    FOREIGN KEY (column_name_ki) REFERENCES related_table_name_m(related_column_name_xj)
);

The FOREIGN KEY constraint is very important and used to preserve data unification and prevent actions that could destroy links between tables. Now, having both “Customer” and “Product” tables, we’re ready to make the “Order” table using their connections.

CREATE TABLE order (
    orderID INT PRIMARY KEY,
    customerID INT,
    order_date DATE,
    FOREIGN KEY (customerID) REFERENCES customer(customerID)
);

You may wonder, Each column must have a unique primary key, what if I specifically want two foreign keys combined to be its primary key? Is it possible? If it is, what do I do?

More than one foreign keys combined to be the primary key of a column is accepted. The primary key in this case is called a composite key. We’ll talk more about this in the next post.

How to use SQL for Data Analysis - SQL Queries

SQL (Structured Query Language) is a powerful tool for data analysis. It allows you to query and manipulate databases efficiently. Let’s take a look at common basic SQL queries. The following examples were performed with a given database. You can either practice with your own data or check out SQL Animate developed by AI VIETNAM.

SELECT

As mentioned earlier, we can use SELECT to

• Read all data of a table.

• Read some selected columns from a table.

In addition, we can also use it to perform tasks as follows.

Modify a Column’s name with Alias

SELECT column_name AS alias
FROM table_name;

An alias serves as a temporary name for a column so either queries are more concise or their results are more readable. AS is optional, that is, we can skip it and get the same output. We can also make an alias for a table in FROM statement and use it in SELECT statement. It’s possible because SQL reads FROM before SELECT.

Modify a Column’s Values

SELECT 
    column_name,
    column_name desired_operation
FROM table_name;

Again, this will not change the table permanently, instead it helps with seeing a desired operation’s result momentarily.

SELECT
    price,
    price + 10
FROM products;

For example, this query produces two columns where the existing one comes first and the new one is named price + 10. You can make the latter’s name different with an alias, that is, price + 10 AS new_price.

Read Distinct Values of a Column

SELECT DISTINCT column_name FROM table_name;

Values in a column can be duplicate, for instance, different customers are from the same area. If we want to see all the areas they are from, there’s no need for duplication. In this case, SELECT DISTINCT is necessary.

WHERE

WHERE is used when you want to read a colum’s values with particular conditions like a Python if statement.

SELECT column_name FROM table_name
WHERE 
    condition_1
    AND condition_2
    ...
    AND condition_n; 

Like in Python, comparison operators are considered conditions.

These comparison operators work for not only numbers but dates, letters, and words as well. For instance, the condition statment `WHERE order_date > ‘2000-12-03’ means order_date after March 12, 2000.

In addition to comparison, logical operators AND, OR, and NOT can also be applied here. As such, you can certainly put NOT in front of any comparison operators above to reverse the meaning, for example, NOT = is the same as !=. Learn more about how operators work using the Truth Table.

You can definitely use arithmetic operations and alias introduced in the SELECT section. Keep in mind that you may not use the alias to compare. Let’s look at an example to understand this idea.

SELECT *, price + 10 AS new_price
FROM products
WHERE price + 10 < 30;

In the condition statement, we cannot use new_price to replace price + 10 since new_price is to replace the name of a column, not what that column does. In theory, AS and SELECT are usually read by SQL after other statements.

IN - BETWEEN … AND

Instead of OR and comparision operators, we can use IN and BETWEEN ... AND, respectively. The latter way may be easier to understand since it sounds closer to our spoken language.

SELECT * FROM table_name
WHERE 
    column_name IN (desired_value_1, ..., desired_value_n);

SELECT * FROM table_name
WHERE 
    column_name BETWEEN desired_value_1 AND desired_value_2;

Desired values are not necessarily values of the chosen column. Let’s see two ways of using the WHERE statement.

SELECT * FROM states
WHERE 
    state = 'LA' OR state = 'TX' OR state = 'FL'
    AND ranking > 10
    AND ranking < 40;

SELECT * FROM customers
WHERE 
    state in ('LA', 'TX', 'FL')
    AND ranking BETWEEN 10 AND 40; 

IS NULL - ORDER BY - LIMIT

IS (NOT) NULL
Excluding NULL values is an important step of data preprocessing. IS NULL shows such values of a column while IS NOT NULL does the opposite.

SELECT * FROM table_name
WHERE column_name IS (NOT) NULL;

ORDER BY … (DESC)
We can display a table so that their values in a given column are arranged in ascending order by default and in descending order using DESC.

SELECT * FROM table_name
ORDER BY column_name (DESC)

LIMIT
Using LIMIT with ORDER BY is to choose a specific range you wish to see. That is, after the rearrangement, you can choose at which row you want to start and stop seeing. Here, the offset is the number of rows skipped from the beginning and the count is the number of rows displayed after skipping. The former is understood if there is only one number is given.

SELECT * FROM table_name
ORDER BY column_name (DESC)
LIMIT offset, count;

And of course, you’re allowed to use WHERE condition statement with ORDER BY and LIMIT to narrow your search.

LIKE - REGEXP

There are situations where you need to look at rows with particular characters (including numbers) in a column. Such situations may not be handled using the above queries. This is when LIKE and REGEXP come in handy.

LIKE

SELECT * FROM table_name
WHERE column_name LIKE `...`;

Besides desired characters, you can use the following symbols to specify how you want a string to look like.

Let’s see what should go in the ... above and what they mean based on the given description. Note that lower or upper cases are not distinguished in SQL.

For strings with literal % and _ characters, you can place them in a pair of brackets to distinguish them from the symbols.

REGEXP
Even though LIKE takes care of a few common cases, it may take some efforts to tackle more complicated problems using only LIKE. With those instances, REGEXP is more flexible and preferred and we’ll discuss some of them in a moment. In fact, REGEXP is short for Regular Expression which appears in all programming languages.

Let’s see how these symbols mean in actual situations.

There are many more regular expressions in theory that you may want to check out in the below image.

REGULAR EXPRESSION IMAGE

FAQ

Here are some good questions asked during class. The answers to these questions came from either some admin, TA, STA, or members of AIO2025.

Check List

• Exercise 1 (date)
• Exercise 2 (date)
  • Put on left sock
  • Put on right sock
• Go to school