Clauses are in-built functions available 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.
- Where Clause in SQL
- And Clause in SQL
- Or Clause in SQL
- Like Clause in SQL
- Order By Clause in SQL
- Group By Clause in SQL
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.
2. Usage:
Used to query and transact the database.
Used with an update and delete statements
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.
SQL>Select * from emp WHERE age>25 ;
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
2. Usage:
Used to query and transact the database.
Used with an update and delete statements
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 greater than 25 and esal greater then 39000.
SQL>Select * from emp WHERE age>25 and esal>39000;
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
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 emp where salary > 30000 OR age > 26
4. Like Clause in SQL
LIKE clause is beneficial to find specific patterns in the data. We use specific symbols i.e (%) and ( _ ).
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.
SQL>Select * from emp where ename LIKE 'A%' ;
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.
Note: By default the fileds attribute(salary) will be in (asc)ascending order
It is used to prove or categorized the data that which return information for each group .
Synax :
Select <col name> , group function <col> from<table name> group by <col name>;
Ex : Display the deptno , sum of salaries of the employees group by department number .
SQL>Select dno , sum(sal) from employee group by (dno);
Display the deptno , total num of employees group by department name .
sql>Select deptno , count (eno) from employee group by (deptname);
Having clause :
The having clause was introduced in SQL because the ‘where’ keyword could not be used with aggregate functions . The having clause must preceed after groupby clause .
Synax :
Ex : Display ecod , total number of product code from products groupby ecode whose product price is less than average of 100
SQL > select ecode , count(pcode) from products groupby(ecode) having avg(price)<100;
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.
sql>SELECT COUNT(age), age FROM emp GROUP BY age ;
No comments:
Post a Comment