Introduction to SQL with SQLite

In this tutorial, we will learn the basics of SQL (Structured Query Language) using SQLite.
SQLite is a lightweight, file-based database system that integrates easily with Python.

We will:

1. Create a simple relational table.
2. Insert data.
3. Write queries to explore the data.
4. Practice with exercises.

# Step 0: Setup
import sqlite3
import pandas as pd

# Create an in-memory SQLite database
conn = sqlite3.connect(":memory:")

# Create a cursor object
cur = conn.cursor()

Step 1: Create a Table

Let's create a Students table with the following columns:

• roll_no (Primary Key)
• name
• age
• grade

cur.execute("""
CREATE TABLE Students (
    roll_no INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    grade TEXT
)
""")
conn.commit()

Step 2: Insert Data

We will now add some rows into the Students table.

students = [
    (1, "Alice", 20, "A"),
    (2, "Bob", 21, "B"),
    (3, "Charlie", 19, "A"),
    (4, "Diana", 22, "C"),
    (5, "Evan", 20, "B"),
]

cur.executemany("INSERT INTO Students VALUES (?, ?, ?, ?)", students)
conn.commit()

Step 3: Query the Data

Let's start exploring the table.

# Select all rows
df = pd.read_sql_query("SELECT * FROM Students", conn)
df

	roll_no	name	age	grade
0	1	Alice	20	A
1	2	Bob	21	B
2	3	Charlie	19	A
3	4	Diana	22	C
4	5	Evan	20	B

Step 4: Exercises

👉 Write SQL queries for the following tasks:

1. Show the names of all students who got grade 'A'.
2. Find the average age of students.
3. List all students ordered by age (youngest first).
4. Count how many students got each grade.
5. Find the names of students older than 20.

Replace the SQL query inside the quotes and re-run the cell or create a cell for each query.

# Example: Exercise 1
df = pd.read_sql_query("SELECT name FROM Students WHERE grade = 'A'", conn)
df

	name
0	Alice
1	Charlie

Step 5: Bonus Challenge

Suppose we add another table:

• Courses(course_id, course_name)
• Enrollments(roll_no, course_id)

Write SQL queries to show which student is enrolled in which course.
This will require using JOIN between tables.

(Try creating the tables and data yourself!)