Real Time Scenarios in SQL Queries | SQL Scenarios

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.

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;

Real Time Scenarios in SQL

 

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.

 

73 Replies to “Real Time Scenarios in SQL Queries | SQL Scenarios”

  1. You are great sir…. The article is being a great help for me… Can you share more articles on PL/SQL cursor and trigger, please?

  2. Hi Amit,

    I came to know about GTT in PLSQL to overcome the mutating trigger problem. (Instead of using compound trigger)

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

    What does the below indicate in this query

    Where 2=

    1. Hi Suganya,

      The 2 indicates distinct value of salary which will fetch second highest salary..

      Kindly execute the query.

      Regards and thanks,
      Amit Shiravadekar

  4. Hi Sir ,
    I have SQL interview scheduled for next week. Can you send me set of SQL questions for advance level.
    Mostly scenario based

          1. i am looking for a change in etl testing and looking for a good set of sql queries questions . It would be very great and thankful if you can help , suggest and send me good set of questions.

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

    Will it give you correct result if we have employee with same figure ?

  6. Hi Sir ,
    I have SQL interview scheduled for next week. Can you send me set of SQL questions for advance level.
    Mostly scenario based

  7. Hey Amit! thanks for sharing these, its been helpful. could you share me some interview questions on complex sql queries as well? I have an interview this week.

  8. All sets of question is very good.
    Sir this weekend is interview and I am 2.5 years experience in PL/SQL.. Can you send me interview Question??

  9. Hi Amit,
    Can you please send me some basic scenario based SQL questions and answers,.Would really appreciate if you can send asap.I have an interview.

    Thanks in advance,
    Aditya

  10. Thanks Amit for sharing very useful scenarios. Really helpful. Could you please share scenario based /real time SQL interview questions along with answers. Thanks in advance.

Leave a Reply

Your email address will not be published. Required fields are marked *