Q1. What is JDBC? Describe the steps needed to execute a SQL query using JDBC.
Ans. The JDBC is a pure Java API used to execute SQL statements. It provides a set of classes and interfaces that can be used by developers to write database applications.
The steps needed to execute a SQL query using JDBC:
1. Open a connection to the database.
2. Execute a SQL statement.
3. Process th results.
4. Close the connection to the database.
Q2. What is connection pooling?
Ans. It's a technique to allow multiple clients to make use of a cached set of shared and reusable connection objects providing access to a database or other resource.
Q3. Difference between Inner and Outer Join ?
Ans. Inner join is the intersection of two tables on a particular columns whereas Outer Join is the Union of two tables.
Q4. What is a Cursor ?
Ans. It's a facility that allows traversal over the records pulled from a table or combination of tables. Its like iterator in Java.
Q5. What is database deadlock ? How can we avoid them?
Ans. When multiple external resources are trying to access the DB locks and runs into cyclic wait, it may makes the DB unresponsive.
Deadlock can be avoided using variety of measures, Few listed below -
Can make a queue wherein we can verify and order the request to DB.
Less use of cursors as they lock the tables for long time.
Keeping the transaction smaller.
Q6. What are temp tables ?
Ans. These are the tables that are created temporarily and are deleted once the Stored Procedure is complete.
For example - we may like to pull some info from a table and then do some operations on that data and then store the output in final output table. We can store the intermediary values in a temp table and once we have final output with us, we can just delete it.
Q7. Does SQL allow null values ? Can we use it within Where clause ?
Ans. Yes , we can have null values for columns in SQL. Null value represent that the columns value is unknown or haven't been filled. Yes, We can use it within where clause to get the rows with null values.
Q8. What things you would care about to improve the performance of Application if its identified that its DB communication that needs to be improved ?
Ans. 1. Query Optimization ( Query Rewriting , Prepared Statements )
2. Restructuring Indexes.
3. DB Caching Tuning ( if using ORM )
4. Identifying the problems ( if any ) with the ORM Strategy ( If using ORM )
Q9. Write an SQL to find all records having all upper case alphanumeric characters in a field ?
Ans. Select * from Table where field = upper(field)
Q10. Write an SQL to find all records having all numeric characters in a field ?
Ans. Select * from Table where isnumeric(field) = 1;
Q11. If you are given a choice to implement the code to either Insert a Record or Update if already exist, Which approach will you follow ?
1. Insert into the DB Table. If exception occurs, update the existing record.
2. Check if the record exists and update it if it exists, If not insert a new record.
Ans. In first case, there would be 2 DB calls in worst case and 1 in best case. In 2nd approach there will be always 2 DB calls.
Decision on the approach should depend on the following considerations -
1. How costly is the call to DB ? Are we using indices , hibernate etc
If calls to DB are costly , 1st approach should be the choice.
2. Exception Book keeping load upon exception.
The benefit of saving 1st call in approach 1 should be bigger than the Book keeping for the exception.
3. Probability of the exception in first apparoach.
If the DB Table is almost empty, it makes sense to follow Approach 1 as majority of the 1st calls will pass through without exception.
Q12. What's the benefit for specifying constraints like Not Null , Primary Key explicitly instead of specifying it against the column ?
Ans. In case we specify them explicitly we can have control over constraint name as otherwise they will be system generated. It provides an ease in case we plan to drop the constraint in future.
Q13. How can we disable a constraint ?
Ans. alter table
table_name
DISABLE constraint
constraint_name;
Q14. Can we have foreign key reference to a non primary key column ?
Ans. Yes, but the respective key in the foreign table should be declared Unique.
Q15. What should be done for auto generating primary key id in a table ?
Ans. We need to create a sequence.
Q16. Which constraint cannot be specified as an explicit constraint and should be specified with the column only ?
Ans. NOT NULL
Ans. The JDBC is a pure Java API used to execute SQL statements. It provides a set of classes and interfaces that can be used by developers to write database applications.
The steps needed to execute a SQL query using JDBC:
1. Open a connection to the database.
2. Execute a SQL statement.
3. Process th results.
4. Close the connection to the database.
Q2. What is connection pooling?
Ans. It's a technique to allow multiple clients to make use of a cached set of shared and reusable connection objects providing access to a database or other resource.
Q3. Difference between Inner and Outer Join ?
Ans. Inner join is the intersection of two tables on a particular columns whereas Outer Join is the Union of two tables.
Q4. What is a Cursor ?
Ans. It's a facility that allows traversal over the records pulled from a table or combination of tables. Its like iterator in Java.
Q5. What is database deadlock ? How can we avoid them?
Ans. When multiple external resources are trying to access the DB locks and runs into cyclic wait, it may makes the DB unresponsive.
Deadlock can be avoided using variety of measures, Few listed below -
Can make a queue wherein we can verify and order the request to DB.
Less use of cursors as they lock the tables for long time.
Keeping the transaction smaller.
Q6. What are temp tables ?
Ans. These are the tables that are created temporarily and are deleted once the Stored Procedure is complete.
For example - we may like to pull some info from a table and then do some operations on that data and then store the output in final output table. We can store the intermediary values in a temp table and once we have final output with us, we can just delete it.
Q7. Does SQL allow null values ? Can we use it within Where clause ?
Ans. Yes , we can have null values for columns in SQL. Null value represent that the columns value is unknown or haven't been filled. Yes, We can use it within where clause to get the rows with null values.
Q8. What things you would care about to improve the performance of Application if its identified that its DB communication that needs to be improved ?
Ans. 1. Query Optimization ( Query Rewriting , Prepared Statements )
2. Restructuring Indexes.
3. DB Caching Tuning ( if using ORM )
4. Identifying the problems ( if any ) with the ORM Strategy ( If using ORM )
Q9. Write an SQL to find all records having all upper case alphanumeric characters in a field ?
Ans. Select * from Table where field = upper(field)
Q10. Write an SQL to find all records having all numeric characters in a field ?
Ans. Select * from Table where isnumeric(field) = 1;
Q11. If you are given a choice to implement the code to either Insert a Record or Update if already exist, Which approach will you follow ?
1. Insert into the DB Table. If exception occurs, update the existing record.
2. Check if the record exists and update it if it exists, If not insert a new record.
Ans. In first case, there would be 2 DB calls in worst case and 1 in best case. In 2nd approach there will be always 2 DB calls.
Decision on the approach should depend on the following considerations -
1. How costly is the call to DB ? Are we using indices , hibernate etc
If calls to DB are costly , 1st approach should be the choice.
2. Exception Book keeping load upon exception.
The benefit of saving 1st call in approach 1 should be bigger than the Book keeping for the exception.
3. Probability of the exception in first apparoach.
If the DB Table is almost empty, it makes sense to follow Approach 1 as majority of the 1st calls will pass through without exception.
Q12. What's the benefit for specifying constraints like Not Null , Primary Key explicitly instead of specifying it against the column ?
Ans. In case we specify them explicitly we can have control over constraint name as otherwise they will be system generated. It provides an ease in case we plan to drop the constraint in future.
Q13. How can we disable a constraint ?
Ans. alter table
table_name
DISABLE constraint
constraint_name;
Q14. Can we have foreign key reference to a non primary key column ?
Ans. Yes, but the respective key in the foreign table should be declared Unique.
Q15. What should be done for auto generating primary key id in a table ?
Ans. We need to create a sequence.
Q16. Which constraint cannot be specified as an explicit constraint and should be specified with the column only ?
Ans. NOT NULL