Interview Questions - SQL

What is 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 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;the DBMS automatically fires the trigger as a result of a data modification to the associated table.
Triggers can be viewed as 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 is 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 data in the view, as views are the way to look at part 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 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 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.
Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.
Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered 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 & TRUNCATE commands?
Delete command removes the rows from a table based on 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.

Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as 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 though of as views that take parameters and can be used in JOINs and other Rowset operations.

What types of Joins are possible with Sql Server?
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Specifies a search condition for a group or an aggregate. 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. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What are primary keys and foreign keys?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers,the value of this cannot be controled. Identity/GUID columns do not need to be indexed.

What is 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 have a hierarchal reporting structure whereby one member of staff reports to another.

What is 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.

List few advantages of Stored Procedure.· Stored procedure can reduced network traffic and latency, boosting application performance.
· Stored procedure execution plans can be reused, 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.

JSR 168, 286 Portlets & Portal Interview Questions

What is a portal ?

A portal can be best described as a web site that acts as a "point of entry" or a gate to a larger system. While it seems pretty difficult to define a portal in the strictest sense, these are some of the common features of portals:

• Content aggregation:
  Portals tend to aggregate content from multiple, disparate sources and provide one unified view of the same.
• Personalization:
  This refers to the ability of the portal to tailor the content/services that it offers according to the user of the portal.
• Search:
  Most if not all portals, offer some form of searching of it's content and sometimes even content from external sources.
• Single Sign On:
  Allows users to authenticate once to the portal and thereby allows access to many other systems without the need for re authentication.

What is a JSR 168 Portlet ?

Prior to JSR 168, almost all portal platforms offered their own proprietary approach to create pluggable portal components. For example, IBM had IBM portlets, Sun(iPlanet) had Providers, SAP had iViews and Plumtree had Gadgets.

JSR 168 aims to standardize these pluggable portal components so that they are independent of the actual portal server that they are written to. What this means is that one can migrate portlets seamlessly from one portal server to another without any code change.

What are the types of request in JSR 168 portlets?

Their are two types of request :-
1) RenderRequest
2) ActionRequest

What are the differences between portlets and servlets?

Essentially, Servlets provide content that normally takes up the whole page in a browser (unless you're using frames), and portlets provide content that is wrapped by a window. With portlets, you can have multiple portlets side by side with one another and each one can provide content and functionality that is different from the other. A portlet can provide the complete interaction for one type of application, while another portlet can provide content for another type of application. The portal can provide some house keeping functionality and secured single point of entry to all of the portlets on a page. As for the particulars (similarities/differences) between them, please continue reading.

Here are some similarities:

• Servlets and portlets are web based components that utilize Java for their implementation
• Portlets are managed by a portlet container similar to a servlet container
• Both of these components generate content, which can be static or dynamic
• Both portlets and servlets have a lifecycle that is controlled by the container
• The client/server model is used for both servlets and portlets
• The packaging and deployment are essentially the same
• The manner in which the classes are loaded and the class loaders that perform the work are also the same
• Lifecycle management is similar
• The Request and Response semantics are also similar

Here are some differences:

• Servlets can provide complete web pages, whereas portlets only provide fragments. These fragments are then aggregated to form a complete web page by the portal
• Portlets aren?t allowed to generated HTML code that contains tags such as base, body, frame, frameset, head, html, or title. The iframe tag can be used with caution.
• The user cannot access a portlet directly using a URL in the way that a servlet is accessed. Instead, the URL points to the page containing all of the portlets on one page
• Communication between the web client and the portlets is performed through the portal
• Portlets can be provided with buttons or controls to manipulate the portlets? window states or portlet modes
• Multiple instances of a single portlet can be placed onto the same page
• Portlets support persistent configuration and customization
• Portlets also support user profile information
• Portlets support two scopes within the session; application scope and portlet scope

There are several things that servlets are allowed to do, but portlets aren?t. These include the following:

• Portlet aren?t allowed to set the character set encoding of the response
• Portlet also aren?t allowed to set the HTTP headers on the response
• Portlet cannot manipulate the URL of the client request to the portal

What is a portlet container ?

A portlet container runs portlets and provides them with the required runtime environment. A portlet container contains portlets and manages their lifecycle. It also provides persistent storage for portlet preferences. A portlet container receives requests from the portal to execute requests on the portlets hosted by it.A portlet container is not responsible for aggregating the content produced by the portlets. It is the responsibility of the portal to handle the aggregation.

Can I get the HttpServletRequest from a Portlet?

The PortletRequest object is supposed to give you everything you need i.e. parameters, attributes, dispatching, etc. As per the spec, you should not need the HttpServletRequest.

However, some portlet container implementations do provide some kind of hack to get hold of HttpServletRequest e.g. in Pluto you can cast the RenderRequest to HttpServletRequest. But, be aware that this behavior cannot be relied upon.

What's difference between PortletConfig.getInitParameter() and PortletContext.getInitParameter()?

Context-wide init-params share the same context space as Servlets and JSPs belonging to the same application and they are defined in the web.xml file. You can get them using PortletContext.getInitParameter() method.

Portlet-wide initialization parameters on the other hand belong in the portlet.xml file and you can get them using PortletConfig.getInitParameter() method.

What is a PortalSession Interface ?

User identification across many requests and transient information storage about the user is processed by PortletSession interace. One PortletSession is created per portlet application per client.

The PortletSession interface provides a way to identify a user across more than one request and to store transient information about that user.

The storing of information is defined in two scopes- APPLICATION_SCOPE and PORTLET_SCOPE.

APPLICATION_SCOPE: All the objects in the session are available to all portlets,servlets, JSPs of the same portlet application, by using APPLICATION_SCOPE.

PORTLET_SCOPE: All the objects in the session are available to the portlet during the requests for the same portlet window. The attributes persisted in the PORTLET_SCOPE are not protected from other web components.

What is a PortletContext Interface ?

The portlet view of the portlet container is defined by PortletContext. It allows the availability of resources to the portlet. Using this context, the portlet log can be accessed and URL references to resources can be obtained. There is always only one context per portlet application per JVM.

Is there any difference betweeb websphere and weblogic ?

Webpshere tends to focus more on integration, connectivity and web services. it has rich implementation of J2EE, better performance, more extensive integration and transaction management. In terms of trnsaction weblogic is having default transaction attribute as ’supports’, but websphere does not have any default transaction attribute.

How to implement JDBC-ODBC bridge driver (Type 1 ) in Websphere?

If you use JDBC type (I) driver you dont need to add any driver in websphere. you simply created DSN and use it locally, same we use java class, if you use Type(2) and Type(4) so first go to admin console then go to connection, then add driver there fill other info like conn. size, uname pass, max conn. and connect it to you applications.

What is the difference between application server and web server?

ApplicationServer: takes care of Security, Transaction, Multithreading, Resource pooling, load balancing, clustering, performence, highly availability, scalability, etc. Exposes business logic to client applications through various protocols, possibly including HTTP. Supports deployment of .war and .ear filesApplication server = webserver + EJB container.

Webserver: handles HTTP protocol. Receives HTTP request, it responds with an HTTP response.

Explain relationship betweeb the Servlet Container and Portlet Container?

The portlet container is an extension of the servlet container. As such, a portlet container
can be built on top of an existing servlet container or it may implement all the
functionality of a servlet container. Regardless of how a portlet container is implemented,
its runtime environment is assumed to support Servlet Specification 2.3

Explain portlet lifecycle?

A portlet is managed through a well defined life cycle that defines how it is loaded,
instantiated and initialized, how it handles requests from clients, and how it is taken out
of service. This life cycle of a portlet is expressed through the init, processAction,
render and destroy methods of the Portlet interface.