What are SQL Query execution steps with example?

What is SQL Query execution steps with examples?

In This article I will explain about all the different SQL Statements which are DDL,DML an DCL. The main focus is on SQL Select Statement execution. Every SQL statement has its own format and parser will execute every sql statement with specific order. First we will check about different SQL statements then we will try to decipher the idea of SQL Statement Execution Order. There are specific steps for SQL Query execution steps using which one can check the order of execution of the statement to apply the logic of business requirements accordingly.

1.DDL – Data Definition Language:

Command Description
CREATE Creates a new table, a view of a table, or other object in database
ALTER Modifies an existing database object, such as a table.
DROP Deletes an entire table, a view of a table or other object in the database.

2.DML – Data Manipulation Language:

Command Description
INSERT Creates a record
UPDATE Modifies records
DELETE Deletes records

3.DCL – Data Control Language:

Command Description
GRANT Gives a privilege to user
REVOKE Takes back privileges granted from user

4.DQL – Data Query Language:

Command Description
SELECT Retrieves certain records from one or more tables
  • SQL Select Statement : SQL Query execution steps  :

Select statement in SQL is used to fetch the records from the table. Here the main point is how that SQL Statement Execution order is done.To select the data from the database table SQL select statements are used.The actual result is stored in to Result table which is called as result-set which is stored in cache not in the main memory.We can fetch the records from the table by using  types:

SQL Query execution order

Select Syntax by using ‘*’:

Select * from Table_name;

Select Syntax by using column names:

Select column_name1,column_name2 from table_name;

As considering the performance matrices the column name syntax is better than select * syntax.

Real Life Scenarios for SQL Select Statement:

Select statement is most common and very widely used sql statement.So there should be millions of scenarios of Select statement.I am explaining the basic scenarios of select statement.

Consider following table named ‘Employee’:

Employee_num Employee_name Department ID Salary
1 Amit 100 680000
2 Rohan 100 550000
3 Rohit 101 430000

1.Scenario 1:

List the all information about Employee?

Query used:

Select * from Employee;

Select Employee_num,Employee_name,Department_ID,Salary from Employee;

The above 2 statement fetches all records of Employee.

2.Scenario 2:

Select the Employees whos salary is greater than 500000?

Query used:

Select * from Employee where salary > 500000;

Select Employee_num,Employee_name,Department_ID,Salary from Employee where salary>500000;

Output:

Employee_num Employee_name Department ID Salary
1 Amit 100 680000
2 Rohan 100 550000

3.Scenario 3:

Fetch the department-wise total salary.

Query:

Select Department ID,Sum(Salary) from Employee group by Department_ID;

Output:

Department ID Salary
100 1230000
101 430000

4.Scenario 4:

Select Departmentwise sum of salary where department_id is 100;

Query:

Select Department ID,Sum(Salary) from Employee group by Department_ID

having department_id=100;

Output:

Department ID Salary
100 1230000

What is SQL Query order of execution with real industry scenarios :

In this section i will try to give you idea about the SQL Statement Execution Order. There are some steps in that particular order the sql statement gets executed. So I will take one example here and will try to give you that SQL Statement Execution Order in depth.

Select Department ID,Sum(Salary) from Employee group by Department_ID

having department_id=100;

Step 1: From Clause Execution:

In Every select statement first step is execution of the statements in from clause.In above statement there is only one table so it directly takes the table.So in above statement first step optimizer or compiler points to table name Employee.

Step 2:Where Clause:

The second step is where clause.When there is no where clause in Select statement optimizer directly fetches all the results from specified table.Where clause filters the data in database.In above query there is no where condition so Optimizer directly takes all the records from the table.

Step 3:Group By clause

The Step 3 is Group by clause.If the query contains group by clause then the specified result of Query is partitioned in to different groups.One group is every combination of values given in to group by clause.In above query we are doing grouping the department id wise.

Step 4:Having Clause

If your Query contains having clause then it will executes after the group by clause. After grouping if user wants to filter the records then having conditions comes in picture.In our query we are taking sum of records having department id=100.

Step 5:Aggregate functions and select columns:

After fetching all the records and executing the clauses the columns which are taken in select statement gets executed.The functions like max,min,count,sum is executed first and then all columns taken in select statement gets executed last.

SQL Query execution stepsHope everyone gets idea about select statement and SQL Statement Execution Order. Please comment below if you have any queries or if you like article.

8 Replies to “What are SQL Query execution steps with example?”

  1. In Select statement, we are having Where and Having cluase, both where and having filters the data then what is the difference between them?

    1. you cannot use the aggragate function(count,min,max,sum) with WHERE clauses but can be used with having clauses

  2. Can you provide execution flow of nested queries with joins . i mean complex queries? this article is awesome.

  3. Hi Amit,
    can you please send complete SQL queries interview questions and answers to my email id please and appreciate your help and support
    Regards
    Naga

Comments are closed.