Advanced SQL

Not long ago I created an SQL Beginner’s tutorial, you can read it here. Today it’s time to tell you about more advanced topics, such as GROUP BY and HAVING operators, MERGE statement, and stored procedures.

GROUP BY

The GROUP BY clause in SQL is used to group rows in a result set based on one or more columns. It is typically used in conjunction with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX to calculate values for each group.

SELECT category, SUM(sales)
FROM orders
GROUP BY category;

This query will group the rows in the orders table by the category column, and then calculate the sum of the sales column for each group. The result will be a table with one row for each unique category value, showing the category and the total sales for that category.

Another example:

SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department;

This query will group the rows in the employees table by the department column, and then calculate the count of the employee_id column for each group. The result will be a table with one row for each unique department value, showing the department and the total number of employee for that department.

It’s important to note that you must utilize aggregate functions on any columns you choose that are not covered by the GROUP BY clause in order to avoid receiving an error. Additionally, it’s critical to remember that the columns in the GROUP BY clause are the ones that define the groups; the order of the columns in the SELECT statement has no bearing on the result set.

HAVING

Based on conditions for the aggregate function, groups of rows in a result set can be filtered using the HAVING clause in SQL. It is typically combined with the GROUP BY clause to eliminate groups that don’t fit particular requirements.

For example, consider the following SQL query that selects the total sales for each product category in a sample order table, and only returns categories that have total sales greater than 1000:

SELECT category, SUM(sales)
FROM orders
GROUP BY category
HAVING SUM(sales) > 1000;

This query will group the rows in the orders table by the category column, and then calculate the sum of the sales column for each group. The HAVING clause will then filter out any groups where the sum of sales is less than or equal to 1000.

Another example:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING COUNT(employee_id) > 5;

This query will group the rows in the employees table by the department column, and then calculate the average salary for each group. The HAVING clause will then filter out any groups that have less than 5 employees.

Similar to the WHERE clause, the HAVING clause is used to filter groups after the GROUP BY clause has produced them and aggregate functions have been applied.

MERGE

The SQL MERGE statement is used to update or insert data into a target table based on data in a source table, or to combine data from two or more tables into a single table. It is frequently used as an alternative to statements that combine INSERT, UPDATE, and DELETE.

Syntax:

MERGE INTO target_table
USING source_table
ON (join_condition)
WHEN MATCHED THEN
    UPDATE SET target_table.column1 = source_table.column1, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...)
    VALUES (source_table.column1, source_table.column2, ...)

For example, consider a scenario where a company wants to update the prices of their products from a spreadsheet, the company has two tables, one table called products that contain the products information, and another table called prices that contain the updated prices, they can use the following query:

MERGE INTO products
USING prices
ON (products.product_id = prices.product_id)
WHEN MATCHED THEN
    UPDATE SET products.price = prices.price;

This query will match the rows in the products table with the rows in the prices table by the product_id column, and update the price column of the products table with the values from the prices table.

MERGE INTO employees
USING temp_employees
ON (employees.employee_id = temp_employees.employee_id)
WHEN MATCHED THEN
    UPDATE SET employees.salary = temp_employees.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, department, salary)
    VALUES (temp_employees.employee_id, temp_employees.first_name, temp_employees.last_name, temp_employees.department, temp_employees.salary);

This query will match the rows in the employeestable with the rows in the temp_employees table by the employee_id column, if the rows are matched it will update the salary column of the employees table with the values from the temp_employees table, otherwise it will insert a new row to the employees table with the values from the temp_employees table.

Some database management systems such as MySQL and SQLite don’t support the MERGE statement, so you need to use the combination of the INSERT, UPDATE, and DELETE statements instead.

The MERGE command should be tested on a small subset of data before being used on the entire table because it might have a major performance impact on large tables.

Stored Procedures

A precompiled group of SQL statements that are kept in a database as a stored procedure. They are often employed to carry out a specific task or collection of activities, such getting information out of a database or doing tricky math. Compared to running numerous separate SQL statements, stored procedures are more versatile and effective since they can receive input parameters and return output values.

Here’s an example of a simple stored procedure that retrieves all the rows from a table employees:

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM employees;
END

This stored procedure can be executed by calling it like this:

EXEC GetAllEmployees;

Thanks for reading.Happy learning 😄

Do support our publication by following it

The Fresh Writes

Also refer to the following articles.

Java 8 Stream API Interview Questions and Answers

Marat Miftakhov