Advanced SQL Interview Questions
SQL Interview Questions

What are latest Advanced SQL Interview Queries ?

Amit S July 2, 2018

In my previous articles I have explained different interview questions related to SQL with its answers. This article gives you idea about the Advanced SQL Interview Questions with answers. This article will give you the idea about important Advanced SQL Interview Questions with its answers. The Advanced SQL Interview Questions contains the questions with complex sql as well as questions with different advanced SQL.

Question 1 : Is there any way to find out column is indexed or not? If yes how?

Answer:

To find out the selected column is indexed or not there are following 2 ways

1.Selecting Explain plan:

Select that specific table and check the explain plan of the table.But if that select statement is not using the index then it will not show the column is indexed or not.

2.Using System tables:

You can use the system table all_ind_columns and all_indexes table to check that column is indexed or not.This is the best way to find out column is indexed or not.

Select * from all_ind_columns where table_name=’Name of the table’ and column_name=’Name of column’;

If the column is indexed then you will get the output.

Question 2 : Explain Rank as aggregate function with examples.(100% asked Advanced SQL Interview Questions )

Answer :

Rank function is used as aggregate function to return the rank of rows in the table within group of rows.If someone needs to find out the rank of specific row in the table then we will use the rank function.

Rank Function Syntax:

RANK( expr1 [, expr2, … expr_n ] ) WITHIN GROUP ( ORDER BY expr1 [, expr_2, … expr_n ] );

Real Life Example:

Consider following table:

Employee_num Employee_name Department Salary
1 Amit OBIEE 680000
2 Rohan OBIEE 550000
3 Rohit COGNOS 430000

Question:Find out Rank of Employee whose Department is OBIEE and Name is rohan?

select RANK(Rohan, OBIEE) WITHIN GROUP (ORDER BY Name, Department) from employees;

The Above will return result as 2 if we consider the table given in example.

Question 3 : Explain Rank Function as Analytical function with Example.(70% asked Advanced SQL Interview Questions )

Answer:

Rank function is used as analytical function in SQL/PLSQL/SQL server which is used to give the rank to the specific record in the table.Rank function is giving you ranking in ordered partitions.Means Ties are assigned to the same values after using the order by clause.So Rank function is not useful where same data is repeated again and again.It is useful in Unique data where user can make partition and order  the data properly.

Syntax of Rank:

RANK () OVER (PARTITION BY expression ORDER BY expression)

Example:

SELECT Employee_Name,Department_No,Salary,RANK() OVER (PARTITION BY Department_No ORDER BY Salary) “Rank” FROM EMPLOYEE;

If we consider above query the same rank will be given for same salaried Employees but it will jump to the new rank for next salaried employee.Kindly check following Output

Employee Name Department No Salary Rank
      Amit 10 15000 1
      Rahul 10 8700 2
      Aditya 10 8700 2  (Tie assigned same rank)
      Shrey 10 12500 4

In above table the Tie for Rahul’s salary and Aditya’s salary.So Same rank has been assigned to both of them.

Question 4 : How many types of privilleges available in SQL? Explain.

Answer:

There are two types of privilleges available in SQL.One is system privillege other is User privillege.

  • System Privilege: System privileges deal with an object of a particular type and specifies the right to perform one or more actions on it which include Admin allows a user to perform administrative tasks, ALTER ANY INDEX, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW etc.
  • Object Privilege: This allows to perform actions on an object or object of another user(s) viz. table, view, indexes etc. Some of the object privileges are EXECUTE, INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX, REFERENCES etc.

Question 5 : What is difference between unique and distinct?(90% asked Advanced SQL Interview Questions )

Answer:

There is no difference between unique and distinct keywords apart from one difference.unique is applied before insertion and retrival.It consists  of non duplicate values.if unique constraint is given it does not take duplicate values.distinct is used in retrieval it gives the suppressed row(ex if two rows are same it will show single row and non duplicate row) therefore distinct is the combination of suppressed duplicate and non duplicate rows.Specify DISTINCT or UNIQUE if you want Oracle to return only one copy of each set of duplicate rows selected (these two keywords are synonymous). Duplicate rows are those with matching values for each expression in the select list.

So there is no functional difference between Unique and distinct both have same functionalities.

Advanced SQL Interview Questions

Question 6 :What is dense_rank Explain with examples.

Answer :

Dense Rank analytical function is same as rank but it has assigned the consecutive rank for Tie values in the table.So Disadvantage of rank function  has been overcome in Dense_Rank function.Dense rank function is useful to give the rank for the SQL values in the table.It is not repeating the rank values so these functions are really very useful in development of reports where we require actual rank values.

“Dense_Rank gives consecutive ranking for ordered partitions…”

Syntax of Dense_Rank:

Dense_RANK () OVER (PARTITION BY expression ORDER BY expression)

Example:

SELECT Employee_Name,Department_No,Salary,Dense_RANK() OVER (PARTITION BY Department_No ORDER BY Salary) “Rank” FROM EMPLOYEE;

If we consider above query the different rank will be given for same salaried Employees:

Employee Name Department No Salary Rank
      Amit 10 15000 1
      Rahul 10 8700 2
      Aditya 10 8700 2 (Tie assigned different rank)
      Shrey 10 12500 3

In above output the consecutive rank has been assigned to same salaried Employees also.

Question 7 :What will be the output of following Query?

Query :

select case when null=null then ‘Amit’ Else ‘Pradnya’ from dual;

Answer:

In SQL null value is not equal to itself.So null=null is false and the output of above query is ‘Pradnya’.

Question 8 : Which are different Set operators in SQL?(100% asked Advanced SQL Interview Questions )

Answer :

Set operators are nothing but the operators which are used to connect two tables and fetch the records from the two tables.We need to follow one condition that the table set 1 columns and table set 2 columns are same and its datatype must be same.SQL Set Operators combines the result of 2 queries or components on to the single result.

Following are Set Operators in SQL:

  1. Union
  2. Unionall
  3. Intersect
  4. Minus

Question 9 :How to select first 5 characters from First name in Employee table?

Answer:

Oracle Query:

Select Substr(First_name,0,5) from Employee;

MS SQL:

Select Substr(First_name,1,5) from Employee;

MySQL:

Select Substr(First_name,1,5) from Employee;

Question 10 :What is first and last function in SQL?(80% asked Advanced SQL Interview Questions )

Answer:

The FIRST and LAST functions can be used to return the first or last value from an ordered sequence. Say we want to display the salary of each Employee, along with the lowest and highest within their department we may use something like.

Example:

SELECT EmpNo, DeptNo, Sal ,MIN (Sal) KEEP (DENSE_RANK FIRST ORDER BY Sal) OVER (PARTITION BY DeptNo)”Lowest”, MAX (Sal) KEEP (DENSE_RANK LAST ORDER BY Sal) OVER (PARTITION BY DeptNo) “Highest”FROM   EMPLOYEE ORDER BY DeptNo, Sal;

Question 11 : What is difference between Union and Union all Operators?

Answer:

Union Union ALL
1.Union Set operator is used to fetch the records from 2 different tables which eliminates the duplicate records 1.Union all Set operator is used to fetch the records from 2 different tables which does not eliminates the duplicate records
2.Syntax:Select col1,col2…from table1;

Union

Select col1,col2…from table2;

 2.Syntax:Select col1,col2…from table1;

Union

Select col1,col2…from table2;
3.For Performance tuning Union operator is not preferable as it takes time to eliminate duplicate records 3.Union all is preferable operator in Performance tuning.

Question 12: How to find all details about Constraint?

Answer:

To find details about constraint following query is used:

1.Select  * from User_constraints;

2.Select * from User_cons_columns;

Question 13:What will be the output of following query?

Query :

Select * from (select ‘a’ union all select ‘b’) Q;

Answer:

It will throw error because no values are selected in Subquery.

Error code-ORA-00923 from keyword not found expected values.

Question 14: What is subquery?(100% asked Advanced SQL Interview Questions )

Answer:

Subquery is query within query.The output of outer query is assigned to the column which is used in where condition of outer query.The subquery output is returning only one output value and based on that output value the outer query is executed.Subqueries are used in various real life scenarios like report development,Application logic development,Performance tuning of query.

Example:

Select * from Employee where dept_no In (Select dept_no from department where department_name=’Oracle’);

Question 15:What is Correlated Subquery.

Answer:

Correlated Query is nothing but the subquery whose output is depending on the inner query used in that query.Correlated query is the query which is executed after the outer query is executed.The outer query is always dependent on inner query.The approach of the correlated subquery is bit different than normal subqueries.In normal subqueries the inner queries are executed first and then the outer query is executed but in Correlated Subquery outer query is always dependent on inner query so first outer query is executed then inner query is executed.Correlated Subqueries always uses operator like Exist,Not Exist,IN,Not IN.

“Correlated Queries are also called as Synchronized queries…”

Question 16 : Explain co-related sub-query with example.

Answer:

Fetch the Employees who have not assigned a single department.

Select * from Employee E where Not exist

(Select Department_no From Department D where E.Employee_id=D.Employee_ID);

Execution of query:

Step 1:

Select * from Employee E ;

It will fetch the all employees

Step 2:

The First Record of the Employee second query is executed and output is given to first query.

(Select Department_no From Department D where E.Employee_id=D.Employee_ID);

Step 3:

Step 2 is repeated until and unless all output is been fetched.

Question 17 :What is Materialized View?

Answer:

Materialized view is also a logical structure which is stored physically on the disc. Like a view in Materialized view we are using simple select statement to create it. You should have create materialized view privileges to create a materialized view. Definition of materialized view(called as MV) has been stored in databases. Materialized views are useful in Data-warehousing concepts.

Question 18:What is difference between NVL,NVL2 and Null if?

Answer:

1.NVL :

NVL function substitutes a value when a null value is encountered.

2.NVL2 :

NVL2 substitutes a value when a null value is encountered as well as when a non-null value is encountered.

3.NULLIF:

NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.

Question 19:Explain Refresh options of Materialized view?

Answer:

1.Refresh on commit:

This option committed the data in materialized views immediately after data inserted and committed in table. This option is known as incremental refresh option. View is not fully refreshed with this option

2.Refresh on Demand:

Using this option you can add the condition for refreshing data in materialized views.

You can refresh the data using fast (incremental approach),Complete, Force options.

Question 20 :What is difference between varchar and varchar2 datatype?

Answer:

Varchar can store up to 2000 bytes and varchar2 can store up to 4000 bytes of memory space.Varchar will occupy the space for null values whereas varchar2 can not occupy the space for null values.So varchar2 is good to use not to  face performace related problems.varchar2 is faster than varchar datatype.

Question 21:How to get number of Weekends of current month?

Answer:

SELECT count (*) AS Weekends FROM

(SELECT TRUNC (SYSDATE,’mm’) +LEVEL-1 Current_dt

FROM Dual

CONNECT BY LEVEL <= last_day (SYSDATE) – TRUNC (SYSDATE,’mm’) +1

)

Where TO_CHAR (Current_dt,’dy’) IN (‘sat’,’sun’);

Question 22:What is Index?What is use of index in SQL?

Answer:

Index is optional structure associated with the table which may or may not improve the performance of Query.In simple words suppose we want to search the topic in to book we go to index page of that book and search the topic which we want.Just like that to search the values from the table when indexing is there you need not use the full table scan.

Indexes are used to improve the performance of the query.

Question 23:What is unique index?

Answer:

To create unique index you must have CREATE ANY INDEX privilege.Here the concept is bit different.User needs to check the values of the table to create unique index.If table contains uniquely identified values in specified column then you should use unique index.Especially while creating the table if we specify the primary key  then unique index is automatically created on that column.But for Unique key constraint columns you separately need to do indexing.Kindly make sure that Unique key indexes created on the columns which has unique values only.

Question 24 :What is difference between ‘Between’ operator and ‘In’ operator?

Answer:

BETWEEN Operator :
The BETWEEN operator is used to fetch rows based on a range of values.

Example :

SELECT * FROM Students WHERE ROLL_NO BETWEEN 20 AND 30;

This query will select all those rows from the table Students where the value of the field ROLL_NO lies between 20 and 30.
IN Operator :
The IN operator is used to check for values contained in specific sets.

Example :

SELECT * FROM Students WHERE ROLL_NO IN (20,21,23);

This query will select all those rows from the table Students where the value of the field ROLL_NO is either 20 or 21 or 23.

Question 25 : How to convert the System time in to seconds in oracle?

Answer :

To_char function is used to convert time to character and ssss will used to convert the time in to seconds.

Following query is useful.

SQL> select
  2    to_char(sysdate,'hh24:mi:ss') As "SystemTime",
  3    to_char(sysdate,'sssss') "Seconds"
  4  from dual;

SystemTime     Seconds
--------       -----
11:34:50       41750

These are above some very important Advanced SQL Interview Questions.Hope you like this article on Advanced SQL Interview Questions.If you like the article on Advanced SQL Interview Questions kindly comment it in comment section.

130 Replies to “What are latest Advanced SQL Interview Queries ?”

  1. Question 9 answer is missing column name in the SQL statement. To select first 5 characters from First name in Employee table, we use following syntax :
    SELECT SUBSTRING(first_name,1,5) FROM employees;

  2. Employee Name Department No Salary Rank
    Amit 10 15000 1
    Rahul 10 8700 2
    Aditya 10 8700 2 (Tie assigned different rank)
    Shrey 10 12500 4

    Can you please explain why last column –> Shrey 10 12500 4 got rank 4 if we order by salary even when they all belong to same department no

    1. Sure Bishir.. I would just like you to create scenario from your end and check..It is taking rank 4 because the rank 3 is tie the assignment..

    2. Hi Amit,

      It’ll be really helpful, if you could share some advanced SQL questions for 3+ years experience as ETL developer.

      Thanks in Advanc
      Abdul

        1. Sir, I am an ETL developer with 3 years of experience looking for SQL interview questions.
          Can you please share sql interview questions for my experience.

    3. please use dense_rank if you want a actual numbering. Rank will allocate same number if having same order by field like same age ,salary but the number will get incremented like the next number will be assigned same like here 2 is assigned twice and then directly 4 as 2 twice.
      if 2 is assigned three time then Shrey will get no 5.
      if you want serial no then use dense_rank() with same condition.

  3. select case when null=null then ‘Amit’ Else ‘Pradnya’ from dual;

    Error will be raised .. because in case statement end is not mentioned

  9. Hi Amit, can you please send me some advanced SQL interview questions for 3-5 years of experience? Thanks!

  10. >> select case when null=null then ‘Amit’ Else ‘Pradnya’ from dual;
    Don’t know what “from dual” is…a table?
    replaced it with “end” and like the other user said, it works afterwards

  11. Hi Moderator,

    Can you provide SQL for interview for Banking and Healthcare Domain. Also do you have question for Teradata or complex Warehousing Question.

  13. Hi Amit ..please send me sql questions for banking domain also can you please send me some advanced SQL interview questions for 3-5 years of experience? Thanks!

  16. Hi, Please send critical 5+ years plsql interview questions along with performance tuning questions

  18. Hi Amit, could you please send me some advanced SQL interview questions for 3-5 years of experience?

  19. Hi Amit,

    Thanks for the explanation. Very helpful. Can you please share the PL/SQL interview question .

  20. Hi Amit,
    Great post and very helpful. Could you please share with me SQL and PLSQL interview questions for 5 yrs exp

  22. Could you please provide SQL Server Interview questions for 10 yrs experience.
    Also, Data Warehousing and Data Modeling questions

  23. Hi Amit,
    Can you also send me SQL and PLSQL questions for interview 5 yrs exp? I’m supposed to have interview next week! Thanks!

  24. Hi Amit,
    Can you please provide SQL and SQL query related interview questions for 3-4 years of experience in banking and Insurance domain.

  25. Hi Amit, can you please send me some advanced SQL interview questions for 3-5 years of experience? Thanks!

  26. Hi,
    Could you please share Advanced SQL interview questions with 5 years of experience. Thank you!

  27. Hi Amit,
    Very helpful post 🙂
    could you please send me SQL and PLSQL interview questions for 5 yrs exp.
    Thank you

  28. Hi Amit,

    Can you please send 3+ years experience SQL interview questions ( amazon ) if you have anything specific.
    Thanks in advance.

  29. Hi Amit, can you please send me some advanced SQL interview questions for 3-5 years of experience? Thanks!

  30. Hi Amit Sir, can you please send me some advanced SQL interview questions for 3-5 years of experience? Thanks 🙂

  31. Hi Amit,
    Kindly share the SQL advanced interview questions for 5 years of experience.

    Regards,
    Dharma

  33. Hi Amit,

    Can you please provide me most complex SQL and datawarehousing interview questions for 5 years experience .

  35. Hi Amit ,
    Thanks for the post.
    can you please send me some advanced SQL/plqsl interview questions for 5 years of experience? Thanks 🙂

  36. Hi Amit,
    Kindly share the SQL advanced interview questions for 5 years of experience.

    Thanks,
    Sabbir

  37. Hi Amit,
    Thanks for the article. Would you please share advance plsql interview question for exprience and also real time scenario question asked in plsql interview.
    That would be very helpful.

    Thanks in advance.

  38. Hi Amit,

    You make life easy for people who are struggling to find good resources. Thank you for this.

    I have an interview coming up soon. Could you email me Advanced SQL interview questions for about 0-1 year of experience?

    Cheers

  39. Hi Amit,
    Great job..!!
    Please send me the interview questions and SQL query interview questions for 5 year experience

        1. Hello Sai,
          Kindly check following useful links..
          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

  41. Do you have questions for Amazon sql interview onsite?
    If you do could you please send them tome ?

  43. Hi ,can u please send me recent plsql interview questions and advance SQL queries for interview

    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/  

  45. Hi Amit,
    Can you please share some advance interview questions in sql for Data Analytics.

    1. Sure Shivani..
      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

    1. Sure Chinmaya..

      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 Shiravadekar

  48. Hi Amit,
    Can you plz share with me some advanced sql questions for (3-5 yrs exp)for zeotap.

  49. Hi Amit,
    This is really great stuff. However, Would you mind emailing all in one list of expected SQL questions for 3+ years experience ?

  51. Hi Amit,

    Brilliant job in helping so many persons on this platform. Making the lives better. The platform is very informative. I am a big data engineer but gets rejected in many interviews on data warehousing and modelling (haven’t been part of any such projects) . It would be helpful if you could guide or share materials on the above mentioned topics along with query interview questions like this. Much thanks in advance.

  52. I am looking for 3+ year of years in SQL data analyst interview questions could you please help me ?

  53. Hi Amit ,
    Can you please send me the MS SQL questions for banking domain.

  54. Its very useful .Thanks Amit
    I have interview Monday on advanced sql plsql .could you please send me questions .

  55. Very useful explanation. Could you please share advanced level sql questions as am preparing for interviews for sdet.

Comments are closed.