SQL provides a comprehensive set of string manipulation functions that enable developers to transform and analyze text data effectively.

• CONCAT(): Combines multiple strings into a single string. You can pass two or more arguments to this function, and it will join them sequentially.
• UPPER(): Converts all characters in a string to uppercase letters, useful for standardization and case-insensitive comparisons.
• LOWER(): Transforms all characters in a string to lowercase, complementing the UPPER function for case normalization.
• SUBSTRING(): Extracts a portion of a string starting from a specified position with a defined length. The first character position is typically 1, not 0.
• LENGTH(): Returns the total number of characters in a string, including spaces and special characters.
• TRIM(): Removes whitespace characters from both the beginning and end of a string, ensuring clean data storage and comparison.
• REPLACE(): Substitutes occurrences of a specified substring within a string with a new substring, supporting data cleaning and transformation tasks.

2. Mathematical Functions

Mathematical functions in SQL provide computational capabilities for numerical data processing and analysis.

• ABS(): Returns the absolute value of a number, making negative values positive while leaving positive values unchanged.
• ROUND(): Rounds a numeric value to a specified number of decimal places, following standard rounding conventions.
• CEIL(): Returns the smallest integer value that is greater than or equal to the input number, also known as the ceiling function.
• FLOOR(): Returns the largest integer value that is less than or equal to the input number, representing the floor function.
• RAND(): Generates a random floating-point number between 0 (inclusive) and 1 (exclusive), useful for sampling and randomization.

3. Date and Time Functions

Date and time functions are essential for temporal data manipulation, reporting, and time-based analysis.

• CURRENT_TIMESTAMP(): Returns the current date and time in the database server's timezone, providing a precise timestamp for record tracking.
• DATE_FORMAT(): Formats a date or timestamp according to a specified pattern string, allowing customizable date presentation.
• YEAR(): Extracts the four-digit year component from a date value, facilitating yearly aggregations and comparisons.
• MONTH(): Retrieves the numeric month (1-12) from a date, enabling month-based filtering and analysis.
• DAY(): Extracts the day of the month (1-31) from a date value, useful for daily reporting and scheduling.
• DATEDIFF(): Calculates the difference in days between two date values, supporting age calculations and date range operations.

4. Aggregate Functions

Aggregate functions perform calculations across multiple rows and return a single summary value, fundamental for statistical analysis and reporting.

• SUM(): Calculates the arithmetic total of numeric values in a column, commonly used for financial calculations and metrics.
• AVG(): Computes the mathematical average of numeric values, providing central tendency measurements.
• COUNT(): Counts the number of rows or non-null values in a column, essential for dataset sizing and frequency analysis.
• MIN(): Identifies the smallest value in a column across all qualifying rows.
• MAX(): Identifies the largest value in a column across all qualifying rows.

GROUP BY Clause

The GROUP BY clause organizes rows sharing common values into grouped subsets, enabling aggregate calculations per category. When used with aggregate functions, it transforms detailed records into summary reports. The syntax involves specifying the column(s) by which to group, and the database calculates aggregates for each unique combination of those column values.

ORDER BY Clause

ORDER BY arranges query results in ascending or descending sequence based on one or more columns. By default, results appear in ascending order (ASC), but DESC explicitly requests descending order. Multiple columns can be specified, with each potentially having its own sort direction, enabling sophisticated result sorting strategies.

5. Advanced Functions and Clauses

String-to-Date Conversion

The STR_TO_DATE() function transforms string representations into proper date types using specified format masks. Common format specifiers include %Y (four-digit year), %m (two-digit month), %d (two-digit day), %H (24-hour hour), %i (minutes), and %s (seconds). This function is crucial when importing date data from external sources with non-native formats.

DISTINCT Keyword

DISTINCT eliminates duplicate rows from query results, returning only unique value combinations. When applied to multiple columns, it considers the combination of all specified columns for uniqueness, making it invaluable for deduplication and identifying unique records.

HAVING Clause

HAVING filters grouped data after aggregation, distinguishing itself from WHERE which filters before grouping. It supports conditions on aggregate functon results, enabling sophisticated post-aggregation filtering:

SELECT customer_id, SUM(purchase_amount) AS total_spending
FROM transactions
GROUP BY customer_id
HAVING total_spending > 500;

ROUND versus FORMAT

While both functions handle numeric values, they serve distinct purposes. ROUND performs mathematical rounding to specified decimal places, returning a numeric type suitable for calculations. FORMAT converts numbers into formatted string representations with thousands separators and specified decimal precision, ideal for display purposes but not mathematical operations.

6. Practical Query Patterns

Date Range Queries

Time-based filtering requires precise date boundary handling. Two primary approaches exist for range queries:

-- Method 1: BETWEEN operator for inclusive range matching
SELECT * FROM sales_data
WHERE transaction_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Method 2: Comparison operators for explicit boundary control
SELECT * FROM sales_data
WHERE transaction_date >= '2024-01-01' AND transaction_date <= '2024-12-31';

-- Important considerations:
-- 1. Verify date format compatibility with your database system
-- 2. Account for timezone differences in distributed systems
-- 3. Consider time components when precise boundaries are required

Pagination with LIMIT and OFFSET

Result set pagination controls both the quantity and starting position of returned records. LIMIT specifies maximum rows to return, while OFFSET defines the starting row position (zero-indexed):

SELECT product_name, unit_price, stock_quantity
FROM inventory
ORDER BY product_name ASC
LIMIT 25 OFFSET 50;

This query retrieves 25 products starting from the 51st record (since offset begins at 0), commonly used for implementing page navigation in applications.

Logical and Set Operators

SQL operators determine how conditions combine and how tables connect:

OR: Returns results when any specified condition is true, expanding result sets by accepting multiple matching criteria. Useful for inclusive filtering where multiple values satisfy requirements.

AND: Requires all specified conditions to be true simultaneously, narrowing result sets through conjunctive filtering. Essential for precise data selection with multiple constraints.

IN: Tests membership within a predefined set of values, replacing multiple OR conditions with cleaner syntax. Particularly efficient when checking against known value collections.

ON: Specifies join conditions during table combination, defining how rows from different tables relate to eachother. Critical for relational data retrieval across normalized database structures.

Pattern Matching with LIKE

LIKE enables flexible string matching through wildcard characters, supporting partial and flexible text searches:

-- Match names starting with specific letter
SELECT employee_name, department FROM staff
WHERE employee_name LIKE 'S%';

-- Find records containing substring anywhere in text
SELECT product_code, description FROM catalog
WHERE description LIKE '%wire%';

-- Match exactly 5-character strings using underscore wildcards
SELECT item_name FROM storage_locations
WHERE item_name LIKE '_____';

-- Match strings ending with specific characters
SELECT customer_email FROM clients
WHERE customer_email LIKE '%@gmail.com';

-- Case sensitivity varies by database; use ILIKE (PostgreSQL) or LOWER() transformation for case-insensitive searches

Complex Pattern Combinations

Advanced queries combine LIKE with logical operators for sophisticated filtering:

-- Exclude patterns while matching other conditions
SELECT product_code FROM inventory
WHERE category_code NOT LIKE '%obsolete%' AND stock_level > 0;

-- Multiple pattern alternatives using OR
SELECT supplier_name FROM vendors
WHERE contact_email LIKE '%@bigcorp.com' OR contact_email LIKE '%@global.net';

-- Nested logical conditions with parentheses
SELECT order_number, customer_region FROM sales
WHERE order_total > 1000 AND (shipping_status LIKE '%pending%' OR shipping_status LIKE '%processing%');