Structured Query Language

What is SQL?

SQL stands for Structured Query Language. It is used to communicate with databases. SQL allows you to create, read, update, and delete data.

Databases are used almost everywhere:

Popular Database Systems

Tables, Rows, and Columns

Databases organize data into tables.

• Table: Collection of related data.
• Row: A single record.
• Column: A field or attribute.

Creating a Database

CREATE DATABASE school_db;

This creates a database named school_db.

Creating Tables

CREATE TABLE students (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

This creates a table called students with multiple columns.

Data Types

Selecting All Data

SELECT * FROM students;

The asterisk (*) means all columns.

Selecting Specific Columns

SELECT first_name, email FROM students;

This returns only the first_name and email columns.

Aliases

SELECT first_name AS Name,
       email AS Contact
FROM students;

Aliases rename columns temporarily in query results.

Distinct Values

SELECT DISTINCT country FROM customers;

DISTINCT removes duplicates.

WHERE Clause

SELECT * FROM students
WHERE age > 18;

WHERE filters rows based on conditions.

Comparison Operators

AND / OR Operators

SELECT * FROM employees
WHERE department = 'IT'
AND salary > 5000;

SELECT * FROM employees
WHERE department = 'HR'
OR department = 'Finance';

LIKE Operator

SELECT * FROM users
WHERE first_name LIKE 'Da%';

% represents wildcard characters.

IN Operator

SELECT * FROM products
WHERE category IN ('Laptop', 'Phone', 'Tablet');

BETWEEN

SELECT * FROM orders
WHERE total BETWEEN 100 AND 500;

Why Joins Matter

Real databases usually separate data into multiple tables. Joins combine information from different tables.

INNER JOIN

SELECT customers.name,
       orders.total
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;

Returns matching records from both tables.

LEFT JOIN

SELECT customers.name,
       orders.total
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

Returns all rows from the left table.

RIGHT JOIN

SELECT customers.name,
       orders.total
FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;

FULL OUTER JOIN

SELECT customers.name,
       orders.total
FROM customers
FULL OUTER JOIN orders
ON customers.id = orders.customer_id;

Join Visualization

COUNT()

SELECT COUNT(*) FROM users;

SUM()

SELECT SUM(total) FROM orders;

AVG()

SELECT AVG(salary) FROM employees;

MIN() and MAX()

SELECT MIN(price), MAX(price)
FROM products;

GROUP BY

SELECT department,
       COUNT(*)
FROM employees
GROUP BY department;

HAVING

SELECT department,
       COUNT(*) AS total
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Subqueries

SELECT name
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Views

CREATE VIEW high_salary_employees AS
SELECT * FROM employees
WHERE salary > 7000;

Indexes

CREATE INDEX idx_email
ON users(email);

Indexes improve query performance.

Transactions

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

Normalization

Normalization reduces duplicate data and improves database structure.

• 1NF - Remove repeating groups
• 2NF - Remove partial dependencies
• 3NF - Remove transitive dependencies

ORDER BY

ORDER BY sorts query results in ascending or descending order.

SELECT * FROM products
ORDER BY price DESC;

SELECT * FROM users
ORDER BY username ASC;

LIMIT

LIMIT restricts how many rows are returned.

SELECT * FROM orders
LIMIT 10;

OFFSET

SELECT * FROM products
LIMIT 10 OFFSET 20;

Useful for pagination systems.

CASE Statements

SELECT first_name,
CASE
    WHEN salary > 8000 THEN 'High Salary'
    WHEN salary > 5000 THEN 'Medium Salary'
    ELSE 'Entry Level'
END AS salary_level
FROM employees;

UNION

SELECT email FROM customers
UNION
SELECT email FROM employees;

UNION combines results from multiple queries.

UNION ALL

SELECT city FROM suppliers
UNION ALL
SELECT city FROM customers;

Unlike UNION, UNION ALL keeps duplicates.

DELETE Statement

DELETE FROM users
WHERE id = 5;

UPDATE Statement

UPDATE employees
SET salary = 7000
WHERE id = 2;

INSERT INTO

INSERT INTO customers (name, email, country)
VALUES ('David', 'david@example.com', 'Costa Rica');

Primary Keys

A primary key uniquely identifies each row in a table.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

Foreign Keys

Foreign keys create relationships between tables.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

NULL Values

SELECT * FROM users
WHERE phone_number IS NULL;

SELECT * FROM users
WHERE phone_number IS NOT NULL;

Date Queries

SELECT * FROM orders
WHERE order_date > '2026-01-01';

SELECT CURRENT_DATE;

String Functions

SELECT UPPER(first_name) FROM users;

SELECT LOWER(email) FROM customers;

SELECT LENGTH(username) FROM users;

Mathematical Functions

SELECT ROUND(price, 2) FROM products;

SELECT ABS(-25);

Window Functions

SELECT employee_name,
       department,
       salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num
FROM employees;

Window functions are very important in analytics and reporting.

CTEs (Common Table Expressions)

WITH high_earners AS (
    SELECT * FROM employees
    WHERE salary > 7000
)
SELECT * FROM high_earners;

Stored Procedures

CREATE PROCEDURE GetEmployees()
BEGIN
    SELECT * FROM employees;
END;

Database Security

SQL security is critical in real systems.

• Use strong authentication
• Restrict database permissions
• Prevent SQL injection
• Encrypt sensitive information
• Use role-based access control

Database Optimization

Query optimization improves performance and scalability.

• Use indexes wisely
• Avoid unnecessary joins
• Limit returned rows
• Use proper normalization
• Monitor slow queries

E-Commerce Example

SELECT products.name,
       SUM(order_items.quantity) AS total_sold
FROM products
INNER JOIN order_items
ON products.id = order_items.product_id
GROUP BY products.name
ORDER BY total_sold DESC;

Cybersecurity Example

SELECT ip_address,
       COUNT(*) AS failed_attempts
FROM login_logs
WHERE success = false
GROUP BY ip_address
HAVING COUNT(*) > 10;

Social Media Example

SELECT users.username,
       COUNT(posts.id) AS total_posts
FROM users
LEFT JOIN posts
ON users.id = posts.user_id
GROUP BY users.username;

Keep Practicing

SQL becomes easier with repetition. Build small projects such as:

• Inventory systems
• Blog databases
• User authentication systems
• Expense trackers
• Student management systems