We offer you a brighter future with FREE online courses - Start Now!!
SQL is a query language which queries and returns the desired data from the database. We use SQL for multiple operations related to data, some of them being viewing the data and analyzing the data.
There are various clauses available in SQL, which help us to work on data easily. We will discuss the following clauses in this tutorial – where, and, or, like, and many more.
What are Clauses in SQL?
Clauses are in-built functions available to us in SQL. With the help of clauses, we can deal with data easily stored in the table.
Clauses help us filter and analyze data quickly. When we have large amounts of data stored in the database, we use Clauses to query and get data required by the user.
Some of the examples of clauses are – where, and, or, like, top, etc.
Demo Database
Let us first view our database, which we will use in this tutorial; we will be using a database of DataFlair employees and their details. Our database is as follows :
SELECT * FROM dataflair_employee ;
1. Where Clause in SQL
We use the WHERE clause to specify conditionals in our SQL query. Where clause can be used in the update and delete statements as well as to perform operations on the desired data.
Rules and Usage:
1. Rules:
- When using a Where clause, we need to mention at least one condition.
Technology is evolving rapidly!
Stay updated with DataFlair on WhatsApp!!
2. Usage:
- Used to query and transact the database.
- Used with an update and delete statements to make sure the right data points are deleted.
Syntax:
SELECT * FROM tableName WHERE condition ;
Example 1: Let us find the details of all employees who earn more than 25,000 and are above 25.
Select * from dataflair_employee WHERE age>25 and salary >25000 ;
Here we can see, the details of all employees who earn more than 25,000 and are above 25.
2. And Clause in SQL
We use And clause while specifying multiple conditions together in a query with the Where clause.
Rules and Usage:
1. Rules:
- When using an And clause, we need to mention at least two conditions(the result would satisfy both.)
2. Usage:
- Used to query and transact the database.
- Used with an update and delete statements to make sure the right data points are deleted.
- Returns a data point only if all conditions meet the requirements.
Syntax:
SELECT * FROM tableName WHERE condition1 AND condition2 ;
Example 1: Let us find the details of employees whose age is between 22 to 28 and earn less than 30,000.
Select * from dataflair_employee where age>22 and age<28 and salary<30000 ;
Here, we can see the details of employees whose age is between 22 and 28 and have a salary less than 30,000.
3. Or Clause in SQL
Or clause is beneficial when we need to pass multiple conditions, and we need data that satisfies any one of those specified conditions.
Rules and Usage:
1. Rules:
- When using an Or clause, we need to mention at least two conditions(the result would be at least one of the specified conditions.)
2. Usage:
- Used while transacting and querying a database.
- Used in the update and delete statements.
- Or clause returns a data point when any one of the specified conditions is met.
Syntax:
SELECT * FROM tableName WHERE condition1 OR condition2 ;
Example 1: Let us find the employees with age more than 26 or a salary more than 30000
Select * from dataflair_employee where salary > 30000 OR age > 26
Here, we can see the details of employees with age more than 26 and salary more than 30,000.
4. Like Clause in SQL
LIKE clause is beneficial to find specific patterns in the data. We use specific symbols i.e (%) and ( _ ). The rules and usage is specified below.
Rules and Usage:
1. Rules:
- % – Represents zero, one, or multiple characters.
- _ – Represents one single character.
2. Usage:
- Used to retrieve data points that satisfy the pattern passed using the like clause.
Syntax :
SELECT * FROM tableName WHERE column2 LIKE pattern ;
Example 1: Let us find the details of employees whose name starts with A.
Select * from dataflair_employee where name_emp LIKE 'A%' ;
Here, we can see the details of the employees whose name starts with ‘A’.
5. Limit Clause in SQL
We use the limit clause when we have a large amount of data in the table. With the help of the limit clause, we can restrict the number of rows our query returns.
Rules and Usage:
1. Rules:
- We need to specify a number after the limit clause.
- Float and exponential values can’t be utilized.
2. Usage:
- Used to limit the data that a query would return.
Syntax:
SELECT * FROM tableName LIMIT number ;
Example 1: Let us view the first 5 rows of data from our table DataFlair_Employee.
Select * from dataflair_employee limit 5 ;
Here, we can see the first five rows of our table.
6. Order By Clause in SQL
We use order by clause to sort data in ascending or descending order as required by the user. By default, the data is sorted in ascending order.
Rules and Usage:
1. Rules:
- A comparable data column should be passed in the query.
- Any column can be used in the order by clause, even those which do not appear in our select statement.
- We can sort data in ascending or descending order (by default sorting is done in ascending order).
2. Usage:
- Order by clause is useful to get data in required sorting orders.
Syntax:
SELECT * FROM tableName ORDER BY column1, column2, ... ASC/DESC;
Example 1: Let us view the details of employees ordered in Descending order according to salary.
Select * from dataflair_employee Order by salary desc ;
Here, we have obtained the details of employees in descending order of salary.
7. Group By Clause in SQL
We use order by clause to get the summary of data in rows and is mostly taken in usage with the aggregate functions like Count, Sum, etc.
Rules and Usage:
1. Rules:
- Columns appearing in the Select clause can only be taken care of in the Group By clause.
- Columns we are passing to the Group By clause should be of comparable type.
2. Usage:
- We use Group by clause to get the groups present in data.
Syntax:
SELECT * FROM table_name WHERE condition GROUP BY column1 ;
Example 1: Let us count the employees with each age.
SELECT COUNT(age), age FROM dataflair_employee GROUP BY age ;
Here, we have generated a count of the number of employees of each age in our organisation.
Summary
We have discussed various clauses and their utilities. SQL clauses are beneficial as they simplify our queries and help us to get data quickly and efficiently.
With the use of clauses, we can get data in patterns, groups, and ordered format as well.
When we use the clause available, we can get rid of extra complexities arising in queries due to large formulas or large sets of data. This also helps us to increase the efficiency of our queries and the return time of the query.