Filtering Data in SQL

Using the WHERE Clause

In SQL, the WHERE clause is a crucial component for filtering and retrieving specific records from a database table. It enables you to narrow down the results based on specific conditions, ensuring that you retrieve only the data that meets your criteria.

The syntax:

SELECT column1, column2
FROM table_name
WHERE condition;

Common symbols used in WHERE statements include:

1. > (greater than)

2. < (less than)

3. >= (greater than or equal to)

4. <= (less than or equal to)

5. = (equal to)

6. != (not equal to)

When using non-numeric columns in the WHERE clause wrap the names in single or double quotes.

Operators

sample table:

AND Operator:

This operator is used to filter data based on multiple conditions. It returns records that fulfill all the specified conditions.

SELECT *
FROM employees
WHERE job_title = 'Supervisor' AND department = 'IT';

OR Operator:

The OR operator is used when you want to retrieve records that match at least one of the conditions specified.

SELECT employee_id, employee_name,job_title
FROM employees
WHERE job_title = 'Manager' OR job_title = 'Supervisor';

LIKE Operator:

The Like operator is used for pattern matching within text columns. The % symbol represents any number of characters, while the _ symbol represents a single character. This is particularly useful for flexible searches.

SELECT *
FROM products
WHERE product_name LIKE 'App%';

+----+------------------+
| ID |   product_name   |
+----+------------------+
| 1  | Apple iPhone     |
| 2  | Apple MacBook    |
| 3  | App Store Gift   |
+----+------------------+

ILIKE Operator:

similar to Like only that it is case-insensitive

1.          SELECT *
            FROM products
            WHERE product_name ILIKE '%pro%';
   

    +----+------------------+
    | ID |   product_name   |
    +----+------------------+
    | 1  | Apple iPhone     |
    | 4  | Android Phone    |
    | 7  | Wireless Headphone |
    +----+------------------+
   

BETWEEN:

shows that the values in the range are included.

SELECT *
FROM sales
WHERE  amount BETWEEN 1000 AND 5000;

WHERE ... IN

The "WHERE ... IN" clause allows you to filter rows based on a set of specific values within a column. This is particularly useful when you have a list of values and you want to retrieve rows that match any of those values.

SELECT *
FROM employees
WHERE department IN ('HR', 'IT');

NOT IN

You can also use the "NOT IN" operator to retrieve rows that don't match any of the specified values:

SELECT * FROM employees WHERE department NOT IN ('HR', 'IT');

Using Quotes

When dealing with text values in your conditions, it's important to enclose them in single quotes, like 'value'. This ensures that the database engine interprets them as strings.

The HAVING Clause

The HAVING clause is used in conjunction with aggregate functions like SUM, AVG, COUNT, etc., when you want to filter results based on the result of these functions. Unlike the WHERE clause, which filters rows before aggregation, the HAVING clause filters the aggregated results. Used mostly in conjunction with the GROUP BY clause.

SELECT salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson
HAVING SUM(amount) > 10000;

vamos a practicar