Mastering SQL Functions: A Beginner-to-Expert Guide
Mastering SQL Functions: A Beginner-to-Expert Guide
In this blog, we’ll explore the most crucial SQL functions and concepts, breaking down each one with simple examples to ensure readers of all experience levels can grasp the material. By the end, you’ll have a solid understanding of functions like GROUP BY
, DISTINCT
, COUNT
, and how different types of JOIN
s work, all explained in layman's terms with relatable examples.
1. GROUP BY
: Grouping Data by Columns
The GROUP BY
clause is used when you want to group rows that have the same values in specified columns into aggregated results.
When to Use:
Use GROUP BY
when you want to aggregate data by a particular column, such as summarizing sales per month or counting employees in each department.
Example:
Imagine you have a table Employees
:

You want to count how many employees work in each department. Here’s how you can do that:
SELECT Department, COUNT(EmpID) AS EmployeeCount
FROM Employees
GROUP BY Department;
Output:

This query groups the employees by department and counts how many employees are in each group.
2. DISTINCT
: Removing Duplicate Rows
The DISTINCT
keyword is used to remove duplicates and return only unique values from a column or set of columns.
When to Use:
Use DISTINCT
when you want to eliminate duplicate records from the result. For example, you might have multiple entries of the same product in a sales table and want to see only unique products.
Example:
If you have the following Sales
table:

To get a list of unique products:
SELECT DISTINCT Product
FROM Sales;
Output:

Here, DISTINCT
ensures that "Laptop" appears only once in the result, even though it has multiple entries in the table.
3. COUNT
: Counting Records Across Tables
The COUNT()
function is used to return the number of rows that match a specified condition. It can be used with any SQL SELECT
statement.
When to Use:
Use COUNT()
when you need to count the number of rows in a table or count based on specific conditions.
Example:
If you want to count the total number of employees in the Employees
table:
SELECT COUNT(*) AS TotalEmployees
FROM Employees;
Output:

For counting specific records, for example, only employees in the HR department:
SELECT COUNT(*) AS HREmployees
FROM Employees
WHERE Department = 'HR';
Output:

4. JOIN
: Combining Data from Multiple Tables
SQL JOIN
statements are used to combine data from two or more tables based on a related column. The most common types of joins are:
Types of JOIN
s:
- INNER JOIN: Returns only matching rows from both tables.
- LEFT JOIN: Returns all rows from the left table, and matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.
- RIGHT JOIN: Returns all rows from the right table, and matching rows from the left table.
- FULL OUTER JOIN: Returns all rows when there is a match in either left or right table.
Example 1: INNER JOIN
You have two tables: Employees
and Departments
.

To get the list of employees along with their department names:
SELECT Employees.EmpName, Departments.DeptName
FROM Employees
INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;
Output:

In this INNER JOIN
, only employees with a matching department ID in both tables are returned.
Example 2: LEFT JOIN
If you use LEFT JOIN
, even employees without a matching department will be listed:
SELECT Employees.EmpName, Departments.DeptName
FROM Employees
LEFT JOIN Departments ON Employees.DeptID = Departments.DeptID;
Output:

Here, Bob is shown with NULL
for the department, because his DeptID
(103) doesn't exist in the Departments
table.
5. Practical Example of a FULL OUTER JOIN
A FULL OUTER JOIN
returns all rows from both tables, with matching rows from both sides where available. If there is no match, NULL
is returned for the missing side.
Example:
If you want to see all departments and employees, even if they don’t match, you can use:
SELECT Employees.EmpName, Departments.DeptName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DeptID = Departments.DeptID;
Output:

This query ensures that we see all records from both tables, even if they don’t have a match.
Most Asked Query in Interview : Provide a query is used to find the N-th
highest salary from the EmployeePosition
table. Let's break it down step by step.
Query Breakdown:
SELECT Salary
FROM EmployeePosition E1
WHERE N-1 = (
SELECT COUNT( DISTINCT ( E2.Salary ) )
FROM EmployeePosition E2
WHERE E2.Salary > E1.Salary );
- Main Query (Outer Query):
SELECT Salary FROM EmployeePosition E1
- This selects the
Salary
from theEmployeePosition
table and aliases it asE1
.
2. Subquery Explanation:
WHERE N-1 = (
SELECT COUNT( DISTINCT ( E2.Salary ) )
FROM EmployeePosition E2
WHERE E2.Salary > E1.Salary
);
- The subquery counts the number of distinct salaries in the
EmployeePosition
tableE2
that are greater than the current salary inE1
. E2.Salary > E1.Salary
means we are counting all salaries that are higher than the salary of the current row inE1
.COUNT( DISTINCT (E2.Salary) )
ensures that only distinct salary values are counted (i.e., duplicate salaries are ignored).
3. N-1
Comparison:
WHERE N-1 = (count of distinct salaries greater than E1.Salary)
:- This condition ensures that the current salary (
E1.Salary
) is theN-th
highest salary. - How? If there are
N-1
distinct salaries greater thanE1.Salary
, it means thatE1.Salary
is theN-th
highest salary.
Example:
Assume we have the following EmployeePosition
table:

If N = 2
, the query will return the second-highest salary:
- The subquery checks how many distinct salaries are greater than each salary in the outer query.
- For salary
4000
: there are 0 salaries greater. - For salary
3000
: there is 1 salary greater (which is4000
). - For salary
2000
: there are 2 salaries greater (3000
and4000
). - For salary
1000
: there are 3 salaries greater (2000
,3000
, and4000
).
Since N-1 = 1
, the query will return the salary where exactly 1 distinct salary is greater. In this case, it returns 3000
, which is the second-highest salary.
General Idea:
- The query returns the
N-th
highest salary by checking how many distinct salaries are greater than each salary in the table and comparing that count toN-1
.
Conclusion
SQL is a powerful language for managing and querying databases. Understanding core concepts like GROUP BY
, DISTINCT
, COUNT
, and the various types of JOIN
s will give you the ability to extract meaningful information from your data. With these functions and clauses, you can build complex queries that handle anything from simple reports to more advanced data analytics.
Comments
Post a Comment