Java J2EE Spring SQL Interview Questions and Answers



Q: Explain inner and outer SQL joins?
A: Joins allow database users to combine data from one table with data from one or more other tables (or views, or synonyms). Tables are joined two at a time making a new table containing all possible combinations of rows from the original two tables.


Inner joins: Chooses the join criteria using any column names that happen to match between the two tables. The example below displays only the employees who are executives as well.

SELECT emp.firstname, exec.surname 
FROM employees emp, executives exec 
WHERE emp.id = exec.id;


Left Outer joins: A problem with the inner join is that only rows that match between tables are returned. The example below will show all the employees and fill the null data for the executives. 

SELECT emp.firstname, exec.surname 
FROM employees emp left join executives exec ON emp.id  = exec.id;

Right Outer join: A problem with the inner join is that only rows that match between tables are returned. The example below will show all the executives and fill the null data for the employees. 

SELECT emp.firstname, exec.surname 
FROM employees emp right join executives exec ON emp.id  = exec.id;

Full outer join: To cause SQL to create both sides of the join

SELECT emp.firstname, exec.surname 
FROM employees emp full join executives exec ON emp.id  = exec.id;

Self join: A self-join is a join of a table to itself. If you want to find out all the employees who live in the same city as employees whose first name starts with “Peter”, then one way is to use a sub-query as shown below:

SELECT emp.firstname, emp.surname 
FROM employees emp 
WHERE city IN (SELECT city FROM employees where firstname like ‘Peter’)


Q. Explain a sub-query? How does a sub-query impact on performance?
A. It is possible to embed a SQL statement within another. When this is done on the WHERE or the HAVING statements, we have a subquery construct.


Q. What is subquery useful for?
A. It is used to join tables and there are cases where the only way to correlate two tables is through a subquery.

SELECT emp.firstname, emp.surname 
FROM employees emp 
WHERE emp.id NOT IN (SELECT id FROM executives);

There are performance problems with sub-queries, which may return NULL values. The above sub-query can be re-written as shown below by invoking a correlated sub-query.

SELECT emp.firstname, emp.surname 
FROM employees emp 
WHERE emp.id NOT EXISTS (SELECT id FROM executives);

The above query can be re-written as an outer join for a faster performance as shown below:

SELECT emp.firstname, exec.surname 
FROM employees emp left join executives exec on emp.id  = exec.id AND exec.id IS NULL;

The above execution plan will be faster by eliminating the need for a sub-query.


Q. Can you give SQL examples for the following scenarios?
A.

Scenario 1: Retrieve first name and sum of order qty for order sum greater than 25, and group the order sum by first name.?

SELECT FIRSTNAME,SUM(QTY) 
FROM orders 
GROUP BY FIRSTNAME 
HAVING SUM(QTY)>25;


Scenario 2: Retrieve all employees whose name has a String "au"?

SELECT * 
FROM employees emp 
WHERE emp.firstname LIKE ‘%au%’;

Scenario 3: select  account number and adviser code for a given adviser code, but restrict the returned values to supplied min and max limit. For example, record 1 to record 10, record 11 to record 20, etc.

The SQL for the above scenario needs to use some custom SQL parameters and functions. The example below uses the ROWNUM variable that keeps track of the row numbers in Oracle.  The nested query shown below can limit the returned results based on a lower and upper limit.

select * from 
    (select a.ACCOUNT_NO, a.ADVISER_CODE, ROWNUM rnum from 
       ( Select * from accounts where ADVISER_CODE=:advCode order by advCode)  a 
where ROWNUM <= :max_row) 
    where rnum  >=  :min_row

Q: In your experience, what are some of the common mistakes developers make?
A:

1. Cartesian joins

SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements.

The Syntax for joining two tables is:

SELECT col1, col2, col3 
FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1;

If a sql join condition is omitted as shown below

SELECT col1, col2, col3 
FROM table_name1, table_name2

or if the condition is invalid, then the join operation will result in a Cartesian product. The Cartesian product returns a number of rows equal to the product of all rows in all the tables being joined. For example, if the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, or 200 rows. This query will take a long time to execute.


2. Use of SELECT *

For example, a common misuse of SELECT * is to extract a set of all employees and to insert them into another table called Contractors with the same structure

INSERT INTO Contractors 
  SELECT * FROM Employees WHERE emp_type = 'C';

The above query does the job, however, one day business requirements change and two new columns are added to the Employees table:

ALTER TABLE Products
  ADD effective_start_date DATETIME, effective_end_date DATETIME;

All of sudden the query that extracts from the Employees table and insert records into the Contractor table results in error.

"Insert Error: Column name or number of supplied values does not match table definition."

The fix is to explicitly list the column names in the query:

INSERT INTO Contractors (emp_id, emp_name)
  SELECT emp_id, emp_name FROM Employees WHERE emp_type = 'C';


3. Embedding User Interface (UI) layer logic into Data layer via SQL. For example

SELECT '<a href="http://www.blogger.com/...">' + name ' </a>'


The above code is a bad practice because it tightly couples your UI Layer with the Data Layer.


4. Not using Prepared statements. Prepared statements are more secured and efficient than the ordinary statements. Prepared statements prevent SQL injection attacks.


5. Using the predicate "LIKE" in indexed columns. The "LIKE" predicate typically performs a search without the normal performance benefit of indexes. Using '=', '<>', etc instead of "LIKE" will increase performance. Also should be aware of that case sensitivity (e.g., 'A' versus 'a') may be different based upon database Server or configuration.


6. Over use of cursors in stored procedures. If possible, avoid using SQL stored proc cursors. They generally use a lot of  Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task.

Here are some alternatives to using a cursor:

  •     Use WHILE LOOPS
  •     Use temp tables
  •     Use materialized views (allowing you to pre-join complex views and pre-compute summaries for super-fast response time. )
  •     Use derived tables
  •     Perform multiple queries
  •     Use correlated sub-queries
  •     Use the CASE statement 

Q. Can you give some database performance tuning tips based on your experience?
A.

1. Materialized views are one of the important SQL tuning tools in Oracle. Instead of the entire company accessing a single database server, user load can be distributed across multiple database servers with the help of materialized views in Oracle. Through the use of multi tier materialized views, you can create materialized views based on other materialized views, which enables you to distribute user load to an even greater extent because clients can access materialized view sites instead of master sites. To decrease the amount of data that is replicated, a materialized view can be a subset of a master table or master materialized view. 

  • Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data.
  • It allows you to pre-join complex views and pre-compute summaries for super-fast response times. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view provides indirect access to table data by storing the results of a query in a separate schema object.
  • You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
  • A materialized view can be stored in the same database as its base tables (improves query performance through query rewrite) or in a different database.

It is also worth noting that this capability may not suit best for too frequent activities as in online transaction processing (i.e. OLTP)  environments. In other databases equivalent functionalities can be achieved through triggers on base tables that would update/insert aggregate or dimension tables and queries can be executed against these aggregated or dimension tables as oppose to the base tables.


2. As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that increases in one direction (i.e. monotonic) such as an identity column, or some other column where the value is increasing and is unique. In many cases, the primary key is the ideal column for a clustered index. Create an index on any column that is a foreign key. If you know it will be unique, set the flag to force the index to be unique.

3. Avoid temp tables as much as you can, but if you need a temp table, create it explicitly using Create Table #temp.

4. Only return the columns and the rows you need.

5. Avoid full table scan where possible. The full table scan can be caused by


  • No WHERE condition.
  • No index on any type of field in the WHERE clause.
  • NOT IN predicate that is easier to write (replace NOT IN with a left outer join).
  • WHERE clauses like column_name is not null, condition 1 or condition 2, column_name between ... and ..., not equality comparisons
  • Use of SQL “LIKE clause” operator to find a string within a large table column (e.g. VARCHAR(2000), CLOB, BLOB).
  • DISTINCT, ANY, and ALL.




Other relevant questions and answers




    Labels:

    Labels

    .equals = operator abstract class abstract method abstract window toolkit Access Modifiers accessing java beans accessing javabeans action events actionperformed active addition Advanced Advanced Overloading AdvJavaBooks Agile development ajax alive AMQP and Android anonymous class anonymous inner class Ant ant tutorials anti patterns antipatterns Apache Camel api for jsp api for servlet api for servlets api jsp application context application scope application session Apps Architecture are you eligible for the ocmjd certificaiton are you eligible for the scjd certification arithmetic operator arpanet array construction array declaration array initialization array list array to list conversion arraylist arraylist of strings arraylist of types arraylist questions arraylists Arrays arrays in java ask for help assert assert in java assertions assertions in java assignment assignment operator Atlassian attribute visibility authentication authorization autoboxing autounboxing awt AWT Event Handling awt interview questions AWT Layouts awt questions awt questions and answers backed collection backed collections Basic Basics of event handling bean attributes bean properties bean scope Beginner best practices BigData blocked books boxing buffer size bufferedreader bufferedwriter business delegate business delegate pattern calendar case statement casting in java casting interview questions chapter review choosing a java locking mechanism choosing a locking mechanism choosing a thread locking mechanism class inside a method class questions class with no name class without a name classes interview questions Clipboard closing jsp tags code snap coding cohesion collection generics collection interview questions collection methods collection of types collection questions collection searching collection types Collections Collections Framework collections interview questions collections sorting colors in java swings colors in swing command line arguments communication between threads comparable comparator comparison operators compiling java classes computers concurrency example and tutorial config Configuration ConnectionPooling constructor creation constructor interview questions constructor overloading constructors in java containers contents of deployment descriptor contents of web.xml context context scope converting array to list converting list to array core java core java interview core java interview question core java interview questions core java questions core java; core java; object oriented programming CoreJava CoreJavaBooks CORS coupling create threads creating 2 dimensional shapes creating 2D shapes creating a frame creating a jframe creating a thread creating an arraylist creating an inner class creating an interface creating java beans creating java threads creating javabeans creating threads creating threads in java CSS cURL currency current thread determination custom tag library custom taglib custom taglibs custom tags CVS dao dao design pattern dao factory pattern dao pattern data access object data access object pattern data structure and algorithm database date and time tutorial date format dateformat dates deadlock deadlocks debugging Declarations decorator pattern decrement default deleting sessions deploy web app deployment deployment descriptor deployment descriptor contents deployment of web application deserialization deserialize design pattern design pattern interview questions design patterns Designpatterns destory method destroy destroying sessions determining current thread determining the current thread Developer Differences different types of collections display stuff in a frame displaying images displaying images in java swings displaying images in swings displaying text in a component division do while loop doget dohead dopost doput DOS Downloads drawing a line drawing an ellipse drawing circles drawing ellipses drawing lines Drools tutorial eBooks Eclipse Eclipse Tutorial Encapsulation encapsulation in java enhanced for loop entity facade pattern enumerations enumerations in java enums equal to equals equals comparison error and exception error codes error handling in servlets error page event handling in swings event listeners exam prep tips example servlet Examples exception exception handling exception handling in servlets exception handling interview questions exception handling questions Exceptions exceptions in java exceptions in web applications explicit locking explicit locking of objects file file navigation filereader filewriter final class final method FireBug first servlet FIX protocol FIX Protocol interview questions FIX protocol tutorial font fonts for each loop for loop form parameters form values formatting forwarding requests frame frame creation frame positioning frame swings front controller front controller design pattern front controller pattern fundamental.Java FXML Games garbage collection garbage collection interview questions garbage collection questions garbage collector gc gc questions general generic Generics generics collections Geo get get set methods getattribute getting bean property getting form values getting form values in servlet getting scwcd certified getting servlet initialization parameters getting sun certified Google Graphics2D gregorian calendar handling strings in java hash hash map hash table hashcode hashmap hashset hashtable head head request HeadFirst heap heaps hibernate hibernate interview questions hibernate interview questions and answers hibernate questions hibernate questions and answers Hibernate Tutorial HibernateBooks homework How To HTML HTML and JavaScript html form http request http request handling http request header http request methods http request servlet http request type http session httprequest httprequest methods httpservlet httpservlet interview questions httpservlet interview questions with answers httpsession httpsession interview questions httpsession questions HttpSessionActivationListener HttpSessionAttributeListener HttpSessionBindingListener if if else if else block if else statement Image IO implementing an interface Implicit objects increment info inheritance inheritance in java init init method Initialization Blocks inner class inner class inside a method inner classes innerclass installation instanceof instanceof operator IntelliJ interaction between threads interface interface interview interface questions interfaces interfaces in java interfaces interview questions internet history interrupting a thread interrupting threads Interview interview questions interview questions on design patterns interview questions on exception handling interview questions on java collections interview questions on serialization introduction to java threads introduction to jsps introduction to threading introduction to threads invalidating session Investment Banking IO Package iscurrentthread iterator J2EE j2ee api j2ee design pattern j2ee design pattern interview questions j2ee design patterns j2ee hibernate interview questions j2ee history j2ee interview j2ee interview questions j2ee mvc j2ee mvc pattern j2ee programmer j2ee questions j2ee servlet api j2ee session j2ee struts interview questions java java 5 tutorial Java 8 java arrays java assertions java assignments java awt questions java bean java bean scope java beans java beginners tutorial Java career java certification Java Class java collection interview questions and answers java collection tutorial java collections java collections interview questions java constructors java currency Java CV java data base connectivity java database connectivity java database connectivity interview questions and answers java dates java design pattern java design patterns java developer certification Java EE java encapsulation java enums java event listeners java exceptions java formatting java garbage collection java garbage collector java gc java heap Java I/O java inheritance java input output Java Interface Java Interview Java Interview Answers Java Interview Questions Java Introduction java io java IO tutorial java iterator java jdbc Java JSON tutorial Java Key Areas java lists java literals java locks nested Java Media Framework java methods java multithreading Java multithreading Tutorials java nested locks java networking tutorial java numbers Java Objects java operators java overloading java parsing Java Programming Tutorials java race conditions java regex java regular expressions Java resume java scjp java searching java serialization java server pages java server pages api java server pages questions java spring interview questions. j2ee spring interview questions java stack java strings java swing java swing event listeners java swing frame java swing images java swings java swings images java thread explicit locking java thread lock scope java thread locking java thread locking mechanism java thread locking objects java threads java threads race condition java tips java tokenizing Java Tools Java Tutorial java ui questions Java Utilities java variables java wrappers Java xml tutorial java.lang java8 javabean javabean accessing javabean scope JavaBeans javac JavaEE JavaFX JavaFX 3D JavaFX 8 JavaOne JavaScript JavaTips JDBC jdbc driver jdbc example jdbc interview questions jdbc interview questions and answers jdbc interview questions with answers jdbc sample code JDBC Tutorial jdbc type 1 driver jdbc type 2 driver jdbc type 3 driver jdbc type 4 driver Jdeveloper JDK JDK8 JEE Tutorial jframe jframe creation jframe position jframe positioning JIRA JMeter JMS JMX join() joining threads JPA JQuery JS JSF JSF Tutorial JSONP JSP jsp and java beans jsp and servlets jsp and xml jsp api jsp code jsp compilation jsp conversion jsp directives jsp error page jsp error page directive jsp implicit objects jsp interview jsp interview questions jsp introduction jsp intvw questions jsp life jsp life cycle jsp life-cycle jsp lifecycle jsp page directive jsp questions jsp sample jsp scripting jsp scriptlets jsp servlets jsp summary jsp synopsis jsp tag libraries jsp tag library jsp taglib jsp tags jsp technology jsp to servlet jsp to servlet conversion jsp translation jsp usage jsp usebean jsp xml tags jsp xml tags usage jsp-servlet jsp:getProperty jsp:setProperty jsp:usebean jsps JSTL JUnit testing keyword synchronized keyword volatile Keywords Lambda Expressions Learning libraries life cycle life cycle of a jsp life cycle of a servlet life cycle of a thread life cycle of jsp life cycle of threads lifecycle of a thread linked list linkedhashmap linkedhashset linkedlist linux List listeners lists Literals locale lock manager pattern lock scope locking objects using threads log Logging logging errors logical and logical operators logical or loops loosely coupled making an arraylist making threads sleep making threads sleep for time MapReduce maps maps usage Maven Maven Tutorial max priority member access method arguments method local inner class method overloading method overriding method return types methods creating classes min priority Miscellaneous mobile mock exam model view controller model view controller design pattern model view controller pattern Multi Threading Multi-threading multiple threads multiplication multithreading multithreading in java multithreading interview questions multithreading questions mvc mvc design pattern mvc pattern MyEclipse mysql nested java lock nested java locks nested java thread locks nested locks nested thread locks NetBeans Networking new news nio NonAccess Modifiers norm priority normal inner class Normalization not equal to Notepad notify notifyall number format numberformat numbers object comparison object notify object orientation object oriented object oriented programming Object Oriented Programming in java objects interview questions ocmjd certification ocmjd certification eligibility OO OO Java oops OpenCSV OpenCV opening jsp tags OpenJDK OpenJFX Operators or Oracle Oracle ADF Mobile Oracle Certified Exams oracle certified master java developer oracle database ORM other topics out overloading overloading constructors overloading in java overriding page page directive page scope parsing passing variables passing variables to methods performance Platform Playing with Numbers points to remember polymorphism positioning a frame post practice exam Primitive Casting primitive variables printwriter priority queue priority queues priorityqueue priorityqueues private processing form values Products programming Projects protected public put questions questions on garbage collection questions on java strings queue quick recap quick review race conditions read objects from stream reading http request header RealTime_Tips redirecting to another servlet redirection reference reference variable casting reference variables Refreshing Java regex Regular Expressions regular inner class relational operators reminder request request dispatcher request forwarding request header request object. httpservletrequest request scope requestdispatcher response RESTClient RESTful retrieving values from session return error codes return types returning values runnable runnable interface running running java programs RUP sample jsp sample questions sample questions scwcd sample servlet scanner Scene Builder scjd certification scjd certification eligibility requirements scjp SCJP Certification scjp exam scjp exam questions scjp exam sample questions scjp questions scjp test scjp test questions scope scope of java locks scope of java thread locks scope of locks scripting in jsp scriptlet tags scriptlets scriptlets in jsp pages scwcd scwcd certification scwcd certification practice exam scwcd exam scwcd exam questions scwcd jsp summary scwcd mock exam scwcd mock exam answers scwcd practice exam scwcd practice test scwcd questions scwcd test SDLC searching searching arrays searching collections searching in java searching treemap searching treesets security self assement self assement scwcd self assessment scjp self test self test scjp self test scwcd send error method senderror method sending error code to browser serialization serialization in java serialization interview questions Serialization on Swing serialization questions service service method servlet servlet and forms servlet and jsp servlet api servlet attributes servlet code servlet container servlet context servlet error handling servlet exception handling servlet handling http request servlet initialization servlet initialization parameters servlet interview servlet interview questions servlet interview questions with answers servlet intvw questions servlet life cycle servlet lifecycle servlet questions servlet questions with answers servlet request servlet request dispatcher servlet request type servlet skeleton servletcontext servletcontextevent servletrequest Servlets servlets and jsps servlets api servlets details servlets request handling session session clean up session event listeners session facade pattern session interview questions session invalidation session listeners session management session questions session scope session timeout session tracking through url rewriting set collections set status method setattribute sets setstatus method setting bean property setting request type short circuit operators Singleton sleep sleeping threads soapUI Software Installation sorting sorting arraylist sorting arrays sorting collections special collections special inner classes split spring spring and hibernate interview questions spring batch Spring Core Spring Framework Spring Integration spring interview questions Spring JDBC Spring MVC Spring security Spring tutorial SQL SQL and database tutorial examples SQL Tutorial SSL stack stacks stacks and heaps static static class static declaration static imports static inner static inner class static method Static variable stopped stopping a thread stopping thread stopping threads Stored Procedure storing values in session Streams strictfp StrictMath string string buffer string builder string class string formatting String Handling string interview questions string manupulation string questions string tokenizer stringbuffer stringbuffer questions stringbuilder Strings strings in java struts Struts 1 Struts 1.2 Struts 2 struts framework interview questions struts interview questions struts interview questions with answers struts mvc interview questions struts questions Struts2 StrutsBooks submitting request subtraction Sun Certification sun certified java developer Sun Certified Java Programmer swing swing action performed swing and colors Swing Components swing event handling swing event listeners swing events Swing Hacks swing images Swing Look And Feels swings swings frame switch block switch case block switch case statement Sybase Sybase and SQL Server synchronization synchronized code synchronized keyword synchronized method System Properties tag lib tag library tag-lib taglibs tags TDD Technical Blogging ternary operator Test Driven Development test scjp Testing the context the session the volatile keyword thread thread class thread deadlocks thread interaction thread interruption thread life cycle thread lifecycle thread lock scope thread locks thread notify thread priorities thread race conditions race conditions in threads thread sleep thread states thread stoppage thread stopping thread synchronization thread syncing thread yield Threads threads in java threads interview questions threads life cycle threads questions tibco tightly coupled tips tips and tricks tips.FXML tokenizing Tomcat Tools toString transitions treemap treeset tricks Tricks Bag try catch try catch finally. finally block Tutorial type casting in java ui programming with java swings UML unboxing unit testing unix url rewriting use bean usebean using a arraylist using collections using colors using colours using command line arguments using different fonts using expressions using font using fonts using fonts in swings using hashmap using http session using httpsession using iterator using java beans in jsp using javabean in jsp using javabeans in jsp using javac using lists using maps using request dispatcher using scriptlets using session persistense using sets using special fonts using system properties using the jsp use bean using treeset using use bean Using Variables using volatile Using Wrappers using xml in jsp Util pack value object value object design pattern value object pattern var-args varargs Variable Arguments Variables vector vector questions vectors visibility vo pattern volatile volatile keyword volatile variables wait method waiting web app exception handling web app interview web application deployment web application exceptions web application scope web application security web component developer web component developer certification web context web context interfaces web context listeners web interview questions web security web server web servers Web services web.xml web.xml deployment webapp log weblogic website hacking website security what are threads what is a java thread what is a thread what is thread while loop windows windows 8 wrapper classes wrappers write objects to stream WSAD xml xml and jsp xml tags xslt yield()