Lesson 1

Structured Query Language (SQL) is a powerful and versatile language designed for managing relational databases. Two of its core features are grouping and filtering, which allow users to efficiently organize, analyze, and refine data. This overview delves into the fundamentals of SQL grouping and filtering, providing a comprehensive understanding of their importance and functionality in relational database management.

Grouping is a technique used to aggregate and summarize data based on specific criteria. It is often employed to perform statistical analyses, such as calculating the sum, average, count, minimum, or maximum of a particular data set. The main SQL command responsible for grouping is the GROUP BY clause, which is used in conjunction with SELECT statements.
The GROUP BY clause collects rows with similar values in specified columns into groups, enabling the application of aggregate functions. These functions, such as COUNT(), SUM(), AVG(), MIN(), and MAX(), compute single values from the grouped data.
For example, consider a database with a 'sales' table containing columns like 'product_id', 'price', and 'sale_date'. To calculate the total revenue for each product, the following SQL query can be used:

SELECT product_id, SUM(price) as total_revenue
FROM sales
GROUP BY product_id;

This query groups the sales data by 'product_id' and calculates the total revenue for each product using the SUM() function.

Filtering is an essential technique for refining and narrowing down data based on specific conditions. It allows users to selectively retrieve and display data that meets certain criteria. In SQL, filtering is primarily achieved through the WHERE and HAVING clauses.
The WHERE clause is used in conjunction with SELECT, UPDATE, and DELETE statements to filter data based on the conditions specified. It is applied to individual rows and is evaluated before the grouping and aggregation process.
For example, to retrieve the sales records for a specific month, the following SQL query can be used:

SELECT product_id, price, sale_date
FROM sales
WHERE sale_date >= '2023-05-01' AND sale_date <= '2023-05-31';

This query filters the data to display sales records that occurred within the given date range. The HAVING clause, on the other hand, is used to filter the results of a GROUP BY operation based on aggregate functions. It is applied after the grouping process and can be used in conjunction with the GROUP BY clause.
For example, to display the products with total revenue greater than 10000, the following SQL query can be used:

SELECT product_id, SUM(price) as total_revenue
FROM sales
GROUP BY product_id
HAVING total_revenue > 10000;

This query groups the data by 'product_id', calculates the total revenue, and filters the results to display only those products with a total revenue greater than 10000. SQL grouping and filtering are indispensable techniques for organizing, analyzing, and refining data in relational databases. The GROUP BY and WHERE clauses, along with aggregate functions and the HAVING clause, enable users to efficiently manipulate and gain insights from large data sets.

After you have a good understanding of what SQL can do, it is time to move on to more sophisticated queries. It is important to understand what your database engine is capable of, and what your requirements are for pulling information from your system. This module will introduce you to some of the more advanced queries used for extracting information from tables. These new clauses include

1. grouping and
2. filtering.

You will see how to make sure you skip duplicate information in the database, making the information more meaningful and easier to digest.

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group.
These functions are:

COUNT, MAX, MIN, AVG, SUM, DISTINCT

SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition.
If the WHERE condition is not specified, then the query returns the total number of rows in the table.
For Example: If you want the number of drinks in a particular category, the query would be:

Example of SELECT COUNT (*)

SELECT COUNT (*) FROM fridge
WHERE pop = 'Pepsi'; >

One of the advanced uses of the Select statement is in Group functions. Group functions are functions applied to a group of rows.

Examples of Group Functions

COUNT(*) - Returns the number of rows in the group.
MIN(exp) - Returns the minimum value of the expression evaluated on each row of the group.
MAX(exp) - Returns the maximum value of the expression evaluated on each row of the group.
AVG(exp) - Returns the average value of the expression evaluated on each row of the group.

The SELECT statement retrieves data from a database and returns it to you in the form of query results. As a reminder, the exact format of the query results will vary from one SQL product to another. Here are several more sample queries that retrieve information about sales offices:
List the sales offices with their targets and actual sales.

SELECT CITY, TARGET, SALES
FROM OFFICES;