SQL Database Fundamentals: A Complete Guide

SQL (Structured Query Language) is essential for working with databases. Learn the fundamentals of database design and querying.

What is a Database?

A database is an organized collection of data. Relational databases store data in tables with rows and columns.

Creating Your First Table

sql
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE,
    gpa DECIMAL(3, 2)
);

CRUD Operations

Create

sql
INSERT INTO students (name, email, enrollment_date, gpa)
VALUES ('John Doe', 'john@example.com', '2024-01-15', 3.75);

Read

sql
SELECT name, email, gpa
FROM students
WHERE gpa > 3.5
ORDER BY gpa DESC;

Update

sql
UPDATE students
SET gpa = 3.85
WHERE id = 1;

Delete

sql
DELETE FROM students
WHERE id = 1;

Joins

Joins combine data from multiple tables:

sql
SELECT students.name, courses.course_name
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id;

Normalization

Normalization reduces data redundancy:

• 1NF: Eliminate repeating groups
• 2NF: Remove partial dependencies
• 3NF: Remove transitive dependencies