In - Built functions in SQL

SQL Functions

SQL functions are powerful tools that help in performing specific tasks on data. These functions simplify and speed up database operations. Below are common SQL functions explained with real-life analogies and examples.

Common SQL Functions

Function	Description	Real-Life Example	SQL Syntax
COUNT	Counts the number of rows in a set of data.	Counting the number of students in a classroom.	SELECT COUNT(*) FROM students;
SUM	Adds up all values in a column.	Calculating the total price of items in a shopping cart.	SELECT SUM(price) FROM shopping_cart;
AVG	Finds the average value of numbers in a set.	Calculating the average score of a class in a test.	SELECT AVG(score) FROM math_quiz;
MAX	Finds the highest value in a column.	Finding the tallest person in a group.	SELECT MAX(height) FROM people;
MIN	Finds the smallest value in a column.	Finding the shortest person in a group.	SELECT MIN(goals) FROM soccer_team;
UPPER	Converts all text to uppercase.	Changing "hello" to "HELLO."	SELECT UPPER(name) FROM customers;
LOWER	Converts all text to lowercase.	Changing "Hello" to "hello."	SELECT LOWER(name) FROM customers;
CONCAT	Combines two or more strings into one.	Combining "John" and "Doe" to form "John Doe."	SELECT CONCAT(first_name, ' ', last_name) FROM employees;
SUBSTRING	Extracts part of a string from text.	Extracting the first three letters of a last name.	SELECT SUBSTRING(last_name, 1, 3) FROM employees;
LENGTH	Returns the length of a string.	Counting the number of characters in a tweet.	SELECT LENGTH(description) FROM tweets;
NOW	Returns the current date and time.	Checking the time on your watch or phone.	SELECT NOW();
DATE Functions	Manipulate and calculate with dates (e.g., DATEADD, DATEDIFF).	Counting the days until your birthday.	SELECT DATEDIFF(day, birthday, NOW()) FROM people;
CASE	Implements conditional logic to return different values.	Deciding what to wear based on the weather (e.g., hot or cold).	SELECT name, CASE   WHEN temperature > 80 THEN 'Hot'   WHEN temperature < 50 THEN 'Cold'   ELSE 'Moderate' END AS weather FROM locations;

Summary

SQL functions are like tools in a toolkit, helping you efficiently perform tasks such as counting, summing, finding maximum values, and working with text or dates. These examples demonstrate how SQL simplifies data management in databases while drawing parallels to real-life scenarios for better understanding.