Introduction to Database Systems and SQL Fundamentals

TL;DR

Database systems help you store, organize, and retrieve information efficiently. SQL is the standard language you use to communicate with these databases to manage your data. Understanding these basics is crucial for anyone working with data.

1. The Mental Model

Imagine a super-organized digital filing cabinet where you can store all sorts of information. A database system is like that cabinet, and SQL is the specific language you use to ask for files, put new files in, or even reorganize the cabinet.

2. The Core Material

At its heart, a database is a structured collection of data. Most common databases, especially those you'll encounter first, are Relational Database Management Systems (RDBMS). This means data is stored in tables, which are like spreadsheets with rows and columns.

• Tables: A collection of related data organized into rows and columns. Each table represents an entity (e.g., Students, Courses).
• Rows (Records/Tuples): A single entry in a table, representing one instance of the entity (e.g., one specific student).
• Columns (Fields/Attributes): Define the type of data stored in each row for a particular aspect (e.g., student_ID, student_name).
• Primary Key: A column (or set of columns) that uniquely identifies each row in a table. No two rows can have the same primary key value, and it can't be empty (NULL).
• Foreign Key: A column (or set of columns) in one table that refers to the primary key in another table. It establishes a link between tables.

SQL Fundamentals

Photo by Myburgh Roux on Pexels

SQL (Structured Query Language) is the language for interacting with RDBMS. It's used for:

• Data Definition Language (DDL): For defining the database structure (creating, modifying, deleting tables).
• Data Manipulation Language (DML): For managing data within the tables (inserting, updating, deleting, retrieving data).

Here are the most common SQL commands:

CREATE TABLE (DDL)

Photo by Ron Lach on Pexels

This command defines a new table, specifying its columns and their data types.

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE
);

In this example:
* INT: Integer (whole number).
* VARCHAR(50): Variable-length string up to 50 characters.
* PRIMARY KEY: Ensures student_id is unique and not NULL.
* UNIQUE: Ensures email values are unique across all students.
* DATE: Stores dates.

INSERT INTO (DML)

Photo by Dale Kirkwood on Pexels

This command adds new rows (records) into a table.

INSERT INTO Students (student_id, first_name, last_name, email, enrollment_date)
VALUES (1, 'Alice', 'Smith', 'alice.smith@example.com', '2023-09-01');

INSERT INTO Students VALUES (2, 'Bob', 'Johnson', 'bob.j@example.com', '2023-09-05');

SELECT (DML)

Photo by ROMBO on Pexels

This is the most frequently used command, allowing you to retrieve data from one or more tables.

-- Retrieve all columns for all students
SELECT * FROM Students;

-- Retrieve specific columns for all students
SELECT first_name, email FROM Students;

-- Retrieve students named 'Alice'
SELECT * FROM Students WHERE first_name = 'Alice';

-- Retrieve students enrolled after a specific date
SELECT first_name, last_name FROM Students WHERE enrollment_date > '2023-09-03';

UPDATE (DML)

This command modifies existing data in a table. It's crucial to use a WHERE clause to specify which rows to update; otherwise, you'll update all rows!

-- Update Alice's email
UPDATE Students
SET email = 'alice.s@newmail.com'
WHERE student_id = 1;

DELETE FROM (DML)

This command removes rows from a table. Again, use a WHERE clause carefully!

-- Delete Bob from the Students table
DELETE FROM Students
WHERE student_id = 2;

-- DANGER: Deletes all records from the table if no WHERE clause!
-- DELETE FROM Students;

Database Schema - Relationships

Databases aren't just single tables; they often link multiple tables using primary and foreign keys. This forms a schema.

erDiagram
    Students {
        INT student_id PK
        VARCHAR first_name
        VARCHAR last_name
        VARCHAR email UK
        DATE enrollment_date
    }

    Courses {
        INT course_id PK
        VARCHAR course_name
        VARCHAR department
    }

    Enrollments {
        INT enrollment_id PK
        INT student_id FK
        INT course_id FK
        DATE registration_date
    }

    Students ||--o{ Enrollments : "enrolls in"
    Courses ||--o{ Enrollments : "has"

This diagram shows:
* Students table has a student_id as its primary key.
* Courses table has a course_id as its primary key.
* Enrollments table links Students and Courses using student_id and course_id as foreign keys, meaning a student can enroll in multiple courses, and a course can have multiple students.

3. Worked Example

Let's say you have a table Products and you need to:
1. Create the Products table.
2. Add two products.
3. Update the price of one product.
4. Retrieve all products.

-- 1. Create the Products table
CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2), -- Stores numbers with 2 decimal places
    stock_quantity INT
);

-- 2. Add two products
INSERT INTO Products (product_id, product_name, price, stock_quantity)
VALUES (101, 'Laptop', 1200.00, 50);

INSERT INTO Products (product_id, product_name, price, stock_quantity)
VALUES (102, 'Mouse', 25.50, 200);

-- 3. Update the price of the 'Laptop'
UPDATE Products
SET price = 1150.00
WHERE product_id = 101; -- Or WHERE product_name = 'Laptop';

-- 4. Retrieve all products to see the changes
SELECT * FROM Products;

After running this, the output for SELECT * FROM Products; would show:

product_id | product_name | price    | stock_quantity
-----------|--------------|----------|---------------
101        | Laptop       | 1150.00  | 50
102        | Mouse        | 25.50    | 200

4. Key Takeaways

• Databases organize data into tables with rows and columns, like structured spreadsheets.
• SQL is the language used to interact with these databases for defining and manipulating data.
• CREATE TABLE defines the structure of your data, specifying columns and their types.
• INSERT INTO adds new records (rows) into a table.
• SELECT is used to retrieve data, and WHERE filters results based on conditions.
• UPDATE changes existing data, and DELETE FROM removes records.
• Primary and foreign keys are essential for linking tables and maintaining data integrity.

Common Mistakes to Avoid

• Forgetting the WHERE clause in UPDATE or DELETE statements, potentially affecting all rows.
• Not understanding data types, leading to errors or inefficient storage.
• Using SELECT * in production code when you only need specific columns, which can be inefficient.
• Misunderstanding primary vs. foreign keys, causing issues with data relationships.
• Not quoting string values in SQL (e.g., 'Alice' instead of Alice).

5. Now Try It

Create a new table called Employees with columns for employee_id (primary key), first_name, last_name, hire_date, and salary. Then, insert at least three employees into it. Finally, write a SELECT query to retrieve the first_name and salary of all employees who earn more than 50000.

Success looks like: You have a CREATE TABLE statement, three INSERT INTO statements, and one SELECT statement that correctly shows only the names and salaries of the high-earning employees.