Introduction to Databases and SQL

Welcome to your first lesson in SQL! In this course we will explore database concepts by working with a fictional university. You will create departments, manage instructors, track students, publish courses, and record enrollments, all inside a relational database engine. Every query you write in the upcoming modules relies on the shared University schema you assembled in the previous lesson.

Learning Goals

• Understand what databases are and where SQL fits in
• Explore core relational concepts through the University schema
• Review the sample tables and relationships you will query throughout the course
• Run your first SQL statements against that shared dataset

What is a Database?

A database is an organized collection of data that can be stored, searched, and updated efficiently. Imagine running a university on spreadsheets: keeping faculty rosters, course catalogs, and student enrollment lists in sync quickly becomes messy. A relational database stores those entities in tables and enforces relationships between them so the information stays consistent.

Sample student records

Student ID

▲

First Name

▲

Last Name

▲

Department

▲

Enrollment Year

▲

1

Aman

Sharma

Computer Science

2024

3

Rohit

Verma

Information Technology

2024

5

Sana

Ali

Computer Science

2025

8

Tarun

Iyer

Electrical Engineering

2024

9

Nisha

Desai

Mathematics

2025

Why Use Databases?

Relational databases help you:

• Store large volumes of information across multiple related entities
• Enforce rules like “each course belongs to exactly one department”
• Allow multiple stakeholders (registrar, instructors, students) to access the same truth
• Secure sensitive records, such as grades, while providing reliable backups
• Run complex analytical queries without duplicating data in spreadsheets

tip

Relational databases provide the ACID properties (Atomicity, Consistency, Isolation, Durability). These guarantees protect your student and course data even if multiple transactions run simultaneously or an unexpected failure occurs.

Types of Databases

Relational Databases (SQL Databases)

Relational databases organize information into tables with rows (records) and columns (attributes). Relationships between tables are maintained through keys. Popular relational engines include PostgreSQL, MySQL, SQL Server, and SQLite.

Non-relational Databases (NoSQL)

NoSQL systems store information using alternative formats such as JSON documents, key-value pairs, or graphs. They shine in scenarios that require flexible schemas or massive horizontal scaling. While this course focuses on SQL, it is helpful to recognize where other models fit.

Type	Description	Example Database	Official Documentation
Document Database	Stores data as JSON-like documents	MongoDB	MongoDB Docs
Key-Value Store	Uses key-value pairs for extremely fast lookups	Redis	Redis Documentation
Column-Family Store	Organizes data by columns for analytics workloads	Apache Cassandra	Cassandra Documentation
Graph Database	Focuses on nodes and relationships	Neo4j	Neo4j Documentation

Meet the University Schema

We will revisit the same five tables in every lesson:

• department – Stores dept_id and dept_name. Departments own instructors and courses.
• instructor – Contains faculty names, contact info, and a required dept_id foreign key.
• student – Lists students, when they joined the university, and an optional dept_id for their major.
• course – Represents each course offering, including credits, owning department, and instructor.
• enrollment – Bridge table linking students to the courses they take each semester and optionally storing a grade.

Creating the schema in SQLite looks like this:

create_tables.sql

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)
);

Relational Building Blocks

Relational databases rely on a few core constructs:

• Tables define the shape of each entity. For example, the course table captures code, title, credit value, and the corresponding department.
• Rows hold individual records. Each row in enrollment records a student taking a specific course in a semester.
• Primary keys uniquely identify each row (student.student_id, course.course_id).
• Foreign keys connect rows across tables (enrollment.student_id ↔ student.student_id).
• Constraints enforce data quality, such as making dept_name unique or limiting credits to positive numbers.

Your First SQL Query

SQL (Structured Query Language) is the standard way to interact with relational databases. A basic query requests specific columns from a table:

first_query.sql

-- Inspect the course catalog
SELECT course_code, course_title, credits
FROM course;

-- Retrieve students in the Computer Science department
SELECT s.student_id,
       s.first_name,
       s.last_name,
       d.dept_name
FROM student AS s
JOIN department AS d ON d.dept_id = s.dept_id
WHERE d.dept_name = 'Computer Science';

SQL keywords such as SELECT and FROM declare what you want to read, while clauses like WHERE and JOIN refine the results.

Sample Data and Results

The schema lesson includes seed data so you can experiment right away:

seed_data.sql

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

INSERT INTO course (course_code, course_title, credits, dept_id, instructor_id) VALUES
  ('CS101', 'Introduction to Programming', 4, 1, 1),
  ('IT105', 'Database Systems', 3, 2, 3),
  ('EE120', 'Circuits and Systems', 3, 3, 5);

INSERT INTO enrollment (student_id, course_id, semester, grade) VALUES
  (1, 1, 'Spring 2025', 'A'),
  (3, 2, 'Spring 2025', 'B+'),
  (5, 1, 'Spring 2025', NULL);

Running the course catalog query returns:

course_code | course_title               | credits
----------- | -------------------------- | -------
CS101       | Introduction to Programming| 4
IT105       | Database Systems           | 3
EE120       | Circuits and Systems       | 3

Why Learn SQL?

SQL continues to be one of the most requested skills across software engineering, analytics, and data science roles because it:

• Works similarly across major relational databases
• Enables complex data questions to be expressed clearly
• Plays nicely with reporting tools, BI dashboards, and application backends
• Ensures you can reason about data quality, relationships, and performance
• Provides a foundation for advanced analytics and data engineering topics

Common Pitfalls

• Forgetting relationships: Queries that ignore JOINs can return duplicate rows or miss needed context.
• Relying on SELECT *: Fetch only the columns you need to avoid surprises when schemas evolve.
• Ignoring NULL semantics: Always use IS NULL or IS NOT NULL when filtering optional columns like grade.
• Mixing up identifiers: Pay attention to whether you need dept_id (a numeric key) or dept_name (the friendly label).
• Skipping transactions: When inserting lots of seed data, wrap the statements in a transaction for safety and speed.

Summary

In this lesson you learned:

• How relational databases store information in tables and why that model suits the University scenario
• The key tables, columns, and relationships that form our shared schema
• How to run a basic SQL query against familiar tables like student and course
• Where the provided DDL and seed data live so you can recreate the environment anytime

With the groundwork in place, move on to the next lesson to ensure your SQL environment is configured to run these scripts smoothly.

Quiz

Show quiz

1. Which table links students to the classes they take each semester?
   A) department
   B) course
   C) enrollment
   D) instructor

2. What does the dept_id column in the student table represent?
   A) The student's advisor
   B) The department the student belongs to
   C) The course the student is currently taking
   D) The student's major GPA

3. Which SQL keyword lists the columns you want to retrieve?
   A) WHERE
   B) FROM
   C) SELECT
   D) JOIN

4. Why should you avoid using = NULL in your queries?
   A) It runs too slowly
   B) SQL converts it to zero
   C) Comparisons with NULL always return unknown, so the filter fails
   D) It only works in document databases

5. Which property of relational databases guarantees that a group of statements either all succeed or all fail together?
   A) Durability
   B) Consistency
   C) Atomicity
   D) Isolation

---

Answers

1. C) enrollment
2. B) The department the student belongs to
3. C) SELECT
4. C) Comparisons with NULL always return unknown
5. C) Atomicity