SQL Basics

SQL (Structured Query Language) is the standard language used to interact with relational databases. At its core, SQL allows you to retrieve, filter, and organize data stored in tables.
The most essential commands include:
- SELECT - Chooses which columns to display.
- FROM - Specifies the table.
- WHERE - Filters rows based on conditions.
- ORDER BY - Sort data.
- LIMIT - Restrict how many rows are returned.
- DISTINCT - Remove duplicates.
SQL also supports basic comparison and pattern-matching operators such as >, <, BETWEEN, and LIKE, allowing you to search for ranges or patterns in your data. These foundational skills form the basis for all SQL querying and are critical before moving on to joins, aggregations, and more advanced operations.

SELECT, FROM, WHERE

These three statements form the foundation of almost every SQL query. They define what data you want, where it should come from, and which rows should be included. Understanding this trio is essential before learning more advanced SQL features.

SELECT

The SELECT keyword tells the database which columns you want to retrieve.
This retrieves only the first_name and last_name columns from the students table.
Column list is delimited by commas.

SELECT first_name, last_name
FROM students;

Using * or the wildcard character, returns all the columns.

SELECT *
FROM students;

FROM

The FROM clause tells SQL which table to read data from.
SQL reads data from the users table.

SELECT id, email
FROM users;

WHERE

The WHERE clause filters the rows returned by a query based on a condition Only rows that evaluate to TRUE are included.
Filter by a value, returns only employees in the Sales department.

SELECT *
FROM employees
WHERE department = 'Sales';

Filter using numeric condition, returns only employees that earn more than 50000.

SELECT first_name, salary
FROM employees
WHERE salary > 50000;

Multiple conditions can be defined with either AND or OR, using AND means both conditions are true while OR means either conditions can be true. Both AND and OR can be used at the same time depending on the query conditions.
Multiple conditions returns only courses that are equal to 3 and is_active is true.

SELECT *
FROM courses
WHERE credits = 3 AND is_active = TRUE;

SELECT the name and price columns, FROM the products table, WHERE price is less than 20.

SELECT name, price
FROM products
WHERE price < 20;

ORDER BY, LIMIT, DISTINCT

These SQL clauses help you organize and refine query results. They control how data is sorted, how many rows are returned, and whether duplicate rows are removed.

ORDER BY

Sorts the rows returned by your query in either:
- ASC - Sort by low - high or ascending values.
- DESC - Sort by high - low or descending values.
Sort by one column (ascending by default).

SELECT name, price
FROM products
ORDER BY price;

Sort in descending order.

SELECT name, price
FROM products
ORDER BY price DESC;

Sort by multiple columns. Column list is delimited by commas.

SELECT first_name, last_name, grade
FROM students
ORDER BY grade DESC, last_name ASC;

LIMIT

LIMIT controls how many rows the database returns. It's useful for pagination, previews, or performance optimization.
Return only 5 rows.

SELECT *
FROM employees
ORDER BY id
LIMIT 5;

Return 10 rows, starting from row 20 (OFFSET).

SELECT *
FROM employees
ORDER BY last_name
LIMIT 10 OFFSET 20;

DISTINCT

DISTINCT ensures the returned list contains unique values only.
Remove duplicate departments.

SELECT DISTINCT department
FROM employees;

Distinct on multiple columns.

SELECT DISTINCT country, city
FROM customers;

This returns the first 10 unique city names sorted alphabetically.

SELECT DISTINCT city
FROM customers
ORDER BY city ASC
LIMIT 10;

Basic Filtering (>, <, BETWEEN, LIKE)

Basic filtering allows you to control which rows are returned by your SQL queries by applying simple conditions. These filters are used in the WHERE clause to match numbers, ranges, and text patterns.

Greater Than ( > ) and Less Than ( < )

These operators filter numeric or date values based on comparison.
Price greater than 50.

SELECT name, price
FROM products
WHERE price > 50;

Age less than 18.

SELECT first_name, age
FROM users
WHERE age < 18;

Date comparison.

SELECT *
FROM bookings
WHERE booking_date > '2025-01-01';

BETWEEN

Checks whether a value falls between two inclusive boundaries. Since it is inclusive, it is logically equivalent to >= <= operators.
Price between 10 and 20, using BETWEEN.

SELECT name, price
FROM products
WHERE price BETWEEN 10 AND 20;

Price between 10 and 20, using >= <= operators.

SELECT name, price
FROM products
WHERE price &gt;= 10 AND price &lt;= 20;

Date range.

SELECT *
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';

BETWEEN includes both endpoints, or inclusive.

BETWEEN 1 AND 5 means 1, 2, 3, 4, and 5.

LIKE

Used to search for patterns within text values.
It works with wildcards:
- % = any number of characters
- _ = exactly one character
Names starting with "A"

SELECT *
FROM students
WHERE name LIKE 'A%';

Emails ending with “@gmail.com”

SELECT email
FROM users
WHERE email LIKE '%@gmail.com';

3-letter product codes. Three underscores (___) = exactly 3 characters.

  SELECT code
  FROM items
  WHERE code LIKE '___';

Combining multiple filters. SELECT name and price, FROM the products table, WHERE price is between 20 and 50 and name has the string 'Pro' and quantity is greater than 0.

SELECT name, price
FROM products
WHERE price BETWEEN 20 AND 50
AND name LIKE '%Pro%'
AND quantity > 0;

CREATE Resource

Use the CREATE statement to either create DATABASE or TABLE. You have to create a database first before you can create a table, think of the database as the container for tables.

CREATE DATABASE

Basic syntax:

CREATE DATABASE database_name;

Example:

CREATE DATABASE college_db;

CREATE TABLE

Use database command

Prior to table creation, the USE DATABASE statement needs to be executed. This tells the server which database to work on for the duration of the session. The basic syntax is USE DATABASE database_name, for instance if the database name is e_commerce_db then the command is USE DATABASE e_commerce_db.

Basic syntax:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Table with field names and datatypes only

CREATE TABLE customers (
    customer_id INT,
    name VARCHAR(100),
    email VARCHAR(150)
);

Table with primary key

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150)
);

Table with primary key defined separately

CREATE TABLE customers (
    customer_id INT,
    name VARCHAR(100),
    PRIMARY KEY (customer_id)
);

Table with auto increment primary key

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

Table with NOT NULL, UNIQUE, DEFAULT constraints

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    status VARCHAR(20) DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

NOT NULL - Column cannot be empty. UNIQUE - No duplicate values allowed. DEFAULT - Value used if none provided, for instance if the INSERT statament did not include a value for this field then it will use the datault value.

Table with foreign key

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

The orders table references the customers table via the customer_id.

Table with foreign key and cascade behavior

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

The cascade option tells the database on what to do to the child rows when the parent rows are deleted or updated.

Option	Behavior on parent delete / update
CASCADE	Automatically delete / update matching child rows.
RESTRICT	Block the delete / update if child rows exist.
SET NULL	Set the FK column in child rows to NULL (column must allow NULL).

Table with check constraint

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    stock INT CHECK (stock >= 0)
);

The check constraint prevents invalid values from being saved to the column.

Table with enum type

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    role ENUM('admin', 'editor', 'viewer') DEFAULT 'viewer'
);

ENUM type restricts the column to predefined list of values.

Table with composite keys

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Composite keys are list of primary keys defined to uniquely indentify a row.

SELECT, FROM, WHERE​

SELECT​

FROM​

WHERE​

ORDER BY, LIMIT, DISTINCT​

ORDER BY​

LIMIT​

DISTINCT​

Basic Filtering (>, <, BETWEEN, LIKE)​

Greater Than ( > ) and Less Than ( < )​

BETWEEN​

LIKE​

CREATE Resource​

CREATE DATABASE​

CREATE TABLE​