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 JOINs 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 JOINs:

  1. INNER JOIN: Returns only matching rows from both tables.
  2. 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.
  3. RIGHT JOIN: Returns all rows from the right table, and matching rows from the left table.
  4. 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 );
  1. Main Query (Outer Query):
SELECT Salary FROM EmployeePosition E1
  • This selects the Salary from the EmployeePosition table and aliases it as E1.

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 table E2 that are greater than the current salary in E1.
  • E2.Salary > E1.Salary means we are counting all salaries that are higher than the salary of the current row in E1.
  • 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 the N-th highest salary.
  • How? If there are N-1 distinct salaries greater than E1.Salary, it means that E1.Salary is the N-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 is 4000).
  • For salary 2000: there are 2 salaries greater (3000 and 4000).
  • For salary 1000: there are 3 salaries greater (2000, 3000, and 4000).

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 to N-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 JOINs 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

Popular posts from this blog

Building a Mock API Framework in Java Using Spring Boot

Mastering Selenium Automation with Docker and the Page Object Model Framework

Maven: Solving Build and Dependency Management Challenges