#8.2 SqLite CRUD - Advanced Topics

 Here’s an SQLite tutorial expansion covering Joins, Indexing, and Transactions in four short modules (~30 minutes total). This will help students optimize queries, combine data from multiple tables, and ensure data integrity.

---

🎯 Goal of the Tutorial

• Understand Joins to combine multiple tables.
• Learn Indexing for faster queries.
• Use Transactions to maintain database integrity.

---

📌 Module 1: Understanding Joins (8 mins)

🔹 Why Use Joins?

Joins help combine data from multiple tables using common keys.

🔹 Creating Example Tables

Students Table

CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    course_id INTEGER
);

Courses Table

CREATE TABLE courses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    course_name TEXT NOT NULL
);

Inserting Data

INSERT INTO courses (course_name) VALUES ('Computer Science'), ('Mathematics');
INSERT INTO students (name, course_id) VALUES ('Alice', 1), ('Bob', 2);

🔹 Performing Joins

INNER JOIN (Most Common)

SELECT students.name, courses.course_name
FROM students
JOIN courses ON students.course_id = courses.id;

✔️ Shows only students with a matching course.

LEFT JOIN (All Students, Even Without a Course)

SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.course_id = courses.id;

✔️ Includes students without a course (NULL values).

RIGHT JOIN (Not Supported in SQLite)

SQLite doesn’t support RIGHT JOIN, but we can swap table positions and use LEFT JOIN.

🛠 Exercise 1

1. Create a teachers table.
2. Use a JOIN to show which teacher is assigned to which course.

---

📌 Module 2: Indexing for Performance (7 mins)

🔹 What is Indexing?

• Speeds up queries by reducing search time.
• Works like a book index.

🔹 Creating an Index

CREATE INDEX idx_students_course ON students(course_id);

✔️ Speeds up searches involving course_id.

🔹 Checking Execution Time

EXPLAIN QUERY PLAN 
SELECT * FROM students WHERE course_id = 1;

✔️ Shows whether the query is using an index.

🔹 Deleting an Index

DROP INDEX idx_students_course;

🛠 Exercise 2

1. Create an index for the students' names.
2. Run a SELECT query with and without the index.

---

📌 Module 3: Transactions for Data Integrity (8 mins)

🔹 What is a Transaction?

• Ensures multiple operations succeed or fail together.
• Uses BEGIN, COMMIT, and ROLLBACK.

🔹 Example: Safe Bank Transfer

BEGIN TRANSACTION;

UPDATE students SET course_id = 2 WHERE name = 'Alice';

-- Simulate an error before committing
ROLLBACK; 

SELECT * FROM students;  -- Alice's course remains unchanged

✔️ ROLLBACK prevents incomplete operations from saving.

🔹 COMMIT: Finalizing Transactions

BEGIN TRANSACTION;

UPDATE students SET course_id = 2 WHERE name = 'Alice';
COMMIT;

SELECT * FROM students;  -- Alice's course is now updated

✔️ COMMIT permanently saves the changes.

🛠 Exercise 3

1. Begin a transaction to update multiple students.
2. Rollback before committing and check if data changes.

---

📌 Module 4: Combining Joins, Indexing, and Transactions (7 mins)

🔹 Complex Query Example

BEGIN TRANSACTION;

SELECT students.name, courses.course_name
FROM students
JOIN courses ON students.course_id = courses.id
WHERE students.name LIKE 'A%';

COMMIT;

✔️ Finds students whose names start with 'A' and ensures query execution in a transaction.

🔹 Optimizing Queries

CREATE INDEX idx_courses_name ON courses(course_name);

✔️ Speeds up searching for courses by name.

🛠 Final Exercise

1. Create a new table for enrollments and use a JOIN to show which student is in which course.
2. Use transactions to ensure enrollment updates succeed.
3. Optimize the query using an index.

---

🎯 Summary of the Tutorial

✔️ Module 1: Joins (Combining tables)
✔️ Module 2: Indexing (Faster queries)
✔️ Module 3: Transactions (Data integrity)
✔️ Module 4: Combining them in real-world queries

---

📌 Next Steps ( We Will Study later !)

• Try using foreign keys to enforce relationships.
• Experiment with nested joins and multi-table indexes.
• Learn about triggers for automated actions.

Would you like sample SQL scripts for practice? 🚀