Top Most Top 10 Oracle Interview Question and Answer - Database and SQL

These are some interview question and answer asked during my recent interview. Oracle interview questions are very important during any programming job interview. Interviewer always want to check how comfortable we are with any database either we go for Java developer position or C, C++  programmer position .So here I have discussed some basic question related with oracle database. Apart from these questions which is very specific to Oracle database you may find some general questions related to database fundamentals and SQL e.g. Difference between correlated and noncorrelated subquery in database  or truncate vs delete in SQL etc. Some of the most important topics in Oracle Interview questions are SQL, date, inbuilt function, stored procedure and less used features like cursor, trigger and views. These questions also gives an idea about formats of questions asked during Oracle Interview.

Read more »

Top Most Difference between Primary key vs Foreign key in table – SQL database tutorial

Main difference between Primary key and Foreign key in a table is that, it’s the same column which behaves as primary key in parent table and as foreign key in child table. For example in Customer and Orderrelationship, customer_id is primary key in Customer table but foreign key in Order table. By the way what is foreign key in a table and difference between Primary and Foreign key are some of the popular SQL interview questions, much like truncate vs delete in SQL or difference between correlated and noncorrelated subquery. We have been learning key SQL concepts along with these frequently asked SQL questions and in this SQL tutorial we will discuss about what is foreign key in SQL and purpose of foreign key in any table. By the way this is the third article related to primary key in SQL, other being difference between primary and unique key and How to find second highest salary in SQL. If you are preparing for any technical job interview where you expect some SQL questions, check out these questions, they are worth preparing.

Read more »

Top Most SQL Query to find all table names on database in MySQL and SQL Server Examples

How do you find names of all tables in a database is a recent  SQL interview questions asked to one of my friend. There are many ways to find all table names form any database like MySQL and SQL Server. You can get table names either from INFORMATION_SCHEMA or sys.tables based upon whether you are using MySQL or Sql Server database. This is not a popular question like when to use truncate and delete or correlated vs noncorrelated subquery which you can expect almost all candidate prepare well but this is quite common if you are working on any database e.g. MySQL. In this SQL tutorial we will see examples of getting names of all tables from MySQL and SQL Server database. In MySQL there are two ways to find names of all tables, either by using "show" keyword or  by query INFORMATION_SCHEMA. In  case of SQL Server or MSSQL, You can either use sys.tables or INFORMATION_SCHEMA to get all table names for a database. By the way if you are new in MySQL server and exploring it , you may find this list of frequently used MySQL server commands handy.

Read more »

Top Most What is Referential Integrity in Database or SQL - MySQL Example Tutorial

Referential Integrity is set of constraints applied to foreign key which prevents entering a row in child table (where you have foreign key) for which you don't have any corresponding row in parent table i.e. entering NULL or invalid foreign keys. Referential Integrity prevents your table from having  incorrect or incomplete relationship e.g. If you have two tables Order and Customer where Customer is parent table with primary key customer_id and Order is child table with foreign key customer_id. Since as per business rules you can not have an Order without a Customer and this business rule can be implemented using referential integrity in SQL on relational database. Referential Integrity will cause failure on any INSERT or UPDATE SQL statement changing value of customer_id in child table, If value of customer_id is not present in Customer table. By the way What is Referential Integrity in SQL is also an important SQL question similar to finding second highest salary in SQL or difference between truncate and delete  and should be prepared well before going for any job interview, where knowledge of SQL is one of the requirement.

Read more »

Top Most Oracle Pagination SQL query example for Java programmer

Many time we need SQL query which return data page by page i.e. 30 or 40 records at a time, which can be specified as page size. In fact Database pagination is common requirement of Java web developers, especially dealing with largest data sets.  In this article we will see how to query Oracle 10g database for pagination or how to retrieve data using paging from Oracle. Many Java programmer also use display tag for paging in JSP which supports both internal and external paging. In case of internal paging all data is loaded in to memory in one shot and display tag handles pagination based upon page size but it only suitable for small data where you can afford those many objects in memory. If you have hundreds of row to display than its best to use external pagination by asking databaseto do pagination. In pagination, ordering is another important aspect which can not be missed. It’s virtually impossible to sort large collection in Java using Comparator or Comparablebecause of limited memory available to Java program, sorting data in database using ORDER BY clause itself is good solution while doing paging in web application.

Read more »

Top Most SQL query to add, modify and drop column in table with default value NOT NULL constraint – MySQL database

How to add column in existing table with default value is another popular SQL interview question asked for Junior level programming job interviews. Though syntax of SQL query to add column with default value varies little bit from database to database, it always been performed using ALTER keyword of ANSI SQL. Adding column in existing table in MySQL database is rather easy and straight forward and we will see example of SQL query for MySQL database which adds a column with default value. You can also provide constraints like NULL or NOT NULL while adding new column in table. In this SQL tutorial  we are adding third column in a table called Contacts which contains name and phone of contacts. Now we want to add another column email with default value "abc@yahoo.com". We will use ALTER command in SQL to do that. By the way this is next in our SQL tutorials e.g. How to join three tables in SQL and SQL query to find duplicate records in table. If you haven’t read them yet, then you may find them useful.

Read more »

Top Most Database Interview Questions for Java Developers

'Java database questions' or 'Database interview questions for java developer' is covered in this blog. All enterprise applications uses RDBS to persist data and latter use them for business processing . All java developer must looked into database to figure out the issues and persistance of business data. The expectation from java developer is - they should able to analysis the application data issues and perform basic database operation to support and develop the better application. 

Database Questions - Part 2

What is Database Partitioning?

Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.

Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.

Partitioned Index-Organized Tables

You can range partition index-organized tables. This feature is very useful for providing improved manageability, availability and performance for index-organized tables. In addition, data cartridges that use index-organized tables can take advantage of the ability to partition their stored data. Common examples of this are the Image and interMedia cartridges.

For partitioning an index-organized table:

• Only range and hash partitioning are supported
• Partition columns must be a subset of primary key columns
• Secondary indexes can be partitioned -- locally and globally
• OVERFLOW data segments are always equipartitioned with the table partitions.

Oracle provides the following partitioning methods:

• Range Partitioning
• List Partitioning
• Hash Partitioning
• Composite Partitioning

Example:

create table Book (ID number not null, 

 TITLE varchar2TITLE varchar2(40),

PUBID charPUBID char(3),

PUBDATE datePUBDATE date,

constraint lab6_pk primary key(ID))

Partition by range (pubdate)(Partition 

p1 values less than (to_date('01-JAN-2000','DD-MON-YYYY')),

Partition p2 values less than (to_date('01-JAN-2010','DD-MON-YYYY')),

Partition p3 values less than (MAXVALUE)

)

  You would insert and select data just the same way that you would for a non-partitioned table

INSERT INTO Book( id, title, pubid, pubdate )VALUES( 1, 'Something', 'FOO', sysdate );

SELECT rowid, id, title, pubid, pubdat FROM Book;

Explain Database Pessimistic and Optimistic locking?

Transactional isolation is usually implemented by locking whatever is accessed in a transaction. There are two different approaches to transactional locking:

1.  Pessimistic locking
2.  Optimistic locking

      The disadvantage of pessimistic locking is that a resource is locked from the time it is first accessed in a transaction until the transaction is finished, making it inaccessible to other transactions during that time. If most transactions simply look at the resource and never change it, an exclusive lock may be overkill as it may cause lock contention, and optimistic locking may be a better approach. With pessimistic locking, locks are applied in a fail-safe way. In the banking application example, an account is locked as soon as it is accessed in a transaction. Attempts to use the account in other transactions while it is locked will either result in the other process being delayed until the account lock is released, or that the process transaction will be rolled back. The lock exists until the transaction has either been committed or rolled back.

      With optimistic locking, a resource is not actually locked when it is first is accessed by a transaction. Instead, the state of the resource at the time when it would have been locked with the pessimistic locking approach is saved. Other transactions are able to concurrently access to the resource and the possibility of conflicting changes is possible. At commit time, when the resource is about to be updated in persistent storage, the state of the resource is read from storage again and compared to the state that was saved when the resource was first accessed in the transaction. If the two states differ, a conflicting update was made, and the transaction will be rolled back. This is very important for application performance to use Optimistic locking.

Difference in Inner join and  Outer join?

Inner Joins

   An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.

Outer Joins

   An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

• To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.
• To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.

To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.

Why Group by is only used with having clause and  aggregative functions example?

HAVING is used to perform an action on groups created by GROUP BY similar to that of the WHERE clause on rows in a basic SQL statement. The WHERE clause limits the rows evaluated. The HAVING clause limits the grouped rows returned.

• AVG: returns the variance or variability of an expression.
• COUNT: returns the number of rows returned by a query.
• FIRST: returns the first value from an ordered sequence.
• LAST: returns the last value from an ordered sequence.
• MAX: returns the maximum value of a column.
• MIN: returns the minimum value of a column.
• STDDEV: returns the standard deviation of a set of numbers.
• SUM: returns the sum or total the values of a column.
• VARIANCE: returns the variance or variability of an expression

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function.  Example of HAVING and WHERE in one query:

SELECT titles.pub_id, AVG( titles.price) FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id WHERE publishers.state = 'CA' GROUP BY titles.pub_id HAVING  AVG(titles.price)  > 10;

Sometimes you can specify the same set of rows using either a WHERE clause or a HAVING clause. In such cases, one method is not more or less efficient than the other. The optimizer always automatically analyzes each statement you enter and selects an efficient means of executing it. It is best to use the syntax that most clearly describes the desired result. In general, that means eliminating undesired rows in earlier clauses.

What is a Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What is a Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be considered to be similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself; so when the trigger is fired because of data modification, it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

What are the Different Types of Triggers?

There are two types of Triggers.

1)      DML Trigger

There are two types of DML Triggers

    1.Instead of Trigger
        Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete.

    2. After Trigger
        After triggers execute following the triggering action, such as an insert, update, or delete.

2)      DDL Trigger

This type of trigger is fired against Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always After Triggers.

What is a View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does the data in the view as views are the way to look at parts of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is an Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan, the SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

What is a Cursor?

A cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor, we need to perform some steps in the following order:

• Declare cursor
• Open cursor
• Fetch row from the cursor
• Process fetched row
• Close cursor
• Deallocate cursor.

What is the Difference between a Function and a Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

What are Different Types of Join?

Cross Join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Inner Join

    A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.

Outer Join

• A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:

• Left Outer Join: In Left Outer Join, all the rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.  
• Right Outer Join: In Right Outer Join, all the rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
• Full Outer Join: In Full Outer Join, all the rows in all joined tables are included, whether they are matched or not.

Self Join

    This is a particular case when one table joins to itself with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.

What is the Correct Order of the Logical Query Processing Phases?

The correct order of the Logical Query Processing Phases is as follows:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. TOP
11. ORDER BY

What are the Difference between Clustered and a Non-clustered Index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore, the table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What are the Different Index Configurations a Table can have?

A table can have one of the following index configurations:

• No indexes
• A clustered index
• A clustered index and many non-clustered indexes
• A non-clustered index
• Many non-clustered indexes

What’s the Difference between a Primary Key and a Unique Key?

Both primary key and unique key enforce uniqueness of the column on which they are defined. But by default, the primary key creates a clustered index on the column, whereas unique key creates a non-clustered index by default. Another major difference is that primary key doesn’t allow NULLs, but unique key allows one NULL only.

What is Difference between DELETE  and TRUNCATE Commands?

Delete command removes the rows from a table on the basis of the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table, and there will be no data in the table after we run the truncate command.

TRUNCATE

• TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. (Read all the points below)
• TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
• TRUNCATE removes all the rows from a table, but the table structure, its columns, constraints, indexes and so on remains. The counter used by an identity for new rows is reset to the seed for the column.
• You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
• Using T-SQL – TRUNCATE cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN … END TRANSACTION using T-SQL.
• TRUNCATE is a DDL Command.
• TRUNCATE resets the identity of the table.

DELETE

• DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
• DELETE does not reset Identity property of the table.
• DELETE can be used with or without a WHERE clause
• DELETE activates Triggers if defined on table.
• DELETE can be rolled back.
• DELETE is DML Command.
• DELETE does not reset the identity of the table.

What are Different Types of Locks?

• Shared Locks: Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
• Update Locks: Used on resources that can be updated. It prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
• Exclusive Locks: Used for data-modification operations, such as INSERT, UPDATE, or DELETE. It ensures that multiple updates cannot be made to the same resource at the same time.

What are Pessimistic Lock and Optimistic Lock?

Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn’t changed before you write the record back. If the record is dirty (i.e. different version to yours), then you abort the transaction and the user can re-start it.

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks.

When is the use of UPDATE_STATISTICS command?

This command is basically used when a large amount of data is processed. If a large amount of deletions, modifications or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

What is the Difference between a HAVING clause and a WHERE clause?

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query, whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What is CHECK Constraint?

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

What is NOT NULL Constraint?

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

What is the difference between UNION and UNION ALL?

UNION
   The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
     The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between UNION and UNION ALL is that UNION ALL will not eliminate duplicate rows, instead it just pulls all rows from all the tables fitting your query specifics and combines them into a table.

What is B-Tree?

The database server uses a B-tree structure to organize index information. B-Tree generally has following types of index pages or nodes:

• Root node: A root node contains node pointers to only one branch node.
• Branch nodes: A branch node contains pointers to leaf nodes or other branch nodes, which can be two or more.
• Leaf nodes: A leaf node contains index items and horizontal pointers to other leaf nodes, which can be many.

What are the Advantages of Using Stored Procedures?

• Stored procedure can reduced network traffic and latency, boosting application performance.

• Stored procedure execution plans can be reused; they staying cached in SQL Server’s memory, reducing server overhead.

• Stored procedures help promote code reuse.

• Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.

• Stored procedures provide better security to your data.

What is BCP? When is it Used?

BCP or BulkCopy is a tool used to copy huge amounts of data from tables and views. BCP does not copy the complete structures from source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

What is SQL Injection? How to Protect Against SQL Injection Attack?

SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.

Here are few methods which can be used to protect again SQL Injection attack:

• Use Type-Safe SQL Parameters
• Use Parameterized Input with Stored Procedures
• Use the Parameters Collection with Dynamic SQL
• Filtering Input parameters
• Use the escape character in LIKE clause
• Wrapping Parameters with QUOTENAME() and REPLACE()

What is CHECKPOINT Process in the SQL Server?

CHECKPOINT process writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk.

What is a Hint?

Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query

There are three different types of hints. Let us understand the basics of each of them separately.

Join Hint

This hint is used when more than one table is used in a query. Two or more tables can be joined using different types of joins. This hint forces the type of join algorithm that is used. Joins can be used in SELECT, UPDATE and DELETE statements.

Query Hint

This hint is used when certain kind of logic has to be applied to a whole query. Any hint used in the query is applied to the complete query as opposed to a part of it. There is no way to specify that only a certain part of a query should be used with the hint. After any query, the OPTION clause is specified to apply the logic to this query. A query always has any of the following statements: SELECT, UPDATE, DELETE, INSERT or MERGE (SQL 2K8); and this hint can be applied to all of them.

More Database Questions - Part 2

Top Most How to create auto incremented identity column in SQL Server, MySQL, Sybase and Oracle ?

Automatic incremented ID, Sequence or Identity columns are those columns in any table whose value is automatically incremented by database based upon predefined rule. Almost all databases e.g. Microsoft SQL Server, MySQL, Oracleor Sybase supports auto incremented identity columns but in different ways like Oracle provides SEQUENCE object which can be used to generate automatic numbers, Microsoft SQL Server upto 2008 version provides IDENTITY() functions for similar purpose. Sybase also has IDENTITY function but little different than SQL Server and MySQL uses auto_incremented keyword to make any numeric column auto incremented. As first normal form advised about primary keys which is used to uniquely identity row and if there is no natural column or combination of column exists to act as primary key, mostly database developer use auto incremented surrogate keys which is used to uniquely identify each row. In this SQL tutorial we will see how to generate auto incremented ID column in Microsoft SQL Server, Oracle 11g, MySQL and Sybase ASE Server. By the way this SQL article is continuation of my earlier post on SQL and database like difference between truncate and delete in SQL and Finding second highest salary in MySQL and SQL Server. If you haven't got chance to read them than I suggest they are worth looking.

Read more »

Top Most Database Transaction Tutorial in SQL with Example for Beginners

Database transaction is an important concept to understand while working in database and SQL. Transaction in database is required to protect data and keep it consistent when multiple users access the database at same time.  In this database transaction tutorial we will learn what is transaction in database, why do you need transaction in database, ACID properties of database transaction and an example of database transaction along with commit and rollback.   Almost all vendors like Oracle, MySQL, SQL Server or Sybase provide transaction facility though MySQL only provide it for certain storage engine like InnoDB and BDB and not for MyISAM.

Read more »

Top Most Sybase and SQL Server PATINDEX CHARINDEX Example to split String in Stored procedure

Some time we need to split a long comma separated String in Stored procedure  e.g. Sybase or SQL Server stored procedures. Its quite common to pass comma delimited or delimiter separated String as input parameter to Stored procedure and than later split comma separated String into multiple values inside stored proc. This is not just case of input parameter but you can also have comma separated string in any table data. Unfortunately there is no split() function in Sybase or SQL Server 2005 or 2008 which can directly split string based on delimiter just like in Java string split method. Fortunately Sybase Adaptive Server and Microsoft SQL server has functions like CHARINDEX and PATINDEX which can be used to split comma separated String. This is next on our SQL tutorials after seeing SQL query to find duplicate records in table and How to find 2^nd and Nth maximum salary in SQL.

Read more »

Top Most Different types of JDBC drivers in Java - Quick overview

How many types of JDBC drivers in Java is a classical JDBC interview question , though I have not see this question recently but it was very popular during 2006 - 2008 period and still asked mostly on Junior programmer level interviews. There are mainly 4 types of JDBC drivers in Java, those are referred as type 1 to type 4 jdbc drivers. I agree its easy to remember them by type rather than with there actual name, Which I have yet to get in memory except plain old JDBC-ODBC bridge driver. By the way here are there full names :

Type 1 JDBC Driver is called JDBC-ODBC Bridge driver (bridge driver)

Type 2 JDBC Driver is referred as Native-API/partly Java driver (native driver)

Type 3 JDBC Driver is called AllJava/Net-protocol driver (middleware driver)

Type 4 JDBC Driver is called All Java/Native-protocol driver (Pure java driver)

This JDBC tutorial is in continuation of my earlier tutorials in JDBC like How to connect to Oracle database using JDBC 
and  4 tips to improve performance of JDBC applications. If you are new here and haven't read them already, Its worth looking.  Anyway out of all those 4 types, JDBC-ODBC Bridge driver is most common for connecting SQL Server, MS Access and mostly on training and development. here are quick review of all these four types of JDBC drivers. Also there has been some speculation of type 5 JDBC driver, I have to yet to see it.

Read more »

Top Most How to find second highest or maximum salary of Employee in SQL - Interview question

How to find second highest or second maximum salary of an Employee is one of the most frequently asked SQL interview question similar to finding duplicate records in tableand when to use truncate vs delete. There are many ways to find second highest salary based upon which database you are using as different database provides different feature which can be used to find second maximum or Nth maximum salary of employee. Well this question can also be generalized with other scenario like finding second maximum age etc. In this SQL tutorial we will see different example of SELECT SQL query to find second highest salary independent of databases or you may call in ANSI SQL and other SQL queries which uses database specific feature to find second maximum salary.

Read more »

Top Most How to join three tables in SQL query – MySQL Example

Three table JOIN Example SQL

Joining three tables in single SQL query can be very tricky if you are not good with concept of SQL Join. SQL Joins have always been tricky not only for new programmers but for many others,  who are in programmingand SQL for more than 2 to 3 years. There are enough to confuse someone on SQL JOIN ranging from various types of SQL JOIN like INNER and OUTER join, LEFT and RIGHT outer join, CROSS join etc. Between all of these fundamentals, What is most important about Join is, combining multiple tables. If you need data from multiple tables in one SELECT query you need to use either subqueryor JOIN . Most of times we only join two tables like Employee and Department but some time you may require to join more than two tables and a popular case is joining three tables in SQL. In case of joining three tables table 1 relates to table 2 and than table 2 relates to table 3. If you look at closely you find that table 2 is a joining table which contains primary key from both table 1 and table 2. As I said it can be extremely confusing to understand join of three or more tables. I have found that understanding table relationship as primary key and foreign key helps to alleviate confusion than the classical matching row paradigm. SQL Join is also a very popular topic in SQL interviews and there is always been some questions from Join like Difference between INNER and OUTER JOIN,  SQL query with JOIN e.g. Employee Departmentrelationship and  Difference between LEFT and RIGHT OUTER JOIN etc. In short this is one of the most important topic in SQL both from experience and interview point of view.

Read more »

Top Most SQL query to copy, duplicate or backup table in MySQL, Oracle and PostgreSQL database

Many times we need to create backup or copy of tables in database like MySQL, Oracle or PostgreSQL while modifying table schema like adding new columns, modifying column or dropping columns. Since its always best to have a backup of table which can be used in any event. I was looking for an easy way to create exact copy or duplicate tables which must be same in schema as well as in data, similar to creating copy of folder. Luckily there is an easy SQL query "CREATE table table_name AS" which allows you to create exact copy of table by executing just one SQL query. Yes, you read it correctly, no tool is required to create backup of table you just need to execute an SQL query. This is simply awesome given its importance and best part of this SQL query is that it works in almost all the database. I have tested it in MySQL and Oracle but t it should work perfectly find in other databases like PostgreSQL, SQL Server and DB2 as well. This SQL query tip is in continuation of my earlier SQL query examples like SQL query to find duplicate rows in a table and SQL query to join three tables in MySQL.

Read more »

Top Most How to use truncate and delete command in SQL >> Tutorial and Example

Delete and truncate command in SQL
Truncate and delete in SQL are two commands which is used to remove or delete data from table. Though quite basic in nature both sql commands can create lot of trouble until you are familiar with details before using it. Truncate and delete are not just important to understand perspective but also a very popular interview topic which in my opinion a definite worthy topic. What makes them tricky is amount of data. Since most of Electronic trading system stores large amount of transactional data and some even maintain historical data, good understanding of delete and truncate command is required to effectively work on those environment.I have still seen people firing delete command just to empty a table with millions of records which eventually lock the whole table for doing anything and take ages to complete or Simply blew log segment or hang the machine.

Read more »

Top Most 10 Example Queries of SQL Select Command

Select command in SQL is one of the most powerful and heavily used commands. This is I guess the first command any one learn in SQL even before CREATE which is used to create table in SQL. SELECT is used in SQL to fetch records from database tables and you can do a lot many things using Select. For example you can select all records, you can select few records based on condition specified in WHERE clause, select all columns using wild card (*) or only selecting few columns by explicitly declaring them in query.

Read more »

Top Most How to find duplicate records in a table on database - SQL tips

How to find duplicate records in table is a popular SQL interview question which has been asked as many times as difference between truncate and delete in SQL or finding second highest salary of employee. Both of these SQL queries are must know for any one who is appearing on any programming interview where some questions on database and SQL are expected. In order to find duplicate records in database table you need to confirm definition of duplicates, for example in below contact table which is suppose to store name and phone number of contact, a record is considered to be duplicate if both name and phone number is same but unique if either of them varies. Problem of duplicates in database arise when you don't have a primary key or unique key on database and that's why its recommended to have a key column in table. Anyway its easy to find duplicate records in table by using group by clause of ANSI SQL. Group by clause is used to group data based upon any column or a number of columns. Here in order to locate duplicate records we need to  use group by clause on both name and phone as shown in second SQL SELECT query example. You can see in first query that it listed Ruby as duplicate record even though both Ruby have different phone number because we only performed group by on name. Once you have grouped data you can filter out duplicates by using having clause. Having clause is counter part of where clause for aggregation queries. Just remember to provide temporary name to count() data in order to use them in having clause.

Read more »

Top Most JDBC and JAVA DB Integration interview questions

This Topic is always asked by old timer java developer as few years back Java was focusing on how to best communicate this Database and almost most of the application were facing performance issue with database query execution. Focus on few code snipet and basic understanding of jdbc driver should help you to clear this topic
--------------------------------------------------------------------------------------------------------------------------------------
Java Database Connectivity API contains commonly asked Java interview questions. A good understanding of JDBC API is required to understand and leverage many powerful features of Java technology. Here are few important practical questions and answers which can be asked in a Core Java JDBC interview.

These are the most commonly asked questions:-

1) what is Index and difference with cluster index?

Non-clustered

The data is present in arbitrary order, but the logical ordering is specified by the index. The data rows may be spread throughout the table regardless of the value of the indexed column or expression. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the record (page and the row number in the data page in page-organized engines; row offset in file-organized engines).
In a non-clustered index:

• The physical order of the rows is not the same as the index order.
• Typically created on non-primary key columns used in JOIN, WHERE, and ORDER BY clauses.

There can be more than one non-clustered index on a database table.

Clustered

Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.
Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s). Create an object where the physical order of rows is the same as the index order of the rows and the bottom (leaf) level of clustered index contains the actual data rows.
Good read on index : http://www.orafaq.com/node/1403


2) DB query performance and optimization?
Among the various ways to tune the performance of a query are:

• Rewriting the query. There is no one way to write a query. If you find that a query with an OUTER JOIN is taking a long time, rewrite it using an INNER JOIN. Or perhaps the WHERE clause is written in such a way that it is causing excessive database read operations.
• Normalizing or de-normalizing tables. While normalizing tables results in ideal database design, it often leads to poor querying. If you find that frequently-used queries have to span too many normalized tables, consider some duplication of data. For example, in the Northwind database, the Unit Cost column is present in both the Order Details and Products table.
• Adding indexes. Lack of well-defined indexes is a frequent cause of queries taking a long time to execute. Creating indexes will cause a query to lookup the index rather than the table.
• Removing indexes. In some cases, it is a poorly defined index that is the cause of slow query execution. For example, an index which does not include the frequently looked up columns in a table. In such a case, it is better to drop the index and recreate it.

3) Benefits of prepared statement?
DB execution happens in this steps
  1.Parse the incoming SQL query
  2. Compile the SQL query
  3. Plan/optimize the data acquisition path
  4. Execute the optimized query / acquire and return data
- statement perform step 1-4 to do the each task and prepared statement will pre execute the 1-3 and perform only 4 for each task.
- prepared statements are safe as they safeguard against sql injection on using direct query.
- We can bind non standard java objects  like blob etc in prepared statement, but not in statement.


4) DB execute plan and how to use it?

5) Foreign key and primary key difference?

6) Materialized view in database?


7) Truncate and delete difference?

8) How to design table?

9) Database transaction management?

10) what is cursor and trigger in dB?

These are very standard API question in interview:

What are available drivers in JDBC

JDBC technology drivers fit into one of four categories:

1. A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers. Note that some ODBC native code and in many cases native database client code must be loaded on each client machine that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading of a Java technology application is not important. For information on the JDBC-ODBC bridge driver provided by Sun, see JDBC-ODBC Bridge Driver.
2. A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
3. A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products.
4. A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the 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 style of driver. Several database vendors have these in progress.

What are the types of statements in JDBC?

           The JDBC API has 3 Interfaces, (1. Statement, 2. PreparedStatement, 3. CallableStatement ). The     key features of these are as follows:
              Statement

·         This interface is used for executing a static SQL statement and returning the results it produces.

·         The object of Statement class can be created using Connection.createStatement() method.

             PreparedStatement

o A SQL statement is pre-compiled and stored in a PreparedStatement object.

o This object can then be used to efficiently execute this statement multiple times.

o The object of PreparedStatement class can be created using Connection.prepareStatement() method. This extends Statement interface.

              CallableStatement

o This interface is used to execute SQL stored procedures.

o This extends PreparedStatement interface.

o The object of CallableStatement class can be created using Connection.prepareCall() method.

What is a stored procedure? How to call stored procedure using JDBC API?

               Stored procedure is a group of SQL statements that forms a logical unit and performs a particular task. Stored Procedures are used to encapsulate a set of operations or queries to execute on database. Stored procedures can be compiled and executed with different parameters and results and may have any combination of input/output parameters. Stored procedures can be called using CallableStatement class in JDBC API. Below code snippet shows how this can be achieved.

0. CallableStatement cs = con.prepareCall("{call MY_STORED_PROC_NAME}");

1. ResultSet rs = cs.executeQuery();

CallableStatement cs = con.prepareCaWhat is Connection pooling? What are the advantages of using a connection pool?

Connection Pooling is a technique used for sharing the server resources among requested clients. It was pioneered by database vendors to allow multiple clients to share a cached set of connection objects that provides access to a database.

Getting connection and disconnecting are costly operation, which affects the application performance, so we should avoid creating multiple connection during multiple database interactions. A pool contains set of Database connections which are already connected, and any client who wants to use it can take it from pool and when done with using it can be returned back to the pool.

Apart from performance this also saves you resources as there may be limited database connections available for your application.

How to do database connection using JDBC thin driver ?

This is one of the most commonly asked questions from JDBC fundamentals, and knowing all the steps of JDBC connection is important.

0. import java.sql.*;

1. class JDBCTest {

2. public static void main (String args []) throws Exception

3. {

4. //Load driver class

5. Class.forName ("oracle.jdbc.driver.OracleDriver");

6. //Create connection

7. Connection conn = DriverManager.getConnection

8. ("jdbc:oracle:thin:@hostname:1526:testdb", "scott", "tiger");

9. // @machineName:port:SID, userid, password

10.

11. Statement stmt = conn.createStatement();

12. ResultSet rs = stmt.executeQuery("select 'Hi' from dual");

13. while (rs.next())

14. System.out.println (rs.getString(1)); // Print col 1 => Hi

15. stmt.close();

16. }

17. }

What does Class.forName() method do?
Method forName() is a static method of java.lang.Class. This can be used to dynamically load a class at run-time. Class.forName() loads the class if its not already loaded. It also executes the static block of loaded class. Then this method returns an instance of the loaded class. So a call to Class.forName('MyClass') is going to do following

- Load the class MyClass.

- Execute any static block code of MyClass.

- Return an instance of MyClass.

JDBC Driver loading using Class.forName is a good example of best use of this method. The driver loading is done

0. Class.forName("org.mysql.Driver");

All JDBC Drivers have a static block that registers itself with DriverManager and DriverManager has static initializer method registerDriver() which can be called in a static blocks of Driver class. A MySQL JDBC Driver has a static initializer which looks like this:
          1. static {

2. try {

3. java.sql.DriverManager.registerDriver(new Driver());

4. } catch (SQLException E) {

5. throw new RuntimeException("Can't register driver!");

6. }

7.

Class.forName() loads driver class and executes the static block and the Driver registers itself with the DriverManager.

Which one will you use Statement or PreparedStatement? Or Which one to use when (Statement/PreparedStatement)? Compare PreparedStatement vs Statement.

By Java API definitions:
Statement is a object used for executing a static SQL statement and returning the results it produces.
PreparedStatement is a SQL statement which is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
There are few advantages of using PreparedStatements over Statements

       1. Since its pre-compiled, Executing the same query multiple times in loop, binding different parameter values each time is faster. (What does pre-compiled statement means? The prepared statement(pre-compiled) concept is not specific to Java, it is a database concept. Statement precompiling means: when you execute a SQL query, database server will prepare a execution plan before executing the actual query, this execution plan will be cached at database server for further execution.)

       2. In PreparedStatement the setDate()/setString() methods can be used to escape dates and strings properly, in a database-independent way.

        3. SQL injection attacks on a system are virtually impossible when using PreparedStatements 

What does setAutoCommit(false) do?

A JDBC connection is created in auto-commit mode by default. This means that each individual SQL statement is treated as a transaction and will be automatically committed as soon as it is executed. If you require two or more statements to be grouped into a transaction then you need to disable auto-commit mode using below command

0. con.setAutoCommit(false);

Once auto-commit mode is disabled, no SQL statements will be committed until you explicitly call the commit method. A Simple transaction with use of autocommit flag is demonstrated below.

1. con.setAutoCommit(false);

2. PreparedStatement updateStmt =

3. con.prepareStatement( "UPDATE EMPLOYEE SET SALARY = ? WHERE EMP_NAME LIKE ?");

4. updateStmt.setInt(1, 5000); updateSales.setString(2, "Jack");

5. updateStmt.executeUpdate();

6. updateStmt.setInt(1, 6000); updateSales.setString(2, "Tom");

7. updateStmt.executeUpdate();

8. con.commit();

9. con.setAutoCommit(true);

ccon.setAWhat are database warnings and How can I handle database warnings in JDBC?

Warnings are issued by database to notify user of a problem which may not be very severe. Database warnings do not stop the execution of SQL statements. In JDBC SQLWarning is an exception that provides information on database access warnings. Warnings are silently chained to the object whose method caused it to be reported.
Warnings may be retrieved from Connection, Statement, and ResultSet objects.
Handling SQLWarning from connection object

0. //Retrieving warning from connection object

1. SQLWarning warning = conn.getWarnings();

2.

3. //Retrieving next warning from warning object itself

4. SQLWarning nextWarning = warning.getNextWarning();

5.

6. //Clear all warnings reported for this Connection object.

7. conn.clearWarnings();

Handling SQLWarning from Statement object

8. //Retrieving warning from statement object

9. stmt.getWarnings();

10.

11. //Retrieving next warning from warning object itself

12. SQLWarning nextWarning = warning.getNextWarning();

13.

14. //Clear all warnings reported for this Statement object.

15. stmt.clearWarnings();

Handling SQLWarning from ResultSet object

16. //Retrieving warning from resultset object

17. rs.getWarnings();

18.

          19. //Retrieving next warning from warning object itself

20. SQLWarning nextWarning = warning.getNextWarning();

21.

22. //Clear all warnings reported for this resultset object.

23. rs.clearWarnings();

The call to getWarnings() method in any of above way retrieves the first warning reported by calls on this object. If there is more than one warning, subsequent warnings will be chained to the first one and can be retrieved by calling the method SQLWarning.getNextWarning on the warning that was retrieved previously.
A call to clearWarnings() method clears all warnings reported for this object. After a call to this method, the method getWarnings returns null until a new warning is reported for this object.
Trying to call getWarning() on a connection after it has been closed will cause an SQLException to be thrown. Similarly, trying to retrieve a warning on a statement after it has been closed or on a result set after it has been closed will cause an SQLException to be thrown. Note that closing a statement also closes a result set that it might have produced.

What is Metadata and why should I use it?

JDBC API has 2 Metadata interfaces DatabaseMetaData & ResultSetMetaData.
The DatabaseMetaData provides Comprehensive information about the database as a whole. This interface is implemented by driver vendors to let users know the capabilities of a Database Management System (DBMS) in combination with the driver based on JDBC technology ("JDBC driver") that is used with it. Below is a sample code which demonstrates how we can use the DatabaseMetaData

0. DatabaseMetaData md = conn.getMetaData();

1. System.out.println("Database Name: " + md.getDatabaseProductName());

2. System.out.println("Database Version: " + md.getDatabaseProductVersion());

3. System.out.println("Driver Name: " + md.getDriverName());

4. System.out.println("Driver Version: " + md.getDriverVersion());

The ResultSetMetaData is an object that can be used to get information about the types and properties of the columns in a ResultSet object. Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query, such as size and types of columns. Below a sample code which demonstrates how we can use the ResultSetMetaData

5. ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");

6. ResultSetMetaData rsmd = rs.getMetaData();

7. int numberOfColumns = rsmd.getColumnCount();

8. boolean b = rsmd.isSearchable(1);

What is RowSet? or What is the difference between RowSet and ResultSet? or Why do we need RowSet? or What are the advantages of using RowSet over ResultSet?

RowSet is a interface that adds support to the JDBC API for the JavaBeans component model. A rowset, which can be used as a JavaBeans component in a visual Bean development environment, can be created and configured at design time and executed at run time. The RowSet interface provides a set of JavaBeans properties that allow a RowSet instance to be configured to connect to a JDBC data source and read some data from the data source. A group of setter methods (setInt, setBytes, setString, and so on) provide a way to pass input parameters to a rowset's command property. This command is the SQL query the rowset uses when it gets its data from a relational database, which is generally the case.
Rowsets are easy to use since the RowSet interface extends the standard java.sql.ResultSet interface so it has all the methods of ResultSet. There are two clear advantages of using RowSet over ResultSet 

        o RowSet makes it possible to use the ResultSet object as a JavaBeans component. As a consequence, a result set can, for example, be a component in a Swing application.

        o RowSet be used to make a ResultSet object scrollable and updatable. All RowSet objects are by default scrollable and updatable. If the driver and database being used do not support scrolling and/or updating of result sets, an application can populate a RowSet object implementation (e.g. JdbcRowSet) with the data of a ResultSet object and then operate on the RowSet object as if it were the ResultSet object

What is a connected RowSet? or What is the difference between connected RowSet and disconnected RowSet? or Connected vs Disconnected RowSet, which one should I use and when?

Connected RowSet

A RowSet object may make a connection with a data source and maintain that connection throughout its life cycle, in which case it is called a connected rowset. A rowset may also make a connection with a data source, get data from it, and then close the connection. Such a rowset is called a disconnected rowset. A disconnected rowset may make changes to its data while it is disconnected and then send the changes back to the original source of the data, but it must reestablish a connection to do so.
Example of Connected RowSet:
A JdbcRowSet object is a example of connected RowSet, which means it continually maintains its connection to a database using a JDBC technology-enabled driver.

Disconnected RowSet

A disconnected rowset may have a reader (a RowSetReader object) and a writer (a RowSetWriter object) associated with it. The reader may be implemented in many different ways to populate a rowset with data, including getting data from a non-relational data source. The writer can also be implemented in many different ways to propagate changes made to the rowset's data back to the underlying data source.
Example of Disconnected RowSet:
A CachedRowSet object is a example of disconnected rowset, which means that it makes use of a connection to its data source only briefly. It connects to its data source while it is reading data to populate itself with rows and again while it is propagating changes back to its underlying data source. The rest of the time, a CachedRowSet object is disconnected, including while its data is being modified. Being disconnected makes a RowSet object much leaner and therefore much easier to pass to another component. For example, a disconnected RowSet object can be serialized and passed over the wire to a thin client such as a personal digital assistant (PDA).

What is the benefit of having JdbcRowSet implementation? Why do we need a JdbcRowSet like wrapper around ResultSet?

The JdbcRowSet implementation is a wrapper around a ResultSet object that has following advantages over ResultSet

        o This implementation makes it possible to use the ResultSet object as a JavaBeans component. A JdbcRowSet can be used as a JavaBeans component in a visual Bean development environment, can be created and configured at design time and executed at run time.

          o It can be used to make a ResultSet object scrollable and updatable. All RowSet objects are by default scrollable and updatable. If the driver and database being used do not support scrolling and/or updating of result sets, an application can populate a JdbcRowSet object with the data of a ResultSet object and then operate on the JdbcRowSet object as if it were the ResultSet object.

Adding sample programing which will help us to easily understand the jdbc connectivity with any database. Here Adding sample code for calling function and store procedure from plain java class, which required only oracle odbc 14.jar in classpath;

Table creation script:

CREATE TABLE EMPLOYEE
( ID NUMBER(10, 0) NOT NULL,
  FIRST_NAME VARCHAR2(20) NOT NULL,
  LAST_NAME VARCHAR2(20),
  SALARY NUMBER NOT NULL,
  DEPTNO NUMBER NOT NULL
, CONSTRAINT EMPLOYEE_PK PRIMARY KEY
  (ID)
  ENABLE )  ;


 CREATE TABLE DEPARTMENT
(
  DEPT_ID NUMBER NOT NULL,
  DEPT_NAME VARCHAR2(20),
  MANAGER NUMBER,
  LOCATION VARCHAR2(20)
, CONSTRAINT DEPARTMENT_PK PRIMARY KEY
  (
    DEPT_ID
  )
  ENABLE
);

Function and store procedure code

--- Procedure
CREATE OR REPLACE PROCEDURE raise_sal(i_empno NUMBER, i_newsal NUMBER)
AS
BEGIN
  UPDATE employee SET salary = i_newsal WHERE id = i_empno;
END;

--- Function

CREATE OR REPLACE FUNCTION FUNCTION_Sal (inID NUMBER) RETURN NUMBER IS
  sal NUMBER;
BEGIN
  SELECT salary INTO sal FROM employee
    WHERE id = inID;
  RETURN sal;
END; 

*** Java class to call Store Procedure

package com.db.sql;

import java.sql.*;

public class JavaSpExample {

    public static void main(String[] arg) throws SQLException{   
        Connection connection = null;   
        ResultSet resultSet = null;
        //javaStoreProcesdureCall(connection);
        Scanner sc = new Scanner (System.in);
        for(sc.hasNext();;){
          connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "Test", "test");
          javafunctionCall(connection,sc.nextInt());
        }
    }

    /** Demo for calling store procedure from java code
     */
    public static void javaStoreProcesdureCall(Connection connection
            ) {
        CallableStatement cstmt = null;
        try {
       
            System.out.println(" Starting !!  ");
       
            cstmt = connection.prepareCall("{ CALL RAISE_sal(?,?)}");
            cstmt.registerOutParameter(1, Types.INTEGER);
            cstmt.setInt(1, 1);
            cstmt.setInt(2, 7000);
            cstmt.executeUpdate();
            System.out.println(" Done !!");
            //float acctBal = cstmt.getFloat(1);
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } finally {
            try {
                cstmt.close();
                connection.close();
                // throws exception closed conection
                //java.sql.SQLRecoverableException: Closed Connection
                //cstmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            } catch (Exception exception) {
                exception.printStackTrace();
                System.exit(1);
            }
        }
    }

      /**

        *  Demo for calling function from java code
        */
    public static void javafunctionCall(Connection connection, int id
            ) {
        CallableStatement cstmt = null;
        try {       
            System.out.println(" Starting javafunctionCall !!  ");    
            cstmt = connection.prepareCall("{ ?=call FUNCTION_Sal(?) }");
            cstmt.registerOutParameter(1, Types.INTEGER);
            cstmt.setInt(2, id);
            cstmt.executeUpdate();
            System.out.println(" Done !!");
            Integer salary = cstmt.getInt(1);
            System.out.println(" Result ; "+salary);
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } finally {
            try {
                cstmt.close();
                connection.close();
                // throws exception closed conection
                //java.sql.SQLRecoverableException: Closed Connection
                //cstmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            } catch (Exception exception) {
                exception.printStackTrace();
                System.exit(1);
            }
        }
    }
}

-----------------------------------------------------------------------------------------------------------------------

Can you think of a questions which is not part of this post? Please don't forget to share it with me in comments section & I will try to include it in the list.