SQL Simplified: A Guide for Beginners, Developers, and Data Analysts

For SQL beginners seeking a solid foundation, seasoned developers aiming to fill knowledge gaps in table creation and data retrieval, and data analysts looking to refine their querying skills for actionable insights.

---

Simplified SQL Quick Guide For Quick wins

SQL Table Creation

The following SQL statement creates a table named example with specific constraints and relationships.

create table example (
    id int generated always as identity primary key,
    name varchar(50) not null,
    user_id int,
    price decimal(10,2) check(price >= 0),
    unique(name, id),
    constraint fk_user_id foreign key(user_id)
        references users(id)
        on delete set null on update cascade
)

Breakdown of Concepts in the Statement

Concept	Description	Example
id int generated always as identity primary key	Automatically generates a unique integer for each row, serving as the primary key (uniquely identifies each row).	id will auto increment for each new row (e.g. 1, 2, 3, ...).
name varchar(50) not null	Defines a column name of type varchar (variable length string) with a max length of 50 characters. not null ensures this column cannot have NULL values.	name = "John Doe" (valid), name = NULL (invalid).
user_id int	Defines a column user_id of type int (integer).	user_id = 123 (references a user ID from another table).
price decimal(10,2) check(price >= 0)	Defines a column price of type decimal (precise numeric) with 10 digits total and 2 decimal places. check ensures price is non negative.	price = 19.99 (valid), price = -5.00 (invalid).
unique(name, id)	Ensures the combination of name and id is unique across all rows.	No two rows can have the same name and id pair.
constraint fk_user_id foreign key(user_id) references users(id)	Creates a foreign key constraint: user_id must match an existing id in the users table.	If users(id) has 1, 2, 3, user_id can only be 1, 2, or 3.
on delete set null	If a referenced user in users(id) is deleted, user_id in this table is set to NULL.	If users(id=1) is deleted, all rows with user_id=1 will have user_id set to NULL.
on update cascade	If a referenced user in users(id) is updated, user_id in this table is automatically updated to match.	If users(id=1) is changed to id=4, all rows with user_id=1 will update to user_id=4.

---

DRL (Data Retrieval Language) Concepts

Basic Queries

• select username as "Noms", 10 + 2 as resultat from users
as is optional and used for aliasing (renaming columns in the result).
  Example:

  select u.username from users u

  Renames username to Noms and calculates 10 + 2 as resultat.

• select distinct country from users
  Returns unique values of country (no duplicates).

• concat
  Concatenates strings or field values.
  Syntax: concat(string1, string2) or string1 || string2.
  Example:

  select concat(first_name, ' ', last_name) as full_name from users;

---

Filtering with WHERE

• where filters rows based on conditions.

• Operators: =, >, <, >=, <=, <>, between, in, like, is null, is not null.

• Example:

  select * from users where age > 18 and country = 'Belgium';

LIKE vs ILIKE

Operator	Description	Example	Result
LIKE	Case sensitive pattern matching.	where name LIKE 'J%'	Matches "John", "Jane" (not "john").
ILIKE	Case insensitive pattern matching (PostgreSQL specific).	where name ILIKE 'j%'	Matches "John", "jane", "JOHN".
%	Wildcard: Matches any sequence of characters (including none).	where name LIKE 'J%'	"John", "Jenny", "J".
_	Wildcard: Matches exactly one character.	where name LIKE 'J_ne'	"Jane", "June" (not "Jenny").

---

Sorting with ORDER BY

Sorts results by one or more columns.
Example:

select * from users order by last_name ASC, age DESC;

Sorts by last_name (A Z) and then by age (highest first).

---

Handling NULL Values

Operator	Description	Example
IS NULL	Checks if a value is NULL.	where user_id IS NULL
IS NOT NULL	Checks if a value is not NULL.	where user_id IS NOT NULL
<> NULL	Incorrect! NULL is not equal to anything, even itself. Use IS NULL.	Not valid where user_id <> NULL (invalid) Valid where user_id IS NOT NULL

---

Logical Operators

Operator	Description	Example
NOT	Negates a condition.	where NOT (age > 18) means age <= 18
!	Alternative to NOT (in some SQL dialects).	where !(age > 18)
SIMILAR	Checks if a string matches a regular expression (PostgreSQL specific).	where name SIMILAR TO 'J[ae]n(e y)' Matches "Jane", "Jenny", "Jany".

---

Operator Precedence in SQL

SQL evaluates operators in this order (highest to lowest):

1. Parentheses ()

2. NOT

3. AND

4. OR

5. Comparison operators (=, >, <, etc.)

Example:

where age > 18 AND (country = 'Belgium' OR country = 'France')

AND is evaluated before OR unless parentheses are used.

---

Quotes vs. Single Quotes

Usage	Example	Purpose
Double Quotes	select "Nom" from users	For aliases, table names, or column names (e.g. with spaces).
Single Quotes	insert into users (name) values ('John Doe')	For string literals (actual values).

---

SQL Functions

Native SQL Functions

Category	Function	Description	Example
Date/Time	current_date	Returns the current date.	select current_date; means 2026 07 23
	current_time	Returns the current time.	select current_time; means 14:30:45
	now() / current_timestamp	Returns the current date and time.	select now(); means 2026 07 23 14:30:45
	date_part / extract	Extracts a part of a date (e.g. year, month).	select date_part('year', birth_date) from users; means 1990
	to_char	Converts a date to a formatted string.	select to_char(now(), 'YYYY MM DD'); means "2026 07 23"
String	position	Returns the position of a substring (case sensitive, starts at 1).	select position('o' in 'John'); means 2
	length / char_length	Returns the length of a string.	select length('Hello'); means 5
	substring	Extracts a substring from a string.	select substring('Hello', 2, 3); means "ell"
	left / right	Returns the left/right part of a string.	select left('Hello', 2); means "He"
	upper / lower	Converts a string to uppercase/lowercase.	select upper('hello'); means "HELLO"
	replace	Replaces a substring with another.	select replace('Hello', 'l', 'x'); means "Hexxo"
	trim / ltrim / rtrim	Removes leading/trailing spaces.	select trim(' Hello '); means "Hello"
Numeric	abs / @	Returns the absolute value.	select abs(-5); means 5
	mod / %	Returns the remainder of a division.	select 10 % 3; means 1
Type Casting	cast / ::	Converts a value to a different type.	select cast('123' as int); means 123 or select '123'::int; means 123

---

Aggregation Functions

Function	Description	Example
count	Counts the number of rows (returns bigint).	select count(*) from users; means 100
max	Returns the maximum value in a column.	select max(age) from users; means 99
min	Returns the minimum value in a column.	select min(age) from users; means 18
avg	Returns the average value in a column.	select avg(price) from products; means 19.99
sum	Returns the sum of values in a column.	select sum(price) from products; means 1999.00
case	Conditional logic (like if else).	select case when age > 18 then 'Adult' else 'Minor' end from users;
nullif	Returns NULL if two values are equal.	select nullif(age, 18) from users; means NULL if age = 18
coalesce	Returns the first non NULL value in a list.	select coalesce(name, 'Unknown') from users; means "John" or "Unknown"

---

Summary

This article covers:

• SQL table creation with constraints.
• Data retrieval using SELECT, WHERE, ORDER BY, and logical operators.
• Handling NULL values and pattern matching with LIKE/ILIKE.
• SQL functions for strings, numbers, dates, and aggregations.

Disclaimer: this article was brainstormed with Mistral Medium 3.5 and automated with Mistral Medium 3.5 and Gemini 3.6 Flash based on strict guidance and control of the AI artist. The brainstorm was fact-checked by the same AI artist. The article was amended for inaccuracies and deviation by the same AI artist. It is compliant with the human-in-the-loop, the iteration, and the automation practices. No AI was hurt during the implementation of the above practices.