Clause In SQL
Clause In SQL 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.
- Clause are use to add a filtration while retrieving rows/data from table.
- Operators are mostly use to write a condition on the data.
- Some of the commonly used clauses
Where Clause
In the where clause you can write a conditions on the column so that only a specific records will be selected. These is one of the way using which you can filter the records from the database.
Like Clause
- In this clause you can get the record based on partial values.
- ‘%’ in this query denotes any match.
- Example:
select * from employee where contact like ‘99%’;
select * from employee where contact like ‘%8877%’;
select * from employee where contact like ‘%8877’;
Limit clause
- Limit clause is use to limit the number of rows getting selected by query.
- Example:
select * from employee limit 5;
Order by Clause
- You can select record in the order.
- Records can be selected by Ascending (asc) and Descending (desc) order.
- Example
select * from employee ORDER BY salary desc;select * from employee ORDER BY salary desc;select * from employee ORDER BY salary,name desc;select * from employee ORDER BY salary desc, name asc;
Distinct clause
- You can select only the unique values for a specific column
- Example:
SELECT DISTINCT salary FROM employee;SELECT DISTINCT gender FROM employee;
Group By Clause
- Is use to group the records using the functionality.
- Group by clause is mostly used with an aggregate functions.
- Group By Cluse is use with select query.
- Syntax:
SELECT column, function(column) from Table where Condition GROUP BY column
Having Clause
- Is use to apply the condition on the group records.
- This has to apply with the GROUP By clause.
- Syntax:
SELECT column, function(column) from Table where ConditionGROUP BY column HAVING condition
Operators In SQl
AND, OR operator
- These operators are use to combine multiple condition together.
Between operator
- Is use to select a record which is in the range of two values.
- Syntax:
SELECT * FROM <TableName> where column BETWEEN value1 AND value2; - Example :
select * from employee where salary BETWEEN 1000000 and 2000000;
In Operator
- Is use to select a records base on the list of values
- Example:
select * from employee where name in (‘A’,’B’,’C’,’D’);
Not Operator
- Is use to reverse the condition.
- Example:
select * from employee where NOT isActive = 0;
Some Example:-
mysql> select count(isActive) as count, sum(salary), isActive from employee group by isActive;
+——-+————–+———-+
| count | sum(salary) | isActive |
+——-+————–+———-+
| 6 | 11229473.795 | 1 |
| 3 | 5808510.176 | 0 |
+——-+————–+———-+
2 rows in set (0.00 sec)
mysql> select count(isActive) as count, sum(salary), isActive from employee group by isActive HAVING count>5;
+——-+————–+———-+
| count | sum(salary) | isActive |
+——-+————–+———-+
| 6 | 11229473.795 | 1 |
+——-+————–+———-+
1 row in set (0.00 sec)
mysql> select * from employee where isActive=1;
+—-+——+————+————-+——–+———-+————+
| id | name | contact | salary | gender | isActive | doj |
+—-+——+————+————-+——–+———-+————+
| 1 | A | 9988778787 | 3436987.873 | M | 1 | 2022-01-12 |
| 3 | C | 9943124432 | 1236987.873 | F | 1 | 2022-03-12 |
| 4 | D | 9913124432 | 1676987.873 | M | 1 | 2021-03-15 |
| 6 | F | 8923224432 | 2006987.873 | M | 1 | 2002-05-10 |
| 8 | H | 6723224432 | 1346987.873 | M | 1 | 2017-02-11 |
+—-+——+————+————-+——–+———-+————+
5 rows in set (0.01 sec)
mysql> select * from employee where isActive=1 AND gender=’M’;
+—-+——+————+————-+——–+———-+————+
| id | name | contact | salary | gender | isActive | doj |
+—-+——+————+————-+——–+———-+————+
| 1 | A | 9988778787 | 3436987.873 | M | 1 | 2022-01-12 |
| 4 | D | 9913124432 | 1676987.873 | M | 1 | 2021-03-15 |
| 6 | F | 8923224432 | 2006987.873 | M | 1 | 2002-05-10 |
| 8 | H | 6723224432 | 1346987.873 | M | 1 | 2017-02-11 |
+—-+——+————+————-+——–+———-+————+
4 rows in set (0.00 sec)
mysql> select * from employee where isActive=1 AND gender=’F’;
+—-+——+————+————-+——–+———-+————+
| id | name | contact | salary | gender | isActive | doj |
+—-+——+————+————-+——–+———-+————+
| 3 | C | 9943124432 | 1236987.873 | F | 1 | 2022-03-12 |
+—-+——+————+————-+——–+———-+————+
1 row in set (0.00 sec)
mysql> select * from employee;
+—-+——+————+————-+——–+———-+————+
| id | name | contact | salary | gender | isActive | doj |
+—-+——+————+————-+——–+———-+————+
| 1 | A | 9988778787 | 3436987.873 | M | 1 | 2022-01-12 |
| 3 | C | 9943124432 | 1236987.873 | F | 1 | 2022-03-12 |
| 4 | D | 9913124432 | 1676987.873 | M | 1 | 2021-03-15 |
| 5 | E | 7713124432 | 1676987.873 | M | 0 | NULL |
| 6 | F | 8923224432 | 2006987.873 | M | 1 | 2002-05-10 |
| 7 | G | 7713224432 | 3106987.873 | F | 0 | 2012-01-22 |
| 8 | H | 6723224432 | 1346987.873 | M | 1 | 2017-02-11 |
+—-+——+————+————-+——–+———-+————+
7 rows in set (0.00 sec)
mysql> select * from employee where salary>1000000 AND salary<2000000;
+—-+——+————+————-+——–+———-+————+
| id | name | contact | salary | gender | isActive | doj |
+—-+——+————+————-+——–+———-+————+
| 3 | C | 9943124432 | 1236987.873 | F | 1 | 2022-03-12 |
| 4 | D | 9913124432 | 1676987.873 | M | 1 | 2021-03-15 |
| 5 | E | 7713124432 | 1676987.873 | M | 0 | NULL |
| 8 | H | 6723224432 | 1346987.873 | M | 1 | 2017-02-11 |
+—-+——+————+————-+——–+———-+————+
4 rows in set (0.00 sec)
Join Telegram : Click Here
All Full Stack Java Study Material
Job’s For Fresher