Day 4: SQL Constraints, Aggregate and String Functions
1. SQL Constraints
SQL constraints are essential rules that help maintain the data integrity and consistency in a
relational database. They define specific conditions or restrictions on data entries in a table
to ensure the data is valid and accurate.
1.1 Types of Constraints and Detailed Explanation
1. NOT NULL:
o Ensures that a column cannot contain NULL values.
o Syntax: column_name data_type NOT NULL
o Example:
CREATE TABLE Employees (
ID INT NOT NULL,
Name VARCHAR(100) NOT NULL
);
-- If you try to insert a record without a value for ID or Name, the database will throw an
error.
INSERT INTO Employees (ID) VALUES (1); -- Error: Column 'Name' cannot be null.
2. UNIQUE:
o Ensures that all values in a column are unique (no duplicates allowed).
o Syntax:
▪ column_name data_type UNIQUE
▪ CREATE TABLE table_name ( column_name data_type, CONSTRAINT
constraint_name UNIQUE (column_name) );
o Example:
CREATE TABLE Employees (
Email VARCHAR(255) UNIQUE
);
o -- If you attempt to insert a duplicate email, the query will fail.
INSERT INTO Employees (Email) VALUES ('[Link]@[Link]');
INSERT INTO Employees (Email) VALUES ('[Link]@[Link]'); -- Error: Duplicate entry.
3. PRIMARY KEY:
o Combines NOT NULL and UNIQUE to uniquely identify each record in a table.
o Syntax:
▪ column_name data_type PRIMARY KEY
▪ CREATE TABLE table_name ( column_name data_type, CONSTRAINT
constraint_name PRIMARY KEY (column_name) );
o Example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100)
);
o -- Ensures no two rows have the same EmployeeID and that it cannot be NULL.
4. FOREIGN KEY:
o Enforces referential integrity by linking a column in one table to the primary
key in another table.
o Syntax:
▪ FOREIGN KEY (column_name) REFERENCES parent_table_name
(parent_column_name)
▪ CREATE TABLE table_name ( column_name data_type, CONSTRAINT
constraint_name FOREIGN KEY (column_name) REFERENCES parent_table_name
(parent_column_name) );
o Example:
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100)
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
o -- Ensures CustomerID in Orders exists in Customers.
5. CHECK:
o Validates that values in a column meet a specific condition.
o Syntax:
▪ CHECK (condition)
▪ CREATE TABLE table_name ( column_name data_type, CONSTRAINT
constraint_name CHECK (condition) );
o Example:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Price DECIMAL(10, 2),
CHECK (Price > 0)
);
o -- Price must always be greater than 0.
6. DEFAULT:
o Assigns a default value to a column if no value is provided during insertion.
o Syntax:
▪ column_name data_type DEFAULT default_value
o Example:
CREATE TABLE Users (
UserID INT PRIMARY KEY,
IsActive BOOLEAN DEFAULT TRUE
);
o -- Automatically sets IsActive to TRUE if no value is specified.
7. INDEX:
o Speeds up data retrieval by creating an index on one or more columns.
o Syntax:
▪ CREATE INDEX index_name ON table_name (column_name);
o Example:
CREATE INDEX idx_lastname ON Employees (LastName);
o -- Improves search performance when querying LastName.
2. Aggregate Functions
Aggregate functions process multiple rows and return a single summary value, such as totals
or averages.
2.1 Common Aggregate Functions with Syntax and Examples
1. COUNT():
o Counts the number of rows that match a condition.
o Syntax:
▪ SELECT COUNT(column_name) FROM table_name;
▪ SELECT COUNT(*) FROM table_name;
o Example:
SELECT COUNT(*) AS TotalEmployees FROM Employees;
SELECT COUNT(DISTINCT Department) AS UniqueDepartments FROM Employees;
2. SUM():
o Calculates the total sum of numeric values in a column.
o Syntax:
▪ SELECT SUM(column_name) FROM table_name;
o Example:
SELECT SUM(Salary) AS TotalSalary FROM Employees;
SELECT SUM(Salary) AS TotalSalary FROM Employees WHERE Department = 'HR';
3. AVG():
o Calculates the average value of a numeric column.
o Syntax:
▪ SELECT AVG(column_name) FROM table_name;
o Example:
SELECT AVG(Salary) AS AverageSalary FROM Employees;
4. MAX():
o Finds the maximum value in a column.
o Syntax:
▪ SELECT MAX(column_name) FROM table_name;
o Example:
SELECT MAX(Salary) AS HighestSalary FROM Employees;
SELECT MAX(JoiningDate) AS LatestJoining FROM Employees;
5. MIN():
o Finds the minimum value in a column.
o Syntax:
▪ SELECT MIN(column_name) FROM table_name;
o Example:
SELECT MIN(Salary) AS LowestSalary FROM Employees;
3. String Functions
String functions allow for manipulation and transformation of string data types.
3.1 Common String Functions with Syntax and Examples
1. CONCAT():
o Combines two or more strings into one.
o Syntax:
▪ SELECT CONCAT(string1, string2, ...) FROM table_name;
o Example:
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;
2. LENGTH() / LEN():
o Returns the number of characters in a string.
o Syntax:
▪ SELECT LENGTH(column_name) FROM table_name;
o Example:
SELECT LENGTH(Name) AS NameLength FROM Employees;
3. UPPER():
o Converts all characters in a string to uppercase.
o Syntax:
▪ SELECT UPPER(column_name) FROM table_name;
o Example:
SELECT UPPER(Name) AS UpperCaseName FROM Employees;
4. LOWER():
o Converts all characters in a string to lowercase.
o Syntax:
▪ SELECT LOWER(column_name) FROM table_name;
o Example:
SELECT LOWER(Name) AS LowerCaseName FROM Employees;
5. SUBSTRING() / SUBSTR():
o Extracts a portion of a string based on specified positions.
o Syntax:
▪ SELECT SUBSTRING(column_name, start_position, length) FROM table_name;
o Example:
SELECT SUBSTRING(Name, 1, 3) AS ShortName FROM Employees;
6. TRIM():
o Removes spaces or specified characters from the beginning or end of a string.
o Syntax:
▪ SELECT TRIM(column_name) FROM table_name;
o Example:
SELECT TRIM(' John Doe ') AS TrimmedName;
7. REPLACE():
o Substitutes all occurrences of a substring within a string with another
substring.
o Syntax:
▪ SELECT REPLACE(column_name, 'old_substring', 'new_substring') FROM
table_name;
o Example:
SELECT REPLACE(Name, 'John', 'Jonathan') AS UpdatedName FROM Employees;
8. INSTR():
o Returns the position of the first occurrence of a substring within a string.
o Syntax:
▪ SELECT INSTR(column_name, 'substring') FROM table_name;
o Example:
SELECT INSTR(Name, 'a') AS Position FROM Employees;
9. LEFT():
o Extracts characters from the left of a string.
o Syntax:
▪ SELECT LEFT(column_name, number_of_characters) FROM table_name;
o Example:
SELECT LEFT(Name, 4) AS StartName FROM Employees;
[Link]():
o Extracts characters from the right of a string.
o Syntax:
▪ SELECT RIGHT(column_name, number_of_characters) FROM table_name;
o Example:
SELECT RIGHT(Name, 3) AS EndName FROM Employees;