Sorting and Filtering data in Relational Databases

Sorting and Filtering in Database

A. Sorting Data in Relational Database Management System with ORDER BY 

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword

Follow the tutorial to learn "How to use ORDER BY  and LIKE in database?"

Basic Syntax

The basic syntax to use ORDER BY keywords to sort data in Relational Database like MS SQL Server of MySQL or Oracle is as follows

SELECT column(s) FROM tbl_name ORDER BY column_name ASC; (for Ascending Order)

SELECT column(s) FROM tbl_name ORDER BY column_name DESC; (for Descending Order)

Example

SELECT name FROM tbl_employee ORDER BY name ASC;

The query above will display the name of employees in Ascending order by names.

SELECT name FROM tbl_employee ORDER BY salary DESC;

The query above will display the names of employees in Descending order based on salary (employees with high salary will be on top).

B. Filtering Data in Relational Database Management System with WHERE and LIKE Keywords

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

• % - The percent sign represents zero, one, or multiple characters
• _ - The underscore represents a single character

Basic Syntax

The basic syntax to use WHERE keywords in Relational Databases is as follows

SELECT column(s) FROM tbl_name WHERE column LIKE '%keyword';

The query above will filter or search data which contains the keyword at last.

SELECT column(s) FROM tbl_name WHERE column LIKE 'keyword%';

The query above will filter or search data which contains the keyword at first.

SELECT column(s) FROM tbl_name WHERE column LIKE '%keyword%';

The query above will filter or search data which contains keyword.



Example

SELECT * FROM tbl_employee WHERE name LIKE 'v%';

The query above will display the details of employees if they have letter 'v' as the first letter in their name.

SELECT * FROM tbl_employee WHERE name LIKE '%m';

The query above will display the details of employees if they have letter 'm' as the last letter in their name.

SELECT * FROM tbl_employee WHERE name LIKE '%m%';

The query above will display the details of employees if they have letter 'm' in their name.

C. Making complex queries with AND/OR keywords in Relational Database

The AND and OR operators are used to filter records based on more than one condition:

• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE.

 Follow the tutorial to learn "How to use AND/OR keywords in database?"

Basic Syntax

The basic syntax to make complex queries with AND/OR keywords in Relational Databases is as follows

SELECT column(s) FROM tbl_name WHERE condition AND condition; (with AND keyword)

SELECT column(s) FROM tbl_name WHERE condition OR condition; (with OR keyword)

Example

SELECT * FROM tbl_employee WHERE address = 'kathmandu' AND age > 24;

The query above will display the list of employees whose address is 'Kathmandu' and age is greater than 24. (Here both conditions need to be True);

SELECT * FROM tbl_employee WHERE address = 'kathmandu' OR salary > 40000;

The query above will display the list of employees whose address is 'Kathmandu' or salary is greater than 40,000. (Here either both or single conditions can be True);

Do you want to learn Database?

Here's a "Database for Beginners" FREE Course for you.

Get Access Now