Key SQL Concepts I've Learned

Introduction

Learning SQL has been an exciting journey that has changed the way I think about data. As a beginner, I've gone from writing simple queries to understanding how databases are structured and how information can be retrieved, filtered, and connected. In this article, I'll share the key SQL concepts I've learned so far.

SQL Data Types

SQL data types define the kind of values that can be stored in a table, ensuring data is stored accurately and efficiently.At first they seemed a bit confusing but after enough practise the diffference became clear to me

They include;

Numeric Data Types

Used for storing numbers.

1. INT / INTEGER → Whole numbers (e.g., 1, 99)
2. SMALLINT → Smaller range of integers
3. BIGINT → Large integers
4. DECIMAL(p,s) → Fixed precision numbers (e.g., DECIMAL(10,2) = up to 10 digits total, 2 after decimal)
5. NUMERIC(p,s) → Same as DECIMAL in many databases
6. FLOAT / REAL / DOUBLE PRECISION → Floating-point numbers, approximate values

String (Character) Data Types

Used for text.

1. CHAR(n) → Fixed-length string (e.g., CHAR(5) always stores 5 characters, padded if shorter)
2. VARCHAR(n) → Variable-length string up to n characters
3. TEXT → Very large text data, no strict length limit in many databases

Date and Time Data Types

Used for dates and times.

1. DATE → Stores only a date (e.g., 2025-07-15)
2. TIME → Stores only time (e.g., 14:23:45)
3. DATETIME → Stores date and time (e.g., 2025-07-15 14:23:45)
4. TIMESTAMP → Similar to DATETIME, often includes time zone support in some databases
5. YEAR → Stores year value (e.g., 2025)

Boolean Data Types

1. BOOLEAN → Stores TRUE or FALSE

• Some databases store it as TINYINT (1 = TRUE, 0 = FALSE)

Other Specialized Types

ENUM → Stores one value from a predefined list (e.g., 'small', 'medium', 'large')
UUID → Universally unique identifier
GEOMETRY / GEOGRAPHY → Spatial data types for GIS applications

SQL CLAUSES

SQL clauses are keywords that define how a query should retrieve, filter, group, or sort data from a database. They work together to control the behavior of a SQL statement and produce the desired result.

SQL Keyword	Purpose
SELECT	Retrieve data from the database
FROM	Specify the table to select data from
WHERE	Filter rows based on a condition
AS	Rename a column or table with an alias
JOIN	Combine rows from two or more tables
ON	Specify the condition for a JOIN
AND	Combine multiple conditions; all must be true
OR	Combine multiple conditions; at least one must be true
LIMIT	Restrict the number of rows returned
IN	Specify multiple values in a WHERE clause
CASE	Create conditional logic within a SQL statement
IS NULL	Check for empty or NULL values
ORDER BY	Sort the result set by one or more columns
GROUP BY	Group rows that share a property into summary rows
HAVING	Filter groups based on conditions
INSERT INTO	Add new records to a table
VALUES	Specify values to insert into the table
UPDATE	Modify existing records in a table
SET	Specify the column and values to update
DELETE	Remove records from a table
ALTER	Modify the structure of a table
DROP	Delete tables, databases, or indexes
DISTINCT	Retrieve unique values from a column
TRUNCATE	Delete all records from a table while keeping its structure
UNION	Combine the result sets of two or more SELECT queries

CLASSIFICATION OF SQL COMMANDS

SQL commands are grouped into different categories based on the tasks they perform within a database. Understanding these command types makes it easier to create, manage, query, and secure databases efficiently.

5 Types of SQL Commands

1. Data Definition Language (DDL)

Defines and modifies the structure of database objects such as tables, indexes, and schemas.

Common DDL Commands:

• CREATE
• ALTER
• DROP
• TRUNCATE
• RENAME

2. Data Manipulation Language (DML)

Used to add, modify, and remove data stored in database tables.

Common DML Commands:

• INSERT
• UPDATE
• DELETE
• MERGE

3. Data Control Language (DCL)

Controls user access and permissions within a database, helping maintain security.

Common DCL Commands:

• GRANT
• REVOKE

4. Transaction Control Language (TCL)

Manages transactions to ensure data integrity and allows changes to be committed or rolled back when necessary.

Common TCL Commands:

• COMMIT
• ROLLBACK
• SAVEPOINT
• SET TRANSACTION

5. Data Query Language (DQL)

Primarily used to retrieve data from a database.

Common DQL Command:

• SELECT

SQL QUERY ORDER

One of the most important lessons I learned in SQL is that queries are written in one order but executed in another. Understanding the SQL execution order helps explain why some queries behave differently than expected and makes it easier to write efficient SQL statements.

Although we write SQL queries starting with SELECT, the database processes them in a different sequence.

SQL Query (Coding Order)

This is the actual order that one uses when writing out a query

SELECT⇒DISTINCT⇒FROM⇒JOIN⇒WHERE⇒GROUP BY⇒HAVING⇒ORDER BY⇒LIMIT

But this is how sql executes the query :

Execution Order	Clause	Purpose
1	FROM	Identifies the table(s) from which data is retrieved.
2	JOIN	Combines rows from multiple tables based on a matching condition.
3	WHERE	Filters individual rows before any grouping occurs.
4	GROUP BY	Groups rows that have the same values into summary groups.
5	HAVING	Filters the grouped results after aggregation.
6	SELECT	Chooses the columns and performs calculations to return.
7	DISTINCT	Removes duplicate rows from the selected results.
8	ORDER BY	Sorts the output based on one or more columns.
9	LIMIT	Restricts the number of rows returned.