SQL Fundamentals: Syntax, Data Types & Operators

In This Chapter

SQL Syntax
Data Types
SQL Operators

1 SQL Syntax

SQL has a clean, English-like syntax. Every SQL statement tells the database what to do — and the database figures out how to do it. Let's build a mini-project from scratch to see how the syntax works in practice.

Step 1: Create a Database

SQLCREATE DATABASE bookstore;

Step 2: Create a Table

SQL

CREATE TABLE books (
    id        INT PRIMARY KEY,
    title     VARCHAR(200),
    author    VARCHAR(100),
    price     DECIMAL(8,2),
    published DATE
);

Step 3: Insert Data

SQL

INSERT INTO books (id, title, author, price, published) VALUES
    (1, 'SQL in 10 Minutes',    'Ben Forta',      24.99, '2021-11-01'),
    (2, 'Learning SQL',          'Alan Beaulieu',  39.99, '2020-04-15'),
    (3, 'SQL Pocket Guide',      'Alice Zhao',     19.99, '2021-08-10');

Step 4: Query the Data

SQL

SELECT title, author, price
FROM books
WHERE price < 30
ORDER BY price;

Result

title              | author      | price
-------------------|-------------|------
SQL Pocket Guide   | Alice Zhao  | 19.99
SQL in 10 Minutes  | Ben Forta   | 24.99

Syntax Rules to Remember

Rule	Example	Notes
Statements end with `;`	`SELECT * FROM books;`	Tells the DBMS the statement is complete
Keywords are case-insensitive	`SELECT` = `select` = `Select`	Convention: UPPERCASE for keywords
Strings use single quotes	`WHERE name = 'Alice'`	Double quotes are for identifiers in some DBs
Single-line comments	`-- This is a comment`	Everything after `--` is ignored
Multi-line comments	`/* ... */`	For longer explanations
Whitespace doesn't matter	You can split queries across lines	Use line breaks for readability

Best practice: Write SQL keywords in UPPERCASE and table/column names in lowercase. This makes queries much easier to read: SELECT name FROM customers WHERE country = 'UAE'

Key Takeaways

Every SQL statement ends with a semicolon (;)
SQL keywords are case-insensitive, but use UPPERCASE by convention
Strings go in single quotes; comments use -- or /* */
The basic flow: CREATE a table, INSERT data, SELECT to query

2 Data Types

Top

Every column in a SQL table has a data type that controls what kind of values it can hold. Choosing the right type matters — it affects storage, performance, and what operations you can perform.

Numeric Types

Type	What It Stores	Example Values	When to Use
`INT`	Whole numbers	`1`, `-42`, `1000000`	IDs, counts, quantities
`BIGINT`	Very large whole numbers	Up to 9.2 quintillion	Social media post IDs, large counters
`DECIMAL(p,s)`	Exact fixed-point numbers	`DECIMAL(10,2)` → `12345678.99`	Money, financial data (always!)
`FLOAT`	Approximate decimals	`3.14159`	Scientific calculations, measurements

String / Character Types

Type	What It Stores	Max Size	When to Use
`CHAR(n)`	Fixed-length text	`CHAR(2)` always stores 2 chars	Country codes (`'US'`), status flags
`VARCHAR(n)`	Variable-length text	Up to `n` characters	Names, emails, descriptions
`TEXT`	Large text blocks	Up to 65,535+ chars	Blog posts, comments, long descriptions

Date and Time Types

Type	Format	Example	When to Use
`DATE`	`YYYY-MM-DD`	`'2026-04-04'`	Birthdays, order dates
`TIME`	`HH:MM:SS`	`'14:30:00'`	Schedules, durations
`DATETIME`	`YYYY-MM-DD HH:MM:SS`	`'2026-04-04 14:30:00'`	Timestamps, created_at fields

Other Types

Type	What It Stores	Notes
`BOOLEAN`	`TRUE` or `FALSE`	MySQL uses `TINYINT(1)` internally
`NULL`	Absence of a value	Not zero, not empty string — literally "unknown"

Practical Example

SQL

CREATE TABLE employees (
    id          INT PRIMARY KEY,
    name        VARCHAR(100)    NOT NULL,
    email       VARCHAR(150)    UNIQUE,
    department  VARCHAR(50),
    salary      DECIMAL(10,2),
    hire_date   DATE,
    is_active   BOOLEAN DEFAULT TRUE
);

INSERT INTO employees VALUES
    (1, 'Sara Ahmed',  'sara@company.com',  'Engineering', 85000.00, '2023-01-15', TRUE),
    (2, 'James Lee',   'james@company.com', 'Marketing',   62000.00, '2023-06-01', TRUE),
    (3, 'Priya Patel', 'priya@company.com', 'Engineering', 92000.50, '2022-09-10', TRUE);

Common mistake: Never use FLOAT for money. FLOAT has rounding errors — 0.1 + 0.2 might equal 0.30000000000000004. Always use DECIMAL for financial data. Also, prefer VARCHAR over CHAR unless the length is truly fixed (like country codes).

Key Takeaways

Use INT for whole numbers, DECIMAL for money, FLOAT for science
Use VARCHAR(n) for most text; CHAR(n) only for fixed-length values
Use DATE for dates, DATETIME for timestamps
NULL means "unknown" — it's not zero or empty string
Choosing the right data type affects storage, performance, and data integrity

3 SQL Operators

Top

Operators are the tools you use inside SQL queries to filter, compare, calculate, and combine data. Let's look at each category with practical examples using the employees table we created above.

Arithmetic Operators

Perform calculations directly in your queries:

SQL

-- Calculate annual bonus (10% of salary)
SELECT name, salary, salary * 0.10 AS bonus
FROM employees;

Result

name        | salary   | bonus
------------|----------|--------
Sara Ahmed  | 85000.00 | 8500.00
James Lee   | 62000.00 | 6200.00
Priya Patel | 92000.50 | 9200.05

Operator	Meaning	Example
`+`	Addition	`salary + 5000`
`-`	Subtraction	`price - discount`
`*`	Multiplication	`quantity * price`
`/`	Division	`total / 12`
`%`	Modulus (remainder)	`id % 2` (odd/even check)

Comparison Operators

Used in WHERE clauses to filter rows:

SQL

-- Employees earning more than 80K
SELECT name, salary FROM employees
WHERE salary > 80000;

Result

name        | salary
------------|----------
Sara Ahmed  | 85000.00
Priya Patel | 92000.50

Operator	Meaning	Example
`=`	Equal to	`WHERE department = 'Engineering'`
`!=` or `<>`	Not equal to	`WHERE status != 'inactive'`
`>`	Greater than	`WHERE salary > 80000`
`<`	Less than	`WHERE age < 30`
`>=`	Greater than or equal	`WHERE rating >= 4`
`<=`	Less than or equal	`WHERE price <= 99.99`

Logical Operators (AND, OR, NOT)

Combine multiple conditions:

SQL

-- Engineers earning over 90K
SELECT name, department, salary FROM employees
WHERE department = 'Engineering' AND salary > 90000;

-- Engineers OR anyone earning over 90K
SELECT name, department, salary FROM employees
WHERE department = 'Engineering' OR salary > 90000;

-- Everyone except Marketing
SELECT name, department FROM employees
WHERE NOT department = 'Marketing';

Watch out: AND is evaluated before OR. The query WHERE a = 1 OR b = 2 AND c = 3 means WHERE a = 1 OR (b = 2 AND c = 3). Use parentheses to be explicit: WHERE (a = 1 OR b = 2) AND c = 3.

BETWEEN, IN, LIKE, IS NULL

These are the operators you'll use most often for filtering:

SQL

-- BETWEEN: salary range
SELECT name, salary FROM employees
WHERE salary BETWEEN 60000 AND 90000;

-- IN: match any value in a list
SELECT name, department FROM employees
WHERE department IN ('Engineering', 'Design', 'Product');

-- LIKE: pattern matching with wildcards
SELECT name FROM employees
WHERE name LIKE 'S%';          -- starts with S

SELECT email FROM employees
WHERE email LIKE '%@company.com'; -- ends with @company.com

-- IS NULL: check for missing values
SELECT name FROM employees
WHERE department IS NULL;        -- no department assigned

Operator	What It Does	Example
`BETWEEN x AND y`	Value within a range (inclusive)	`WHERE price BETWEEN 10 AND 50`
`IN (list)`	Matches any value in the list	`WHERE country IN ('USA', 'UK')`
`LIKE pattern`	Pattern match (`%` = any chars, `_` = one char)	`WHERE name LIKE 'J_hn%'`
`IS NULL`	Checks for null (missing) values	`WHERE phone IS NULL`
`IS NOT NULL`	Checks value exists	`WHERE email IS NOT NULL`

Why IS NULL instead of = NULL? In SQL, NULL isn't a value — it's the absence of a value. You can't compare something to nothing with =. The expression NULL = NULL returns NULL (unknown), not TRUE. Always use IS NULL or IS NOT NULL.

Key Takeaways

Arithmetic operators (+ - * / %) let you calculate values in queries
Comparison operators (= != > < >= <=) filter rows in WHERE clauses
AND narrows results; OR broadens them; NOT inverts conditions
BETWEEN, IN, LIKE, and IS NULL are your everyday filtering tools
Use parentheses with AND/OR to avoid operator precedence surprises

What's next? Now that you understand SQL syntax, data types, and operators, you're ready to start creating database objects. The next chapter covers DDL Commands — CREATE, ALTER, DROP, TRUNCATE, and RENAME.

All Chapters in SQL Beginner Course

Continue Learning

Previous ChapterDatabase Concepts Next Chapter DDL Commands

SQL Fundamentals

In This Chapter

1 SQL Syntax

Step 1: Create a Database

Step 2: Create a Table

Step 3: Insert Data

Step 4: Query the Data

Syntax Rules to Remember

Key Takeaways

2 Data Types

Numeric Types

String / Character Types

Date and Time Types

Other Types

Practical Example

Key Takeaways

3 SQL Operators

Arithmetic Operators

Comparison Operators

Logical Operators (AND, OR, NOT)

BETWEEN, IN, LIKE, IS NULL

Key Takeaways

All Chapters in SQL Beginner Course

Continue Learning

Courses

Services

Support

Connect