Top 100+ SQL Commands

SELECT first_name, last_name, email
FROM employees
WHERE department = 'Sales';

SELECT * FROM products
LIMIT 5;

SELECT * FROM orders
WHERE status = 'pending'
AND total_amount > 100;

SELECT name, price
FROM products
ORDER BY price DESC, name ASC;

-- MySQL/PostgreSQL
SELECT * FROM users LIMIT 10;

-- SQL Server
SELECT TOP 10 * FROM users;

-- Oracle
SELECT * FROM users FETCH FIRST 10 ROWS ONLY;

SELECT DISTINCT department
FROM employees
ORDER BY department;

SELECT 
    first_name AS "First Name",
    salary * 12 AS annual_salary
FROM employees e
WHERE e.department = 'IT';

SELECT * FROM customers
WHERE email LIKE '%@gmail.com'
AND name LIKE 'J___';

SELECT * FROM orders
WHERE status IN ('pending', 'processing', 'shipped');

SELECT * FROM products
WHERE price BETWEEN 50 AND 200
ORDER BY price;

SELECT * FROM employees
WHERE manager_id IS NULL;

-- Find all assigned employees
SELECT * FROM employees
WHERE manager_id IS NOT NULL;

SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Gadgets')
AND price < 500
AND NOT discontinued;

-- Page 2 with 10 items per page
SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 10;

SELECT name, price,
    CASE 
        WHEN price < 50 THEN 'Budget'
        WHEN price < 200 THEN 'Mid-range'
        ELSE 'Premium'
    END AS price_tier
FROM products;

SELECT 
    name,
    COALESCE(nickname, first_name, 'Unknown') AS display_name
FROM users;

INSERT INTO employees (name, email, department)
VALUES ('John Doe', 'john@company.com', 'Engineering');

INSERT INTO products (name, price)
VALUES 
    ('Keyboard', 79.99),
    ('Mouse', 29.99),
    ('Monitor', 299.99);

INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < '2024-01-01';

UPDATE employees
SET salary = salary * 1.10,
    updated_at = CURRENT_TIMESTAMP
WHERE department = 'Engineering';

-- MySQL
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.shipping_address = c.address
WHERE o.shipping_address IS NULL;

DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;

-- MySQL
DELETE o FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'inactive';

-- PostgreSQL
INSERT INTO users (email, name)
VALUES ('john@email.com', 'John')
ON CONFLICT (email) 
DO UPDATE SET name = EXCLUDED.name;

MERGE INTO target_table t
USING source_table s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

REPLACE INTO settings (key, value)
VALUES ('theme', 'dark');

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    salary DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE high_value_customers AS
SELECT * FROM customers
WHERE total_purchases > 10000;

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20),
ADD COLUMN address TEXT;

-- MySQL
ALTER TABLE products
MODIFY COLUMN price DECIMAL(12,2) NOT NULL;

-- PostgreSQL
ALTER TABLE products
ALTER COLUMN price TYPE DECIMAL(12,2);

ALTER TABLE employees
DROP COLUMN temporary_notes;

-- Rename table
ALTER TABLE users RENAME TO customers;

-- Rename column (MySQL)
ALTER TABLE customers
RENAME COLUMN name TO full_name;

DROP TABLE IF EXISTS temp_data;

TRUNCATE TABLE log_entries;

CREATE DATABASE IF NOT EXISTS ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

DROP DATABASE IF EXISTS test_db;

CREATE SCHEMA IF NOT EXISTS analytics;

CREATE TABLE analytics.user_events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50)
);

COMMENT ON TABLE employees IS 'Stores employee information';
COMMENT ON COLUMN employees.salary IS 'Monthly salary in USD';

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) 
        REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(50) UNIQUE
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18 AND age <= 100),
    salary DECIMAL(10,2) CHECK (salary > 0)
);

CREATE TABLE articles (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    views INT DEFAULT 0
);

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price >= 0);

ALTER TABLE orders
DROP CONSTRAINT fk_customer;

-- MySQL syntax
ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

SELECT o.order_id, c.name, o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

SELECT sizes.name, colors.name
FROM sizes
CROSS JOIN colors;

SELECT e.name AS employee,
       m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

SELECT * FROM orders
NATURAL JOIN customers;

SELECT o.order_id, c.name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

SELECT 
    COUNT(*) AS total_orders,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

SELECT 
    SUM(total_amount) AS revenue,
    SUM(quantity) AS items_sold
FROM orders
WHERE order_date >= '2024-01-01';

SELECT 
    AVG(salary) AS avg_salary,
    AVG(age) AS avg_age
FROM employees
WHERE department = 'Engineering';

SELECT 
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive,
    MAX(order_date) AS last_order
FROM products;

SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5 AND SUM(total) > 1000;

-- MySQL
SELECT department, GROUP_CONCAT(name) AS members
FROM employees GROUP BY department;

-- PostgreSQL
SELECT department, STRING_AGG(name, ', ') AS members
FROM employees GROUP BY department;

SELECT 
    department, 
    job_title,
    SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department, job_title);

SELECT 
    region, 
    product,
    SUM(sales) AS total
FROM sales_data
GROUP BY CUBE(region, product);

SELECT department, year, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS (
    (department),
    (year),
    (department, year)
);

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- PostgreSQL alternative
SELECT first_name || ' ' || last_name AS full_name
FROM employees;

SELECT 
    SUBSTRING(phone, 1, 3) AS area_code,
    SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM contacts;

SELECT 
    UPPER(name) AS upper_name,
    LOWER(email) AS lower_email
FROM users;

SELECT 
    TRIM('  Hello World  ') AS trimmed,
    LTRIM('  Left') AS left_trimmed,
    RTRIM('Right  ') AS right_trimmed;

SELECT name, LENGTH(name) AS name_length
FROM products
WHERE LENGTH(name) > 20;

SELECT REPLACE(phone, '-', '') AS clean_phone
FROM contacts;

UPDATE urls SET path = REPLACE(path, 'http://', 'https://');

SELECT 
    CAST(price AS INT) AS rounded_price,
    CAST(quantity AS VARCHAR(10)) AS qty_text,
    CAST('2024-01-15' AS DATE) AS order_date
FROM orders;

-- Avoid division by zero
SELECT 
    revenue / NULLIF(orders_count, 0) AS avg_order_value
FROM sales;

SELECT 
    NOW() AS current_datetime,
    CURRENT_DATE AS today,
    CURRENT_TIME AS current_time;

-- MySQL
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week;

-- PostgreSQL
SELECT NOW() + INTERVAL '7 days' AS next_week;

SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month
FROM orders;

SELECT 
    ROUND(price, 2) AS rounded,
    FLOOR(price) AS floored,
    CEIL(price) AS ceiling
FROM products;

SELECT 
    name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

SELECT * FROM products
WHERE category_id IN (
    SELECT id FROM categories
    WHERE name LIKE 'Electronics%'
);

SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
    AND o.total > 1000
);

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees
    WHERE department = 'Sales'
);

SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees
    WHERE department = 'Sales'
);

SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department = e.department
);

WITH high_value_orders AS (
    SELECT customer_id, SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total) > 10000
)
SELECT c.name, h.total_spent
FROM customers c
JOIN high_value_orders h ON c.id = h.customer_id;

CREATE INDEX idx_customer_email
ON customers(email);

CREATE INDEX idx_orders_date
ON orders(order_date DESC);

CREATE UNIQUE INDEX idx_users_email
ON users(email);

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

-- MySQL
DROP INDEX idx_customer_email ON customers;

-- PostgreSQL
DROP INDEX idx_customer_email;

-- MySQL
SHOW INDEX FROM orders;

-- PostgreSQL
SELECT * FROM pg_indexes WHERE tablename = 'orders';

-- PostgreSQL
CREATE INDEX idx_active_users
ON users(email)
WHERE is_active = true;

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 123
ORDER BY order_date DESC;

BEGIN;
-- or
START TRANSACTION;

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

BEGIN;

DELETE FROM important_data WHERE id = 1;
-- Oops, wrong deletion!
ROLLBACK;

BEGIN;

INSERT INTO orders VALUES (1, 'pending');
SAVEPOINT order_created;

INSERT INTO order_items VALUES (1, 101, 2);
-- Error occurred
ROLLBACK TO order_created;

COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- or
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

LOCK TABLE inventory IN EXCLUSIVE MODE;

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;

COMMIT;

CREATE VIEW active_customers AS
SELECT c.id, c.name, c.email, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.is_active = true
GROUP BY c.id, c.name, c.email;

CREATE OR REPLACE VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

DROP VIEW IF EXISTS old_report;

CREATE VIEW us_customers AS
SELECT * FROM customers
WHERE country = 'USA'
WITH CHECK OPTION;

-- Can insert/update through view
INSERT INTO us_customers (name, country)
VALUES ('John', 'USA');

-- PostgreSQL
CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(quantity) AS total_sold
FROM order_items
GROUP BY product_id;

-- Refresh data
REFRESH MATERIALIZED VIEW sales_summary;

-- PostgreSQL
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers
ORDER BY name;

SELECT product_id FROM orders_2023
UNION ALL
SELECT product_id FROM orders_2024;

SELECT customer_id FROM orders_january
INTERSECT
SELECT customer_id FROM orders_february;

-- PostgreSQL, SQL Server
SELECT email FROM all_users
EXCEPT
SELECT email FROM unsubscribed_users;

-- Oracle
SELECT email FROM all_users
MINUS
SELECT email FROM unsubscribed_users;

SELECT 
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS rank
FROM employees;

SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

SELECT 
    order_date,
    total,
    LAG(total) OVER (ORDER BY order_date) AS prev_total,
    total - LAG(total) OVER (ORDER BY order_date) AS change
FROM daily_sales;

SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    SUM(amount) OVER () AS grand_total
FROM orders;

-- SQL Server
SELECT *
FROM (
    SELECT product, month, revenue
    FROM sales
) AS src
PIVOT (
    SUM(revenue)
    FOR month IN ([Jan], [Feb], [Mar])
) AS pvt;

WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;