Filtering and Advanced SELECT in SQL

 In our previous tutorial, we introduced the basics of the SQL SELECT statement. Now, let's take a deeper dive into filtering and more advanced techniques to refine your data queries.

Review of Basic SELECT

Before we dive into advanced filtering, let's quickly recap the basic SELECT statement.

SELECT column1, column2, ...

FROM table_name

WHERE condition;

• column1, column2, ...: The columns you want to retrieve.
• table_name: The name of the table.
• condition: An optional clause to filter data.

Filtering Data with the WHERE Clause

The WHERE clause is your primary tool for filtering data based on specified conditions. It's essential for retrieving the data you need. Here are some common operators to use in your conditions:

• = (equal)
• != or <> (not equal)
• < (less than)
• > (greater than)
• <= (less than or equal to)
• >= (greater than or equal to)
• AND (logical AND)
• OR (logical OR)

Example: Retrieving Employees with a Salary Greater than 50,000

SELECT first_name, last_name, salary

FROM employees

WHERE salary > 50000;

This query retrieves the first name, last name, and salary of employees whose salary is greater than 50,000.

Combining Multiple Conditions

You can combine multiple conditions using AND and OR operators.

Example: Retrieving Software Engineers with a Salary Greater than 60,000 or Web Developers

SELECT first_name, last_name, job_title, salary

FROM employees

WHERE (job_title = 'Software Engineer' AND salary > 60000) OR job_title = 'Web Developer';

This query retrieves employees who are either Software Engineers with a salary above 60,000 or Web Developers.

Using Wildcards for Partial Matches

Wildcards allow for partial matches in your conditions:

• % represents zero or more characters.
• _ represents a single character.

Example: Retrieving Employees Whose Last Name Starts with 'S'

SELECT first_name, last_name

FROM employees

WHERE last_name LIKE 'S%';

This query retrieves employees whose last names start with 'S'.

Advanced SELECT Techniques

Aliasing Columns

You can use aliases to rename columns in the result set.

Example: Aliasing Columns

SELECT first_name AS "First Name", last_name AS "Last Name"

FROM employees;

This query renames the first_name column as "First Name" and last_name as "Last Name" in the result set.

Sorting by Multiple Columns

To sort results by multiple columns, list them in the ORDER BY clause.

Example: Sorting by Job Title and Salary

SELECT first_name, last_name, job_title, salary

FROM employees

ORDER BY job_title, salary DESC;

This query sorts employees first by job title in ascending order and then by salary in descending order.

Conclusion

Filtering and advanced SELECT techniques are fundamental to working with SQL. These tools allow you to precisely tailor your data queries, extract meaningful insights, and perform complex data manipulations. As you continue your SQL journey, explore other advanced topics like joins, subqueries, and more to become a proficient SQL data wrangler. Happy querying!