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.
Let us now view our demo database – DataFlair.
SELECT * FROM DataFlair;
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.
SELECT * FROM tableName WHERE condition;
Example: Let us now find the employees with an experience of more than 5 years.
SELECT * FROM DataFlair WHERE experience > 5;
2. SELECT Clause in SQL
Select clause is used to query the database and display the output.
SELECT col1 , col2 , col3…. FROM tableName;
Example: Let us now view our database using the Select clause.
SELECT * FROM DataFlair;
3. UPDATE Clause in SQL
Allows us to update the records present in our database.
UPDATE tableName SET col1 = val1, col2 = val2...., WHERE condition;
Example: Let us now update the experience of Aman to 10 years.
UPDATE DataFlair SET experience = 10 WHERE name = 'Aman'; SELECT * FROM DataFlair
4. INSERT Clause in SQL
With the help of the insert clause, we insert data into our database.
INSERT INTO tableName VALUES (val1,val2,val3,.......);
Example: Let us insert the employee Raj into the database – DataFlair.
INSERT INTO dataflair VALUES ('B35','Raj','Indore',7); SELECT * FROM DataFlair;
5. DELETE Clause in SQL
With the delete clause, we delete the existing records from the table.
DELETE FROM table_name WHERE condition ;
Example: Let us now delete the employee corresponding to the Id – X82.
DELETE FROM dataflair WHERE emp_id = 'X82' ; SELECT * FROM DataFlair
6. UNION Clause in SQL
Helps us to combine the data from different tables in one single query.
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.
SELECT emp_id AS ID FROM DataFlair UNION SELECT location_id FROM location;
7. GROUP By Clause in SQL
Group By clause is used with the select statement to get similar data in manageable groups.
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.
SELECT Count(emp_id) as ID, location FROM DataFlair GROUP BY location;
8. ORDER By Clause in SQL
Allows us to explicitly decide the order of the output of our query.
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.
SELECT emp_id AS ID, name, location FROM DataFlair ORDER BY emp_id ASC;
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.
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.
SELECT count(emp_id),location FROM DataFlair GROUP BY location HAVING count(emp_id)>5 ORDER BY emp_id ASC;
10. LIMIT Clause in SQL
Helps us to limit the number of results from the query we run on our database.
SELECT * FROM tableName LIMIT n;
Example: Let us now view the first five records from our DataFlair database.
SELECT emp_id as ID, name FROM DataFlair LIMIT 5;
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.
SELECT col1,col2,col3,..... FROM tableName WHERE col1 LIKE expression;
Example: Let us now find the employees whose name starts with ‘A’.
SELECT * FROM DataFlair WHERE name LIKE 'A%';
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.
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.
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;
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.