Different Clauses in SQL With Example

Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners. Also, if you are interested in content writing, you can mail us at tutorialwing@gmail.com.

In this post, we will learn about Different clauses in SQL. We will learn about below clauses in SQL –

  1. WHERE Clause
  2. ORDER BY Clause
  3. GROUP BY Clause
  4. HAVING Clause
  5. UNION Clause
  6. LIMIT Clause

1. WHERE Clause in SQL

If you want to fetch results based on some condition then we make use of WHERE clause. This is one of my most used clauses when you fetch results.

Example 1

If you want to fetch the employee whose salary is less than 50000.

mysql> select eid, fname, lname, salary from employee where salary < 50000

Result

eid fname Iname salary
101 Neelam Padma 25000.00
102 Alok Pawar 15000.00
103 Sharada Jadhao 35000.00
104 Vaishnavi Munigial 10000.00

4 rows in set (0.00 sec)

Example 2
mysql> select eid, fname, lname, salary from employee where eid < 101

Result

eid fname Iname salary
101 Padma Padma 25000.00

1 row in set (0.03 sec)

2. ORDER BY Clause in SQL

Query results can be sorted by a specific field (or fields ) using the ORDER BY clause in the SELECT statement. The sorting can be in ascending or descending order. Ascending is the default order.

Example 1

Sorting in ascending order by varchar data type.

mysql> SELECT fname, lname from employee ORDER BY fname;

Result

fname Iname
Abhishek Agarwal
Alok Pawar
Ashwini kakde
Neelam Padma
sharada Jadha0
sharada Patil
Vaishnavi Munigial

7 rows in set (0.00 sec)

Example 2

Sorting in ascending order by date data type.

mysql> SELECT fname, lname, dob from employee ORDER BY dob;

Result

fname Iname Dob
Ashwini kakde 1955-04-19
Vaishnavi Munigial 1963-12-09
Sharada Patil 1972-01-18
Sharada Jadhao 1974-11-13
Neelam Padma 1982-02-10
Abhishek Agarwal 1982-02-02
Alok Pawar 1985-12-20

7 rows in set (0.00 sec)

Example 3

Sorting in descending order;

mysql> SELECT fname, lname from employee ORDER BY fname desc;

Result

fname Iname
Vaishnavi Munigial
sharada Jadhao
Sharada Patil
Neelam Padma
Ashwini kakde
Alok Pawar
Abhishek Agarwal

7 rows in set (0.00 sec)

3. GROUP BY clause

This clause is used with SELECT statement. It groups rows based on distinct values that exist for specified columns. It creates a dataset containing several sets of records grouped together based on a condition.

Example 1
mysql> SELECT sex from employee group by sex;

Result

sex
F
M

2 rows in set (0.00 sec)

Example 2

Count total of male and female employees;

mysql> SELECT sex, count(*) from employee group by sex;

Result

sex count(*)
F 5
M 2

2 rows in set (0.00 sec)

4. HAVING clause

This clause is used in conjunction with the GROUP BY clause. HAVING imposes a condition on the GROUP BY clause, which further filters the groups created by the GROUP BY clause. Each column specification specified in the HAVING clause must occur within a statistical function or must occur in the list of columns named in the GROUP BY clause.

Example 1
mysql> SELECT sex, count(*) from employee group by sex having sex= 'F'

Result

sex Count(*)
F 5

1 row in set (0.04 sec)

Example 2

Same above example but by using alias to the column name.

mysql> SELECT sex as gender, count(*) from employee group by sex having gender= 'F'

Result

gender count(*)
F 5

1 row in set (0.04 sec)

5. UNION clause in SQL

In union clause we combine the results of two select queries. But when performing a union of both SQL queries we must ensure that the columns in both the queries are same. UNION clause will actually remove the duplicate rows.

Example 1

Let’s say we have two tables’ suppliers and orders.

Supplier

SupplierId Suppliername
1 Reebok
2 Adidas
3 Nike

Orders

orderId orderdate SupplierID
1 05-02-2019 2
2 11-01-2019 3
3 19-01-2019 8
SELECT supplierID FROM suppliers
UNION
SELECT supplierID FROM orders

Result

SupplierID
1
2
3
8

6. LIMIT clause in SQL

Using Limits, we can specify the number of records to be displayed.

Example 1

In the below example, 4 specifies the number of records to be displayed.

mysql> SELECT * from employee limit 4;

Result

eid fname Iname DOB address sex Salary dnum
101 Neelam Padma 1982-02-10 Dhayari, Pune F 25000.00 3
102 Alok Pawar 1985-12-20 Balaji nagar,Pune M 15000.00 2
103 Sharada Jadhao 1974-11-13 Shivaji nagar, Aurangabad F 35000.00 1
104 Vaishnavi Munigial 1963-12-09 VIP road, Nanded F 1000.00 4

4 rows in set (0.00 sec)

Example 2

In the below example, 2 specifies the starting record number from where the records are to be displayed starting from record 0 and specifies number of records to be displayed.

mysql> SELECT * from employee limit 2, 3;

Result

eid fname Iname DOB address sex Salary dnum
103 Sharada Jadhao 1974-11-13 Shivaji nagar, Aurangabad F 35000.00 1
104 Vaishnavi Munigial 1963-12-09 VIP  road,  Nanded F 10000.00 4
105 Ashwini Kakde 1955-04–19 pimpri station, Pimpri F 20000.00 3

3 rows in set (0.00 sec)

That’s end of our post on Different clauses in SQL

Support Us

If you like Tutorialwing and would like to contribute, you can email an article on any educational topic at tutorialwing@gmail.com. We would love to publish your article. See your article on Tutorialwing and help others with your knowledge. Follow Facebook, LinkedIn, Google+, Twitter, Youtube for latest updates.
Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners