Practical Code Snippets

SQL Query Examples

Copyable SQL statements demonstrating DDL, DML, filtering, joins, aggregations, transactions, and window functions on MagnumDB v0.4.8.

DDL

CREATE TABLE

Defines a new relation schema with typed columns and constraint specifications.

SQL STATEMENT

sql

CREATE TABLE users (
    id INT PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT DEFAULT 'user@example.com',
    age INT
);

EXPECTED OUTPUT

CREATE TABLE

DML

INSERT INTO

Inserts one or more tuples into an existing B+ Tree indexed table.

SQL STATEMENT

sql

INSERT INTO users (id, username, email, age) 
VALUES 
    (1, 'soham', 'soham@example.com', 24),
    (2, 'alex', 'alex@example.com', 30),
    (3, 'taylor', 'taylor@example.com', 28);

EXPECTED OUTPUT

INSERT 0 3

Querying

SELECT with WHERE Clause

Queries tuples using projection and binary conditional filter expressions.

SQL STATEMENT

sql

SELECT id, username, age 
FROM users 
WHERE age >= 25 AND username != 'admin';

EXPECTED OUTPUT

+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  2 | alex     |  30 |
|  3 | taylor   |  28 |
+----+----------+-----+
(2 rows)

DML

UPDATE Records

Modifies existing tuple attributes matching target predicate filter.

SQL STATEMENT

sql

UPDATE users 
SET age = 25, email = 'soham.dev@example.com' 
WHERE username = 'soham';

EXPECTED OUTPUT

UPDATE 1

DML

DELETE Records

Removes tuples matching filter criteria from B+ Tree storage.

SQL STATEMENT

sql

DELETE FROM users WHERE age < 25;

EXPECTED OUTPUT

DELETE 1

Querying

INNER JOIN Query

Combines records from two tables based on explicit join predicate equality.

SQL STATEMENT

sql

SELECT u.username, o.amount, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100.0;

EXPECTED OUTPUT

+----------+--------+------------+
| username | amount | order_date |
+----------+--------+------------+
| alex     | 250.50 | 2026-08-10 |
| taylor   | 180.00 | 2026-08-12 |
+----------+--------+------------+
(2 rows)

Querying

GROUP BY and HAVING

Aggregates tuple groups and filters calculated aggregate results.

SQL STATEMENT

sql

SELECT user_id, COUNT(*) AS total_orders, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 200.0;

EXPECTED OUTPUT

+---------+--------------+-------------+
| user_id | total_orders | total_spent |
+---------+--------------+-------------+
|       2 |            3 |      450.00 |
+---------+--------------+-------------+
(1 row)

Querying

ORDER BY with LIMIT and OFFSET

Sorts result sets by specified columns and applies pagination bounds.

SQL STATEMENT

sql

SELECT id, username, age
FROM users
ORDER BY age DESC
LIMIT 2 OFFSET 1;

EXPECTED OUTPUT

+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  3 | taylor   |  28 |
|  1 | soham    |  24 |
+----+----------+-----+
(2 rows)

Transactions

Transaction Control (BEGIN, COMMIT)

Executes atomic set of writes protected by Write-Ahead Logging and MVCC.

SQL STATEMENT

sql

BEGIN;

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

COMMIT;

EXPECTED OUTPUT

BEGIN
UPDATE 1
UPDATE 1
COMMIT

Transactions

Transaction Abort (ROLLBACK)

Aborts current uncommitted transaction changes and discards modifications.

SQL STATEMENT

sql

BEGIN;

DELETE FROM critical_logs WHERE timestamp < '2026-01-01';

-- Discard uncommitted changes
ROLLBACK;

EXPECTED OUTPUT

BEGIN
DELETE 42
ROLLBACK

Administration

CREATE USER (Server Mode)

Registers authentication principal and MD5 password hash for server access.

SQL STATEMENT

sql

CREATE USER app_worker WITH PASSWORD 'secure_pass_123';

EXPECTED OUTPUT

CREATE ROLE

Advanced

Prepared Statements

Pre-parses SQL template and executes repeatedly with parameterized values.

SQL STATEMENT

sql

PREPARE get_user_by_id (INT) AS
    SELECT id, username, email FROM users WHERE id = $1;

EXECUTE get_user_by_id(2);

EXPECTED OUTPUT

+----+----------+------------------+
| id | username | email            |
+----+----------+------------------+
|  2 | alex     | alex@example.com |
+----+----------+------------------+
(1 row)

Advanced

Subqueries in WHERE Clause

Filters tuples against dynamic result set returned by nested SELECT expression.

SQL STATEMENT

sql

SELECT username, age 
FROM users 
WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 200.0
);

EXPECTED OUTPUT

+----------+-----+
| username | age |
+----------+-----+
| alex     |  30 |
+----------+-----+
(1 row)

Advanced

Common Table Expressions (WITH / CTE)

Defines temporary named result set accessible within primary query execution.

SQL STATEMENT

sql

WITH high_value_orders AS (
    SELECT user_id, amount 
    FROM orders 
    WHERE amount >= 150.0
)
SELECT u.username, hvo.amount
FROM users u
JOIN high_value_orders hvo ON u.id = hvo.user_id;

EXPECTED OUTPUT

+----------+--------+
| username | amount |
+----------+--------+
| alex     | 250.50 |
| taylor   | 180.00 |
+----------+--------+
(2 rows)

Querying

UNION and UNION ALL

Combines result rows of two query streams into unified result set.

SQL STATEMENT

sql

SELECT username AS name, 'user' AS role FROM users
UNION ALL
SELECT admin_name AS name, 'admin' AS role FROM administrators;

EXPECTED OUTPUT

+--------+-------+
| name   | role  |
+--------+-------+
| soham  | user  |
| alex   | user  |
| root   | admin |
+--------+-------+
(3 rows)

DDL

ALTER TABLE ADD COLUMN

Modifies table schema definition by appending new attribute column.

SQL STATEMENT

sql

ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

EXPECTED OUTPUT

ALTER TABLE

DDL

DROP TABLE

Removes B+ Tree table metadata and associated storage files from disk.

SQL STATEMENT

sql

DROP TABLE IF EXISTS legacy_logs;

EXPECTED OUTPUT

DROP TABLE