🗂️ [SQL] SQL Key Functions

Understanding Key SQL Functions

SQL functions are built-in operations that help you manipulate and retrieve data from your databases more effectively. Here's a guide to some of the most commonly used SQL functions.

💡 Key SQL Functions

1. CONCAT()

• Description : Concatenates two or more strings into one string
• Syntax : The CONCAT (string1, string2, ...)
• Example : The SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
  -Use Case : Combining first and last names to create a full name.

2. AS

• Description : Renames a column or table with an alias.
• Syntax : The SELECT column_name AS alias_name FROM table_name;
• Example : The SELECT name AS customer_name FROM users;
• Note : Cannot be used in The WHERE, USING, ON
  -Use Case : Simplifying column names in the result set

3. DISTINCT

• Description : Removes duplicate records from the results
• Syntax : The SELECT DISTINCT column1, column2 FROM table_name;
• Example : The SELECT DISTINCT city FROM users;
  -Use Case : Finding unique values in a column

4. GROUP BY

• Description : Groups rows that have the same values into summary rows
• Syntax : The SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
• Example : The SELECT city, COUNT(*) FROM users GROUP BY city;
  -Use Case : Aggregating data to get counts or sums per group.

5. HAVING

• Description : Filters groups created by the GROUP BY clause.
• Syntax : The SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;
• Example : The SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 5;
  -Use Case : AFiltering aggregated results.

6. COUNT()

• Description : Returns the number of rows that matches a specified criteria
• Syntax : The SELECT COUNT(column_name) FROM table_name;
• Example : The SELECT COUNT(*) FROM users WHERE age > 30;
  -Use Case : Counting the number of records

7. AVG()

• Description : Returns the average value of a numeric column
• Syntax : The SELECT AVG(column_name) FROM table_name;
• Example : The SELECT AVG(salary) FROM employees;
  -Use Case : Calculating the average of a column

8. SUM()

• Description : Returns the total sum of a numeric column
• Syntax : The SELECT SUM(column_name) FROM table_name;
• Example : The SELECT SUM(salary) FROM employees;
  -Use Case : Calculating the total of a column

9. MAX()

• Description : Returns the maximum value in a set
• Syntax : The SELECT MAX(column_name) FROM table_name;
• Example : The SELECT MAX(salary) FROM employees;
  -Use Case : Finding the highest value in a column

10. MIN()

• Description : Returns the minimum value in a set
• Syntax : The SELECT MIN(column_name) FROM table_name;
• Example : The SELECT MIN(salary) FROM employees;
  -Use Case : Finding the lowest value in a column

---

💡 Example Query Using Multiple Functions

SELECT
    department,
    COUNT(employee_id) AS num_employees,
    AVG(salary) AS average_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM
    employees
GROUP BY
    department
HAVING
    COUNT(employee_id) > 10
ORDER BY
    average_salary DESC;

Explanation: This query groups employees by department, counts the number of employees per department, calculates the average, maximum, and minimum salary per department, filters departments with more than 10 employees, and sorts the results by average salary in descending order