SQL Fundamentals

  • ID: DBSQL-F-L02
  • Type: Lesson
  • Audience: Public
  • Theme: Selecting, filtering, and ordering data

SQL is a language for asking structured questions.

In this lesson, we focus on the core pattern used in analytical queries:

SELECT → FROM → WHERE → ORDER BY → LIMIT

Setup

Confirm the Database Connection

print(con)
<sqlite3.Connection object at 0x10b6f9990>

The printed object confirms that Python created a live SQLite connection.

This is not a connection to a server.

SQLite is file-based and embedded, so the database engine runs locally within the process.

List Available Tables

tables = pd.read_sql(
    "SELECT name FROM sqlite_master WHERE type='table';",
    con
)
print(tables)
          name
0    customers
1     products
2       orders
3  order_items

Select Specific Columns

SELECT customer_id, full_name, country
FROM customers;

Selecting only needed columns keeps queries readable and reduces unnecessary data transfer.

Filter with WHERE

SELECT full_name, city
FROM customers
WHERE country = 'Tanzania';

You can combine conditions:

SELECT order_id, order_date, status
FROM orders
WHERE status = 'paid' AND order_date >= '2026-02-01';

Sort with ORDER BY

SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC;

DESC sorts from largest to smallest.

Limit Results

SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 3;

Execute SQL in Python

query = '''
SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 3;
'''

df = pd.read_sql(query, con)
print(df)
     product_name  unit_price
0      Headphones       19.50
1  USB Drive 32GB        8.90
2    Water Bottle        6.25

This is the backbone-to-analysis workflow:

Database → SQL → DataFrame