Setting Up Your SQL Environment

Now that you understand what databases and SQL are, it's time to get your hands dirty! In this lesson, you'll provision the tools you need and load the University schema that powers every exercise in this course.

Learning Goals:

• Install and configure a SQL database system
• Choose and set up a SQL client/interface
• Create the university.db database and load the shared schema
• Run test queries against the seeded tables to confirm everything works

Choosing Your SQL Database

For learning purposes, we'll use SQLite - it's lightweight, requires no server setup, and stores everything in a single file. This makes it perfect for beginners and practice.

tip

While we're using SQLite for this course, the SQL you learn will work with other popular databases like MySQL, PostgreSQL, and SQL Server with minor syntax variations.

Installing SQLite

Windows

1. Download the precompiled binaries from sqlite.org
2. Get the "sqlite-tools-win32-*.zip" package
3. Extract to C:\sqlite
4. Add C:\sqlite to your PATH environment variable

macOS

# Using Homebrew
brew install sqlite

# Or download from sqlite.org and install manually

Linux

# Ubuntu/Debian
sudo apt update && sudo apt install sqlite3

# CentOS/RHEL/Fedora
sudo yum install sqlite3
# or
sudo dnf install sqlite3

Setting Up Your SQL Client

You have several options for interacting with SQLite:

Option 1: Command Line Interface (Recommended)

The SQLite CLI comes with the installation and provides direct access:

Starting SQLite CLI

sqlite3

Basic SQLite commands

.help                    -- Show available commands
.databases               -- List databases
.quit                    -- Exit SQLite

Option 2: Graphical Tools

If you prefer a visual interface, consider these free options:

• DB Browser for SQLite - Cross-platform GUI
• SQLite Studio - More advanced features
• VS Code with SQLite extensions - Great for developers

Creating Your First Database

Let's create the database file that will store the shared University schema:

Create and open the SQLite database

sqlite3 university.db

With the SQLite prompt open, load the DDL from the previous lesson. You can paste it directly or place it in a script such as create_tables.sql:

create_tables.sql

DROP TABLE IF EXISTS enrollment;
DROP TABLE IF EXISTS course;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS instructor;
DROP TABLE IF EXISTS department;

CREATE TABLE department (
  dept_id   INTEGER PRIMARY KEY,
  dept_name TEXT NOT NULL UNIQUE
);

CREATE TABLE instructor (
  instructor_id INTEGER PRIMARY KEY,
  first_name    TEXT NOT NULL,
  last_name     TEXT NOT NULL,
  email         TEXT UNIQUE,
  dept_id       INTEGER NOT NULL,
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

CREATE TABLE student (
  student_id      INTEGER PRIMARY KEY,
  first_name      TEXT NOT NULL,
  last_name       TEXT NOT NULL,
  email           TEXT UNIQUE,
  dept_id         INTEGER,
  enrollment_year INTEGER,
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

CREATE TABLE course (
  course_id     INTEGER PRIMARY KEY,
  course_code   TEXT NOT NULL UNIQUE,
  course_title  TEXT NOT NULL,
  credits       INTEGER NOT NULL DEFAULT 3,
  dept_id       INTEGER NOT NULL,
  instructor_id INTEGER,
  FOREIGN KEY (dept_id) REFERENCES department(dept_id),
  FOREIGN KEY (instructor_id) REFERENCES instructor(instructor_id)
);

CREATE TABLE enrollment (
  enroll_id  INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL,
  course_id  INTEGER NOT NULL,
  semester   TEXT NOT NULL,
  grade      TEXT,
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (course_id) REFERENCES course(course_id)
);

From the SQLite prompt, run:

Load the schema

.read create_tables.sql

Next, insert the sample data so your environment matches the examples throughout the course. Save the inserts from the schema lesson into seed_data.sql and run:

seed_data.sql excerpt

INSERT INTO department (dept_name) VALUES
  ('Computer Science'),
  ('Information Technology'),
  ('Electrical Engineering'),
  ('Business Administration'),
  ('Mechanical Engineering'),
  ('Mathematics');

INSERT INTO instructor (first_name, last_name, email, dept_id) VALUES
  ('Rakesh', 'Mehta', 'rakesh.mehta@univ.local', 1),
  ('Sonia', 'Kapoor', 'sonia.kapoor@univ.local', 1),
  ('Imran', 'Khan', 'imran.khan@univ.local', 2);

INSERT INTO student (first_name, last_name, email, dept_id, enrollment_year) VALUES
  ('Aman',  'Sharma',  'aman.sharma@students.local', 1, 2024),
  ('Priya', 'Singh',   'priya.singh@students.local', 1, 2023),
  ('Rohit', 'Verma',   'rohit.verma@students.local', 2, 2024);

Load the seed data

.read seed_data.sql

Running Your First Query

Now verify everything is wired up correctly by querying the seeded tables:

List departments

SELECT dept_id, dept_name
FROM department
ORDER BY dept_id;

dept_id  dept_name
-------  ------------------------
1        Computer Science
2        Information Technology
3        Electrical Engineering
4        Business Administration
5        Mechanical Engineering
6        Mathematics

Try a join to confirm relationships are in place:

Students and their departments

SELECT s.student_id,
       s.first_name || ' ' || s.last_name AS full_name,
       COALESCE(d.dept_name, 'Undeclared') AS department,
       s.enrollment_year
FROM student AS s
LEFT JOIN department AS d ON d.dept_id = s.dept_id
ORDER BY s.student_id;

You should see each student alongside the department referenced by the dept_id foreign key. Any future lessons that mention running a query assume this database and dataset are available.

note

The asterisk (*) in SELECT * means "all columns." While convenient for exploration, in production code you should specify exactly which columns you need for better performance and clarity.

Alternative: Online SQL Environments

If you can't install software locally, try these browser-based options:

• SQLite Online - Simple web-based SQLite
• DB Fiddle - Supports multiple database systems
• SQLite Playground - No installation required

Common Pitfalls

• File permissions: Ensure you have write access to the directory where you create SQLite database files
• Path issues: If sqlite3 command isn't found, verify your PATH environment variable is set correctly
• Database location: Remember that SQLite creates database files in your current working directory
• Forgetting to commit: Unlike some databases, SQLite automatically commits most changes
• Case sensitivity: SQL keywords are case-insensitive, but string comparisons may be case-sensitive depending on configuration

Summary

You now have a working SQL environment! You've:

• Installed SQLite database system
• Learned to use the SQLite CLI
• Created your first database and table
• Inserted sample data and run queries
• Explored alternative interface options

Your environment is ready for the real SQL learning to begin in the next lesson!

Quiz

Show quiz

1. What makes SQLite particularly good for beginners?

   • A) It requires complex server configuration
   • B) It stores data in a single file with no server needed
   • C) It only works on Windows systems
   • D) It has the most advanced features

2. Which symbol is used in SELECT statements to return all columns?

   • A) %
   • B) &
   • C) *
   • D) @

3. True or False: You must manually commit every change in SQLite.

   • A) True
   • B) False

4. What command shows available databases in SQLite CLI?

   • A) SHOW DATABASES
   • B) .databases
   • C) LIST DB
   • D) DISPLAY DATABASES

---

Answers:

1. B - SQLite stores data in a single file with no server needed, making it beginner-friendly
2. C - The asterisk (*) returns all columns in a SELECT statement
3. B - False - SQLite automatically commits most changes
4. B - The .databases command shows available databases in SQLite CLI