What are SQL interview questions for 5 years experience professionals?

In my previous articles I have given the SQL interview questions for experience professionals. In this article I would like to focus on SQL interview questions for 5 years experience professionals. I have collected the interview questions which are supposed to be asked for 5 or 5+ year experienced professionals.These interview question collection is based on the multiple people experiences during the interviews. The following questions and answers are useful to the experienced professionals

Question 1 : What is difference between Coalesce() and isnull() functions?

Answer :

Coalesce : The coalesce function is function which accepts two or more parameters. The coalesce function will return first not null parameter if we are using two or more parameters.

Isnull() : Isnull() function accepts only two parameters. It will check first parameter if it is null then it returns second parameter if that is not null it returns the first parameter.

The both functions are vastly used in adding the validations in T-SQL or big SQL queries.

Question 2 : What is difference between rank and dense_rank? (most asked SQL interview question for 5 years experience professional)

Answer:

Rank	Dense_rank
Rank function gives you order within your partition	Dense_rank function is used to give the consecutive ranking for ordered partition
Ties are assigned to same rank in rank function ( Refer example given in article)	The ranks are assigned consecutively to every record of the table
When ties are assigned it will give the same rank and next rank will be skipped	There is no mechanism of tie so the next ranks are not skipped and consecutive ranking has been given to the record
RANK( expr1 [, expr2, … expr_n ] ) WITHIN GROUP ( ORDER BY expr1 [, expr_2, … expr_n ] );	Dense_RANK () OVER (PARTITION BY expression ORDER BY expression)

Question 3: What is difference between Clustered and non-clustered index?

Answer :

Clustered Index	Non- Clustered Index
The Clustered Indexes are indexes which will sort the data physically	Non- Clustered indexes does not sort the data physically. These are logical indexes.
Only one clustered index created for one column	User can create up to 999 non- clustered indexes
These indexes are faster to read than non- clustered indexes	Non- clustered indexes are slower in read operation as compare to clustered indexes.
The select operations are fast in clustered indexes	Insert and update operations are fast in non-clustered indexes
Clustered indexes will only sort the table in specific order so it will not consume the physical space	Non-clustered indexes are physically stored indexes which works in logical way.
Clustered index contains data at the leaf node	Non- clustered indexes do not contain data at leaf node.

Question 4 : What is query to calculate 4th highest salary of employee?

Answer :

Select * from Employee a Where 4 = (Select Count (distinct Salary) from Employee where a.salary<=b.salary;

Question 5 : What is difference between primary key and foreign key?(most asked SQL interview question for 5 years experience professional)

Answer:

Primary Key	Foreign Key
Primary Key is a chosen candidate key that uniquely defines a tuple in a relation.	Foreign key in a table refers to the primary key of other table.
Primary key value can never be NULL.	Foreign key accepts NULL value.
No two tuples in a relation carry duplicate values for a primary key attribute.	Tuples can carry duplicate value for a foreign key attribute.
There can be only one primary key of a relation.	There can be multiple foreign keys in a relation.
Primary key constraint can be defined on the temporary tables.	Foreign Key constraint can not be defined on the temporary tables.
By default, a primary key is clustered indexed.	Foreign key is not clustered indexed automatically; it has to be done manually.
We can insert a value to a primary key attribute, even if the referencing foreign key does not have that value in its column.	We can not insert a value to a foreign key, if that value is not present in the referenced primary key column.

Question 6 : Give us example of Correlated subquery.

Answer:

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.  (Source-click here)

Example:

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. (Source-Click here)

Question 7 : How to write query to display 1 to 25 numbers in sequence?

Answer :

Select level from dual connect by level <=25;

Tip: User needs to know the concept of Hierarchical queries. Click here to get concept of hierarchical queries

Question 8 :What is query to fetch the maximum salary and minimum salary of employee?

Answer:

Select max (salary) from Employees

Union

Select min (salary) from Employees;

Question 9 : How to get number of weekends for 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 10 : How to calculate second highest salary of employee using correlated subquery?

Answer :

SELECT name, salary FROM Employee e1 WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM Employee e2 WHERE e2.salary > e1.salary)SELECT name, salary FROM Employee e1 WHERE 2-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2 WHERE e2.salary > e1.salary);

Question 11 : How to find account details who done the transaction with Card and Net banking in last hour.

Answer :

Select a.* from Account_master a,Transaction_details b where a.account_number=b.account_number and b.transaction_type in(‘Card’ , ‘Net Banking’ and b.date_of_transaction=TO_CHAR(SYSDATE -1, ‘dd-mm-yy hh24:mi:ss’) ;

Question 12 : Can you join table by itself? If Yes how? If no Why?

Answer:

1.Using Self-Joins,A table can be joined to itself in a self-join.

2.Use a self-join when you want to create a result set that joins records in a table with other records in the same table.

3.To list a table two times in the same query, you must provide a table alias for at least one of instance of the table name.

Question 13 : Will self join is inner join or outer join?

Answer :

1.A self-join can be an inner join or an outer join or even a cross join.

2.A table is joined to itself based upon a column that have duplicate data in different rows.

Question 14 : What is query to display employee name, his job, and his manager. Display also employees who are without a manager.

Answer :

select e.ename, e.job, m.ename Manager from emp e,emp m wheree.mgr=m.empno union select ename,job,’no manager’ from emp wheremgr is null;

Question 15 : What is query to drop all tables in Oracle SQL?

Answer :

Begin

For I In

(Select * from Tabs)    —Tabs is system table in which user get the different user defined table names.

Loop

Execute immediate (‘Drop Table  ‘||i.table_name||’cascade constraints’);

End loop;

End;

Question 16 : Explain how ETL is used in Data warehousing?

Answer :

The most common example of ETL is ETL is used in Data warehousing.User needs to fetch the historical data as well as current data for developing data warehouse. The Data warehouse data is nothing but combination of historical data as well as transactional data. Its data sources might be different.User needs to fetch the data from multiple heterogeneous systems and load it in to single target system which is also called as data warehouse.

As The ETL definition suggests that ETL is nothing but Extract, Transform and loading of the data; This process needs to be used in data warehousing widely. The simple example of this is managing sales data in shopping mall. If user wants the historical data as well as current data in the shopping mall first step is always user needs to follow the ETL process. Then that data will be used for reporting purpose.

Question 17 : What are different database Environments used in any project?

Answer:

The Project to project database environment varies.But the following is basic environment structure used for projects.

1.Development Environment:

In Development Environment all developer works and development work is been done on development environment.

2.Test Environment:

Developers does not have access of test environment.After development is done the code is migrated to Test Environment.Testing team is working on Test environment and execute black box as well as white box test cases on this Environment.Sometimes System Integration Testing (SIT) is also done on this Environment.

3.UAT Environment:

UAT stands for User Acceptance Testing.On this Environment the Customer side testers tests the software and executes User Acceptance Test Cases.

4.Performance Testing Environment:

On this environment the performance tester tests all performance related issues on this environment. This environment contains very huge data and performance tester will try to break the system using that big data.

5.Production Environment:

On this Environment actual user works and uses the software.

Question 18 : What is difference between Union and union all?

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;UnionSelect col1,col2…from table2;	2.Syntax:Select col1,col2…from table1;UnionSelect 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 19 : What are different types of indexes?

Answer :

There are following types of indexes:

1.Normal Indexes

2.Bit map indexes

3.B-tree Indexes

4.Unique Indexes

5.Function Based Indexes

Question 20 : What is Bit-map index? Explain with Example.

Answer:

1.If Table contains the distinct values which are not more than 20 distinct values then user should go for Bit map indexes.

2.User should avoid the indexing on each and every row and do the indexing only on distinct records of the table column.You should able to check drastic change in query cost after changing the normal index to Bit map index.

3.The bit map indexes are very much useful in dataware housing where there are low level of concurrent transactions.Bit map index stores row_id as associated key value with bitmap and did the indexing only distinct values.

4.Means If in  1 million  records only 20 distinct values are there so Bitmap index only stores 20 values as bitmap and fetches the records from that 20 values only.

Syntax:

Create bitmap index Index_name on Table_name(Columns which have distinct values);

Example:

CREATE BITMAP index  BM_DEPT_NAME on DEPT(Department_name);

These are most important SQL interview questions for 5 years experience professionals. I hope this help the SQL professionals. If you like this article or if you have any issues with the same kindly comment in comments section.