Interview Questions for Deloitte :

I have written the popular articles on SQL Questions for Cognizant Technologies as well as Infosys technologies.I have studied lot of Websites and i have experienced the SQL interview for Deloitte and come up with the set of Interview Questions for Deloitte.Deloitte is well known organization and it has some tricky interviews.I will try to cover the Tricky interview questions which will probably asked in SQL Interview of Deloitte.You need to give English versant test if you successfully completed and shortlisted from the interview.

Following are some Interview Questions for Deloitte which will ask for SQL,PLSQL,ETL developer Interview:

1.What is difference between unique and distinct?

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.

2.What is Normalization?(100% asked Interview Questions for Deloitte)

Answer :

Database Normalization is organizing non structured data in to structured data.Database normalization is nothing but organizing the tables and columns of the tables in such way that it should reduce the data redundancy and complexity of data and improves the integrity of data.

Database Normalization is nothing but technique of designing the database in structured way to reduce redundancy and improve data integrity.

3.What is purpose of Normalization?

Answer :

Normalization is used for following purpose:

• To Eliminate the redundant or useless data
• To Reduce the complexity of the data
• To Ensure the relationship between tables as well as data in the tables
• To Ensure data dependencies and data is logically stored.

4.What is Self join?

Answer :

Self join is nothing but the table joins with itself. There are lot of tables which contains more than one functionality at that time the concept of self join comes to the picture.

Syntax:

SELECT a.column_name, b.column_name…
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

Real Example:

If one table contains Employee and its position.If we need to fetch the Employees and its managers then self join is used.

5.Consider following tables:

create table table_a(id numeric);

create table table_b(id numeric);

insert into table_a(id) values
(10),
(20),
(30),
(40),
(50);

insert into table_b(id) values
(10),
(30),
(50);

Write a query to fetch values in table table_a that are and not in table_b without using the NOT keyword?

Answer :

The Query for the same is:

select * from table_a
except
select * from table_b;

6.What are different forms of Database Normalization?

Answer :

There are following Four Normal Forms used in Database Normalization:

1.First Normal Form

2.Second Normal Form

3.Third Normal Form

4. Boyce-code Normal Form(BCNF)

7.Explain First Normal Form with example.

Answer :

The first normal form is the normal form of database where data must not contain repeating groups.The database is in First normal form If,

1.It contains only automic values.

Automic values:- The Single cell have only single value

2.Each Record needs to be unique and there are no repeating groups.

Repeating Groups:- Repeating group means a table contains 2 or more values of columns that are closely related.

Example:

Consider following table which is not normalized:

Employee Table:

Employee No	Employee Name	Department
1	Amit	OBIEE,ETL
2	Divya	COGNOS
3	Rama	Administrator

To bring it in to first normal form We need to split table into 2 tables.

First table:Employee Table

Employee No	Employee Name
1	Amit
2	Divya
3	Rama

Second Table: Department table

Employee No	Department
1	OBIEE
1	ETL
2	COGNOS
3	Administrator

We have divided the table into two different tables and the column of each table is holding the Automic values and duplicates also removed.

8.What is difference between view vs materialized view?(100% asked Interview Questions for Deloitte)

Answer :

View	Materialized Views(Snapshots)
1.View is nothing but the logical structure of the table which will retrieve data from 1 or more table.	1.Materialized views(Snapshots) are also logical structure but data is physically stored in database.
2.You need to have Create view privileges to create simple or complex view	2.You need to have create materialized view ‘s privilges to create Materialized views
3.Data  access is not as fast as materialized views	3.Data retrieval is fast as compare to simple view because data is accessed fom directly physical location
4.There are 2 types of views: 1.Simple View 2.Complex view	4.There are following types of Materialized views: 1.Refresh on Auto 2.Refresh on demand
5.In Application level views are used to restrict data from database	5.Materialized Views are used in Data Warehousing.

9.What is Fast Refresh and Incremental Refresh by considering snapshot?

Answer :

When in database level some DML changes are done then Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. 

In this situation materialized view is not full refresh and it only refresh the data on incremental basic and only incremental data is added in to materialized view.

10.What is Truncate in SQL?Explain with example.

Answer :

Truncate:

1.Truncate is Data Definition Language command which is used to remove the all Rows from the table.

2.You can not Filter rows while truncate data from the database because it does not allows where clause.

3.Truncate does not return number of rows truncated from the table.

4.Truncate deallocates the memory for that object and other object will use that deallocated space.

5.Truncate operation can not roll backed because it does not operates on individual row.It directly processes all rows from the table.

6.Truncate is faster than delete.

7.You can not use conditions in case of truncate.

Syntax:

Truncate table <Tablename>

Real Life Example:

Suppose you want to delete or remove all records from table named department which has following table structure:

Name of Table: Department

Department ID	Employee Name
100	Amit
100	Rohan
101	Rohit
102	Null

Truncate table Department;

So the all values are truncated.

If we want to check the count of that table:

Select count(*) from Department;

0 rows displayed

Use Roll back and check the Count:

     Rollback;

Select count(*) from Department;

0 rows displayed

After Rollback statement also the count is Zero.So truncate table is used to truncate all the rows from the table.

11.Explain Rank function in SQL.(90% asked Interview Questions for Deloitte)

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;

CLICK HERE TO CHECK RANK AND DENSE RANK FUNCTION DIFFERENCE

12.What is the Query to fetch last record from the table?

Answer:

Select * from Employee where Rowid= select max(Rowid) from Employee;

16.How do i fetch only common records between 2 tables.

Answer :

Select * from Employee;

Intersect

Select * from Employee1;

17.What are indexes in SQL?(90% asked Interview Questions for Deloitte)

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.

18.What is the Query to fetch first record from Employee table?

Answer:

 Select * from Employee where Rownum =1;

19.What is Query to display last 5 Records from Employee table?

Answer:

Select * from Employee e where rownum <=5

union

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

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

20.How to get 3 Highest salaries records from Employee table?

Answer:

select distinct salary from employee a where 3 >= (select count(distinct salary) from emp loyee b where a.salary <= b.salary) order by a.salary desc;

21.How Can i create table with same structure of Employee table?

Answer:

Create table Employee_1 as Select * from Employee where 1=2;

22.What are advantages of Indexes?

Answer:

Indexes are memory objects which are used to improve the performance of queries which allows faster retrieval of records.

Following are advantages of Indexes:

1.It allows faster retrieval of data

2.It avoids the Full table scan so that the performance of retrieving data from the table is faster.

3.It avoids the table access alltogether

4.Indexes always speeds up the select statement.

5.Indexes used to improve the Execution plan of the database

4.What are disadvantages of Indexes?(80 % asked in Performance Tuning Interview Questions)

Answer:

1.Indexes slows down the performance of insert and update statements.So always we need follow best practice of disabling indexes before insert and update the table

2.Indexes takes additional disk space so by considering memory point indexes are costly.

23.What is parser?

Answer:

When SQL Statement has been written and generated then first step is parsing of that SQL Statement.Parsing is nothing but checking the syntaxes of SQL query.All the syntax of Query is correct or not is checked by SQL Parser.

There are 2 functions of parser:

1.Syntax analysis

2.Semantic analysis

24.What is composite index?(90% asked Interview Questions for Deloitte)

Answer:

When 2 or more columns are related to each other in the table and the same columns are used in where condition of the query then user can create index on both columns.These indexes are known as composite indexes.

Example:

Create index CI_Employee on Employee(Eno,Deptno);

25.What is mean by Clustered index?

Answer:

1.The clustered indexes are indexes which are physically stored in order means it stores in ascending or descending order in Database.

2.Clustered indexes are created once for each table.When primary key is created then clustered index has been automatically created in the table.

3.If table is under heavy data modifications the clustered indexes are preferable to use.

In this article i have explained 25 most important Interview Questions for Deloitte.Hope you like this article.Don’t forget to share this article.