SQL Complex Queries

Real Time Scenarios in SQL Queries | SQL Scenarios

Amit S April 30, 2018

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;

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.

250 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?

  3. Hi Amit,

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

  5. 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

  7. 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.

  9. great yaar, Please send me scenarion based examples. very clear explantion u have provided

  10. 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 ?

  12. great post.
    Can you please share some scenarios based query which is generally asked in Interviews

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

  14. i want to interview questions
    please send me
    it is my request
    my Email_Id:-siddarthkotekal@gmail.com

  15. 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.

  18. Can you share advance SQL interview question specially focusing on creating Organized data layer?

  19. 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??

  20. 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

  21. 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.

  34. Hi Amit,
    Your posts are very nice and helpful.
    I have 3 yrs exp in oracle plsql but still I’m into basic level kindly share me some interview questions and answers with real time example especially for packages procedures, functions, materialised views, views, gtt and exception handling.
    Thanks in advance
    Mail id : srirambhoopalan@gmail.com

  35. Can you also please share those advanced level sql interview questions to my mailid? Thanks a lot for the knowledge sharing,

  36. Hello Sir,
    Thank you Sir for this excellent article, may I request you for the interview questions advanced level.

  41. Hi Amit.

    I have SQL based interview next week. Can you send me interview questions for real time complex scenarios. Also I am interviewing for a Health insurance company. If you have anything related to health care analysis using SQL, that would be very helpful.
    email- secreteyes02@gmail.com

  45. Hi Amit

    It is really helpful for me. Could you please send more sql scenario question to my email id :- firozoracle1992@gmail.com
    I am looking for job change as application support or production support. If you have Linux and UNIX real time scenario interview questions then plzzzzz share on my email id.

    I will be thankful always.
    Firoz khan

  48. Thanks Amit !! The article was of great use. Looking for more scenario based on complex SQL queries.

  52. Hi,
    Your scenarios are of great help.
    I am planning for a switch as Orcale PL/SQL developer.
    Can you please help me with the interview questions both theoretical and real time scenarios that they can ask.

  53. Hi Amit,

    Can you please share the scenario based oracle SQL/PLSQL interview question that will be very helpfull.
    My Mail id :scorprabu@gmail.com

  54. can u please send interview SQL and PLSQL Questions (Advanced)
    if do you have any senario based document could you please send.

    THank

  55. Hi Sir,
    Thanks for your wonderful work.!
    I have a interview on next week. Could you send me sql and plsql scenario based interview questions as well as studying materials.
    it will be very helpful for me.
    Thanks,
    my mail id: yasodhadatchana18@gmail.com

  56. Hi,

    I am looking for scenario based SQL interview questions for ETL positions (Eg: Emp and dept table related)
    Real time scenario based interview questions.

  57. Advanced SQL interview question and answers for ETL positions.
    Real time scenario based please

  60. Hi Amit ,

    Can you please send me SQL and PL/SQL scenario based questions and the above scenarios are great help .
    Thank you in advance .

  63. Really good set of questions, Could you please share more scenario based SQL questions for interview purpose and few tips as well.

      3. Amit, Please share the interview questions basic level and advance level. Now only am learning SQL.

  66. Hi Amit,

    Could you please share me the advance Sql queries. It will help for interview preparation.

  67. Could you please share Informatica and SQL advanced scenario based questions with answers.

    Will be of great help!

  73. Good set of questions. Could you please share more scenario based SQL questions for interview

    Thanks in advance

  74. Hi Amit,
    Can you please send me scenario based oracle SQL and PL sql interview questions for 5 years of experience along with performance based questions.
    Regards
    Balgovind

    1. Sure Neeraja…
      Joins Interview Questions : http://www.complexsql.com/sql-joins-interview-questions/http://www.complexsql.com/category/this-category-includes-sql-interview-questions/
      http://www.complexsql.com/pl-sql-examples/.http://www.complexsql.com/unix-scripting-interview-questions/
      http://www.complexsql.com/etl-testing-interview-questions/
      http://www.complexsql.com/data-modeling-interview-questions-with-answers-for-professionals/

      Like this page on Facebook for More Updates :
      https://www.facebook.com/Complexsqlcom-1302739539812605/  

  79. Hi Amit,
    It is really helpful..!! Could you please share with me some scenario-based SQL/ PLSQL and UNIX interview questions and answers ..Thanks in advance🙂…My mail id- pralayabehura@gmail.com.

    1. Sure Pralaya..check your mailbox..
      Joins Interview Questions : http://www.complexsql.com/sql-joins-interview-questions/http://www.complexsql.com/category/this-category-includes-sql-interview-questions/
      http://www.complexsql.com/pl-sql-examples/.http://www.complexsql.com/unix-scripting-interview-questions/
      http://www.complexsql.com/etl-testing-interview-questions/
      http://www.complexsql.com/data-modeling-interview-questions-with-answers-for-professionals/

      Like this page on Facebook for More Updates :
      https://www.facebook.com/Complexsqlcom-1302739539812605/  

  80. Hi amit
    can you send me some real time scenario questions @smmahesh25@gmail.com

  81. Great Post
    Thanks a lot
    Can you please share SQL and PL/SQL interview questions on real-time scenario-based questions.

  82. Hi Amit,
    can you please send me some scenario based sql and plsql questions for 6 years of experience..I have an interview tomorrow.. Thanks in advance…
    They ask with pen/paper with simple values like A,B…0,1,2….
    My mail id- ks24iibs@gmail.com

    1. Joins Interview Questions : http://www.complexsql.com/sql-joins-interview-questions/http://www.complexsql.com/category/this-category-includes-sql-interview-questions/
      http://www.complexsql.com/pl-sql-examples/.http://www.complexsql.com/unix-scripting-interview-questions/
      http://www.complexsql.com/etl-testing-interview-questions/
      http://www.complexsql.com/data-modeling-interview-questions-with-answers-for-professionals/

      Like this page on Facebook for More Updates :
      https://www.facebook.com/Complexsqlcom-1302739539812605/  
      Regards and thanks,
      Amit S

  85. Hi Amit,,very nice explanation.Keep posting new ones.
    Can you send me scenario based sql .Thanks
    my mail id infarayala@ gmail.com

  86. Hi Amit,

    Please send me some scenario based sql and plsql questions for 2 years & 7 months of experience..I have an interview tomorrow.. Thanks in advance.

    1. Hi Sneha,

      Kindly check following links..You will get details.
      On Special Request of some of the users kindly check following interview questions related to joins and advanced SQL:
      Joins Interview Questions : http://www.complexsql.com/sql-joins-interview-questions/
      http://www.complexsql.com/category/this-category-includes-sql-interview-questions/
      http://www.complexsql.com/pl-sql-examples/.
      http://www.complexsql.com/unix-scripting-interview-questions/
      http://www.complexsql.com/etl-testing-interview-questions/
      http://www.complexsql.com/data-modeling-interview-questions-with-answers-for-professionals/
      Like this page on Facebook for More Updates :
      https://www.facebook.com/Complexsqlcom-1302739539812605/

      Regards and thanks,
      Amit S

  87. Hi Amit,
    Thanks for sharing real time scenarios which are really helpful.
    I am having a test in interview for data warehouse test analyst position, could you please send me the scenarios for SQL for test cases asap.

    Thanks in advance

  88. 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.

  90. Hi Amith,
    Can you please share SQL interview questions on real-time scenario
    Thanks in advance

  91. Hi Amit,

    Very nice scenario questions.Could you please send me the sql interview questions?

  92. Hi Amit,

    Please send me some scenario based sql, partition ,performace tuning. and plsql questions for 10 years of experience..
    I have an interview tomorrow..
    Thanks in advance.

      1. Hi Amit,
        Your explanation is very clear.. could you please send me the Scenario based SQL queries @cmvennila@gmail.com. Many thanks in advance.

  93. Hi Dear Amit,
    can you please send me some scenario based SQL and ETL/DWH Testing and Unix questions for 4.5 years of experience..

    Complex SQL Queries and Informatica ETL related questions im expecting.
    I am Eagerly Searching job but i didn’t selected due to in real time scenarios,

    Thanks in advance
    Harish

  95. I have SQL interview scheduled after 2 days. Can you please send me set of SQL questions for 4 years experienced.
    Mostly scenario based

  96. Hi Sir,
    Nice post and it is very helpful .
    For all the comments you are responding so nice of you sir.
    Could you please send scenario based question to my email id usandhya87@gmail.com.

    I have one question here sir. If we are displaying last 5 records in the table is there any need of displaying first 5 records with the result. I think it will be cover by select * from (Select * from Employee e order by rowid desc) where rownum <=5;
    is n't it?
    If user wants to display last five records from Employee table:

    Select * from Employee e where rownum <=5

    union

    select * from (Select * from Employee e order by rowid desc) where rownum <=5;

    Thanks,
    sandhya

    1. You will get answer of your question in complex sql queries in the site. kindly search and let me know if you want some more explaination.

  97. Please share advanced SQL questions earliest. Mostly on scenario based questions on medium to complex difficulty level

  99. Hi Amit,

    Could you please share the real time scenario queries and solutions,CTE , Query optimization techniques real time solutions.
    Thank you!

  100. Hello Sir,
    Hope you are doing well.

    Could you please share more scenario based interview question for testing professional.

    Regards,

  102. Hi Amit,
    I have one year of experience in etl but I am not cracking outside interview due to lack of scenario based questions solutions.
    Can you please send me set of interview questions to practice.
    It would be great help.
    Thank you..

  103. Yes Amit it is helpful the things which you have accommodated.. i am really impressed.. Thanks

  106. Can you share me some more scenario based complex sql queries for interview for 5- years

  107. Hi Amit, I am looking to hire SQL BI developers, mid to senior level and wondering if you are able to share with me few SQL real time scenarios to test their DB knowledge and to test candidates SQL coding abilities. Please share interview questions with answers and queries if you can, many thanks. Preveena.thirumalai@gmail.com is my email address to share .

  109. Hi Amit,
    Thanks! very well explained!
    Could you please share the more scenario based interview questions and answers to my mail.

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

  111. Can you share me some more scenario based complex sql queries for interview for 4-5 YEARS
    and also if you have POWER BI Technology scenario based questions.

    Thanks
    Damu

Comments are closed.