Lab 01: Environment Setup & First SQL

Complete walkthrough of SQLite CLI, DB Browser, Python sqlite3, table creation, and constraint enforcement

Lab Source Document

Original Lab Handout: Lab 1 (SQLite Toolchain & First SQL) · Module I Week 1
Download lab01.pdf

1. Objectives & Setup Checklist

By completing this lab, you will establish a fully functional database development environment and test fundamental DDL and DML operations.

Toolchain Verification Commands:

# 1. Verify SQLite 3 CLI
sqlite3 --version

# 2. Test interactive shell
sqlite3 lab1.db
sqlite> .databases
sqlite> .quit

# 3. Verify Python 3 DB-API
python3 -c "import sqlite3; print('sqlite3 module OK', sqlite3.sqlite_version)"

# 4. Check PostgreSQL CLI availability (required by Lab 4)
psql --version

2. Table Creation & Constraints (Part 1)

In SQLite, foreign keys are disabled by default. Always begin your session with:

PRAGMA foreign_keys = ON;

Table 1: `Students`

CREATE TABLE Students (
    student_id  INTEGER PRIMARY KEY,
    first_name  TEXT NOT NULL,
    last_name   TEXT NOT NULL,
    discipline  TEXT
);

Table 2: `Faculty`

CREATE TABLE Faculty (
    faculty_id  INTEGER PRIMARY KEY,
    first_name  TEXT NOT NULL,
    last_name   TEXT NOT NULL,
    department  TEXT
);

3. Testing with DML & Constraint Violations

Task 1: Insert Valid Records

-- Insert 3 students
INSERT INTO Students (student_id, first_name, last_name, discipline) VALUES 
    (1, 'Aarav', 'Sharma', 'CSE'),
    (2, 'Diya', 'Patel', 'Physics'),
    (3, 'Rohan', 'Verma', 'Mechanical');

-- Insert 3 faculty members
INSERT INTO Faculty (faculty_id, first_name, last_name, department) VALUES 
    (101, 'Amit', 'Dhar', 'CSE'),
    (102, 'Priya', 'Nair', 'Physics'),
    (103, 'Suresh', 'Rao', 'Mathematics');

Task 2: Verify Contents

SELECT * FROM Students;
SELECT * FROM Faculty;

Task 3: Deliberately Trigger Constraint Failures

To ensure constraints are operating properly, execute statements designed to fail:

-- 1. Test NOT NULL constraint (Should FAIL: NOT NULL constraint failed: Students.first_name)
INSERT INTO Students (student_id, first_name, last_name, discipline) 
VALUES (4, NULL, 'Kapoor', 'EE');

-- 2. Test PRIMARY KEY uniqueness (Should FAIL: UNIQUE constraint failed: Students.student_id)
INSERT INTO Students (student_id, first_name, last_name, discipline) 
VALUES (1, 'Kunal', 'Singh', 'CSE');

Task 4: Test Conditional Deletion

-- Remove student in Physics
DELETE FROM Students WHERE discipline = 'Physics';

-- Verify removal
SELECT * FROM Students WHERE discipline = 'Physics';
-- Returns 0 rows.

Lab 01: Environment Setup & First SQL

1. Objectives & Setup Checklist

Toolchain Verification Commands:

2. Table Creation & Constraints (Part 1)

Table 1: `Students`

Table 2: `Faculty`

3. Testing with DML & Constraint Violations

Task 1: Insert Valid Records

Task 2: Verify Contents

Task 3: Deliberately Trigger Constraint Failures

Task 4: Test Conditional Deletion

4. Lab Checkpoints & Key Takeaways

On this page

Lab 01: Environment Setup & First SQL

Checkpoint: When is 'INTEGER PRIMARY KEY' special in SQLite?

Checkpoint: What is the purpose of the .schema meta-command?

On this page