sql_quickref

Modern SQL Quick Reference Guide

This guide provides a quick reference to common SQL statements and their modern syntax. Note that specific syntax details can sometimes vary slightly between different Database Management Systems (DBMS) like MySQL, PostgreSQL, SQL Server, Oracle, etc.

SQL Statement | Syntax / Example

**`AND` / `OR` Operators**
Used to combine multiple conditions in a `WHERE` clause.
```sql
SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2;

SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;

ALTER TABLE Modifies an existing table structure.

-- Add a column
ALTER TABLE table_name
ADD COLUMN column_name datatype [constraints];

-- Drop a column
ALTER TABLE table_name
DROP COLUMN column_name;

-- Modify a column's data type (syntax varies)
-- PostgreSQL, SQL Server:
ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_datatype;
-- MySQL:
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;

Note: Modifying columns (type, constraints) is common but syntax varies significantly. Adding/dropping columns is more standard.

```sql
**`AS` (Alias)**
Assigns a temporary name to a table or column.
```sql
-- Column alias
SELECT column_name AS alias_name
FROM table_name;

-- Table alias (often used in JOINs)
SELECT t1.column1, t2.column2
FROM table_name1 AS t1
JOIN table_name2 AS t2 ON t1.id = t2.fk_id;

```sql
**`BETWEEN` Operator**
Selects values within a given range (inclusive).
```sql
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

```sql
**`CASE` Expression**
Allows for conditional logic within SQL statements.
```sql
SELECT column_name,
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE result_else
       END AS new_column_name
FROM table_name;

```sql
**`CREATE DATABASE`**
Creates a new database.
```sql
CREATE DATABASE database_name;

```sql
**`CREATE TABLE`**
Creates a new table.
```sql
CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    column3 datatype [constraints],
    ...
    PRIMARY KEY (column_name_pk),
    FOREIGN KEY (column_name_fk) REFERENCES other_table(other_column)
);
-- Example with common constraints:
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT, -- Or SERIAL for PostgreSQL
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INT,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

```sql
**`CREATE INDEX`**
Creates an index to improve query performance.
```sql
CREATE INDEX index_name
ON table_name (column1, column2, ...);

-- Create a unique index
CREATE UNIQUE INDEX index_name_unique
ON table_name (column_unique);

```sql
**`CREATE VIEW`**
Creates a virtual table based on the result-set of an SQL statement.
```sql
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

```sql
**`DELETE`**
Deletes rows from a table.
```sql
-- Delete specific rows
DELETE FROM table_name
WHERE condition;

-- Delete all rows (use with caution!)
DELETE FROM table_name;

Note: TRUNCATE TABLE is faster for deleting all rows but has different characteristics.

DROP DATABASE Deletes an existing database (use with extreme caution!).

DROP DATABASE database_name;

DROP INDEX Deletes an index.

-- Common syntax (MySQL, PostgreSQL, SQL Server)
DROP INDEX index_name ON table_name;

-- Oracle syntax
DROP INDEX index_name;

-- Older MySQL syntax (still works)
-- ALTER TABLE table_name DROP INDEX index_name;

DROP TABLE Deletes an existing table (use with extreme caution!).

DROP TABLE table_name;

```sql
**`EXISTS` Operator**
Tests for the existence of any record in a subquery.
```sql
SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT 1 FROM another_table WHERE condition);

```javascript
**`GROUP BY` Clause**
Groups rows that have the same values in specified columns into summary rows. Often used with aggregate functions.
```sql
SELECT column_name, aggregate_function(column_to_aggregate) AS result
FROM table_name
WHERE condition -- Optional
GROUP BY column_name
ORDER BY column_name; -- Optional

```javascript
**`HAVING` Clause**
Filters groups created by `GROUP BY`. Used because `WHERE` cannot filter on aggregate functions.
```sql
SELECT column_name, aggregate_function(column_to_aggregate) AS result
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_to_aggregate) operator value;

```sql
**`IN` Operator**
Allows you to specify multiple values in a `WHERE` clause.
```sql
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
-- Can also be used with a subquery
WHERE column_name IN (SELECT column_from_another_table FROM another_table WHERE condition);

```sql
**`INSERT INTO`**
Inserts new rows into a table.
```sql
-- Inserting values for all columns (order matters)
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

-- Inserting values for specific columns
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

```sql
**`INSERT INTO SELECT`**
Copies data from one table and inserts it into another table.
```sql
INSERT INTO target_table (column1, column2)
SELECT source_column1, source_column2
FROM source_table
WHERE condition;

```sql
**`JOIN` Operations (INNER, LEFT, RIGHT, FULL OUTER)**
Combines rows from two or more tables based on a related column.
```sql
-- INNER JOIN (returns matching rows from both tables)
SELECT columns
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

-- LEFT JOIN (returns all rows from left table, matching from right, NULLs otherwise)
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

-- RIGHT JOIN (returns all rows from right table, matching from left, NULLs otherwise)
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;

-- FULL OUTER JOIN (returns all rows when there is a match in either table, NULLs for non-matches)
SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.common_column = table2.common_column;

Note: Some DBMS use LEFT OUTER JOIN, RIGHT OUTER JOIN. OUTER is often optional.

```sql
**`LIKE` Operator**
Used in a `WHERE` clause to search for a specified pattern in a column.
Wildcards: `%` (matches any sequence of characters), `_` (matches any single character).
```sql
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE 'pattern%'; -- e.g., 'a%', '%xyz', '_bc%'

```sql
**`LIMIT` / `FETCH FIRST` / `TOP` Clause**
Restricts the number of rows returned by a query.
```sql
-- MySQL, PostgreSQL, SQLite
SELECT column_name(s)
FROM table_name
ORDER BY column_name -- Optional, but recommended for consistent results
LIMIT number_of_rows;

-- SQL Server
SELECT TOP number_of_rows_or_percent column_name(s)
FROM table_name
ORDER BY column_name; -- Optional

-- Oracle, Standard SQL (2008+)
SELECT column_name(s)
FROM table_name
ORDER BY column_name -- Optional
FETCH FIRST number_of_rows ROWS ONLY;

```sql
**`ORDER BY` Clause**
Sorts the result set in ascending (ASC - default) or descending (DESC) order.
```sql
SELECT column_name(s)
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

```sql
**`SELECT`**
Extracts data from a database.
```sql
SELECT column1, column2, ...
FROM table_name;

```sql
**`SELECT *`**
Selects all columns from a table.
```sql
SELECT *
FROM table_name;

Note: Generally discouraged in production code for performance and clarity; prefer naming specific columns.

```sql
**`SELECT DISTINCT`**
Returns only unique (distinct) values for specified columns.
```sql
SELECT DISTINCT column_name(s)
FROM table_name;

```sql
**`SELECT INTO` / `CREATE TABLE AS SELECT`**
Copies data into a new table.
```sql
-- SQL Server (creates new_table)
SELECT column1, column2
INTO new_table_name
FROM old_table_name
WHERE condition;

-- Standard SQL, PostgreSQL, MySQL, Oracle (creates new_table)
CREATE TABLE new_table_name AS
SELECT column1, column2
FROM old_table_name
WHERE condition;

Note: SELECT ... INTO variable_name in PL/SQL (Oracle) or T-SQL (SQL Server) is different and assigns values to variables.

```sql
**`TRUNCATE TABLE`**
Removes all rows from a table quickly. This operation is usually faster than `DELETE FROM table_name` and typically cannot be rolled back easily as it's often minimally logged. It also resets auto-increment counters in some DBMS.
```sql
TRUNCATE TABLE table_name;

Caution: Use with care as it deletes all data irreversibly in many systems without firing row-level triggers.

```sql
**`UNION` / `UNION ALL` Operators**
Combines the result sets of two or more `SELECT` statements.
`UNION` removes duplicate rows. `UNION ALL` includes all rows (faster).
```sql
SELECT column_name(s) FROM table1
UNION -- or UNION ALL
SELECT column_name(s) FROM table2;

Note: The SELECT statements must have the same number of columns, and corresponding columns must have compatible data types.

```sql
**`UPDATE`**
Modifies existing records in a table.
```sql
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition; -- Important! Without WHERE, all rows will be updated.

```sql
**`WHERE` Clause**
Filters records based on specified conditions.
```sql
SELECT column_name(s)
FROM table_name
WHERE condition;

This quick reference covers many fundamental SQL commands. For advanced topics like Window Functions, Common Table Expressions (CTEs), and specific DBMS functions, refer to more detailed documentation.