Real Time Scenarios in SQL Queries

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’);

How to Generate Permutations in SQL 

with mydata as (
    select 'A' mycol from dual
    union all
    select 'B' mycol from dual
    union all
    select 'C' mycol from dual
    union all
    select 'D' mycol from dual
    union all
    select 'E' mycol from dual
    union all
    select 'F' mycol from dual
    union all
    select 'G' mycol from dual
    union all
    select 'H' mycol from dual
)
select c1.mycol , c2.mycol
from mydata c1, mydata c2
where c1.mycol <> c2.mycol