Filtering Data with SQL

Data is being generated all the time. Hence, it is of utmost importance to understand how to filter data. The previous article was on the ways to select and retrieve data. In this article, you will learn filtering data with SQL.

Table of Contents

• Basic Filtering Data with SQL
• Advanced Filtering of Data
• Filtering using Wildcards

In this article, I am going to consider the following table to explain to you how to filter data with SQL.

Patients Table

Let us start with filtering data in SQL.

Filtering Data 

Basic Filtering Data with SQL

Data Filtering is one of the most common tasks that Data Analysts perform on a daily basis. It is used when the users know what exactly do they want to retrieve and helps the users narrow down the data from large datasets to small chunks of data(subsets) to generate meaningful insights.  

Few of the benefits of filtering of data are:

• Optimize performance of queries
• Reduction in the number of records
• Decrease the strain on the client applications

This section of the article will help you understand how to perform basic filtering of data with the help of WHERE and BETWEEN clauses. Let us start with the WHERE clause

WHERE CLAUSE

As mentioned above, WHERE clause is used to filter the data by mentioning conditions. This clause is generally used with the SELECT, INSERT, UPDATE and DELETE commands along with various operators. 

Syntax:

[code]
SELECT column1, column2, ...
FROM TableName WHERE Conditions;
[/code]
 
Example:
[code]
SELECT PatientName, State FROM Patients WHERE Age > 20;
[/code]

Few of the popular operators used with the WHERE clause are as follows:

Few other operators used commonly with WHERE clause for basic filtering are:

Let us now understand some of the most common commands that are used for advanced filtering.

Advanced Filtering of Data

Advanced filtering of data in SQL can be achieved by using the IN, OR, AND, NOT, ALL, and ANY clauses. Refer to the table below.

Note:

• While using the OR clause, note that the DBMS will not evaluate the second condition in the WHERE clause if the first condition is met.
• SQL processes OR before AND. So in case, you want it to go to AND operation you have to use parentheses ().

Moving ahead with this article, let us discuss the use of wildcards in the filtering of data

Filtering using Wildcards

There are often times when the users aren’t clear about what exactly they are looking for.  We either would like to filter the data starting/ ending with something, data having a certain number of characters etc.

In such cases, the LIKE operator is used with wildcards to filter data according to required patterns

The following are the two wild cards that are most commonly used.

• %[Percentage Sign] – It matches 0 or more characters.
• _ [Underscore]– It matches exactly one character.

LIKE

Most commonly used in a WHERE clause is to search for a pattern in a column of a table with the help of wildcards.

Syntax:

[code]
SELECT Column1, Column2, ...
FROM TableName
WHERE ColumnName LIKE Pattern; 
[/code]
 
Example:
[code]
SELECT * FROM Patients
WHERE PatientName LIKE ‘A%’;
[/code]

The various patterns that can be used with the LIKE operator are as follows:

Now that you know how to filter data in SQL, let us take a look at the top interview questions on the same.

Interview Questions on Filtering Data in SQL

Q1. Write a query to add email validation to a database.

[code]
SELECT Email FROM Patients WHERE NOT REGEXP_LIKE(Email,[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4});
[/code] 

Q2. Write a query to remove duplicate queries from the table.

[code]
SELECT PatientID FROM Patients WHERE ROWID (SELECT MAX (rowid) FROM Patients P WHERE PatientID = P.PatientID);
 
DELETE FROM Patients WHERE ROWID(SELECT MAX (rowid) FROM Patients P WHERE PatientID = P.PatientID);
[/code]

Q3. Write a query to retrieve patient records whose Age is greater than 25.

[code]
SELECT * FROM Patients WHERE Age > 25;
[/code]

Q4. Write a query to retrieve patient records whose name starts from S.

[code]
SELECT * FROM Patients WHERE PatientName LIKE ‘S%’;
[/code] 

Q5. Write a query to retrieve patient records whose Age is between 12 to 25.

[code]
SELECT * FROM Patients WHERE Age BETWEEN 12 AND 25;
[/code]

With this, we end this article on how to filter data in SQL. We hope you found it informative. You can refer to the article on MySQL Commands for a detailed walkthrough of all commands.

Top Trending Tech Articles:
Career Opportunities after BTech | Online Python Compiler | What is Coding | Queue Data Structure | Top Programming Language | Trending DevOps Tools | Highest Paid IT Jobs | Most In Demand IT Skills | Networking Interview Questions | Features of Java | Basic Linux Commands | Amazon Interview Questions

Recently completed any professional course/certification from the market? Tell us what liked or disliked in the course for more curated content.

Click here to submit its review with Shiksha Online.0.00 avg. rating (0% score) – 0 votes