Assignment

---

1️⃣ Introduction to Relational Databases (4 Assignments)

Assignment 1: Connecting to a SQLite Database

🔹 Task: Establish a connection to an SQLite database using SQLAlchemy. 🔹 Example Snippet:

from sqlalchemy import create_engine

engine = create_engine("sqlite:///test.db")
print("Database connected successfully!")

---

Assignment 2: Creating a Database Table with SQLAlchemy

🔹 Task: Define a users table using SQLAlchemy ORM. 🔹 Example Snippet:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

engine = create_engine("sqlite:///test.db")
Base.metadata.create_all(engine)
print("Table created successfully!")

---

Assignment 3: Inspecting Database Tables

🔹 Task: List all tables in a database. 🔹 Example Snippet:

from sqlalchemy import inspect

engine = create_engine("sqlite:///test.db")
inspector = inspect(engine)
print(inspector.get_table_names())

---

Assignment 4: Checking Database Connection

🔹 Task: Verify the database connection and execute a raw SQL query. 🔹 Example Snippet:

with engine.connect() as connection:
    result = connection.execute("SELECT sqlite_version();")
    print(result.fetchone())  # Output: SQLite version

---

2️⃣ SQLAlchemy ORM (Object Relational Mapping) (6 Assignments)

Assignment 5: Defining a New Table with More Fields

🔹 Task: Create a products table with id, name, price, and quantity. 🔹 Example Snippet:

class Product(Base):
    __tablename__ = "products"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    price = Column(Integer)
    quantity = Column(Integer)

Base.metadata.create_all(engine)

---

Assignment 6: Creating a Session to Interact with the Database

🔹 Task: Set up a session for database operations. 🔹 Example Snippet:

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()
print("Session created successfully!")

---

Assignment 7: Adding a New User to the Database

🔹 Task: Insert a new user into the users table. 🔹 Example Snippet:

new_user = User(name="John Doe", email="[e-mail]")
session.add(new_user)
session.commit()
print("User added successfully!")

---

Assignment 8: Querying All Users from the Database

🔹 Task: Retrieve all users from the users table. 🔹 Example Snippet:

users = session.query(User).all()
for user in users:
    print(user.name, user.email)

---

Assignment 9: Filtering Data in SQLAlchemy

🔹 Task: Find users with a specific email. 🔹 Example Snippet:

user = session.query(User).filter_by(email="[e-mail]").first()
print(user.name)

---

Assignment 10: Ordering Query Results

🔹 Task: Retrieve all users in descending order by id. 🔹 Example Snippet:

users = session.query(User).order_by(User.id.desc()).all()
for user in users:
    print(user.name)

---

3️⃣ CRUD Operations with SQLite/MySQL/PostgreSQL (6 Assignments)

Assignment 11: Updating a User's Email

🔹 Task: Update the email of a user. 🔹 Example Snippet:

user = session.query(User).filter_by(name="John Doe").first()
user.email = "[e-mail]"
session.commit()
print("User updated successfully!")

---

Assignment 12: Deleting a User from the Database

🔹 Task: Remove a user with a specific email. 🔹 Example Snippet:

user = session.query(User).filter_by(email="[e-mail]").first()
session.delete(user)
session.commit()
print("User deleted successfully!")

---

Assignment 13: Counting the Number of Users

🔹 Task: Count the total number of users in the database. 🔹 Example Snippet:

count = session.query(User).count()
print("Total users:", count)

---

Assignment 14: Using LIKE Operator in Queries

🔹 Task: Find users whose names start with "J". 🔹 Example Snippet:

users = session.query(User).filter(User.name.like("J%")).all()
for user in users:
    print(user.name)

---

Assignment 15: Querying Users with a Specific Email Domain

🔹 Task: Find all users with emails ending in @example.com. 🔹 Example Snippet:

users = session.query(User).filter(User.email.like("[e-mail]")).all()
for user in users:
    print(user.name)

---

Assignment 16: Using distinct() to Get Unique Values

🔹 Task: Retrieve all unique email domains. 🔹 Example Snippet:

domains = session.query(User.email).distinct().all()
print(domains)

---

4️⃣ Faker for Generating Fake Test Data (4 Assignments)

Assignment 17: Installing and Using Faker

🔹 Task: Install Faker and generate a fake name. 🔹 Example Snippet:

from faker import Faker

fake = Faker()
print(fake.name())  # Output: Random name

---

Assignment 18: Generating Fake Emails and Addresses

🔹 Task: Generate fake email and address. 🔹 Example Snippet:

print(fake.email())  # Output: Random email
print(fake.address())  # Output: Random address

---

Assignment 19: Inserting Fake Users into the Database

🔹 Task: Insert 5 fake users into the database. 🔹 Example Snippet:

for _ in range(5):
    fake_user = User(name=fake.name(), email=fake.email())
    session.add(fake_user)

session.commit()
print("Fake users added successfully!")

---

Assignment 20: Populating a Products Table with Fake Data

🔹 Task: Generate fake product names and prices. 🔹 Example Snippet:

for _ in range(5):
    fake_product = Product(name=fake.word(), price=fake.random_int(min=10, max=500), quantity=fake.random_int(min=1, max=50))
    session.add(fake_product)

session.commit()
print("Fake products added successfully!")

---