Database interview Questions :

In my previous article i have given the idea about different SQL interview questions. This article gives you different Database interview questions from basic level to advanced level.The Database interview questions are most important for those who is willing to learn database and who wants to make career in database.This article gives you idea about most commonly asked database interview questions for freshers as well as experienced person.

1.What is mean by DBMS? (100% asked Database interview Questions )

Answer : 

DBMS is nothing but Database Management system. This is backbone of every application or every system.A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.

2.What are different types of databases?

Answer :

• Hierarchical databases.
• Network databases.
• Relational databases.
• Object-oriented databases.

3.Explain Data Definition Language?(100% asked Database interview Questions )

Answer:

DDL – Data Definition Language:

Command	Description
CREATE	Creates a new table, a view of a table, or other object in database
ALTER	Modifies an existing database object, such as a table.
DROP	Deletes an entire table, a view of a table or other object in the database.

4.Explain Data Manipulation Language?

Answer:

DML – Data Manipulation Language:

Command	Description
INSERT	Creates a record
UPDATE	Modifies records
DELETE	Deletes records

5.What are advantages of DBMS ?(100% asked Database interview Questions )

Answer:

Database management systems were developed to handle the following difficulties of typical file-processing systems supported by conventional operating systems.
1. Data redundancy and inconsistency
2. Difficulty in accessing data
3. Data isolation – multiple files and formats
4. Integrity problems
5. Atomicity of updates
6. Concurrent access by multiple users
7. Security problems.

6.What is Normalization?(100% asked Database interview Questions )

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.

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

8.What is difference between unique and distinct in DBMS?

Answer :

There is no difference between unique and distinct keywords apart from one difference. Unique is applied before insertion and retrieval. 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.

9.What are different forms of Database Normalization?(100% asked Database interview Questions )

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)

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

11.What are the differences between DDL, DML and DCL in SQL?

Answer:

Following are some common differences between DDL,DML and DCL in SQL :

DDL stands for Data Definition Language. SQL queries like CREATE, ALTER, DROP and RENAME come under this.
DML stands for Data Manipulation Language. SQL queries like SELECT, INSERT and UPDATE come under this.
DCL stands for Data Control Language. SQL queries like GRANT and REVOKE come under this.

12.What is Identity?

Answer: 

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

13.What is parser?(80% asked Database interview Questions )

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

14.What is a transaction? What are ACID properties?

Answer: 

A Database Transaction is a set of database operations that must be treated as whole, means either all operations are executed or none of them.
An example can be bank transaction from one account to another account. Either both debit and credit operations must be executed or none of them.
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

15.What are Set operators in SQL?

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

16.What are different parameters to consider the database performance of Application?

Answer:

There are lot of parameters to consider the performance of application:

1.What size of images we are using in application.The images we are using on application should not be maximum size.

2.What is the data volume used to fetch the data

3.Data cardinality: The most important factor is data cardinality of the data in application.Data should be divided in proper manner and the database should be in well normalized form

4.Indexing done:Indexing should be done properly in database.

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

18.What is Optimizer?

Answer:

Optimizer is nothing but the execution of query in optimum manner.Optimizer is most efficient way of processing the query.SQL parser ,SQL Optimizer and source code generator compiles the SQL statement.

19.What is latest version of Oracle?

Answer:

Oracle 18c is latest version of oracle which is worlds first autonomous database.

20.What are advantages of Oracle 18c?

Answer :

Following are different advantages of Oracle 18c :

• Oracle 18c uses adaptive machine learning which will reduce the human errors.
• It reduces the complexity of database
• It is highly relible and secure database
• It reduces the operational cost
• Self Driving database
• Self Tuning database
• Most efficient consumption of resources
• Less human intervention

21.What is SQL?How SQL is helpful in your project?(100% asked Database interview Questions )

Answer:

SQL Stands for Structured Query Language which is specially desinged to communicate with databases.SQL pronounced as Sequel is very widely used language in most of the database management systems like Oracle,Mysql,Postgresql etc.SQL provides us  a simple and efficient way of reading,writing,executing the data from the system.

SQL is helpful from starting of development to testing in our project. We are using backend technology as oracle so we need to use SQL for any data management.So that SQL is used in every SDLC(Software Development Life Cycle Steps) except requirement gathering phase in our project.

22.What is difference between joins and union?

Answer:

SQL joins are nothing but fetching the records where 2 or more tables having the similar condition.

Example:

Suppose we want to fetch Employee name from Employee table and Department name from department table where common condition is dept_no.

Select E.Employee_name,D.Department_name from Employee E,Department D where E.dept_no=d.dept_no;

CLICK HERE TO GET MORE INFORMATION ABOUT JOINS IN SQL

Union is used to create distinct dataset from two similar datasets.Union does not require any joining condition but the basic required thing is similar kind of dataset where all columns from 2 datasets  have same datatype and same number of columns.

CLICK HERE TO GET INFORMATION ABOUT UNION

Example:

Select * from Employee1;

Union

Select * from Employee2;

23.What is Self join?Why it is required?

Answer:

Self join is nothing but Act of joining one table with itself.

Self join is useful in converting hierarchical data structure to flat data structure.

Using Self join you may achieve 2 different functionalities at a same time.

Real Life Example:

The simple and most common example is if in same Employee table we are storing manager id also.We need to fetch the Employee and its manager name then following query is used:

Select E.Employee_name,M.Employee_name as Manager_name

From Employee E,Employee M

where E.Manager_ID=M.Manager_ID;

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

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

Hope This article will useful for everyone.If you like this article or if you have any suggestions regarding this article kindly comment in comment section.