Complete Database & SQL References & Tips with Code Examples

Hello my friend! Welcome to the Complete Database & SQL References & Tips guide on dev.rean.me!

Relational databases and SQL (Structured Query Language) are the foundation of modern software development. Whether you work with MySQL, PostgreSQL, Microsoft SQL Server, SQLite, or Oracle, mastering SQL query syntax, schema design, indexes, transactions, and performance optimization is essential for building scalable applications.

In this comprehensive guide, we cover all core SQL concepts — data types, DDL schema creation, DML operations, complex SELECT queries, JOINs, GROUP BY aggregations, CTE subqueries, window functions, indexes, transactions, security practices, and practical tips — paired with code examples and links to related database tutorials. Bookmark this page for your daily database reference! 🛢️⚡

1. Database Setup & Environment

Popular Database Systems Overview

Database Engine	Type	Common Use Case	Default Port
MySQL / MariaDB	Open Source Relational	Web applications, WordPress, LAMP stack	`3306`
PostgreSQL	Advanced Open Source RDBMS	Complex data models, JSONB, geospatial, enterprise apps	`5432`
Microsoft SQL Server	Enterprise Commercial RDBMS	Enterprise .NET applications, Windows Server	`1433`
SQLite	Embedded Serverless File	Mobile apps, local desktop apps, testing	N/A (File)

CLI Quick Connect Commands

# Connect to MySQL server via CLI
mysql -u root -p -h localhost -P 3306

# Connect to PostgreSQL server via CLI
psql -U postgres -d my_database -h localhost

# Connect to SQLite database file
sqlite3 mydata.db

# Connect to SQL Server using sqlcmd
sqlcmd -S localhost -U sa -P 'MyStrongPass123!'

2. SQL Data Types Reference

Selecting the right data type ensures storage efficiency, data integrity, and faster query execution.

Category	Data Type	Description & Typical Storage	Example
Numeric	`INT` / `INTEGER`	4-byte integer (-2.1B to +2.1B)	`42`
Numeric	`BIGINT`	8-byte large integer (auto-increment IDs)	`9223372036854775807`
Numeric	`DECIMAL(p,s)` / `NUMERIC`	Exact decimal for currency/financials	`DECIMAL(10,2)` -> `199.99`
Numeric	`FLOAT` / `DOUBLE`	Approximate floating-point numbers	`3.14159`
String	`VARCHAR(n)`	Variable-length string up to n chars	`VARCHAR(255)`
String	`CHAR(n)`	Fixed-length string (padded with spaces)	`CHAR(2)` -> `'KH'`
String	`TEXT`	Long text field (articles, comments)	Up to 64KB (MySQL)
Date/Time	`DATE`	Calendar date (YYYY-MM-DD)	`'2026-09-19'`
Date/Time	`DATETIME` / `TIMESTAMP`	Date and time (YYYY-MM-DD HH:MM:SS)	`'2026-09-19 14:30:00'`
Boolean	`BOOLEAN` / `TINYINT(1)`	True/False flag (`1` or `0`)	`TRUE` or `1`
Binary/JSON	`JSON`	Native JSON document indexing	`'{"role": "admin"}'`

💡 Tip: Always use DECIMAL(p,s) or NUMERIC(p,s) for financial amounts and money values. Never use FLOAT or DOUBLE for currency because floating-point binary representation causes rounding errors (0.1 + 0.2 = 0.30000000000000004).

Common SQL Data Types in Database

3. Data Definition Language (DDL) — Schemas & Tables

DDL statements create, alter, and delete database structures like databases, tables, indexes, and views.

Managing Databases

-- Create database
CREATE DATABASE IF NOT EXISTS school_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- Switch database
USE school_db;

-- Drop database (CAUTION: Deletes all data!)
DROP DATABASE IF EXISTS old_school_db;

Table Creation with Constraints

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    balance DECIMAL(10, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Altering & Modifying Tables

-- Add new column
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- Modify existing column type
ALTER TABLE users MODIFY COLUMN username VARCHAR(80) NOT NULL;

-- Rename column (MySQL 8.0+)
ALTER TABLE users RENAME COLUMN phone TO mobile_number;

-- Drop column
ALTER TABLE users DROP COLUMN status;

-- Rename table
RENAME TABLE users TO app_users;

Dropping & Truncating Tables

-- TRUNCATE: Instantly removes all rows and resets AUTO_INCREMENT (Faster than DELETE)
TRUNCATE TABLE logs;

-- DROP: Completely removes table structure and data
DROP TABLE IF EXISTS temporary_imports;

4. Data Manipulation Language (DML) — INSERT, UPDATE, DELETE

DML statements manage data records inside existing tables.

INSERT Statements

-- Insert single record
INSERT INTO users (username, email, password_hash, balance) 
VALUES ('sokha_dev', 'sokha@rean.me', '$2y$10$e8Z...', 150.00);

-- Insert multiple records in a single query
INSERT INTO users (username, email, password_hash, balance) VALUES
('cheata_ui', 'cheata@rean.me', '$2y$10$x9K...', 200.50),
('bopha_code', 'bopha@rean.me', '$2y$10$w1P...', 75.00),
('vanna_db', 'vanna@rean.me', '$2y$10$z4M...', 310.00);

-- INSERT IGNORE (Skip duplicates without erroring)
INSERT IGNORE INTO users (user_id, username, email, password_hash)
VALUES (1, 'sokha_dev', 'sokha@rean.me', '$2y$10$e8Z...');

-- UPSERT / ON DUPLICATE KEY UPDATE (Insert or Update if Primary Key exists)
INSERT INTO users (user_id, username, balance) 
VALUES (1, 'sokha_dev', 500.00)
ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance);

UPDATE Statements

-- Update specific columns for matching rows
UPDATE users 
SET balance = balance + 50.00, 
    status = 'active'
WHERE user_id = 1;

-- Update with conditional CASE statement
UPDATE users 
SET status = CASE 
    WHEN balance > 100 THEN 'active'
    ELSE 'inactive'
END;

⚠️ Warning: Always test UPDATE and DELETE queries with a SELECT statement first to verify which rows will be affected before running the actual modification query without a safety clause!

DELETE Statements

-- Delete specific rows matching condition
DELETE FROM users 
WHERE status = 'banned' AND created_at < '2025-01-01';

-- Safe pattern: Check affected rows before deleting
SELECT COUNT(*) FROM users WHERE status = 'banned';
-- If count is correct, execute DELETE

Student Exam Database Part 2 - Insert Data and Download Example (Video in Khmer)

5. SELECT Statements & Data Retrieval

The SELECT query retrieves rows from tables using filtering, sorting, pagination, and projection.

Core SELECT Query Syntax

-- Basic SELECT all columns
SELECT * FROM products;

-- SELECT specific columns with aliases
SELECT 
    product_id AS id,
    product_name AS title,
    unit_price AS price,
    unit_price * 1.10 AS price_with_tax
FROM products;

-- Distinct unique values
SELECT DISTINCT category_id FROM products;

WHERE Clause & Filtering

SELECT product_id, product_name, unit_price, stock_quantity
FROM products
WHERE unit_price >= 50.00 
  AND stock_quantity > 0 
  AND category_id IN (1, 3, 5)
ORDER BY unit_price DESC;

Sorting & Pagination (ORDER BY, LIMIT, OFFSET)

-- Top 10 most expensive items
SELECT product_id, product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 10;

-- Pagination: Page 2 (10 items per page -> Skip 10, Take 10)
SELECT product_id, product_name, unit_price
FROM products
ORDER BY product_id ASC
LIMIT 10 OFFSET 10;

6. Logical SQL Execution Order

Understanding how SQL processes query clauses under the hood helps prevent syntax errors (like trying to use a SELECT column alias in a WHERE clause).

 ┌────────────────────────────────────────────────────────┐
 │ 1. FROM & JOIN     -> Locate source tables & merge    │
 │ 2. WHERE           -> Filter individual rows           │
 │ 3. GROUP BY        -> Group rows into summary buckets  │
 │ 4. HAVING          -> Filter grouped aggregate buckets │
 │ 5. SELECT          -> Compute columns & aliases        │
 │ 6. DISTINCT        -> Remove duplicate result rows      │
 │ 7. ORDER BY        -> Sort final output rows           │
 │ 8. LIMIT / OFFSET  -> Paginate result slice            │
 └────────────────────────────────────────────────────────┘

-- Query demonstrating execution logic
SELECT 
    category_id,
    COUNT(product_id) AS total_items,
    AVG(unit_price) AS avg_price           -- Step 5: SELECT & Aliases
FROM products                              -- Step 1: FROM
WHERE stock_quantity > 0                   -- Step 2: WHERE (row filter)
GROUP BY category_id                       -- Step 3: GROUP BY
HAVING AVG(unit_price) > 20.00             -- Step 4: HAVING (group filter - cannot use avg_price alias in standard SQL!)
ORDER BY total_items DESC                  -- Step 7: ORDER BY (can use alias)
LIMIT 5;                                   -- Step 8: LIMIT

SQL Operation Logic Order Explained

7. Filtering & Conditional Operators Cheat Sheet

Operator	Syntax Example	Meaning
`LIKE`	`name LIKE 'Sok%'`	Starts with "Sok" (`%` = wildcard, `_` = single char)
`IN`	`status IN ('paid', 'shipped')`	Matches any value in the provided list
`BETWEEN`	`price BETWEEN 10 AND 50`	Range match inclusive of endpoints
`IS NULL`	`deleted_at IS NULL`	Checks if column value is NULL
`IS NOT NULL`	`email IS NOT NULL`	Checks if column contains a non-null value
`NOT`	`NOT (status = 'cancelled')`	Inverts boolean condition
`CASE WHEN`	`CASE WHEN stock > 0 THEN 'In Stock' ELSE 'Out' END`	Conditional branching expression

LIKE Wildcard Examples

-- Contains 'sql' anywhere in title (case-insensitive in default collation)
SELECT * FROM articles WHERE title LIKE '%sql%';

-- 4-letter word starting with 'd' and ending with 'ev'
SELECT * FROM tags WHERE tag_name LIKE 'd_ev';

CASE WHEN Expressions

SELECT 
    order_id,
    total_amount,
    CASE 
        WHEN total_amount >= 500 THEN 'VIP Customer'
        WHEN total_amount >= 100 THEN 'Regular Customer'
        ELSE 'Basic Customer'
    END AS customer_tier
FROM orders;

8. SQL JOINs Reference

JOIN clauses combine rows from two or more tables based on a related column between them.

       INNER JOIN                   LEFT JOIN                  RIGHT JOIN
   ┌───────┬───────┐           ┌───────┬───────┐          ┌───────┬───────┐
   │ Table │ Table │           │ Table │ Table │          │ Table │ Table │
   │   A   │   B   │           │   A   │   B   │          │   A   │   B   │
   │    ┌──┴──┐    │           │███████┼──┐    │          │    ┌──┼███████│
   │    │█████│    │           │███████│██│    │          │    │██│███████│
   │    └──┬──┘    │           │███████┼──┘    │          │    └──┼███████│
   └───────┴───────┘           └───────┴───────┘          └───────┴───────┘
  Only matching rows         All Left + matching Right   All Right + matching Left

1. INNER JOIN (Matching records in both tables)

SELECT 
    o.order_id,
    c.customer_name,
    c.email,
    o.order_date,
    o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

2. LEFT JOIN (All rows from left table + matched rows from right)

-- Find all customers, including those with 0 orders
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

3. Finding Unmatched Records (Anti-JOIN pattern)

-- Find customers who have NEVER placed an order
SELECT 
    c.customer_id,
    c.customer_name,
    c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

4. FULL OUTER JOIN (All records from both tables)

-- Supported natively in PostgreSQL / SQL Server (Emulated with UNION in MySQL)
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.customer_name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

9. Aggregation & Grouping (GROUP BY & HAVING)

Aggregate functions compute a single summary value from multiple row values.

Common Aggregate Functions

Function	Purpose	Example
`COUNT()`	Counts total rows or non-null values	`COUNT(*)`, `COUNT(DISTINCT category_id)`
`SUM()`	Calculates total numeric sum	`SUM(total_amount)`
`AVG()`	Calculates average numeric value	`AVG(unit_price)`
`MIN()`	Returns minimum value	`MIN(created_at)`
`MAX()`	Returns maximum value	`MAX(score)`

Grouping and HAVING Example

SELECT 
    category_id,
    COUNT(product_id) AS item_count,
    MIN(unit_price) AS cheapest,
    MAX(unit_price) AS most_expensive,
    AVG(unit_price) AS average_price
FROM products
GROUP BY category_id
HAVING COUNT(product_id) >= 5
ORDER BY average_price DESC;

💡 Tip: Use WHERE to filter rows before aggregation occurs (faster performance). Use HAVING only to filter aggregated results after GROUP BY.

03-Library Database Exercise 2 - 10 More SQL Questions and Answers (Video in Khmer)

10. Subqueries & Common Table Expressions (CTEs)

CTEs (WITH clause) break complex nested queries into clean, readable tabular steps.

Subquery in WHERE Clause

-- Find employees who earn more than the department average salary
SELECT employee_id, first_name, salary, department_id
FROM employees
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees
);

Subquery with IN / EXISTS

-- Find categories that have at least one product in stock
SELECT category_id, category_name
FROM categories c
WHERE EXISTS (
    SELECT 1 
    FROM products p 
    WHERE p.category_id = c.category_id 
      AND p.stock_quantity > 0
);

Common Table Expression (CTE - WITH Clause)

WITH HighValueOrders AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY customer_id
    HAVING SUM(total_amount) > 1000.00
),
CustomerDetails AS (
    SELECT customer_id, customer_name, email
    FROM customers
)
SELECT 
    cd.customer_name,
    cd.email,
    hvo.total_spent
FROM HighValueOrders hvo
JOIN CustomerDetails cd ON hvo.customer_id = cd.customer_id
ORDER BY hvo.total_spent DESC;

04-Library Database Exercise 3 - 5 Advanced SQL Questions and Answers (Video in Khmer)

11. Primary Keys, Foreign Keys & Constraints

Constraints enforce relational data integrity and prevent invalid data entries.

CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    salary DECIMAL(10,2) CHECK (salary > 0),    -- CHECK constraint
    dept_id INT NOT NULL,
    
    -- Foreign Key Definition with CASCADE action
    CONSTRAINT fk_employees_departments 
        FOREIGN KEY (dept_id) 
        REFERENCES departments(dept_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Foreign Key Cascade Options

Cascade Action	Behavior on Parent Record Deletion/Update
`RESTRICT` / `NO ACTION`	Blocks deletion if child records exist (Default / Safest)
`CASCADE`	Automatically deletes or updates matching child rows
`SET NULL`	Sets the foreign key column in child rows to `NULL`

12. Practical Database Schemas & Multi-Table Projects

Here are real-world relational database design examples implemented across different database systems:

Complete Student Exam System Schema Example (MySQL)

CREATE TABLE students (
    student_id VARCHAR(10) PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    dob DATE
);

CREATE TABLE subjects (
    subject_id INT AUTO_INCREMENT PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL
);

CREATE TABLE exam_scores (
    score_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id VARCHAR(10) NOT NULL,
    subject_id INT NOT NULL,
    score DECIMAL(5,2) CHECK (score BETWEEN 0 AND 100),
    exam_date DATE NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
);

13. Built-in SQL Functions Reference

String Functions

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    UPPER(email) AS upper_email,
    LOWER(dept_name) AS lower_dept,
    LENGTH(username) AS char_count,
    SUBSTRING(phone, 1, 3) AS area_code,
    REPLACE(slug, '-', '_') AS clean_slug,
    TRIM('   hello   ') AS trimmed_text
FROM users;

Date & Time Functions

-- Current Date & Time
SELECT CURRENT_TIMESTAMP, CURRENT_DATE(), CURRENT_TIME();

-- Extract Date Parts (MySQL)
SELECT 
    created_at,
    YEAR(created_at) AS year_val,
    MONTH(created_at) AS month_val,
    DAY(created_at) AS day_val,
    DATE_FORMAT(created_at, '%d/%m/%Y %H:%i') AS formatted_date
FROM orders;

-- Date Arithmetic (MySQL)
SELECT 
    order_date,
    DATE_ADD(order_date, INTERVAL 7 DAY) AS estimated_delivery,
    DATEDIFF(NOW(), order_date) AS days_elapsed
FROM orders;

Handling NULL Values (`COALESCE` & `IFNULL`)

-- Return first non-null argument
SELECT 
    username,
    COALESCE(phone, mobile, email, 'No Contact Info') AS primary_contact
FROM users;

-- IFNULL (MySQL specific)
SELECT product_name, IFNULL(discount_price, regular_price) AS final_price
FROM products;

14. Indexes, Performance Optimization & Query Tuning

Indexes speed up read query lookups (SELECT) by maintaining a balanced tree (B-Tree) structure, at the cost of slightly slower writes (INSERT/UPDATE).

Creating Indexes

-- Single-column index for fast WHERE / JOIN filtering
CREATE INDEX idx_users_email ON users(email);

-- Composite Index (Order matters! Left-to-right prefix rule)
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Unique index
CREATE UNIQUE INDEX idx_products_sku ON products(sku);

-- Drop index
DROP INDEX idx_users_email ON users;

Analyzing Query Performance (`EXPLAIN`)

-- Inspect execution plan to check if index is used
EXPLAIN SELECT * FROM users WHERE email = 'sokha@rean.me';

💡 Indexing Best Practices:

Index columns used frequently in WHERE, ON (JOINs), ORDER BY, and GROUP BY.

Avoid indexing small tables (less than 1,000 rows) as full table scans are faster.

Do not apply functions to indexed columns in WHERE queries (WHERE YEAR(created_at) = 2026 invalidates index! Use WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' instead).

15. Transactions & Concurrency Control

Transactions group multiple database statements into a single atomic execution unit adhering to ACID properties (Atomicity, Consistency, Isolation, Durability).

-- Start transaction block
START TRANSACTION;  -- Or BEGIN; in PostgreSQL / SQLite

-- Step 1: Deduct balance from sender
UPDATE accounts 
SET balance = balance - 100.00 
WHERE account_id = 101 AND balance >= 100.00;

-- Step 2: Add balance to recipient
UPDATE accounts 
SET balance = balance + 100.00 
WHERE account_id = 202;

-- Step 3: Insert audit log
INSERT INTO transaction_logs (sender_id, receiver_id, amount) 
VALUES (101, 202, 100.00);

-- If everything succeeds, commit changes permanently
COMMIT;

-- If an error occurs, undo all changes in transaction
-- ROLLBACK;

16. Views & Stored Procedures

Views (Virtual Saved Queries)

-- Create View for popular active products
CREATE VIEW vw_active_products AS
SELECT 
    p.product_id,
    p.product_name,
    p.unit_price,
    c.category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE p.stock_quantity > 0;

-- Querying the View like a real table
SELECT * FROM vw_active_products WHERE unit_price < 50.00;

Stored Procedures (MySQL Example)

DELIMITER //

CREATE PROCEDURE GetCustomerSummary(IN customerId INT, OUT totalOrders INT, OUT totalSpent DECIMAL(10,2))
BEGIN
    SELECT COUNT(*), IFNULL(SUM(total_amount), 0.00)
    INTO totalOrders, totalSpent
    FROM orders
    WHERE customer_id = customerId;
END //

DELIMITER ;

-- Calling Stored Procedure
CALL GetCustomerSummary(1, @count, @spent);
SELECT @count AS total_orders, @spent AS total_amount_spent;

17. SQL Security & SQL Injection Prevention

SQL Injection occurs when user inputs are directly concatenated into dynamic SQL string queries.

Vulnerable Code vs Secure Parameterized Query

❌ Vulnerable (DO NOT DO THIS!):

// User input: ' OR '1'='1
$sql = "SELECT * FROM users WHERE username = '" . $_POST['user'] . "'";
// Resulting Query: SELECT * FROM users WHERE username = '' OR '1'='1'
// Allows attacker to bypass authentication!

✅ Secure Parameterized Query (PDO in PHP):

$stmt = $pdo->prepare("SELECT user_id, password_hash FROM users WHERE username = :user");
$stmt->execute(['user' => $_POST['user']]);
$user = $stmt->fetch();

✅ Secure Parameterized Query (Node.js MySQL2 / pg):

// Parameter placeholder (?) escapes input automatically
const [rows] = await db.execute(
  'SELECT * FROM users WHERE email = ? AND status = ?',
  [userInputEmail, 'active']
);

18. SQL Best Practices vs Common Mistakes

Recommended Best Practice	Avoid Common Mistake
Use explicit column names (`SELECT id, name`)	Using `SELECT *` in production application APIs
Use parameterized queries (`?`, `:var`)	Concatenating user inputs directly into SQL strings
Wrap multi-step mutations in `START TRANSACTION`	Running multi-step updates without transaction safety
Use `DECIMAL(10,2)` for currency values	Using `FLOAT` or `DOUBLE` for monetary amounts
Add Indexes on Foreign Keys & WHERE columns	Leaving foreign keys unindexed causing slow table joins
Use `TRUNCATE TABLE` to clear all table rows	Running `DELETE FROM table` without WHERE clause
Filter dates with range bounds (`date >= '2026-01-01'`)	Wrapping indexed date columns in functions (`YEAR(date) = 2026`)
Use `EXPLAIN` to audit slow query execution	Guessing why a query takes seconds to execute

#database #sql #mysql #postgresql #sql-server #cheat-sheet #reference #tips #crud #joins #indexing #tutorial-list

Happy querying and building high-performance database-driven applications! 🛢️🚀

Database & SQL Complete Tutorial Series & Course List

1. Database Setup & Environment

Popular Database Systems Overview

CLI Quick Connect Commands

🏷️ Related Posts:

2. SQL Data Types Reference

🏷️ Related Posts:

3. Data Definition Language (DDL) — Schemas & Tables

Managing Databases

Table Creation with Constraints

Altering & Modifying Tables

Dropping & Truncating Tables

🏷️ Related Posts:

4. Data Manipulation Language (DML) — INSERT, UPDATE, DELETE

INSERT Statements

UPDATE Statements

DELETE Statements

🏷️ Related Posts:

5. SELECT Statements & Data Retrieval

Core SELECT Query Syntax

WHERE Clause & Filtering

Sorting & Pagination (ORDER BY, LIMIT, OFFSET)

🏷️ Related Posts:

6. Logical SQL Execution Order

🏷️ Related Posts:

7. Filtering & Conditional Operators Cheat Sheet

LIKE Wildcard Examples

CASE WHEN Expressions

8. SQL JOINs Reference

1. INNER JOIN (Matching records in both tables)

2. LEFT JOIN (All rows from left table + matched rows from right)

3. Finding Unmatched Records (Anti-JOIN pattern)

4. FULL OUTER JOIN (All records from both tables)

9. Aggregation & Grouping (GROUP BY & HAVING)

Common Aggregate Functions

Grouping and HAVING Example

🏷️ Related Posts:

10. Subqueries & Common Table Expressions (CTEs)

Subquery in WHERE Clause

Subquery with IN / EXISTS

Common Table Expression (CTE - WITH Clause)

🏷️ Related Posts:

11. Primary Keys, Foreign Keys & Constraints

Foreign Key Cascade Options

12. Practical Database Schemas & Multi-Table Projects

Complete Student Exam System Schema Example (MySQL)

🏷️ Related Posts:

13. Built-in SQL Functions Reference

String Functions

Date & Time Functions

Handling NULL Values (COALESCE & IFNULL)

14. Indexes, Performance Optimization & Query Tuning

Creating Indexes

Analyzing Query Performance (EXPLAIN)

15. Transactions & Concurrency Control

16. Views & Stored Procedures

Views (Virtual Saved Queries)

Stored Procedures (MySQL Example)

17. SQL Security & SQL Injection Prevention

Vulnerable Code vs Secure Parameterized Query

18. SQL Best Practices vs Common Mistakes

Summary Tag Cloud Directory

Database & SQL Complete Tutorial Series & Course List

Handling NULL Values (`COALESCE` & `IFNULL`)

Analyzing Query Performance (`EXPLAIN`)