Real Time Scenarios in SQL Queries :

In my previous articles i have given the proper idea about the complex sql queries and complex sql interview questions.This article gives you idea about different Real Time Scenarios in SQL Queries which contains simple SQL queries as well as complex sql queries. I have consolidated the different queries from my website so that user will get idea about different Real Time Scenarios in SQL Queries.Everyone have always question in mind that what will be different Real Time Scenarios  in SQL Queries? You will find the answer of this query in this article.

Real Time Scenarios in SQL :

Scenario 1 :  What is Query to find Second highest salary for employee?

This is most asked Real Time Scenarios in SQL in many industries. There are lot of real time situation where user needs to deal with this kind of situation. User will try multiple queries to find out the same result.

Query 1 :

Select distinct Salary from Employee e1 where 2=Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary;

Query 2:

select min(salary)from(select distinct salary from emp order by salary desc)where rownum<=2;

Query 3:

select * from(Select S.*,DENSE_RANK() OVER (PARTITION BY DNO ORDER BY SALARY DESC) DR from Source) S Where S.DR=2;

Scenario 2 : Fetching Nth Record from the table.

There are some situations where user needs to find out the Nth records from the table. I will divide this scenario in to 3 parts for better understanding of people.

Query 1 :  Query to find First Record from the table.

 Select * from Employee where Rownum =1;

Query 2: Query to find last record from the table.

Select * from Employee where Rowid= select max(Rowid) from Employee;

Query 3 : Query to find Nth Record from the table.

select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE — including the order by ) a where rownum <= N_ROWS ) where rnum >= N_ROWS;

Scenario 3 : Find and delete duplicate rows

There are real world situations where user needs to find and delete duplicate rows from the table. These are most used SQL queries in real world to find the duplicate rows and delete it. When there is a situation where user needs to add unique constraint to column,user needs to delete duplicate rows.

Query 1 :  Query to find duplicate rows.

 select a.* from Employee a where rowid != 
         (select max(rowid) from Employee b where  a.Employee_num =b.Employee_num;

Query 2: Query to delete duplicate rows

Delete from Employee a where rowid !=  (select max(rowid) from Employee b where  a.Employee_num =b.Employee_num;

Scenario 4 : Find a table specific information

There are times where user needs to find out the table specific information. There are so many system tables which will find a table specific information.

Query 1: How to Find table name and its owner?

Make sure that the database user have logged in with SYS user.

Select table_name,Owner from All_tables order by table_name,owner;

Query2:How to find Selected Tables from a User?

SELECT Table_Name FROM User_Tables WHERE Table_Name LIKE ‘STU%’;

Scenario 5 : Find the constraint information

There are so many scenarios in real world that user needs to find out the constraint information.There are so  many constraints used to make the database normalized.The following are some important queries which will gives us the information about the oracle constraints.

Query 1 : How to find all details about Constraints?

SELECT * From User_Constraints;

SELECT * FROM User_Cons_Columns;

Query 2: How to find Constraint Name?

SELECT Table_Name, Constraint_Name FROM User_Constraints;

Query 3: How to find Constraint Name with Column_Name?

SELECT Column_Name, Table_Name, Constraint_Name FROM User_Cons_Columns;

Query 4: How to find Selected Tables which have Constraint?

SELECT Table_Name FROM User_Cons_Columns WHERE Table_Name LIKE ‘STU%’;

Query 5: How to find Constraint_Name, Constraint_Type, Table_Name?

SELECT Table_Name, Constraint_Type, Constraint_Name FROM User_Constraints;

SELECT Table_Name, Constraint_Type, Constraint_Name, Generated FROM User_Constraints;

Scenario 6: How to create a table which has same structure  or how to create duplicate table.

There are so many situations where user needs to create duplicate tables for testing purpose. There are some needs where user needs to create the structure of the table. The following are 2 most important queries which are used in 90% of Real Time Scenarios in SQL.

Query 1: Create the duplicate table with data

Create table Employee_1 as Select * from Employee;

Query 2: Create the table structure duplicate to another table.

Create table Employee_1 as Select * from Employee where 1=2;

Scenario 7 : Finding the procedures information

There are situations in Real Time Scenarios of SQL where user needs to find out the procedures information.

Query 1 :How to check Procedures?

 SELECT * FROM User_Source

WHERE Type=’PROCEDURE’

AND NAME IN (‘SP_CONNECTED_AGG’,’SP_UNCONNECTED_AGG’);

Query 2:How to find procedure columns information?

select OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT from ALL_ARGUMENTS order by OWNER, OBJECT_NAME, SEQUENCE;

Scenario 8: Scenario of Self Join

We need to check the table which are joined with itself.There are the situations where user needs to join the table with itself. I will try to give one query which explains the scenario of self join.

Query : The query to find out the manager of employee

Select e.employee_name,m.employee name from Employee e,Employee m where e.Employee_id=m.Manager_id;

Scenario 9 : Email validation of SQL

There is need to add the email validation using SQL queries. These are also most common Real Time Scenarios in SQL.

Query : How to add the email validation using only one query?

User needs to use REGEXP_LIKE function for email validation.

 SELECT
Email
FROM
Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

 Scenario 10 : Find Database name

While working with multiple databases user needs to find out the details of databases using oracle system tables.Following are some Real Time Scenarios in SQL which are used to find out the name of database.

Query : How to find DB Name?

SELECT Ora_Database_Name FROM DUAL;

SELECT * FROM GLOBAL_NAME;

SELECT Name from V$DATABASE;

I have tried to explain 10 different Real Time Scenarios in SQL which will helpful to everyone.Hope you like this article on Real Time Scenarios in SQL.Please don’t forget to comment in comment section.