Incedo Interview on SQL knowledge

Hi Friends, 

In this post I'm sharing SQL round interview questions-answers asked in Incedo.

You can also refer my other interview posts here:

• SQL Interview - 1
• Java 8 interview Questions - Part - I
• Java Interview @ Aricent - Part - 2
• Java Interview @ Gemini Solutions
• Java Interview @ OLX
• Java Interview @ SAP
• Java Interview @ GalaxE
• Java Interview @ OrangeMantra

Question 1:

What are the JDBC statement interfaces?

Answer:

JDBC statement interfaces are used for accessing the database.

These are of 3 types:

1. Statement
2. PreparedStatement
3. CallableStatement

Statement: Use this for general purpose access to the database. It is useful when we are using static SQL statements at runtime. The Statement interface cannot accept parameters.

PreparedStatement: It is used when we plan to use SQL statements many times. This interface accepts input parameters at runtime.

CallableStatement: It is used when we want to access database stored procedures. This interface also accepts runtime input parameters. 

Creating Statement Object:

We need to create Statement object before we can use it. We can call createStatement() method on Connection object as follows:

try{
    Statement st = Connection.createStatement();
}
catch(Exception e){

}

Now, we can call any of the methods given below to execute SQL statement:

• execute()
• executeUpdate()
• executeQuery() 

Creating PreparedStatement Object:

try{
    String SQL = "update Student set age = ? where id = ?";
    Statement st = Connection.prepareStatement(SQL);
}
catch(Exception e){

}

All parameters in JDBC are represented by the ? symbol, which is known as the parameter marker. We must supply values for each parameter before executing the SQL statement.




Question 2:

What is the difference between BLOB and CLOB?

Answer:

Blob and Clob are both known as LOB [Large Object Types].

BLOB: It is a variable length Binary large object string that can be upto 2GB long. Primarily intended to hold non-traditional data such as voice or mixed media.

CLOB: It is a variable length character large object string that can be upto 2GB long.  A CLOB can be used to store single byte character strings or multibyte character-based data.

Following are the differences between BLOB and CLOB:

1. The full form of Blob is Binary Large Object. The full form of Clob is Character Large Object.
2. Blob is used to store large binary data. Clob is used to store large textual data.
3. Blob stores values in the form of binary streams. Clob stores values in the form of character streams.
4. Using Clob, we can store files like text files, PDF documents, word documents etc. Using Blob, we can store files like videos, images, gifs and audio files.
5. MySQL supports Blob with the following datatypes: 
1. TinyBlob
2. Blob
3. MediumBlob
4. LongBlob

          MySQL supports Clob with the following datatypes:

1. TinyText
2. Text
3. MediumText
4. LongText         

       6. In JDBC API, it is represented by java.sql.BLOB interface.  While Clob is represented by java.sql.Clob interface.

Question 3:


What is the benefit of using PreparedStatement?

Answer:

PreparedStatements help us prevent SQL injections attack.

In this the query and the data are sent to the database server separately.

While using prepared statement, we first send prepared statement to the DB server and then we send the data by using execute() method.

e.g.:

$db-> prepare("Select * from Users where id = ?");

This exact query is sent to the server. Then we send the data in second request like:

$db->execute($data);


If we don't use prepared statement then, SQL injection attack can occur like:

$spoiledData = "1; DROP table Users";
$query = "Select * from Users where id=$spoiledData";

will produce a malicious sequence:

Select * from Users where id = 1; DROP table Users;



Question 4:

How to handle indexes in JDBC?

Answer:

Indexes in a table are pointers to the data which speeds up the retrieval of data from table.
If we use indexes, INSERT and UPDATE statements execute slower whereas SELECT and WHERE get executed faster.

Creating an Index:

create index index_name on table_name(column_name);

Displaying the Index:

Show indexes from table_name;

Dropping the index:

drop index index_name;


That's all for this post.
Hope this post helps everybody in their SQL interviews.