SQL Statements | SQL Statement Execution Order

Execution of Select Statement
  • SQL Statement Execution Order in Detail :

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 Statement Execution Order 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  :

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 Statement 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 departmentwise 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

SQL Statement Execution Order :

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 funnctions like max,min,count,sum is executed first and then all columns taken in select statement gets executed last.

Execution of Select StatementHope everyone gets idea about select statement and SQL Statement Execution Order.Please comment below if you have any queries or if you like article.

About admin 136 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development

9 Trackbacks / Pingbacks

  1. Query to find second highest salary of employee? | How to find second highest salary of employee?
  2. SQL Interview Questions for Tech Mahindra | Important SQL Interview Questions
  3. Interview Questions For Oracle | SQL interview Questions
  4. Basic Unix Commands | Frequently Used Unix Commands | Unix Commands with Examples | Useful Unix Commands
  5. Chmod command in unix | Unix File Permissions | chmod with examples | Chwn command | Chgrp command | UNMASK
  6. Database Normalization Examples | Normalization in Database with Examples | Database Normalization with Real Life Examples | Designing Normalized Database
  7. Difference between Truncate and Delete, Drop in SQL | Truncate Delete and Drop Commands in SQL | SQL Difference between Truncate,Delete and Drop Commands | Real Life Examples of Truncate Delete and Drop Command
  8. Interview Questions for TCS | SQL Interview Questions for TCS | Interview Questions Asked in TCS | TCS Interview Questions
  9. sql create table with primary key

Leave a Reply

Your email address will not be published.


*