🧾 SQL References & Functions – A Handy Guide to SQL Keywords and Built-In Functions
🧲 Introduction – Why Learn SQL References and Functions?
SQL (Structured Query Language) is the foundation for managing and querying databases. But beyond just writing SELECT and INSERT statements, understanding SQL references, keywords, and functions empowers developers, analysts, and DBAs to write precise, powerful, and optimized queries.
Whether you’re working with MySQL, SQL Server, or MS Access, each platform offers a rich set of keywords and built-in functions that allow for advanced operations like string manipulation, date calculations, type conversions, and data aggregation.
🎯 In this guide, you’ll learn:
- Common SQL keywords used across all RDBMS
- Popular built-in functions in MySQL and SQL Server
- Specialized features in Microsoft Access
📘 Topics Covered in This Guide
| 🔢 Topic | 🔎 Description |
|---|---|
| 📚 SQL Keywords | Core commands used across all SQL operations |
| 🔧 MySQL Functions | Common MySQL functions for string, math, date, and aggregation |
| 🛠️ SQL Server Functions | Powerful T-SQL functions for advanced data processing |
| 📂 MS Access Functions | Built-in functions tailored for Microsoft Access users |
1. 📚 SQL Keywords
SQL keywords are reserved words that define the structure and logic of SQL queries. They are case-insensitive, though capitalized for readability.
✅ Common SQL Keywords:
| Keyword | Purpose |
|---|---|
SELECT | Retrieve data from one or more tables |
INSERT | Add new data into a table |
UPDATE | Modify existing records |
DELETE | Remove records |
CREATE | Create database objects like tables |
DROP | Delete database objects |
ALTER | Modify table structures |
WHERE | Filter records |
ORDER BY | Sort results |
GROUP BY | Group records for aggregation |
HAVING | Filter grouped records |
JOIN | Combine rows from multiple tables |
DISTINCT | Remove duplicate rows |
LIMIT / TOP | Restrict the number of rows returned |
2. 🔧 MySQL Functions
MySQL offers numerous built-in functions across different categories:
🔤 String Functions
| Function | Description |
|---|---|
CONCAT(a, b) | Combine strings a and b |
UPPER(str) | Convert string to uppercase |
LOWER(str) | Convert string to lowercase |
SUBSTRING(str, start, len) | Extract part of a string |
📅 Date Functions
| Function | Description |
|---|---|
NOW() | Current date and time |
CURDATE() | Current date |
DATE_ADD(date, INTERVAL n DAY) | Add days to a date |
DATEDIFF(date1, date2) | Difference between two dates |
➕ Math Functions
| Function | Description |
|---|---|
ROUND(num, decimals) | Round number to decimals |
FLOOR(num) | Round down |
CEIL(num) | Round up |
ABS(num) | Absolute value |
📊 Aggregate Functions
| Function | Description |
|---|---|
COUNT(*) | Count records |
SUM(col) | Total of column |
AVG(col) | Average value |
MAX(col) | Maximum value |
MIN(col) | Minimum value |
3. 🛠️ SQL Server Functions
SQL Server (T-SQL) includes a rich library of system functions.
🔠 String Functions
| Function | Description |
|---|---|
LEN(str) | Length of string |
REPLACE(str, find, replace) | Replace substrings |
LEFT(str, n) | First n characters |
RIGHT(str, n) | Last n characters |
🕒 Date & Time Functions
| Function | Description |
|---|---|
GETDATE() | Current system datetime |
DATEADD(day, n, date) | Add days to a date |
DATEDIFF(day, start, end) | Day difference |
FORMAT(date, 'dd-MM-yyyy') | Format date |
➗ Math & Conversion Functions
| Function | Description |
|---|---|
CAST(value AS datatype) | Convert data types |
CONVERT(datatype, value) | Another type conversion |
ROUND(num, decimals) | Round values |
POWER(x, y) | x raised to power y |
📊 Ranking & Analytic Functions (SQL Server 2012+)
| Function | Description |
|---|---|
RANK() OVER(...) | Ranking within partitions |
ROW_NUMBER() | Row number in result set |
NTILE(n) | Divide rows into n buckets |
4. 📂 MS Access Functions
Microsoft Access supports a variety of VBA-style functions tailored for database manipulation.
🔤 Text Functions
| Function | Description |
|---|---|
Len(text) | Length of text |
Left(text, n) | First n characters |
Mid(text, start, length) | Extract part of a string |
Instr(start, text, find) | Find substring position |
📅 Date Functions
| Function | Description |
|---|---|
Date() | Current system date |
Now() | Current date and time |
DateAdd("d", n, date) | Add n days |
DateDiff("d", date1, date2) | Difference in days |
💡 Miscellaneous
| Function | Description |
|---|---|
Nz(value, substitute) | Replace null with substitute |
IIf(condition, true, false) | Inline conditional |
Switch(expr1, result1, expr2, result2...) | Multiple condition handler |
📌 Summary – Recap & Next Steps
Mastering SQL keywords and built-in functions is key to writing optimized and powerful queries across platforms like MySQL, SQL Server, and MS Access.
🔍 Key Takeaways:
- SQL keywords form the backbone of all SQL operations
- MySQL and SQL Server provide specialized functions for strings, dates, math, and aggregates
- MS Access offers user-friendly, VBA-like syntax for rapid development
⚙️ Real-World Relevance:
These functions are used daily by database administrators, backend developers, and data analysts to build dynamic applications, generate reports, and maintain data quality.
❓ FAQ – SQL References & Functions
❓ Are SQL keywords case-sensitive?
✅ No, SQL keywords are case-insensitive, but uppercase improves readability.
❓ Do MySQL and SQL Server use the same functions?
🟡 Many are similar, but they have distinct syntax and capabilities. Always check documentation.
❓ Can I create custom functions in SQL?
✅ Yes, SQL Server supports user-defined functions (UDFs). MySQL supports them with CREATE FUNCTION.
❓ What’s the difference between CAST() and CONVERT() in SQL Server?
✅ Both convert data types. CAST() is ANSI-compliant; CONVERT() allows format styles.
Share Now :
