Full_SQL_Syntax_Handbook

Command Syntax Description

CREATE DATABASE CREATE DATABASE db_name; Creates a new database.
DROP DATABASE DROP DATABASE db_name; Deletes an existing database.
USE USE db_name; Selects a database to work with.
CREATE TABLE CREATE TABLE table_name (col1 datatype, col2 datatype, ...); Creates a new table.
DROP TABLE DROP TABLE table_name; Deletes a table.
ALTER TABLE (ADD) ALTER TABLE table_name ADD column_name datatype; Adds a new column to a table.
ALTER TABLE (MODIFY) ALTER TABLE table_name MODIFY column_name datatype; Modifies an existing column.
ALTER TABLE (DROP) ALTER TABLE table_name DROP column_name; Drops a column from a table.
INSERT INSERT INTO table_name (col1, col2) VALUES (val1, val2); Inserts data into a table.
UPDATE UPDATE table_name SET col1=val1 WHERE condition; Updates existing data in a table.
DELETE DELETE FROM table_name WHERE condition; Deletes data from a table.
SELECT SELECT col1, col2 FROM table_name; Retrieves data from a table.
SELECT ALL SELECT * FROM table_name; Retrieves all data from a table.
WHERE SELECT * FROM table_name WHERE condition; Filters records.
ORDER BY SELECT * FROM table_name ORDER BY col1 ASC|DESC; Sorts records.
GROUP BY SELECT col1, COUNT(*) FROM table_name GROUP BY col1; Groups rows sharing a property.
HAVING SELECT col1, COUNT(*) FROM table_name GROUP BY col1 HAVING COUNT(*)>1; Filters groups.
JOIN (INNER) SELECT * FROM A INNER JOIN B ON A.id=B.id; Returns rows with matching values.
JOIN (LEFT) SELECT * FROM A LEFT JOIN B ON A.id=B.id; Returns all from left and matching from right.
JOIN (RIGHT) SELECT * FROM A RIGHT JOIN B ON A.id=B.id; Returns all from right and matching from left.
JOIN (FULL) SELECT * FROM A FULL OUTER JOIN B ON A.id=B.id; Returns all rows from both tables.
UNION SELECT col1 FROM A UNION SELECT col1 FROM B; Combines results from multiple queries.
INDEX CREATE INDEX idx_name ON table_name (col1); Creates an index on a table column.
VIEW CREATE VIEW view_name AS SELECT col1 FROM table_name; Creates a virtual table.

TRIGGER CREATE TRIGGER trg_name BEFORE INSERT ON table_name FOR EACH ROW SET NEW.col1=val; Defines automatic actions.
STORED PROCEDURE CREATE PROCEDURE proc_name() BEGIN SELECT * FROM table_name; END; Defines reusable SQL blocks.
FUNCTION CREATE FUNCTION func_name() RETURNS datatype BEGIN RETURN value; END; Creates reusable functions.
TRANSACTION BEGIN; UPDATE table SET col1=val1; COMMIT; Manages transactions.
ROLLBACK ROLLBACK; Undoes a transaction.
GRANT GRANT SELECT, INSERT ON db.* TO 'user'@'host'; Gives privileges to users.
REVOKE REVOKE SELECT ON db.* FROM 'user'@'host'; Removes privileges from users.
PRIMARY KEY ALTER TABLE table_name ADD PRIMARY KEY (col1); Defines a primary key on a column.
ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (col1) REFERENCES
FOREIGN KEY parent_table(col2); Defines a foreign key relationship.
DEFAULT ALTER TABLE table_name ALTER column_name SET DEFAULT default_value; Sets a default value for a column.
CHECK ALTER TABLE table_name ADD CHECK (col1 > 0); Adds a constraint to enforce rules on data.
UNIQUE ALTER TABLE table_name ADD CONSTRAINT unique_col UNIQUE (col1); Ensures all values in a column are unique.
IF EXISTS DROP TABLE IF EXISTS table_name; Safely drops a table if it exists.
CASE SELECT col1, CASE WHEN col2>0 THEN 'POS' ELSE 'NEG' END AS status FROM table_name; Adds conditional logic in queries.
COALESCE SELECT COALESCE(col1, 'default') FROM table_name; Replaces NULL with a default value.
NULLIF SELECT NULLIF(col1,col2) FROM table_name; Returns NULL if two expressions are equal.
LIMIT / TOP SELECT * FROM table_name LIMIT 10; Limits the number of rows returned.
OFFSET SELECT * FROM table_name LIMIT 10 OFFSET 20; Skips a number of rows before returning rows.
DISTINCT SELECT DISTINCT col1 FROM table_name; Returns unique values.
EXISTS SELECT * FROM table_name WHERE EXISTS (SELECT 1 FROM other_table WHERE condition); Checks for the existence of rows.
ANY / SOME SELECT * FROM table_name WHERE col1 > ANY (SELECT col1 FROM other_table); Compares a value to a set of values.
 Command Syntax Description
ALL SELECT * FROM table_name WHERE col1 > ALL (SELECT col1 FROM other_table); Compares a value to all values in a set.
WITH (CTE) WITH cte_name AS (SELECT col1 FROM table_name) SELECT * FROM cte_name; Creates a Common Table Expression for reuse.

WINDOW FUNCTION (ROW_NUMBER) SELECT col1, ROW_NUMBER() OVER (PARTITION BY col2 ORDER BY col3) AS rn FROM table_name; Assigns row numbers within partitions.
WINDOW FUNCTION (RANK) SELECT col1, RANK() OVER (ORDER BY col2 DESC) AS rnk FROM table_name; Ranks rows with gaps for ties.
DENSE_RANK SELECT col1, DENSE_RANK() OVER (ORDER BY col2) AS drnk FROM table_name; Ranks rows without gaps for ties.
LEAD SELECT col1, LEAD(col2) OVER (ORDER BY col3) AS next_val FROM table_name; Returns the next row value in order.
LAG SELECT col1, LAG(col2) OVER (ORDER BY col3) AS prev_val FROM table_name; Returns the previous row value in order.
PARTITION BY SELECT col1, SUM(col2) OVER (PARTITION BY col3) AS part_sum FROM table_name; Computes aggregates per partition.
EXPLAIN EXPLAIN SELECT * FROM table_name; Shows the execution plan for a query.
CREATE INDEX (ADV) CREATE UNIQUE INDEX idx_name ON table_name (col1, col2); Creates an advanced index on multiple columns.
FULLTEXT INDEX CREATE FULLTEXT INDEX idx_name ON table_name (col1); Creates an index for full-text search.
JSON Functions SELECT JSON_EXTRACT(json_col, '$.key') FROM table_name; Extracts data from JSON columns.
MERGE INTO target_table USING source_table ON condition WHEN MATCHED THEN UPDATE SET ...
MERGE (UPSERT) WHEN NOT MATCHED THEN INSERT (...); Combines insert and update logic.
TEMP TABLE CREATE TEMPORARY TABLE temp_name AS SELECT * FROM table_name; Creates a temporary table for session use.

STORED FUNCTION CREATE FUNCTION func_name (param datatype) RETURNS datatype BEGIN RETURN ...; END; Creates a user-defined function.
CURSOR DECLARE cur CURSOR FOR SELECT col1 FROM table_name; OPEN cur; FETCH cur INTO var; Iterates through a result set row by row.
AUTO_INCREMENT (MySQL) CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY); MySQL syntax for auto-increment primary key.
IDENTITY (SQL Server) CREATE TABLE t1 (id INT IDENTITY(1,1) PRIMARY KEY); SQL Server syntax for auto-increment primary key.
SEQUENCE (Oracle) CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1; Oracle syntax for generating sequence values.
SERIAL (PostgreSQL) CREATE TABLE t1 (id SERIAL PRIMARY KEY); PostgreSQL auto-increment shorthand.
LIMIT (MySQL/PostgreSQL) SELECT * FROM t1 LIMIT 10; Limits rows returned in MySQL and PostgreSQL.
TOP (SQL Server) SELECT TOP 10 * FROM t1; Limits rows in SQL Server.
ROWNUM (Oracle) SELECT * FROM t1 WHERE ROWNUM <= 10; Limits rows in Oracle.
CONCAT (MySQL) SELECT CONCAT(col1, col2) FROM t1; Concatenates strings in MySQL.
|| Operator (PostgreSQL/Oracle) SELECT col1 || col2 FROM t1; Concatenates strings in PostgreSQL/Oracle.
ISNULL (SQL Server) SELECT ISNULL(col1, 'default') FROM t1; Replaces NULL in SQL Server.
NVL (Oracle) SELECT NVL(col1, 'default') FROM t1; Replaces NULL in Oracle.
COALESCE (MySQL/PostgreSQL) SELECT COALESCE(col1, 'default') FROM t1; Replaces NULL in MySQL/PostgreSQL.
IFNULL (MySQL) SELECT IFNULL(col1, 'default') FROM t1; Replaces NULL in MySQL.
GETDATE() (SQL Server) SELECT GETDATE(); Gets current date/time in SQL Server.
SYSDATE (Oracle) SELECT SYSDATE FROM DUAL; Gets current date/time in Oracle.
NOW() (MySQL/PostgreSQL) SELECT NOW(); Gets current date/time in MySQL/PostgreSQL.

LIMIT with OFFSET (PostgreSQL/MySQL) SELECT * FROM t1 LIMIT 10 OFFSET 5; Pagination syntax in PostgreSQL/MySQL.
FETCH FIRST (SQL Server/Oracle) SELECT * FROM t1 ORDER BY col1 OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY; Pagination in SQL Server and Oracle 12c+.
DUAL Table (Oracle) SELECT 1 FROM DUAL; Dummy table for selecting constants in Oracle.
TEXT/JSON Column (PostgreSQL) SELECT col->>'key' FROM t1; JSON field access in PostgreSQL.
JSON_EXTRACT (MySQL) SELECT JSON_EXTRACT(json_col, '$.key') FROM t1; JSON field access in MySQL.
MERGE INTO target t USING source s ON (t.id=s.id) WHEN MATCHED THEN UPDATE SET t.col=s.col
MERGE (Oracle/SQL Server) WHEN NOT MATCHED THEN INSERT (col) VALUES (s.col); Upsert syntax in Oracle/SQL Server.
INSERT ... ON DUPLICATE KEY UPDATE
(MySQL) INSERT INTO t1 (id, col) VALUES (1,'A') ON DUPLICATE KEY UPDATE col='A'; MySQL upsert syntax.

INSERT ... ON CONFLICT (PostgreSQL) INSERT INTO t1 (id, col) VALUES (1,'A') ON CONFLICT (id) DO UPDATE SET col='A'; PostgreSQL upsert syntax.