PostgreSQL Basic SQL Operations

1. CREATE TABLE

Creates a new table with defined columns and data types:

sql

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE DEFAULT CURRENT_DATE,
    is_active BOOLEAN DEFAULT true
);

Key points:

• SERIAL auto-increments integers (similar to AUTO_INCREMENT)

• Define data types: INTEGER, VARCHAR(n), DECIMAL(p,s), DATE, BOOLEAN, etc.

• Constraints: PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE, CHECK

2. INSERT

Adds new rows to a table:

sql

-- Insert single row
INSERT INTO employees (name, department, salary) 
VALUES ('John Doe', 'Engineering', 75000);

-- Insert multiple rows
INSERT INTO employees (name, department, salary) 
VALUES 
    ('Jane Smith', 'Marketing', 65000),
    ('Bob Wilson', 'Engineering', 82000);

-- Insert with default values
INSERT INTO employees (name) VALUES ('Alice Johnson');

3. SELECT

Retrieves data from tables:

sql

-- Select all columns
SELECT * FROM employees;

-- Select specific columns
SELECT name, department, salary FROM employees;

-- Select with expressions
SELECT name, salary * 1.1 AS increased_salary FROM employees;

4. UPDATE

Modifies existing data:

sql

-- Update all rows
UPDATE employees SET is_active = true;

-- Update specific rows
UPDATE employees 
SET salary = salary * 1.05 
WHERE department = 'Engineering';

-- Update multiple columns
UPDATE employees 
SET department = 'HR', salary = 70000 
WHERE name = 'Jane Smith';

5. DELETE

Removes rows from a table:

sql

-- Delete all rows (be careful!)
DELETE FROM employees;

-- Delete specific rows
DELETE FROM employees WHERE is_active = false;

-- Delete with complex condition
DELETE FROM employees 
WHERE department = 'Temp' AND hire_date < '2023-01-01';

6. WHERE Clause

Filters rows based on conditions:

sql

-- Comparison operators
SELECT * FROM employees WHERE salary > 70000;
SELECT * FROM employees WHERE department = 'Engineering';

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

-- IN operator
SELECT * FROM employees 
WHERE department IN ('Engineering', 'Marketing');

-- BETWEEN operator
SELECT * FROM employees 
WHERE salary BETWEEN 60000 AND 80000;

-- LIKE for pattern matching
SELECT * FROM employees 
WHERE name LIKE 'J%';  -- Names starting with J

7. ORDER BY

Sorts result sets:

sql

-- Ascending order (default)
SELECT * FROM employees ORDER BY name;

-- Descending order
SELECT * FROM employees ORDER BY salary DESC;

-- Multiple sort criteria
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

-- With WHERE clause
SELECT * FROM employees 
WHERE is_active = true 
ORDER BY hire_date DESC;

8. LIMIT

Restricts number of rows returned:

sql

-- Get first 5 rows
SELECT * FROM employees LIMIT 5;

-- With OFFSET for pagination
SELECT * FROM employees LIMIT 10 OFFSET 20;  -- Rows 21-30

-- Often used with ORDER BY
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 3;  -- Top 3 highest salaries

9. DISTINCT

Returns unique values:

sql

-- Distinct departments
SELECT DISTINCT department FROM employees;

-- Distinct combinations
SELECT DISTINCT department, is_active FROM employees;

-- Count distinct values
SELECT COUNT(DISTINCT department) FROM employees;

-- With WHERE clause
SELECT DISTINCT department 
FROM employees 
WHERE salary > 70000;

10. NULL Handling

Working with NULL (missing/unknown) values:

sql

-- Check for NULL
SELECT * FROM employees WHERE department IS NULL;

-- Check for NOT NULL
SELECT * FROM employees WHERE department IS NOT NULL;

-- COALESCE: returns first non-null value
SELECT name, COALESCE(department, 'Not Assigned') AS dept 
FROM employees;

-- NULLIF: returns NULL if values match
SELECT NULLIF(department, 'Temporary') AS dept FROM employees;

-- Arithmetic with NULL (returns NULL)
SELECT name, salary + bonus AS total FROM employees;  -- If bonus is NULL, total is NULL

-- Aggregate functions ignore NULL (except COUNT(*))
SELECT AVG(salary) FROM employees;  -- NULL salaries excluded

Important Notes about NULL:

• NULL ≠ NULL (use IS NULL not = NULL)

• NULL values are sorted last in ascending order

• Most aggregate functions ignore NULL values

• Use COALESCE() to provide default values for NULL

Complete Example

sql

-- Create table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2),
    stock_quantity INT DEFAULT 0
);

-- Insert data
INSERT INTO products (name, category, price, stock_quantity)
VALUES 
    ('Laptop', 'Electronics', 999.99, 10),
    ('Desk Chair', 'Furniture', 149.99, 15),
    ('Coffee Mug', NULL, 12.99, 50);

-- Query examples
SELECT DISTINCT category FROM products;
SELECT * FROM products WHERE price > 100 ORDER BY price DESC;
SELECT name, COALESCE(category, 'Uncategorized') FROM products;
SELECT * FROM products WHERE stock_quantity > 0 LIMIT 5;

These fundamental SQL operations form the basis for working with PostgreSQL databases. Master these before moving to more advanced topics like JOINs, aggregates, and subqueries.