Prasanna

We have compiled most frequently asked Java J2EE Interview Questions which will help you with different expertise levels.

Java J2EE Interview Questions on Internationalization

Question 1.
Elucidate UDDI.
Answer:
The Universal Description, Discovery, and Integration is a database of web services providers that is accessible from within a program and supplies information about the web services available from the provider including information about interfaces to web services. The web services community centers around a group of web-based registries that contain information about each member, their web services, and the application programming interfaces used to access those services. This group of registries is referred to as the Universal Description, Discovery, and Integration (UDDI). A UDDI entry is called a tModel and contains:-

Question 2.
What do you mean by internationalization?
Answer:
Internationalization, also known as I18N, encompasses tailoring content specific to locales based on their different languages, currencies, and formatting conventions. Due to international globalization has arisen the need for software that can adapt to the conventions and languages used by customers in different countries.

The acronym I18N is sometimes used in place of the word internationalization since it is such a long word to type. I18N represents the first letter i, followed by 18 characters, and then the final letter n.

Question 3.
What is Java’s built-in internationalization support?
Answer:
Java’s built-in internationalization support is centered on three main classes. The following table (Table 1) lists each class and its description:-

Class	Description
Java.util.Locale	Encapsulates the language, country, and variant for a specific locale
Java.util.ResourceBundle	Encapsulates locale-specific resources
Java.text.MessageFormat	Provides methods for creating locale-specific formatted messages.

Question 4.
How do you internationalize applications using Struts?
Answer:
With Struts, creating an I18N application is easy. If you are willing to pull all the titles, labels, and text messages from a properties file, then you can easily I18N your application. Just create a properties file for each language (locale) you would like to support.

1. Struts attempts to use a properties file that corresponds to the browser language setting. When requesting a page, the browser sends an Accept-Language request header, listing the language preferences. If no corresponding language (locale) is found, then Struts uses the default properties file.

2. Default properties file: To establish a default properties file, use the message-resource element in struts-config.xml. Thus, when you say:
<message-resources parameter “someName” …>

3. The file, WEB-INF/classes/someName.properties, is loaded and treated as the default language file.

4. Locale-specific properties file: Based on the languages accepted by the browser, Struts automatically looks for specialized files corresponding to the locale. A locale-specific file has the same name as the default properties file but is augmented with local information, e.g. someName_es.properties (Spanish locale) or sometime fr. properties (French locale). Entries from a more specific file override entries from the default file. The locale can also be set explicitly (e.g., based on the incoming checkbox value) in an Action with setLocale.

Once you have created a locale-specific properties file, you will want to test it in your Web application. To test the new properties file, change the language settings in the browser. For Internet Explorer, for instance, select Tools from the menu, then Internet Options, then Languages. Click the Add button to select other languages for the browser. To change the language preference order, use the Move Up button.

Now, we present an example where we internationalize for English, Spanish and French. All we need to do is to create two new properties files (refer to chapter 8 on Struts for more on properties files), MessageResources_es.properties for the Spanish locale and MessageResources_fr.properties for the French locale, The assumption is that the original file, MessageResources.properties, supports the English locale. The two new locale files need to contain all the same messages as MessageResources.properties but are translated to the appropriate language. The keys remain the same. This change is all that is required to internationalize the application for Spanish and French – there are no changes to any configuration files or code!

We have compiled most frequently asked Java J2EE Interview Questions which will help you with different expertise levels.

Java J2EE Interview Questions on Web Services & Xml

Question 1.
Elucidate UDDI.
Answer:
The Universal Description, Discovery, and Integration is a database of web services providers that is accessible from within a program and supplies information about the web services available from the provider including information about interfaces to web services. The web services community centers around a group of web-based registries that contain information about each member, their web services, and the application programming interfaces used to access those services. This group of registries is referred to as the Universal Description, Discovery, and Integration (UDDI). A UDDI entry is called a tModel and contains:-

1. The name of the business
2. Contact information
3. Industry codes
4. Product classification
5. A description of the web service
6. Requirements for rights to access the web service
7. Technical reference to the interface and properties

Question 2.
Elucidate JAXR.
Answer:
The keystone of web services is the XML registry that is the repository for information about the availability of web services and information necessary to access web services. The XML registry is the mechanism for organizations to make available web services to other organizations and to avail themselves of another organization’s web services. Two important XML registry standards are UDDI (refer to the previous problem) and the Java API for XML registries (JAXR) that is used to access an XML registry. The JAXR architecture is organized into two groups – the JAXR client and the JAXR provider. The JAXR client is a Java program that accesses the information contained in an XML registry by interacting with a JAXR provider.

A JAXR provider implements the RegistryService interface giving JAXR client registry access. There are two packages that are j necessary to implement JAXR: the javax.xml.registry package contains the interface to access the registry while the javax.xml.registry.info model package contains interfaces that define registry objects and how those objects are interrelated.

Question 3.
Elucidate SOA. Are SOA and EJB mutually exclusive?
Answer:
At the core of Service-oriented architecture (SOA) lies the concept of service. A simplistic definition of service is a group of related components that carry out a given business process function, e.g. transferring funds between banks or booking an itinerary. An SOA, thus, is a paradigm focusing on the development of services rather than piecemeal components such that these services provide a higher level of abstraction from a functional standpoint.

It is, of course, essential for EJB developers and architects to understand that SOA and EJB are not mutually exclusive but rather are symbiotic. You can write robust SOA architectures using EJB. You might even be called upon to implement SOA projects using EJB.

Question 4.
Are SOA and Web Services the same? Elaborate.
Answer:
The terms Web Services and SOA are often used interchangeably and wrongly so. SOA is a paradigm as elaborated above. There are many possible ways of building software so that it implements salient features of SOA (mainly coarse granularity and loose coupling). One such way is Web Services. Web Services are a group of XML technologies that can be used for implementing SOA. Core Web Service technologies – mainly SOAP and WSDL – form the basis of most of these Web Service implementations today.

Question 5.
Elucidate SOAP.
Answer:
Simple Object Access Protocol (SOAP) is an XML-based application-level protocol intended for exchanging information in a distributed network. SOAP supports both the models of distributed computing: RPC as well as document-style messaging. RPC style SOAP allows remote invocation of operations. The RPC in-out parameters and return values of these operations are serialized into XML, whereas in document-style SOAP, because an operation’s input and output are XML fragments, serialization of parameters and return values to XML is not needed.

Although most of the Web Service applications use SOAP over HTTP today, the standard does not preclude using SOAP over other Internet protocols, such as Simple Mail Transfer Protocol (SMTP). The latest version of SOAP, viz. SOAP 1.2, is a World Wide Web Consortium (W3C) Recommendation.

Question 6.
Elucidate WSDL.
Answer:
Web Service. Description Language (WSDL) is an XML-based metadata standard that is used to describe the service interface as well as service binding information. For RPC style services, a WSDL service interface consists of the supported operations, the input-output parameters that these operations accept, and their return values. For document-style services, the service interface description contains the XML schema fragments for the input-output messages of the service operations, whereas the service binding description specifies communication protocols, ports, the service URL, and other such binding information. Now, the latest version of WSDL, viz. WSDL 2.0, is well on its way to becoming a W3C standard.

The backbone of web services is communication used to transmit between web service providers to an XML registry and from an XML registry to a web services consumer. The Web Service Description Language (WSDL) is a standard used to describe network services used to facilitate the interaction between web services providers and consumers. Organizations that either
publish web services or consume web services must be able to communicate with each other using an agreed-upon protocol. Protocols supported by an organization are described using WSDL.

WSDL standardizes XML elements that describe a collection of communication endpoints. WSDL defines a network service using seven XML elements, viz. type, message, operation, port type, binding, port, and service. WSDL has binding extensions for popular protocols and message formats that are positioned on top of the network service definition. These include SOAP 1.1, HTTP GET/POST, and MIME.

Question 7.
Explain service-oriented architecture with Web services.
Answer:
Web services are a way of building a Service-Oriented Architecture (SOA). SOA is an architectural approach to structuring large-scale, distributed systems that integrate heterogeneous applications behind ‘service’ interfaces. Figure 12 shows the basic model of a service lookup in a Service-Oriented Architecture as supported by Web services technologies:-

1. A service provider creates an abstract service definition that can ‘publish’ in a service registry. With Web services, the description is a Web Services Description Language (WSDL) file, and the registry follows the Universal Description, Discovery, and Integration (UDDI) standard.

2. A service requestor can find the service description, possibly using a set of selection criteria to query the registry.

3. If a suitable description is found, the requestor can bind to and use the service.

Question 8.
Can you explain the concept of Web services in the form of an equation?
Answer:
The set of de facto standards that make up Web services today can be summarized in the form of the following simple equation:-

Question 9.
Elucidate JMS.
Answer:
The Java Message Service (JMS) is a messaging standard, designed to eliminate many of the disadvantages MOM (Message-Oriented Middleware) products have faced over the past few years. JMS has two parts: an API, for which you write code to send/receive messages, and a Service Provider Interface (SPI) where you plug in JMS providers. A JMS provider knows how to talk to a specific MOM implementation.

Question 10.
What is an Object-Relational (OR) mapping? Elaborate.
Answer:
The simplest way to persist objects in Java is to use Java’s native serialization API that lets you write objects to files. Another popular way to store Java objects is to use a traditional relational database management system (RDBMS) such as Oracle, DB2, Microsoft SQL Server, etc. Rather than serialize an object as a complete bit blob, we would decompose each object inti’) its constituent parts and store each part separately. For instance, for a bank account object, the bank account number could be stored in one relational database field and the bank account balance in another field.

When you save your Java objects, you would use JDBC to ‘map’ the object data into a relational database. When you want to load your objects from the database, you would instantiate an object from that class, read the data in from the database, and then populate that object instance’s field with the relational data read in. This is shown in Figure 13.

This mapping of objects to relational databases is a technology called ofc/ECF-relational mapping. It is the act of converting and upconverting in-memory objects to relational data. An object-relational (OR) mapper may map your objects to any kind of relational database schema.

For instance, a simple object-relational mapping engine might map a Java class to a SQL table definition. An instance of that class would map to a row in that table, while fields in that instance would map to individual cells in that row. This is shown in Figure 14.

Question 11.
Elucidate publish/subscribe messaging domain.
Answer:
When you perform messaging, you first need to choose the messaging domain: publish/subscribe (pub/sub) or point-to-point (PTP). PTP is a special case of pub/sub. So, we shall describe only the latter over here. Publish/subscribe messaging is analogous to watching television: many TV stations broadcast their signals, and many people listen to their broadcasts. Thus, with pub/sub, you can have ‘many’ message producers talking to ‘many’ message consumers. In this sense, the pub/subdomain is an implementation of a distributed event-driven processing model.

Subscribers (listeners) register their interest in a particular event ‘topic’. Publishers (event sources) create messages (events) that are distributed to all of the subscribers (listeners). Producers aren’t hard-coded to know the specific consumers in receiving its messages; rather, the MOM system maintains the subscriber list. You will get an idea of pub/sub vs. P-T-P messaging domains from Figure 15.

Question 12.
What criteria would you employ while choosing between servlets and stateless session beans as Web service endpoints?
Answer:
Here are some guidelines that should help you in choosing between servlets and stateless session beans as Web service endpoints.

Use a servlet as a Web service endpoint if:

1. The business logic of the service is within a Web tier since in this case both the endpoint and Web service’s business implementation will reside in the same tier.
2. You need a lightweight Web service container, viz. servlet container.
3. You need to execute some logic that resides on the Web tier before invoking Web services.
4. You do not mind writing logic for synchronizing multithreaded access to your service. This is required since, the servlet container does not synchronize concurrent requests to the servlet instance and hence, in this case, to your Web service endpoint.

Use a stateless session bean as a Web service endpoint if:

1. The business logic of the service is within an EJB tier since in this case both the endpoint and Web service’s business implementation will reside in the same tier.
2. You need the Web service implementation to avail themselves of the transaction- and component-level security services from the container.
3. You need to execute some logic that resides on the EJB tier before invoking Web services.
4. You want the container to take care of synchronizing concurrent access to your service.

Question 13.
Elucidate Aspect-Oriented Programming (AQP).
Answer:
‘Aspect’ forms the core of Aspect-Oriented Programming (AOP). Aspects are reusable services that are quintessentially cross-cutting of your application. In the context of a business application, services that provide user authentication, user authorization, logging of access to the system, and persistence of application data are examples of cross-cutting services or ‘concerns’ for a business application developer – ‘concerns’ because a developer cannot write robust applications without taking care of them. Hence, AOP can be defined as a programming platform that facilitates the development of ‘aspects’ to mitigate ‘concerns’ so that ‘aspects’ can be ‘reused’ by all living objects within a given environment.

Question 14.
Does the EJB world use AOP techniques?
Answer:
Yes! It does!! All the services that our beans get are ‘aspects’, e.g. persistence, life-cycle management, transaction management, security, and dozens of other things that we, the business application developers, care about. EJB containers implement these cross-cutting ‘concerns’ and provide reusable ‘aspects’ so that all the beans deployed within the container can offload these ‘concerns’ on the container ‘aspects’. However, there is a caveat, viz. the EJB programming model does not allow you to develop new ‘aspects’ to take care of ‘concerns’ that are not supported by the EJB container.

Question 15.
Is AOP different than OOP?
Answer:
Yes! It is!! One of the common traits of both AOP and OOP is reusability. However, OOP instills reusability via inheritance. This works well in a vertical parent-child relationship chain. But, OOP does not work when behavior needs to be used horizontally, owing to the behavior’s cross-cutting nature. Now, why should you want to reuse behavior horizontally? Since you don’t want your business object, e.g. a ShoppingCart, to inherit the behavior pertaining to transactions, because these behaviors are unrelated; you don’t want an apple to inherit grape-like qualities; rather, you want to mix apples and grapes to prepare a margarita. This is exactly where AOP comes into the picture. AOP and OOP are not competing but complementary technologies. Consider, for example, an EJB server where cross-cutting ‘aspects’ are provided to your object-oriented beans. To conclude, OPP and AOP coexist.

Question 16.
Elucidate XML.
Answer:
XML (Extensible Markup Language) is a framework for defining markup languages. In contrast to HTML (HyperText Markup Language), there is no fixed collection of markup tags in XML. Instead, XML lets you define your own tags, tailored and customized to the kind of information you wish to represent. Each XML language is targeted at a particular application domain, but all of them will share many common features such as they all use the same markup syntax and they all benefit from a common set of generic tools for processing documents.

Question 17.
Is XML merely an extension of HTML? Elaborate.
Answer:
XML is not an extension of HTML, nor is it a replacement for HTML, which ideally should be just another XML language. However, because of a number of minor syntactical differences, HTML does not directly fit into the XML framework. As a remedy, the W3C has designed XHTML as an XML variant of HTML.

Question 18.
What are the inherent advantages of using XML?
Answer:
XML has been designed with some simple but powerful principles in mind. First of all, it allows tailor-made markup for any imaginable application domain. As an additional benefit, XML is inherently internationalized and platform-independent. All XML documents are written in the Unicode alphabet which leads to easy internationalization.

Finally, XML is designed and intended to be the future of all structured information. This even includes information stored in relational databases, which has motivated and led to the development of the powerful query language – XQuery. In fact, XML was designed to bring the power of SGML (Standard Generalized Markup Language), which was standardized in 1986, to the Web. A major benefit of using XML is the availability of “generic tools and technologies” that surround the core XML notation. t

Question 19.
When would you use XML with EJB?
Answer:
Using XML with EJB is useful in the following scenarios:-

1. Pbr data-driven integration
2. As a document persistence mechanism
3. As a Web service interface

Question 20.
In the XPath data model, what is an XML tree?
Answer:
In the XPath data model, an XML tree is a special kind of ordered tree whose nodes can be any of the following kinds:-

• Text nodes
• Element nodes
• Attribute nodes
• Comment nodes
• Processing instruction nodes
• Root nodes

Question 21.
Is an XML document just a tree structure, just a textual representation, or both?
Answer:
An XML document can be considered both in its textual representation and as a tree structure.

Question 22.
Elucidate XPath.
Answer:

• XPath is a language used to navigate XML trees.
• The development of several different XML technologies has identified a common need for a flexible notation for pointing into and navigating around XML documents. This has resulted in the XPath language, which is used for:
• uniqueness and scope descriptions in XML Schema;
• for pattern matching and selection in XSLT;
• for selection and iteration in XQuery; and,
• as a key component of XLink and XPointer.

Besides, a part of XPath is able to express computations on data values, which is well exploited in XSLT and XQuery.

Question 23.
Elucidate XPointer and XLink.
Answer:
XPointer and XLink provide hyperlinks in general XML documents. The XPointer language generalizes the ability to point at specific places ins XML documents.

Question 24.
Elucidate XSLT.
Answer:
The XSLT language is used to transform XML documents using XSL (Extensible Stylesheet Language) technology.

1. The XSL technology has two constituents: XSLT (XSL Transformations), which is a declarative programming language for specifying transformations between XML languages, and XSL-LO (XSL Formatting Objects, which is a particular target language suitable for specifying the physical layout.

2. XSLT was originally developed and designed as a generalized stylesheet, intended to fit in the same niche as CSS (Cascading Stylesheets). However, it has developed into a complete programming language with many more applications. The behavior of a stylesheet may be obtained by specifying a transformation into the XHTML language, which enables rendering in a browser. But,

3. XSLT transformations may also be used to translate between other XML languages, extract views of XML data, and perform query-like computations. XSLT transformations may be executed in standalone tools, but they are also supported by all modern browsers. Specifically, an XML document may contain a processing instruction that links to an external XSLT document. A browser will, then load the XSLT file, execute the corresponding transformation and present the result, which will presumably be an XHTML document.

Question 25.
Elucidate XQuery.
Answer:
XML documents naturally generalize database relations. XQuery is the corresponding generalization of SQL. There are many reasons why a merger of XML and databases is attractive. But, the main strategic advantage is that if XML is the future of WWW and XML becomes the future of databases, then in the future, the WWW’ will simply become one gigantic database. That’s why XQuery is designed to generalize SQL, as XML generalizes database tables.

Question 26.
Evaluate XQuery vis-a-vis XSLT.
Answer:
XQuery and XSLT may emulate each other but are designed for different tasks:-

1. While XSLT is exceedingly good at defining complicated recursive traversals and transformations to arbitrary depths of XML documents, while XQuery must use explicit recursion for user-defined functions.

2. Conversely, XQuery has more the flavor of a database programming language and allows simple solutions for simple problems, while XSLT may be more verbose.

Question 27.
Can you elucidate the relation between Web services and XML?
Answer:
Web services support service-oriented architectures (SOA) using in particular, XML and HTTP.

• The basic Web service standards, as already elaborated, are SOAP, WSDL, and UDDI. While SOAP is a protocol for exchanging XML documents, typically but not necessarily using HTTP, WSDL is an interface description language for Web services. Again, U DDI is a system for making clients and servers find each other. And, not surprisingly, all three of them use an XML notation.

Question 28.
Give one feature each of SOAP, WSDL, and UDDI.
Answer:
The main strengths of SOAP are support for intermediaries, fault management, and RPC (Remote Procedure Calls) interactions. A WSDL description of a Web service describes its functionality, data encodings, communication protocols, and location. UDDI allows Web services to be registered and discovered.

 

We have compiled most frequently asked Java J2EE Interview Questions which will help you with different expertise levels.

Java J2EE Interview Questions on Databases & Jdbc

Question 1.
What do you understand by a database?
Answer:
A database is a permanent, self-descriptive store of data that is contained in one or more files. Self-description is what sets a database apart from ordinary files. A database contains the data structure or ‘schema’ – description of data — as well as the data.

Question 2.
What do you understand by DBMS?
Answer:
A database management system (DBMS) is the software for managing access to a database. Some of the reasons for using a DBMS are:-

• Data Protection: A DBMS protects data from accidental loss due to hardware crashes, disk media failures, and application errors.
• Efficiency: A DBMS has efficient algorithms for managing large quantities of data.
• Sharing between users & sharing between applications: Multiple users can access the database at the same time. Multiple application programs can read/write data to the same database.
• Data quality: You can specify rules that data must satisfy.
• Data distribution: You can partition data across various sites, organizations, and hardware platforms. The DBMS keeps the fragmented data consistent.
• Security: A DBMS can restrict the reading/writing of data to authorized users only.

Question 3.
How do you implement one-to-one, one-to-many, and many-to-many relationships while designing tables?
Answer:
The one-to-one relationship can be implemented as a single table and very rarely as two tables with primary and foreign key relationships. The one-to-many relationship can be implemented by splitting the data into two tables with primary and foreign key relationships. Many-to-many relationships can be implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

Question 4.
Bring out the difference between a primary key and a unique key.
Answer:
Both primary key and unique key enforce the uniqueness of the column on which they are defined. But, by default, the primary key creates a clustered index on the column whereas the unique key creates a non-clustered index by default. Also, the primary key doesn’t permit NULL value while the unique key permits NULL value, but only one.

Question 5.
What do you understand by a stored procedure? What are its advantages?
Answer:
A stored procedure is a set of precompiled T-SQL statements, which can be executed whenever required. These are similar to procedures in any other programming language and whence, can accept input parameters, return output parameters, and can even return status values to the calling procedure or batch to indicate success/failure along with the reason for failure, if any.

The main advantages of using stored procedures are:-

1. Increased modularized programming;
2. Faster execution since they are pre-compiled;
3. Reduce the network traffic by sending the call to the stored procedure, instead of sending hundreds of T-SQL lines of code that are embedded in the stored procedure.

Question 6.
What do you mean by triggers? How do you invoke a trigger on demand?
Answer:
Triggers are a special kind of stored procedure that gets executed automatically when an INSERT/UPDATE/DELETE operation takes place on a table. Triggers cannot be invoked on demand. They get triggered automatically as mentioned already. Triggers can be used to extend the referential integrity checks also, but wherever possible, constraints should be used for this purpose instead of triggers since constraints are much faster.

Question 7.
Bring out the difference between a stored procedure and a trigger.
Answer:
The trigger is a special type of stored procedure that cannot be called directly by the user. At the time of creating the trigger, it is defined to be executed when a specific type of data modification such as Insert/Update/Delete is made against a specific table/column.

Question 8.
What do you understand by JDBC?
Answer:
JDBC or Java Database Connectivity, as it is called, is an Application Programming Interface (API), which makes it possible for Java programs to connect to any database, retrieve the data/information from the database and utilize this data for themselves.

Question 9.
What do you understand by DSN?
Answer:
DSN or Daja Source Name, as it is called, is the unique name given to the database in order to identify it in a Java program. It is linked with the actual location of the database.

Question 10.
What do you understand by ResultSet?
Answer:
ResultSet is an object containing the rows (results) of executing an SQL statement upon a database.

Question 11.
What do you understand by stored procedures?
Answer:
A stored procedure means a set of statements stored and executed at the database server and sending the results to the client.

Question 12.
What do you understand by BLOB?
Answer:
BLOB or Binary Large Object, as it is called, represents an SQL datatype that helps in storing LARGE volumes of binary data into the database. By means of BLOBS, you can even store images in the database.

Question 13.
What do you understand by CLOB?
Answer:
CLOB or Character Large Object, as it is called, represents an SQL datatype that helps in storing LARGE volumes of text data into the database. By means of CLOBS, you can even store text files in the database.

Question 14.
Can you explain the use of the CallableStatement?
Answer:
The CallableStatement is used to call stored procedures and functions that run at a database server and get the results to the client.

Question 15.
What do you understand by parsing?
Answer:
Parsing means checking the syntax and grammar of a Java statement as a whole as well as word by word.

Question 16.
What do you understand by a database driver?
Answer:
A database driver represents a set of classes/interfaces written in accordance with the JDBC API in order to communicate with a given database.

Question 17.
How do you register a database driver? Elaborate.
Answer:
A database driver can be registered in any of the following ways:-

1. By using System class get property( ) method;
2. By creating an object to the driver class;
3. By sending the driver class name to the Class.forname( ) method; or,
4. By sending the driver class object to the Driver Manager.registerDriver( ) method.

Question 18.
Does the performance of a JDBC program depend on the driver? Elucidate.
Answer:
Yes! It does!! Each driver performs differently.

Question 19.
What do you understand by an RDBMS?
Answer:
RDBMS or Relational Database Management System, as it is called, is a way to store the information in a database, creating relationships amongst different, related data in order to enable the user to query among different factors and get the maximum range of data from a single query. To put it more simply, it’s a system that organizes data into related rows and columns.

Question 20.
What do you mean by SQL?
Answer:
SQL or Structured Query Language, as it is called, enables:-

1. ACCESS TO A DATABASE;
2. INSERTION/UPDATION/DELETION OF RECORDS IN A DATABASE;
3. RETRIEVAL OF DATA/INFORMATION FROM A DATABASE.

Question 21.
What are the different types of DBMS?
Answer:
DBMS (Database Management System) is essential of the following kinds:-

1. Hierarchical
2. Network
3. Relational
4. Object
5. Object-Relational

Of all these, Relational DBMS is the most widely used one.

Question 22.
Enumerate the main features of a relational database.
Answer:
A relational database has essentially the following features:-

1. It makes no presumptions about the inter-relationships between the data elements;
2. Relationships are represented in the form of one table containing key data from another table.
3. Relations are dynamic and determined as needed.
4. Everything in a relational database is stored in the form of tables which consist of rows and columns.
5. The creation of these tables and their columns is performed using SQL. Similarly, storage and retrieval of data is also carried out using SQL.
6. In formal relational theory, tables are known as relations; whence, these came to be known as relational databases.

Question 23.
What do you understand by an Entity-Relationship (E-R) diagram?
Answer:
One of the best ways to design a database is to draw an image of the tables with the relationships among them. This graphical representation of the database tables is known as an E- R diagram.

Question 24.
What do you mean by referential integrity?
Answer:
Referential integrity prevents users/applications from entering inconsistent data into a table. Referential integrity rules are used when a relationship is created between two tables. Different RDBMSs have different referential integrity rules. As already mentioned, these rules help to maintain the integrity of data, e.g. if we are dealing with a ‘customer’ table and multiple orders from a customer, we can set referential integrity rule in such a manner that all orders from a customer should get deleted before the customer gets deleted; this is commonly referred to as cascading delete.

Question 25.
What do you understand by a primary key?
Answer:
The primary key is used to uniquely identify a row of data in a table. This can be a single column of the table or a combination of more than one column; in the latter case, it’s known as a composite primary key.

Question 26.
What do you mean by a foreign key?
Answer:
A key column in a table that identifies records in a different table is referred to as a foreign key.

Question 27.
What is an alternate key in a table?
Answer:
In a database table, apart from primary key column(s), some other columns may need to be a’ key as well; these are referred to as alternate keys. This column value may or may not be unique.

Question 28.
Elucidate the concept of normalization.
Answer:
Normalization is the process of efficiently organizing data in a database by creating tables and establishing relationships between those tables according to certain normalization rules which have been designed to make the database more flexible by eliminating two factors, viz. redundancy and inconsistent dependency.

There are a few rules for database normalization. Each such rule is referred to as a ‘normal form”. If the first rule is observed, the database is said to be in “first normal form”, and so on.

Question 29.
Elucidate the First Normal Form (INF).
Answer:
INF states that:-

• Eliminate duplicate columns from the same table;
• Create a separate table for each set of related data and identify each such set of related data with a primary key.

Question 30.
Elucidate the Second Normal Form (2NF).
Answer:
2NF states that:-

• Create separate tables for sets of values that apply to multiple records;
• Create relationships between these new tables and their predecessors through the use of foreign keys.

Question 31.
Elucidate the Third Normal Form (3NF).
Answer:
3NF states that:-
Eliminate fields that do not depend on the primary key.

Question 32.
Elucidate the concept of denormalization.
Answer:
Developers can violate normal forms for good cause, such as to increase the performance of a database that is read and seldom updated. Such a relaxation is known as denormalization. The important issue here is to violate normal forms deliberately and only when necessary.

Question 33.
Elucidate the SQL Data Manipulation Language (DML).
Answer:
As previously mentioned, SQL is the syntax for executing queries and updating, inserting, and deleting records.
These query and update commands together form the DML part of SQL:-

1. SELECT: extracts data from a database table
2. UPDATE: updates data in a database table
3. DELETE: deletes data from a database table
4. INSERT INTO: inserts new data into a database table

Question 34.
Elucidate the SQL Data Definition Language (DDL).
Answer:
The DDL part of SQL provides a syntax for creating and deleting database tables. One can also define indexes (keys), specify links between tables and impose constraints between database tables. The most important DDL statements in SQL are the following:-

1. CREATE TABLE: creates a new database table
2. ALTER TABLE: alters (changes) a database table
3. DROP TABLE: deletes a database table
4. CREATE INDEX: creates an index (search key)
5. DROP INDEX: deletes an index

Question 35.
Explain the concepts of transaction and ACID properties.
Answer:
A transaction is a unit of work in which a series of operations occur between the BEGIN TRANSACTION and END TRANSACTION statements of an application. The term ACID stands for Atomic, Consistent, Isolated, and Durable. These properties ensure predictable behavior, reinforcing the role of transactions as all-or-none propositions designed to reduce the management load when there are many variables.

Question 36.
Bring out the difference between DELETE TABLE and TRUNCATE TABLE commands.
Answer:
DELETE TABLE is a logged operation, whence, the deletion of each row gets logged in the transaction log, which makes it slow. On the other hand, TRUNCATE TABLE also deletes all the rows in a table/but it does not log the deletion of each row; instead, it logs the de-allocation of the data pages of the table, making it much faster.

Question 37.
What are constraints? Enumerate the different types of constraints.
Answer:
Constraints enable the database system to enforce the integrity of the data automatically, without the need to create triggers, rules, or defaults. The various types of constraints are:-

1. NOT NULL (a NOT NULL constraint on a column prevents its values from being NULL)
2. CHECK
3. UNIQUE
4. PRIMARY KEY
5. FOREIGN KEY

Question 38.
What are cursors? Enumerate the different types of cursors.
Answer:
A cursor is like a virtual table, with rows and columns specified by the query. A cursor also has the notion of a current row, which in essence is a pointer to the row in use in the virtual table. The different types of cursors are:-

1. Static
2. Dynamic
3. Forward-only
4. Keyset-driven

Question 39.
What is a join? Enumerate the different types of joins.
Answer:
Joins are used in queries to explain how different tables are related. Joins also let you .select data from a table based upon data from another table. The various types of joins are:-

1. Inner join
2. Outer join
3. Self-join
4. Cross join

Question 40.
What do you mean by a self-join? Illustrate with the help of an example.
Answer:
In a self-join, two instances of the same table, are joined in the query. We illustrate this concept with the help of an example. Suppose, there is an employee table containing rows for normal employees as well as managers. To find out the managers of all the employees, you need a self-join.

CREATE TABLE employee
(
Emp_ID int,
Mgr_ID int,
__ Emp__NM char(20)
)
INSERT employee SELECT 1, 2, "Meena”
INSERT employee SELECT 2, 3, “Arunesh”
INSERT employee SELECT 3, NULL, "Harry"
SELECT tempi.Emp_NM [employee], temp2.Emp_NM [Manager]
FROM employee temp1, employee temp2
WHERE tempi.Mgr_ID = temp2.Emp_ID

Question 41.
How does JDBC help in accessing databases?
Answer:
JDBC provides a standard library for accessing relational databases. By using the JDBC (Java DataBase Connectivity) API, you can access a wide variety of SQL databases with exactly the same Java syntax. It is, however, important to point out that although the JDBC API standardizes the approach for connecting to databases,

the syntax for sending queries and v committing transactions, and the data structure representing the result, JDBC does not attempt to standardize the SQL syntax. So, you can use any SQL extensions your database vendor supports. But, since most queries follow standard SQL syntax, using JDBC lets you change database hosts, ports, and even database vendors with minimal changes to your code.

Question 42.
What are the steps involved in using JDBC in general?
Answer:
(1) Load the JDBC driver: To load a driver, you specify the class name of the database driver in the Class.forName method. By doing so, you automatically create a driver instance and register it with the JDBC driver manager.

(2) Define the connection URL: In JDBC, a connection URL specifies the server host, port, and database name with which to establish a connection.

(3) Establish the connection: With the connection URL, username, and password, a network connection to the database can be established. Once the connection is established, database queries can be performed until the connection is closed.

(4) Create a Statement object: Creating a Statement object enables you to send queries
and commands to the database.

(5) Execute a query or update: Given a Statement object, you can send SQL statements to the database by using the execute, executeQuery, executeUpdate, or execute batch methods.

(6) Process the results: When a database query is executed, a Result-Set is returned. The ResultSet represents a set of rows and columns that you can process by calls to next and various getXxx methods.

(7) Close the connection: When you are finished performing queries and processing results, you should close the connection, releasing resources to the database.

Question 43.
Enumerate and elucidate the various types of JDBC drivers.
ANSWER:

Figure 5 Two common JDBC Driver Implementations. JDK 1.4 includes a JDBC-ODBC Bridge; however, a pure JDBC Driver (provided by the vendor) yields better performance JDBC technology drivers fit into one of the following categories according to the Sun Java developer Forum:-

Type 1: A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers. Some ODBC native code and in many cases, native database client code must be loaded on each client machine using this type of installation, and downloading a Java technology application is not important.

Type 2: A native-API partly Java technology-enabled driver converts JDBC (fails into calls on the client API for Oracle, Sybase, DB2, Informix, or other DBMS.’ Some binary code must be loaded on each client machine.

Type 3: A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then further translated to a DBMS protocol by a server. This net server middleware is able to connect all of its technology-based clients to many different databases.

The specific protocol used is vendor-dependent. In general, this is the most flexible JDBC API alternative. All vendors of this solution will most likely provide products for Intranet use. In order for such products to also support Internet access, they must handle the additional requirements for security, access through firewalls, etc. that the Web imposes.

Type 4: A native-protocol fully Java driver converts JDBC technology calls into a 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 type/style of driver. This is the most preferred driver since it improves portability.

Question 44.
Can the ResultSet be used even after closing the connection?
Answer:
No! Not at all! This is due to the fact that the ResultSet becomes invalid once the connection is closed.

Question 45.
Can a connection object be serialized and used from another machine?
Answer:
No! Not at all!! A connection object can’t be serialized and used from another machine This is due to the fact that the connection is created for the current machine IP address.

Question 46.
How do you get hold of the column names fetched in a ResultSet?
Answer:
To retrieve information about the fetched ResultSet, you may use con.getMetaData(‘). It returns a MetaData Object; using which the various properties of the resultset can be fetched.

Question 47.
Elaborate setAutoCommit(true).
Answer:
A transaction is one or more SQL statements forming a logical unit of work. Within one transaction, all the SQL statements must succeed/fail as one logical entity.

Changes are made to the. the database only if all statements in the transaction succeed and a COMMIT is issued. If one or more statements fail, we must issue a ROLLBACK to undo the changes. This ensures the integrity and security of the data in the database.

By setting AutoCommit to false, I’ve prevented the connection from committing the changes unless the commit method is called in the program. We can COMMIT/ROLLBACK in the code depending upon whether all the statements within the transaction succeed/fail.

Question 48.
Can a ResultSet be updated?
Answer:
Yes! Of course!! We can indeed update the database values from a ResultSet.

Question 49.
How can you call a stored procedure from JDBC?
Answer:
We can invoke a stored procedure using CallableStatement as illustrated below:-
CallableStatement dblstmt = con.prepareCa11 (” {call getTestData(?, ?)}”);
clblstmt.registerOutParameterd, java.sql.Types.TINYINT);
clblstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 3);
clblstmt.executeQuery( );
byte b = dblstmt.getByte(1);
java.math.BigDecimal bd = dblstmt.getBigDecimal(2, 3);

In general, calling a stored procedure in a database involves the following six basic steps:-

(1) Define the call to the database procedure: The procedure definition includes escape syntax where the appropriate? defines input and output parameters.

(2) Prepare a CallableStatement for the procedure: You obtain a CallableStatement from a Connection by calling prepare Call.

(3) Register the output parameter types: Before executing the procedure, you must declare the type of each output parameter.

(4) Provide values for the input parameters: Before executing the procedure, you must supply the input parameter values.

(5) Execute the stored procedure: To execute the database stored procedure, call execute on the CallableStatement.

(6) Access the returned output parameters: Call the corresponding getXxx method, according to the output type.

Question 50.
What do you understand by connection pooling?
Answer:
Connection pooling is a technique that allows multiple clients to share a cached set of connection objects that provide access to a database resource. It is actually implemented in different ways by different application server vendors.

Question51.
Will the following code compile?

Give appropriate reason(s) for your answer.
     import java.sql.*;
     import java.util.*;
     class TestingltOut
    {
         Date dt = new Date(2012,10, 01);
         System.out.println(dt.toString( ));
    }

Answer:
No! Not at all!! This is due to the fact that both sql and util packages have the Date class; the compiler is in a fix and doesn’t know which one to use!!!

We have compiled most frequently asked Java J2EE Interview Questions which will help you with different expertise levels.

Java J2EE Interview Questions on Threading

Question 1.
What do you understand by multithreading?
Answer:
Java has built-in support for multithreaded programming. A multithreaded program contains two or more parts that can run concurrently. Each such part is called a thread, and each thread defines a separate path of execution. A text editor formatting its text and printing at the same time is a good example of multithreading. In this way, we see that multithreading is a specialized version of multitasking.

Question 2.
Can multithreading lead to problems at times?
Answer:
Multithreading can lead to problems at times especially when you create too many threads. In such a case, you actually degrade the performance of your program rather than enhance it as multithreading is supposed to do. This is because some overhead is always associated with context switching. If you create too many threads, more CPU time will be spent changing contexts rather than executing your program! You must always remember and be aware of this vital fact.

Question 3.
What is the use of the isAlive( ) method?
Answer:
How can one thread know when another thread has ended? You can call isAlive( ) on the thread. This method is defined by Thread, and its most general form is as follows:-

final boolean isAlive( )

The isAlive( ) method returns true if the thread upon which it is called is still running and false otherwise.

Question 4.
What is the use of the join( ) method?
Answer:
There is a second way to know when a thread has ended apart from the isAlive( ) method described above in Q. 229. While isAlive( ) is occasionally useful, the method, which you will more often use to wait for a thread to finish is called join(), whose most general form is as follows:-

final void join( ) throws InterruptedException

This method waits until the thread on which it is called terminates/finishes. Its name comes from the concept of the calling thread waiting until the specified thread joins it. Apart from the form of joint) elaborated above, there are additional forms, which allow you to specify the maximum amount of time that you want to wait for the specified thread to terminate/finish.

Question 5.
What do you mean by a deadlock?
Answer:
There is a special type of error relating specifically to multitasking that you need to avoid. It is known as a deadlock, which occurs when two threads have a circular dependency on a pair of synchronized objects.

Question 6.
Can the thread priority be changed? What is the default priority assigned to a thread?
Answer:
The thread priority can indeed be changed ranging from a minimum one to a maximum one; the default priority assigned to a thread is 5.

Question 7.
Elucidate the daemon thread.
Answer:
A continuously executing thread such as the one providing background processing (e.g. the garbage collector thread) is referred to as a daemon thread; daemon threads act as service providers for other objects/threads.

Question 8.
Explain the thread lifecycle.
Answer:
A thread: is created using the new Thread( ) statement
is executed using the start( ) method
now, enters the ‘runnable state’
when blocked on I/O or when wait()/sleep( ) methods are used, enters ‘not runnable state’
comes back into a ‘runnable state’ continuing to run/execute the statements
finally, ‘dies’ after coming out of the run( ) method
ALL THE AFOREMENTIONED TRANSITION STATES OF A THREAD ARE REFERRED TO AS ITS LIFECYCLE (Figure 3).

Question 9.
What do you understand by a monitor?
Answer:
A monitor is a lock on an object which allows only one thread to access or modify the contents of that object.

Question 10.
How can you make code thread-safe?
Answer:
The keyword ‘synchronized’ is used to temporarily lock an object to have exclusive access to it. It marks a block of code or an entire method as a critical section. Only one thread can execute at any point in time. Other threads will wait for their turn to use the critical section.

When an instance method is synchronized, the synchronized code can be run by many threads on different objects simultaneously, since the locking is on the object. For a synchronized class method, the locking is on the class object, which thus limits to only one thread executing that particular code.

Question 11.
What is the purpose behind Thread. wait( )?
Answer:
Thread.wait( ) and Thread. notify( ) methods are used for inter-thread communication.
Thread. wait( ) makes the thread go to sleep until some other thread wakes it up using the Thread.notify( ) method.

Question 12.
Bring out the difference between Thread. notify( ) and Thread. notifyall ( ).
Answer:

Thread. notify( ): This method can only be called from within a synchronized
method/block. It wakes up a single thread that is waiting on the object’s lock. In case there is more than one thread waiting, the choice is arbitrary, i.e. there is no particular way to determine which of the waiting threads should be awakened.

Thread.notifyAll( ): This method wakes up all the waiting threads. In the JVM the scheduler then decides which of the awakened threads will execute.

Question 13.
How do you invoke a thread’s run( ) method?
Answer:
After a thread is started through its start( ) method or that of the Thread class, the JVM scheduler invokes the thread’s run() method once the turn comes for this thread to run/ execute.

Question 14.
Can you elucidate the concept of thread synchronization?
Answer:
Two threads are said to be synchronized if while the first one is acting on an object, the second one doesn’t act on that object and while the second one acts on an object, the first one doesn’t act on that object. This concept of thread synchronization is especially handy in multithreading.

Question 15.
Differentiate between a ‘synchronized block’ and the ‘synchronized’ keyword.
Answer:
While a ‘synchronized block’ synchronizes a block of statements, the ‘synchronized’ keyword is used to synchronize a complete method.

Question 16.
Differentiate between sleep( ) and wait( ).
Answer:
Both of these methods are used to suspend thread execution for a specified period. But, when sleep( ) is executed inside a synchronized block, the object continues to be under lock;
on the other hand, when wait( ) is executed inside a synchronized block, the lock is removed. In general, sleep( ) is used to make a thread wait for some time while wait( ) is used alongside notify( ) / notifyAll( ) in multithreading.

Question 17.
Does the thread execute any method by default; if yes, which one?
Answer:
Yes, it does! The public void run() method.

Question 18.
Differentiate between “implements Runnable” and “extends Thread”
Answer:
Functionally, both of them are the same but by using “extends Thread”, we cannot extend another class since Java doesn’t support multiple inheritances; on the other hand, if we use “implements Runnable”, we can still extend another class; This feature is especially handy when we want to use threads as well as access the characteristics of another class.
class class1 extends Thread, class2                        // not okay
class class1 extends class2 implements Runnable // okay

Question 19.
Can you stop a thread in Java; if yes, how?
Answer:
Yes, it is indeed possible to stop a thread in Java. This can be achieved by creating a boolean type variable that is initialized to ‘false’ and later on when we want to stop the thread, we should make its value ‘true’; the status of this variable is checked in the run() method and since it is true, the thread executes the return statement and then stops executing.

Question 20.
Which is the thread that always runs in a Java program by default?
Answer:
The main thread.

Question 21.
In how many ways can you create a thread? Elaborate.
Answer:
There are essentially two ways of creating a thread:-

(1) The first method involves extending the Thread class. This should only be done in case the class doesn’t need to be extended from another class.

import java. lang.*;
public class Cntr extends Thread
{
      public void run( )
      {...............}
}

Here, a new class Cntr is being created that extends the Thread class and overrides the Thread. run( ) method. The run( ) method is where all the work of the Cntr class thread is being done.

(2) The second way involves implementing the interface Runnable.

import java. lang.*;
public class Cntr implements Runnable
{
      Thread T;
      public void run( )
     {....................}
}

In this case, the abstract method run( ) is defined in the Runnable interface and is being implemented. Note that we have an instance of the Thread class as a variable of the Cntr class.

The only difference between the two methods is that by implementing Runnable, there is greater flexibility in the creation of the class Cntr, i.e. there still is an opportunity to extend the class Cntr, if needed.

Question 22.
What is the purpose of Thread. start( )?
Answer:
Thread. start( ) creates the system resources necessary to run the thread and schedules it to run. After the start method has returned, the thread is actually in the Runnable state. When a thread gets the CPU time, it will be executed.

Question 23.
Explain the concept of thread prioritization.
Answer:
Execution of multiple threads on a single CPU, in some order, is called scheduling. The Java runtime supports a very simple, deterministic scheduling algorithm known as fixed priority scheduling. This algorithm schedules threads based on their priority relative to other runnable threads.

When a java thread is created, it inherits its priority from the thread that created it. A thread’s priority can also be modified at any time after its creation using the setPriority( ) method.

Thread, priorities are integers ranging between MIN_PRIORITY (1) and MAX__PRIORITY (10). The value 5 is the default priority. The greater the integer, the higher the priority of the corresponding thread.

At any given time, when multiple threads are ready to be executed, the java runtime system chooses the runnable thread with the highest priority for execution. Only when that thread stops, yields, or becomes not runnable for some reason will a lower priority thread start executing.

If two threads of the same priority are waiting for the CPU, the scheduler chooses one of them in a round-robin fashion. The chosen thread will run until a higher priority thread becomes runnable, or, it yields (or, its run method exits), or, its time allotment has expired (on systems that support time-slicing). Then, the second thread is given a chance to run, and so on, until the interpreter exits.

Java runtime system’s thread scheduling algorithm is preemptive as well. Thus, if at any time, a thread with a higher priority than all other runnable threads becomes runnable, the runtime system chooses the new higher priority thread for execution. This new higher priority thread is said to have preempted the other threads.

Question 24.
Bring out the difference between yield( ) and sleep( ) methods.
Answer:
When a thread invokes its yield() method, it returns to its ready state. On the other hand, when a thread invokes its sleep( ) method, it returns to its waiting state.

Question 25.
Bring out the difference between preemptive scheduling and time slicing.
Answer:
Under preemptive scheduling, the highest priority thread executes until it enters its waiting or dead states or a higher priority thread comes into existence. On the other hand, under time slicing, a thread executes for a predefined slice of time and then re-enters the pool of ready threads. The scheduler then determines which thread should execute next, based upon priority and other factors.

Question 26.
Explain in brief the concepts of synchronized methods and synchronized statements.
Answer:
Synchronized methods are methods that are used to control access to an object. A thread only executes a synchronized method after it has acquired the lock for the method’s object or class.

Synchronized statements are similar to synchronized methods. A synchronized statement can only be executed after a thread has acquired the lock for the object or class referenced in the synchronized statement.

This means that no other thread can access the object till the current thread releases the object. This is used to make the code thread-safe and prevent dirty reads.

Question 27.
Linder, what condition(s) do you use synchronized statements?
Answer:
Synchronized statements are good to use in two cases:-
(1) It is sufficient to synchronize only part of a method, e.g.

void mthd( )
{
     // some statement
     Synchronized( this)
     {
          this.x = 100;
     }
         // some statement
}

(2) If a class whose object to be locked is part of a third part jar file, e.g.

void mthd( )
{
      // some statement
      Synchronized(thirdPa rty Object)
      {
         thirdParlyObject.x = 100;
      }
      // some statement
}

Question 28.
Can you restart a dead thread; if yes, how?
Answer:
A dead thread cannot be restarted.

Question 29.
Under what condition(s) does a thread go out of the runnable state?
Answer:
A thread becomes Not Runnable when one of the following events occurs:-

1. Its sleep method is invoked.
2. The thread is blocking on Input/Output.
3. The thread calls the wait method to wait for a specific condition to be fulfilled.

We have compiled most frequently asked Java J2EE Interview Questions which will help you with different expertise levels.

Java J2EE Interview Questions on Networking

Question 1.
What is the client/server model in the context of networking?
Answer:
The term client/server is often heard in the context of networking. A server is anything that has some resource that can be shared. Thus, there are compute servers that provide computing power; print servers that manage a collection of printers; disk servers that provide networked disk space; and web servers which store web pages. And, a client is simply any other entity that wants to gain access to a particular server.

A network can have several clients and servers and this architecture is aptly called the client/ server architecture. Figure 2 shows a simple client/server model. In this simple model, it can be easily seen that the client sends a request to the server and the server responds upon receiving the request.

Question 2.
Elaborate on the advantages of a network.
Answer:
There is essentially one chief advantage that we derive by using a network, viz. it helps in the optimal utilization of the available system resources by making resource sharing possible amongst the connected systems on the network.

Question 3.
What do you mean by ‘throughput’?
Answer:
In computer parlance, throughput is the amount of work a computer network can perform during a given period of time.

Question 4.
What do you understand by a firewall?
Answer:
A firewall is simply a mechanism that isolates a network from the rest of the Internet, allowing only specific traffic to pass through it.

Question 5.
Why is data encryption required?
Answer:
Data traversing unsecured networks is open to many kinds of attacks. Data can be read, altered, or forged by anybody who has access to the route your data takes, e.g. a protocol analyzer (sniffer) can read packets and gain classified information, or, an intimidating party can tamper with packets and cause damage by hindering, reducing, or preventing network communications within your organization.
Encryption provides a means to safeguard network data that travels from one router to another across unsecured networks. Encryption is particularly important if certain confidential/critical data is being sent.

Question 6.
What do you understand by public-key encryption?
Answer:
Any cryptographic system essentially employs two keys for encryption/decryption, viz. the public key known to everyone and a private (secret) key known only to the recipient of the message. When device X wants to send a secure message to device Y, it uses device Y’s public key to encrypt the message. Device Y then uses its private key to decrypt the message sent by device X.

An important element to the public key system is that the public and private keys are related in such a manner that only the public key can be used to encrypt the messages and only the corresponding private key can be used to decrypt them. Moreover, it is virtually impossible to deduce the private key if you know the public key.

The only difficulty with public-key systems is that you must know the recipient’s public key to encrypt a message.

Question 7.
Elucidate digital signatures/certificates.
Answer:
Digital signatures/certificates are essentially attachments to an electronic message used for purposes of security. The most common use of digital certificates is to verify that a user sending a message is the one he/she claims to be and to provide the receiver with the means to encode a reply.

An individual wishing to send an encrypted message applies for a digital certificate from a Certificate Authority (CA). The CA issues an encrypted digital certificate containing the applicant’s public key and a variety of other identification information. The CA makes its own public key readily available through print publicity or perhaps on the Internet.

The recipient of an encrypted message uses the CA’s public key to decode the digital certificate attached to the message, verifies it as issued by the CA, and then obtains the sender’s public key and identification information held within the certificate. With this information, the recipient can send an encrypted reply.

The most widely used standard for digital certificates is X.509.

Question 8.
What is the use of the URLs?
Answer:
The Uniform Resource Locators (URLs) provide a reasonably intelligible form to uniquely identify/address information on the Internet.

Question 9.
What is the most widely used protocol on the Internet?
Answer:
hypertext transfer protocol (HTTP) is the most widely used protocol on the Internet. Using this protocol, the text from one machine to another is sent/received.

Question 10.
Why is Java suitable for the Internet?
Answer:
Java suits the Internet just about in the possible way. But, there are specifically two main reasons. First Java, the being system independent, is portable and can be run on any kind of system on the Internet. In other words, Java’s versatility is the mainstay of the Internet. And, secondly, Java with its strong type checking and lack of pointers is especially suitable for the Internet by eliminating most of the security concerns of the Internet.

Question 11.
What do you understand by a socket?
Answer:
A socket is essentially a connection point between a client and a server on the same network. t

Question 12.
Does the port number identify a socket uniquely?
Answer:
Yes! It does!!

Question 13.
What do you understand by IP address?
Answer:
An IP address is a unique identification number allotted to every computer on a network/Internet. It contains some unique bytes identifying the network and the actual inside the network.

Question 14.
What do you understand by DNS?
Answer:
DNS or Domain Naming Service, as it is called, is a service provided by the Internet that helps in mapping the IP addresses with their corresponding website names.

Question 15.
What is the full form of JSSE?
Answer:
Java Secure Socket Extension.

Question 16.
What are proxy servers?
Answer:
A proxy server speaks the client side of a protocol to another server. This is often required when clients have certain restrictions on which servers they can connect to. Thus, a client would connect to a proxy server, which did not have such restrictions, and the proxy server would, in turn, communicate for the client.

We have compiled most frequently asked Java J2EE Interview Questions which will help you with different expertise levels.

Java J2EE Interview Questions on APPLETS

Question 1.
Define an applet in Java.
Answer:
There are two classes of programs in Java, namely, the applications and the applets. Applications are the normal programs while applets are small applications that are accessed on an Internet server, transported over the Internet, automatically installed, and run as part of a Web document.

Question 2.
What are the two packages that all applets must import?
Answer:

1. java.applet; and,
2. java.awt.

Question 3.
What do you understand by double buffering?
Answer:
Images are not only useful for storing pictures, but you can also use them as offscreen drawing surfaces. This allows you to render any image, including text and graphics, to an offscreen buffer which you can display later on. The main advantage of doing this is that the image is seen only when it is complete. Drawing a complicated image could take several milliseconds or more,

which can be seen by the user as flashing/flickering. This flickering/flashing is distracting and causes the user to perceive your rendering as slower than it actually is. The use of an offscreen image to reduce flicker is known as double buffering, since the screen is considered as a buffer for pixels, and the offscreen image is the second buffer, where you can prepare pixels for display.

Question 4.
What are the methods called in order when an applet begins and ends, respectively?
Answer:
When an applet begins, the AWT calls the following methods in order

• init( )
• start( )
• paint( )

When an applet terminates/ends, the following order of methods comes into play:-

• stop( )
• destroy( )

Question 5.
What is Hotjava?
Answer:
Hotjava is a Web browser fully programmed in Java developed by the two Sun programmers, Patrick Naughton and Jonathan Payne. Hotjava enabled the running of Java applets on web pages. Subsequently, Java applets became implementable in other Web browsers such as Netscape Navigator.

Question 6.
Why is Java important to the Internet?
Answer:
Java’s ability to create and run applets and its portability because of its ability to create bytecodes to be run by the Java Virtual Machine (JVM) render it extremely suitable for the internet.

Question 7.
Does Java support pointers? If not, why not?
Answer:
Java does not support or allow pointers. The reason for that is simple. Java cannot allow pointers as by doing so, Java applets would be able to breach the firewall between the Java Runtime Environment and the host computer (this is because pointers can be assigned any address in memory – even those outside the Java runtime systems).

C/C++ programmers make extensive use of pointers and they might be inclined to think that their loss puts Java at a significant disadvantage. But, this is false. Java is designed in such a manner that as long as one stays within the boundaries of its execution environment, he will never feel the need to use a pointer. Moreover, he would not get any additional benefit by doing that either.
We must also add here that C/C++ code making use of pointers can be converted conveniently to Java code that makes no use of such pointers.

Question 8.
Can you enumerate the tag used to embed an applet into a WEB/HTML page?
Answer:
The relevant tag used is <APPLET>.

Question 9.
Where are the applets executed?
Answer:
Applets are executed by the applet engine similar in working to a virtual machine existing in the web browser at the client-side.

Question 10.
Enumerate the main points of difference between an application and an applet.
Answer:
There are basically two main points of difference between an application and an applet enumerated as under:-

1. Applets are useful for creating dynamic and interactive web applications. An applet runs under the control of a browser whereas an application runs as a stand-alone program on the desktop.

2. An applet is subjected to tougher security restrictions in terms of file and network access whereas an application can easily access the file system and network.

We have compiled most frequently asked SQL Server Interview Questions which will help you with different expertise levels.

SQL Server Interview Questions on Transaction and Leeks

Question 1.
What is “Database Transactions “?
Answer:
It’s a unit of interaction within a database that should be independent of other transactions.

Question 2.
What is ACID?
Answer:
“ACID” is a set of rule which are laid down to ensure that “Database transaction” is reliable. A database transaction should principally follow the ACID rule to be safe. “ACID” is an acronym which stands for:-

• Atomicity

A transaction allows for the grouping of one or more changes to tables and rows in the database to form an atomic or indivisible operation. That is, either all of the changes occur or none of them do. If for any reason the transaction cannot be completed, everything this transaction changed can be restored to the state it was in prior to the start of the transaction f via a rollback operation.

• Consistency

Transactions always operate on a consistent view of the data and when they end always leave the data in a consistent state. Data may be said to be consistent as long as it conforms to a set of invariants, such as no two rows in the customer table have the same customer id and all orders have an associated customer row. While a transaction executes these invariants may be violated, but no other transaction will be allowed to see these inconsistencies, and ‘ all such inconsistencies will have been eliminated by the time the transaction ends.

• Isolation

To a given transaction, it should appear as though it is running all by itself on the database. The effects of concurrently running transactions are invisible to this transaction, and the, effects of this transaction are invisible to others until the transaction is committed.

• Durability

Once a transaction is committed, its effects are guaranteed to persist even in the event of subsequent system failures. Until the transaction commits, not only are any changes made by that transaction not durable but are guaranteed not to persist in the face of a system failure, as crash recovery will roll back their effects.

The simplicity of ACID transactions is especially important in a distributed database environment where the transactions are being made simultaneously.

Question 3.
What are “Begin Trans”, “Commit Tran”, “Rollback Tran” and “Save Tran”?
Answer:
Begin Tran: – It’s a point that says that from this point onwards we are starting the transaction.
Commit Tran: – This is a point where we say we have completed the transaction. From this point, the data is completely saved into the database.
Rollback Tran: – This point is from where we go back to the start point that i.e. “Begin Tran” stage.
Save Tran: – It’s like a bookmark for rollback to come to some specified state. When we say “rollback Tran” we go back directly to “Begin Tran”, but what if we want to go back to some specific point after “Begin Tran”. So “Save Tran” is like bookmarks that can be used to come back to that state rather than going directly to the start point.

There are two paths defined in the transaction one which rollbacks to the main state and the other which rollbacks to a “tran1”. You can also see “tran1” and “tran2” are planted in multiple places as bookmarks to roll back to that state.

Brushing up the syntaxes

To start a transaction 
BEGIN TRAN Tran1
Creates a book point 
SAVE TRAN PointOne
This will roll back to point one
ROLLBACK TRAN PointOne
This commits complete data right when Begin Tran point 
COMMIT TRAN Tran1

Question 4.
(DB)What are “Checkpoint’s” in SQL Server?
Answer:
In normal operation, everything that is done by SQL Server is not committed directly to the database. All operation is logged in to “Transaction Log” first. “Checkpoint” is a point that signals SQL Server to save all data to the main database. If there are no “Checkpoints” then the log file will get full.

You can use the “CHECKPOINT” command to commit all data into SQL SERVER. “Checkpoint” command is also fired when you shut the SQL Server, that’s why it takes a long time to shut down.

Question 5.
(DB)What are “Implicit Transactions”?
Answer:
In order to initiate a transaction, we use “Begin Tran Tran1” and later when we want to save complete data we use “Commit Tran <TransactionName>”. In SQL Server you can define to start a transaction by default i.e. without firing “Begin Tran Trl”. You can set this by using:-
SET IMPLICIT_TRANSACTIONS ON
So after the above command is fired any SQL statements that are executed will be by default in a transaction. You have to only fire “Commit Tran transaction Name>” to close the transaction.

Question 6.
(DB)Is it good to use “Implicit Transactions”?
Answer:
No. If the case developer forgets to shoot the “Commit Tran” it can open a lot of transaction’s which can bring down SQL Server Performance.

Question 7.
What is Concurrency?
Answer:
In a multi-user environment, if two users are trying to perform operations (Add, Modify and Delete) at the same time is termed “Concurrency”. In such scenarios, there can be a lot of conflicts about the data consistency and following ACID principles.

For instance, the above figure depicts the concurrency problem. “Mr. X” started viewing “Record” after some time “MR Y” picks up “Record” and starts updating it. So “Mr. X” is viewing data that is not consistent with the actual database.

Question 8.
How can we solve concurrency problems?
Answer:
Concurrency problems can be solved by implementing a proper “Locking strategy”. In short by “Locking”. Locks prevent action on a resource to be performed when some other resource is already performing some action on it.

In our first question, we saw the problem above is how locking will work. “Mr. X” retrieves “Record1” and locks it. When “Mr. Y” comes in to update “Record1” he can not do it as it’s been locked by “Mr. X”.

Note   What I have showed is small glimpse, in actual situations there are different types of
           locks we will going through each in the coming questions.

Question 9.
What kind of problems occurs if we do not implement a proper locking strategy?
There are four major problems that occur:-

• Dirty Reads
• Unrepeatable reads
• Phantom reads
• Lost updates

Question 10.
What are “Dirty reads”?
Answer:

“Dirty Read” occurs when one transaction is reading a record which is part of a half-finished work of another transaction. The above figure defines the “Dirty Read” problem in a pictorial format. I have defined all activities in Step’s which shows in what sequence they are happening (i.e. Step1, Step 2, etc).

• Step1: -“Mr. Y” Fetches “Record” which has “Value=2” for updating it.
• Step2:- In the meantime “Mr. X” also retrieves “Record1” for viewing. He also sees it as “Value=2”.
• Step3:- While “Mr. X” is viewing the record, concurrently “Mr. Y” updates it as “Value=5”. Boom… the problem “Mr. X” is still seeing it as “Value=3”, while the actual value is “5”.

Question 11.
What are “Unrepeatable reads”?
Answer:
In every data read if you get different values then it’s an “Unrepeatable Read” problem. Let’s try to iterate through the steps of the above-given figure:-

• Step1:- “Mr. X” gets “Record” and sees “Value=2”.
• Step2:- “Mr. Y” meantime comes and updates “Record1” to “Value=5”.
• Step3:- “Mr. X” again gets “Record1” ohh… values are changed “2” … Confusion.

Question 12.
What are “Phantom rows”?
Answer:

If “UPDATE” and “DELETE” SQL statements seem to not affect the data then it can be a “Phantom Rows” problem.

• Step1:- “Mr. X” updates all records with “Value=2” in “recordl” to “Value=5”.
• Step2:- In meantime “Mr. Y” inserts a new record with “Value=2”.
• Step3:- “Mr. X” wants to ensure that all records are updated, so issues a select command for “Value=2″….surprisingly find records which “Value=2″…

So “Mr. X,” thinks that his “UPDATE” SQL commands are not working properly.

Question 13.
What are “Lost Updates”?
Answer:

“Lost Updates” are scenario where one updates which are successfully written to the database are over-written with other updates of other transaction. So let’s try to understand all the steps for the above figure:-

• Step1:- “Mr. X” tries to update all records with “Value=2” to “Value=5”.
• Step2:- “Mr. Y” comes along at the same time and updates all records with “Value=5”
  to “Value=2”. ‘
• Step3:- Finally the “Value=2” is saved in the database which is inconsistent according to “Mr. X” as he thinks all the values are equal to “2”.

Question 14.
What are the different levels of granularity of locking resources?
Answer:
Extent-Extent is made of one or more pages. So all pages are locked and data inside those pages are also locked.
Page: – Page lock puts a lock on all data, tables, and indexes on the page.
Database:-If you are making database structure changes then the whole database will be locked.
Table:-We can also lock objects at a table level. That means indexes related to it also are locked.
Key: – If you want to lock a series key of indexes you can place a lock on those groups of records.
Row or Row Identifier (RID):-This is the lowest level of locking. You can lock data on a low level.

Question 15.
What are the different types of Locks in SQL Server?
Answer:
Below are the different kinds of locks in SQL Server:-

1. Shared Locks (S): – These types of locks are used while reading data from SQL Server. When we apply a Shared lock on a record, then other users can only read the data, but modifying the data is not allowed. Other users can add new records to the table but can not modify the row which has a shared lock
applied to it.

2. Exclusive Locks (X):- These types of locks are not compatible with any other type of locks. As the name suggests any resource which is having exclusive locks will not allow any locks to take over it. Nor it can take over any other type of lock. For instance, if a resource is having a “Shared” lock on a resource you can not make an “Exclusive lock” over the resource. They are specially used for “Insert”, “Update” and “Delete” operations.

3. Update Locks (U):- “Update” locks are in a mid-level between “Shared” and “Exclusive” locks. When SQL Server wants to modify data and later promote the “Update” locks to “Exclusive” locks then “Update” locks are used. “Update” locks are compatible with “Shared” locks.

Ok just to give a brief of how the above three locks will move in the actual environment. Below is the figure which shows the sequence of “SQL” steps executed and the locks they are trying to acquire on it.

 

Step1:- The first transaction issues a “SELECT” statement on the resource, thus acquiring a “Shared Lock” on the data.

Step2:- The second transaction also executes a “SELECT” statement on the resource which is permitted as “Shared” lock is honored by “Shared” lock.

Step3:- Third transaction tries to execute an “Update” SQL statement. As it’s an “Update” statement it tries to acquire an “Exclusive”. But because we already have a “Shared” lock on it, it acquires an “Update” lock. ,

Step4:- The final transaction tries to fire “Select” SQL on the data and tries to acquire a “Shared” lock. But it can not do until the “Update” lock mode is done.

So first “Step4” will not be completed until “Step3” is not executed. When; “Step1” and “Step2” are done “Step3” make the lock into “Exclusive” mode and updates the data. Finally “Step4” is completed.

1. Intent Locks: – When SQL Server wants to acquire a “Shared” lock or an “Exclusive”, lock below the hierarchy you can use “Intent” locks. For instance one of the transactions has been acquired as table lock and you want to have a row-level lock you can i use “Intent” locks. Below are different flavors of “Intent” locks but with one main ‘ intention to acquire locks on the lower level:-

1. Intent locks include: j
2. Intent shared (IS)
3. Intent exclusive (IX)
4. Shared with intent exclusive (SIX)
5. Intent update (IU)
6. Update intent exclusive (UIX)
7. Shared intent update (SIU)

2. Schema Locks: – Whenever you are doing any operation which is related to “Schema” ! operation this lock is acquired. There are basically two types of flavors in this:- ;

• Schema modification lock (Sch-M):- Any object structure change using ALTER, DROP, CREATE etc will have this lock.
• Schema stability lock (Sch-S) – This lock is to prevent “Sch-M” locks.

These locks are used when compiling queries. This lock does not block any transactional locks, but when the Schema stability (Sch-S) lock is used, the DDL operations cannot be performed on the table.

• Bulk Update locks:-Bulk Update (BU) locks are used during bulk copying of data into a table. For example when we are executing a batch process at midnight over a . database.
• Key-Range locks: – Key-Range locks are used by SQL Server to prevent phantom insertions or deletions into a set of records accessed by a transaction.

Below are different flavors of “Key-range” locks

• RangeI_S
• RangeI_U
• RangeI_X
• RangeX_S
• RangeX_U

Question 16.
What are different Isolation levels in SQL Server?
Answer:

Twist: – What is an Isolation level in SQL Server?

Locking protects your data from any data corruption or confusion due to multi-user transactions. An isolation level determines how sensitive are your transaction in respect to other transactions. How long the transaction should hold locks to protect from changes done by other transactions. For example, if you have a long exclusive transaction, then other transactions that want to take over the transaction have to wait for quite a long time. So, the isolation level defines the contract between two transactions how they will operate and honor each other in SQL Server. In short how much is on transaction isolated from other transactions?

Question 17.
What are the different types of Isolation levels in SQL Server?
Answer:
Following are different Isolation levels in SQL Server:-

• READ COMMITTED
• READ UNCOMMITTED
• REPEATABLE READ
• SERIALIZABLE

Note By default SQL Server has a "READ COMMITTED"Isolation level.

Read Committed

Any “Shared” lock created using “Read Committed” will be removed as soon as the SQL statement is executed. So if you are executing several “SELECT” statements using “Read Committed” and “Shared Lock”, locks are freed as soon as the SQL is executed.
But when it comes to SQL statements like “UPDATE / DELETE AND INSERT” locks are held during the transaction.
With “Read Committed” you can prevent “Dirty Reads” but “Unrepeatable” and “Phantom” still occurs.

Read Uncommitted

This Isolation level says “do not apply any locks”. This increases performance but can introduce “Dirty Reads”. So why is this Isolation level in existence?. Well sometimes when you want that other transactions does not to get affected and you want to draw some blurred report, this is a good isolation level to opt for.

Repeatable Read

This type of reading prevents “Dirty Reads” and “Unrepeatable reads”.

Serializable

It’s the king of everything. All concurrency issues are solved by using “Serializable” except for “Lost update”. That means all transactions have to wait if any transaction has a “Serializable” isolation level.

Note Syntax for setting isolation level:-

SET TRANSACTION ISOLATION LEVEL <READ COMMITTED/READ UNCOMMITTED/REPEA TABLE READ/SERIALIZABLE>

Question 18.
If you are using COM+ what “Isolation” level is set by default?
Answer:
In order to maintain integrity, COM+ and MTS set the isolation level to “SERIALIZABLE”.

Question 19.
What are “Lock” hints?
Answer:
This is for more control on how to use locking. You can specify how locking should be applied in your SQL queries. This can be given by providing optimizer hints. “Optimizer” hints tell SQL Server that escalate me to this specific lock level. For example, the below query says to put table lock while executing the SELECT SQL.

SELECT * FROM MasterCustomers WITH (TABLOCKX)

Question 20.
What is a “Deadlock”?
Answer:
Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to roll back is the transaction that is aborted.

Question 21.
What are the steps you can take to avoid “Deadlocks”?
Answer:
Below are some guidelines for avoiding “Deadlocks”

1. Make the database normalized as possible. As more small pieces the system is better granularity you have to lock which can avoid a lot of clashing.

2. Do not lock during the user is making input to the screen, keep lock time as minimum as possible by good design.

3. As far as possible avoid cursors.

4. Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there. ‘

5. Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.

6. If appropriate, reduce lock escalation by using the ROWLOCK or PADLOCK.

7. Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.

8. If appropriate, use as low of an isolation level as possible for the user connection running the transaction.

9. Consider using bound connections.

Question 22.
(DB)How can I know what locks are running on which resource?
Answer:
In order to see the current locks on an “object” or a “process” expand the management tree and right-click on the “Activity” tab. So in case you want to see “deadlocks” or you want to terminate the “deadlock” you can use this facility to get a bird-eye view.

We have compiled most frequently asked SQL Server Interview Questions which will help you with different expertise levels.

SQL Server Interview Questions on Database Optimization

Question 1.
What are indexes?
Answer:
Index makes your search faster. So defining indexes to your database will make your search faster.

Question 2.
What are B-Trees?
Answer:
Most of the indexing fundamentals use “B-Tree” or “Balanced-Tree” principle. It’s not a principle that is something is created by SQL Server but is a mathematical derived fundamental.

Above is a sample diagram which explains how B-Tree fundamental works. The above diagram is showing how index will work for number from 1-50. Let’s say you want to search 39. SQL Server will first start from the first node i.e. root node.

It will see that the number is greater than 30, so it moves to the 50 node.

• Further in Non-Leaf nodes it compares is it more than 40 or less than 40. As it’s less than 40 it loops through the leaf nodes which belong to 40 nodes.

You can see that this is all attained in only two steps.. .faster aaah. That is how exactly indexes work in SQL Server.

Question 3.
I have a table which has lot of inserts, is it a good database design to create indexes on that table?
Answer:

Twist: – Insert’s are slower on tables that have indexes, justify it?
Twist: – Why do page splitting happen?

“B-Tree” stands for balanced tree. In order that “B-tree” fundamental work properly both of the sides should be balanced. All indexing fundamentals in SQL Server use “B-tree” fundamental. Now whenever there is new data inserted or deleted the tree tries to become unbalance. In order that we can understand the fundamental properly let’s try to refer the figure down.

If you see the first level index there is “2” and “8”, now let say we want to insert “6”. In order to balance the “B-TREE” structure rows it will try to split in two pages, as shown. Even though the second page split has some empty area it will go ahead because the primary thing for him is balancing the “B-TREE” for fast retrieval.

Now if you see during the split it is doing some heavy-duty here:-

• Creates a new page to balance the tree.
• Shuffle and move the data to pages.

So if your table is having heavy inserts that means it’s transactional, then you can visualize the number of splits it will be doing. This will not only increase insert time but will also upset the end-user who is sitting on the screen.

So when you forecast that a table has a lot of inserts it’s not a good idea to create indexes.

Question 4.
What are “Table Scan’s” and “Index Scan’s”?
Answer:
These are ways by which SQL Server searches a record or data in a table. In “Table Scan” SQL Server loops through all the records to get to the destination. For instance, if you have 1, 2, 5, 23, 63, and 95. If you want to search for 23 it will go through 1, 2, and 5 to reach it. Worst if it wants to search 95 it will loop through all the records.
While for “Index Scan’s” it uses the. “B-TREE” fundamental to get to a record. For “B-TREE” refer to previous questions.

Note   Which way to search is chosen by SQL Server engine. Example if itfinds that the table records are 
           very less it will go for table scan. If itfinds the table is huge it will gofor index scan.

Question 5.
What are the two types of indexes and explain them in detail?
Answer:
Twist: – What’s the difference between clustered and non-clustered indexes?
There are basically two types of indexes:-

• Clustered Indexes.
• Non-Clustered Indexes.

Ok, every7 thing is the same for both the indexes i.e. it uses “B-TREE” for searching data. But the main difference is the way it stores physical data. If you remember the previous figure (give figure number here) there were leaf level and non-leaf level. Leaf level holds the key which is used to identify the record. And non-leaf level actually points to the leaf level.
In a clustered index, the non-leaf level actually points to the actual data.

In a Non-Clustered index, the leaf nodes point to pointers (they are rowid’s) which then point to actual data.

So here’s what the main difference is in clustered and non-clustered , in clustered when we reach the leaf nodes we are on the actual data. In non-clustered indexes we get a pointer, which then points to the actual data.
So after the above fundamentals following are the basic differences between them:-

• Also note in clustered index actual data as to be sorted in same way as the clustered indexes are. While in non-clustered indexes as we have pointers which is logical arrangement we do need this compulsion.
• Sc we can have only’one clustered index on a table as we can nave only one physical order while we can have more than one non-clustered indexes.

Question 6.
If we make non-clustered index on a table which has clustered indexes, how does the architecture change?
Answer:
The only change is that the leaf node point to clustered index key. Using this clustered index key can then be used to finally locate the actual data. So the difference is that leaf node has pointers while in the next half it has clustered keys. So if we create non-clustered index on a table which has clustered index it tries to use the clustered index.

Question 7.
(DB)What is “FillFactor” concept in indexes?
Answer:
When SQL Server creates new indexes, the pages are by default full. “FillFactor” is a percentage value (from 1 – 100) which says how much full your pages will be. By default “FillFactor” value is zero.

Question 8.
(DB) What is the best value for “FillFactor”?
Answer:
“FillFactor” depends on how transactional your database is. Example if your database is highly transactional (i.e. heavy insert’s are happening on the table), then keep the fill factor less around 70. If it’s only a read-only database probably used only for reports you specify 100%.
Remember there is a page split when the page is full. So fill factor will play an important role.

Question 9.
What arc “Index statistics”?
Answer:
Statistics are something the query optimizer will use to decide what type of index (table scan or index scan) to be used to search data. Statistics change according to inserts and updates on the table, nature of data on the table etc…In short “Index statistics” are not same in all situations. So DBA has to run statistics again and again after certain interval to ensure that the statistics are up-to-date with the current data.

Note  If you want to create index you can use either the "Create Index" statement or you can use the GUI.

Question 10.
(DB)How can we see the statistics of an index?
Answer:
Twist: – How can we measure the health of the index?
In order to see statistics of any index following the T-SQL command, you will need to run.

Note  Before reading this you should have all the answers of the pervious section clear. 
          Especially about extent, pages and indexes.

DECLARE
@ID int,
@IndexID int,
@IndexName varchar(128)
-- input your table and index name
SELECT @IndexName = ’AK_Department_Name'
SET @ID = OBJECT_ID('HumanResources.Department')
SELECT @IndexID = IndID
FROM sysindexes
WHERE id = @ID AND name = @IndexName
--run the DBCC command
DBCC SHOWCONTIG (@id, @lndexID)

Just a short note here “DBCC” i.e. “Database consistency checker” is used for checking the health of a lot of entities in SQL Server. Now here we will be using it to see index health. After the command is run you will see the following output. You can also run “DBCC SHOWSTATISTICS” to see when was the last time the indexes rebuild.

Pages Scanned

The number of pages in the table (for a clustered index) or index.

Extents Scanned

The number of extents in the table or index. If you remember we had said in the first instance that extent has pages. The more extents for the same number of pages the higher will be the fragmentation.

Extent Switches

The number of times SQL Server moves from one extent to another. More the switches it has to make for the same amount of pages, the more fragmented it is.

Avg. Pages per Extent

The average number of pages per extent. There are eight pages/extent so if you have an extent full with the eight you are in a better position.

Scan Density [Best Count: Actual Count]

This is the percentage ratio of Best count / Actual count. The best count is a number of extent changes when everything is perfect. It’s like a baseline. The actual count is the actual number of extent changes on that type of scenario.

Logical Scan Fragmentation

Percentage of out-of-order pages returned from scanning the leaf pages of an index. An out of order page is one for which the next page indicated is a different page than the page pointed to by the next page pointer in the leaf page.

Extent Scan Fragmentation

This one is telling us whether an extent is not physically located next to the extent that it is logically located next to. This just means that the leaf pages of your index are not physically in order (though they still can be logical), and just what percentage of the extent this problem pertains to.

Avg. Bytes free per page

This figure tells how many bytes are free per page. If it’s a table with heavy inserts or highly transactional then more free space per page is desirable, so that it will have fewer page splits.
If it’s just a reporting system then having this closer to zero is good as SQL Server can then read data with less number of pages.

Avg. Page density (full)

Average page density (as a percentage). It’s is nothing but:-
1 – (Avg. Bytes free per page / 8096)
8096 = one page is equal to 8096 bytes

Note  Read every of the above sections carefully, mease you are looking for DBA job you will
          need the above'fundamentals to be very clear. Normally interviezver will try to shoot 
          questions like "Ifyou see the fillfactor is this much, what will you conclude?, If you see
          the scan density this much what zvill you conclude?

Question 11.
(DB) How do you reorganize your index, once you find the problem?
Answer:
You can reorganize your index using “DBCC DBREINDEX”. You can either request a particular index to be re-organized or just re-index all indexes of the table.

This will re-index your all indexes belonging to the "HumanResources.Department".
DBCC DBREINDEX ([HumanResources.Department])
This will re-index only "AK__Depart.mentr.Name".
DBCC DBREINDEX ([HumanResources.Department] , [AK_Department_Name])
This will re-index with a "fill factor".
DBCC DBREINDEX ([HumanResources.Department],[AK_Department_Name], 70)

You can then again run DBCG SHOWCONTIG to see the results.

Question 12.
What is Fragmentation?
Answer:
Speed issues occur because of two major things

• Fragmentation.
• Splits.

Splits have been covered in the first questions. But one other big issue is fragmentation. When the database grows it will lead to splits, but what happens when you delete something from the database…hehehe life has a lot of turns right. Ok, let’s say you have two extents and each has two pages with some data. Below is a graphical representation. Well actually that’s now how things are inside but for sake of clarity lot of things have been removed.

Now over a period of time some Extent and Pages data undergo some delete. Here’s the modified database scenario. Now one observation you can see is that some pages are not removed even when they do not have data. Second If SQL. the server wants to fetch all “Females” it has to span across to two extents and multiple pages within them. This is called “Fragmentation” i.e. to fetch data you span across a lot of pages and extents. This is also termed “Scattered Data”.

What if the fragmentation is removed, you only have to search in two extant and two pages. Definitely, this will be faster as we are spanning across fewer entities.

Question 13.
(DB)How can we measure Fragmentation?
Answer:
Using “DBCC SHOWCONTIG

Question 14.
(DB)How can we remove the Fragmented spaces?
Answer:

• Update Statistics The most used way by DBA’s
• Sp_updatestats. It’s the same as update statistics, but update statistics applies only for specified objects and indexes, while “sp_updatestats” loops through all tables and applies statistics updates to each and every table. Below is a sample that is run on the “AdventureWorks” database.

Note  "AdventureWorks" is a sample database that is shipped with SQL Server2005.

• DBCC INDEXFRAG: – This is not the effective way of doing fragmentation it only does fragment on the leaf nodes.

Question 15.
What are the criteria you will look into while selecting an index?
Answer:

Note  Some answers what I have got for this question.

1. I will create an index wherever possible.

2. I will create clustered index on every table.
That’s why DBA’s are always needed.

3. How often the field is used for selection criteria. For example, in a “Customer” table
you have “CustomerCode” and “PinCode”. Most of the searches are going to be performed on “CustomerCode” so it’s a good candidate for indexing rather than using “PinCode”. In short, you can look into the “WHERE” clauses of SQL to figure out if it’s the right choice for indexing.

4. If the column has a higher level of unique values and is used in selection criteria again is a valid member for creating indexes.

5. If the “Foreign” key of the table is used extensively in Joins (Inner, Outer, and Cross) again a good member for creating indexes.

6. If you find the table to be highly transactional (huge insert, update, and deletes) probably not a good entity for creating indexes. Remember the split problems with Indexes.

7. You can use the “Index tuning wizard” for index suggestions.

Question 16.
(DB)What is “Index Tuning Wizard”?
Answer:
Twist: – What is “Work Load File”?
In the previous question, the last point was using the “Index Tuning wizard”. You can get the “Index Tuning Wizard” from “Microsoft SQL Server Management Studio” – “Tools” – “SQL Profiler”.

Note This book refers to SQL Server 2005, so probably if you have SQL Server 2000 installed you will 
        get the SQL Profiler in Start - Programs - Microsoft SQL Server -- Profiler. But in this whole book, 
       we will refer only to SQL Server 2005. We will go step by step for this answer explaining how exactly
       "Index Tuning Wizard" can be used.

Ok before we define any indexes let’s try to understand what is “Work Load File”. “Work Load File” is the complete activity that has happened on the server for a specified period of time. All the activity is entered into a “.trc” file which is called “Trace File”. Later “Index Tuning Wizard” runs on the “Trace File” and on every query fired it tries to find which columns are valid candidates for indexes depending on the Indexes.

Following are the step’s to use “Index Tuning Wizard”:-

• Create the Trace File using “SQL Profiler”.
• Then use “Database Tuning Advisor” and the “Trace File” for what columns to be indexed.

Create Trace File

Once you have opened the “SQL Profiler” click on “New Trace”.

It will alert for giving you to all trace file details for instance the “Trace Name”, “File where to save”. After providing the details click on the “Run” button provided below. I have provided the file name of the trace file as the “Testing.trc” file.

HUH and the action starts. You will notice that profiler has started tracing queries that are hitting “SQL Server” and logging all those activities in to the “Testing.trc” file. You also see the actual SQL and the time when the SQL was fired.

Let the trace run for some bit of time. In an actually practical environment, I run the trace for almost two hours at peak to capture the actual load on the server. You can stop the trace by clicking on the red icon given above.

You can go to the folder and see your “.trc” file created. If you try to open it in notepad you will see binary data. It can only be opened using the profiler. So now that we have the load file we have to just say to the advisor, hey advisor here’s my problem (trace file) can you suggest to me some good indexes improve my database performance.

Using Database Tuning Advisor

In order to go to “Database Tuning Advisor,” you can go from “Tools” – “Database Tuning Advisor”.

In order to supply the workload file, you have to start a new session in “Database tuning advisor”.

After you have said “New Session” you have to supply all details for the session. There are two primary requirements you need to provide to the Session:-

• Session Name
• “Work Load File” or “Table” (Note you can create either a trace file or you can put it in SQL Server table while running the profiler).

I have provided my “Testing.trc” file which was created when I ran the SQL profiler. You can also filter for which database you need index suggestions. At this moment I have checked all the databases. After all the details are filled in you have to click on the “Green” icon with the arrow. You can see the tooltip as “Start analysis” in the image below.

While analyzing the trace file it performs basic four major steps:-

• Submits the configuration information.
• Consumes the Workload data (that can be in the format of a file or a database table).
• Start performing analysis on all the SQL executed in the trace file.
• Generates reports based on analysis.
• Finally give the index recommendations.

You can see all the above steps have run successfully which is indicated by “0 Error and 0 Warning”.

Now it’s time to see what index recommendations SQL Server has provided us. Also, note it has included two new tabs after the analysis was done “Recommendations” and “Reports”.

You can see on “AdventureWorks” SQL Server has given me huge recommendations. Example on “HumanResources.Department” he has told me to create an index on “PK_Department_DepartmentId”.

In case you want to see detailed reports you can click on the “Reports” tab and there is a wide range of reports which you can use to analyze how your database is performing on that “Work Load” file.

Note  The whole point of putting this alt step by step was that you have a complete understanding 
          of how to do automatic index decisions " using SQL Server. During the interview one of the questions's 
          that is very sure "How do you increase speed performance of SQL Server? "and talking about the "index
          Tuning Wizard" can fetch you some decent points.

Question 17.
(DB)What is an Execution Plan?
Answer:
The execution plan gives how the query optimizer will execute a give SQL query. Basically, it shows the complete plan of how SQL will be executed. The whole query plan is prepared to depend on a lot of data for instance:-

• What type of indexes do the tables in the SQL have?
• Amount of data.
• Type of joins in SQL (Inner join, Left join, Cross join, Right join, etc)

Click on the ICON in SQL Server management studio as shown in the figure below.

In the bottom window pane, you will see the complete breakup of how your SQL Query will execute. Following is the way to read it:-

• Data flows from left to right.
• Any execution plan sums to a total of 100 %. For instance in the below figure it is 18 + 28 + 1 + 1 + 52. So the highest is taken by Index scan 52 percent. Probably we can look into that logic and optimize this query.
• Right, most nodes are actually data retrieval nodes. I have shown them with arrows the two nodes.
• In the below figure you can see some arrows are thick and some are thin. The more thickness more the data is transferred.
• There are three types of join logic nested join, hash join and merge join.

If you move your mouse gently over any execution strategy you will see a detail breakup of how that node is distributed.

Question 18.
How do you see the SQL plan in textual format?
Answer:
Execute the following “set showplan_text on” and after that execute your SQL, you will see a textual plan of the query. In the first question what I discussed was a graphical view of the query plan. Below is a view of how a textual query plan looks like. In older versions of SQL Server where there was no way of seeing the query plan graphically “SHOWPLAN” was the most used. Today if any one is using it then I think he is doing a show business or a newcomer learner.

Question 19.
(DB)What is nested join, hash join, and merge join in SQL Query plan?
Answer:
A join is whenever two inputs are compared to determine an output. There are three basic types of strategies for this and they are: nested loops join, merge join and hash join. When a join happens the optimizer determines which of these three algorithms is best to use for the given problem, however, any of the three could be used for any join. All of the costs related to the join are analyzed the most cost-efficient algorithm is picked for use. These are in-memory loops used by SQL Server.

Nested Join

If you have less data this is the best logic. It has two loops one is outer and the other is the inner loop. For every outer loop, its loops through all records in the inner loop. You can see the two-loop inputs given to the logic. The top index scan is the outer loop and the bottom index seek is the inner loop for every outer record.

It’s like executing the below logic:-

For each outer records
        For each inner records 
        Next
Next

So you visualize that if there fewer inner records this is a good solution.

Hash Join

Hash join has two inputs “Probe” and “Build” input. First, the “Build” input is processed, and then the “Probe” input. Every input that is smaller is the “Build” input. SQL Server first builds a hash table using the build table input. After that, he loops through the probe input and finds the matches using the hash table created previously using the build table and does the processing and gives the output.

Merge Join

In merge joins both the inputs are sorted on the merge columns. Merge columns are determined depending on the inner join defined in SQL. Since each input join is sorted merge join takes input and compares for equality. If there is equality then a matching row is produced. This is processed till the end of rows.

Question 20.
What joins are good in what situations?
Answer:
Nested joins are best suited if the table is small and it’s a must the inner table should have an index.

Merge joins best of large tables and both tables participating in the joins should have indexes.
Hash joins best for small outer tables and large inner tables. Not necessary that tables should have indexes, but would be better if the outer table has indexed.

Note  Previously we have discussed table scan and index scan do revise it which is also important
          from the aspect of reading query plan.

Question 21.
(DB)What is RAID and how does it work ?
Answer:
Redundant Array of Independent Disks (RAID) is a term, used to describe the technique of j improving data availability through the use of arrays of disks and various data-striping , methodologies. Disk arrays are groups of disk drives that work together to achieve higher ? data-transfer and I/O rates than those provided by single large drives. An array is a set of multiple disk drives plus a specialized controller (an array controller) that keeps track of _ how data is distributed across the drives. Data for a particular file is written in segments to the different drives in the array rather than being written to a single drive.

For speed and reliability, it’s better to have more disks. When these disks are arranged in j certain patterns and use a specific controller, they are called a Redundant Array of Inexpensive Disks (RAID) set. There are several numbers associated with RAID, but the most common are 1, 5 and 10.
% RAID 1 works by duplicating the same writes on two hard drives. Let’s assume you have ^ two 20 Gigabyte drives. In RAID 1, data is written at the same time to both drives. RAID1 is optimized for fast writes. ”

RAID 5 works by writing parts of data across all drives in the set (it requires at least three drives). If a drive failed, the entire set would be worthless. To combat this problem, one of the drives stores a “parity” bit. Think of a math problem, such as 3 + 7 = 10. You can think , of the drives as storing one of the numbers, and the 10 is the parity part. By removing any – one of the numbers, you can get it back by referring to the other two, like this: 3 + X = 10. Of course, losing more than one could be evil. RAID 5 is optimized for reads.

RAID 10 is a bit of a combination of both types. It doesn’t store a parity bit, so it’s fast, but it duplicates the data on two drives to be safe. You need at least four drives for RAID 10. This type of RAID is probably the best compromise for a database server.

Note It’s difficult to cover complete aspect of RAID in this book.lt’s better to take some decent SQL SER VER book for in detail knowledge, but yes from interview aspect you can probably escape with this answer.

We have compiled most frequently asked SQL Server Interview Questions which will help you with different expertise levels.

SQL Server Interview Questions on Reporting Services

Note  I know everyone screaming this is a part of Data mining and warehousing. I echo the same voice 
          with you my readers, but not necessarily. When you want to derive reports on OLTP systems this is the 
          best day to get your work done. Secondly reporting services is used so much heavily in projects now a 
          day that it will be completely unfair to discuss this topic in a short way as a subsection of some chapter.

Question 1.
Can you explain how can we make a simple report in reporting services?
Answer:
We will be using the “AdventureWorks” database for this sample. We would like to derive a report on how much quantity sales were done per product. For this sample, we will have to refer to three tables Salesorderdetails, Salesorderheader, and product table. Below is the SQL which also shows what the relationship between those tables is:-

select production .product.Name as ProductName, count (*) as TotalSales from sales. sales order detail
inner join Sales.Salesorderheader 
on Sales.Salesorderheader.salesorderid=
Sales.Salesorderdetail.Salesorderid 
inner join production. product
on production. product,productid=sales.sales order detail .productid
group by production. product.Name

So we will be using the above SQL and trying to derive the report using reporting services. First, click on the business intelligence studio menu in SQL Server 2005 and say File –> New — > Project. Select the “Report” project wizard. Let’s give this project name “TotalSalesBy Product”. You will be popped with a startup wizard as shown below.

Click next and you will be prompted to input data source details like type of server, connection string, and name of the data source. If you have the connection string just paste it on the text area or else click edit to specify connection string values through GUI.

As we are going to use SQL Server for this sample specify OLEDB provider for SQL Server and click next.

After selecting the provider specify the connection details which will build your connection string. You will need to specify the following details Server Name, Database Name, and security details.

This is the most important step of reporting services, specifying SQL. You remember the top SQL we had specified the same we are pasting it here. If you are not sure about the query you can use the query builder to build your query.

Now it’s time to include the fields in reports. At this moment we have only two fields name of the product and total sales.

Finally, you can preview your report. In the final section, there are three tabs data, layout, and preview. In the data tab, you see your SQL or the data source. In the layout tab, you can design your report most look and feel aspect is done in this section. Finally below is the preview where you can see your results.

Question 2.
How do I specify stored procedures in Reporting Services?
Answer:
There are two steps to specify stored procedures in reports of reporting services:-
Specify it in the query string. For instance, I have a stored procedure “GettotalSalesofproductsbySales” which has “@ProductSold” as the input parameter.

You have to also specify the command type from the data tab.

Question 3.
What is the architecture for “Reporting Services “?
Answer:
“Reporting Services” is not a stand-alone system but rather a group of server sub-system which work together for the creation, management, and deployment of reports across the enterprise.

Report designer

This is an interactive GUI that will help you to design and test your reports.

Reporting Service Database

After the report is designed they are stored in XML format. These formats are in RDL (Report Design Layout) formats. These entire RDL formats are stored in Report Service Database.

Report Server

Report Server is nothing but an ASP.NET application running on IIS Server. Report Server renders and stores these RDL formats.

Report Manager

It’s again an ASP.NET web-based application that can be used by administrators to control security and managing reports. From an administrative perspective who has the authority to create the report, run the report etc…
You can also see the various formats which can be generated XML, HTML, etc using the report server.

We have compiled most frequently asked SQL Server Interview Questions which will help you with different expertise levels.

SQL Server Interview Questions on Integration Seruices?DTS

Note  We had seen some questions on DTS in the previous chapter "Data Warehousing". 
          But in order to just make complete justice with this topic, I have included them in integration services.

Question 1.
What is Integration Services import/export wizard?
Answer:

Note  What is DTS Import /Export Wizard?

Note  Try to do this practice as it can be useful if the interviewer wants to visualize the whole stuff.

DTS import/export wizard lets us import and export from external data sources. There are seven steps that you can just go through of how to use the wizard.
You can find DTS import and export wizard as shown below.

You will be popped with a screen as below click “Next’

The next step is to specify from which source you want to copy data. You have to specify the Data source name and server name. For the understanding purpose, we are going to move data between “AdventureWorks” databases. I have created a dummy table called “SalesPersonDummy” which has the same structure as that of the “Salesperson” table. But the only difference is that “SalesPersonDummy” does not have data.

The next step is to specify the destination where the source will be moved. At this moment we are moving data inside “AdventureWorks” itself so specify the same database as the source.

The next step is to specify the option from where you want to copy data. For the time being, we going to copy from the table so selected the first option.

Finally, choose which object you want to map where. You can map multiple objects if you want.

When everything goes successful you can see the below screen, which shows the series of steps DTS has gone through.

Question 2.
What arc prime components in Integration Services?
Answer:
There are two important components in Integration services:-

• DTP ( Data transformation pipeline)

DTP is a bridge that connects the source (CSV, any other Database, etc) and the destination (SQL Server Database). While DTP moves data between source and destination, transformation takes place between input and output columns. Probably some columns will go as one-to-one mapping and some with some manipulations.

• DTR ( Data transformation runtime)

While DTP acts as a bridge DTR controls your integration service. They are more about how will be the workflow and different components during transformation. Below are different components associated with DTR:-

• Task: – It’s the smallest unit that you want to execute.
• Container: – Container logically groups task. For instance, you have a task to load
• CSV file into the database. So you will have two or three tasks probably:-
• Parse the CSV file.
• Check for field data type
• Map the source field to the destination.

So you can define all the above work as tasks and group them logically into a container called a Container.

• Package: – Package is executed to actually do the data transfer.
  DTP and DTR model expose API which can be used in .NET language for better control.

Note  I can hear the shout practical.. practical I think I have con fused you guys over there. 
          So let's warm-up on some practical DTS stuff. 1000 words is equal to one compiled program - 
          Shivprasad Koirala? I really want to invent some proverbs if you do not mind it.

Question 3.
How can we develop a DTS project in Integration Services?
Answer:
Twist: – Can you say how have you implemented DTS in your project and for what?
Note: – We had visited DTS import/export wizard in the previous section of this chapter. But for a real data transformation or a data warehousing (ETL process), it’s not enough. You will need to customize the project, there’s where we can use this beautiful thing called a “BI development project”. If possible just try to go step by step in creating this sample project.

You can get the development studio as shown below.

Click File—New – Project and select “Data Transformation Project”.

Give a name to the project as a “Salesperson” project. Before moving ahead let me give a brief about what we are trying to do. We are going to use “Sales.SalesPerson” table from the “AdventureWorks” database. “Sales. Salesperson” table has a field called “Bonus”. We have the following task to be accomplished:-

Note  These both tables have to be created manually by you. I will suggest to use the create statements
          and just make both tables. You can see in the image below there are tzvo tables "SalesPerson5000"
          and "SalesPersonNot5000".

• Whenever the “Bonus” field is equal to 5000 it should go in”Sales.Salesperson5000″.
• Whenever the “Bonus” field is not equal to 5000 it should go in “Sales. Salesperson Not5000”.

Once you selected the “Data transformation project”, you will be popped with a designer explorer as shown below. I understand you must be saying it’s cryptic…it is. But let’s try to simplify it. On the right hand, you can see the designer pane which has a lot of objects on it. On the right-hand side, you can see four tabs (Control flow, Data Flow, Event handlers, and Package Explorer).

Control flow: – It defines how the whole process will flow. For example, if you loading a CSV file. Probably you will have tasks like parsing, cleaning, and then loading. You can see a lot of control flow items that can make your data mining task easy. But first, we have to define a task in which we will define all our data flows. So you can see the curve arrow which defines what you have to drag and drop on the control flow designer You can see the arrow tip which defines the output point from the task.

In this project, I have only defined one task, but in the real-time project, something below like this can be seen (Extraction, Transformation, and Loading: – ETL). One task points as an input to another task and the final task inputs data in SQL Server.

Data Flow: – Data flow says how the objects will flow inside a task. So Data flow is a subset of a task defining the actual operations.

Event Handlers: – The best part of DTS is that we can handle events. For instance, if there is an error what action do you want it to do. Probably log your errors in the error log table, flat file, or be more interactive send a mail.

Package Explorer: – It shows all objects in a DTS in a hierarchical way.

Now that you have defined your task it’s time to define the actual operation that will happen within the task. We have to move data from “Sales.SalesPerson” to “Sales.SalesPerson5000” (if their “Bonus” fields are equal to 5000) and “Sales.SalesPersonNot5000” (if their “Bonus” fields are not equal to 5000). In short, we have “Sales. The salesPerson” as the source and the other two tables as Destination. So click on the “Data Flow” tab and drag the OLEDB Source data flow item on the designer, we will define the source in this item. You can see that there is some error which is shown by a cross on the icon. This signifies that you need to specify the source table that is “Sales. Salesperson”.

In order to specify source tables, we need to specify connections for the OLEDB source. So right-click on the below tab “Connections” and select “New OLEDB Connection”. You will be popped up with a screen as shown below. Fill in all details and specify the database as “AdventureWorks” and click “OK”.

If the connection credentials are proper you can see the connection in the “Connections” tab as shown in the below figure.

Now that we have defined the connection we have to associate that connection with the OLE DB source. So right dick and select the “Edit” menu.

Once you click edit you will see a dialog box as shown below. In data access mode select “Table or View” and select the “Sales. Salesperson” table. To specify the mapping click on the “Columns” tab and then press ok.

If the credentials are ok you can see the red Cross is gone and the OLE DB source is not ready to connect further. As said before we need to move data to appropriate tables on the condition that “Bonus” field value. So from the data flow item drag and drop the “Conditional Split” data flow item.

Right-click on the “Conditional Split” data flow item so that you can specify the criteria. It also gives you a list of fields in the table which you can drag-drop. You can also drag-drop the operators and specify the criteria. I have made two outputs from the conditional split one which is equal to 5000 and the second not equal to 5000.

Conditional split now has two outputs one which will go in “Sales.SalesPerson5000” and the other in “Sales.SalesPersonNot5000”. So you have to define two destinations and the associate respective tables to them. So drag two OLE DB destination data flow items and connect them to the two outputs of the conditional split.

When you drag from the conditional split items over OLEDB destination items it will pop up a dialog to specify which output this destination has to be connected to. Select the one from the drop-down and press ok. Repeat this step again for the other destination object.

That’s the final Data flow structure expected.

It’s time to build and run the solution which you can do from the drop-down. To run the DTS you press the green icon as pointed by the arrow in the below figure. After you run the query both the tables have the appropriate values or not.

Note You can see various dataflow items on the right-hand side; it’s out of the scope to cover all items ( You must be wondering hoiv much time this author will say out of scope, but it’s a fact guys something you have to explore). In this sample project, we needed the conditional split so we used it. Depending on projects you still need to explore the toolbox. It’s rare that an interviewer will ask about individual items but rather ask fundamentals or a general overview of how you did DTS.