SQL Clauses and Its Types – Syntax and Example (Part-2)

In this tutorial, we will understand the SQL Clauses and learn about them in detail.

In the previous articles, we have understood in detail specifically about some of the clauses.

Here, we aim to build the base on what the clauses are and how we should go around using them in our queries.

Let us now dive into our SQL Clause tutorial.

Stay updated with latest technology trends
Join DataFlair on Telegram!!

What are Clauses in SQL?

Clauses in SQL are similar to conditionals in high-level languages.

SQL being a query language requires a method to apply constraints on the data and for this we use Clauses.

We have a large variety in the SQL clauses like the Where clause, Union Clase, Order By clause etc.

Clauses help us to restrict and manage the data using valid constraints on the data in our database.

Use of SQL Clause

Some of the uses of SQL Clauses are as follows:

1. Allow us to apply constraints on data.

2. Help us to reduce the complexity of the query.

3. With the help of clauses, we can filter the data according to our requirements.

4. Allow us to restrict the number of outputs we need.

5. Help in writing user-friendly queries, which are easy to read and understand.

Demo Database

Let us now view our demo database – DataFlair.
Query: 

SELECT * FROM DataFlair;

Output:

SQL Clauses Demo Database

Types of SQL Clause

We have a variety of SQL clauses available. Let us now discuss each of them in detail.

1. WHERE Clause in SQL

It is an integral part of any query and allows us to apply constraints on the query output.
Syntax:

SELECT * FROM tableName WHERE condition;

Example: Let us now find the employees with an experience of more than 5 years.
Query:

SELECT * FROM DataFlair WHERE experience > 5;

Output:

Where clause in SQL

2. SELECT Clause in SQL

Select clause is used to query the database and display the output.
Syntax:

SELECT col1 , col2 , col3….
FROM tableName;

Example: Let us now view our database using the Select clause.
Query:

SELECT * FROM DataFlair;

Output:

Select Clause in SQL

3. UPDATE Clause in SQL

Allows us to update the records present in our database.
Syntax:

UPDATE tableName
SET col1 = val1, col2 = val2...., 
WHERE condition;

Example: Let us now update the experience of Aman to 10 years.
Query:

UPDATE DataFlair
SET experience = 10
WHERE name = 'Aman';
SELECT * FROM DataFlair

Output:

Update Clause in SQL

4. INSERT Clause in SQL

With the help of the insert clause, we insert data into our database.
Syntax:

INSERT INTO tableName VALUES (val1,val2,val3,.......);

Example: Let us insert the employee Raj into the database – DataFlair.
Query:

INSERT INTO dataflair VALUES ('B35','Raj','Indore',7);
SELECT * FROM DataFlair;

Output:

Insert Clause in SQL

5. DELETE Clause in SQL

With the delete clause, we delete the existing records from the table.
Syntax:

DELETE FROM table_name
WHERE condition ;

Example: Let us now delete the employee corresponding to the Id – X82.
Query:

DELETE FROM dataflair
WHERE emp_id = 'X82' ;
SELECT * FROM DataFlair

Output:

Delete Clause in SQL

6. UNION Clause in SQL

Helps us to combine the data from different tables in one single query.
Syntax:

SELECT colName FROM tableName1
UNION
SELECT colName FROM tableName2;

Example: Let us now view the employee ID and the location ID from the DataFlair database.
Query:

SELECT emp_id AS ID FROM DataFlair
UNION
SELECT location_id FROM location;

Output:

Union Clause in SQL

7. GROUP By Clause in SQL

Group By clause is used with the select statement to get similar data in manageable groups.
Syntax:

SELECT col1, col2, 
col3 , .....
FROM tableName
WHERE conditions 
GROUP BY col1, col2;

Example: Let us find the number of employees at each location of DataFlair’s office.
Query:

SELECT Count(emp_id) as ID, location 
FROM DataFlair
GROUP BY location;

Output:

Group By Clause in SQL

8. ORDER By Clause in SQL

Allows us to explicitly decide the order of the output of our query.
Syntax:

SELECT col1, col2, 
col3 , .....
FROM tableName
WHERE conditions 
GROUP BY col1, col2
ORDER BY col1, col2;

Example: Let us now view our employees based on the ascending order of the employee ID.
Query:

SELECT emp_id AS ID, name,
location 
FROM DataFlair
ORDER BY emp_id ASC;

Output:

Order By Clause in SQL

9. HAVING Clause in SQL

It comes in handy when we need to apply aggregate functions as we can’t use aggregate functions with the WHERE clause.
Syntax:

SELECT col1 , col2 , col3,.....
FROM tableName
WHERE condition
GROUP BY col1
HAVING condition
ORDER BY col1;

Example: Let us find the city having more than 5 employees in our DataFlair database.
Query:

SELECT count(emp_id),location
FROM DataFlair
GROUP BY location
HAVING count(emp_id)>5
ORDER BY emp_id ASC;

Output:

Having Clause in SQL

10. LIMIT Clause in SQL

Helps us to limit the number of results from the query we run on our database.
Syntax:

SELECT * FROM tableName
LIMIT n;

Example: Let us now view the first five records from our DataFlair database.
Query:

SELECT emp_id as ID, name 
FROM DataFlair
LIMIT 5;

Output:

Limit Clause in SQL

11. LIKE Clause in SQL

With the use of Like clause, we can get the output’s which match the exact expressions or patterns as required by us.
Syntax:

SELECT col1,col2,col3,.....
FROM tableName
WHERE col1 LIKE expression;

Example: Let us now find the employees whose name starts with ‘A’.
Query:

SELECT *
FROM DataFlair
WHERE name LIKE 'A%';

Output:

Like Clause in SQL

12. JOIN Clause in SQL

Used to combine the data from multiple tables into single queries by using one or more columns to match the data.
Syntax:

SELECT tableName1.col1, tableName1.col2, tableName2.col1,....  
FROM tableName1   
LEFT JOIN tableName2  
ON tableName1.col1 = tableName2.col2;  

Example: Let us now view the details of employees and their locations with the location ID from the location table.
Query:

SELECT emp_id , name,
DataFlair.location AS Location , 
location.location_id AS Location_ID FROM DataFlair
LEFT JOIN location ON DataFlair.location = location.location
ORDER BY location.location_id ASC;

Output:

Join Clause in SQL

Summary

In this tutorial, we have seen what clauses are and why do we need clauses in SQL.

Moving further, we have discussed each clause in detail with the syntax and examples of each.

Did you like our efforts? If Yes, please give DataFlair 5 Stars on Google | Facebook

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.