SQLancer
Practice
⚡ 200 Cheat Codes Available

Official Database Cheat Codes

20 essential cheat codes for each of our 10 database engines. Instant copy-paste reference for querying, schema design, and administration.

🐘
PostgreSQL
20 Cheat Codes
 🐎
MySQL
20 Cheat Codes
 ðŸŠķ
SQLite
20 Cheat Codes
 ðŸ”ī
Oracle Database
20 Cheat Codes
 ðŸ’ŧ
Microsoft SQL Server
20 Cheat Codes
 🍃
MongoDB
20 Cheat Codes
 ðŸ”ī
Redis
20 Cheat Codes
 ❄ïļ
Snowflake
20 Cheat Codes
 🍒
ClickHouse
20 Cheat Codes
 ðŸĶ†
DuckDB
20 Cheat Codes
🐘 PostgreSQL SELECT DQL

Retrieve columns from table.

Generic Syntax:
SELECT col1, col2 FROM tbl;
Working Example:
SELECT id, email FROM users;
🐘 PostgreSQL WHERE DQL

Filter query output rows.

Generic Syntax:
SELECT * FROM tbl WHERE col = val;
Working Example:
SELECT * FROM orders WHERE total > 100;
🐘 PostgreSQL JOIN Joins

Combine rows from two tables on key.

Generic Syntax:
SELECT * FROM t1 JOIN t2 ON t1.id = t2.fk;
Working Example:
SELECT u.name, o.id FROM users u JOIN orders o ON u.id = o.user_id;
🐘 PostgreSQL GROUP BY Aggregations

Group rows for aggregate computations.

Generic Syntax:
SELECT col, COUNT(*) FROM tbl GROUP BY col;
Working Example:
SELECT dept, COUNT(*) FROM emp GROUP BY dept;
🐘 PostgreSQL HAVING Aggregations

Filter aggregated grouped output.

Generic Syntax:
SELECT col, AVG(x) FROM tbl GROUP BY col HAVING AVG(x) > 50;
Working Example:
SELECT dept, AVG(sal) FROM emp GROUP BY dept HAVING AVG(sal) > 50000;
🐘 PostgreSQL INSERT INTO DML

Add new row records into table.

Generic Syntax:
INSERT INTO tbl (col1) VALUES (val1);
Working Example:
INSERT INTO users (email) VALUES ('a@b.com');
🐘 PostgreSQL UPDATE SET DML

Modify existing table column values.

Generic Syntax:
UPDATE tbl SET col = val WHERE condition;
Working Example:
UPDATE users SET status = 'active' WHERE id = 1;
🐘 PostgreSQL DELETE FROM DML

Remove records matching condition.

Generic Syntax:
DELETE FROM tbl WHERE condition;
Working Example:
DELETE FROM sessions WHERE expired = true;
🐘 PostgreSQL ON CONFLICT (UPSERT) DML

Insert row or update on key conflict.

Generic Syntax:
INSERT INTO tbl (id, val) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;
Working Example:
INSERT INTO stats (id, count) VALUES (1, 1) ON CONFLICT (id) DO UPDATE SET count = stats.count + 1;
🐘 PostgreSQL RETURNING DML

Return modified column values directly.

Generic Syntax:
INSERT INTO tbl (col) VALUES (val) RETURNING id;
Working Example:
INSERT INTO users (email) VALUES ('a@b.com') RETURNING id, created_at;
🐘 PostgreSQL JSONB Query (->>) JSONB

Extract JSON field as text string.

Generic Syntax:
SELECT data->>'field' FROM tbl;
Working Example:
SELECT metadata->>'author' FROM docs;
🐘 PostgreSQL JSONB Contains (@>) JSONB

Check if JSONB contains key-value pair.

Generic Syntax:
SELECT * FROM tbl WHERE data @> '{"key": "val"}';
Working Example:
SELECT * FROM events WHERE payload @> '{"type": "click"}';
🐘 PostgreSQL STRING_AGG Aggregations

Concatenate group strings with delimiter.

Generic Syntax:
SELECT STRING_AGG(col, ', ') FROM tbl GROUP BY grp;
Working Example:
SELECT dept, STRING_AGG(name, ', ') FROM emp GROUP BY dept;
🐘 PostgreSQL EXPLAIN ANALYZE Performance

Execute query and return real runtime stats.

Generic Syntax:
EXPLAIN ANALYZE SELECT * FROM tbl WHERE col = val;
Working Example:
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';
🐘 PostgreSQL CREATE INDEX DDL

Build B-Tree index to accelerate lookups.

Generic Syntax:
CREATE INDEX idx_name ON tbl(col);
Working Example:
CREATE INDEX idx_users_email ON users(email);
🐘 PostgreSQL CREATE GIN INDEX DDL

Build GIN index for JSONB or array columns.

Generic Syntax:
CREATE INDEX idx_name ON tbl USING GIN (json_col);
Working Example:
CREATE INDEX idx_docs_meta ON docs USING GIN (metadata);
🐘 PostgreSQL WITH RECURSIVE (CTE) Advanced

Query hierarchical org or tree data.

Generic Syntax:
WITH RECURSIVE cte AS (...) SELECT * FROM cte;
Working Example:
WITH RECURSIVE tree AS (SELECT id FROM emp UNION ALL SELECT e.id FROM emp e JOIN tree t ON e.mgr_id = t.id) SELECT * FROM tree;
🐘 PostgreSQL ROW_NUMBER() OVER Window

Assign row index numbers within partition.

Generic Syntax:
SELECT col, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY val) FROM tbl;
Working Example:
SELECT name, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY sal DESC) FROM emp;
🐘 PostgreSQL VACUUM ANALYZE Maintenance

Reclaim dead tuples and update statistics.

Generic Syntax:
VACUUM ANALYZE tbl_name;
Working Example:
VACUUM ANALYZE users;
🐘 PostgreSQL TRUNCATE TABLE DDL

Rapidly wipe all rows from table.

Generic Syntax:
TRUNCATE TABLE tbl RESTART IDENTITY;
Working Example:
TRUNCATE TABLE staging_events RESTART IDENTITY;
🐎 MySQL SELECT DQL

Retrieve columns from MySQL table.

Generic Syntax:
SELECT col1, col2 FROM tbl;
Working Example:
SELECT id, username FROM users;
🐎 MySQL WHERE DQL

Filter output matching condition.

Generic Syntax:
SELECT * FROM tbl WHERE col = val;
Working Example:
SELECT * FROM products WHERE price > 50;
🐎 MySQL INNER JOIN Joins

Match rows across joined tables.

Generic Syntax:
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.fk;
Working Example:
SELECT u.name, o.id FROM users u INNER JOIN orders o ON u.id = o.user_id;
🐎 MySQL LEFT JOIN Joins

Keep all left rows with matching right rows.

Generic Syntax:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.fk;
Working Example:
SELECT u.name, o.id FROM users u LEFT JOIN orders o ON u.id = o.user_id;
🐎 MySQL GROUP BY Aggregations

Group rows for calculation.

Generic Syntax:
SELECT col, COUNT(*) FROM tbl GROUP BY col;
Working Example:
SELECT status, COUNT(*) FROM orders GROUP BY status;
🐎 MySQL HAVING Aggregations

Filter aggregated results.

Generic Syntax:
SELECT col, SUM(x) FROM tbl GROUP BY col HAVING SUM(x) > 1000;
Working Example:
SELECT dept, SUM(sales) FROM rep GROUP BY dept HAVING SUM(sales) > 10000;
🐎 MySQL INSERT INTO DML

Add new records into table.

Generic Syntax:
INSERT INTO tbl (col1) VALUES (val1);
Working Example:
INSERT INTO customers (name) VALUES ('Acme Corp');
🐎 MySQL UPDATE SET DML

Modify column values in rows.

Generic Syntax:
UPDATE tbl SET col = val WHERE condition;
Working Example:
UPDATE orders SET status = 'shipped' WHERE id = 101;
🐎 MySQL DELETE FROM DML

Remove records from table.

Generic Syntax:
DELETE FROM tbl WHERE condition;
Working Example:
DELETE FROM logs WHERE created_at < '2026-01-01';
🐎 MySQL ON DUPLICATE KEY UPDATE DML

Execute upsert on unique key duplicate.

Generic Syntax:
INSERT INTO tbl (id, val) VALUES (1, 10) ON DUPLICATE KEY UPDATE val = val + 10;
Working Example:
INSERT INTO views (page_id, count) VALUES (5, 1) ON DUPLICATE KEY UPDATE count = count + 1;
🐎 MySQL GROUP_CONCAT Aggregations

Combine text values into single string.

Generic Syntax:
SELECT GROUP_CONCAT(col SEPARATOR ', ') FROM tbl GROUP BY grp;
Working Example:
SELECT dept_id, GROUP_CONCAT(name SEPARATOR ', ') FROM emp GROUP BY dept_id;
🐎 MySQL DATE_FORMAT Functions

Format DATETIME into custom string.

Generic Syntax:
SELECT DATE_FORMAT(dt, '%Y-%m-%d') FROM tbl;
Working Example:
SELECT order_id, DATE_FORMAT(created_at, '%W, %M %e') FROM orders;
🐎 MySQL LIMIT / OFFSET Pagination

Paginate result rows.

Generic Syntax:
SELECT * FROM tbl ORDER BY col LIMIT count OFFSET skip;
Working Example:
SELECT * FROM items ORDER BY id DESC LIMIT 10 OFFSET 20;
🐎 MySQL CREATE TABLE DDL

Define new table with AUTO_INCREMENT key.

Generic Syntax:
CREATE TABLE tbl (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));
Working Example:
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL);
🐎 MySQL ALTER TABLE DDL

Add or drop columns from table.

Generic Syntax:
ALTER TABLE tbl ADD COLUMN col datatype;
Working Example:
ALTER TABLE users ADD COLUMN age INT;
🐎 MySQL CREATE INDEX DDL

Build index on table columns.

Generic Syntax:
CREATE INDEX idx_name ON tbl(col);
Working Example:
CREATE INDEX idx_user_email ON users(email);
🐎 MySQL EXPLAIN FORMAT=JSON Performance

Analyze MySQL optimizer execution details.

Generic Syntax:
EXPLAIN FORMAT=JSON SELECT * FROM tbl WHERE col = val;
Working Example:
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 42;
🐎 MySQL SHOW PROCESSLIST DBA

Inspect active database connections.

Generic Syntax:
SHOW FULL PROCESSLIST;
Working Example:
SHOW FULL PROCESSLIST;
🐎 MySQL KILL PROCESS DBA

Terminate stuck client thread ID.

Generic Syntax:
KILL process_id;
Working Example:
KILL 1234;
🐎 MySQL JSON_EXTRACT Functions

Parse nested JSON column property.

Generic Syntax:
SELECT JSON_UNQUOTE(JSON_EXTRACT(json_col, '$.key')) FROM tbl;
Working Example:
SELECT JSON_UNQUOTE(JSON_EXTRACT(meta, '$.role')) FROM users;
ðŸŠķ SQLite SELECT DQL

Fetch columns from SQLite database.

Generic Syntax:
SELECT col1, col2 FROM tbl;
Working Example:
SELECT id, name FROM notes;
ðŸŠķ SQLite WHERE DQL

Filter output rows.

Generic Syntax:
SELECT * FROM tbl WHERE col = val;
Working Example:
SELECT * FROM tasks WHERE completed = 1;
ðŸŠķ SQLite JOIN Joins

Join two tables on condition.

Generic Syntax:
SELECT * FROM t1 JOIN t2 ON t1.id = t2.fk;
Working Example:
SELECT n.title, c.name FROM notes n JOIN categories c ON n.cat_id = c.id;
ðŸŠķ SQLite GROUP BY Aggregations

Group rows for aggregate stats.

Generic Syntax:
SELECT col, COUNT(*) FROM tbl GROUP BY col;
Working Example:
SELECT category, COUNT(*) FROM tasks GROUP BY category;
ðŸŠķ SQLite ORDER BY DQL

Sort output rows.

Generic Syntax:
SELECT * FROM tbl ORDER BY col DESC;
Working Example:
SELECT * FROM notes ORDER BY updated_at DESC;
ðŸŠķ SQLite LIMIT OFFSET Pagination

Paginate SQLite query output.

Generic Syntax:
SELECT * FROM tbl LIMIT count OFFSET skip;
Working Example:
SELECT * FROM logs LIMIT 20 OFFSET 40;
ðŸŠķ SQLite INSERT INTO DML

Add new row into table.

Generic Syntax:
INSERT INTO tbl (col) VALUES (val);
Working Example:
INSERT INTO notes (title) VALUES ('Meeting Summary');
ðŸŠķ SQLite UPDATE SET DML

Modify column values in rows.

Generic Syntax:
UPDATE tbl SET col = val WHERE condition;
Working Example:
UPDATE tasks SET completed = 1 WHERE id = 5;
ðŸŠķ SQLite DELETE FROM DML

Remove records matching filter.

Generic Syntax:
DELETE FROM tbl WHERE condition;
Working Example:
DELETE FROM temp_files WHERE age > 7;
ðŸŠķ SQLite ON CONFLICT DO UPDATE DML

Execute upsert on unique key collision.

Generic Syntax:
INSERT INTO tbl (id, val) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET val = excluded.val;
Working Example:
INSERT INTO kv (key, val) VALUES ('theme', 'dark') ON CONFLICT (key) DO UPDATE SET val = excluded.val;
ðŸŠķ SQLite PRAGMA table_info Introspection

Inspect table columns and data types.

Generic Syntax:
PRAGMA table_info('tbl');
Working Example:
PRAGMA table_info('users');
ðŸŠķ SQLite PRAGMA journal_mode Storage

Enable high-concurrency WAL mode.

Generic Syntax:
PRAGMA journal_mode = WAL;
Working Example:
PRAGMA journal_mode = WAL;
ðŸŠķ SQLite VACUUM Maintenance

Reclaim unused file disk space.

Generic Syntax:
VACUUM;
Working Example:
VACUUM;
ðŸŠķ SQLite CREATE VIRTUAL TABLE (FTS5) FTS5

Build full-text search index.

Generic Syntax:
CREATE VIRTUAL TABLE fts_tbl USING fts5(col1, col2);
Working Example:
CREATE VIRTUAL TABLE docs_fts USING fts5(title, body);
ðŸŠķ SQLite FTS5 MATCH FTS5

Execute full-text search query.

Generic Syntax:
SELECT * FROM fts_tbl WHERE fts_tbl MATCH 'query';
Working Example:
SELECT * FROM docs_fts WHERE docs_fts MATCH 'sqlite OR database';
ðŸŠķ SQLite json_extract() Functions

Extract JSON field value.

Generic Syntax:
SELECT json_extract(json_col, '$.key') FROM tbl;
Working Example:
SELECT json_extract(data, '$.user.name') FROM events;
ðŸŠķ SQLite ATTACH DATABASE Multi-DB

Attach secondary database file.

Generic Syntax:
ATTACH DATABASE 'file.db' AS alias;
Working Example:
ATTACH DATABASE 'archive.db' AS archive;
ðŸŠķ SQLite datetime() Functions

Manipulate timestamps and dates.

Generic Syntax:
SELECT datetime('now', 'start of month');
Working Example:
SELECT datetime('now', '-7 days');
ðŸŠķ SQLite PRAGMA foreign_keys Integrity

Enable foreign key constraint checks.

Generic Syntax:
PRAGMA foreign_keys = ON;
Working Example:
PRAGMA foreign_keys = ON;
ðŸŠķ SQLite STRICT TABLE DDL

Enforce strict column data types.

Generic Syntax:
CREATE TABLE tbl (id INT PRIMARY KEY) STRICT;
Working Example:
CREATE TABLE accounts (id INT PRIMARY KEY, balance REAL) STRICT;
ðŸ”ī Oracle Database SELECT DQL

Retrieve columns from Oracle table.

Generic Syntax:
SELECT col1, col2 FROM tbl;
Working Example:
SELECT employee_id, last_name FROM employees;
ðŸ”ī Oracle Database WHERE DQL

Filter row results.

Generic Syntax:
SELECT * FROM tbl WHERE col = val;
Working Example:
SELECT * FROM employees WHERE department_id = 10;
ðŸ”ī Oracle Database JOIN Joins

Match records across joined tables.

Generic Syntax:
SELECT * FROM t1 JOIN t2 ON t1.id = t2.fk;
Working Example:
SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;
ðŸ”ī Oracle Database GROUP BY Aggregations

Group rows for calculation.

Generic Syntax:
SELECT col, COUNT(*) FROM tbl GROUP BY col;
Working Example:
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
ðŸ”ī Oracle Database HAVING Aggregations

Filter aggregated group calculations.

Generic Syntax:
SELECT col, AVG(sal) FROM tbl GROUP BY col HAVING AVG(sal) > 5000;
Working Example:
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 8000;
ðŸ”ī Oracle Database INSERT INTO DML

Add new row into Oracle table.

Generic Syntax:
INSERT INTO tbl (col) VALUES (val);
Working Example:
INSERT INTO departments (department_id, department_name) VALUES (90, 'Executive');
ðŸ”ī Oracle Database UPDATE SET DML

Modify column values.

Generic Syntax:
UPDATE tbl SET col = val WHERE condition;
Working Example:
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 20;
ðŸ”ī Oracle Database DELETE FROM DML

Remove records matching filter.

Generic Syntax:
DELETE FROM tbl WHERE condition;
Working Example:
DELETE FROM job_history WHERE end_date < '2020-01-01';
ðŸ”ī Oracle Database MERGE INTO (UPSERT) DML

ANSI standard MERGE upsert statement.

Generic Syntax:
MERGE INTO target t USING source s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.val = s.val WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);
Working Example:
MERGE INTO emp_target t USING emp_stage s ON (t.emp_id = s.emp_id) WHEN MATCHED THEN UPDATE SET t.sal = s.sal WHEN NOT MATCHED THEN INSERT (emp_id, sal) VALUES (s.emp_id, s.sal);
ðŸ”ī Oracle Database NVL / NVL2 Functions

Replace NULL values with default fallback.

Generic Syntax:
SELECT NVL(col, default_val) FROM tbl;
Working Example:
SELECT last_name, NVL(commission_pct, 0) FROM employees;
ðŸ”ī Oracle Database FETCH FIRST N ROWS Pagination

Standard ANSI pagination syntax in Oracle 12c+.

Generic Syntax:
SELECT * FROM tbl ORDER BY col DESC FETCH FIRST n ROWS ONLY;
Working Example:
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;
ðŸ”ī Oracle Database LISTAGG Aggregations

Concatenate string values across rows.

Generic Syntax:
SELECT LISTAGG(col, '; ') WITHIN GROUP (ORDER BY col) FROM tbl GROUP BY grp;
Working Example:
SELECT department_id, LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) FROM employees GROUP BY department_id;
ðŸ”ī Oracle Database ROWNUM Pagination

Legacy pseudo-column row limiter.

Generic Syntax:
SELECT * FROM tbl WHERE ROWNUM <= n;
Working Example:
SELECT * FROM employees WHERE ROWNUM <= 5;
ðŸ”ī Oracle Database START WITH CONNECT BY Hierarchical

Traverse parent-child org hierarchies.

Generic Syntax:
SELECT LEVEL, col FROM tbl START WITH parent IS NULL CONNECT BY PRIOR id = parent;
Working Example:
SELECT LEVEL, employee_id, last_name FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id;
ðŸ”ī Oracle Database CREATE SEQUENCE Sequences

Build sequence object for auto keys.

Generic Syntax:
CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1;
Working Example:
CREATE SEQUENCE emp_seq START WITH 1000 INCREMENT BY 1;
ðŸ”ī Oracle Database NEXTVAL / CURRVAL Sequences

Fetch next unique value from sequence.

Generic Syntax:
SELECT seq_name.NEXTVAL FROM dual;
Working Example:
INSERT INTO employees (id, name) VALUES (emp_seq.NEXTVAL, 'Alex');
ðŸ”ī Oracle Database FLASHBACK QUERY Recovery

Query past historical data state.

Generic Syntax:
SELECT * FROM tbl AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);
Working Example:
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE);
ðŸ”ī Oracle Database PIVOT Analytics

Transform rows into reporting columns.

Generic Syntax:
SELECT * FROM (SELECT grp, col, val FROM tbl) PIVOT (SUM(val) FOR col IN ('A', 'B'));
Working Example:
SELECT * FROM (SELECT department_id, salary FROM employees) PIVOT (SUM(salary) FOR department_id IN (10, 20, 30));
ðŸ”ī Oracle Database CREATE MATERIALIZED VIEW Views

Create pre-computed query snapshot view.

Generic Syntax:
CREATE MATERIALIZED VIEW mv_name AS SELECT ...;
Working Example:
CREATE MATERIALIZED VIEW dept_summary AS SELECT department_id, COUNT(*) cnt FROM employees GROUP BY department_id;
ðŸ”ī Oracle Database EXPLAIN PLAN FOR Performance

Generate optimizer plan into plan_table.

Generic Syntax:
EXPLAIN PLAN FOR SELECT * FROM tbl WHERE col = val;
Working Example:
EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 50;
ðŸ’ŧ Microsoft SQL Server SELECT TOP (N) DQL

Restrict returned rows in T-SQL.

Generic Syntax:
SELECT TOP (n) col1 FROM dbo.tbl;
Working Example:
SELECT TOP (10) OrderID, TotalAmount FROM dbo.Orders ORDER BY OrderDate DESC;
ðŸ’ŧ Microsoft SQL Server WHERE DQL

Filter row outputs.

Generic Syntax:
SELECT * FROM dbo.tbl WHERE col = val;
Working Example:
SELECT * FROM dbo.Customers WHERE Country = 'USA';
ðŸ’ŧ Microsoft SQL Server JOIN Joins

Match records across T-SQL tables.

Generic Syntax:
SELECT * FROM t1 JOIN t2 ON t1.id = t2.fk;
Working Example:
SELECT c.CompanyName, o.OrderID FROM dbo.Customers c JOIN dbo.Orders o ON c.CustomerID = o.CustomerID;
ðŸ’ŧ Microsoft SQL Server GROUP BY Aggregations

Group rows for aggregate totals.

Generic Syntax:
SELECT col, COUNT(*) FROM dbo.tbl GROUP BY col;
Working Example:
SELECT CustomerID, COUNT(*) AS OrderCount FROM dbo.Orders GROUP BY CustomerID;
ðŸ’ŧ Microsoft SQL Server HAVING Aggregations

Filter aggregated groups.

Generic Syntax:
SELECT col, SUM(val) FROM dbo.tbl GROUP BY col HAVING SUM(val) > 1000;
Working Example:
SELECT CustomerID, SUM(TotalAmount) FROM dbo.Orders GROUP BY CustomerID HAVING SUM(TotalAmount) > 5000;
ðŸ’ŧ Microsoft SQL Server INSERT INTO DML

Add new record into table.

Generic Syntax:
INSERT INTO dbo.tbl (col) VALUES (val);
Working Example:
INSERT INTO dbo.Categories (CategoryName) VALUES ('Hardware');
ðŸ’ŧ Microsoft SQL Server UPDATE SET DML

Modify column values.

Generic Syntax:
UPDATE dbo.tbl SET col = val WHERE condition;
Working Example:
UPDATE dbo.Products SET UnitPrice = UnitPrice * 1.05 WHERE CategoryID = 2;
ðŸ’ŧ Microsoft SQL Server DELETE FROM DML

Remove records matching condition.

Generic Syntax:
DELETE FROM dbo.tbl WHERE condition;
Working Example:
DELETE FROM dbo.Logs WHERE LogDate < '2026-01-01';
ðŸ’ŧ Microsoft SQL Server MERGE INTO (UPSERT) DML

T-SQL MERGE statement upsert.

Generic Syntax:
MERGE INTO dbo.Target t USING dbo.Source s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.val = s.val WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);
Working Example:
MERGE INTO dbo.TargetUsers t USING dbo.SourceUsers s ON (t.UserID = s.UserID) WHEN MATCHED THEN UPDATE SET t.Email = s.Email WHEN NOT MATCHED THEN INSERT (UserID, Email) VALUES (s.UserID, s.Email);
ðŸ’ŧ Microsoft SQL Server CROSS APPLY Joins

Invoke correlated table function per row.

Generic Syntax:
SELECT * FROM t1 CROSS APPLY dbo.func(t1.id);
Working Example:
SELECT c.CustomerID, o.OrderID FROM dbo.Customers c CROSS APPLY (SELECT TOP (1) OrderID FROM dbo.Orders WHERE CustomerID = c.CustomerID ORDER BY OrderDate DESC) o;
ðŸ’ŧ Microsoft SQL Server OUTER APPLY Joins

Invoke correlated table function preserving NULLs.

Generic Syntax:
SELECT * FROM t1 OUTER APPLY dbo.func(t1.id);
Working Example:
SELECT c.CustomerID, o.OrderID FROM dbo.Customers c OUTER APPLY (SELECT TOP (1) OrderID FROM dbo.Orders WHERE CustomerID = c.CustomerID ORDER BY OrderDate DESC) o;
ðŸ’ŧ Microsoft SQL Server STRING_AGG Aggregations

Concatenate text with specified separator.

Generic Syntax:
SELECT STRING_AGG(col, ', ') WITHIN GROUP (ORDER BY col) FROM dbo.tbl GROUP BY grp;
Working Example:
SELECT DepartmentID, STRING_AGG(FirstName, ', ') WITHIN GROUP (ORDER BY FirstName) FROM dbo.Employees GROUP BY DepartmentID;
ðŸ’ŧ Microsoft SQL Server TRY_CAST / TRY_CONVERT Functions

Safely convert types returning NULL on failure.

Generic Syntax:
SELECT TRY_CAST(col AS INT) FROM dbo.tbl;
Working Example:
SELECT TRY_CAST(RawString AS INT) FROM dbo.StagingData;
ðŸ’ŧ Microsoft SQL Server IDENTITY(1,1) Identity

Auto-generate unique numeric keys.

Generic Syntax:
CREATE TABLE dbo.tbl (ID INT IDENTITY(1,1) PRIMARY KEY);
Working Example:
CREATE TABLE dbo.Orders (OrderID INT IDENTITY(1,1) PRIMARY KEY, Total DECIMAL(18,2));
ðŸ’ŧ Microsoft SQL Server #temp local table Temp Tables

Create session-private temporary table.

Generic Syntax:
CREATE TABLE #TempTable (id INT);
Working Example:
CREATE TABLE #MonthlyStats (MonthID INT, TotalRev DECIMAL(18,2));
ðŸ’ŧ Microsoft SQL Server ##temp global table Temp Tables

Create global session-shared temporary table.

Generic Syntax:
CREATE TABLE ##GlobalTemp (id INT);
Working Example:
CREATE TABLE ##SharedCache (KeyID INT, Val VARCHAR(100));
ðŸ’ŧ Microsoft SQL Server ROW_NUMBER() OVER Window

Assign row index inside partition.

Generic Syntax:
SELECT col, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY val) FROM dbo.tbl;
Working Example:
SELECT CustomerID, OrderID, ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate DESC) FROM dbo.Orders;
ðŸ’ŧ Microsoft SQL Server PIVOT Analytics

Rotate row values into output columns.

Generic Syntax:
SELECT * FROM (SELECT grp, col, val FROM dbo.tbl) PIVOT (SUM(val) FOR col IN ([A], [B])) pvt;
Working Example:
SELECT Year, [1] AS Jan, [2] AS Feb FROM (SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, Total FROM dbo.Orders) Src PIVOT (SUM(Total) FOR Month IN ([1], [2])) pvt;
ðŸ’ŧ Microsoft SQL Server SET IDENTITY_INSERT Identity

Override automatic identity constraint for inserts.

Generic Syntax:
SET IDENTITY_INSERT dbo.tbl ON; INSERT ... SET IDENTITY_INSERT dbo.tbl OFF;
Working Example:
SET IDENTITY_INSERT dbo.Customers ON; INSERT INTO dbo.Customers (CustomerID, CompanyName) VALUES (999, 'Acme'); SET IDENTITY_INSERT dbo.Customers OFF;
ðŸ’ŧ Microsoft SQL Server sys.dm_db_index_physical_stats DMV DBA

Inspect index fragmentation levels.

Generic Syntax:
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED');
Working Example:
SELECT object_name(object_id) AS TableName, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED');
🍃 MongoDB find() CRUD

Query documents matching filter criteria.

Generic Syntax:
db.collection.find({ key: "value" });
Working Example:
db.users.find({ status: "active" });
🍃 MongoDB findOne() CRUD

Retrieve single document matching filter.

Generic Syntax:
db.collection.findOne({ _id: ObjectId("...") });
Working Example:
db.users.findOne({ email: "alex@dev.com" });
🍃 MongoDB insertOne() CRUD

Insert single document into collection.

Generic Syntax:
db.collection.insertOne({ key: "val" });
Working Example:
db.orders.insertOne({ userId: 10, total: 99.99, createdAt: new Date() });
🍃 MongoDB insertMany() CRUD

Insert multiple documents in bulk.

Generic Syntax:
db.collection.insertMany([ { doc1 }, { doc2 } ]);
Working Example:
db.tags.insertMany([ { name: "sql" }, { name: "nosql" } ]);
🍃 MongoDB updateOne() ($set) CRUD

Modify fields in single matching document.

Generic Syntax:
db.collection.updateOne({ filter }, { $set: { key: "val" } });
Working Example:
db.users.updateOne({ _id: 1 }, { $set: { status: "premium" } });
🍃 MongoDB updateMany() ($inc) CRUD

Increment numeric field across multiple documents.

Generic Syntax:
db.collection.updateMany({ filter }, { $inc: { count: 1 } });
Working Example:
db.stats.updateMany({ active: true }, { $inc: { views: 1 } });
🍃 MongoDB deleteOne() CRUD

Remove single document from collection.

Generic Syntax:
db.collection.deleteOne({ _id: val });
Working Example:
db.sessions.deleteOne({ _id: "sess_123" });
🍃 MongoDB deleteMany() CRUD

Remove all documents matching filter.

Generic Syntax:
db.collection.deleteMany({ filter });
Working Example:
db.logs.deleteMany({ level: "debug" });
🍃 MongoDB aggregate ($match & $group) Aggregation

Multi-stage pipeline filter and group totals.

Generic Syntax:
db.collection.aggregate([ { $match: ... }, { $group: ... } ]);
Working Example:
db.orders.aggregate([ { $match: { status: "completed" } }, { $group: { _id: "$userId", total: { $sum: "$amount" } } } ]);
🍃 MongoDB aggregate ($lookup) Aggregation

Left outer join secondary collection.

Generic Syntax:
db.collection.aggregate([ { $lookup: { from: "col", localField: "a", foreignField: "b", as: "out" } } ]);
Working Example:
db.orders.aggregate([ { $lookup: { from: "users", localField: "userId", foreignField: "_id", as: "user" } } ]);
🍃 MongoDB aggregate ($unwind) Aggregation

Deconstruct array field into document rows.

Generic Syntax:
db.collection.aggregate([ { $unwind: "$arrayField" } ]);
Working Example:
db.articles.aggregate([ { $unwind: "$tags" } ]);
🍃 MongoDB createIndex (2dsphere) Indexing

Build spatial index for geospatial queries.

Generic Syntax:
db.collection.createIndex({ location: "2dsphere" });
Working Example:
db.places.createIndex({ location: "2dsphere" });
🍃 MongoDB createIndex (Text) Indexing

Build text search index over document fields.

Generic Syntax:
db.collection.createIndex({ field: "text" });
Working Example:
db.posts.createIndex({ title: "text", content: "text" });
🍃 MongoDB createIndex (TTL Expire) Indexing

Set automatic document deletion TTL timestamp.

Generic Syntax:
db.collection.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 });
Working Example:
db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 86400 });
🍃 MongoDB dropIndex() Indexing

Remove specified index from collection.

Generic Syntax:
db.collection.dropIndex("index_name");
Working Example:
db.users.dropIndex("email_1");
🍃 MongoDB countDocuments() CRUD

Count total documents matching query filter.

Generic Syntax:
db.collection.countDocuments({ filter });
Working Example:
db.users.countDocuments({ role: "admin" });
🍃 MongoDB sort() & limit() & skip() Pagination

Paginate document search query output.

Generic Syntax:
db.collection.find().sort({ field: -1 }).skip(20).limit(10);
Working Example:
db.products.find().sort({ price: 1 }).skip(0).limit(10);
🍃 MongoDB upsert: true CRUD

Insert document if no update match found.

Generic Syntax:
db.collection.updateOne({ filter }, { $set: ... }, { upsert: true });
Working Example:
db.stats.updateOne({ date: "2026-01-01" }, { $inc: { views: 1 } }, { upsert: true });
🍃 MongoDB jsonSchema Validator Schema

Enforce strict JSON Schema validation rules.

Generic Syntax:
db.createCollection("name", { validator: { $jsonSchema: ... } });
Working Example:
db.createCollection("users", { validator: { $jsonSchema: { required: ["email"] } } });
🍃 MongoDB explain("executionStats") Performance

Inspect query execution statistics and index usage.

Generic Syntax:
db.collection.find({ filter }).explain("executionStats");
Working Example:
db.orders.find({ status: "pending" }).explain("executionStats");
ðŸ”ī Redis SET / GET Strings

Store and retrieve key-value string pairs.

Generic Syntax:
SET key "value" / GET key
Working Example:
SET user:42 "Alex" / GET user:42
ðŸ”ī Redis SET EX (TTL Expiration) Strings

Set key-value pair with TTL expiration seconds.

Generic Syntax:
SET key "value" EX seconds
Working Example:
SET cache:session "token_123" EX 3600
ðŸ”ī Redis HSET / HGETALL Hashes

Store and fetch structured hash fields.

Generic Syntax:
HSET key field "val" / HGETALL key
Working Example:
HSET user:101 name "Alex" email "a@b.com" / HGETALL user:101
ðŸ”ī Redis LPUSH / RPOP Lists

Push element to list head and pop from tail (queue).

Generic Syntax:
LPUSH key "val" / RPOP key
Working Example:
LPUSH jobs "job_99" / RPOP jobs
ðŸ”ī Redis SADD / SMEMBERS Sets

Add unique members to set and fetch all members.

Generic Syntax:
SADD key "member" / SMEMBERS key
Working Example:
SADD tags:post:10 "sql" "nosql" / SMEMBERS tags:post:10
ðŸ”ī Redis ZADD / ZREVRANGE Sorted Sets

Add member with score and fetch top leaderboard ranks.

Generic Syntax:
ZADD key score "member" / ZREVRANGE key 0 -1 WITHSCORES
Working Example:
ZADD leaderboard 2500 "PlayerA" 1800 "PlayerB" / ZREVRANGE leaderboard 0 2 WITHSCORES
ðŸ”ī Redis PUBLISH / SUBSCRIBE Pub/Sub

Publish message to channel and subscribe.

Generic Syntax:
PUBLISH channel "msg" / SUBSCRIBE channel
Working Example:
PUBLISH news:updates "New release live!"
ðŸ”ī Redis INCR / DECR Strings

Atomically increment or decrement integer key.

Generic Syntax:
INCR key / DECR key
Working Example:
INCR page:views:42
ðŸ”ī Redis EXPIRE / TTL Expiration

Set TTL expiration seconds on key and check remaining TTL.

Generic Syntax:
EXPIRE key seconds / TTL key
Working Example:
EXPIRE rate:limit:ip 60 / TTL rate:limit:ip
ðŸ”ī Redis DEL Keys

Delete specified key from Redis memory.

Generic Syntax:
DEL key1 key2
Working Example:
DEL cache:user:42
ðŸ”ī Redis EXISTS Keys

Check if key exists in memory.

Generic Syntax:
EXISTS key
Working Example:
EXISTS session:active:10
ðŸ”ī Redis SCAN Keys

Iterate over database keys safely without blocking.

Generic Syntax:
SCAN cursor MATCH pattern COUNT n
Working Example:
SCAN 0 MATCH user:* COUNT 100
ðŸ”ī Redis MULTI / EXEC Transactions

Execute atomic block of commands sequentially.

Generic Syntax:
MULTI ... commands ... EXEC
Working Example:
MULTI; INCR visits; EXPIRE visits 60; EXEC;
ðŸ”ī Redis MSET / MGET Strings

Set and get multiple key-value pairs simultaneously.

Generic Syntax:
MSET k1 "v1" k2 "v2" / MGET k1 k2
Working Example:
MSET user:1 "A" user:2 "B" / MGET user:1 user:2
ðŸ”ī Redis PFADD / PFCOUNT HyperLogLog

Track unique cardinality with 12KB memory footprint.

Generic Syntax:
PFADD key "val" / PFCOUNT key
Working Example:
PFADD uv:2026-01-01 "ip_1" "ip_2" / PFCOUNT uv:2026-01-01
ðŸ”ī Redis GEOADD / GEODIST Geospatial

Store spatial coordinates and measure distance.

Generic Syntax:
GEOADD key long lat "member" / GEODIST key m1 m2 km
Working Example:
GEOADD cities -73.98 40.74 "NYC" -118.24 34.05 "LA" / GEODIST cities NYC LA km
ðŸ”ī Redis EVAL (Lua Script) Scripting

Execute atomic server-side Lua script.

Generic Syntax:
EVAL "script" numkeys key1 arg1
Working Example:
EVAL "return redis.call('get', KEYS[1])" 1 mykey
ðŸ”ī Redis FLUSHALL Administration

Wipe all keys from all Redis databases.

Generic Syntax:
FLUSHALL [ASYNC]
Working Example:
FLUSHALL ASYNC
ðŸ”ī Redis INFO memory DBA

Inspect memory consumption and fragmentation.

Generic Syntax:
INFO memory
Working Example:
INFO memory
ðŸ”ī Redis CONFIG GET / SET Administration

Read or alter Redis runtime settings.

Generic Syntax:
CONFIG GET param / CONFIG SET param val
Working Example:
CONFIG GET maxmemory / CONFIG SET maxmemory 2gb
❄ïļ Snowflake SELECT DQL

Retrieve analytical column datasets.

Generic Syntax:
SELECT col1, col2 FROM tbl;
Working Example:
SELECT user_id, amount FROM sales;
❄ïļ Snowflake WHERE DQL

Filter output rows.

Generic Syntax:
SELECT * FROM tbl WHERE col = val;
Working Example:
SELECT * FROM orders WHERE status = 'completed';
❄ïļ Snowflake JOIN Joins

Join two tables on condition.

Generic Syntax:
SELECT * FROM t1 JOIN t2 ON t1.id = t2.fk;
Working Example:
SELECT u.name, s.amount FROM users u JOIN sales s ON u.id = s.user_id;
❄ïļ Snowflake GROUP BY Aggregations

Group rows for aggregate totals.

Generic Syntax:
SELECT col, SUM(x) FROM tbl GROUP BY col;
Working Example:
SELECT region, SUM(amount) FROM sales GROUP BY region;
❄ïļ Snowflake QUALIFY SQL Extensions

Filter window function outputs directly without subquery wrappers.

Generic Syntax:
SELECT col, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY val DESC) as rn FROM tbl QUALIFY rn = 1;
Working Example:
SELECT employee_id, dept, salary, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) as rn FROM emp QUALIFY rn = 1;
❄ïļ Snowflake PIVOT Analytics

Rotate row values into reporting columns.

Generic Syntax:
SELECT * FROM tbl PIVOT (SUM(val) FOR col IN ('Q1', 'Q2'));
Working Example:
SELECT * FROM sales_summary PIVOT (SUM(amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));
❄ïļ Snowflake COPY INTO (Stage Load) Ingestion

Bulk load stage Parquet files into table.

Generic Syntax:
COPY INTO tbl FROM @stage/file.parquet FILE_FORMAT = (TYPE = 'PARQUET');
Working Example:
COPY INTO target_table FROM @my_s3_stage/data/ FILE_FORMAT = (TYPE = 'PARQUET');
❄ïļ Snowflake CREATE DATABASE CLONE Cloning

Create zero-copy instant database metadata clone.

Generic Syntax:
CREATE DATABASE clone_db CLONE src_db;
Working Example:
CREATE DATABASE prod_dev_sandbox CLONE production_db;
❄ïļ Snowflake Time Travel AT / BEFORE Data Recovery

Query past table data state prior to update or drop.

Generic Syntax:
SELECT * FROM tbl AT(OFFSET => -60*10);
Working Example:
SELECT * FROM orders AT(OFFSET => -60*5);
❄ïļ Snowflake UNDROP TABLE Data Recovery

Restore deleted table with zero data loss.

Generic Syntax:
UNDROP TABLE tbl_name;
Working Example:
UNDROP TABLE orders;
❄ïļ Snowflake VARIANT (JSON Extraction) Semi-Structured

Parse nested JSON VARIANT field value.

Generic Syntax:
SELECT raw_payload:user.id::INT FROM stage_tbl;
Working Example:
SELECT raw_payload:user.id::INT AS user_id FROM events_stage;
❄ïļ Snowflake LATERAL FLATTEN Semi-Structured

Unpack JSON arrays into individual output rows.

Generic Syntax:
SELECT id, f.value::STRING FROM tbl, LATERAL FLATTEN(input => col) f;
Working Example:
SELECT id, f.value::STRING AS tag FROM articles, LATERAL FLATTEN(input => metadata:tags) f;
❄ïļ Snowflake CREATE WAREHOUSE Compute

Build virtual warehouse compute cluster.

Generic Syntax:
CREATE WAREHOUSE wh_name WITH WAREHOUSE_SIZE = 'MEDIUM';
Working Example:
CREATE WAREHOUSE analytics_wh WITH WAREHOUSE_SIZE = 'MEDIUM' AUTO_SUSPEND = 300;
❄ïļ Snowflake ALTER WAREHOUSE Compute

Dynamically resize virtual warehouse size.

Generic Syntax:
ALTER WAREHOUSE wh_name SET WAREHOUSE_SIZE = 'LARGE';
Working Example:
ALTER WAREHOUSE analytics_wh SET WAREHOUSE_SIZE = 'LARGE';
❄ïļ Snowflake RESULT_SCAN Caching

Query cached result set of previous statement.

Generic Syntax:
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Working Example:
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
❄ïļ Snowflake CREATE SECRET Security

Securely store cloud authentication tokens.

Generic Syntax:
CREATE SECRET secret_name TYPE = S3 ...;
Working Example:
CREATE SECRET s3_dev TYPE = S3 KEY_ID = 'AKIA...' SECRET = 'wJal...';
❄ïļ Snowflake LIST @stage Storage

List files located in external cloud stage.

Generic Syntax:
LIST @stage_name;
Working Example:
LIST @my_s3_stage;
❄ïļ Snowflake SHOW TABLES Introspection

List tables in current database schema.

Generic Syntax:
SHOW TABLES LIKE 'pattern%';
Working Example:
SHOW TABLES LIKE 'orders%';
❄ïļ Snowflake EXPLAIN Performance

Analyze Snowflake micro-partition pruning plan.

Generic Syntax:
EXPLAIN SELECT * FROM tbl WHERE col = val;
Working Example:
EXPLAIN SELECT * FROM sales WHERE sale_date >= '2026-01-01';
❄ïļ Snowflake CREATE SNOWPIPE Data Pipeline

Define continuous automated S3 data ingestion pipe.

Generic Syntax:
CREATE PIPE pipe_name AS COPY INTO tbl FROM @stage;
Working Example:
CREATE PIPE auto_pipe AS COPY INTO raw_events FROM @s3_events_stage;
🍒 ClickHouse SELECT DQL

Query columnar data at high execution speed.

Generic Syntax:
SELECT col1, col2 FROM tbl;
Working Example:
SELECT user_id, event_type FROM user_events;
🍒 ClickHouse WHERE DQL

Filter rows matching partition pruning.

Generic Syntax:
SELECT * FROM tbl WHERE col = val;
Working Example:
SELECT * FROM logs WHERE status = 500;
🍒 ClickHouse GROUP BY Aggregations

Aggregate billions of rows in memory.

Generic Syntax:
SELECT col, COUNT(*) FROM tbl GROUP BY col;
Working Example:
SELECT domainWithoutWWW(url), COUNT(*) FROM web_clicks GROUP BY 1;
🍒 ClickHouse ENGINE = MergeTree() Table Engine

Default MergeTree table engine DDL declaration.

Generic Syntax:
CREATE TABLE tbl (...) ENGINE = MergeTree() ORDER BY (col);
Working Example:
CREATE TABLE logs (dt Date, user_id UInt64) ENGINE = MergeTree() ORDER BY (dt, user_id);
🍒 ClickHouse PARTITION BY Partitioning

Partition MergeTree table parts by month or date.

Generic Syntax:
CREATE TABLE tbl (...) ENGINE = MergeTree() PARTITION BY toYYYYMM(dt) ORDER BY (col);
Working Example:
CREATE TABLE sales (dt Date, amt Float64) ENGINE = MergeTree() PARTITION BY toYYYYMM(dt) ORDER BY dt;
🍒 ClickHouse ReplacingMergeTree Table Engine

Background deduplicating table engine.

Generic Syntax:
CREATE TABLE tbl (...) ENGINE = ReplacingMergeTree(ver) ORDER BY (id);
Working Example:
CREATE TABLE profiles (id UInt64, email String, updated DateTime) ENGINE = ReplacingMergeTree(updated) ORDER BY id;
🍒 ClickHouse SummingMergeTree Table Engine

Automatically sum numeric columns on background merge.

Generic Syntax:
CREATE TABLE tbl (...) ENGINE = SummingMergeTree() ORDER BY (id);
Working Example:
CREATE TABLE daily_totals (date Date, category String, amount Float64) ENGINE = SummingMergeTree() ORDER BY (date, category);
🍒 ClickHouse CREATE MATERIALIZED VIEW Views

Continuous streaming aggregation materialized view.

Generic Syntax:
CREATE MATERIALIZED VIEW mv ENGINE = SummingMergeTree() ORDER BY (col) AS SELECT ...;
Working Example:
CREATE MATERIALIZED VIEW mv_sales ENGINE = SummingMergeTree() ORDER BY (date) AS SELECT dt AS date, SUM(amt) FROM sales GROUP BY date;
🍒 ClickHouse arrayJoin() Array Functions

Unpack array elements into individual output rows.

Generic Syntax:
SELECT col, arrayJoin(arr_col) FROM tbl;
Working Example:
SELECT user_id, arrayJoin(tags) FROM user_profiles;
🍒 ClickHouse quantileExact() Aggregations

Compute exact 95th/99th percentile query latency.

Generic Syntax:
SELECT quantileExact(0.95)(latency) FROM tbl;
Working Example:
SELECT service, quantileExact(0.95)(duration_ms) FROM logs GROUP BY service;
🍒 ClickHouse domainWithoutWWW() String Functions

Parse clean domain from URL string.

Generic Syntax:
SELECT domainWithoutWWW(url_col) FROM tbl;
Working Example:
SELECT domainWithoutWWW(referrer) FROM web_clicks;
🍒 ClickHouse argMax() Aggregations

Fetch column value matching maximum timestamp.

Generic Syntax:
SELECT argMax(val, timestamp) FROM tbl GROUP BY grp;
Working Example:
SELECT user_id, argMax(status, updated_at) FROM status_logs GROUP BY user_id;
🍒 ClickHouse system.parts System Catalog

Inspect physical MergeTree part file sizes.

Generic Syntax:
SELECT table, sum(bytes), sum(rows) FROM system.parts GROUP BY table;
Working Example:
SELECT table, formatReadableSize(sum(bytes)) FROM system.parts WHERE active = 1 GROUP BY table;
🍒 ClickHouse OPTIMIZE TABLE FINAL Maintenance

Force immediate background partition merge.

Generic Syntax:
OPTIMIZE TABLE tbl FINAL;
Working Example:
OPTIMIZE TABLE user_profiles FINAL;
🍒 ClickHouse FORMAT JSON Output Format

Output query results as formatted JSON text.

Generic Syntax:
SELECT * FROM tbl FORMAT JSON;
Working Example:
SELECT * FROM user_events LIMIT 5 FORMAT JSON;
🍒 ClickHouse EXPLAIN Performance

View ClickHouse query execution plan.

Generic Syntax:
EXPLAIN SELECT * FROM tbl WHERE col = val;
Working Example:
EXPLAIN SELECT COUNT(*) FROM user_events WHERE event_date = '2026-01-01';
🍒 ClickHouse toYYYYMM() Date Functions

Convert date to numeric YYYYMM integer for partitioning.

Generic Syntax:
SELECT toYYYYMM(date_col);
Working Example:
SELECT toYYYYMM(today());
🍒 ClickHouse dictGetString() Dictionary

Perform high-speed external dictionary lookup.

Generic Syntax:
SELECT dictGetString('dict_name', 'attr', key_col);
Working Example:
SELECT user_id, dictGetString('users_dict', 'email', user_id) FROM clicks;
🍒 ClickHouse INSERT INTO SELECT DML

Bulk insert aggregated data into target MergeTree table.

Generic Syntax:
INSERT INTO target_tbl SELECT * FROM src_tbl;
Working Example:
INSERT INTO archive_events SELECT * FROM user_events WHERE event_date < '2025-01-01';
🍒 ClickHouse ALTER TABLE DELETE DML

Asynchronously delete rows matching condition.

Generic Syntax:
ALTER TABLE tbl DELETE WHERE condition;
Working Example:
ALTER TABLE user_events DELETE WHERE user_id = 999;
ðŸĶ† DuckDB SELECT DQL

Query local in-memory or file datasets.

Generic Syntax:
SELECT col1, col2 FROM tbl;
Working Example:
SELECT country, amount FROM sales;
ðŸĶ† DuckDB WHERE DQL

Filter output rows.

Generic Syntax:
SELECT * FROM tbl WHERE col = val;
Working Example:
SELECT * FROM orders WHERE status = 'shipped';
ðŸĶ† DuckDB JOIN Joins

Join datasets in vectorized execution engine.

Generic Syntax:
SELECT * FROM t1 JOIN t2 ON t1.id = t2.fk;
Working Example:
SELECT u.name, s.amount FROM users u JOIN sales s ON u.id = s.user_id;
ðŸĶ† DuckDB GROUP BY Aggregations

Vectorized aggregation grouping.

Generic Syntax:
SELECT col, AVG(x) FROM tbl GROUP BY col;
Working Example:
SELECT category, AVG(price) FROM products GROUP BY category;
ðŸĶ† DuckDB read_parquet() File Access

Query Parquet files directly using SQL without import.

Generic Syntax:
SELECT * FROM read_parquet('file.parquet');
Working Example:
SELECT country, COUNT(*) FROM read_parquet('data/sales_2026.parquet') GROUP BY country;
ðŸĶ† DuckDB COPY TO (.parquet) Export

Export query results directly into compressed Parquet file.

Generic Syntax:
COPY (SELECT ...) TO 'output.parquet' (FORMAT PARQUET);
Working Example:
COPY (SELECT * FROM orders WHERE year = 2026) TO 'orders_2026.parquet' (FORMAT PARQUET, COMPRESSION SNAPPY);
ðŸĶ† DuckDB read_csv_auto() File Access

Auto-detect CSV schema and query file directly.

Generic Syntax:
SELECT * FROM read_csv_auto('file.csv');
Working Example:
SELECT * FROM read_csv_auto('logs/*.csv') WHERE status = 500;
ðŸĶ† DuckDB SUMMARIZE EDA Stats

Generate statistical summary (nulls, min, max, avg) for all columns.

Generic Syntax:
SUMMARIZE SELECT * FROM tbl;
Working Example:
SUMMARIZE SELECT * FROM read_parquet('dataset.parquet');
ðŸĶ† DuckDB QUALIFY SQL Extensions

Filter window function output directly without subquery wrappers.

Generic Syntax:
SELECT col, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY val DESC) as rn FROM tbl QUALIFY rn = 1;
Working Example:
SELECT customer_id, order_date, amount, ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC) as rn FROM orders QUALIFY rn = 1;
ðŸĶ† DuckDB PIVOT / UNPIVOT Analytics

Rotate quarterly row values into reporting columns.

Generic Syntax:
PIVOT (SELECT year, quarter, sales FROM tbl) ON quarter IN ('Q1', 'Q2') USING SUM(sales);
Working Example:
PIVOT (SELECT year, quarter, sales FROM quarterly_sales) ON quarter IN ('Q1', 'Q2', 'Q3', 'Q4') USING SUM(sales);
ðŸĶ† DuckDB Pandas DataFrame Zero-Copy Ecosystem

Query Python Pandas DataFrame directly in DuckDB.

Generic Syntax:
import duckdb; duckdb.query('SELECT * FROM df');
Working Example:
import duckdb, pandas as pd; df = pd.DataFrame({'a': [1,2]}); duckdb.query('SELECT AVG(a) FROM df').df();
ðŸĶ† DuckDB INSTALL httpfs / LOAD httpfs Cloud Access

Enable HTTP and S3 remote file querying.

Generic Syntax:
INSTALL httpfs; LOAD httpfs;
Working Example:
INSTALL httpfs; LOAD httpfs; SELECT * FROM 's3://my-bucket/data.parquet' LIMIT 10;
ðŸĶ† DuckDB CREATE SECRET (S3) Security

Store S3 access key and secret token.

Generic Syntax:
CREATE SECRET s3_dev (TYPE S3, KEY_ID '...', SECRET '...');
Working Example:
CREATE SECRET s3_dev (TYPE S3, KEY_ID 'AKIA...', SECRET 'wJal...', REGION 'us-east-1');
ðŸĶ† DuckDB EXPLAIN ANALYZE Performance

Display vectorized query execution plan and timing metrics.

Generic Syntax:
EXPLAIN ANALYZE SELECT ...;
Working Example:
EXPLAIN ANALYZE SELECT COUNT(*) FROM read_parquet('large_file.parquet');
ðŸĶ† DuckDB PRAGMA enable_profiling Performance

Output JSON query profiling timings to file.

Generic Syntax:
PRAGMA enable_profiling = 'json'; PRAGMA profiling_output = 'prof.json';
Working Example:
PRAGMA enable_profiling = 'json'; PRAGMA profiling_output = 'prof.json';
ðŸĶ† DuckDB CREATE TABLE AS SELECT DDL

Create table from Parquet or CSV query result.

Generic Syntax:
CREATE TABLE tbl AS SELECT * FROM read_parquet('file.parquet');
Working Example:
CREATE TABLE orders_cache AS SELECT * FROM read_parquet('s3://bucket/orders.parquet');
ðŸĶ† DuckDB read_json_auto() File Access

Query JSON or NDJSON files directly.

Generic Syntax:
SELECT * FROM read_json_auto('file.json');
Working Example:
SELECT * FROM read_json_auto('events/*.json') WHERE type = 'signup';
ðŸĶ† DuckDB DENSE_RANK() OVER Window

Rank rows without rank gaps.

Generic Syntax:
SELECT col, DENSE_RANK() OVER(ORDER BY val DESC) FROM tbl;
Working Example:
SELECT player, score, DENSE_RANK() OVER(ORDER BY score DESC) FROM leaderboard;
ðŸĶ† DuckDB UNION ALL Queries

Combine result rows from multiple queries preserving duplicates.

Generic Syntax:
SELECT col FROM t1 UNION ALL SELECT col FROM t2;
Working Example:
SELECT email FROM leads UNION ALL SELECT email FROM customers;
ðŸĶ† DuckDB ALTER TABLE RENAME COLUMN DDL

Rename column inside table schema.

Generic Syntax:
ALTER TABLE tbl RENAME COLUMN old_name TO new_name;
Working Example:
ALTER TABLE users RENAME COLUMN fname TO first_name;