Advance SQL questions and answers

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.

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

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 commited the data in materialized views immediately after data inserted and commited 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.