(SQL Example for Citizen Data Scientist & Business Analyst)
SQL | WHERE Clause
WHERE keyword is used for fetching filtered data in a result set.
- It is used to fetch data according to a particular criteria.
- WHERE keyword can also be used to filter data by matching patterns.
Basic Syntax:
SELECT column1,column2 FROM table_name WHERE column_name operator value;
column1 , column2: fields int the table table_name: name of table column_name: name of field used for filtering the data operator: operation to be considered for filtering value: exact value or pattern to get related data in result
List of operators that can be used with where clause:
operator | description |
> | Greater Than |
>= | Greater than or Equal to |
< | Less Than |
<= | Less than or Equal to |
= | Equal to |
<> | Not Equal to |
BETWEEN | In an inclusive Range |
LIKE | Search for a pattern |
IN | To specify multiple possible values for a column |
Queries
-
- To fetch record of students with age equal to 20
SELECT * FROM Student WHERE Age=20;
Output:
ROLL_NO NAME ADDRESS PHONE Age 3 SUJIT ROHTAK XXXXXXXXXX 20 3 SUJIT ROHTAK XXXXXXXXXX 20
- To fetch record of students with age equal to 20
- To fetch Name and Address of students with ROLL_NO greater than 3
SELECT ROLL_NO,NAME,ADDRESS FROM Student WHERE ROLL_NO > 3;
Output:
ROLL_NO NAME ADDRESS 4 SURESH Delhi
BETWEEN operator
It is used to fetch filtered data in a given range inclusive of two values.
Basic Syntax:
SELECT column1,column2 FROM table_name WHERE column_name BETWEEN value1 AND value2;
BETWEEN: operator name
value1 AND value2: exact value from value1 to value2 to get related data in
result set.
Queries
- To fetch records of students where ROLL_NO is between 1 and 3 (inclusive)
SELECT * FROM Student WHERE ROLL_NO BETWEEN 1 AND 3;
Output:
ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 3 SUJIT ROHTAK XXXXXXXXXX 20 2 RAMESH GURGAON XXXXXXXXXX 18
- To fetch NAME,ADDRESS of students where Age is between 20 and 30 (inclusive)
SELECT NAME,ADDRESS FROM Student WHERE Age BETWEEN 20 AND 30;
Output:
NAME ADDRESS SUJIT Rohtak SUJIT Rohtak
LIKE operator
It is used to fetch filtered data by searching for a particular pattern in where clause.
Basic Syntax:
SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern;
LIKE: operator name
pattern: exact value extracted from the pattern to get related data in
result set.
Note: The character(s) in pattern are case sensitive.
Queries
- To fetch records of students where NAME starts with letter S.
SELECT * FROM Student WHERE NAME LIKE 'S%';
The ‘%'(wildcard) signifies the later characters here which can be of any length and
value.More about wildcards will be discussed in the later set.Output:
ROLL_NO NAME ADDRESS PHONE Age 3 SUJIT ROHTAK XXXXXXXXXX 20 4 SURESH Delhi XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 - To fetch records of students where NAME contains the patter ‘AM’.
SELECT * FROM Student WHERE NAME LIKE '%AM%';
Output:
ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18
IN operator
It is used to fetch filtered data same as fetched by ‘=’ operator just the difference is that here we can specify multiple values for which we can get the result set.
Basic Syntax:
SELECT column1,column2 FROM table_name WHERE column_name IN (value1,value2,..);
IN: operator name
value1,value2,..: exact value matching the values given and get related data in result set.
Queries
- To fetch NAME and ADDRESS of students where Age is 18 or 20.
SELECT NAME,ADDRESS FROM Student WHERE Age IN (18,20);
Output:
NAME ADDRESS Ram Delhi RAMESH GURGAON SUJIT ROHTAK SURESH Delhi SUJIT ROHTAK RAMESH GURGAON - To fetch records of students where ROLL_NO is 1 or 4.
SELECT * FROM Student WHERE ROLL_NO IN (1,4);
Output:
ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 4 SURESH Delhi XXXXXXXXXX 18
Learn to Code SQL Example – SQL | WHERE Clause
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.