• Thanks to those who have already sent me their group members! 😉
• If you haven’t done so yet, please email me your group members as soon as possible (4-5 people per group)
• Otherwise, you’ll be assigned to a random group
• The project instructions are available at https://github.com/danilofreire/qtm350/blob/main/project/project-instructions.pdf
• The submission deadline is December 8th at 11:59 PM
• Please reach out if you have any questions or need help!

• Just a quick reminder that I am also teaching DATASCI 185 - Introduction to AI Applications next semester 🤖
• The course is for everyone, no prior programming or AI experience is required
• If you (or anyone you know) is interested, please check it out and consider enrolling!
• A short course description:

This course offers a non-technical introduction to artificial intelligence and its effects on institutions, work and everyday life. The course is practical: students will learn how modern systems are designed and how to ask the right questions about data, reliability and harms. The course combines short demonstrations (no programming experience required), case studies, and project work. It is suitable for undergraduate students from any faculty who want a grounded understanding of what AI can and cannot do.

• For more information, please visit https://atlas.emory.edu/ or email me at danilo.freire@emory.edu to request the syllabus or discuss further!

• As with many languages, you can merge two tables in SQL either by columns or by rows
• The most common method is the JOIN clause
• JOIN is used to combine rows and columns from two or more tables based on a related column between them
• As you know, there are two types of keys, primary and foreign keys
• The primary key is a column that uniquely identifies each row in a table
• A foreign key is a column that identifies a column in another table
  • One table can have multiple foreign keys, and they can be NULL
  • SQLite supports foreign keys, and the Python sqlite3 module handles them correctly by default

• Let’s load the libraries and connect to the SQLite database. We’ll use a file named lecture19.db.

import pandas as pd; import sqlite3

# Connect to the SQLite database (this will create the file if it doesn't exist)
connection = sqlite3.connect('lecture19.db'); cursor = connection.cursor()

cursor.execute('DROP TABLE IF EXISTS players;')
cursor.execute('''
CREATE TABLE players (
    player_id INTEGER PRIMARY KEY AUTOINCREMENT, player_name TEXT NOT NULL UNIQUE,
    goals INT NOT NULL, victories INT NOT NULL
);
''')

cursor.execute('DROP TABLE IF EXISTS teams;')
cursor.execute('''
CREATE TABLE teams (
    team_id INTEGER PRIMARY KEY AUTOINCREMENT, team_name TEXT NOT NULL
);
''')
connection.commit() # Commit changes

• Now let’s insert some data into the tables!

# Insert data into the tables
cursor.execute('''
INSERT INTO players (player_name, goals, victories) VALUES
('Messi', 10, 5),
('Vini Jr', 8, 4),
('Neymar', 6, 3),
('Mbappé', 5, 2),
('Lewandowski', 4, 1),
('Haaland', 5, 3);
''')

cursor.execute('''
INSERT INTO teams (team_name) VALUES
('Inter Miami'),
('Real Madrid'),
('Santos'),
('Real Madrid'),
('Barcelona');
''')
connection.commit() # Commit changes

• Let’s see our tables using pandas 🐼
• read_sql works fine with the sqlite3 connection object!

pd.read_sql('SELECT * FROM players', connection)

pd.read_sql('SELECT * FROM teams', connection)

• The INNER JOIN returns only the records where there is a match between both tables (intersection) based on the join condition
• If there’s no match for a record in either table, that record will be excluded from the results
• The matching condition is specified in the ON clause (e.g., ON table1.id = table2.id)
• Note that Haaland (player_id 6) is not in the teams table (which only has 5 rows), so he will not appear in the result set

pd.read_sql('''
SELECT players.player_name, teams.team_name, players.goals, players.victories
FROM players
INNER JOIN teams
ON players.player_id = teams.team_id;
''', connection)

• The LEFT JOIN returns all records from the left table (first table) and the matched records from the right table (second table)
• The result is NULL (None or NaN in Python) for columns from the right side if there is no match
• This is perhaps the most common type of join, as it keeps all the data from the table we are usually primarily interested in (the “left” table)
• Note that Haaland is included here because he is in the players table (the left table)

pd.read_sql('''
SELECT players.player_name, teams.team_name, players.goals
FROM players
LEFT JOIN teams
ON players.player_id = teams.team_id;
''', connection)

• The RIGHT JOIN command returns all records from the right table (second table) and the matched records from the left table (first table)
• SQLite does not implement RIGHT JOIN, so we emulate it by swapping tables in a LEFT JOIN
  • We select from the teams table first, then LEFT JOIN the players table
• In our case, since teams has fewer rows than players and all team_ids match a player_id, the emulated result looks the same as the INNER JOIN.

pd.read_sql('''
SELECT players.player_name, teams.team_name, players.goals
FROM teams
LEFT JOIN players
ON players.player_id = teams.team_id;
''', connection)

• FULL OUTER JOIN returns all records from both of them, regardless of whether there is a match in the other table
• Rows from either table that do not have a match in the other table will have NULL for the columns of the table without a match
• SQLite also does not implement FULL OUTER JOIN. We can emulate it by taking a LEFT JOIN in both directions and combining with UNION (which removes duplicates)

pd.read_sql('''
SELECT players.player_id, players.player_name, teams.team_name, players.goals
FROM players
LEFT JOIN teams
ON players.player_id = teams.team_id

UNION

SELECT players.player_id, players.player_name, teams.team_name, players.goals
FROM teams
LEFT JOIN players
ON players.player_id = teams.team_id
ORDER BY players.player_id;
''', connection)

• Let’s create two new tables (products, reviews) and insert some data into them. We use REAL for the price in SQLite

# Create the tables and insert data
cursor.execute('DROP TABLE IF EXISTS reviews;') 
cursor.execute('DROP TABLE IF EXISTS products;')
cursor.execute('''
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_name TEXT NOT NULL,
    price REAL 
);
''')

# Insert products
cursor.execute('''
INSERT INTO products (product_name, price) VALUES
    ('Coffee Maker', 99.99),
    ('Toaster', 29.99),
    ('Blender', 79.99),
    ('Microwave', 149.99),
    ('Air Fryer', 89.99);
''')

cursor.execute('''
CREATE TABLE reviews (
    review_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INT,
    rating INT CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
''')

# Insert reviews
cursor.execute('''
INSERT INTO reviews (product_id, rating, comment) VALUES
    (1, 5, 'Great coffee maker!'),
    (1, 4, 'Good but expensive'),
    (2, 3, 'Average toaster'),
    (3, 5, 'Best blender ever');
''')
connection.commit()
print("Tables 'products' and 'reviews' created and populated.")

Tables 'products' and 'reviews' created and populated.

• Now try to merge the products and reviews tables using INNER JOIN and LEFT JOIN
• Explain the differences between the two results based on which products appear
• Appendix 01

• CROSS JOIN is available in SQLite
• A cross join does not use any comparisons (like ON) to match rows
• Instead, the result is constructed by pairing every row from the first table with every row from the second table (Cartesian product)
• Useful for generating all possible combinations (e.g., pairing all clothing sizes with all colours)

# Displaying cross join between players and teams
pd.read_sql('''
SELECT players.player_name, teams.team_name
FROM players
CROSS JOIN teams
ORDER BY players.player_id, teams.team_id;
''', connection)

• Another example of CROSS JOIN
• SQLite uses || for string concatenation

# Drop and recreate tables
cursor.execute('DROP TABLE IF EXISTS colours;')
cursor.execute('DROP TABLE IF EXISTS sizes;')
cursor.execute('CREATE TABLE colours (colour_name TEXT);')
cursor.execute('CREATE TABLE sizes (size_code TEXT);')
cursor.execute("INSERT INTO colours VALUES ('Black'), ('Red');")
cursor.execute("INSERT INTO sizes VALUES ('S'), ('M');")
connection.commit()

# Perform cross join and concatenate strings using ||
pd.read_sql('''
SELECT colours.colour_name, sizes.size_code, colours.colour_name || ' - ' || sizes.size_code as t_shirt
FROM colours
CROSS JOIN sizes
ORDER BY colours.colour_name, sizes.size_code DESC;
''', connection)

• A self join is when you join a table with itself to find relationships between rows in the same table
• But why would you do that? 🤔
  • When data contains hierarchical relationships (manager → employee, mother → child, etc.)
• You need two different “views” of the same table, so you use aliases to tell them apart
• Imagine an employees table, and you want to show each employee with their manager’s name:

• The SQL query would look like this:

SELECT e.name AS employee, m.name AS manager
FROM employees AS e          -- First "copy" for employees
LEFT JOIN employees AS m     -- Second "copy" for managers
ON e.manager_id = m.employee_id  -- Join condition

• The UNION operator combines the result sets of two or more SELECT statements vertically (stacking rows)
• It automatically removes duplicate rows from the combined results
  • If you want to keep duplicates, use UNION ALL
• UNION is useful when:
  • Combining different tables with different structures
  • Each query needs different logic or different columns
  • You want to add categories or labels per query
• Here is an example where we categorise players based on different criteria using UNION (note the added category column):

# UNION example: Different categories for different criteria
pd.read_sql('''
SELECT player_name, goals, victories, 'Elite Scorer' as category
FROM players
WHERE goals > 9

UNION

SELECT player_name, goals, victories, 'Team Leader' as category
FROM players
WHERE victories > 2 AND goals < 10

ORDER BY category, player_name;
''', connection)

• Similar to UNION, UNION ALL also merges tables by rows (stacks results vertically)
• Unlike UNION, UNION ALL retains all duplicate rows
• It simply appends the results. It’s generally faster than UNION as it doesn’t need to check for duplicates
• Let’s see an example combining players who are high scorers (more than 7 goals) and players with many victories (more than 3), keeping duplicates if a player meets both criteria:

# UNION ALL keeps all rows, including duplicates if a player meets both criteria.
print(pd.read_sql('''
SELECT player_name, goals, victories, 'High Scorer (>7)' AS category
FROM players
WHERE goals > 7

UNION ALL

SELECT player_name, goals, victories, 'Many Victories (>3)' AS category
FROM players
WHERE victories > 3

ORDER BY player_name, category;
''', connection))

  player_name  goals  victories             category
0       Messi     10          5     High Scorer (>7)
1       Messi     10          5  Many Victories (>3)
2     Vini Jr      8          4     High Scorer (>7)
3     Vini Jr      8          4  Many Victories (>3)

• The INTERSECT operator returns only the rows that are common to the result sets of two (or more) SELECT statements

• It’s useful when you need to find the overlap between two distinct lists that are difficult to combine with a simple AND

• For instance, we want to find “Platinum” customers for a loyalty programme based on two separate tables

  • Table 1: TopSpenders (customers who spent over $500)
  • Table 2: FrequentShoppers (customers who made 10+ purchases)
  • We want to find customers who are on both tables

# Create the two separate tables for our example
cursor.execute('DROP TABLE IF EXISTS TopSpenders;')
cursor.execute('CREATE TABLE TopSpenders (customer_name TEXT);')
cursor.execute("INSERT INTO TopSpenders VALUES ('Alice'), ('Bob'), ('David');")

cursor.execute('DROP TABLE IF EXISTS FrequentShoppers;')
cursor.execute('CREATE TABLE FrequentShoppers (customer_name TEXT);')
cursor.execute("INSERT INTO FrequentShoppers VALUES ('Bob'), ('Charlie'), ('David');")
connection.commit()

# Now, find the customers who are in BOTH lists
print(pd.read_sql('''
    SELECT customer_name FROM TopSpenders
    
    INTERSECT
    
    SELECT customer_name FROM FrequentShoppers;
''', connection))

  customer_name
0           Bob
1         David

• The EXCEPT operator returns all rows from the first SELECT statement that are not present in the second SELECT statement’s results
• It finds the difference between two tables (e.g., “Give me everything in A, except for the things that are also in B”)
• For example, now we want to run a “re-engagement” campaign
  • Table 1: TopSpenders (customers who spent over $500)
  • Table 2: FrequentShoppers (customers who made 10+ purchases)
  • Find customers who are TopSpenders but are not FrequentShoppers (they spent a lot once, but haven’t been back)

print(pd.read_sql('''
    SELECT customer_name FROM TopSpenders
    
    EXCEPT
    
    SELECT customer_name FROM FrequentShoppers;
''', connection))

  customer_name
0         Alice

• SQLite provides UPSERT (Update or Insert) operations using the INSERT ... ON CONFLICT clause
• This allows you to attempt an INSERT, and if it violates a constraint (like UNIQUE or PRIMARY KEY), you should perform a DO UPDATE SET or a DO NOTHING instead
• For instance, if we try to insert a player with an existing player_name, we update their goals instead
• You will notice that excluded is a special table that refers to the row that would have been inserted if there was no conflict. SQLite always uses this name

# Messi already exists, and he has 10 goals and 5 victories
# We will insert him again with 2 more goals and 1 more victory
cursor.execute(""" 
INSERT INTO players (player_name, goals, victories) 
VALUES ('Messi', 2, 1) 
ON CONFLICT(player_name) DO UPDATE SET 
    goals = goals + excluded.goals,
    victories = victories + excluded.victories;
""")

pd.read_sql('SELECT * FROM players', connection)

• A VIEW is a virtual table based on the result of a SELECT query.
• It does not store data itself but provides a way to simplify complex queries or encapsulate frequently used queries.
• You can create a view using the CREATE VIEW statement, followed by the view name and the SELECT query.
• In SQLite, you can create a view using the CREATE VIEW statement, and you can also drop it using DROP VIEW.

# Drop the view if it exists
cursor.execute('DROP VIEW IF EXISTS player_stats;')

# Create the view
cursor.execute('''
CREATE VIEW player_stats AS
SELECT player_name, SUM(goals) AS total_goals, SUM(victories) AS total_victories
FROM players
GROUP BY player_name;
''')
connection.commit()

pd.read_sql('SELECT * FROM player_stats LIMIT 4', connection)

• You can also use the view in a join with another table
• For example, let’s create a view that combines colours and sizes to show all possible T-shirt combinations, as we did earlier
• So we can simply query SELECT * FROM colour_size instead of writing the CROSS JOIN every time!

# Drop the view if it already exists to avoid errors on re-run
cursor.execute('DROP VIEW IF EXISTS colour_size;')

# Create the view using cursor.execute()
cursor.execute('''
CREATE VIEW colour_size AS
SELECT
    c.colour_name,
    s.size_code,
    c.colour_name || ' - ' || s.size_code as t_shirt -- Use || for concatenation
FROM colours AS c
CROSS JOIN sizes AS s
ORDER BY c.colour_name, s.size_code DESC;
''')
connection.commit() # Commit the view creation

# Now showcase the view by querying it with pandas
pd.read_sql('SELECT * FROM colour_size;', connection)

• Today we learned about different types of joins in SQL (INNER, LEFT, RIGHT, FULL OUTER), noting that SQLite does not support RIGHT and FULL OUTER joins natively
• We also learned about special joins: CROSS JOIN for combinations and SELF JOIN for relating rows within the same table
• We saw how to merge tables vertically (by row) with UNION, UNION ALL, INTERSECT, and EXCEPT
• We also saw how SQLite implements UPSERT functionality using INSERT ... ON CONFLICT and how to update existing records (DO UPDATE SET) or DO NOTHING on conflicts
• We saw how to create and use VIEWS in SQLite, which are virtual tables based on SELECT queries

• Here is the solution to the exercise comparing INNER JOIN and LEFT JOIN for products and reviews.

print("INNER JOIN Results (Only products with reviews):")
# INNER JOIN only includes products that have at least one review.
# Products like 'Microwave' and 'Air Fryer' are excluded.
print(pd.read_sql('''
    SELECT p.product_name, r.rating, r.comment
    FROM products p
    INNER JOIN reviews r ON p.product_id = r.product_id
    ORDER BY p.product_id, r.review_id; -- Added review_id for consistent ordering
''', connection))

INNER JOIN Results (Only products with reviews):
   product_name  rating              comment
0  Coffee Maker       5  Great coffee maker!
1  Coffee Maker       4   Good but expensive
2       Toaster       3      Average toaster
3       Blender       5    Best blender ever

print("\nLEFT JOIN Results (All products, reviews where available):")
print(pd.read_sql('''
    SELECT p.product_name, r.rating, r.comment
    FROM products p
    LEFT JOIN reviews r ON p.product_id = r.product_id
    ORDER BY p.product_id, r.review_id;
''', connection))

LEFT JOIN Results (All products, reviews where available):
   product_name  rating              comment
0  Coffee Maker     5.0  Great coffee maker!
1  Coffee Maker     4.0   Good but expensive
2       Toaster     3.0      Average toaster
3       Blender     5.0    Best blender ever
4     Microwave     NaN                 None
5     Air Fryer     NaN                 None

Back to exercise

• Here is the solution to the self-join exercise comparing player victories.
• Note the use of CAST(... AS REAL) or multiplying by 1.0 to ensure floating-point division in SQLite.

print("Self Join on Players to Compare Victories:")
print(pd.read_sql('''
SELECT
    p1.player_name,
    p1.victories,
    p2.player_name AS compared_to,
    p2.victories AS their_victories,
    -- Ensure floating point division by casting one operand to REAL or NUMERIC
    ROUND(CAST(p1.victories AS REAL) / p2.victories, 2) AS victories_ratio
FROM players p1
JOIN players p2
    ON p1.player_id < p2.player_id -- Avoid duplicates and self-comparison
WHERE
    p2.victories > 0 -- Avoid division by zero
ORDER BY
    p1.player_id, p2.player_id; -- Consistent ordering
''', connection))

Self Join on Players to Compare Victories:
    player_name  victories  compared_to  their_victories  victories_ratio
0         Messi          6      Vini Jr                4             1.50
1         Messi          6       Neymar                3             2.00
2         Messi          6       Mbappé                2             3.00
3         Messi          6  Lewandowski                1             6.00
4         Messi          6      Haaland                3             2.00
5       Vini Jr          4       Neymar                3             1.33
6       Vini Jr          4       Mbappé                2             2.00
7       Vini Jr          4  Lewandowski                1             4.00
8       Vini Jr          4      Haaland                3             1.33
9        Neymar          3       Mbappé                2             1.50
10       Neymar          3  Lewandowski                1             3.00
11       Neymar          3      Haaland                3             1.00
12       Mbappé          2  Lewandowski                1             2.00
13       Mbappé          2      Haaland                3             0.67
14  Lewandowski          1      Haaland                3             0.33

Back to exercise

• Unlike server-based databases like PostgreSQL, SQLite databases are typically single files.

• “Cleaning” the database often means simply deleting the database file and starting fresh.

• If you encounter issues, the easiest way to reset is to close any active connections to the database file and then remove the file using your operating system or Python’s os module.

• Here’s how you can close the connection and delete the database file (lecture19.db) we used in this lecture using Python:

# Ensure the connection is closed first
try:
    connection.close()
    print("SQLite connection closed.")
except Exception as e:
    print(f"Error closing connection (might be already closed): {e}")

SQLite connection closed.

Back to the lecture