Sql Clause

WHERE clause

Where clause is used to specify condition while retrieving data from table. Where clause is used mostly with Select, Update and Delete
query. If condition specified by where clause is true then only the result from table is returned.



Syntax for WHERE clause

SELECT column-name1, 
 column-name2, 
 column-name3, 
 column-nameN 
from table-name WHERE [condition];

Example using WHERE clause

Consider a Student table

s_id s_Name age address
101 John 16 Noida
102 Alam 18 Delhi
103 Abhi 17 Rohtak
104 Ankit 22 Panipat

Now we will use a SELECT statement to display data of the table, based on a condition, which we will add to the SELECT query using WHERE clause.

SELECT s_id, s_name,age,address from Student WHERE s_id=101; 

s_id s_Name age address
101 John 16 Noida

Like clause

Like clause is used as condition in SQL query. Like clause compares data with an expression using wildcard operators. It is used to find similar data from the table.


Wildcard operators

There are two wildcard operators that are used in like clause.

  • Percent sign % : represents zero, one or more than one character.
  • Underscore sign _ : represents only one character.

Example of LIKE clause

Consider the following Student table.

s_id s_Name age
101 John 16
102 Alam 18
103 Abhi 17
SELECT * from Student where s_name like = 'A%';

The above query will return all records where s_name starts with character ‘A’.

s_id s_Name age
101 John 16
102 Alam 18
103 Abhi 17

Example

SELECT * from Student where s_name like = '_d%';

The above query will return all records from Student table where s_name contain ‘d’ as second character.

s_id s_Name age
101 John 16

Example

SELECT * from Student where s_name like = '%x';

The above query will return all records from Student table where s_name contain ‘x’ as last character.

s_id s_Name age
102 Alam 18


Order By Clause

Order by clause is used with Select statement for arranging retrieved data in sorted order. The Order by clause by default sort data in ascending order. To sort data in descending order DESC keyword is used with Order by clause.


Syntax of Order By

SELECT column-list|* from table-name order by asc|desc


Example using Order by

Consider the following emp table

eid name age salary
401 Anu 22 9000
402 Shane 29 8000
403 Rohan 34 6000
404 Scott 44 10000
405 Tiger 35 8000
SELECT * from emp order by salary;

The above query will return result in ascending order of the salary.

eid name age salary
403 Rohan 34 6000
402 Shane 29 8000
405 Tiger 35 8000
401 Anu 22 9000
404 Scott 44 10000

Example of Order by DESC

Consider the emp table described above

SELECT * from emp order by salary DESC;

The above query will return result in descending order of the salary.

eid name age salary
404 Scott 44 10000
401 Anu 22 9000
405 Tiger 35 8000
402 Shane 29 8000
403 Rohan 34 6000

Order By Clause

Order by clause is used with Select statement for arranging retrieved data in sorted order. The Order by clause by default sort data in ascending order. To sort data in descending order DESC keyword is used with Order by clause.


Syntax of Order By

SELECT column-list|* from table-name order by asc|desc; 

Example using Order by

Consider the following emp table

eid name age salary
401 Anu 22 9000
402 Shane 29 8000
403 Rohan 34 6000
404 Scott 44 10000
405 Tiger 35 8000
SELECT * from emp order by salary; 

The above query will return result in ascending order of the salary.

eid name age salary
403 Rohan 34 6000
402 Shane 29 8000
405 Tiger 35 8000
401 Anu 22 9000
404 Scott 44 10000

Example of Order by DESC

Consider the emp table described above

SELECT * from emp order by salary DESC;

The above query will return result in descending order of the salary.

eid name age salary
404 Scott 44 10000
401 Anu 22 9000
405 Tiger 35 8000
402 Shane 29 8000
403 Rohan 34 6000

Group By Clause

Group by clause is used to group the results of a SELECT query based on one or more columns. It is also used with SQL functions to group the result from one or more tables.

Syntax for using Group by in a statement.

SELECT column_name, function(column_name)
FROM table_name 
WHERE condition 
GROUP BY column_name

Example of Group by in a Statement

Consider the following emp table.

eid name age salary
401 Anu 22 9000
402 Shane 29 8000
403 Rohan 34 6000
404 Scott 44 9000
405 Tiger 35 8000

Here we want to find name and age of employees grouped by their salaries

SQL query for the above requirement will be,

SELECT name, age from Emp group by salary;

Result will be

name age
Rohan 34
shane 29
anu 22

Example of Group by in a Statement with WHERE clause

Consider the following emp table

eid name age salary
401 Anu 22 9000
402 Shane 29 8000
403 Rohan 34 6000
404 Scott 44 9000
405 Tiger 35 8000

SQL query will be,

select name, salary from Emp where age > 25group by salary;

Result will be.

name salary
Rohan 6000
Shane 8000
Scott 9000

You must remember that Group By clause will always come at the end, just like the Order by clause.


HAVING Clause

having clause is used with SQL Queries to give more precise condition for a statement. It is used to mention condition in Group based SQL functions, just like WHERE clause.

Syntax for having will be,

select column_name, function(column_name)
FROM table_name
WHERE column_name condition
GROUP BY column_name
HAVING function(column_name) condition

Example of HAVING Statement

Consider the following Sale table.

oid order_name previous_balance customer
11 ord1 2000 Alam
12 ord2 1000 John
13 ord3 2000 Abhi
14 ord4 1000 John
15 ord5 2000 Alam

Suppose we want to find the customer whose previous_balance sum is more than 3000.

We will use the below SQL query

SELECT *from sale group customer having sum(previous_balance) > 3000;

Result will be

oid order_name previous_balance customer
11 ord1 2000 Alam

SELECT Query

Select query is used to retrieve data from a tables. It is the most used SQL query. We can retrieve complete tables, or partial by mentioning conditions using WHERE clause.


Syntax of SELECT Query

SELECT column-name1, column-name2, column-name3, column-nameN from table-name; 

Example for SELECT Query

Conside the following Student table

S_id S_Name age address
101 John 16 Noida
102 Alam 18 Delhi
103 Abhi 17 Rohtak
104 Ankit 22 Panipat
SELECT s_id, s_name, age from Student;

The above query will fetch information of s_id, s_name and age column from Student table

S_id S_Name age
101 John 16
102 Alam 18
103 Abhi 17
104 Ankit 22

Example to Select all Records from Table

A special character asterisk * is used to address all the data(belonging to all columns) in a query. SELECT statement uses * character to retrieve all records from a table.

SELECT * from student; 

The above query will show all the records of Student table, that means it will show complete Student table as result.

S_id S_Name age address
101 John 16 Noida
102 Alam 18 Delhi
103 Abhi 17 Rohtak
104 Ankit 22 Panipat

Example to Select particular Record based on Condition

SELECT * from Student WHERE s_name = 'Abhi';
103 Abhi 17 Rohtak

Example to Perform Simple Calculations using Select Query

Conside the following Employee table.

eid Name age salary
101 John 16 5000
102 Ricky 42 8000
103 Abhi 22 10000
104 Rohan 35 5000
SELECT eid, name, salary+3000  from Employee;

The above command will display a new column in the result, showing 3000 added into existing salaries of the employees.

eid Name salary+3000
101 John 8000
102 Ricky 11000
103 Abhi 13000
104 Rohan 8000

Admin has written 171 articles

Leave a Reply