Definition
SQL is the standard language for working with relational databases, which store data in tables. SQLite is a small database that lives in one file and comes built into Python through the sqlite3 module.
In simple words
A database is a set of very strict spreadsheets that many programs can use safely. SQL is how you ask it questions: show me all students with marks above 80, sorted highest first.
Overview
- SQL (Structured Query Language) is the standard language for asking questions of relational databases.
- SQLite is a small database stored in a single file and built into Python's sqlite3 module.
- A query is written in a fixed clause order: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
- Parameters (? placeholders) must be used for user values to prevent SQL injection.
- Changes are saved with commit(); a with-block or close() ends the connection.
- pandas.read_sql turns query results straight into a DataFrame.
Why it matters
Most of the world's business data sits in SQL databases, so it's required for nearly every data job, and often the first round in interviews.
Key terms
- Relational database
- Data stored in tables of rows and columns, linked by shared keys.
- Primary key
- A column whose value uniquely identifies each row, such as student_id.
- Foreign key
- A column that refers to the primary key of another table, linking them.
- Query
- An SQL statement that asks for or changes data, such as SELECT.
- JOIN
- Combining rows from two tables using matching key values.
- Parameterised query
- A query with ? placeholders filled safely by the library, preventing SQL injection.
Types
SQL statements are grouped into sub-languages by what they do. These are the types every SQL user meets.
1. Data Definition Language (DDL)
Statements that create and change the structure of the database.
- How it works: CREATE TABLE defines columns, types and keys; ALTER TABLE changes them; DROP TABLE deletes a table and all its rows.
- Example: CREATE TABLE students (roll_no INTEGER PRIMARY KEY, name TEXT, city TEXT).
- When to use it: Use it when setting up or changing tables; be very careful with DROP.
2. Data Manipulation Language (DML)
Statements that add, change and delete rows.
- How it works: INSERT INTO adds rows; UPDATE ... SET changes values; DELETE FROM removes rows; without WHERE, UPDATE and DELETE affect every row.
- Example: cursor.execute("INSERT INTO students VALUES (?, ?, ?)", (1, "Asha", "Pune")).
- When to use it: Use it to keep data up to date; always use parameters and test the WHERE clause with a SELECT first.
3. Data Query Language (SELECT queries)
SELECT statements that read data, optionally filtering, grouping and sorting.
- How it works: SELECT city, AVG(marks) FROM results WHERE year = 2024 GROUP BY city HAVING COUNT(*) > 10 ORDER BY 2 DESC LIMIT 5.
- Example: Finding the five cities with the best average marks.
- When to use it: Use it for nearly all analysis work in SQL.
4. Joins
Queries that combine rows from two or more tables on matching keys.
- How it works: INNER JOIN keeps matching rows; LEFT JOIN keeps all rows of the left table; SQLite added RIGHT and FULL OUTER JOIN in version 3.39.
- Example: SELECT s.name, r.marks FROM students s JOIN results r ON s.roll_no = r.roll_no.
- When to use it: Use them whenever the information you need is split across tables.
5. Transaction control (TCL) and constraints
COMMIT, ROLLBACK and rules such as PRIMARY KEY, NOT NULL, UNIQUE, CHECK and FOREIGN KEY.
- How it works: A transaction groups changes so that they all succeed or all fail; constraints make the database reject invalid data.
- Example: Moving money: subtract from one account and add to another inside one transaction, rolling back if either step fails.
- When to use it: Use transactions for linked changes and constraints to keep data valid at the source.
Explained in detail
Why databases?
A CSV file is fine for a few thousand rows that one person reads. Real organisations need more: millions of rows, many people reading and writing at once, guarantees that data is not lost halfway through an update, and fast searches. Relational databases, built on ideas published by Edgar F. Codd at IBM in 1970, provide all of this. SQL, the Structured Query Language, developed at IBM in the 1970s, is how you talk to them, and it remains one of the most requested skills in data jobs. SQLite, created by D. Richard Hipp in 2000, stores a whole database in a single file and is built into Python, Android phones and web browsers, which makes it perhaps the most widely deployed database in the world.
The core statements
| Statement | Purpose | Example |
|---|---|---|
| CREATE TABLE | Define a table and its columns | CREATE TABLE marks (id INTEGER PRIMARY KEY, name TEXT, score INTEGER) |
| INSERT | Add rows | INSERT INTO marks (name, score) VALUES ('Riya', 91) |
| SELECT … WHERE | Read rows that match a condition | SELECT name FROM marks WHERE score >= 40 |
| ORDER BY, LIMIT | Sort and take the top rows | SELECT * FROM marks ORDER BY score DESC LIMIT 3 |
| GROUP BY | Summarise per group | SELECT subject, AVG(score) FROM marks GROUP BY subject |
| JOIN | Combine tables | SELECT s.name, m.score FROM students s JOIN marks m ON s.id = m.student_id |
| UPDATE, DELETE | Change or remove rows | UPDATE marks SET score = 85 WHERE id = 3 |
Why split data across tables?
Storing a student's name and address on every row of a marks table repeats it dozens of times, and changing an address means updating every copy, with a risk that some are missed. Instead, a students table holds each student once with a primary key, and the marks table refers to it with a foreign key. This design, called normalisation, avoids duplication and contradictions. JOIN brings the pieces back together when you need them.
SQL from Python, safely
- sqlite3.connect("school.db") opens or creates a database file; conn.execute() runs SQL; conn.commit() saves changes.
- Never build queries by pasting user input into a string: "... WHERE name = '" + name + "'" lets an attacker inject SQL. Use placeholders: conn.execute("SELECT * FROM marks WHERE name = ?", (name,)).
- pandas reads query results straight into a DataFrame with pd.read_sql(query, conn), which is how most data analysis begins in companies.
- Let the database do filtering and summarising where possible; it is usually faster than loading everything into Python.
How it works
| SQL | Does |
|---|---|
| CREATE TABLE | Makes a table |
| INSERT | Adds rows |
| SELECT ... WHERE | Reads matching rows |
| ORDER BY | Sorts |
| GROUP BY | Groups for totals |
| JOIN | Combines tables |
| UPDATE, DELETE | Changes or removes rows |
In code
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE students (name TEXT, city TEXT, mark INT)")
db.executemany("INSERT INTO students VALUES (?, ?, ?)", [
("Riya", "Pune", 91), ("Aman", "Delhi", 72), ("Sara", "Pune", 85),
])
db.execute("CREATE TABLE cities (city TEXT, state TEXT)")
db.execute("INSERT INTO cities VALUES ('Pune', 'Maharashtra'), ('Delhi', 'Delhi')")
print(db.execute("SELECT name FROM students WHERE mark > ? ORDER BY mark DESC", (80,)).fetchall())
print(db.execute("SELECT city, COUNT(*), AVG(mark) FROM students GROUP BY city").fetchall())
print(db.execute("""SELECT s.name, c.state FROM students s
JOIN cities c ON s.city = c.city""").fetchall())Output
[('Riya',), ('Sara',)]
[('Delhi', 1, 72.0), ('Pune', 2, 88.0)]
[('Riya', 'Maharashtra'), ('Aman', 'Delhi'), ('Sara', 'Maharashtra')]Common mistakes
- Using f-strings to build SQL with user data, which opens the door to SQL injection.
- Forgetting commit() on a real file database, so changes aren't saved.
Check yourself
Summary
- SQL reads and changes tables.
- SQLite comes built into Python.
- Always use placeholders for user input.
Finished reading?
Sources: Python docs: sqlite3