How to Write SQL Queries with Simple Steps

In my previous articles i have provided information about different types of SQL Queries with multiple examples.In this article I would like to give you simple steps with real industry examples to Write SQL Queries. The article is about How to write SQL Queries with Simple Steps with examples. I will explain the real life industry examples. I would like to give you steps to write modular queries.This article gives you multiple steps to write queries according to Business Logic.

Writing SQL Queries Easy Steps :

In this section i would like to explain the simple steps to write SQL queries.User needs to understand simple steps to write SQL Queries using modular way.There are so many complex sql queries but if user thinks to write the query in modular way it will be easy for user to write the sql queries.I will explain you 2-3 business scenarios to write the query step by step.

Following are some Business Steps of Writing SQL Queries :

1.Analyse the Business Logic :

The first step is to analyse the Business Logic.

2.Fragment the Business Logic :

The second step is user needs to Fragment the Business logic and accordingly need to fragment logic.

3.Incremental Query :

User needs to write the incremental query according to the Fragmented business logic.

4.Functions needs to use in query :

User needs to check about the functions needs to use for implementing Business Logic.

5.Joining Conditions :

User needs to add the correct joining conditions whenever necessary.

6.Filter Conditions :

User needs to check the Filter conditions according to Business Logic and add that condition in the query.

These are above some different steps to write modular SQL Query. I would like to explain you different scenarios which are used in business logic to write SQL Queries.I would like to give you different Business Statements and will discuss about steps to write sql queries.

Before Checking about the scenarios i would like to explain SQL Query execution order in short words.

Database Interview Questions

SQL Query Execution Order :

Step 1 : From

Step 2 : Join

Step 3 : Where

Step 4 : Group By

Step 5 : Having

Step 6 : Select

Step 7 : Distinct

Step 8 : Order By

Tables Needs to be used to execute the Scenarios :

Employee Table :

Employee_numEmployee_nameDepartment IDSalary
1Andrew10000380000
2Ram10200850000
3Rajiv10100730000

There is another table called as Depatment which has following structure:

Department IDDepartment Name
10000OBIEE
10100Oracle PLSQL
10200COGNOS

Scenario 1 : I want to Fetch the Employees Who’s Name Start with R and Salary is greater than 700000.

Step 1 :

Analyzing Business Logic and Fragment it.So For Above My Fragmentation of logic is,

1.Fetching Employees from Employee Table.

2.Name Of Employee Like ‘R%’

3.Filter Condition is Salary which should be greater than 700000.

Step 2 : Query Building

Select * from Employee; –Step 1

Select * from Employee where Name like ‘R%’; — Step2

Select * from Employee Where Name like ‘R%’ and Salary > 700000;

Answer Of Query :

Select * from Employee Where Name like ‘R%’ and Salary > 700000;

Scenario 2 : I would like to select data of the Employee whose salary is maximum and with that i would like to show the department of that Employee as well.

Step 1 :Analyzing Business Logic of Query and Fragment it.

1.Fetching data from Employee as well as Department.

2.Use of Aggregate function to calculate maximum salaried Employee.

3.Use of Join (Employee table and Department table) to fetch department information as well.

4.Using the concept of Aliases to show the employee as well as department data.

Step 2 : Building of Query

1.Select * from Employee –Step 1

2.Select *,Max(Salary) from Employee; –Step 2

3.Select a.*,Max(a.salary) From Employee E; –Step 3 : Use of Aliases

4.Select a.Employee_Num,a.Employee_Name,,Max(a.Salary),B.Department_Name From Employee E,Department D where A.Department_Id=D.Department_ID;

Answer Of Query :

Select a.Employee_Num,

a.Employee_Name,

Max(a.Salary),

B.Department_Name

From Employee E,Department D where A.Department_Id=D.Department_ID group by B.Department_Name;

I am hoping that this article helps you to write the queries in modular way. If you have any questions or concerns kindly comment in to comments section.

2 Replies to “How to Write SQL Queries with Simple Steps”

  1. Select a.Employee_Num,

    a.Employee_Name,

    Max(a.Salary),

    B.Department_Name

    From Employee E,Department D where A.Department_Id=D.Department_ID; — This query is wrong…. produces error ORA-00937: not a single-group group function
    00937. 00000 – “not a single-group group function”.

    we can write it as SELECT TAB1.*, B.Department_Name FROM
    (SELECT * FROM employee_temp WHERE SALARY=(SELECT MAX(Salary) FROM employee_temp))TAB1,DEPATMENT B WHERE TAB1.DEPARTMENT_ID=B.Department_ID; — i didnt say the its the best way to write thiws query..but this is what i know

    1. Thanks Bharati for correcting me..You are abs right.. I will change it..I just wanted to specify the steps to write the queries that’s why I have shared this example which is bit complex.

Comments are closed.