SQL Cheatsheet

-- Select all columns from a table
SELECT * FROM employees;

-- Select specific columns
SELECT first_name, last_name, salary FROM employees;

-- Filter rows with WHERE clause
SELECT * FROM employees WHERE department = 'Sales';

-- Use logical operators
SELECT * FROM employees WHERE salary > 50000 AND department = 'Sales';

-- Sort results with ORDER BY
SELECT * FROM employees ORDER BY last_name ASC, first_name DESC;

-- Limit the number of rows returned
SELECT * FROM employees LIMIT 10;

-- INNER JOIN: Returns records that have matching values in both tables
SELECT employees.first_name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

-- LEFT JOIN: Returns all records from the left table, and the matched records from the right table
SELECT employees.first_name, orders.order_id
FROM employees
LEFT JOIN orders ON employees.id = orders.employee_id;

-- RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
SELECT employees.first_name, orders.order_id
FROM employees
RIGHT JOIN orders ON employees.id = orders.employee_id;

-- FULL OUTER JOIN: Returns all records when there is a match in either left or right table
-- (Note: Not all SQL databases support this, e.g., MySQL)
SELECT employees.first_name, departments.name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.id;

-- Aggregate Functions
SELECT COUNT(*) FROM employees; -- Count rows
SELECT AVG(salary) FROM employees; -- Average salary
SELECT SUM(salary) FROM employees; -- Total salary
SELECT MIN(salary) FROM employees; -- Minimum salary
SELECT MAX(salary) FROM employees; -- Maximum salary

-- GROUP BY: Group rows that have the same values into summary rows
SELECT department, COUNT(*) as num_employees
FROM employees
GROUP BY department;

-- HAVING: Filter groups based on aggregate functions
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

-- Insert a new record into a table
INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Doe', 'IT', 75000);

-- Update existing records
UPDATE employees
SET salary = 80000
WHERE first_name = 'John' AND last_name = 'Doe';

-- Delete records from a table
DELETE FROM employees
WHERE first_name = 'John' AND last_name = 'Doe';

-- Create a new table
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT, -- Varies by SQL dialect (e.g., SERIAL for PostgreSQL)
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

-- Add a column to an existing table
ALTER TABLE employees
ADD COLUMN email VARCHAR(100);

-- Drop a column from a table
ALTER TABLE employees
DROP COLUMN hire_date;

-- Delete a table completely
DROP TABLE employees;

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INT,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    status VARCHAR(20) DEFAULT 'Active',
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- PRIMARY KEY: Uniquely identifies each record.
-- FOREIGN KEY: Links two tables together.
-- UNIQUE: Ensures all values in a column are different.
-- NOT NULL: Ensures a column cannot have a NULL value.
-- CHECK: Ensures values in a column satisfy a specific condition.
-- DEFAULT: Provides a default value for a column.

-- Transactions ensure that a block of SQL commands are executed
-- all together, or not at all (atomicity).

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

-- If all commands were successful, commit the changes.
COMMIT;

-- If an error occurred, roll back all changes.
-- ROLLBACK;