DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Because the DevOps movement has redefined engineering responsibilities, SREs now have to become stewards of observability strategy.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Related

  • Keep Calm and Column Wise
  • SQL Data Manipulation Language (DML) Operations: Insert, Update, Delete
  • Navigating the Divide: Distinctions Between Time Series Data and Relational Data
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years

Trending

  • Go 1.24+ Native FIPS Support for Easier Compliance
  • Advancing Your Software Engineering Career in 2025
  • Intro to RAG: Foundations of Retrieval Augmented Generation, Part 2
  • Using Java Stream Gatherers To Improve Stateful Operations
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQL Filter Query on FIFA Player Data

SQL Filter Query on FIFA Player Data

In this article, we’ll describe how to use comparison, special, and Boolean operators with the WHERE clause.

By 
Luba Belokon user avatar
Luba Belokon
·
Jul. 17, 18 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
6.3K Views

Join the DZone community and get the full member experience.

Join For Free

In SQL, you can set up criteria to query only the specific rows that interest you the most. The WHERE clause is used in SQL filter queries to apply the conditions in SELECT, INSERT, UPDATE, or DELETE sentences. For the scope of this post, we’ll describe how to use comparison, special, and Boolean operators with the WHERE clause.

You’ll also learn how to build subqueries and a CASE WHEN expression using WHEN. We’ll show the examples using the FIFA players 2018 dataset that contains all the statistics and playing attributes of all the players in the full version of FIFA 18.

Note: This article covers basic SQL filter queries and is dedicated to SQL beginners. For the sake of simplicity, we took only the first 200 rows from the dataset. The example queries work well with Amazon Redshift, but some of them may conflict with other SQL databases.

Comparison Operators

In the WHERE clause, you can use the following operators, which are called relational, since they compare the values of two operands: 

= Equal. A column or expression matches a value.
<> Not equal. Use when you need to exclude a value.
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal


Note:
• In some versions of SQL the inequality operator may be written as !=. • All uppercase characters are less than all lowercase characters, so "Z' < "a," but "a" < "z." Also, all numbers are less than all characters, so "1" < "Z."

SELECT name, age FROM fifa_dataset WHERE club = ‘Real Madrid CF’;

Powered by Statsbot

Note: Text fields in SQL require "single quotes," while numeric fields don’t.

Boolean Expressions

These expressions test for the truth of some condition. To filter query results in SQL, you can use AND, OR, and NOT.

AND. TRUE if all of a set of comparisons are TRUE.

OR. TRUE if either of a set of comparisons is TRUE.

NOT. Reverses the value of any other Boolean operator.

SELECT name FROM fifa_dataset WHERE club = ‘Real Madrid CF’ AND strength >= 80;

Powered by Statsbot

Boolean expressions seem to be simple unless you combine them in a complex SQL filter query. You can effectively manage Boolean and relational expressions using parentheses, which determine the order of operations. When you see a long WHERE clause with Boolean expressions, to keep things simple, you need to estimate the value of internal operators and then apply the upper external operator.

Keep doing this until the expressions end.

SELECT name, club FROM fifa_dataset WHERE NOT((age >= 30 AND balance < 70) OR potential < 90);

Powered by Statsbot

Special Operators

IN() Operator

Instead of writing a complex WHERE clause using Boolean expressions, you can put your values in a list. The IN() operator checks values of a given column by trying to find a match with the values that are inside of a list.

SELECT name, club FROM fifa_dataset WHERE club in (‘Chelsea’, ‘Liverpool’, ‘FC Barcelona’);

BETWEEN Operator

BETWEEN operator is similar to IN(). Between defines the range of values that should go in decreasing order. A query will return the value in a given range.

SELECT name FROM fifa_dataset WHERE potential BETWEEN 90 AND 95;

Powered by Statsbot

Note: Unlike from IN, BETWEEN is sensitive to the order, and the first value in the operator must be the first in alphabetical or numerical order.

LIKE Operator

If you are looking for a name or another value and do not remember how exactly they are written, the LIKE (or NOT LIKE) operator is what you need. It looks for values that match a given pattern. As a condition, LIKE uses group symbols (wildcards). There are two types of wildcards:

  • % replaces any number of symbols before, inside, or after a search term.
  • _ replaces only one symbol.
SELECT name FROM fifa_dataset WHERE club LIKE ‘%senal’ and club NOT LIKE ‘Juv_ntus’;

Powered by Statsbot

Subqueries With the WHEN Clause

You can also use aggregate functions in a WHEN clause along with Boolean expressions, but this works only in subqueries.

SELECT name, age, club FROM fifa_dataset WHERE age =(SELECT min(age) FROM fifa_dataset);

Powered by Statsbot

Note: You don’t need to use subqueries if you use HAVING instead of WHERE for aggregate functions.

Case When With WHEN Clause

A more complex SQL filter query (but not always the optimal one) can use a CASE expression in the WHERE clause. The CASE function itself allows you to return a value when it matches your conditions.

SELECT name, potential, club
FROM fifa_dataset
WHERE
potential = CASE
WHEN potential < 90 THEN potential
END;

Powered by Statsbot

Conclusion

We reviewed different examples of using the WHERE clause in an SQL filter query. Now you can put the conditions, or so-called predicates, for returning rows. These predicates can be as complex as you need, which gives you infinite possibilities when filtering your data. Learn SQL and watch FIFA. 

sql Relational database Database Filter (software) Data (computing)

Published at DZone with permission of Luba Belokon. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Keep Calm and Column Wise
  • SQL Data Manipulation Language (DML) Operations: Insert, Update, Delete
  • Navigating the Divide: Distinctions Between Time Series Data and Relational Data
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!