SELECT

-- All columns
SELECT * FROM users;

-- Specific columns
SELECT id, first_name, email FROM users;

-- With table alias
SELECT u.id, u.email FROM users u;
SELECT u.id, u.email FROM users AS u;

-- Arithmetic in SELECT
SELECT price, qty, price * qty AS total FROM order_items;

-- String expressions
SELECT first_name || ' ' || last_name AS full_name FROM users;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- Conditional expressions
SELECT
    name,
    CASE WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN 'B'
         WHEN score >= 70 THEN 'C'
         ELSE 'F'
    END AS grade
FROM students;

-- Alias without AS (legal but avoid for clarity)
SELECT first_name name FROM users;

-- Aliases can be used in ORDER BY but NOT in WHERE or HAVING
SELECT price * qty AS total FROM order_items ORDER BY total DESC;

-- Remove duplicate rows
SELECT DISTINCT country FROM customers;

-- DISTINCT across multiple columns (combination must be unique)
SELECT DISTINCT country, city FROM customers;

-- COUNT(DISTINCT col)
SELECT COUNT(DISTINCT country) FROM customers;

-- Ascending (default)
SELECT * FROM products ORDER BY price ASC;
SELECT * FROM products ORDER BY price;

-- Descending
SELECT * FROM products ORDER BY price DESC;

-- Multiple columns: primary sort, then secondary
SELECT * FROM employees ORDER BY department ASC, salary DESC;

-- By column position (1-based)
SELECT id, name, price FROM products ORDER BY 3 DESC;

-- By expression
SELECT *, price * qty AS total FROM order_items ORDER BY price * qty DESC;

-- NULL placement
SELECT * FROM users ORDER BY last_login ASC NULLS LAST;
SELECT * FROM users ORDER BY last_login DESC NULLS FIRST;

-- First N rows
SELECT * FROM products ORDER BY price LIMIT 10;

-- Pagination: skip M rows, take N
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;   -- page 3 (0-indexed)

-- SQL Server / older syntax
SELECT TOP 10 * FROM products ORDER BY price;
SELECT * FROM products ORDER BY price FETCH FIRST 10 ROWS ONLY;

-- Random sample (PostgreSQL)
SELECT * FROM users ORDER BY RANDOM() LIMIT 5;
-- Random sample (MySQL)
SELECT * FROM users ORDER BY RAND() LIMIT 5;

SELECT
    UPPER(email),
    LENGTH(bio),
    TRIM(notes),
    REPLACE(phone, '-', ''),
    SUBSTRING(description FROM 1 FOR 100)
FROM users;

-- Common date/time patterns
SELECT
    EXTRACT(YEAR FROM created_at)        AS yr,
    DATE_TRUNC('month', created_at)      AS month_start,
    created_at + INTERVAL '90 days'      AS expires_at,
    NOW() - created_at                   AS age
FROM subscriptions;

-- CASE WHEN (searched form)
SELECT name,
    CASE
        WHEN salary > 100000 THEN 'high'
        WHEN salary > 50000  THEN 'mid'
        ELSE 'low'
    END AS band
FROM employees;

-- CASE value (simple form)
SELECT name,
    CASE status
        WHEN 'A' THEN 'Active'
        WHEN 'I' THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_label
FROM users;

-- COALESCE: first non-NULL
SELECT COALESCE(middle_name, '') AS middle FROM users;

-- NULLIF: returns NULL if equal
SELECT NULLIF(value, 0) FROM measurements;  -- avoid division by zero

-- IIF shorthand (SQL Server)
SELECT IIF(score >= 60, 'Pass', 'Fail') FROM results;

-- IF (MySQL only — not in standard SQL)
SELECT IF(score >= 60, 'Pass', 'Fail') FROM results;

-- Compute expressions directly
SELECT 1 + 1;
SELECT CURRENT_DATE;
SELECT UPPER('hello');
SELECT NOW(), CURRENT_USER;

-- PostgreSQL / MySQL; SQL Server needs: SELECT 1+1 FROM (VALUES(1)) t(n)
-- SQLite: SELECT 1+1

-- List all tables in current schema (PostgreSQL)
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

-- List columns of a table (PostgreSQL)
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users' ORDER BY ordinal_position;

-- MySQL describe shorthand
DESCRIBE users;
SHOW COLUMNS FROM users;
SHOW TABLES;