Domesticated Brain

Data Filtering In SQL

In this lesson we are working with following student table which I have created using celebrity's name

It has following fields

(studentID, FirstName, LastName, state, age)

SQL Tutorials Data Filtering Student Table

 

SQL SELECT Statement

The select statement is used to fetch data from a database

Using following query we select only three fields (FirstName, LastName, age) from our student table

SELECT FirstName, LastName,age FROM student

SQL Tutorials Data Filtering SQL SELECT Statement Result

 

WHERE Clause

Using WHERE clause we can get specified records under given condition

We also use WHERE clause when we need to join tables in a database

SELECT * FROM student WHERE state="NY"

SQL Tutorials Data Filtering SQL WHERE Clause Result

 

SQL AND Operator

The AND operator response to display records only if both or number of conditions are true

We can combine number of conditions using AND operator

SELECT * FROM student WHERE state='NY' AND age='17'

 SQL Tutorials Data Filtering SQL AND Operator Result

 

SQL OR Operator

The OR operator response displays a record if either the first condition OR the second condition is true.

SELECT * FROM student WHERE state='NY' OR age='17'

SQL Tutorials Data Filtering SQL OR Operator Result

 

LIMIT Statement

The limitation of records returned is done by limit statement

SELECT studentID, FirstName, LastName, state  FROM student LIMIT 5

 SQL Tutorials Data Filtering SQL LIMIT Statement Result 01

SELECT * FROM student LIMIT 10, 7

 SQL Tutorials Data Filtering SQL LIMIT Statement Result 02

 

DISTINCT Statement

In a database table a column may contain many duplicated records and sometimes we only want to filter the distinct records from the database

The DISTINCT key word can be used to returned only distinct values from a database

SELECT DISTINCT age FROM student  

SQL Tutorials Data Filtering SQL DISTINCT Statement Result

 

SQL IN Operator

In this example we are selecting students in the age of 16 and 17

So we can specify multiple values in WHERE Clause rather than using bunch of OR statement

SELECT studentID, FirstName, LastName, age FROM student
WHERE age IN('17','19') ORDER BY age

 SQL Tutorials Data Filtering SQL IN Operator Result

 

SQL NOT IN Operator

NOT IN is the opposite of sql IN operator

In this example we have selected students who are not in the age of 17 and 19

SELECT studentID, FirstName, LastName, age FROM student
WHERE age NOT IN('17','19') ORDER BY age

SQL Tutorials Data Filtering SQL NOT IN Operator Result

 

 ORDER BY ASC

In following example our sql statement select all the students in the student table and sort them in ascending order of age

SELECT studentID, FirstName, LastName, age FROM student
ORDER BY age ASC

SQL Tutorials Data Filtering SQL ORDER BY ASC Result

 

 ORDER BY DESC

The following example we are selecting all students in the student table and sort them in descending order of age

SELECT studentID, FirstName, LastName, age FROM student
ORDER BY age DESC

 SQL Tutorials Data Filtering SQL ORDER BY DESC Result

 

 If you have found  what you expected please comment and like on our facebook page