Beginner · SQL Mastery Series

SQL Foundations: The Complete Beginner's Guide

Everything you need to go from zero to confident with SQL — from understanding what a database is to writing real queries with SELECT, WHERE, JOINs, functions, and more. Oracle, MySQL, and PostgreSQL covered.

⏱28 min read📅September 10, 2025✍️Priyanshu Pandey📚SQL Mastery Series

What is SQL? The Language of Databases

SQL (Structured Query Language) is the standard language of relational databases. It lets you create, read, update, and manage data in systems like Oracle, MySQL, SQL Server, and PostgreSQL.

Think of SQL as the Google Search of databases — you describe what data you want, and the database engine finds and returns it.

Why SQL matters:

Data is everywhere — from apps to websites to enterprise systems, everything runs on data stored in relational databases.
Universal skill — SQL syntax is largely the same across Oracle, MySQL, PostgreSQL, and SQL Server, so learning it once works everywhere.
Business backbone — organizations rely on SQL for analytics, reporting, transactions, and integrations.
Career multiplier — SQL consistently ranks as one of the most in-demand technical skills, relevant for developers, analysts, and architects alike.

SQL commands are grouped into categories based on what they do:

Column	Type	Description
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE — define and modify database structure
DML	Data Manipulation Language	INSERT, UPDATE, DELETE — add, change, or remove data
DQL	Data Query Language	SELECT — retrieve data from tables
DCL	Data Control Language	GRANT, REVOKE — control permissions
TCL	Transaction Control Language	COMMIT, ROLLBACK, SAVEPOINT — manage transactions

💡

SQL is declarative

You tell SQL what you want, not how to get it. The database engine figures out the execution plan. This is what makes SQL so powerful — you write 10 lines and the engine does the work of thousands of procedural operations.

Data Types

SQL Data Types — Choosing the Right Column Type

When designing a table, the most important decision for each column is its data type. The right type ensures efficient storage, data integrity, and faster queries.

Numeric Types

Column	Type	Description
INT / INTEGER	Whole number	Employee IDs, quantities, counts. No decimal places.
SMALLINT	Small integer	Age, status codes. Saves storage for small-range values.
BIGINT	Large integer	Population figures, auto-increment PKs in large systems.
DECIMAL(p,s)	Fixed-point	Financial values like salary, price, tax amounts. Use p=precision (total digits), s=scale (decimal places).
FLOAT / REAL	Approximate decimal	Scientific measurements, ratings. Avoid for money — use DECIMAL instead.
NUMBER(p,s)	Oracle native	Oracle's general-purpose numeric type. Equivalent to DECIMAL in most contexts.

⚠️

Never use FLOAT for money

Floating-point types (FLOAT, REAL, DOUBLE) are approximate by design — they can introduce rounding errors. Always use DECIMAL(10,2) or Oracle's NUMBER(10,2) for monetary values like salaries, prices, or tax amounts.

String / Character Types

Column	Type	Description
CHAR(n)	Fixed-length string	Country codes, gender flags — values that are always exactly n characters.
VARCHAR(n)	Variable-length	Names, descriptions — values of varying length up to n characters.
VARCHAR2(n)	Oracle native VARCHAR	Oracle's preferred variable-length string type. Functionally identical to VARCHAR.
TEXT / CLOB	Large text	Long descriptions, notes, HTML content. No length limit (CLOB = Character Large Object).

Date & Time Types

Column	Type	Description
DATE	Date + time	In Oracle: stores full date and time. In MySQL/PostgreSQL: date only.
TIMESTAMP	High-precision date+time	Order timestamps, audit logs. Includes fractional seconds.
TIME	Time only	Store opening/closing hours without a date component.
INTERVAL	Duration	Differences between dates — useful in Oracle for date arithmetic.

⚠️

Oracle DATE includes time — this surprises many developers

In Oracle, a DATE column stores both date and time (down to seconds). If you store SYSDATE in a DATE column, you get the time too. Always use TRUNC(date_col) when you want to compare dates without the time component, otherwise your WHERE clause will miss rows.

Other Important Types

Column	Type	Description
BOOLEAN	True/False	MySQL and PostgreSQL native. Oracle uses NUMBER(1) or VARCHAR2(1) with Y/N convention.
BLOB	Binary Large Object	Images, PDFs, binary files stored in the database.
ROWID	Oracle internal	Oracle's physical row address. Used for ultra-fast single-row lookups.

DDL — Schema Control

DDL — CREATE, ALTER, DROP

DDL (Data Definition Language) commands define and change the structure of your database objects — databases, tables, indexes, and constraints.

CREATE TABLE

Create a simple employees table

SQL

CREATE TABLE employees (
    emp_id      NUMBER(10)      PRIMARY KEY,
    first_name  VARCHAR2(50)    NOT NULL,
    last_name   VARCHAR2(50)    NOT NULL,
    department  VARCHAR2(50),
    salary      DECIMAL(10,2)   DEFAULT 0,
    hire_date   DATE            DEFAULT SYSDATE,
    status      CHAR(1)         DEFAULT 'A'
);

CREATE DATABASE (MySQL / PostgreSQL)

MySQL / PostgreSQL

SQL

CREATE DATABASE retailcoder_db;

-- Switch to the new database (MySQL)
USE retailcoder_db;

ALTER TABLE — Modify Structure

Add a new column

SQL

ALTER TABLE employees ADD email VARCHAR2(100);

-- Modify a column's data type or size
ALTER TABLE employees MODIFY salary NUMBER(12,2);

-- Rename a column (Oracle 12c+)
ALTER TABLE employees RENAME COLUMN status TO emp_status;

-- Add a NOT NULL constraint
ALTER TABLE employees MODIFY email NOT NULL;

-- Drop a column
ALTER TABLE employees DROP COLUMN emp_status;

DROP TABLE

Remove the table and all its data permanently

SQL

DROP TABLE employees;

-- Safer version — only drops if the table exists (MySQL/PostgreSQL)
DROP TABLE IF EXISTS employees;

-- Oracle: move to recycle bin (can be recovered with FLASHBACK)
DROP TABLE employees;

-- Oracle: skip recycle bin entirely
DROP TABLE employees PURGE;

TRUNCATE vs DROP

Column	Type	Description
DROP TABLE	DDL	Yes — all data gone. Yes — table structure gone. Potentially (Oracle recycle bin).
TRUNCATE TABLE	DDL	Yes — all rows removed instantly. No — table structure stays. Generally no — auto-commits.
DELETE FROM	DML	Yes — can use WHERE to be selective. No — table structure stays. Yes — can ROLLBACK.

⚠️

TRUNCATE cannot be rolled back

TRUNCATE is a DDL operation — it auto-commits. Once you truncate, the data is gone. Use DELETE FROM table_name if you need the ability to roll back. TRUNCATE is much faster than DELETE for clearing entire tables, but it's irreversible without a backup.

DML — Data Operations

DML — INSERT, UPDATE, DELETE

DML (Data Manipulation Language) commands work with the actual data inside tables, not the structure.

INSERT

Insert a single row (specifying all columns in order)

SQL

INSERT INTO employees (emp_id, first_name, last_name, department, salary)
VALUES (101, 'Priya', 'Sharma', 'IT', 75000.00);

-- Insert multiple rows (MySQL, PostgreSQL)
INSERT INTO employees (emp_id, first_name, last_name, department, salary)
VALUES 
  (102, 'Rahul', 'Gupta', 'Finance', 80000.00),
  (103, 'Anita', 'Singh', 'Operations', 70000.00);

-- Insert using SELECT (copy from another table)
INSERT INTO employees_archive
SELECT * FROM employees WHERE status = 'I';

COMMIT;

UPDATE

Update a single employee's salary

SQL

UPDATE employees
SET salary = 85000.00
WHERE emp_id = 101;

-- Update multiple columns at once
UPDATE employees
SET salary    = salary * 1.10,
    department = 'Engineering'
WHERE department = 'IT'
  AND hire_date < DATE '2023-01-01';

-- Update based on a subquery (Oracle)
UPDATE employees e
SET salary = (
    SELECT AVG(salary) * 1.15
    FROM employees
    WHERE department = e.department
)
WHERE status = 'A';

COMMIT;

DELETE

Delete a specific row

SQL

DELETE FROM employees WHERE emp_id = 101;

-- Delete all inactive employees
DELETE FROM employees WHERE status = 'I';

-- Delete with a subquery condition
DELETE FROM employees
WHERE department NOT IN (
    SELECT department_name FROM departments WHERE active = 'Y'
);

COMMIT;

⚠️

Always use WHERE with UPDATE and DELETE

Missing a WHERE clause on UPDATE or DELETE affects every row in the table. Before running UPDATE or DELETE on production, always run the equivalent SELECT with the same WHERE clause first to verify the affected rows. Then run the DML and review the row count before committing.

SELECT — Querying Data

The SELECT Statement

SELECT is the most frequently used SQL command. It retrieves data from one or more tables.

Basic SELECT — all columns

SQL

SELECT * FROM employees;

-- SELECT specific columns
SELECT emp_id, first_name, last_name, salary
FROM employees;

-- SELECT with a calculated column
SELECT first_name,
       last_name,
       salary,
       salary * 12 AS annual_salary
FROM employees;

-- SELECT with a string expression
SELECT first_name || ' ' || last_name AS full_name,
       department,
       salary
FROM employees
WHERE status = 'A'
ORDER BY salary DESC;

💡

SELECT * is fine for exploration, not for production

SELECT * retrieves every column, which is useful when exploring data interactively. In production code, always list the specific columns you need. This avoids breaking if columns are added/renamed and dramatically improves query performance on wide tables.

WHERE — Filtering

WHERE Clause — Filtering Rows

The WHERE clause filters which rows are returned. Without it, every row in the table is included.

Equality filter

SQL

SELECT * FROM employees WHERE department = 'Finance';

-- Numeric comparison
SELECT first_name, salary 
FROM employees 
WHERE salary > 70000;

-- Multiple conditions with AND
SELECT first_name, salary
FROM employees
WHERE department = 'IT'
  AND salary > 60000
  AND status = 'A';

-- OR condition
SELECT * FROM employees
WHERE department = 'IT'
   OR department = 'Engineering';

-- Combining AND and OR — use parentheses to control precedence
SELECT * FROM employees
WHERE status = 'A'
  AND (department = 'IT' OR department = 'Engineering');

Column	Type	Description
=	Equals	WHERE dept = 'IT'
!= or <>	Not equals	WHERE status != 'I'
>	Greater than	WHERE salary > 50000
>=	Greater than or equal	WHERE hire_date >= DATE '2023-01-01'
<	Less than	WHERE salary < 100000
AND	Both conditions must be true	WHERE dept = 'IT' AND status = 'A'
OR	Either condition can be true	WHERE dept = 'IT' OR dept = 'HR'
NOT	Negate a condition	WHERE NOT status = 'I'

ORDER BY

ORDER BY — Sorting Results

ORDER BY sorts the result set. Without it, row order is not guaranteed.

Sort by salary descending (highest first)

SQL

SELECT first_name, salary
FROM employees
ORDER BY salary DESC;

-- Sort ascending (default — ASC can be omitted)
SELECT first_name, hire_date
FROM employees
ORDER BY hire_date ASC;

-- Sort by multiple columns
-- Primary sort: department A-Z, then secondary: salary highest first
SELECT first_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

-- Sort by column position (not recommended in production — fragile)
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC;  -- 3 = third column = salary

-- Sort NULLs last (Oracle default is NULLs last for DESC)
SELECT first_name, commission
FROM employees
ORDER BY commission DESC NULLS LAST;

DISTINCT

DISTINCT — Removing Duplicates

DISTINCT eliminates duplicate rows from a result set.

All unique departments in the employees table

SQL

SELECT DISTINCT department
FROM employees;

-- Distinct combination of department + status
SELECT DISTINCT department, status
FROM employees
ORDER BY department;

-- Count distinct values
SELECT COUNT(DISTINCT department) AS unique_dept_count
FROM employees;

💡

DISTINCT operates on the full row

SELECT DISTINCT department, status returns unique combinations of both columns — not just unique departments. Each unique (department, status) pair is one row.

LIKE, IN, BETWEEN

LIKE, IN, and BETWEEN

These three operators are powerful WHERE clause tools for pattern matching, set membership, and range filtering.

LIKE — Pattern Matching

Names starting with 'Pr'

SQL

SELECT * FROM employees WHERE first_name LIKE 'Pr%';

-- Names ending with 'a'
SELECT * FROM employees WHERE first_name LIKE '%a';

-- Names containing 'an' anywhere
SELECT * FROM employees WHERE first_name LIKE '%an%';

-- Email with exactly 5 characters before the @
SELECT * FROM employees WHERE email LIKE '_____@%';

-- NOT LIKE — exclude a pattern
SELECT * FROM employees WHERE department NOT LIKE '%Sales%';

Column	Type	Description
%	Any sequence	LIKE 'A%' matches Alice, Adam, Anita
_	Exactly one character	LIKE '_ita' matches Anita, Smita, Sunita

IN — Set Membership

Match any of the listed values (equivalent to multiple OR conditions)

SQL

SELECT * FROM employees
WHERE department IN ('IT', 'Finance', 'Engineering');

-- NOT IN — exclude listed values
SELECT * FROM employees
WHERE department NOT IN ('HR', 'Admin');

-- IN with a subquery
SELECT first_name, department
FROM employees
WHERE department IN (
    SELECT department_name FROM departments WHERE region = 'North'
);

BETWEEN — Range Filter

Salary range (inclusive on both ends)

SQL

SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 80000;

-- Date range
SELECT * FROM orders
WHERE order_date BETWEEN DATE '2025-01-01' AND DATE '2025-03-31';

-- NOT BETWEEN
SELECT * FROM employees
WHERE salary NOT BETWEEN 40000 AND 60000;

💡

BETWEEN is inclusive on both ends

BETWEEN 50000 AND 80000 includes rows where salary is exactly 50000 or exactly 80000. It is equivalent to salary >= 50000 AND salary = 80000.

NULL Handling

NULL Handling — IS NULL & IS NOT NULL

NULL represents a missing or unknown value. It is not the same as zero, an empty string, or the word "NULL". NULL requires special handling in SQL.

Find employees with no email on file

SQL

SELECT first_name, last_name
FROM employees
WHERE email IS NULL;

-- Find employees who DO have an email
SELECT first_name, email
FROM employees
WHERE email IS NOT NULL;

-- NULL in arithmetic — any operation on NULL returns NULL
SELECT emp_id, salary, commission,
       salary + commission AS total_comp  -- NULL if commission is NULL!
FROM employees;

-- Fix: use NVL (Oracle) or COALESCE (standard) to replace NULL with 0
SELECT emp_id, salary, commission,
       salary + NVL(commission, 0) AS total_comp
FROM employees;

-- COALESCE — returns the first non-NULL value in the list
SELECT first_name,
       COALESCE(mobile, office_phone, 'No contact') AS contact_number
FROM employees;

⚠️

You cannot compare NULL with = or !=

This query will never return rows: WHERE email = NULL. NULL is not equal to anything, including itself. You must always use IS NULL or IS NOT NULL. This is one of the most common SQL beginner mistakes.

Operators

SQL Operators

SQL has four categories of operators. Understanding them is essential for writing effective WHERE clauses and expressions.

Arithmetic Operators

SQL

SELECT emp_id,
       salary,
       salary * 12          AS annual_salary,
       salary * 1.10        AS salary_after_10pct_raise,
       salary + 5000        AS salary_with_bonus,
       salary / 22          AS daily_rate,
       MOD(emp_id, 10)      AS last_digit_of_id
FROM employees;

Comparison Operators

All standard comparisons work in WHERE clauses

SQL

SELECT * FROM employees
WHERE salary = 60000
  AND hire_date  DATE '2024-01-01'
  AND department [-ne] 'HR';      --use sql not equal to operator

Logical Operators

AND, OR, NOT — combine multiple conditions

SQL

SELECT * FROM employees
WHERE status = 'A'
  AND (department = 'IT' OR department = 'Engineering')
  AND NOT emp_id IN (SELECT emp_id FROM suspended_employees);

Concatenation Operator

Oracle uses || for string concatenation

SQL

SELECT first_name || ' ' || last_name AS full_name,
       department || ' - ' || TO_CHAR(hire_date, 'YYYY') AS dept_year
FROM employees;

-- MySQL uses CONCAT()
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

LIMIT & FETCH FIRST

LIMIT & FETCH FIRST — Row Limiting

Row limiting is used to return only a subset of results — essential for pagination and performance.

MySQL / PostgreSQL — LIMIT

SQL

SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

-- MySQL / PostgreSQL — with OFFSET for pagination
SELECT * FROM employees ORDER BY salary DESC LIMIT 10 OFFSET 20;  -- page 3

-- Oracle 12c+ — FETCH FIRST (SQL standard)
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

-- Oracle 12c+ — with OFFSET
SELECT * FROM employees
ORDER BY salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Oracle legacy (pre-12c) — use ROWNUM
SELECT * FROM (
    SELECT * FROM employees ORDER BY salary DESC
)
WHERE ROWNUM = 10;

-- SQL Server — TOP
SELECT TOP 10 * FROM employees ORDER BY salary DESC;

⚠️

Always use ORDER BY with row limiting

Without ORDER BY, the rows returned by LIMIT or FETCH FIRST are non-deterministic — the database can return any rows in any order. For correct pagination, always specify an ORDER BY clause.

Aliases

Aliases — AS Keyword

Aliases give a column or table a temporary name in the output. They improve readability and are required when referencing calculated columns.

Column alias (AS keyword)

SQL

SELECT 
    first_name || ' ' || last_name AS full_name,
    salary * 12                    AS annual_salary,
    ROUND(salary / 22, 2)          AS daily_rate
FROM employees;

-- Column alias without AS (also valid, less readable)
SELECT salary * 12 annual_salary FROM employees;

-- Table alias — essential for self-joins and readability
SELECT e.first_name AS employee, m.first_name AS manager
FROM   employees e
JOIN   employees m ON e.manager_id = m.emp_id;

-- Table alias in subqueries
SELECT dept_stats.department, dept_stats.avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM   employees
    GROUP  BY department
) dept_stats
WHERE dept_stats.avg_salary > 65000;

Comments

SQL Comments

Comments document your SQL code and are ignored by the database engine during execution.

Single-line comment: starts with --

SQL

SELECT first_name, salary  -- returns active employees only
FROM   employees
WHERE  status = 'A';       -- 'A' = Active

/* Multi-line comment:
   used for longer explanations,
   author attribution, or temporarily
   disabling blocks of code           */
SELECT dept_id,
       /* AVG(salary) AS avg_salary, -- temporarily disabled */
       COUNT(*)      AS headcount
FROM   employees
GROUP  BY dept_id;

✨

Good commenting practice

Comment the why, not the what. -- filter active only adds nothing when the code already says WHERE status = 'A'. A better comment would be: -- inactive employees are excluded from payroll calculations per HRMS-101.

Basic Functions

Basic Functions — UPPER, ROUND, NOW

SQL has built-in functions for working with strings, numbers, and dates. These cover the vast majority of everyday transformation needs.

String Functions

UPPER / LOWER — change case

SQL

SELECT UPPER(first_name), LOWER(email) FROM employees;

-- LENGTH — number of characters
SELECT first_name, LENGTH(first_name) AS name_length FROM employees;

-- SUBSTR / SUBSTRING — extract a portion of a string
-- Oracle: SUBSTR(str, start_pos, length)
SELECT SUBSTR('RetailCoder', 1, 6) FROM dual;  -- 'Retail'

-- TRIM — remove leading/trailing spaces
SELECT TRIM('  hello world  ') FROM dual;  -- 'hello world'

-- REPLACE — substitute text
SELECT REPLACE(phone, '-', '') AS clean_phone FROM employees;

-- CONCAT / || — join strings
SELECT CONCAT(first_name, ' ', last_name) FROM employees;  -- MySQL
SELECT first_name || ' ' || last_name FROM employees;       -- Oracle

Numeric Functions

ROUND — round to n decimal places

SQL

SELECT ROUND(salary / 22, 2)    AS daily_rate FROM employees;
SELECT ROUND(3.14159, 2)        FROM dual;    -- 3.14

-- CEIL / CEILING — round up to nearest integer
SELECT CEIL(4.1)  FROM dual;    -- 5

-- FLOOR — round down to nearest integer
SELECT FLOOR(4.9) FROM dual;    -- 4

-- ABS — absolute value
SELECT ABS(-5000) FROM dual;    -- 5000

-- MOD — remainder (modulo)
SELECT MOD(17, 5) FROM dual;    -- 2

-- POWER — exponentiation
SELECT POWER(2, 10) FROM dual;  -- 1024

Date Functions

SYSDATE / CURRENT_DATE — current date and time

SQL

SELECT SYSDATE FROM dual;           -- Oracle
SELECT CURRENT_DATE FROM dual;      -- Standard SQL, MySQL, PostgreSQL

-- TRUNC — remove time component from a date
SELECT TRUNC(SYSDATE) FROM dual;    -- Today's date with time set to 00:00:00

-- ADD_MONTHS — add months to a date (Oracle)
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;  -- 3 months from today

-- MONTHS_BETWEEN — months between two dates (Oracle)
SELECT MONTHS_BETWEEN(SYSDATE, hire_date) AS tenure_months
FROM employees;

-- TO_CHAR — format a date as a string (Oracle)
SELECT TO_CHAR(hire_date, 'DD-Mon-YYYY') AS formatted_date
FROM employees;

-- TO_DATE — parse a string to a date (Oracle)
SELECT * FROM employees
WHERE hire_date >= TO_DATE('01-Jan-2023', 'DD-Mon-YYYY');

-- DATEADD (SQL Server) / DATE_ADD (MySQL)
SELECT DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date FROM orders;

Gotchas

Common Gotchas for Beginners

Important Gotchas

!
Using = NULL instead of IS NULL. WHERE email = NULL never matches any row because NULL is not equal to anything — not even itself. Always write WHERE email IS NULL or WHERE email IS NOT NULL.
!
Forgetting WHERE on UPDATE or DELETE. Without a WHERE clause, UPDATE employees SET salary = 0 sets every employee's salary to zero. Always verify with a SELECT first.
!
Using FLOAT for currency values. Floating-point arithmetic introduces invisible rounding errors. For prices, salaries, and tax amounts always use DECIMAL(p,s) or NUMBER(p,s).
!
Assuming ORDER BY without LIMIT is enough. Row order without LIMIT/FETCH FIRST is not guaranteed in SQL — the engine is free to return rows in any order it chooses for performance reasons. Rely on ORDER BY only in the final query that returns data to the user.
!
Oracle DATE columns storing time. In Oracle, DATE stores date + time. Comparisons like WHERE order_date = TO_DATE('2025-01-15', 'YYYY-MM-DD') fail if the time is not midnight. Use TRUNC(order_date) = DATE '2025-01-15' or order_date BETWEEN DATE '2025-01-15' AND DATE '2025-01-15' + 1.
!
AND has higher precedence than OR. WHERE dept = 'IT' OR dept = 'HR' AND status = 'A' is evaluated as WHERE dept = 'IT' OR (dept = 'HR' AND status = 'A') — which may not be what you intended. Always use parentheses when mixing AND and OR.

Key Takeaways

SQL is the universal language of relational databases. The core syntax works across Oracle, MySQL, PostgreSQL, and SQL Server with minor dialect differences.
Choose data types carefully at design time — DECIMAL for money, VARCHAR2/VARCHAR for text, DATE/TIMESTAMP for time values. Changing column types later requires a migration.
DDL (CREATE, ALTER, DROP) defines structure. DML (INSERT, UPDATE, DELETE) manages data. Always use WHERE with UPDATE and DELETE.
NULL is the absence of a value — it is not zero or empty string. Use IS NULL and IS NOT NULL. Use NVL() or COALESCE() to substitute a default for NULLs in expressions.
LIKE with % and _ provides pattern matching. IN tests set membership. BETWEEN filters ranges (inclusive on both ends).
Always ORDER BY when using LIMIT or FETCH FIRST. Row order without ORDER BY is undefined.
Built-in functions (UPPER, ROUND, TRUNC, SUBSTR, COALESCE, TO_CHAR) handle the vast majority of data transformation needs without writing procedural code.

Next in series →

SQL Intermediate — Joins, Aggregates, Subqueries, Views & Indexes

SQL Foundations: The Complete Beginner's Guide

What is SQL? The Language of Databases#

SQL is declarative

SQL Data Types — Choosing the Right Column Type#

Numeric Types

Never use FLOAT for money

String / Character Types

Date & Time Types

Oracle DATE includes time — this surprises many developers

Other Important Types

DDL — CREATE, ALTER, DROP#

CREATE TABLE

CREATE DATABASE (MySQL / PostgreSQL)

ALTER TABLE — Modify Structure

DROP TABLE

TRUNCATE vs DROP

TRUNCATE cannot be rolled back

DML — INSERT, UPDATE, DELETE#

INSERT

UPDATE

DELETE

Always use WHERE with UPDATE and DELETE

The SELECT Statement#

SELECT * is fine for exploration, not for production

WHERE Clause — Filtering Rows#

ORDER BY — Sorting Results#

DISTINCT — Removing Duplicates#

DISTINCT operates on the full row

LIKE, IN, and BETWEEN#

LIKE — Pattern Matching

IN — Set Membership

BETWEEN — Range Filter

BETWEEN is inclusive on both ends

NULL Handling — IS NULL & IS NOT NULL#

You cannot compare NULL with = or !=

SQL Operators#

Arithmetic Operators

Comparison Operators

Logical Operators

Concatenation Operator

LIMIT & FETCH FIRST — Row Limiting#

Always use ORDER BY with row limiting

Aliases — AS Keyword#

SQL Comments#

Good commenting practice

Basic Functions — UPPER, ROUND, NOW#

String Functions

Numeric Functions

Date Functions

Common Gotchas for Beginners#

Important Gotchas

Key Takeaways#

Key Takeaways

Related Articles

SQL Intermediate: Joins, Aggregates, Subqueries, Functions, Views & Indexes

SQL Architecture: CTEs vs. Global Temporary Tables (GTTs)

Advanced SQL Analytics: Row Pattern Matching (MATCH_RECOGNIZE)

RC:OMS

RC:Storefront

RC:Pulse

What is SQL? The Language of Databases

SQL Data Types — Choosing the Right Column Type

DDL — CREATE, ALTER, DROP

DML — INSERT, UPDATE, DELETE

The SELECT Statement

WHERE Clause — Filtering Rows

ORDER BY — Sorting Results

DISTINCT — Removing Duplicates

LIKE, IN, and BETWEEN

NULL Handling — IS NULL & IS NOT NULL

SQL Operators

LIMIT & FETCH FIRST — Row Limiting

Aliases — AS Keyword

SQL Comments

Basic Functions — UPPER, ROUND, NOW

Common Gotchas for Beginners

Key Takeaways