Skip to main content

Function Concepts

These are fundamental clauses and operators in SQL for filtering and querying data in databases:

WHERE​

Used to filter records that meet a specific condition.

SELECT * FROM users WHERE age > 25;

AND​

Combines multiple conditions that must all be met at the same time.

SELECT * FROM products WHERE price > 100 AND category = 'electronics';

OR​

Combines alternative conditions where at least one must be met.

SELECT * FROM employees WHERE department = 'sales' OR department = 'marketing';

LIKE​

Used for pattern matching queries using wildcards:

% represents any sequence of characters _ represents a single character

SELECT * FROM customers WHERE name LIKE 'Ana%';  -- Names starting with "Ana"
SELECT * FROM products WHERE code LIKE 'A_B';  -- Codes like "A1B", "A2B", etc.

BETWEEN​

Filters values within a range (inclusive on both ends).

SELECT * FROM sales WHERE date BETWEEN '2024-01-01' AND '2024-12-31';
SELECT * FROM products WHERE price BETWEEN 50 AND 200;

These operators can be combined to create more complex and precise queries, allowing you to extract exactly the data you need from your database.

ORDER BY​

It is an SQL clause used to sort the results of a query according to one or more columns.
By default, the order is ascending (ASC), but you can also specify descending order (DESC).

How is it used?

It is placed at the end of a SELECT query, followed by the name of the column by which you want to sort.

Basic Syntax:

SELECT columns
FROM table
ORDER BY column [ASC | DESC];

Usage Examples​

Sort by one column (ascending)​

SELECT * FROM products
ORDER BY price;

Sort by one column (descending)​

SELECT * FROM products
ORDER BY price DESC;

Sort by multiple columns​

SELECT * FROM employees
ORDER BY department ASC, salary DESC;

First sorts by department in ascending order, and within each department, by salary in descending order.

Important Notes​

  • You can sort by columns that are not in the SELECT.

  • You can also use the column number as a reference:

    SELECT name, age, salary
    FROM employees
    ORDER BY 3 DESC; -- Sorts by the third column: salary

  • It is useful in conjunction with LIMIT to show the first N sorted results:

    SELECT * FROM products
    ORDER BY added_date DESC
    LIMIT 5;

Common Uses​

  • Display the cheapest or most expensive products.
  • List older or more recent users.
  • Sort results alphabetically.
  • Prioritize data based on relevance or score.

Aggregate Functions​

1. COUNT() – Count rows​

Counts the number of rows in a query, or the number of non-null values in a specific column.

SELECT COUNT(*) FROM employees;
-- Returns the total number of rows in the employees table.
SELECT COUNT(salary) FROM employees;
-- Returns the number of rows where salary is NOT NULL.

2. SUM() – Sum values​

Sums the numerical values of a column.

SELECT SUM(salary) FROM employees;
-- Returns the sum of all non-null salaries.

3. AVG() – Average​

Calculates the average of the numerical values in a column, ignoring NULL values.

SELECT AVG(salary) FROM employees;
-- Returns the average of the salaries.

4. MIN() – Minimum value​

Returns the minimum value of a column.

SELECT MIN(salary) FROM employees;
-- Returns the lowest salary.

5. MAX() – Maximum value​

Returns the maximum value of a column.

SELECT MAX(salary) FROM employees;
-- Returns the highest salary.

Usage with GROUP BY​

Aggregate functions can be applied to subsets of data using GROUP BY.

SELECT department, AVG(salary)
FROM employees
GROUP BY department;
-- Returns the average salary per department.

Considerations​

info
  • Aggregate functions ignore NULL values.
  • Aliases can be used with AS to give the columns a more readable name.
  • When used with GROUP BY, the result contains one row per group.

DDL EXERCISES

Exercise 1: Library System​

Create the following tables:​

1. AUTHORS Table

CREATE TABLE authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    nationality VARCHAR(50),
    birth_date DATE
);

2. BOOKS Table

CREATE TABLE books (
    isbn VARCHAR(13) PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT,
    publisher VARCHAR(100),
    publication_year INT,
    page_count INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

3. USERS Table

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(15),
    registration_date DATE DEFAULT CURRENT_DATE
);

4. LOANS Table

CREATE TABLE loans (
    loan_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    isbn VARCHAR(13),
    loan_date DATE NOT NULL,
    expected_return_date DATE NOT NULL,
    actual_return_date DATE,
    status ENUM('active', 'returned', 'overdue') DEFAULT 'active',
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (isbn) REFERENCES books(isbn)
);

Exercise 2: Online Store System​

Create database and tables:​

1. Create database

CREATE DATABASE online_store;
USE online_store;

2. CATEGORIES Table

CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT
);

3. PRODUCTS Table

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock INT DEFAULT 0,
    category_id INT,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

4. CUSTOMERS Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    address VARCHAR(255),
    city VARCHAR(100),
    postal_code VARCHAR(10),
    phone VARCHAR(15)
);

Exercise 3: Modifications with ALTER TABLE​

Modify the PRODUCTS table:​

1. Add new column

ALTER TABLE products 
ADD COLUMN brand VARCHAR(100);

2. Modify data type

ALTER TABLE products 
MODIFY COLUMN description VARCHAR(500);

3. Add constraint

ALTER TABLE products 
ADD CONSTRAINT chk_stock CHECK (stock >= 0);

4. Create index

CREATE INDEX idx_product_name ON products(name);

Modify the CUSTOMERS table:​

1. Add audit columns

ALTER TABLE customers 
ADD COLUMN registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

2. Change column name

ALTER TABLE customers 
CHANGE COLUMN password passwd VARCHAR(255) NOT NULL;

Exercise 4: Employee System​

Create full structure:​

1. DEPARTMENTS Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(100),
    budget DECIMAL(12,2)
);

2. POSITIONS Table

CREATE TABLE positions (
    position_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    min_salary DECIMAL(10,2),
    max_salary DECIMAL(10,2),
    CONSTRAINT chk_salary CHECK (max_salary >= min_salary)
);

3. EMPLOYEES Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_number VARCHAR(10) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    phone VARCHAR(15),
    hire_date DATE NOT NULL,
    salary DECIMAL(10,2),
    position_id INT,
    department_id INT,
    supervisor_id INT,
    status ENUM('active', 'inactive', 'vacation') DEFAULT 'active',
    FOREIGN KEY (position_id) REFERENCES positions(position_id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    FOREIGN KEY (supervisor_id) REFERENCES employees(employee_id)
);

Exercise 5: Deletion Operations​

Delete elements from the structure:​

1. Delete constraint

ALTER TABLE employees 
DROP FOREIGN KEY employees_ibfk_3;

2. Delete column

ALTER TABLE employees 
DROP COLUMN phone;

3. Delete index

DROP INDEX idx_product_name ON products;

4. Drop table

DROP TABLE loans;

5. Drop database

DROP DATABASE online_store;

Exercise 6: Create Views and Indexes​

1. Create view for active products

CREATE VIEW available_products AS
SELECT p.name, p.price, p.stock, c.name AS category
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE p.stock > 0;

2. Create composite indexes

CREATE INDEX idx_employee_dept_pos 
ON employees(department_id, position_id);

CREATE INDEX idx_loan_dates 
ON loans(loan_date, expected_return_date);

Additional Challenges​

1. Create table with multiple constraints​

CREATE TABLE sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    sale_number VARCHAR(20) UNIQUE NOT NULL,
    customer_id INT NOT NULL,
    sale_date DATE NOT NULL,
    total DECIMAL(12,2) NOT NULL CHECK (total > 0),
    discount DECIMAL(5,2) DEFAULT 0 CHECK (discount >= 0 AND discount <= 100),
    tax DECIMAL(12,2) GENERATED ALWAYS AS (total * 0.19) STORED,
    status ENUM('pending', 'paid', 'cancelled') DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

2. Create temporary table​

CREATE TEMPORARY TABLE temp_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(255),
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. Create partitioned table (MySQL)​

CREATE TABLE system_logs (
    id INT AUTO_INCREMENT,
    log_date DATE NOT NULL,
    message TEXT,
    level ENUM('INFO', 'WARNING', 'ERROR'),
    PRIMARY KEY (id, log_date)
) PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);
  1. Run each exercise step by step
  2. Verify the structure with DESCRIBE table_name;
  3. Practice modifications in a test environment
  4. Document your changes to keep a history
  5. Use comments in your SQL code for greater clarity

ORDER BY - EXERCISES

1. Sort users by name alphabetically​

Table: users

idnameemail
1Carlacarla@mail.com
2Andresandres@mail.com
3Brunobruno@mail.com

2. Sort products by price from highest to lowest​

Table: products

idnameprice
1Laptop1200
2Keyboard100
3Monitor350

3. Sort users by registration date, from oldest to newest​

Table: users

idnameregistered_at
1Mario2023-08-01
2Lucia2023-05-15
3Diego2023-06-20

4. Sort products by stock quantity, from most to least​

Table: products

idnamestock
1Mouse60
2Laptop20
3Monitor45

5. Sort users by age, from youngest to oldest​

Table: users

idnameage
1Laura22
2Pablo35
3Ana29

6. Sort products first by category (alphabetically) and then by price from lowest to highest​

Table: products

idnamecategoryprice
1ChairHome80
2PhoneTechnology600
3TableHome120

AGGREGATE FUNCTIONS - EXERCISES

Practice Examples​

Suppose the following employees table:

idfirst_namedepartmentsalary
1AnaSales2500
2JuanMarketing3200
3LauraSales2800
4PedroMarketing3000
5AndresSalesNULL

Examples with solutions and expected results:​

  1. SELECT COUNT(*) FROM employees; → 5

  2. SELECT COUNT(salary) FROM employees; → 4

  3. SELECT SUM(salary) FROM employees; → 11500

  4. SELECT AVG(salary) FROM employees; → 2875

  5. SELECT MIN(salary) FROM employees; → 2500

  6. SELECT MAX(salary) FROM employees; → 3200

  7. SELECT COUNT(*) FROM employees WHERE department = 'Sales'; → 3

  8. SELECT AVG(salary) FROM employees WHERE department = 'Marketing'; → 3100

  9. SELECT SUM(salary) FROM employees WHERE salary > 2600; → 9000

  10. SELECT MIN(salary) FROM employees WHERE department = 'Sales'; → 2500

  11. SELECT MAX(salary) FROM employees WHERE department = 'Sales'; → 2800

  12. SELECT COUNT(*) FROM employees WHERE salary IS NULL; → 1

  13. SELECT COUNT(*) FROM employees WHERE salary IS NOT NULL; → 4

  14. SELECT department, COUNT(*) FROM employees GROUP BY department; → Sales: 3, Marketing: 2

  15. SELECT department, AVG(salary) FROM employees GROUP BY department; → Sales: 2650, Marketing: 3100

  16. SELECT department, MAX(salary) FROM employees GROUP BY department; → Sales: 2800, Marketing: 3200

  17. SELECT department, MIN(salary) FROM employees GROUP BY department; → Sales: 2500, Marketing: 3000

  18. SELECT department, SUM(salary) FROM employees GROUP BY department; → Sales: 5300, Marketing: 6200

  19. SELECT department FROM employees GROUP BY department HAVING COUNT(*) > 2; → Sales

  20. SELECT department FROM employees GROUP BY department HAVING AVG(salary) > 2800; → Marketing