Databases & Jdbc Interview Questions in Java

by

We have compiled most frequently asked Java J2EE Interview Questions which will help you with different expertise levels.

Java J2EE Interview Questions on Databases & Jdbc

Question 1.
What do you understand by a database?
Answer:
A database is a permanent, self-descriptive store of data that is contained in one or more files. Self-description is what sets a database apart from ordinary files. A database contains the data structure or ‘schema’ – description of data — as well as the data.

Question 2.
What do you understand by DBMS?
Answer:
A database management system (DBMS) is the software for managing access to a database. Some of the reasons for using a DBMS are:-

  • Data Protection: A DBMS protects data from accidental loss due to hardware crashes, disk media failures, and application errors.
  • Efficiency: A DBMS has efficient algorithms for managing large quantities of data.
  • Sharing between users & sharing between applications: Multiple users can access the database at the same time. Multiple application programs can read/write data to the same database.
  • Data quality: You can specify rules that data must satisfy.
  • Data distribution: You can partition data across various sites, organizations, and hardware platforms. The DBMS keeps the fragmented data consistent.
  • Security: A DBMS can restrict the reading/writing of data to authorized users only.

Question 3.
How do you implement one-to-one, one-to-many, and many-to-many relationships while designing tables?
Answer:
The one-to-one relationship can be implemented as a single table and very rarely as two tables with primary and foreign key relationships. The one-to-many relationship can be implemented by splitting the data into two tables with primary and foreign key relationships. Many-to-many relationships can be implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

Question 4.
Bring out the difference between a primary key and a unique key.
Answer:
Both primary key and unique key enforce the uniqueness of the column on which they are defined. But, by default, the primary key creates a clustered index on the column whereas the unique key creates a non-clustered index by default. Also, the primary key doesn’t permit NULL value while the unique key permits NULL value, but only one.

Question 5.
What do you understand by a stored procedure? What are its advantages?
Answer:
A stored procedure is a set of precompiled T-SQL statements, which can be executed whenever required. These are similar to procedures in any other programming language and whence, can accept input parameters, return output parameters, and can even return status values to the calling procedure or batch to indicate success/failure along with the reason for failure, if any.

The main advantages of using stored procedures are:-

  1. Increased modularized programming;
  2. Faster execution since they are pre-compiled;
  3. Reduce the network traffic by sending the call to the stored procedure, instead of sending hundreds of T-SQL lines of code that are embedded in the stored procedure.

Question 6.
What do you mean by triggers? How do you invoke a trigger on demand?
Answer:
Triggers are a special kind of stored procedure that gets executed automatically when an INSERT/UPDATE/DELETE operation takes place on a table. Triggers cannot be invoked on demand. They get triggered automatically as mentioned already. Triggers can be used to extend the referential integrity checks also, but wherever possible, constraints should be used for this purpose instead of triggers since constraints are much faster.

Question 7.
Bring out the difference between a stored procedure and a trigger.
Answer:
The trigger is a special type of stored procedure that cannot be called directly by the user. At the time of creating the trigger, it is defined to be executed when a specific type of data modification such as Insert/Update/Delete is made against a specific table/column.

Question 8.
What do you understand by JDBC?
Answer:
JDBC or Java Database Connectivity, as it is called, is an Application Programming Interface (API), which makes it possible for Java programs to connect to any database, retrieve the data/information from the database and utilize this data for themselves.

DATABASES & JDBC Interview Questions in Java chapter 5 img 1

Question 9.
What do you understand by DSN?
Answer:
DSN or Daja Source Name, as it is called, is the unique name given to the database in order to identify it in a Java program. It is linked with the actual location of the database.

Question 10.
What do you understand by ResultSet?
Answer:
ResultSet is an object containing the rows (results) of executing an SQL statement upon a database.

Question 11.
What do you understand by stored procedures?
Answer:
A stored procedure means a set of statements stored and executed at the database server and sending the results to the client.

Question 12.
What do you understand by BLOB?
Answer:
BLOB or Binary Large Object, as it is called, represents an SQL datatype that helps in storing LARGE volumes of binary data into the database. By means of BLOBS, you can even store images in the database.

Question 13.
What do you understand by CLOB?
Answer:
CLOB or Character Large Object, as it is called, represents an SQL datatype that helps in storing LARGE volumes of text data into the database. By means of CLOBS, you can even store text files in the database.

Question 14.
Can you explain the use of the CallableStatement?
Answer:
The CallableStatement is used to call stored procedures and functions that run at a database server and get the results to the client.

Question 15.
What do you understand by parsing?
Answer:
Parsing means checking the syntax and grammar of a Java statement as a whole as well as word by word.

Question 16.
What do you understand by a database driver?
Answer:
A database driver represents a set of classes/interfaces written in accordance with the JDBC API in order to communicate with a given database.

Question 17.
How do you register a database driver? Elaborate.
Answer:
A database driver can be registered in any of the following ways:-

  1. By using System class get property( ) method;
  2. By creating an object to the driver class;
  3. By sending the driver class name to the Class.forname( ) method; or,
  4. By sending the driver class object to the Driver Manager.registerDriver( ) method.

Question 18.
Does the performance of a JDBC program depend on the driver? Elucidate.
Answer:
Yes! It does!! Each driver performs differently.

Question 19.
What do you understand by an RDBMS?
Answer:
RDBMS or Relational Database Management System, as it is called, is a way to store the information in a database, creating relationships amongst different, related data in order to enable the user to query among different factors and get the maximum range of data from a single query. To put it more simply, it’s a system that organizes data into related rows and columns.

Question 20.
What do you mean by SQL?
Answer:
SQL or Structured Query Language, as it is called, enables:-

  1. ACCESS TO A DATABASE;
  2. INSERTION/UPDATION/DELETION OF RECORDS IN A DATABASE;
  3. RETRIEVAL OF DATA/INFORMATION FROM A DATABASE.

Question 21.
What are the different types of DBMS?
Answer:
DBMS (Database Management System) is essential of the following kinds:-

  1. Hierarchical
  2. Network
  3. Relational
  4. Object
  5. Object-Relational

Of all these, Relational DBMS is the most widely used one.

Question 22.
Enumerate the main features of a relational database.
Answer:
A relational database has essentially the following features:-

  1. It makes no presumptions about the inter-relationships between the data elements;
  2. Relationships are represented in the form of one table containing key data from another table.
  3. Relations are dynamic and determined as needed.
  4. Everything in a relational database is stored in the form of tables which consist of rows and columns.
  5. The creation of these tables and their columns is performed using SQL. Similarly, storage and retrieval of data is also carried out using SQL.
  6. In formal relational theory, tables are known as relations; whence, these came to be known as relational databases.

Question 23.
What do you understand by an Entity-Relationship (E-R) diagram?
Answer:
One of the best ways to design a database is to draw an image of the tables with the relationships among them. This graphical representation of the database tables is known as an E- R diagram.

Question 24.
What do you mean by referential integrity?
Answer:
Referential integrity prevents users/applications from entering inconsistent data into a table. Referential integrity rules are used when a relationship is created between two tables. Different RDBMSs have different referential integrity rules. As already mentioned, these rules help to maintain the integrity of data, e.g. if we are dealing with a ‘customer’ table and multiple orders from a customer, we can set referential integrity rule in such a manner that all orders from a customer should get deleted before the customer gets deleted; this is commonly referred to as cascading delete.

Question 25.
What do you understand by a primary key?
Answer:
The primary key is used to uniquely identify a row of data in a table. This can be a single column of the table or a combination of more than one column; in the latter case, it’s known as a composite primary key.

Question 26.
What do you mean by a foreign key?
Answer:
A key column in a table that identifies records in a different table is referred to as a foreign key.

Question 27.
What is an alternate key in a table?
Answer:
In a database table, apart from primary key column(s), some other columns may need to be a’ key as well; these are referred to as alternate keys. This column value may or may not be unique.

Question 28.
Elucidate the concept of normalization.
Answer:
Normalization is the process of efficiently organizing data in a database by creating tables and establishing relationships between those tables according to certain normalization rules which have been designed to make the database more flexible by eliminating two factors, viz. redundancy and inconsistent dependency.

There are a few rules for database normalization. Each such rule is referred to as a ‘normal form”. If the first rule is observed, the database is said to be in “first normal form”, and so on.

Question 29.
Elucidate the First Normal Form (INF).
Answer:
INF states that:-

  • Eliminate duplicate columns from the same table;
  • Create a separate table for each set of related data and identify each such set of related data with a primary key.

Question 30.
Elucidate the Second Normal Form (2NF).
Answer:
2NF states that:-

  • Create separate tables for sets of values that apply to multiple records;
  • Create relationships between these new tables and their predecessors through the use of foreign keys.

Question 31.
Elucidate the Third Normal Form (3NF).
Answer:
3NF states that:-
Eliminate fields that do not depend on the primary key.

Question 32.
Elucidate the concept of denormalization.
Answer:
Developers can violate normal forms for good cause, such as to increase the performance of a database that is read and seldom updated. Such a relaxation is known as denormalization. The important issue here is to violate normal forms deliberately and only when necessary.

Question 33.
Elucidate the SQL Data Manipulation Language (DML).
Answer:
As previously mentioned, SQL is the syntax for executing queries and updating, inserting, and deleting records.
These query and update commands together form the DML part of SQL:-

  1. SELECT: extracts data from a database table
  2. UPDATE: updates data in a database table
  3. DELETE: deletes data from a database table
  4. INSERT INTO: inserts new data into a database table

Question 34.
Elucidate the SQL Data Definition Language (DDL).
Answer:
The DDL part of SQL provides a syntax for creating and deleting database tables. One can also define indexes (keys), specify links between tables and impose constraints between database tables. The most important DDL statements in SQL are the following:-

  1. CREATE TABLE: creates a new database table
  2. ALTER TABLE: alters (changes) a database table
  3. DROP TABLE: deletes a database table
  4. CREATE INDEX: creates an index (search key)
  5. DROP INDEX: deletes an index

Question 35.
Explain the concepts of transaction and ACID properties.
Answer:
A transaction is a unit of work in which a series of operations occur between the BEGIN TRANSACTION and END TRANSACTION statements of an application. The term ACID stands for Atomic, Consistent, Isolated, and Durable. These properties ensure predictable behavior, reinforcing the role of transactions as all-or-none propositions designed to reduce the management load when there are many variables.

Question 36.
Bring out the difference between DELETE TABLE and TRUNCATE TABLE commands.
Answer:
DELETE TABLE is a logged operation, whence, the deletion of each row gets logged in the transaction log, which makes it slow. On the other hand, TRUNCATE TABLE also deletes all the rows in a table/but it does not log the deletion of each row; instead, it logs the de-allocation of the data pages of the table, making it much faster.

Question 37.
What are constraints? Enumerate the different types of constraints.
Answer:
Constraints enable the database system to enforce the integrity of the data automatically, without the need to create triggers, rules, or defaults. The various types of constraints are:-

  1. NOT NULL (a NOT NULL constraint on a column prevents its values from being NULL)
  2. CHECK
  3. UNIQUE
  4. PRIMARY KEY
  5. FOREIGN KEY

Question 38.
What are cursors? Enumerate the different types of cursors.
Answer:
A cursor is like a virtual table, with rows and columns specified by the query. A cursor also has the notion of a current row, which in essence is a pointer to the row in use in the virtual table. The different types of cursors are:-

  1. Static
  2. Dynamic
  3. Forward-only
  4. Keyset-driven

Question 39.
What is a join? Enumerate the different types of joins.
Answer:
Joins are used in queries to explain how different tables are related. Joins also let you .select data from a table based upon data from another table. The various types of joins are:-

  1. Inner join
  2. Outer join
  3. Self-join
  4. Cross join

Question 40.
What do you mean by a self-join? Illustrate with the help of an example.
Answer:
In a self-join, two instances of the same table, are joined in the query. We illustrate this concept with the help of an example. Suppose, there is an employee table containing rows for normal employees as well as managers. To find out the managers of all the employees, you need a self-join.

CREATE TABLE employee
(
Emp_ID int,
Mgr_ID int,
__ Emp__NM char(20)
)
INSERT employee SELECT 1, 2, "Meena”
INSERT employee SELECT 2, 3, “Arunesh”
INSERT employee SELECT 3, NULL, "Harry"
SELECT tempi.Emp_NM [employee], temp2.Emp_NM [Manager]
FROM employee temp1, employee temp2
WHERE tempi.Mgr_ID = temp2.Emp_ID

Question 41.
How does JDBC help in accessing databases?
Answer:
JDBC provides a standard library for accessing relational databases. By using the JDBC (Java DataBase Connectivity) API, you can access a wide variety of SQL databases with exactly the same Java syntax. It is, however, important to point out that although the JDBC API standardizes the approach for connecting to databases,

the syntax for sending queries and v committing transactions, and the data structure representing the result, JDBC does not attempt to standardize the SQL syntax. So, you can use any SQL extensions your database vendor supports. But, since most queries follow standard SQL syntax, using JDBC lets you change database hosts, ports, and even database vendors with minimal changes to your code.

Question 42.
What are the steps involved in using JDBC in general?
Answer:
(1) Load the JDBC driver: To load a driver, you specify the class name of the database driver in the Class.forName method. By doing so, you automatically create a driver instance and register it with the JDBC driver manager.

(2) Define the connection URL: In JDBC, a connection URL specifies the server host, port, and database name with which to establish a connection.

(3) Establish the connection: With the connection URL, username, and password, a network connection to the database can be established. Once the connection is established, database queries can be performed until the connection is closed.

(4) Create a Statement object: Creating a Statement object enables you to send queries
and commands to the database.

(5) Execute a query or update: Given a Statement object, you can send SQL statements to the database by using the execute, executeQuery, executeUpdate, or execute batch methods.

(6) Process the results: When a database query is executed, a Result-Set is returned. The ResultSet represents a set of rows and columns that you can process by calls to next and various getXxx methods.

(7) Close the connection: When you are finished performing queries and processing results, you should close the connection, releasing resources to the database.

Question 43.
Enumerate and elucidate the various types of JDBC drivers.
ANSWER:

DATABASES & JDBC Interview Questions in Java chapter 5 img 2

Figure 5 Two common JDBC Driver Implementations. JDK 1.4 includes a JDBC-ODBC Bridge; however, a pure JDBC Driver (provided by the vendor) yields better performance JDBC technology drivers fit into one of the following categories according to the Sun Java developer Forum:-

Type 1: A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers. Some ODBC native code and in many cases, native database client code must be loaded on each client machine using this type of installation, and downloading a Java technology application is not important.

Type 2: A native-API partly Java technology-enabled driver converts JDBC (fails into calls on the client API for Oracle, Sybase, DB2, Informix, or other DBMS.’ Some binary code must be loaded on each client machine.

Type 3: A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then further translated to a DBMS protocol by a server. This net server middleware is able to connect all of its technology-based clients to many different databases.

The specific protocol used is vendor-dependent. In general, this is the most flexible JDBC API alternative. All vendors of this solution will most likely provide products for Intranet use. In order for such products to also support Internet access, they must handle the additional requirements for security, access through firewalls, etc. that the Web imposes.

Type 4: A native-protocol fully Java driver converts JDBC technology calls into a network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary, the database vendors themselves will be the primary source for this type/style of driver. This is the most preferred driver since it improves portability.

Question 44.
Can the ResultSet be used even after closing the connection?
Answer:
No! Not at all! This is due to the fact that the ResultSet becomes invalid once the connection is closed.

Question 45.
Can a connection object be serialized and used from another machine?
Answer:
No! Not at all!! A connection object can’t be serialized and used from another machine This is due to the fact that the connection is created for the current machine IP address.

Question 46.
How do you get hold of the column names fetched in a ResultSet?
Answer:
To retrieve information about the fetched ResultSet, you may use con.getMetaData(‘). It returns a MetaData Object; using which the various properties of the resultset can be fetched.

Question 47.
Elaborate setAutoCommit(true).
Answer:
A transaction is one or more SQL statements forming a logical unit of work. Within one transaction, all the SQL statements must succeed/fail as one logical entity.

Changes are made to the. the database only if all statements in the transaction succeed and a COMMIT is issued. If one or more statements fail, we must issue a ROLLBACK to undo the changes. This ensures the integrity and security of the data in the database.

By setting AutoCommit to false, I’ve prevented the connection from committing the changes unless the commit method is called in the program. We can COMMIT/ROLLBACK in the code depending upon whether all the statements within the transaction succeed/fail.

Question 48.
Can a ResultSet be updated?
Answer:
Yes! Of course!! We can indeed update the database values from a ResultSet.

Question 49.
How can you call a stored procedure from JDBC?
Answer:
We can invoke a stored procedure using CallableStatement as illustrated below:-
CallableStatement dblstmt = con.prepareCa11 (” {call getTestData(?, ?)}”);
clblstmt.registerOutParameterd, java.sql.Types.TINYINT);
clblstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 3);
clblstmt.executeQuery( );
byte b = dblstmt.getByte(1);
java.math.BigDecimal bd = dblstmt.getBigDecimal(2, 3);

In general, calling a stored procedure in a database involves the following six basic steps:-

(1) Define the call to the database procedure: The procedure definition includes escape syntax where the appropriate? defines input and output parameters.

(2) Prepare a CallableStatement for the procedure: You obtain a CallableStatement from a Connection by calling prepare Call.

(3) Register the output parameter types: Before executing the procedure, you must declare the type of each output parameter.

(4) Provide values for the input parameters: Before executing the procedure, you must supply the input parameter values.

(5) Execute the stored procedure: To execute the database stored procedure, call execute on the CallableStatement.

(6) Access the returned output parameters: Call the corresponding getXxx method, according to the output type.

Question 50.
What do you understand by connection pooling?
Answer:
Connection pooling is a technique that allows multiple clients to share a cached set of connection objects that provide access to a database resource. It is actually implemented in different ways by different application server vendors.

Question51.
Will the following code compile?

Give appropriate reason(s) for your answer.
     import java.sql.*;
     import java.util.*;
     class TestingltOut
    {
         Date dt = new Date(2012,10, 01);
         System.out.println(dt.toString( ));
    }

Answer:
No! Not at all!! This is due to the fact that both sql and util packages have the Date class; the compiler is in a fix and doesn’t know which one to use!!!