VGU

Practical Lab Assignments

SQLite & Flask

Two hands-on assignments covering database design, SQL queries, and a full CRUD web app with Flask.

Assignment 1 Assignment 2

Stack

SQLite · Python · Flask

Focus

CRUD · SQL · Templates

Submit

Code · DB · Screenshots

Your assignments

Switch between the two briefs. Complete both for full credit.

Assignment 1 Database · SQL · Relationships

SQLite Database Management

Topic: Student Management Database

Objective

Create and manage a small Student Management Database using SQLite. Demonstrate understanding of databases, tables, relationships, SQL queries, and CRUD operations.

Requirements

Create an SQLite database named student_management.db with these tables:

Students

  • student_id PK
  • name
  • email
  • phone
  • course_id FK

Courses

  • course_id PK
  • course_name
  • duration

Marks

  • mark_id PK
  • student_id FK
  • subject
  • marks

Tasks

  1. 1

    Create all three tables using SQL

    Define appropriate Primary Keys and Foreign Keys.

  2. 2

    Insert sample data

    At least 5 students, 3 courses, and 10 marks records.

  3. 3

    Write SQL queries to:

    • · Display all students
    • · Display students belonging to a particular course
    • · Find students who scored more than 75 marks
    • · Calculate the average marks of each student
    • · Find the student with the highest marks
    • · Display student name, course name, subject, and marks using JOIN
  4. 4

    Perform CRUD operations

    Add student Update student Delete student Search student
  5. 5

    Demonstrate SQL concepts

    INNER JOIN GROUP BY ORDER BY WHERE AVG() / MAX()

Submission checklist

  • ✓ SQLite database file
  • ✓ SQL file with all queries
  • ✓ Screenshots of important results
  • ✓ Short explanation of DB structure
Assignment 2 Flask · Web · CRUD

Student Management Web App

Topic: Flask + SQLite application

Objective

Develop a basic Student Management Web Application using Python Flask and SQLite. Combine backend development, database operations, HTML templates, forms, routing, and CRUD.

Required project structure

student_app/
│
├── app.py
├── database.db
├── templates/
│   ├── index.html
│   ├── add_student.html
│   ├── edit_student.html
│   └── student_details.html
│
└── static/
    └── style.css

Student fields

Student ID Name Email Phone Course Semester Marks

Required features

1. Home page

Table of all students with ID, Name, Course, Semester, Marks, and Actions (View · Edit · Delete).

2. Add student

Form → validate → insert into SQLite → redirect to list.

3. View student

Dedicated page with complete student information.

4. Edit student

Update existing student records via a form.

5. Delete student

Delete with confirmation before removing the record.

6. Search

Search by Name, Course, or Student ID.

7. Result calculation

Use Flask/Python to compute Percentage and Grade, shown on the student details page.

Flask concepts to demonstrate

Flask install & app creation Routes GET / POST request redirect url_for Jinja2 Template inheritance Forms SQLite connection CRUD Error handling

Bonus features

Implement one or more for extra credit:

  • ★ Pagination
  • ★ Course-wise student count
  • ★ Highest-scoring student
  • ★ Average marks
  • ★ Login page
  • ★ Flash messages
  • ★ REST API: GET /api/students & GET /api/students/<id>

Submission checklist

  • ✓ Complete Flask project
  • ✓ SQLite database
  • ✓ Screenshots of the app
  • ✓ Source code + architecture note

Viva preparation

Review these questions before your viva. Expand each set to practice.

Assignment 1 — SQLite viva (7 questions)
  1. 1. What is SQLite?
  2. 2. What is a Primary Key?
  3. 3. What is a Foreign Key?
  4. 4. Why do we use JOIN?
  5. 5. What is the difference between WHERE and HAVING?
  6. 6. What is normalization?
  7. 7. What is the difference between DELETE and DROP?
Assignment 2 — Flask viva (12 questions)
  1. 1. What is Flask?
  2. 2. What is a route in Flask?
  3. 3. What is the difference between GET and POST?
  4. 4. What is Jinja2?
  5. 5. What is request.form?
  6. 6. Why do we use redirect()?
  7. 7. How does Flask connect to SQLite?
  8. 8. What is CRUD?
  9. 9. What is the purpose of the templates folder?
  10. 10. What is the purpose of the static folder?
  11. 11. How can Flask return JSON?
  12. 12. What is the difference between a Flask application and a Flask route?