Conditional Queries and Aggregate Functions
Conditional queries and aggregate functions are fundamental for data analysis and manipulation in SQL. These tools allow you to extract relevant information and summarize large volumes of data effectively.
Comparison Operators
Comparison operators are used to compare values in SQL. The most common operators are presented below:
-
=
: Equal to. -
!=
or<>
: Not equal to. -
<
: Less than. -
>
: Greater than. -
<=
: Less than or equal to. -
>=
: Greater than or equal to.
For example, the following query retrieves all employees whose salary is greater than or equal to 50,000:
SELECT * FROM employees WHERE salary >= 50000;
Conditional Queries
Conditional queries allow you to filter records according to specific criteria. The `WHERE` and `HAVING` clauses are commonly used to set conditions in queries.
WHERE
: Used to filter rows before performing any aggregation.HAVING
: Applied to the results of aggregations to filter records after applying aggregate functions.
For example, the following query retrieves all employees with a salary greater than 50,000:
SELECT * FROM employees WHERE salary > 50000;
Aggregate Functions in SQL
Aggregate functions are used to perform calculations on a set of values and return a single value. The most common functions are:
-
COUNT()
: Counts the number of rows that meet the condition. -
SUM()
: Calculates the sum of values in a specific column. -
AVG()
: Calculates the average of the values in a column. -
MIN()
andMAX()
: Return the minimum and maximum value of a column, respectively.
For example, the following query calculates the average salary of employees:
SELECT AVG(salary) FROM employees;
Using GROUP BY
The `GROUP BY` clause is used to group rows that have the same values in specified columns into a single row. This is useful for applying aggregate functions to groups of data.
For example, the following query counts the number of employees in each department:
SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department;
Additionally, `GROUP BY` can be combined with `HAVING` to filter the results. For example:
SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department HAVING COUNT(*) > 5;
This query only shows departments that have more than 5 employees.