Interview Questions and Answers for 'Database' | Search Interview Question

Interview Questions and Answers - Order By Rating

Q91. What is a Database Procedure ?

Ans. Stored procedures are a batch of SQL statements along with programming constructs ( if else, loops etc ) and stored as a single program that can be called by different clients and hence reused.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve database stored procedure Asked in 2 Companies basic frequent}

Q92. What is the purpose of Commit and Rollback in Databases ?

Database

Ans. When a DML is executed, the changes only stays in session and still not pushed to DB Tables, Commit is used to push those changes to the Tables.

In case we realize that we don't want to commit those changes and would like to ignore them, we can use rollback.

For example - You may like that for a banking transaction you would like to update the account balance only if the debit or credit record was correctly inserted, so you may like to encapsulate both DML's - insert for transaction and update for balance in a single transaction and would only commit if both succeeds else rollback.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve databases commit rollback Asked in 2 Companies Basic}

Q93. What are some alternates to keeping Audit Tables ?

Database

Ans. Audit Tables generally stores Raw information to be reviewed in case of problems or determining impact. If Database space is an issue , and the audit information is rarely retrieved, one design could be to use compressed file system.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve databases audit tables raw transaction information storage Asked in 1 Companies}

Q94. When do we generally get the database error - Unique Constraint Violated ?

Ans. This error can result either on an insert, update or delete when any change in data results in duplicate record or subset of it having unique constraint on record or its subset. For example - If we have a unique constraint on a combination of columns and then trying to insert duplicate column values.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve database Unique Constraint error Unique Constraint violated}

Q95. When do we generally get this database error - integrity constraint violated - parent key not found ?

Ans. This error most likely will be thrown during an insert statement, while inserting a value within a foreign key column which doesnt exist within the Parent column. For example - Trying to add a dept number reference within a Employee Table when the Dept doesnt exist in the Dept Table.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve database integrity constraint error integrity constraint violated}

Q96. What is a self Join and give an example of a self Join ?

or

What is self Join and What is it's purpose ?

Database

Ans. When a Table Join itself , it's a Self Join. For example - we like to know the pair of department names where first dept has lesser employees than the later.

Select D1.name , D2.name from Dept D1, Dept D2 where D1.employee_count < D2.employee_count

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql joins self join self-join Pair of employee names with first having lesser salary than later Pair of department names where first dept has lesser employees than the later Asked in 26 Companies basic frequent}

Q97. What is CRUD ?

Database

Ans. CRUD stands for "Create, read, update and delete" are the basic functions of persistence.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve CRUD Persistence Database Database Operations Asked in 3 Companies Basic frequent}

Q98. What is JDBC? Describe the steps needed to execute a SQL query using JDBC.

Database

Ans. The JDBC is a pure Java API used to execute SQL statements. It provides a set of classes and interfaces that can be used by developers to write database applications.

The steps needed to execute a SQL query using JDBC:

1. Open a connection to the database.
2. Execute a SQL statement.
3. Process th results.
4. Close the connection to the database.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve java jdbc db connectivity Asked in 2 Companies basic frequent}

Q99. What is connection pooling?

Database

Ans. It's a technique to allow multiple clients to make use of a cached set of shared and reusable connection objects providing access to a database or other resource.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve java jdbc db connectivity connection pooling architecture Asked in 3 Companies basic frequent}

Q100. Difference between Inner and Outer Join ?

Database

Ans. Inner join is the intersection of two tables on a particular columns whereas Outer Join is the Union of two tables.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql joins inner join outer join Asked in 4 Companies basic frequent}

Q101. What is a Cursor ?

Database

Ans. It's a facility that allows traversal over the records pulled from a table or combination of tables. Its like iterator in Java.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve databases sql cursors packages Asked in 5 Companies basic frequent}

Frequently asked to experienced developers. Recently asked in many US interviews.

Q102. What is database deadlock ? How can we avoid them?

Database

Ans. When multiple external resources are trying to access the DB locks and runs into cyclic wait, it may makes the DB unresponsive.

Deadlock can be avoided using variety of measures, Few listed below -

Can make a queue wherein we can verify and order the request to DB.

Less use of cursors as they lock the tables for long time.

Keeping the transaction smaller.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve java database architecture Asked in 7 Companies expert frequent}

Q103. What are temp tables ?

Ans. These are the tables that are created temporarily and are deleted once the Stored Procedure is complete.

For example - we may like to pull some info from a table and then do some operations on that data and then store the output in final output table. We can store the intermediary values in a temp table and once we have final output with us, we can just delete it.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve database sql temp tables tables}

Q104. Does SQL allow null values ? Can we use it within Where clause ?

Ans. Yes , we can have null values for columns in SQL. Null value represent that the columns value is unknown or haven't been filled. Yes, We can use it within where clause to get the rows with null values.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql sql null values sql where clause databases}

Q105. What things you would care about to improve the performance of Application if its identified that its DB communication that needs to be improved ?

Solution

Ans. 1. Query Optimization ( Query Rewriting , Prepared Statements )

2. Restructuring Indexes.

3. DB Caching Tuning ( if using ORM )

4. Identifying the problems ( if any ) with the ORM Strategy ( If using ORM )

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve java db database hibernate orm at&t overstock.com performance improvement architecture technical lead architect intermediate}

Q106. Write an SQL to find all records having all upper case alphanumeric characters in a field ?

Ans. Select * from Table where field = upper(field)

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql database}

Q107. Write an SQL to find all records having all numeric characters in a field ?

Ans. Select * from Table where isnumeric(field) = 1;

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql database}

Q108. If you are given a choice to implement the code to either Insert a Record or Update if already exist, Which approach will you follow ?

1. Insert into the DB Table. If exception occurs, update the existing record.
2. Check if the record exists and update it if it exists, If not insert a new record.

Solution

Ans. In first case, there would be 2 DB calls in worst case and 1 in best case. In 2nd approach there will be always 2 DB calls.

Decision on the approach should depend on the following considerations -

1. How costly is the call to DB ? Are we using indices , hibernate etc

If calls to DB are costly , 1st approach should be the choice.

2. Exception Book keeping load upon exception.

The benefit of saving 1st call in approach 1 should be bigger than the Book keeping for the exception.

3. Probability of the exception in first apparoach.

If the DB Table is almost empty, it makes sense to follow Approach 1 as majority of the 1st calls will pass through without exception.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve database insert-update db exceution plan db strategy design architecture technical lead}

Q109. Do you know of any DB statement that can be used if we would like to insert multiple rows into a table ?

Database

Ans. We can use INSERT ALL.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve database insert dml statement sql}

Q110. What is the advantage of JPA ?

Database

Ans. Its a specification that guides the implementation of ORM frameworks. Implementations abiding by the specification would mean that one can be replaced with other in an application without much hassle. Only the Features that are added over the specification needs to be taken care of if any such change is made.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve jpa hibernate orm technologies}

Q111. What things you will look for if you get following exception while making DB call ?

table or view does not exist

Database

Ans. First will check if the table or view actually exist in the DB

If it does , Will make sure to see that the application has rights on the schema that holds the respective Table.

Will then make sure that we have prefixed the schema with the table name while accessing it.

Will then make sure that its not DB Cache that's causing it as the table DDL might have been created recently.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql database oracle technical lead}

Q112. What's the benefit for specifying constraints like Not Null , Primary Key explicitly instead of specifying it against the column ?

Database

Ans. In case we specify them explicitly we can have control over constraint name as otherwise they will be system generated. It provides an ease in case we plan to drop the constraint in future.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql database oracle}

Q113. How can we disable a constraint ?

Database

Ans. alter table
table_name
DISABLE constraint
constraint_name;

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql database oracle}

Q114. Can we have foreign key reference to a non primary key column ?

Database

Ans. Yes, but the respective key in the foreign table should be declared Unique.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql database oracle Asked in 2 Companies}

Q115. What should be done for auto generating primary key id in a table ?

Database

Ans. We need to create a sequence.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql database oracle}

Q116. Which constraint cannot be specified as an explicit constraint and should be specified with the column only ?

Database

Ans. NOT NULL

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql oracle database ddl constraint table creation}

Q117. How to know the structure of a Table in Oracle ?

Database

Ans. DESC

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql database oracle}

Q118. How to know the constraints on a Table in Oracle ?

Database

Ans. SELECT *
FROM user_constraints
WHERE table_name = ''

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql database oracle}

Q119. How to know the default column value of a Table in Oracle ?

Database

Ans. Select DATA_DEFAULT
from DBA_TAB_COLUMNS
where TABLE_NAME = '' and COLUMN_NAME='';

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve sql database oracle}

Q120. What is the use of @GeneratedValue annotation in Hibernate?

Hibernate

Ans. This annotation is added to the auto increment column with the strategy to increment the column value. Usually this is added to the surrogate primary key column and specified with the Database Sequence.

Help us improve. Please let us know the company, where you were asked this question :

_{Like Discuss Correct / Improve hibernate hibernate annotations @generatedvalue database sequence}

Search Interview Questions

Help us and Others Improve. Please let us know the questions asked in any of your previous interview.