Mastering SQL: Essential Functions, Data Manipulation, and Joins

Structured Query Language (SQL) provides a powerful toolkit for extracting, transforming, and analyzing data stored in relational databases. Whether you are cleaning messy strings, performing mathematical calculations, summarizing large datasets, or connecting multiple tables, understanding these core functions and operations is essential.

1. Mathematical Functions

Math functions allow you to perform arithmetic calculations directly on your numeric data columns.

POWER(base, exponent): Raises a number to the specified power.
- Example: SELECT POWER(3, 2); returns 9.
ROUND(number, decimals): Rounds a numeric field to a specified number of decimal places.
- Example: SELECT ROUND(123.456, 2); returns 123.46.
MOD(dividend, divisor): Returns the remainder of a division operation.
- Example: SELECT MOD(10, 3); returns 1.

2. Text and String Functions

Text data is rarely pristine. String functions help you clean, format, and extract specific portions of text.

Case Conversion: UPPER(str) (or UCASE()) converts text to uppercase, while LOWER(str) (or LCASE()) converts it to lowercase.
- Example: SELECT UPPER('hello'); returns HELLO.
Extraction:
- SUBSTRING(str, pos, len) (synonymous with MID() and SUBSTR()) extracts a portion of a string.
- LEFT(str, len) extracts characters from the left.
- RIGHT(str, len) extracts characters from the right.
- Example: SELECT LEFT('Database', 4); returns Data.
Length and Searching:
- LENGTH(str) returns the length of the string in bytes.

Mastering SQL Essential Functions Data Manipulation And Joins