#8.1 SQLite tutorial basics

 Here's a 30-minute SQLite tutorial divided into four modules to explain CRUD principles for undergraduate students. Each module takes about 7-8 minutes and includes theory, examples, and exercises.

---

🎯 Goal of the Tutorial

• Learn the basics of SQLite.
• Understand CRUD (Create, Read, Update, Delete) operations.
• Use SQLite commands to manage a database.

---

📝 Module 1: Introduction to SQLite & Setting Up (7 mins)

🔹 What is SQLite?

• A lightweight, self-contained database.
• No need for a separate server (unlike MySQL/PostgreSQL).
• Used in mobile apps, embedded systems, and local applications.

🔹 Installing SQLite

Windows:

1. Download SQLite from SQLite official site.
2. Extract and open the sqlite3.exe file.
3. Run:

   sqlite3
   

Linux/macOS:

1. Install via terminal:

   sudo apt install sqlite3   # Ubuntu/Debian
   brew install sqlite        # macOS
   

2. Run SQLite:

   sqlite3
   

🛠 Exercise 1: Creating a Database

sqlite3 students.db

✔️ This creates a students.db file where we will store our tables.

---

📌 Module 2: Creating and Reading Data (7 mins)

🔹 Creating a Table (C in CRUD)

We create a students table:

CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    course TEXT
);

🔹 Inserting Data

INSERT INTO students (name, email, age, course)
VALUES ('Alice', 'alice@example.com', 20, 'Computer Science');

🔹 Reading Data (R in CRUD)

SELECT * FROM students;

🛠 Exercise 2: Insert and Read

1. Insert two more student records.
2. Run SELECT * FROM students; to check.

---

✏️ Module 3: Updating and Deleting Data (7 mins)

🔹 Updating Data (U in CRUD)

Updating Alice’s course:

UPDATE students 
SET course = 'Data Science' 
WHERE name = 'Alice';

🔹 Deleting Data (D in CRUD)

DELETE FROM students WHERE name = 'Alice';

🛠 Exercise 3:

1. Update another student’s age.
2. Delete a student record.

---

🚀 Module 4: Advanced Queries & Best Practices (7 mins)

🔹 Filtering Data

SELECT * FROM students WHERE age > 18;

🔹 Sorting Data

SELECT * FROM students ORDER BY name ASC;

🔹 Counting Records

SELECT COUNT(*) FROM students;

🛠 Final Exercise

1. Retrieve students older than 21.
2. Count the number of students.

Dropping Tables and Databases in SQLite

SQLite allows you to delete tables using DROP TABLE, but it does not support dropping databases directly since an SQLite database is just a file.

---

🔹 Dropping a Table in SQLite

To delete a table along with all its data, use:

DROP TABLE table_name;

Example

DROP TABLE students;

✔️ This removes the students table permanently.
❗ Warning: This action cannot be undone.

---

🔹 Dropping a Database in SQLite

Since SQLite databases are just files, you delete a database by removing the database file from the system.

Steps to Drop an SQLite Database

1. Find the database file (e.g., mydatabase.db).
2. Delete it manually using the terminal or command prompt.

Using Command Line

Windows

del mydatabase.db

Linux/macOS

rm mydatabase.db

✔️ This permanently deletes the database file.
❗ Warning: This cannot be undone unless you have a backup.

---

🔹 Checking Before Dropping

Before dropping a table, check if it exists:

DROP TABLE IF EXISTS students;

✔️ This prevents errors if the table doesn’t exist.

---

🔹 Alternative: Clearing a Table Instead of Dropping

If you just want to clear all data but keep the table structure, use:

DELETE FROM students;

✔️ Removes all records but keeps the table.

OR

DELETE FROM students WHERE 1=1;

✔️ Same as above but can be used dynamically in scripts.

Would you like a script to automate table deletions? 🚀

=======================================================================

🎯 Summary of the Tutorial

✔️ Module 1: Setup and creating a database
✔️ Module 2: Creating and reading records
✔️ Module 3: Updating and deleting data
✔️ Module 4: Advanced queries

---

📌 Next Steps

• Try integrating SQLite with Python, Node.js, or JavaScript.
• Experiment with joins, indexing, and transactions.

Would you like me to prepare slides or code files for this tutorial? 🚀