Skip to content

Week 3 - SQLite3 I

SQLite3 1

SQLite3 Assignment 1: Basic Database Operations

  • Objective: Introduce SELECT statements, WHERE clauses, and basic functions like COUNT, AVG, MAX, MIN.
  • Assignment: Create a database with a simple table (e.g., Students with columns ID, Name, Major) and have students write queries to retrieve specific information, such as all students in a particular major, the total number of students, the average ID, etc.

Preliminary Steps: Setting Up Your Environment

Before you begin the assignment, you will need to set up your environment by downloading and installing SQLite3 and DB Browser for SQLite. These tools are essential for creating, managing, and interacting with your SQLite database.
  1. Download SQLite3:
    • Visit the SQLite3 download page: SQLite Download Page.
    • Choose the appropriate version for your operating system (Windows/Linux/Mac).
    • Follow the installation instructions provided on the website.
  2. Download DB Browser for SQLite:
    • Visit the DB Browser for SQLite download page: DB Browser Download Page.
    • Select the version compatible with your operating system.
    • Install the software following the instructions on the site.
Once you have successfully installed both SQLite3 and DB Browser for SQLite, you are ready to begin the assignment.

Background
Understanding the fundamental operations of a database is crucial for any database professional. This assignment focuses on the core aspects of SQLite3, providing hands-on experience with database creation, data manipulation, and simple querying.

Objective

Introduce SELECT statements, WHERE clauses, and basic functions like COUNT, AVG, MAX, MIN. This assignment aims to create and manipulate a basic database using SQLite3, demonstrating proficiency in fundamental SQL operations and understanding of primary keys and data types.

Tasks

  1. Database Creation and Design:
    • Create a new SQLite3 database named your_last_name_university.
    • Design and create the following two tables:
      • students: Should include fields such as student_id (primary key), namemajor, and enrollment_year.
      • courses: Should include fields such as course_id (primary key), course_nameinstructor, and credits.
  2. Data Manipulation (CRUD Operations):
    • Insert at least 5 records into each table.
    • Update the Major of a student.
    • Delete a course from the Courses table.
    • Read and display all records from both tables.
  3. Simple Query Writing:
    • Write a query to list all students enrolled after a certain year.
    • Write a query to find courses with more than 3 credits.
    • Demonstrate the use of simple WHERE clauses.


Deliverables

  • Upload the database named your_last_name_university.db file for the database you created. Make sure you “write changes” and close the DB Browser before you upload to GeorgiaView.
  • Upload the SQL script used for creating tables, inserting, updating, and deleting records, name it your_last_name_part1.sql, there is a save feature in DB Browser. This file should end in .sql.
  • Upload the SQL queries written for task 3, name it your_last_name_part2.sql
  • A brief reflection on the challenges faced and lessons learned during the assignment, typed into the comment section.

Evaluation Criteria

  • Correctness and efficiency of the SQL scripts.
  • Clarity and rationality in database design.
  • Quality and depth of the analysis in the report.
This assignment is designed to solidify your foundational skills in database management, preparing you for more complex tasks in subsequent modules.