Lesson 2 • Beginner
Database Basics & Tables
Create databases, design tables with the right data types, and insert your first rows of data.
🎯 What You'll Learn
- Create databases and tables with CREATE statements
- Choose the right data types for each column
- Use constraints: PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT
- Insert data and modify table structure with ALTER
Database Structure
A relational database organizes data into tables. Each table has columns (the attributes) and rows (the records). Think of it as a well-organized spreadsheet that can handle millions of rows efficiently.
🏢 Real-World Analogy
A database is like an office building. Each floor (table) has rooms (rows), and each room has specific features (columns) — room number, size, occupant name. The building manager (SQL) can find any room instantly.
Create a Database & Table
Build your first database and student table from scratch
-- Create a new database
CREATE DATABASE school;
-- Switch to the database
USE school;
-- Create your first table
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT,
grade DECIMAL(3,1),
enrollment_date DATE
);Common Data Types
| Type | Use For | Example |
|---|---|---|
| INT | Whole numbers | age, quantity, id |
| VARCHAR(n) | Variable text up to n chars | name, email |
| DECIMAL(p,s) | Precise decimals | price, salary |
| DATE | Date values | 2024-03-15 |
| BOOLEAN | True/False | is_active |
| TEXT | Long text | description, bio |
⚠️ Common Mistake
Using VARCHAR(255) for everything. Choose the right type — use INT for numbers, DATE for dates. Wrong types waste storage and cause bugs.
Table Constraints
Constraints are rules that enforce data quality:
PRIMARY KEY
Uniquely identifies each row. Every table should have one. No duplicates, no NULLs.
NOT NULL
Column must always have a value — can't be left empty.
UNIQUE
No two rows can have the same value in this column (e.g., email addresses).
DEFAULT
Automatically fills in a value if none is provided.
💡 Pro Tip
Use AUTO_INCREMENT (MySQL) or SERIAL (PostgreSQL) for primary keys. The database automatically generates unique IDs for you.
Constraints in Action
Create a table with PRIMARY KEY, NOT NULL, UNIQUE, and DEFAULT constraints
-- Create a table with constraints
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
department VARCHAR(50) DEFAULT 'General',
salary DECIMAL(10,2),
hire_date DATE DEFAULT CURRENT_DATE
);
-- View the table structure
DESCRIBE employees;Insert & Modify Data
Add rows to your table and alter its structure
-- Insert data into your table
INSERT INTO students (id, name, age, grade, enrollment_date)
VALUES
(1, 'Alice Johnson', 20, 3.8, '2024-01-15'),
(2, 'Bob Smith', 22, 3.2, '2023-09-01'),
(3, 'Carol White', 19, 3.9, '2024-01-15');
-- Verify the data
SELECT * FROM students;
-- Modify the table: add a new column
ALTER TABLE students ADD COLUMN email VARCHAR(100);📘 Quick Reference
| Statement | Purpose |
|---|---|
| CREATE DATABASE | Creates a new database |
| CREATE TABLE | Creates a new table with columns |
| INSERT INTO | Adds new rows to a table |
| ALTER TABLE | Modifies table structure |
| DROP TABLE | Deletes a table permanently |
🎉 Lesson Complete!
You can now create databases, design tables with proper data types and constraints, and insert data. Next, you'll learn to retrieve data with the SELECT statement — the most-used SQL command!
Sign up for free to track which lessons you've completed and get learning reminders.