Introduction to SQL and SQLite

Master SQL and SQLite: Projects, Exercises, and Challenges

Dive into the essentials of SQL and SQLite with this comprehensive session. Perfect for both beginners and those looking to solidify their understanding, this session covers the fundamental concepts and practical applications of SQL, focusing on SQLite, a lightweight and versatile embedded database.

In this session, you will:

• Understand SQL Basics: Learn the core SQL syntax and commands including SELECT, INSERT, UPDATE, DELETE, and CREATE TABLE. Gain insights into how SQL is used for managing and querying relational databases.
• Explore Querying Databases: Master the art of querying databases with SELECT statements, filtering with WHERE, sorting results with ORDER BY, and limiting results with LIMIT. Understand aggregation functions like COUNT, SUM, AVG, MIN, and MAX, and learn to join tables to combine data from multiple sources.
• Learn Table Creation and CRUD Operations: Discover how to design and create tables, define schemas, and implement CRUD (Create, Read, Update, Delete) operations to manage data effectively.
• Engage with Real-Time Projects: Apply your knowledge through practical projects including:
  • Personal Expense Tracker: Develop a Python application to manage and analyze personal expenses.
  • Library Management System: Create a system to handle book records with CRUD functionalities.
  • Task Management Application: Build a task manager with features for adding, viewing, updating, and deleting tasks.
  • Contact Management System: Design a contact management system with search and update capabilities.
• Complete Practical Exercises: Reinforce your learning with hands-on exercises:
  • Manipulate data in an expense tracker database.
  • Execute advanced queries in a library management system.
  • Implement and test features in a task management application.
  • Enhance search functionality in a contact management system.
  • Ensure data integrity and use transactions in SQLite.
• Prepare for Interviews: Get ready for SQL and SQLite-related interviews with targeted questions covering basics, query optimization, data modeling, and SQLite-specific features.
• Challenge Yourself with LeetCode: Solve SQL and SQLite challenges to test and improve your skills, including queries for aggregation, joins, and SQLite-specific operations.
• Participate in a Hackathon: Build a comprehensive personal management system integrating all learned concepts, demonstrating your ability to develop, test, and deploy a functional application using SQLite.

Table of Contents:

1. Introduction to SQL and SQLite
   • SQL Syntax
   • Querying Databases
   • Creating Tables
   • CRUD Operations
2. Real-Time Projects
   • Project 1: Personal Expense Tracker
   • Project 2: Library Management System
   • Project 3: Task Management Application
   • Project 4: Contact Management System
3. Detailed Project Explanation
   • Setup and Requirements
   • Implementation Steps
   • Code Snippets and Explanations
   • Verification and Testing

1. Introduction to SQL and SQLite

SQL Syntax:

• Introduction to SQL: SQL (Structured Query Language) is a standard language used to communicate with and manage databases.
• Basic SQL Commands:
  • SELECT: Retrieve data from a database.
  • INSERT: Add new data.
  • UPDATE: Modify existing data.
  • DELETE: Remove data.
  • CREATE TABLE: Define a new table structure.
  • DROP TABLE: Remove a table.

Querying Databases:

• SELECT Statements: Learn to query data using SELECT with conditions, sorting, and limiting results.
• Aggregations: Use functions like COUNT, SUM, AVG, MIN, and MAX to perform calculations.
• Joining Tables: Combine data from multiple tables using different types of joins.

Creating Tables:

• Table Structure: Define table schemas, including data types, primary keys, and constraints.
• Creating Tables in SQL: Write SQL statements to create tables with appropriate columns and constraints.

CRUD Operations:

• Create: Insert new data into tables.
• Read: Query and retrieve data.
• Update: Modify existing data.
• Delete: Remove data from tables.

2. Real-Time Projects

Project 1: Personal Expense Tracker

Objective:
Build an application to track personal expenses, allowing users to add, view, update, and delete expense records.

Setup and Requirements:

• Database: SQLite
• Technology: Python

Implementation Steps:

1. Create Database and Table:

import sqlite3

conn = sqlite3.connect('expense_tracker.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS expenses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        category TEXT NOT NULL,
        amount REAL NOT NULL,
        description TEXT
    )
''')
conn.commit()

2. Add Expenses:

def add_expense(date, category, amount, description):
    cursor.execute('''
        INSERT INTO expenses (date, category, amount, description)
        VALUES (?, ?, ?, ?)
    ''', (date, category, amount, description))
    conn.commit()

3. View Expenses:

def view_expenses():
    cursor.execute('SELECT * FROM expenses')
    rows = cursor.fetchall()
    for row in rows:
        print(row)

4. Update and Delete Expenses:

def update_expense(expense_id, date=None, category=None, amount=None, description=None):
    query = 'UPDATE expenses SET'
    params = []
    if date:
        query += ' date = ?,'
        params.append(date)
    if category:
        query += ' category = ?,'
        params.append(category)
    if amount:
        query += ' amount = ?,'
        params.append(amount)
    if description:
        query += ' description = ?,'
        params.append(description)
    query = query.rstrip(',') + ' WHERE id = ?'
    params.append(expense_id)
    cursor.execute(query, params)
    conn.commit()

def delete_expense(expense_id):
    cursor.execute('DELETE FROM expenses WHERE id = ?', (expense_id,))
    conn.commit()

Verification and Testing:

• Add several expense records and verify using view_expenses.
• Test update and delete functionalities.

Project 2: Library Management System

Objective:
Develop a system to manage books in a library, including adding new books, updating information, viewing book details, and removing books.

Setup and Requirements:

• Database: SQLite
• Technology: Python

Implementation Steps:

1. Create Database and Table:

conn = sqlite3.connect('library.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        author TEXT NOT NULL,
        published_date TEXT,
        isbn TEXT UNIQUE
    )
''')
conn.commit()

2. Add a Book:

def add_book(title, author, published_date, isbn):
    cursor.execute('''
        INSERT INTO books (title, author, published_date, isbn)
        VALUES (?, ?, ?, ?)
    ''', (title, author, published_date, isbn))
    conn.commit()

3. View Books:

def view_books():
    cursor.execute('SELECT * FROM books')
    rows = cursor.fetchall()
    for row in rows:
        print(row)

4. Update and Delete Books:

def update_book(book_id, title=None, author=None, published_date=None, isbn=None):
    query = 'UPDATE books SET'
    params = []
    if title:
        query += ' title = ?,'
        params.append(title)
    if author:
        query += ' author = ?,'
        params.append(author)
    if published_date:
        query += ' published_date = ?,'
        params.append(published_date)
    if isbn:
        query += ' isbn = ?,'
        params.append(isbn)
    query = query.rstrip(',') + ' WHERE id = ?'
    params.append(book_id)
    cursor.execute(query, params)
    conn.commit()

def delete_book(book_id):
    cursor.execute('DELETE FROM books WHERE id = ?', (book_id,))
    conn.commit()

Verification and Testing:

• Test adding, viewing, updating, and deleting books.

Project 3: Task Management Application

Objective:
Create a task management system where users can add, update, view, and delete tasks.

Setup and Requirements:

• Database: SQLite
• Technology: Python

Implementation Steps:

1. Create Database and Table:

conn = sqlite3.connect('tasks.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        task_name TEXT NOT NULL,
        due_date TEXT,
        status TEXT
    )
''')
conn.commit()

2. Add a Task:

def add_task(task_name, due_date, status):
    cursor.execute('''
        INSERT INTO tasks (task_name, due_date, status)
        VALUES (?, ?, ?)
    ''', (task_name, due_date, status))
    conn.commit()

3. View Tasks:

def view_tasks():
    cursor.execute('SELECT * FROM tasks')
    rows = cursor.fetchall()
    for row in rows:
        print(row)

4. Update and Delete Tasks:

def update_task(task_id, task_name=None, due_date=None, status=None):
    query = 'UPDATE tasks SET'
    params = []
    if task_name:
        query += ' task_name = ?,'
        params.append(task_name)
    if due_date:
        query += ' due_date = ?,'
        params.append(due_date)
    if status:
        query += ' status = ?,'
        params.append(status)
    query = query.rstrip(',') + ' WHERE id = ?'
    params.append(task_id)
    cursor.execute(query, params)
    conn.commit()

def delete_task(task_id):
    cursor.execute('DELETE FROM tasks WHERE id = ?', (task_id,))
    conn.commit()

Verification and Testing:

• Test all functionalities including adding, viewing, updating, and deleting tasks.

Project 4: Contact Management System

Objective:
Develop a system to manage contacts, allowing users to add, view, update, and delete contact information.

Setup and Requirements:

• Database: SQLite
• Technology: Python

Implementation Steps:

1. Create Database and Table:

import sqlite3

# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('contacts.db')
cursor = conn.cursor()

# Create table for contacts
cursor.execute('''
    CREATE TABLE IF NOT EXISTS contacts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT NOT NULL,
        last_name TEXT NOT NULL,
        phone_number TEXT,
        email TEXT
    )
''')
conn.commit()

2. Add a Contact:

def add_contact(first_name, last_name, phone_number, email):
    cursor.execute('''
        INSERT INTO contacts (first_name, last_name, phone_number, email)
        VALUES (?, ?, ?, ?)
    ''', (first_name, last_name, phone_number, email))
    conn.commit()

3. View Contacts:

def view_contacts():
    cursor.execute('SELECT * FROM contacts')
    rows = cursor.fetchall()
    if rows:
        for row in rows:
            print(f"ID: {row[0]}, First Name: {row[1]}, Last Name: {row[2]}, Phone: {row[3]}, Email: {row[4]}")
    else:
        print("No contacts found.")

4. Update a Contact:

def update_contact(contact_id, first_name=None, last_name=None, phone_number=None, email=None):
    query = 'UPDATE contacts SET'
    params = []

    if first_name:
        query += ' first_name = ?,'
        params.append(first_name)
    if last_name:
        query += ' last_name = ?,'
        params.append(last_name)
    if phone_number:
        query += ' phone_number = ?,'
        params.append(phone_number)
    if email:
        query += ' email = ?,'
        params.append(email)

    query = query.rstrip(',') + ' WHERE id = ?'
    params.append(contact_id)

    cursor.execute(query, params)
    conn.commit()

5. Delete a Contact:

def delete_contact(contact_id):
    cursor.execute('DELETE FROM contacts WHERE id = ?', (contact_id,))
    conn.commit()

# Close the connection to the database
conn.close()

Explanation:

1. Database Setup:
   • The sqlite3.connect('contacts.db') command connects to the SQLite database file named contacts.db. If the file does not exist, it will be created.
   • The CREATE TABLE statement defines the schema for the contacts table with columns for ID, first name, last name, phone number, and email.
2. Functions:
   • add_contact(): Inserts new contact records into the database.
   • view_contacts(): Retrieves and displays all contacts from the database.
   • update_contact(): Updates existing contact information based on the contact ID.
   • delete_contact(): Deletes a contact record based on the contact ID.