Launch offer
Business website $150 USD Custom plugin $200 USD Ready in 5 days
Get a quote
malekimtiyajmca@gmail.com
SQL

SQL Complete Tutorial (100 Topics with Joins, Transactions & Final DBMS Project)

Complete SQL tutorial covering databases, CRUD, joins, CTEs, transactions, indexing, security, WordPress $wpdb, and a final database management system project.

August 21, 2026 By malekimtiyajmca@gmail.com 17 min read

This complete SQL tutorial covers 100 topics — from relational fundamentals and CRUD to joins, CTEs, transactions, indexing, security, WordPress/$wpdb, and a final database management system project.

Course roadmap

1. Introduction to SQL

SQL (Structured Query Language) is how you create, read, update, and manage relational data. This series covers schema design, querying, joins, procedures, security, app integrations, and a complete DBMS project.

  1. Learn relational basics and CREATE/SELECT.
  2. Practice joins, aggregates, and transactions.
  3. Build the final database management project.

Learning path

DB fundamentals + DDL
CRUD + filtering
Aggregates + joins
Advanced SQL + transactions
Security + app stacks
Final DBMS project

2. What is SQL?

SQL lets you define schemas (DDL), manipulate data (DML), control access (DCL), and manage transactions (TCL).

SQL categories

DDL → CREATE ALTER DROP
DML → SELECT INSERT UPDATE DELETE
DCL → GRANT REVOKE
TCL → COMMIT ROLLBACK

3. SQL vs MySQL

SQL is a language standard; MySQL/MariaDB/PostgreSQL/SQL Server are engines that implement SQL with dialect differences.

Quick compare

SQL → language
MySQL → RDBMS product
Other engines: PostgreSQL, SQL Server, SQLite
Syntax mostly similar, details differ

4. Database Fundamentals

A database stores organized data; a DBMS manages access, integrity, and durability.

Core terms

Database
Schema
Table
Query
DBMS / RDBMS

5. Relational Database Concepts

Relational databases store data in tables related by keys — enabling powerful joins and constraints.

Relational ideas

Relation ≈ table
Tuple ≈ row
Attribute ≈ column
Keys relate tables

6. Tables, Rows and Columns in SQL

Each column has a name and data type; each row is one entity instance.

Mental model

users table
 → columns: id, email, name
 → rows: one user each

7. Primary Keys in SQL

Primary keys are NOT NULL and UNIQUE. Prefer stable surrogate keys (e.g., INT AUTO_INCREMENT / UUID) when natural keys change.

Primary key

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(191) NOT NULL
);

8. Foreign Keys in SQL

Foreign keys enforce relationships and enable cascading rules.

Foreign key

CREATE TABLE orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

9. Unique Keys in SQL

Emails, usernames, and SKUs commonly use UNIQUE constraints.

Unique

ALTER TABLE users
  ADD CONSTRAINT users_email_unique UNIQUE (email);

10. Composite Keys in SQL

Common in junction tables for many-to-many relationships.

Composite PK

CREATE TABLE course_user (
  course_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (course_id, user_id)
);

11. Constraints in SQL

Constraints catch bad data at write time — cheaper than fixing corruption later.

Constraint types

PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK
DEFAULT

12. SQL Data Types

Right types improve storage, indexing, and validation.

Common MySQL types

INT, BIGINT, DECIMAL
VARCHAR, TEXT
DATE, DATETIME, TIMESTAMP
BOOLEAN / TINYINT(1)
JSON

13. Creating a Database in SQL

Pick charset/collation carefully (utf8mb4 is typical for modern apps).

Create DB

CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE shop;

14. Creating Tables in SQL

Design for current needs plus clear migration paths.

Create table

CREATE TABLE products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

15. ALTER TABLE in SQL

Add/drop columns, indexes, and constraints as requirements evolve.

Alter examples

ALTER TABLE products ADD COLUMN sku VARCHAR(64) NULL;
ALTER TABLE products MODIFY price DECIMAL(12,2) NOT NULL;

16. DROP TABLE in SQL

Irreversible without backups — prefer migrations and confirm environments.

Drop table

DROP TABLE IF EXISTS temp_imports;

17. TRUNCATE TABLE in SQL

TRUNCATE is DDL-like and often non-transactional depending on engine — know your DBMS behavior vs DELETE.

Truncate

TRUNCATE TABLE session_logs;

18. INSERT Statement in SQL

Prefer explicit column lists for safer migrations.

Insert

INSERT INTO users (email, name)
VALUES ('asha@example.com', 'Asha');

19. SELECT Statement in SQL

Start with explicit columns instead of SELECT * in application queries.

Select

SELECT id, email, name
FROM users;

20. WHERE Clause in SQL

Indexes often matter most for WHERE and JOIN keys.

Where

SELECT id, email
FROM users
WHERE email = 'asha@example.com';

21. DISTINCT in SQL

DISTINCT can be expensive — sometimes GROUP BY or careful schema design is clearer.

Distinct

SELECT DISTINCT country
FROM customers;

22. ORDER BY in SQL

Stable pagination needs a deterministic ORDER BY (include unique key).

Order by

SELECT id, name, price
FROM products
ORDER BY price DESC, id ASC;

23. LIMIT in SQL

Use for top-N reports and pagination pages.

Limit

SELECT id, name
FROM products
ORDER BY created_at DESC
LIMIT 10;

24. OFFSET in SQL

Large offsets get slow — keyset pagination is better for big datasets.

Offset

SELECT id, name
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

25. UPDATE Statement in SQL

Always use WHERE unless you intentionally update all rows. Preview with SELECT first.

Update

UPDATE products
SET price = 19.99
WHERE id = 42;

26. DELETE Statement in SQL

Prefer soft-delete columns when business history matters.

Delete

DELETE FROM products
WHERE id = 42;

27. Comparison Operators in SQL

Watch NULL semantics — use IS NULL instead of = NULL.

Comparisons

SELECT * FROM products
WHERE price >= 10 AND price < 50;

28. Logical Operators in SQL

Use parentheses to make precedence obvious.

Logical

SELECT * FROM orders
WHERE status = 'paid'
  AND (total >= 100 OR priority = 'high');

29. BETWEEN in SQL

BETWEEN is inclusive on both ends — confirm edge cases.

Between

SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

30. IN and NOT IN in SQL

Prefer JOIN/EXISTS over long NOT IN lists with NULLs — NULL handling can surprise you.

IN

SELECT * FROM products
WHERE category_id IN (1, 2, 5);

31. LIKE and NOT LIKE in SQL

Leading wildcards (`%term`) often cannot use normal indexes efficiently.

Like

SELECT * FROM users
WHERE email LIKE '%@example.com';

32. IS NULL and IS NOT NULL in SQL

NULL means unknown — comparisons with =/<> do not work as beginners expect.

Null checks

SELECT * FROM users
WHERE deleted_at IS NULL;

33. Aggregate Functions in SQL

Aggregates collapse rows — use GROUP BY for per-group summaries.

Aggregate idea

SELECT COUNT(*) AS total_users FROM users;

34. COUNT() in SQL

COUNT(*) counts rows; COUNT(col) ignores NULLs in that column.

Count

SELECT COUNT(*) AS orders_count
FROM orders
WHERE status = 'paid';

35. SUM() in SQL

Useful for revenue and quantity totals.

Sum

SELECT SUM(total) AS revenue
FROM orders
WHERE status = 'paid';

36. AVG() in SQL

NULLs are ignored by AVG in standard SQL aggregates.

Avg

SELECT AVG(price) AS avg_price
FROM products;

37. MIN() and MAX() in SQL

Often used for latest timestamps or price ranges.

Min Max

SELECT MIN(price) AS cheapest, MAX(price) AS priciest
FROM products;

38. GROUP BY in SQL

Selected non-aggregated columns must be grouped (or functionally dependent, per engine rules).

Group by

SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status;

39. HAVING in SQL

WHERE filters rows before grouping; HAVING filters groups after.

Having

SELECT user_id, COUNT(*) AS orders_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 3;

40. SQL Aliases

Aliases improve readability and are required for derived tables in many engines.

Aliases

SELECT u.email AS user_email, COUNT(o.id) AS order_count
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.email;

41. SQL String Functions

Function names vary slightly by DBMS — check your dialect.

Strings (MySQL-style)

SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       LOWER(email) AS email_norm
FROM users;

42. SQL Date and Time Functions

Store UTC when possible; convert for display in the app layer.

Dates (MySQL-style)

SELECT id, created_at
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

43. SQL Mathematical Functions

Prefer DECIMAL for money — avoid float rounding surprises.

Math

SELECT ROUND(price * 1.18, 2) AS price_with_tax
FROM products;

44. CASE Statement in SQL

CASE is the standard way to map values without multiple queries.

Case

SELECT id,
  CASE status
    WHEN 'paid' THEN 'Completed'
    WHEN 'pending' THEN 'Awaiting payment'
    ELSE 'Other'
  END AS status_label
FROM orders;

45. IF and IFNULL in SQL

Prefer COALESCE for portable null-handling across engines.

IF / IFNULL (MySQL)

SELECT IF(stock > 0, 'In stock', 'Sold out') AS availability,
       IFNULL(nickname, name) AS display_name
FROM products;

46. COALESCE in SQL

Ideal for fallback columns and portable null defaults.

Coalesce

SELECT COALESCE(phone, email, 'n/a') AS contact
FROM users;

47. SQL Joins

Joins are the heart of relational querying — master INNER and LEFT first.

Join types

INNER, LEFT, RIGHT, FULL
CROSS, SELF
Prefer explicit JOIN … ON syntax

48. INNER JOIN in SQL

Non-matching rows are excluded.

Inner join

SELECT u.email, o.id AS order_id
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

49. LEFT JOIN in SQL

Unmatched right side columns appear as NULL — great for “users without orders”.

Left join

SELECT u.email, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

50. RIGHT JOIN in SQL

Many teams rewrite RIGHT JOINs as LEFT JOINs for readability.

Right join tip

RIGHT JOIN X = LEFT JOIN with tables swapped
Prefer LEFT JOIN style in code reviews

51. FULL OUTER JOIN in SQL

MySQL lacks native FULL OUTER JOIN — emulate with UNION of LEFT and RIGHT anti-join patterns.

Full outer note

Supported in PostgreSQL/SQL Server
MySQL: emulate with UNION

52. CROSS JOIN in SQL

Useful for generating combinations — dangerous if accidental.

Cross join

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

53. SELF JOIN in SQL

Common for employee→manager and category→parent patterns.

Self join

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

54. UNION in SQL

Column counts/types must align across queries.

Union

SELECT email FROM customers
UNION
SELECT email FROM subscribers;

55. UNION ALL in SQL

Usually faster than UNION when duplicates are acceptable.

Union all

SELECT email FROM customers
UNION ALL
SELECT email FROM subscribers;

56. Subqueries in SQL

Start readable — refactor to JOINs/CTEs when clearer or faster.

Subquery

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

57. Correlated Subqueries in SQL

Powerful but can be slow — compare with JOIN + GROUP BY alternatives.

Correlated idea

SELECT u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 100
);

58. Common Table Expressions (CTEs) in SQL

CTEs improve readability for multi-step queries.

CTE

WITH paid_orders AS (
  SELECT user_id, total FROM orders WHERE status = 'paid'
)
SELECT user_id, SUM(total) AS revenue
FROM paid_orders
GROUP BY user_id;

59. Recursive CTEs in SQL

Great for org charts and category trees — set depth guards.

Recursive idea

Anchor member
UNION ALL recursive member
Stop condition / depth limit

60. Views in SQL

Views simplify reporting; check updatability limits per engine.

View

CREATE VIEW paid_orders_v AS
SELECT id, user_id, total, created_at
FROM orders
WHERE status = 'paid';

61. Stored Procedures in SQL

Useful for complex transactional workflows; keep business logic balance with app layer.

Procedure idea (MySQL)

DELIMITER //
CREATE PROCEDURE mark_paid(IN order_id INT)
BEGIN
  UPDATE orders SET status = 'paid' WHERE id = order_id;
END//
DELIMITER ;

62. Stored Functions in SQL

Deterministic helpers can simplify queries — don’t hide heavy side effects in functions.

Function tip

Return a value
Use in SELECT list/WHERE
Mind performance & permissions

63. Triggers in SQL

Triggers are powerful but hard to debug — document them clearly.

Trigger tip

BEFORE/AFTER timing
Row-level vs statement-level
Keep logic minimal
Log side effects

64. Transactions in SQL

Use transactions for money transfers, inventory, and multi-table writes.

Transaction idea

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

65. COMMIT in SQL

After COMMIT, changes are durable (subject to engine/settings).

Commit

COMMIT;

66. ROLLBACK in SQL

Always handle errors by rolling back partial multi-step updates.

Rollback

ROLLBACK;

67. SAVEPOINT in SQL

Useful for complex procedures with optional sub-steps.

Savepoint

SAVEPOINT before_discount;
-- ...
ROLLBACK TO SAVEPOINT before_discount;

68. Indexes in SQL

Indexes speed reads but add write overhead and storage cost.

Create index

CREATE INDEX orders_user_id_idx ON orders (user_id);

69. Clustered vs Non-Clustered Indexes

In InnoDB, the primary key acts as the clustered index.

InnoDB note

Primary key ≈ clustered organization
Secondary indexes point back to PK
Choose PK wisely

70. Composite Indexes in SQL

Column order matters — leftmost prefix rule is key.

Composite index

CREATE INDEX orders_status_created_idx
ON orders (status, created_at);

71. Query Optimization in SQL

Measure with EXPLAIN; fix N+1 patterns in applications too.

Optimization tips

Index join/filter columns
Avoid SELECT *
Watch implicit casts
Prefer set-based SQL over row loops
Paginate efficiently

72. EXPLAIN in SQL

Look for full scans, bad join types, and unused indexes.

Explain

EXPLAIN SELECT * FROM orders WHERE user_id = 10;

73. Database Normalization

Normalization improves integrity; denormalize later only for measured performance needs.

Why normalize

Less duplication
Fewer update anomalies
Clearer relationships

74. First Normal Form (1NF)

Each cell holds one value; each row is unique.

1NF tip

No comma-separated lists in a column
Atomic fields
Unique rows

75. Second Normal Form (2NF)

Non-key attributes must depend on the whole key.

2NF tip

Start from 1NF
Full key dependency for non-key attrs

76. Third Normal Form (3NF)

Non-key attributes should depend only on the key.

3NF tip

No non-key → non-key dependencies
Separate lookup entities

77. Denormalization in SQL

Document why, how refreshed, and consistency rules.

Denormalize when

Heavy read paths
Expensive joins measured
Clear refresh strategy
Accept controlled redundancy

78. Database Relationships

Relationships drive foreign keys and join strategies.

Relationship map

1:1 → shared PK/unique FK
1:N → FK on many side
M:N → junction table

79. One-to-One Relationship in SQL

Often used for optional profile extensions.

1:1 sketch

CREATE TABLE user_profiles (
  user_id INT UNSIGNED PRIMARY KEY,
  bio TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

80. One-to-Many Relationship in SQL

Classic example: one user has many orders.

1:N sketch

users.id ← orders.user_id

81. Many-to-Many Relationship in SQL

Add payload columns (e.g., enrolled_at) on the junction when needed.

M:N sketch

CREATE TABLE course_user (
  course_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (course_id, user_id)
);

82. Referential Integrity in SQL

Foreign keys are the primary tool for referential integrity.

Integrity tip

FK constraints on
No orphan children
Define ON DELETE/UPDATE behavior

83. Cascading Updates and Deletes in SQL

CASCADE is powerful — prefer RESTRICT/SET NULL when deletes should be intentional.

Cascade example

FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE

84. SQL Injection Prevention

Never concatenate untrusted input into SQL. Use prepared statements / parameterized queries and least-privilege DB users.

Safe patterns

Prepared statements / parameter binding
ORM query builders with bindings
Whitelist sort columns
Least-privilege DB accounts
Input validation as defense in depth

85. SQL Security Best Practices

Encrypt connections, rotate credentials, and audit dangerous grants.

Security checklist

Parameterized queries
Least privilege
TLS to DB when remote
No shared root apps
Backups encrypted
Audit logs for admin actions

86. User Roles and Permissions in SQL

App runtime users usually need DML on specific tables — not FULL privileges.

Privilege idea

Separate users: migrate vs app vs report
Grant per DB/table
Revoke unused rights

87. GRANT and REVOKE in SQL

Document who has what — privilege drift causes incidents.

Grant/Revoke (MySQL-style)

GRANT SELECT, INSERT, UPDATE ON shop.* TO 'app'@'localhost';
REVOKE DELETE ON shop.* FROM 'app'@'localhost';

88. Backup and Restore in SQL

A backup you haven’t restored is a hope, not a plan.

Backup tip

Automated daily backups
Offsite copies
Test restore quarterly
mysqldump / snapshots / managed backups

89. Import and Export in SQL

Validate encodings and column mappings on import.

Import/export tip

SQL dumps for schema+data
CSV for spreadsheet exchanges
Validate row counts after import

90. MySQL with PHP

Avoid old insecure concatenation patterns; always bind parameters.

PDO prepare

$stmt = $pdo->prepare('SELECT id, email FROM users WHERE id = ?');
$stmt->execute([$id]);
$user = $stmt->fetch();

91. MySQL with Laravel

Prefer Eloquent/query builder over raw DB::select with string concatenation.

Laravel query

$users = DB::table('users')
    ->where('email', $email)
    ->first();

92. MySQL with CodeIgniter

Query builder bindings help prevent injection when used correctly.

CI query builder idea

$row = $this->db->where('email', $email)->get('users')->row();

93. MySQL with Node.js

Always use `?` / named placeholders — never string-build SQL with user input.

mysql2 example

const [rows] = await pool.execute(
  'SELECT id, email FROM users WHERE id = ?',
  [id]
);

94. SQL for WordPress Development

Prefer WP functions; use $wpdb->prepare when custom SQL is necessary.

WP tip

Prefer WP_Query / APIs
Custom tables via dbDelta carefully
Always $wpdb->prepare for custom SQL

95. WordPress Database Structure

Knowing the schema helps debugging and custom reporting.

Core tables

wp_posts / wp_postmeta
wp_users / wp_usermeta
wp_terms / taxonomy tables
wp_options
wp_comments

96. $wpdb and SQL in WordPress

Escaping with prepare is mandatory for any interpolated values.

$wpdb prepare

global $wpdb;
$row = $wpdb->get_row(
  $wpdb->prepare("SELECT ID, user_email FROM {$wpdb->users} WHERE ID = %d", $user_id)
);

97. SQL Interview Questions

Be ready for joins, indexes, normalization, transactions, and NULL behavior.

Sample Q&A

Q: WHERE vs HAVING?
A: WHERE filters rows; HAVING filters groups.

Q: LEFT vs INNER JOIN?
A: LEFT keeps all left rows; INNER only matches.

Q: Why indexes?
A: Faster lookups/joins; trade-off on writes.

98. SQL Query Practice

Drill filters, joins, aggregates, CTEs, and explain plans.

Practice set ideas

Top customers by revenue
Products never ordered
Monthly order counts
Running totals (window funcs if available)
Duplicate email detection

99. Real-World Database Project

Include ER modeling, migrations, seed data, and reporting queries.

Project flow

Requirements → ERD
Tables + constraints
Seed data
CRUD + reports
Indexes + EXPLAIN

100. Final Project – Complete Database Management System

Build a mini DBMS for a domain (shop, LMS, or clinic): schema + constraints, sample data, views/procedures, transactional workflows, indexes, backup plan, and a small app layer (PHP/Node/Laravel) using parameterized queries. Include an ER diagram and README.

  1. Model the domain and ERD.
  2. Implement schema, seeds, and constraints.
  3. Write reports, transactions, and indexes.
  4. Connect a small app securely and document backups.

Final project scope

1. Domain + ER diagram
2. Normalized schema (3NF target) + FKs
3. Seed scripts
4. Core CRUD queries
5. Reporting queries (JOIN + GROUP BY)
6. At least one transaction workflow
7. Indexes + EXPLAIN notes
8. Views and/or procedure
9. Roles/privileges sketch
10. App demo with prepared statements
11. Backup/restore steps

Suggested domains

E-commerce (users, products, orders)
LMS (courses, enrollments, lessons)
Helpdesk (tickets, agents, replies)

Conclusion

You now have a full SQL path: schema design, querying, joins, transactions, performance, and secure application access. Finish the final database management project to turn the lessons into a portfolio-ready system.

Keep reading

Related guides

More setup guides and walkthroughs worth reading next.

Leave a reply

Your email address will not be published. Required fields are marked *