SQL Functions and Constraints

Definition

SQL functions are built-in or user-defined operations that accept input values and return a result, such as SUM(), COUNT(), UPPER(), or NOW().

SQL constraints are rules applied to table columns or tables that restrict the type and validity of data entered, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

Main Content

1. SQL Functions

Purpose and classification

• SQL functions help manipulate, calculate, transform, and analyze data directly in queries.
• They are commonly grouped into aggregate functions, scalar functions, and analytic/window functions.
• They reduce the need for processing data manually in application code.

Common types with examples

• Aggregate functions work on multiple rows and return one result.
  • SUM(salary) → total salary of all employees
  • AVG(marks) → average marks
  • COUNT(*) → number of rows
  • MAX(price) and MIN(price) → highest and lowest values
• Scalar functions work on one value at a time.
  • UPPER(name) → converts text to uppercase
  • LOWER(city) → converts text to lowercase
  • ROUND(45.678, 2) → 45.68
  • LEN(name) or LENGTH(name) → returns string length
  • NOW() / CURRENT_DATE → returns current date/time depending on the DBMS
• Analytic/window functions calculate values across a set of rows related to the current row.
  • ROW_NUMBER() OVER (ORDER BY salary DESC)
  • RANK() OVER (PARTITION BY department ORDER BY marks DESC)

Examples in SQL

  SELECT COUNT(*) AS total_students
  FROM Students;

  SELECT UPPER(first_name) AS name_upper
  FROM Employees;

  SELECT department, AVG(salary) AS avg_salary
  FROM Employees
  GROUP BY department;

• In the first query, COUNT(*) counts all student rows.
• In the second query, UPPER() transforms text.
• In the third query, AVG() is used with GROUP BY to calculate departmental averages.

2. SQL Constraints

Purpose and importance

• Constraints ensure data integrity by restricting invalid input.
• They protect the database from mistakes such as duplicate IDs, missing required values, or invalid relationships.
• Constraints can be applied at the column level or table level.

Major types of constraints with examples

• NOT NULL

  • Prevents a column from containing NULL.
  • Example: sql CREATE TABLE Students ( student_id INT NOT NULL, name VARCHAR(100) NOT NULL );

• UNIQUE

  • Ensures all values in a column are different.
  • Example: sql email VARCHAR(150) UNIQUE

• PRIMARY KEY

  • Uniquely identifies each row in a table.
  • Combines NOT NULL and UNIQUE.
  • Example: sql student_id INT PRIMARY KEY

• FOREIGN KEY

  • Creates a relationship between two tables by referencing a primary key in another table.
  • Example: sql department_id INT, FOREIGN KEY (department_id) REFERENCES Departments(department_id)

• CHECK

  • Ensures values satisfy a condition.
  • Example: sql age INT CHECK (age >= 18)

• DEFAULT

  • Assigns a default value when no value is provided.
  • Example: sql status VARCHAR(20) DEFAULT 'Active'

Example table using multiple constraints

  CREATE TABLE Employees (
      emp_id INT PRIMARY KEY,
      emp_name VARCHAR(100) NOT NULL,
      email VARCHAR(150) UNIQUE,
      age INT CHECK (age >= 18),
      department_id INT,
      status VARCHAR(20) DEFAULT 'Working',
      FOREIGN KEY (department_id) REFERENCES Departments(department_id)
  );

• emp_id uniquely identifies each employee.
• emp_name cannot be empty.
• email must be unique.
• age must be 18 or above.
• status will automatically become 'Working' if not provided.
• department_id must match a valid department.

How constraints support integrity

• Entity integrity is maintained by PRIMARY KEY, ensuring each row is uniquely identifiable.
• Domain integrity is maintained by CHECK, NOT NULL, and data type rules.
• Referential integrity is maintained by FOREIGN KEY, ensuring relationships between tables remain valid.

3. Relationship Between Functions and Constraints

Functions improve query results

• Functions are mainly used when reading or transforming data.
• They help summarize, format, and compute values from stored records.

Constraints protect stored data

• Constraints act at the time of insert or update.
• They ensure that only valid data reaches the database.

Combined use in practical SQL

• Example:

    SELECT department_id, COUNT(*) AS total_employees, AVG(salary) AS avg_salary
    FROM Employees
    WHERE age >= 18
    GROUP BY department_id;

• Here, constraints like CHECK (age >= 18) may ensure valid data, while functions like COUNT() and AVG() generate meaningful results.
• This combination helps create reliable reports from trustworthy data.

Working / Process

1. Define the table structure with constraints

• Decide what kind of data the table should store.
• Apply constraints such as PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT, and FOREIGN KEY while creating the table.
• This ensures invalid records are blocked at the storage level.

2. Insert and update data according to rules

• When new records are added, the DBMS checks whether all constraint conditions are satisfied.
• If a value violates a constraint, the operation fails.
• Example: inserting a duplicate email into a UNIQUE column will raise an error.

3. Use SQL functions to retrieve and analyze data

• After data is stored correctly, use functions in SELECT queries to compute totals, averages, format text, or work with dates.

• Example: sql SELECT department_id, COUNT(*) AS employee_count FROM Employees GROUP BY department_id;

• The DBMS processes the rows, applies the function, and returns the result set.

Advantages / Applications

• Improves data accuracy by preventing invalid values from being stored
• Ensures consistency and integrity across related tables and records
• Makes queries more powerful by allowing calculation, summarization, and data transformation
• Reduces errors in application logic because many rules are enforced at the database level
• Supports reporting, analytics, and business decision-making through functions
• Helps maintain reliable databases in academic, business, banking, hospital, and e-commerce systems

Summary

• SQL functions are used to calculate and transform data in queries, while constraints control what data can be stored in tables.
• Functions like SUM(), AVG(), and UPPER() help with analysis and formatting.
• Constraints like PRIMARY KEY, FOREIGN KEY, and CHECK maintain data validity.
• Important terms to remember: SUM, COUNT, AVG, UPPER, NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT