Post cover image

September 20, 2026

SQL Fundamentals — Complete Beginner Guide

Introduction

By Shadow

10 min read

SQL Fundamentals — Complete Beginner Guide

Introduction

Databases are one of the most important components of modern web applications.

Websites and applications use databases to store and manage information such as users, products, orders, messages, and authentication data.

For anyone learning web penetration testing, understanding databases and SQL is essential because many web applications rely on SQL queries to process user input.

This guide covers the fundamental concepts of databases, SQL, DBMS, database structure, CRUD operations, filtering, operators, functions, and relationships between tables.

1. What is a Database?

A database is an organized collection of data that can be stored, accessed, managed, and analyzed.

Databases are used in many types of applications, including:

  • Authentication systems
  • Social media platforms
  • Streaming services
  • E-commerce websites
  • Banking systems
  • Content management systems
  • Web applications

A simplified web application architecture looks like:

User
  ↓
Web Application
  ↓
Database
  ↓
Stored Data

For example, an e-commerce application might store:

Users
Products
Orders
Payments
Addresses

2. Types of Databases

There are two major categories worth understanding at the beginner level:

Relational Databases — SQL

Relational databases store structured data using tables.

A table consists of:

Rows
Columns

Examples include:

  • MySQL
  • MariaDB
  • PostgreSQL
  • Oracle Database
  • Microsoft SQL Server
  • SQLite

Relational databases are commonly used when data has clearly defined relationships and structures.

Examples:

Users
Products
Orders
Customers
Transactions

Non-Relational Databases — NoSQL

NoSQL databases do not necessarily use traditional tables, rows, and columns.

They can store data using formats such as:

  • Documents
  • Key-value pairs
  • Graphs
  • Wide-column structures

Examples include:

  • MongoDB
  • Redis
  • Cassandra

NoSQL databases are useful when applications need flexible data structures or specific scalability models.

Important distinction

SQL
→ Usually structured relational data
NoSQL
→ Flexible non-relational data models

NoSQL does not simply mean "no structure"; it means the database does not primarily follow the traditional relational model.

3. Tables, Rows, and Columns

Understanding the structure of a relational database is essential.

Table

A table is a collection of related data.

Example:

Books
--------------------------------
id | name | author
--------------------------------
1  | Book A | Author 1
2  | Book B | Author 2
3  | Book C | Author 3

Column

A column defines an attribute of the stored data.

Examples:

id
name
email
author
price

Each column normally has a specific data type.

Row

A row represents one complete record.

For example:

1 | Book A | Author 1

is one row in the Books table.

4. Common Data Types

Databases support different types of data.

Common examples include:

String

Used for text.

Alice
hello@example.com
Book A

Integer

Used for whole numbers.

1
25
100

Float / Decimal

Used for numbers containing decimal values.

19.99
3.14
99.50

Date / Time

Used for dates and timestamps.

2026-09-20
2026-09-20 14:30:00

The exact data types available can vary between database systems.

5. Primary Keys

A Primary Key (PK) uniquely identifies a row in a table.

Example:

Books
-------------------
id (PK)
-------------------
1
2
3

The primary key should uniquely identify each record.

Important characteristics:

  • Uniquely identifies a record
  • Cannot contain duplicate values
  • A table has one primary key constraint
  • It can consist of one column or multiple columns

For a simple example:

id | name
-----------
1  | Book A
2  | Book B
3  | Book C

The id column identifies each book.

6. Foreign Keys

A Foreign Key (FK) creates a relationship between tables.

For example:

Books
----------------
id
author_id  ← FK

and:

Authors
----------------
id  ← PK
name

The relationship can be represented as:

Books.author_id
       |
       ↓
Authors.id

A foreign key usually references a primary key or another candidate key in another table.

Unlike a primary key, a foreign key can contain repeated values because multiple records may reference the same record.

7. Primary Key vs Foreign Key

Primary KeyForeign KeyUniquely identifies a recordReferences a record in another tableEnforces uniquenessDoes not have to be uniqueUsed to identify rowsUsed to create relationshipsOne primary key constraint per tableMultiple foreign key constraints can existCommonly referenced by foreign keysCommonly references another table's key

Easy way to remember

PK = Unique identifier
FK = Link between tables

8. What is SQL?

SQL stands for Structured Query Language.

It is used to communicate with relational database systems.

SQL can be used to:

  • Retrieve data
  • Insert data
  • Update data
  • Delete data
  • Create databases
  • Create tables
  • Modify tables
  • Manage database structures

Example:

SELECT * FROM users;

This asks the database to return all columns from the users table.

9. What is a DBMS?

DBMS stands for Database Management System.

A DBMS is software used to create, manage, store, and interact with databases.

Examples include:

  • MySQL
  • MariaDB
  • PostgreSQL
  • Oracle Database
  • Microsoft SQL Server
  • SQLite
  • MongoDB

One important distinction:

Database
= Stored data
DBMS
= Software that manages the database
SQL
= Language used to communicate with a relational DBMS

MongoDB is a DBMS, but it is a NoSQL database system rather than a traditional relational SQL DBMS.

10. Connecting to MySQL

A common MySQL login command is:

mysql -u root -p

The options mean:

-u
→ Specify username
root
→ Username
-p
→ Prompt for password

After successful authentication, you may see:

mysql>

This indicates that you are inside the MySQL command-line client.

11. Creating a Database

To create a database:

CREATE DATABASE database_name;

For example:

CREATE DATABASE library;

This creates a database called library.

12. Listing Databases

To display available databases:

SHOW DATABASES;

Example output might include:

information_schema
library
mysql
performance_schema

13. Selecting a Database

Before working with tables inside a database, select it:

USE database_name;

Example:

USE library;

You are now working with the library database.

14. Deleting a Database

A database can be permanently removed using:

DROP DATABASE database_name;

Example:

DROP DATABASE library;

Important: DROP DATABASE permanently removes the database and its contents.

15. Creating a Table

A table can be created with:

CREATE TABLE table_name (
    column_name data_type
);

For example:

CREATE TABLE books (
    id INT,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

This creates a table containing:

id
name
price

16. Showing Tables

To list the tables in the currently selected database:

SHOW TABLES;

17. Describing a Table

To inspect the structure of a table:

DESCRIBE books;

or:

DESC books;

This can show information such as:

  • Column names
  • Data types
  • Keys
  • Whether NULL values are allowed
  • Default values
  • Additional attributes

This is an important command when performing database enumeration.

18. Altering a Table

The ALTER TABLE statement modifies an existing table.

For example:

ALTER TABLE books
ADD description TEXT;

This adds a new description column.

19. Dropping a Table

To permanently delete a table:

DROP TABLE books;

This removes the table and its data.

20. CRUD Operations

CRUD represents the four fundamental operations performed on data.

C = Create
R = Read
U = Update
D = Delete

These correspond to:

INSERT
SELECT
UPDATE
DELETE

21. CREATE — INSERT

The INSERT statement adds a new record.

Example:

INSERT INTO books
(id, name)
VALUES (1, 'Book A');

Multiple values can also be inserted:

INSERT INTO books (id, name)
VALUES
(1, 'Book A'),
(2, 'Book B'),
(3, 'Book C');

22. READ — SELECT

The SELECT statement retrieves data.

To retrieve everything:

SELECT * FROM books;

The * means all columns.

To retrieve specific columns:

SELECT name, price
FROM books;

This returns only the selected columns.

23. UPDATE

The UPDATE statement modifies existing records.

Example:

UPDATE books
SET name = 'New Book'
WHERE id = 1;

The WHERE clause is important because it specifies which record should be modified.

Without an appropriate WHERE condition, an UPDATE statement can affect multiple or all rows.

24. DELETE

The DELETE statement removes records.

Example:

DELETE FROM books
WHERE id = 1;

Again, the WHERE clause determines which rows are deleted.

Without a WHERE clause:

DELETE FROM books;

all rows in the table may be deleted.

25. WHERE Clause

The WHERE clause filters records based on a condition.

Example:

SELECT *
FROM books
WHERE id = 1;

This returns only records where the id is 1.

WHERE is one of the most important SQL concepts because it controls which records are affected by a query.

26. Comparison Operators

SQL provides operators for comparing values.

OperatorMeaning=Equal!=Not equal<>Not equalGreater than<=Less than or equal>=Greater than or equal

Examples:

SELECT * FROM books WHERE price > 20;
SELECT * FROM books WHERE id != 5;

27. AND

AND requires all conditions to be true.

Example:

SELECT *
FROM books
WHERE price > 10 AND price < 50;

Both conditions must be satisfied.

28. OR

OR requires at least one condition to be true.

Example:

SELECT *
FROM books
WHERE id = 1 OR id = 2;

The result can contain records matching either condition.

29. NOT

NOT reverses a condition.

Example:

SELECT *
FROM books
WHERE NOT id = 1;

This selects records where the condition is not true.

30. BETWEEN

BETWEEN checks whether a value falls within a range.

Example:

SELECT *
FROM books
WHERE id BETWEEN 2 AND 4;

This selects values within the specified range.

31. LIKE

LIKE is used for pattern matching.

For example:

SELECT *
FROM books
WHERE name LIKE '%book%';

The % wildcard represents zero or more characters.

Common patterns include:

LIKE 'book%'

Starts with book.

LIKE '%book'

Ends with book.

LIKE '%book%'

Contains book.

32. DISTINCT

DISTINCT removes duplicate values from the result.

Example:

SELECT DISTINCT author
FROM books;

Instead of showing the same author multiple times, SQL returns each unique value once.

33. ORDER BY

ORDER BY sorts query results.

Example:

SELECT *
FROM books
ORDER BY price;

By default, the results are generally sorted in ascending order.

For descending order:

SELECT *
FROM books
ORDER BY price DESC;

For ascending order:

SELECT *
FROM books
ORDER BY price ASC;

34. LIMIT

LIMIT restricts the number of rows returned.

Example:

SELECT *
FROM books
LIMIT 5;

This returns up to five rows.

It is useful when working with large datasets.

35. NULL Values

NULL represents a missing or unknown value.

It is important to understand that:

NULL ≠ 0
NULL ≠ ''
NULL ≠ FALSE

To check for NULL values, use:

SELECT *
FROM users
WHERE email IS NULL;

To find values that are not NULL:

SELECT *
FROM users
WHERE email IS NOT NULL;

You should not normally use:

WHERE email = NULL

because NULL requires IS NULL or IS NOT NULL.

36. String Functions

SQL provides functions for manipulating text.

CONCAT()

Combines strings.

SELECT CONCAT(first_name, ' ', last_name)
FROM users;

GROUP_CONCAT()

Combines values from multiple rows into a single string in database systems that support it, such as MySQL.

Example:

SELECT GROUP_CONCAT(name)
FROM users;

SUBSTRING()

Extracts part of a string.

Example:

SELECT SUBSTRING('Cybersecurity', 1, 4);

This extracts a portion of the string.

LENGTH()

Returns the length of a string.

Example:

SELECT LENGTH('Cybersecurity');

37. Aggregate Functions

Aggregate functions perform calculations across multiple rows.

COUNT()

Counts rows.

SELECT COUNT(*)
FROM users;

SUM()

Adds numeric values.

SELECT SUM(price)
FROM products;

MAX()

Returns the largest value.

SELECT MAX(price)
FROM products;

MIN()

Returns the smallest value.

SELECT MIN(price)
FROM products;

Another important aggregate function is:

AVG()

Calculates the average value.

SELECT AVG(price)
FROM products;

38. GROUP BY

GROUP BY groups rows that have the same value.

For example:

SELECT author, COUNT(*)
FROM books
GROUP BY author;

This can show how many books belong to each author.

A simplified result could look like:

Author  | COUNT
----------------
Alice   | 3
Bob     | 5
Charlie | 2

39. HAVING

HAVING filters grouped results.

Example:

SELECT author, COUNT(*)
FROM books
GROUP BY author
HAVING COUNT(*) > 2;

The important difference is:

WHERE
→ Filters rows before grouping
HAVING
→ Filters groups after grouping

40. SQL JOINs

Relational databases are designed around relationships between tables.

JOIN statements allow data from multiple tables to be combined.

Suppose we have:

Users
----------------
id
name

and:

Orders
----------------
id
user_id
product

The user_id can reference Users.id.

INNER JOIN

Returns matching records from both tables.

SELECT users.name, orders.product
FROM users
INNER JOIN orders
ON users.id = orders.user_id;

LEFT JOIN

Returns all rows from the left table and matching rows from the right table.

SELECT users.name, orders.product
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;

If a user has no matching order, the order fields can contain NULL.

41. SQL Comments

SQL supports comments, although the exact syntax can vary between database systems.

A common single-line comment syntax is:

-- comment

MySQL also supports:

# comment

Comments are useful when documenting SQL queries and understanding existing database code.

42. SQL Statement Structure

A basic SQL query often follows this structure:

SELECT column
FROM table
WHERE condition
ORDER BY column
LIMIT number;

For example:

SELECT name, price
FROM books
WHERE price > 20
ORDER BY price DESC
LIMIT 5;

This query:

  1. Selects name and price
  2. From the books table
  3. Keeps books costing more than 20
  4. Sorts them from highest to lowest price
  5. Returns up to five results

43. SQL Fundamentals for Web Security

Understanding SQL is especially important for web penetration testing.

A typical web application might receive input:

/search?id=10

The application could use that value in a database query.

Conceptually:

HTTP Request
     ↓
Web Application
     ↓
SQL Query
     ↓
Database
     ↓
Response

If developers incorrectly construct SQL queries using untrusted input, SQL Injection may become possible.

This is why learning SQL fundamentals should come before learning tools such as SQLMap.

You should understand what the application is doing before trying to automate the testing.

44. SQL vs SQL Injection

These concepts should not be confused.

SQL

A legitimate language used to communicate with relational databases.

SQL Injection

A security vulnerability that occurs when untrusted input can improperly influence an SQL query.

SQL
→ Language
SQL Injection
→ Vulnerability

SQL itself is not a vulnerability.

45. Quick SQL Cheat Sheet

Database

CREATE DATABASE database_name;
SHOW DATABASES;
USE database_name;
DROP DATABASE database_name;

Tables

CREATE TABLE table_name (...);
SHOW TABLES;
DESCRIBE table_name;
ALTER TABLE table_name ...;
DROP TABLE table_name;

CRUD

INSERT INTO table_name (...) VALUES (...);
SELECT * FROM table_name;
UPDATE table_name
SET column = value
WHERE condition;
DELETE FROM table_name
WHERE condition;

Filtering

WHERE
AND
OR
NOT
LIKE
BETWEEN

Sorting and Limiting

ORDER BY
LIMIT
DISTINCT

Aggregation

COUNT()
SUM()
AVG()
MAX()
MIN()
GROUP BY
HAVING

Relationships

JOIN
INNER JOIN
LEFT JOIN

String Functions

CONCAT()
GROUP_CONCAT()
SUBSTRING()
LENGTH()

Key Takeaways

After studying SQL Fundamentals, I should be able to explain:

  • What a database is
  • The difference between SQL and NoSQL
  • What a relational database is
  • What tables, rows, and columns represent
  • What primary and foreign keys are
  • What SQL is
  • What a DBMS is
  • How to connect to MySQL
  • How to create and manage databases
  • How to create and manage tables
  • How CRUD operations work
  • How SELECT retrieves data
  • How WHERE filters data
  • How comparison and logical operators work
  • How LIKE performs pattern matching
  • How ORDER BY sorts results
  • How LIMIT restricts results
  • How DISTINCT removes duplicates
  • How NULL values work
  • How aggregate functions work
  • How GROUP BY and HAVING work
  • How tables can be connected using JOINs
  • Why SQL knowledge is important for web penetration testing

Conclusion

SQL fundamentals are an important foundation for anyone interested in web security and penetration testing.

Before studying SQL Injection or using automated tools such as SQLMap, it is important to understand how databases are structured and how SQL queries work.

The core concepts to remember are:

Database
→ Stores data
DBMS
→ Manages the database
SQL
→ Communicates with relational databases
Table
→ Stores related records
Row
→ Represents one record
Column
→ Represents an attribute
Primary Key
→ Uniquely identifies a record
Foreign Key
→ Creates a relationship between tables
CRUD
→ Create, Read, Update, Delete

Once these fundamentals are clear, topics such as SQL Injection, SQLMap, database enumeration, and web application security become much easier to understand.

Main lesson: don't memorize SQL commands only. Understand what each query does to the database and why the result changes depending on the conditions you provide.