Home/PostgreSQL/SQL Fundamentals — CRUD

🐘 PostgreSQL1 / 1

SQL Fundamentals — CRUD

📌 WHAT

SQL (Structured Query Language) is used to interact with relational databases. Data is stored in tables with rows (records) and columns (fields).

💡 WHY

Every application needs persistent data storage. SQL is the standard language for querying and manipulating that data across 50+ years of database history.

⏰ WHEN / WHERE

Every time your app reads or writes data: loading a user profile, saving a form, showing a list, updating a record, deleting an item.

🔧 HOW

SELECT reads data, INSERT creates records, UPDATE modifies them, DELETE removes them. WHERE filters results. ORDER BY sorts. LIMIT restricts count.

📚Key Terms

Table

— Like a spreadsheet — rows are records, columns are fields

Primary Key

— Unique identifier for each row, usually an auto-incrementing integer (SERIAL)

Foreign Key

— Column referencing the primary key of another table — creates relationships

NULL

— Absence of a value. Different from 0 or empty string. Use IS NULL to check for it

ACID

— Atomicity, Consistency, Isolation, Durability — properties ensuring data integrity

Code Examples

#1CRUD operations

CRUD operations

sql

-- CREATE TABLE
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,           -- auto-incrementing integer
  name       VARCHAR(100) NOT NULL,        -- max 100 chars, required
  email      VARCHAR(255) UNIQUE NOT NULL, -- unique across all rows
  role       VARCHAR(20) DEFAULT 'user',   -- default value
  created_at TIMESTAMP DEFAULT NOW()       -- auto timestamp
);

-- INSERT (Create)
INSERT INTO users (name, email)
VALUES ('Ali Ahmed', 'ali@example.com');

-- Insert multiple rows
INSERT INTO users (name, email, role) VALUES
  ('Ahmed Khan', 'ahmed@example.com', 'admin'),
  ('Sara Ali',   'sara@example.com',  'user');

-- SELECT (Read)
SELECT * FROM users;                           -- all columns
SELECT id, name, email FROM users;             -- specific columns
SELECT * FROM users WHERE role = 'admin';      -- filter
SELECT * FROM users WHERE age > 18 AND role = 'user';
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- pattern
SELECT * FROM users ORDER BY created_at DESC;  -- sort newest first
SELECT * FROM users ORDER BY name ASC LIMIT 10 OFFSET 20; -- pagination

-- UPDATE
UPDATE users
SET name = 'Ali Khan', role = 'admin'
WHERE id = 1;

-- ⚠️ ALWAYS use WHERE with UPDATE/DELETE or you affect ALL rows!
UPDATE users SET role = 'user'; -- affects EVERY user (usually a bug)

-- DELETE
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < NOW() - INTERVAL '30 days';

Try in Playground

#2JOINs — combining tables

JOINs — combining tables

sql

-- Setup: posts table references users
CREATE TABLE posts (
  id         SERIAL PRIMARY KEY,
  title      TEXT NOT NULL,
  content    TEXT,
  user_id    INTEGER REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW()
);

-- INNER JOIN — only rows with matches in BOTH tables
SELECT
  users.name,
  posts.title,
  posts.created_at
FROM posts
INNER JOIN users ON posts.user_id = users.id;

-- Alias for cleaner syntax
SELECT
  u.name AS author,
  p.title,
  p.created_at
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.role = 'admin'
ORDER BY p.created_at DESC;

-- LEFT JOIN — ALL rows from left table + matching from right
-- Users without posts will appear with NULL post columns
SELECT
  u.name,
  COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name
ORDER BY post_count DESC;

-- Subquery
SELECT * FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM posts
  WHERE created_at > NOW() - INTERVAL '7 days'
);

Try in Playground

Practice This Concept

Open the playground, paste any example, and experiment. The best way to learn is by breaking things.

Open in Playground

API Routes

Setup & CRUD Operations