Hardik Kanajariya

Posted on Oct 22

Day 4: Inserting Data and Basic CRUD Operations

#postgres #sql #tutorial #beginners

Day 4: Inserting Data and Basic CRUD Operations

Welcome to Day 4! Today, we'll learn how to insert, read, update, and delete data - the fundamental operations known as CRUD.

What is CRUD?

Create - INSERT data
Read - SELECT data
Update - UPDATE data

Delete - DELETE data

Setup: Creating Our Practice Database

Let's create a simple employee management database:

CREATE DATABASE company_db;
\c company_db

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE DEFAULT CURRENT_DATE
);

1. CREATE - Inserting Data (INSERT)

Single Row Insert

INSERT INTO employees (first_name, last_name, email, department, salary)
VALUES ('John', 'Doe', 'john.doe@company.com', 'Engineering', 75000.00);

Multiple Rows at Once

INSERT INTO employees (first_name, last_name, email, department, salary)
VALUES 
    ('Jane', 'Smith', 'jane.smith@company.com', 'Marketing', 65000.00),
    ('Mike', 'Johnson', 'mike.j@company.com', 'Sales', 70000.00),
    ('Sarah', 'Williams', 'sarah.w@company.com', 'Engineering', 80000.00),
    ('Tom', 'Brown', 'tom.b@company.com', 'HR', 60000.00);

Insert with RETURNING

Get the newly created row information:

INSERT INTO employees (first_name, last_name, email, department, salary)
VALUES ('Alice', 'Davis', 'alice.d@company.com', 'Engineering', 85000.00)
RETURNING *;

2. READ - Retrieving Data (SELECT)

Select All Columns

SELECT * FROM employees;

Select Specific Columns

SELECT first_name, last_name, department FROM employees;

Filtering with WHERE

-- Find engineers
SELECT * FROM employees WHERE department = 'Engineering';

-- Find employees with salary > 70000
SELECT first_name, last_name, salary 
FROM employees 
WHERE salary > 70000;

-- Multiple conditions with AND
SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 75000;

-- Multiple conditions with OR
SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'Marketing';

Sorting Results (ORDER BY)

-- Sort by salary (ascending)
SELECT * FROM employees ORDER BY salary;

-- Sort by salary (descending)
SELECT * FROM employees ORDER BY salary DESC;

-- Sort by multiple columns
SELECT * FROM employees ORDER BY department, salary DESC;

Limiting Results (LIMIT)

-- Get top 3 highest paid employees
SELECT * FROM employees ORDER BY salary DESC LIMIT 3;

Pattern Matching (LIKE)

-- Find emails containing 'smith'
SELECT * FROM employees WHERE email LIKE '%smith%';

-- Find names starting with 'J'
SELECT * FROM employees WHERE first_name LIKE 'J%';

3. UPDATE - Modifying Data

Update Single Record

UPDATE employees 
SET salary = 90000.00
WHERE employee_id = 1;

Update Multiple Columns

UPDATE employees 
SET salary = 72000.00, department = 'Sales'
WHERE employee_id = 3;

Update with Conditions

-- Give 10% raise to all Engineering employees
UPDATE employees 
SET salary = salary * 1.10
WHERE department = 'Engineering';

Update with RETURNING

UPDATE employees 
SET salary = salary + 5000
WHERE department = 'HR'
RETURNING *;

4. DELETE - Removing Data

Delete Specific Record

DELETE FROM employees WHERE employee_id = 5;

Delete with Conditions

-- Remove all employees from a specific department
DELETE FROM employees WHERE department = 'Sales';

Delete All Records (Careful!)

-- This deletes EVERYTHING (but keeps table structure)
DELETE FROM employees;

-- Faster way to delete all records
TRUNCATE TABLE employees;

⚠️ Always use WHERE clause with UPDATE and DELETE!

Aggregate Functions

-- Count employees
SELECT COUNT(*) FROM employees;

-- Average salary
SELECT AVG(salary) FROM employees;

-- Highest and lowest salary
SELECT MAX(salary), MIN(salary) FROM employees;

-- Total payroll
SELECT SUM(salary) FROM employees;

-- Count by department
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;

-- Average salary by department
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Best Practices

✅ Always use WHERE with UPDATE/DELETE - Avoid accidental mass updates
✅ Test with SELECT first - Run a SELECT with your WHERE clause before UPDATE/DELETE
✅ Use transactions - We'll cover this more in future lessons
✅ Validate data - Check constraints prevent invalid data
✅ Back up before mass operations - Safety first!

Common Mistakes to Avoid

❌ Missing WHERE clause in UPDATE/DELETE
❌ Not handling NULL values properly
❌ Forgetting quotes around string values
❌ Using = instead of LIKE for pattern matching

Tomorrow's Preview: Day 5 - Advanced SELECT Queries and Filtering

Practice Exercises: