SQL for AI 103

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 have read SQL for AI 102, had some coding experience, and known a bit of AI knowledge. Absolute beginners are encouraged to read the material and repeatedly practice coding without AI assistance to strengthen their coding and code-reading skills. Note that only necessary Python knowledge is introduced here.

Relevance: The audience of this lecture were those who could have known nothing about database or have had some experience working with data. Absolute beginners are encouraged to read and practice the basics without AI assistance to familiarize themselves with the material. Note that only necessary SQL knowledge is introduced here.

________quote____________________
-quoter’s name

In today’s post, we’ll explore SQL in Data Analysis, specifically subqueries, procedure and trigger, and some advanced topics.

SQL Join

In practice, we regularly want to obtain information from at least two tables. SQL join refers to various methods of combining tables together to retrieve meanningful information. Data extraction is a key part of data mining that helps us better understand databases. Furthermore, we can apply Machine Learning algorithms to uncover patterns, gain insights, and make predictions based on broader trends in the data.

INNER JOIN

INNER JOIN or JOIN is to read data that several tables have in common. This is similar to taking intersection of sets in Python. We can think of considering one table and taking only parts that contain the other tables.

WHERE statement can also perform this action, but JOIN is preferred for this purpose, especially in large databases.

• INNER JOIN is an explicit join syntax which separates join logic from filtering logic
• WHERE is an implicit join syntax which does not.

Two Tables

SELECT * FROM table_name_1 tb1
INNER JOIN table_name_2 tb2 
    ON tb1.common_column_name = tb2.common_column_name;

This query says, take only rows whose data on common column from table 1 match with that from table 2. Swapping tables’ names provides the same result by definition.

DEMONSTRATION and EXPLANATION

Three or More Tables
We can use more INNER JOIN or JOIN statements to obtain the intersection of multiple tables.

SELECT * FROM table_name_1 tb1
INNER JOIN table_name_2 tb2
    ON tb1.common_column_name_12 = tb2.common_column_name_12
INNER JOIN table_name_3 tb3
    ON tb1.common_column_name_13 = tb2.common_column_name_13
...
INNER JOIN table_name_n tbn
    ON tbk.common_column_name_kn = tbn.common_column_name_kn;

DEMONSTRATION

SELF JOIN

SELECT *
FROM table_name tb1
JOIN table_name tb2
    ON tb1.column_name_1 = tb2.column_name_2;

SELF JOIN is the inner join of a table with itself. We can use this concept when values of two separate columns in a table belong to the same group.

DEMONSTRATION and EXPLANATION

Compound Join Condition

Recall that a table can have a composite key. If we want to join it with another table using these columns, we can modify the regular inner join as follows.

SELECT *
FROM table_name_1 tb1
INNER JOIN table_name_2 tb2
    ON tb1.column_name_1 = tb2.column_name_1
    AND tb1.column_name_2 = tb2.column_name_2
    ...
    AND tb1.column_name_n = tb2.column_name_n;

OUTER JOIN

If we have two tables, OUTER JOIN retrieves data from at least one of them including matched information. There are three types: LEFT JOIN, RIGHT JOIN, and FULL JOIN. While the last one takes both tables, the rest only takes only one, either left or right table.

SELECT * FROM table_name_1 tb1
LEFT JOIN table_name_2 tb2 
    ON tb1.common_column_name = tb2.common_column_name;

SELECT * FROM table_name_1 tb1
RIGHT JOIN table_name_2 tb2 
    ON tb1.common_column_name = tb2.common_column_name;

USING Clause

If common columns from tables have the same name, it’s unnecessary to rewrite it many times. Instead, we can simplify the query. This is extremely useful for compound join conditions.

SELECT * FROM table_name_1 tb1
JOIN table_name_2 tb2 
    USING (common_column_name);

UNION

UNION is to retrieve all conditional data from several tables. This is similar to taking union of sets in Python.

SELECT * FROM table_name_1 tb1
WHERE condition_1
UNION
SELECT * FROM table_name_2 tb2
WHERE condition_2
UNION
...
UNION
SELECT * FROM table_name_n tbn
WHERE condition_n;

SQL Temporary Queries

In numerical data analysis, it’s often useful to aggregate values by group, with summation and average being one of the most commonly used operations. We can solve this problem either directly or indirectly.

GROUP BY - SUM

We use necessary statements to manipulate data as usual.

SELECT * FROM table_name_1 tb1
JOIN table_name_2 tb2 
    USING (common_column_name)
GROUP BY tb2.column_name_group
SUM(tb2.column_name_sum);

There are two mandatory operations in this query template. GROUP BY allows us to group data with the same name together. Then, SUM automatically sums up values for each group. For average, use AVG.

Common Table Expression (CTE) WITH … AS

We can create a temporary table where data are managed and call it afterwards. Just like a function, the data in this table is only created on call.

WITH temp_table_name AS (
    SELECT column_names, SUM(column_name_sum) AS temp_column_name
    FROM table_name_2 tb2
    GROUP BY column_name_group
    SUM(column_name_sum)
)

SELECT * FROM table_name_1 tb1
JOIN temp_table_name
USING (common_column_name);

WITH clause acts as a function with temp_table_name as the function name. We can reuse this temporary table whenever it is needed. Since there is only one table in the clause, there is no point specifying where those columns come from.

EXAMPLE, DEMONSTRATION, and EXPLANATION

Subqueries

In cases where we want to avoid JOIN and GROUP BY, we can apply subqueries.

SELECT column_names,
       (SELECT SUM(column_name_sum)
       FROM table_name_2 tb2
       WHERE tb1.common_column_name = common_column_name) AS temp_column_name
FROM table_name_1 tb1;

The query inside parentheses is called a subquery. It is calculated first based on the condition given in the main query.

Temporary Table

Even though CTE and subqueries are convenient, reevaluation is executed every time they are called.

You may concern, How can we save the outcome of such a query so that it saves resources and is still reusable?

Instead of a shallow temporary table as above, we can create a deep temporary table and insert required operations. This momentary table “lives” throughout a session but disappears entirely when the session ends.

CREATE TEMPORARY TABLE temp_table_name (
    column_names DATATYPE
);

INSERT INTO temp_table_name 
SELECT column_names, 
       SUM(column_name_sum) AS temp_column_name
FROM table_name_2 tb2
GROUP BY column_name_group;

SQL Stored Procedure and Trigger

As mentioned, temporary tables are like renters—staying for a limited time while they’re officially signed with their landlord (the database session). Though they can be very useful for storing intermediate results or simplifying complex queries, they have limitations, such as limited lifespan and lack of persistence after the session ends. As such, we want to store the temporary table in a place where we can grab and use it any time.

Stored Procedure

A stored procedure is essentially a precompiled function of SQL statements stored within a database, designed for efficient and reusable execution of database operations. Once it is created, we can CALL it.

DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
    -- SQL statements
END;
//
DELIMITER;

CALL procedure_name();

Using the previous section’s example, we can place the temporary table where SQL statements go. It will then be executed whenever our session starts.

DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
    DROP TABLE IF EXISTS temp_table_name;
    CREATE TEMPORARY TABLE temp_table_name (
        column_names DATATYPE
    );

    INSERT INTO temp_table_name 
    SELECT column_names, 
        SUM(column_name_sum) AS temp_column_name
    FROM table_name_2 tb2
    GROUP BY column_name_group; 
END;
//
DELIMITER;

Trigger

One advantage when working with any computer program or software is that actions can possibly be logged somewhere. SQL is not an exception.

A trigger is a set of SQL statements that run and log every time specific oprations are conducted such as inserting, updating, or deleting a row.

CREATE TRIGGER trigger_name
AFTER [INSERT, UPDATE, DELETE]  ON table_name
FOR EACH ROW
-- SQL statements

AFTER can be replaced with BEFORE depending on users’ requirement. We can choose one of the three available operations.

VALIDATION

RECURSIVE SQL

WITH RECURSIVE temp_table_name AS (

)

before UNION ALL: return one row after UNION ALL: get the rest

he said manager_id = 1 -> return Null???

after practice slides

______________________TABLE ONLY__________________________________________________________________

# _________________explanation_________________

_________________setup_________________

# test
_________________code_________________

'''
_________________rendered output_________________
'''

# _________________explanation_________________

_________________setup_________________

# test
_________________code_________________

'''
_________________rendered output_________________
'''

______________________COLLAPSIBLE BLOCK WITH TABLE INSIDE__________________________________________________________________

# _________________explanation_________________

_________________setup_________________

# test
_________________code_________________

'''
_________________rendered output_________________
'''

# _________________explanation_________________

_________________setup_________________

# test
_________________code_________________

'''
_________________rendered output_________________
'''

______________________COLLAPSIBLE BLOCK WITH EXERCISES ONLY__________________________________________________________________

______________________COLLAPSIBLE BLOCK WITH EXERCISES AND CODE__________________________________________________________________

_________________code_________________

1. ______main idea________: ________content - explanation_______________________
2. ______main idea________: ________content - explanation_______________________
3. ______main idea________: ________content - explanation_______________________
   • ______main idea________: ________content - explanation_______________________
   • ______main idea________: ________content - explanation_______________________
   • ______main idea________: ________content - explanation_______________________

• ______main idea________: ________content - explanation_______________________
• ______main idea________: ________content - explanation_______________________
• ______main idea________: ________content - explanation_______________________

Applications in AI

Pro tip: ______________________

______________________

:heavy_exclamation mark: ______________________

__________________________________________________________________?

__________________________________________________

>>>

❌ ✅ ⚠️

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