Database Interview Question

Is finalize( ) similar to a destructor?

2010-07-09T03:37:00.000-07:00

I know that C++ defines things called destructors, which are automatically executed when an object is destroyed. Is finalize( ) similar to a destructor?

Java does not have destructors. Although it is true that the finalize( ) method approximates the function of a destructor, it is not the same. For example, a C++ destructor is always called just before an object goes out of scope, but you can’t know when finalize( ) will be called for any specific object. Frankly, because of Java’s use of garbage colle ction,there is little need for a destructor.

Java :Question with Answer

2010-07-09T03:36:00.000-07:00

You say that once created, String objects are immutable. I understand that, from a
practical point of view, this is not a serious restriction, but what if I want to create a string that can be changed?

You’re in luck. Java offers a class called StringBuffer, which creates string objects
that can be changed. For example, in addition to the charAt( ) method, which obtains
the character at a specific location, StringBuffer defines setCharAt( ), which sets a
character within the string. However, for most purposes you will want to use String,
not StringBuffer.

Why does String define the equals( ) method? Can’t I just use ==?

2010-07-09T03:35:00.000-07:00

The equals( ) method compares the character sequences of two String objects for
equality. Applying the == to two String references simply determines whether the two
references refer to the same object.

When should I make an instance variable private?

2010-07-09T03:34:00.000-07:00

There are no hard and fast rules, but here are two general principles. If an instance
variable is to be used only by methods defined within its class, then it should be made private. If an instance variable must be within certain bounds, then it should be private and made available only through accessor methods. This way, you can prevent invalid values from being assigned.

The javadoc Tags

2010-07-09T03:31:00.000-07:00

Tag Meaning
@author Identifies the author of a class.
@deprecated Specifies that a class or member is deprecated.
{@docRoot} Specifies the path to the root directory of the current documentation. (Added by Java
2, version 1.3.)
@exception Identifies an exception thrown by a method.
{@inheritDoc} Inherits a comment from the immediate superclass. (Added by Java 2, version 1.4, butnot currently implemented.)
{@link} Inserts an in-line link to another topic.
{@linkplain} Inserts an in-line link to another topic, but the link is displayed in a plain-text font.(Added by Java 2, version 1.4.)
@param Documents a method’s parameter.
@return Documents a method’s return value.
@see Specifies a link to another topic.
@serial Documents a default serializable field.
@serialData Documents the data written by the writeObject( ) or writeExternal( ) methods.
@serialField Documents an ObjectStreamField component.
@since States the release when a specific change was introduced.
@throws Same as @exception.
{@value} Displays the value of a constant, which must be a static field. (Added by Java 2,version 1.4.)
@version Specifies the version of a class.

Java : Fundamental Question and Answer

2010-07-09T03:29:00.000-07:00

To address the issues of portability and security, why was it necessary to create a
new computer language such as Java; couldn’t a language like C++ be adapted? In
other words, couldn’t a C++ compiler that outputs bytecode be created?

While it would be possible for a C++ compiler to generate bytecode rather than
executable code, C++ has features that discourage its use for the creation of
applets—the most important feature being C++’s support for pointers. A pointer is the
address of some object stored in memory. Using a pointer, it would be possible to access resources outside the program itself, resulting in a security breach. Java does not support pointers, thus eliminating this problem

Java : Fundamental Question and Answer

2010-07-09T03:28:00.000-07:00

Does the use of a code block introduce any run-time inefficiencies? In other words,
does Java actually execute the { and }?

No. Code blocks do not add any overhead whatsoever. In fact, because of their ability
to simplify the coding of certain algorithms, their use generally increases speed and
efficiency. Also, the { and } exist only in your program’s source code. Java does not,per se, execute the { or }.

Java : Fundamental Question and Answer

2010-07-09T03:25:00.000-07:00

You state that object-oriented programming is an effective way to manage large programs. However, it seems that it might add substantial overhead to relatively
small ones. Since you say that all Java programs are, to some extent, object-oriented,does this impose a penalty for smaller programs?

No. As you will see, for small programs, Java’s object-oriented features are nearly
transparent. Although it is true that Java follows a strict object model, you have
wide latitude as to the degree to which you employ it. For smaller programs, their
“object-orientedness” is barely perceptible. As your programs grow, you will integrate
more object-oriented features effortlessly

Semantic Data Model

2010-03-27T10:25:00.000-07:00

A semantic data model in software engineering is a technique to define the meaning of data within the context of its interrelationships with other data. A semantic data model is an abstraction which defines how the stored symbols relate to the real world. A semantic data model is sometimes called a conceptual data model.

The logical data structure of a database management system (DBMS), whether hierarchical, network, or relational, cannot totally satisfy the requirements for a conceptual definition of data because it is limited in scope and biased toward the implementation strategy employed by the DBMS. Therefore, the need to define data from a conceptual view has led to the development of semantic data modeling techniques. That is, techniques to define the meaning of data within the context of its interrelationships with other data. As illustrated in the figure. The real world, in terms of resources, ideas, events, etc., are symbolically defined within physical data stores. A semantic data model is an abstraction which defines how the stored symbols relate to the real world. Thus, the model must be a true representation of the real world

Generic data model

2010-03-27T10:24:00.002-07:00

Generic data models are generalizations of conventional data models. They define standardised general relation types, together with the kinds of things that may be related by such a relation type. Generic data models are developed as an approach to solve some shortcomings of conventional data models. For example, different modelers usually produce different conventional data models of the same domain. This can lead to difficulty in bringing the models of different people together and is an obstacle for data exchange and data integration. Invariably, however, this difference is attributable to different levels of abstraction in the models and differences in the kinds of facts that can be instantiated (the semantic expression capabilities of the models). The modelers need to communicate and agree on certain elements which are to be rendered more concretely, in order to make the differences less significant.

Geographic data model

2010-03-27T10:24:00.001-07:00

A data model in Geographic information systems is a mathematical construct for representing geographic objects or surfaces as data. For example, the vector data model represents geography as collections of points, lines, and polygons; the raster data model represent geography as cell matrixes that store numeric values; and the Triangulated irregular network (TIN) data model represents geography as sets of contiguous, nonoverlapping triangles

Entity-relationship model

2010-03-27T10:23:00.000-07:00

An entity-relationship model is an abstract conceptual data model (or semantic data model) used in software engineering to represent structured data. Entity relationship models (ERMs) produce a conceptual data model of a system, and its requirements in a top-down fashion. There are several notations for data modeling. The actual model is frequently called "Entity relationship model", because it depicts data in terms of the entities and relationships described in the data.

Data Structure Diagram

2010-03-27T10:22:00.000-07:00

Example of a Data Structure Diagram.

A data structure diagram (DSD) is a diagram and data model used to describe conceptual data models by providing graphical notations which document entities and their relationships, and the constraints that binds them. The basic graphic elements of DSDs are boxes, representing entities, and arrows, representing relationships. Data structure diagrams are most useful for documenting complex data entities.

Data structure diagrams are an extension of the entity-relationship model (ER model). In DSDs, attributes are specified inside the entity boxes rather than outside of them, while relationships are drawn as boxes composed of attributes which specify the constraints that bind entities together. The E-R model, while robust, doesn't provide a way to specify the constraints between relationships, and becomes visually cumbersome when representing entities with several attributes. DSDs differ from the ER model in that the ER model focuses on the relationships between different entities, whereas DSDs focus on the relationships of the elements within an entity and enable users to fully see the links and relationships between each entity.

Database model :Types of data models

2010-03-27T10:21:00.000-07:00

A database model is a theory or specification describing how a database is structured and used. Several such models have been suggested.

* Flat model: This may not strictly qualify as a data model. The flat (or table) model consists of a single, two-dimensional array of data elements, where all members of a given column are assumed to be similar values, and all members of a row are assumed to be related to one another.
* Hierarchical model: In this model data is organized into a tree-like structure, implying a single upward link in each record to describe the nesting, and a sort field to keep the records in a particular order in each same-level list.
* Network model: This model organizes data using two fundamental constructs, called records and sets. Records contain fields, and sets define one-to-many relationships between records: one owner, many members.
* Relational model: is a database model based on first-order predicate logic. Its core idea is to describe a database as a collection of predicates over a finite set of predicate variables, describing constraints on the possible values and combinations of values.

Data Model

2010-03-27T10:19:00.002-07:00

A data model in software engineering is an abstract model that describes how data are represented and accessed. Data models formally define data elements and relationships among data elements for a domain of interest. According to Hoberman (2009), "A data model is a wayfinding tool for both business and IT professionals, which uses a set of symbols and text to precisely explain a subset of real information to improve communication within the organization and thereby lead to a more flexible and stable application environment.

Database Design :Design process

2010-03-27T10:19:00.001-07:00

The process of doing database design generally consists of a number of steps which will be carried out by the database designer. Not all of these steps will be necessary in all cases. Usually, the designer must:

* Determine the relationships between the different data elements
* Superimpose a logical structure upon the data on the basis of these relationships.

Within the relational model the final step can generally be broken down into two further steps, that of determining the grouping of information within the system, generally determining what are the basic objects about which information is being stored, and then determining the relationships between these groups of information, or objects. This step is not necessary with an Object database.

The tree structure of data may enforce a hierarchical model organization, with a parent-child relationship table. An Object database will simply use a one-to-many relationship between instances of an object class. It also introduces the concept of a hierarchical relationship between object classes, termed inheritance

Database Design :Physical database design

2010-03-27T10:18:00.001-07:00

The physical design of the database specifies the physical configuration of the database on the storage media. This includes detailed specification of data elements, data types, indexing options and other parameters residing in the DBMS data dictionary. It is the detailed design of a system that includes modules & the database's hardware & software specifications of the system.

DatabaseDesign :Logically structuring data

2010-03-27T10:16:00.004-07:00

Once the relationships and dependencies amongst the various pieces of information have been determined, it is possible to arrange the data into a logical structure which can then be mapped into the storage objects supported by the database management system. In the case of relational databases the storage objects are tables which store data in rows and columns.

Each table may represent an implementation of either a logical object or a relationship joining one or more instances of one or more logical objects. Relationships between tables may then be stored as links connecting child tables with parents. Since complex logical relationships are themselves tables they will probably have links to more than one parent.

In an Object database the storage objects correspond directly to the objects used by the Object-oriented programming language used to write the applications that will manage and access the data. The relationships may be defined as attributes of the object classes involved or as methods that operate on the object classes.

Database :Conceptual schema

2010-03-27T10:16:00.003-07:00

Once a database designer is aware of the data which is to be stored within the database, they must then determine where dependancy is within the data. Sometimes when data is changed you can be changing other data that is not visible. For example, in a list of names and addresses, assuming a situation where multiple people can have the same address, but one person cannot have more than one addresses, the name is dependent upon the address, because if the address is different then the associated name is different too. However, the other way around is different. One attribute can change and not another.

Database :Conceptual schema

2010-03-27T10:16:00.001-07:00

MySQL : Restore Database From the Command Prompt

2009-09-25T00:27:00.000-07:00

mysql - u user_name -p your_password database_name < file_name.sql

MySQL : Backup Database From the Command Prompt

2009-09-25T00:26:00.000-07:00

mysqldump -u user_name -p your_password database_name > File_name.sql

MySQL Function : mysql_affected_rows

2009-09-25T00:21:00.000-07:00

Get number of affected rows in previous MySQL operation

Description
int mysql_affected_rows ([ resource $link_identifier ] )

Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier .

Example #

 = mysql_connect('localhost', 'mysql_user', 'mysql_password');

if (!$link) {

  die('Could not connect: ' . mysql_error());

}

mysql_select_db('mydb');



/* this should return the correct numbers of deleted records */

mysql_query('DELETE FROM mytable WHERE id <>);

printf("Records deleted: %d\n", mysql_affected_rows());



/* with a where clause that is never true, it should return 0 */

mysql_query('DELETE FROM mytable WHERE 0');

printf("Records deleted: %d\n", mysql_affected_rows());

?>

The above example will output something similar to:

Records deleted: 10
Records deleted: 0

MySQL : Have the new Query Analyzer graphs helped you solve development problems internally?

2009-09-25T00:18:00.002-07:00

We use it about every day for our own products, specifically for finding performance issues in development. For example, one of the things we found in this release was that we had more updates than we expected compared to the previous version. We learned it because we saw a peak in “updates” in the row activity graph in the Enterprise Monitor. We highlighted the peak in the updates, and we figured out exactly which part of our code related to these updates through the Query Analyzer, and finally we fixed the problem. This process would have taken much longer if we did it manually because it was actually a certain set of updates that were increasing at a certain point of time, while other normal updates were happening at the same time. If we just looked at the logs, it wouldn't have been as obvious which one was causing that increase.

In addition, our support team also runs the Query Analyzer on internal servers, and they actually found some problem queries. In some occasions they solve performance issues that they didn't know the root cause; in other situations they see the performance drop and have actually been able to tune some queries that they didn't even know were problematic.

MySQL : How do the Query Analyzer graphs benefit customers?

2009-09-25T00:18:00.001-07:00

It saves a lot of time. The first release of the Query Analyzer saved a lot of time by removing the need to send query activity to logs, gather all that information, and post-process the data in order to figure out which queries were running, how often and for how long. The logical next step was correlating this information with the metrics the Enterprise Monitor collects and displays the data with graphs. If you see something happening performance-wise in the Enterprise Monitor, using the correlation graphs you can very quickly see what queries were happening at that time, before that time or even after that time from a query perspective in the Query Analyzer. That was possible to do in our earlier release, except that it was a very manual process.

MySQL : What type of metrics can be correlated with the Query Analyzer?

2009-09-25T00:14:00.000-07:00

Any graph you see, anything we collect – that could be anything from operating system metrics, such as CPU usage, memory usage, I/O counters, to MySQL-specific counters. You can look at the query profiles over time, i.e. how many selects and updates and other classes of queries were running at any point of time, connections created, threads created, InnoDB row accesses, buffer pool, locks taken over time -- all of these are implemented in this correlation graph feature.

SQL Server 2008 Top New Features :Extended Events

2009-09-23T00:31:00.001-07:00

The extended events infrastructure provides an in-depth troubleshooting tool that enables administrators to address difficult-to-solve problems more efficiently. Administrators can investigate excessive CPU usage, deadlocks, and application time outs as well as many other issues. Extended events data can be correlated with Windows events data to obtain a more complete picture that will aid in problem resolution.

Provides detailed information about low level events in SQL Server
Reduces the time required to troubleshoot complex problems
Provides access to event data that has previously been difficult to obtain

SQL Server 2008 Top New Features :Backup Compression

2009-09-23T00:30:00.000-07:00

Backup compression enables the backup of a database to be compressed without having to compress the database itself. All backup types, including log backups, are supported and data is automatically uncompressed upon restore.

Save storage space
Compressed backups can be stored on tape or on disk
Simple configuration using SQL Server Management Studio
Default state of all backups on a server to be compressed can be configured

SQL Server 2008 Top New Features :Partition Aligned Indexed Views

2009-09-23T00:29:00.002-07:00

Indexed Views let SQL Server persist the results of a view, instead of having to dynamically combine the results from the individual queries in the view definition. Indexed Views can now be created to follow the partitioning scheme of the table that they reference. Indexed views that are aligned in this manner do not need to be dropped before a partition is switched out of the partitioned table, as was the case with SQL Server 2005 indexed views.

Indexed views improve performance with very large partitioned tables, such as fact tables in data warehouses.

SQL Server 2008 Top New Features :Upgrade Advisor

2009-09-23T00:29:00.001-07:00

The Upgrade Advisor generates a report that highlights any issues that might hinder an upgrade. This provides administrators detailed information that can be used to prepare for upgrades.

Helps streamline the upgrade process by identifying upgrade issues in advance
Can be used to analyze both local and remote systems
Upgrade Advisor reports provide links to technical information that assist in addressing upgrade problems

SQL Server 2008 Top New Features :Server Group Management

2009-09-23T00:28:00.000-07:00

Server Group management enables T-SQL queries to be issued against multiple servers from a single Central Management Server, which simplifies administration. Stream results of multi-server queries into a single result set or into multiple result sets enables the option of evaluating policies against a server group.

Management is centralized, so servers do not need to be configured individually
Provides a simpler administration model for policy evaluation

SQL Server 2008 Top New Features : Streamlined Installation

2009-09-23T00:27:00.000-07:00

The SQL Server 2008 installation process has been streamlined to be easier and more efficient. Individual SQL Server components, such as Database Services, Analysis Services, and Integration Services, can be optionally selected for installation. Failover cluster support configuration has also been added to the installation.

Easier to install SQL Server
Setup now advises of configuration problems such as installation pre-requisites, which helps streamline the installation process

SQL Server 2008 Top New Features: Hot-Add CPUs and Hot-Add Memory

2009-09-23T00:26:00.002-07:00

Hot-add CPUs, a feature available with the 64-bit edition SQL Server Enterprise, allows CPUs to be dynamically added to servers as needed, without the need to shut down the server or limit client connections. Hot-add memory enables memory to be added in the same way.

Dynamically add memory and processors to servers without incurring downtime

SQL Server 2008 Top New Features : Data Auditing

2009-09-23T00:26:00.001-07:00

Data Auditing provides a simple way to track and log events relating to your databases and servers. You can audit logons, password changes, data access and modification, and many other events. Tracking these events helps maintain security and can also provide valuable troubleshooting information. The results of audits can be saved to file or to the Windows Security or Application logs for later analysis or archiving.

Enables compliance with security regulations
Simple configuration using SQL Server Management Studio
Minimal impact on performance because audit data is stored outside of SQL Server database files

External Key Management / Extensible Key Management

2009-09-23T00:25:00.001-07:00

External Key Management enables certificates and encryption keys to be stored using third-party hardware security modules that are designed specifically for this purpose. Storing the keys separately from the data enables a more extensible and robust security architecture.

Helps organizations comply with data privacy regulations
Reduces administration requirements when there are multiple keys and certificates to manage
Standard interface supports third party hardware security modules

SQL Server 2008 Top New Features : Transparent Data Encryption

2009-09-23T00:24:00.002-07:00

Transparent Data Encryption enables data to be stored securely by encrypting the database files. If the disks that contain database files become compromised, data in those files is protected because that data can only be de-encrypted by an authorized agent. SQL Server performs the encryption and de-encryption directly, so the process is entirely transparent to connecting applications. Applications can continue to read and write data to and from the database as they normally would. Backup copies of encrypted database files are also automatically encrypted.

Implements strong encryption keys and certificates to secure data
Applications do not need to be modified to support Transparent Data Encryption
Enables compliance with data privacy regulations
Does not increase the size of the database

SQL Server 2008 Top New Features :Resource Governor

2009-09-23T00:24:00.001-07:00

The Resource Governor enables administrators to control and allocate CPU and memory resources to high priority applications. This enables predictable performance to be maintained and helps avoid performance from being negatively affected by resource-intense applications or processes

Prioritize applications, users, and computers competing for the same resources
Prevent runaway queries that hold resources for extended periods of time
Limitations are not enforced when there is no competition for resources

SQL Server 2008 Top New Features :Data Compression

2009-09-23T00:23:00.001-07:00

Data compression reduces the amount of storage space needed to store tables and indexes, which enables more efficient storage of data. Data Compression does not require changes be made to applications in order to be enabled.

Save disk storage
Enable compression option for individual tables or indexes
Configuration is easy using the Data Compression wizard
Applications do not need to be reconfigured as SQL Server handles compression and decompression of data
Compression can improve disk I/O and memory utilization

SQL Server 2008 Top New Features : Performance Data Collection (Data Collector)

2009-09-23T00:22:00.000-07:00

The Data Collector provides a convenient way to collect, store, and view performance data automatically. It collects disk usage, server activity, and query statistics data, which it loads in a management data warehouse and performance data can be reviewed in SQL Server Management Studio or by using third-party tools.

Setup wizard makes configuration simple
Data collection is automated by using set of SQL Server Agent jobs and SQL Server Integration Services packages
Management is centralized so data collection can be easily configured and results can be viewed in one place
Performance impact is minimal because the data collected can be cached and uploaded to the data warehouse later

SQL Server 2008 Top New Features : Policy-Based Management

2009-09-23T00:20:00.000-07:00

Policy-Based Management enables the efficient management of multiple SQL Server instances from a single location. Easily create policies that control security, database options, object naming conventions, and other settings at a highly granular level. Policies can evaluate servers for compliance with a set of predefined conditions and prevent undesirable changes being made to servers.

Management is centralized, thereby reducing the need to configure each server separately
Administration is simplified, reducing the effort required to maintain standardization and compliance, even in complex environments
Configuration is straightforward and can be done entirely within SQL Server Management Studio
Out-of-the-box predefined policies make it easy to get started
Backwards compatibility supports managing instances of SQL Server 2008, SQL Server 2005, and SQL Server 2000

When a query is sent to the database and an index is notbeing used, what type of execution is taking place?

2009-08-04T01:59:00.001-07:00

A table scans

What are the pros and cons of using triggers?

2009-08-04T01:58:00.001-07:00

Expected answer:

A trigger is one or more statements of SQL that are being executed in event of data modification in a table to which the trigger belongs. Triggers enhance the security, efficiency, and standardization of databases.
Triggers can be beneficial when used:

– to check or modify values before they are actually updated or inserted in the database. This is useful if you need to transform data from the way the user sees it to some internal database format.

– to run other non-database operations coded in user-defined functions

– to update data in other tables. This is useful for maintaining relationships between data or in keeping audit trail information.

– To check against other data in the table or in other tables. This is useful to ensure data integrity when referential integrity constraints aren’t appropriate, or when table check constraints limit checking to the current table only.

What are the advantages of views?

2009-03-25T23:19:00.001-07:00

- Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different perspective from that of the base table.
- Store complex queries.

What is an Oracle sequence?

2009-03-25T23:18:00.002-07:00

A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

Can a view based on another view?

2009-03-25T23:18:00.001-07:00

Can a view based on another view?
Yes.

Do a view contain data?

2009-03-25T23:17:00.004-07:00

Views do not contain or store data.

What is an Oracle view?

2009-03-25T23:17:00.003-07:00

A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

What is Oracle table?

2009-03-25T23:17:00.001-07:00

A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

Can a tablespace hold objects from different schemes?

2009-03-25T23:16:00.003-07:00

Can a tablespace hold objects from different schemes?
Yes.

Can objects of the same schema reside in different table spaces?

2009-03-25T23:16:00.001-07:00

Yes.

What are Schema Objects?

2009-03-25T23:15:00.002-07:00

Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

What is schema?

2009-03-25T23:15:00.001-07:00

A schema is collection of database objects of a user.

Explain the relationship among database, tablespace and data file.

2009-03-25T23:14:00.002-07:00

Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

What is SYSTEM tablespace and when is it created?

2009-03-25T23:14:00.001-07:00

Every Oracle database contains a tablespace named SYSTEM, which is automatically
created when the database is created. The SYSTEM tablespace always contains the data
dictionary tables for the entire database.

What is a tablespace?

2009-03-25T23:13:00.000-07:00

A database is divided into Logical Storage Unit called tablespaces.
A tablespace is used to grouped related logical structures together.

What are the components of logical database structure of Oracle database?

2009-03-25T23:12:00.000-07:00

There are tablespaces and database's schema objects.

What are the components of physical database structure of Oracle database?

2009-03-25T23:11:00.000-07:00

Oracle database is comprised of three types of files. One or more data files, two are more redo log files, and one or more control files.

Introduction to Full-Text Search -In Microsoft SQL Server 2005

2009-02-26T03:16:00.001-08:00

Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server database. In contrast to the LIKE predicate, which only works on character patterns, full-text queries perform linguistic searches against this data, by operating on words and phrases based on rules of a particular language.

In Microsoft SQL Server 2005, full-text search delivers enterprise search functionality. Significant enhancements in the areas of performance, manageability, and functionality deliver exceptional search capabilities for applications of any size.

The performance benefit of using full-text search can be best realized when querying against a large amount of unstructured text data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.

You can build full-text indexes on columns that contain char, varchar and nvarchar data. Full-text indexes can also be built on columns that contain formatted binary data, such as Microsoft Word documents, stored in a varbinary(max) or image column. You cannot use the LIKE predicate to query formatted binary data.

To create a full-text index on a table, the table must have a single, unique not null column. For example, consider a full-text index for the Document table in Adventure Works in which the DocumentID column is the primary key column. A full-text index indicates that the word "instructions" is found at word number 24 and word number 44 in the DocumentSummary column for the row associated with a DocumentID of 3. This index structure supports an efficient search for all items containing indexed words and advanced search operations, such as phrase searches and proximity searches. For more information, see Document Table (AdventureWorks).

When processing a full-text query, the search engine returns to SQL Server the key values of the rows that match the search criteria. If you want to use a full-text query to find the documents that include the word "instructions", the DocumentID values of 3, 4, 6, 7 and 8 are obtained from the full-text index. SQL Server then uses these keys to return the matching rows.

Using SESSION_USER to return the user name of the current session

2009-02-26T03:12:00.000-08:00

Examples

The following example declares a variable as nchar, assigns the current value of SESSION_USER to that variable, and then prints the variable with a text description.

DECLARE @session_usr nchar(30);
SET @session_usr = SESSION_USER;
SELECT 'This session''s current user is: '+ @session_usr;
GO

ISDATE() TSQL Return Value

2009-02-26T03:11:00.000-08:00

Column value (varchar)	ISDATE return value
NULL	0
Abc	0
100, -100, 100 a, or 100.00	0
.01	0
-100.1234e-123	0
.231e90	0
$100.12345, - $100.12345, or $-1000.123	0
as100 or 1a00	0
1995-10-1, 1/20/95, 1995-10-1 12:00pm, Feb 7 1995 11:00pm, 1995-10-1, or 1/23/95	1
13/43/3425 or 1995-10-1a	0
$1000, $100, or $100 a	0

HOST_ID() -TSQL Example

2009-02-26T03:10:00.001-08:00

The following example creates a table that uses HOST_ID() in a DEFAULT definition to record the terminal ID of computers that insert rows into a table recording orders.

CREATE TABLE Orders
(OrderID int PRIMARY KEY,
CustomerID nchar(5) REFERENCES Customers(CustomerID),
TerminalID char(8) NOT NULL DEFAULT HOST_ID(),
OrderDate datetime NOT NULL,
ShipDate datetime NULL,
ShipperID int NULL REFERENCES Shippers(ShipperID));
GO

HOST_NAME() T-SQL Example

2009-02-26T03:08:00.000-08:00

Examples

The following example creates a table that uses HOST_NAME() in a DEFAULT definition to record the workstation name of computers that insert rows into a table recording orders.

CREATE TABLE Orders
  (OrderID     int        PRIMARY KEY,
   CustomerID  nchar(5)   REFERENCES Customers(CustomerID),
   Workstation nchar(30)  NOT NULL DEFAULT HOST_NAME(),
   OrderDate   datetime   NOT NULL,
   ShipDate    datetime   NULL,
   ShipperID   int        NULL REFERENCES Shippers(ShipperID));
GO

Wildcard character - TSQL

2009-02-26T03:06:00.000-08:00

Wildcard character	Description	Example
%	Any string of zero or more characters.	WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
_ (underscore)	Any single character.	WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).	WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).	WHERE au_lname LIKE 'de[^l]%' all author last names starting with de and where the following letter is not l.

Example:t-SQL Using generic syntax for finding gaps in identity values

2009-02-25T09:17:00.001-08:00

-- Here is the generic syntax for finding identity value gaps in data.
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON
DECLARE @minidentval column_type
DECLARE @maxidentval column_type
DECLARE @nextidentval column_type
SELECT @minidentval = MIN($IDENTITY), @maxidentval = MAX($IDENTITY)
FROM tablename
IF @minidentval = IDENT_SEED('tablename')
SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
FROM tablename t1
WHERE $IDENTITY BETWEEN IDENT_SEED('tablename') AND
@maxidentval AND
NOT EXISTS (SELECT * FROM tablename t2
WHERE t2.$IDENTITY = t1.$IDENTITY +
IDENT_INCR('tablename'))
ELSE
SELECT @nextidentval = IDENT_SEED('tablename')
SET IDENTITY_INSERT tablename OFF
-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column
-- called id_num, which is an increasing identification number, and the
-- second column called company_name.
-- This is the end of the illustration example.

-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID ('dbo.img', 'U') IS NOT NULL
DROP TABLE img
GO
CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)
INSERT img(company_name) VALUES ('New Moon Books')
INSERT img(company_name) VALUES ('Lucerne Publishing')
-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON

DECLARE @minidentval smallint
DECLARE @nextidentval smallint
SELECT @minidentval = MIN($IDENTITY) FROM img
IF @minidentval = IDENT_SEED('img')
SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
FROM img t1
WHERE $IDENTITY BETWEEN IDENT_SEED('img') AND 32766 AND
NOT EXISTS (SELECT * FROM img t2
WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img'))
ELSE
SELECT @nextidentval = IDENT_SEED('img')
SET IDENTITY_INSERT img OFF

Examples :Using the IDENTITY property with CREATE TABLE

2009-02-25T09:15:00.000-08:00

T-SQL

USE AdventureWorks
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
DROP TABLE new_employees
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)

INSERT new_employees
(fname, minit, lname)
VALUES
('Karin', 'F', 'Josephs')

INSERT new_employees
(fname, minit, lname)
VALUES
('Pirkko', 'O', 'Koskitalo')

Examples :Listing the advanced configuration options

2009-02-25T09:14:00.000-08:00

USE master;
GO
EXEC sp_configure 'show advanced option', '1';

Example : Using nested cursors to produce report output

2009-02-25T09:13:00.001-08:00

SET NOCOUNT ON

DECLARE @vendor_id int, @vendor_name nvarchar(50),
@message varchar(80), @product nvarchar(50)

PRINT '-------- Vendor Products Report --------'

DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From Vendor: ' +
@vendor_name

PRINT @message

-- Declare an inner cursor based
-- on vendor_id from the outer cursor.

DECLARE product_cursor CURSOR FOR
SELECT v.Name
FROM Purchasing.ProductVendor pv, Production.Product v
WHERE pv.ProductID = v.ProductID AND
pv.VendorID = @vendor_id -- Variable value from the outer cursor

OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product

IF @@FETCH_STATUS <> 0
PRINT ' <>'

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @message = ' ' + @product
PRINT @message
FETCH NEXT FROM product_cursor INTO @product
END

CLOSE product_cursor
DEALLOCATE product_cursor
-- Get the next vendor.
FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor

Examples :Using simple cursor and syntax

2009-02-25T09:12:00.000-08:00

Examples :Using simple cursor and syntax

DECLARE vend_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor

System stored procedures Description

2009-02-25T09:10:00.000-08:00

sp_cursor_list
Returns a list of cursors currently visible on the connection and their attributes.

sp_describe_cursor
Describes the attributes of a cursor, such as whether it is a forward-only or scrolling cursor.

sp_describe_cursor_columns
Describes the attributes of the columns in the cursor result set.

sp_describe_cursor_tables
Describes the base tables accessed by the cursor.

LINQ programming model

2008-12-18T09:08:00.000-08:00

LINQ is a programming model that introduces queries as a first-class concept into any Microsoft .NET language. However, complete support for LINQ requires some extensions in the language used. These extensions boost productivity, thereby providing a shorter, meaningful,and expressive syntax to manipulate data.

Following is a simple LINQ query for a typical software solution that returns the names of customers in Italy:
var query =
from c in Customers
where c.Country == "Italy"
select c.CompanyName;

Do not worry about syntax and keywords (such as var) for now. The result of this query is a
list of strings. You can enumerate these values with a foreach loop in C#:

foreach ( string name in query ) {
Console.WriteLine( name );
}

Both the query definition and the foreach loop just shown are regular C# 3.0 statements. At
this point, you might wonder what we are querying. What is Customers? Is this query a new
form of embedded SQL? Not at all. The same query (and the following foreach code) can be
applied to an SQL database, to a DataSet, to an array of objects in memory, or to many other
kinds of data. Customers could be a collection of objects:

Customer[] Customers;
Customers could be a DataTable in a DataSet:
DataSet ds = GetDataSet();
DataTable Customers = ds.Tables["Customers"];

Customers could be an entity class that describes a physical table in a relational database:
DataContext db = new DataContext( ConnectionString );
Table Customers = db.GetTable();
Finally, Customers could be an entity class that describes a conceptual model and is mapped to
a relational database:
NorthwindModel dataModel = new NorthwindModel();
ObjectQuery Customers = dataModel.Customers;

As you will see, the SQL-like syntax used in LINQ is called a query expression. Languages thatimplement embedded SQL define only a simplified syntax to put SQL statements into a different language, but these statements are not integrated into the language’s native syntax and
type system. For example, you cannot call a function written using the host language in the middle of an SQL statement, although this is possible in LINQ. Moreover, LINQ is not limited to querying databases, as embedded SQL

Oracle9i AS: Basic Administrations

2008-09-27T03:34:00.000-07:00

Exam : 1Z0-301
Title : Oracle9i AS: Basic Administrations
Sample 3- Questions With Answer

# QUESTION 1
The machine hosting your Oracle9iAS Infrastructure has been stopped and the
operating system has been restarted.
Which three steps do you need to take to get Oracle Internet Directory working?
(Choose three)
A. Start Enterprise Manager daemon.
B. Start the Oracle9iAS metadata repository and the database listener.
C. Start Oracle Management Server in order to get connected to OEM Web site.
D. Navigate to the infrastructure homepage, select Oracle Internet Directory, and then
click Start.
E. Navigate to the Farm page, select the Oracle9iAS Infrastructure instance, and click
Start All to start Oracle9iAS metadata repository and the database listener and all OID
components.
F. Navigate to the Oracle Internet Directory home page and start the Distributed
Administration Service.
Answer: E

# QUESTION 2
Exhibit:
Examine the configuration context in which Oracle HTTP Server configuration
directives can be used.
Not all directives can be used everywhere. The Oracle HTTP Server distinguishes
between the configuration context and a per-directive basis. (Every directive has a fixed
set of contexts in which it is allowed.)
Which three statements are true? (Choose three)
A. The or container is allowed inside a directory container.
B. If you want to match URLs you need to use or
directives.
C. Box 1 represents the directory container built by or
directives.
D. The or container is allowed inside a or
container.
E. Box 1 represents a or container with directives that are
applied to a particular URL and its subareas.
Answer: A, B, C

# QUESTION 3
Exhibit:
You have a single installation of J2EE and Web Cache on a single host.
Which two Web server types does this topology support? (Choose two)
A. A Web server that has enabled single sign-on functionality.
B. A Web server that has a central user management repository.
C. A Web server that is preconfigured for caching of Web pages.
D. A Web server that is enabled to connect to an Oracle Directory Server.
E. A Web server that supports the deployment of J2EE-compliant applications.
F. A Web server that supports the deployment of Oracle Forms-based applications.
Answer: C, E

# QUESTION 4
Which two are appropriate directives for the default-web-site.xml file? (Choose two)
A.
B.
C.
D.
E.
F.
Answer: D, F

# QUESTION 5
Which three metrics can you get from the OC4J Instance page on OEM? (Choose three)
A. CPU usage
B. JDBC Connections
C. Application up time
D. 9iAS Instance up time
E. OC4J Instance up time
Answer: A, B, E

# QUESTION 6
Exhibit:
You are managing an Oracle9iAS Cluster as illustrated un the image.
During a period of high volume activity, an OC4J process on one of the nodes goes
down.
How is this detected, and which two activities takes place at that time? (Choose two)
A. The OC4J process death will be detected by the DCM process, and that process will
automatically start a replacement OC4J process.
B. The OC4J process death will be detected by an OHS process, which updates the
routing tables.
New OC4J requests will be routed to surviving OC4J processes.
C. The OC4J process death will be detected by the OPMN process, which updates the
routing tables.
New OC4J requests will be routed to surviving OC4J processes.
D. The OC4J process death will be detected by the DCM process, which updates the
routing tables.
New OC4J requests will be routed to surviving OC4J processes.
E. The OC4J process death will be detected by the OPMN process, and that process will
automatically start a replacement OC4J process.
Answer: C, E

# QUESTION 7
Exhibit:
dcmctl deployApplication -file name -a app_name
[-co comp_name] [-rc root_context]
Examine the syntax of the dcmctl deployApplication command.
You want to deploy the Web application module packages in simple-web-war to your
OC4J instance j2ee1. The Web archive is stored on the application server under the
directory /home/user/to_deploy. The application should be named simple-web and the
application should be accessible as mysimpleweb.
Which is the correct approach using dcmctl to deploy your application?
A. Wrap the Web application into J2EE application (.ear file) before deployment because
dcmctl supports only ear files.
Enter the following commands on your application server:
$> cd $ORACLE_HOME/dcm/bin
$> dcmctl deploy/Application \
-f /home/users/to_deploy/simple-web.ear \
-a simple-web -co oc4j -rc mysumpleweb
B. Wrap the Web application into a J2EE application (.ear file) before deployment
because dcmctl supports only ear files.
Enter the following commands on your application server:
$> cd $ORACLE_HOME/dcm/bin
$> dcmctl deployApplication \
-f /home/users/to_deploy/simple-web-ear \
-a mysimpleweb -co j2ee1 -rc simple-web
C. Enter the following commands on your application server:
$> cd $ORACLE_HOME/dcm/bin
$> dcmctl deployApplication \
D. Enter the following commands on your application server:
$> cd $ORACLE_HOME/dcm/bin
$> dcmctl deployApplication \
-f /home/users/to_deploy/simple-web.war \
-a simple-web -co j2ee1 -rc mysimpleweb
E. Enter the following commands on your application server:
$> cd $ORACLE_HOME/dcm/bin
$> dcmctl deploy/Application \
-f /home/users/to_deploy/simple-web.war \
-a mysimpleweb -co j2ee1 -rc simple-web
Answer: D

# QUESTION 8
Exhibit:
dcmctl deployApplication -file name -a app_name
[-co comp_name] [-rc root_context]
Examine the syntax of the dcmctl deployApplication command.
You want to deploy a J2EE application to your default OC4J instance that consists of a
Web module named simple-app and that is packaged in the file simple.ear.
The J2EE application should be deployed to your Oracle9iAS Instance named
j2ee.1prod1.us. CertKing .com.
The J2EE archive is stored on the application server under the directory
/home/users/to_deploy. The application should be administered under the name simpletest.
Which is the correct approach using dcmctl to deploy your application?
A. Enter the following commands on your application server:
$> cd $ORACLE_HOME/dcm/bin
$> dcmctl deployApplication \
-f /home/users/to_deploy/simple.ear \
-a simple-test-co j2ee1.prod1.us. CertKing .com \
-rc simple-test
B. Enter the following commands on your application server:
$> cd $ORACLE_HOME/dcm/bin
$> dcmctl deploy/Application \
-f /home/users/to_deploy/simple.ear \
-a simple-test -i j2ee1.prod.us. CertKing .com \
-rf simple-app
C. Enter the following commands on your application server:
$> cd $ORACLE_HOME/dcm/bin
$> dcmctl deployApplication \
-f /home/users/to_deploy/simple.ear \
-a simple-test -i j2ee1.prod1.use. CertKing .com
D. Enter the following commands on your application server:
$> cd $ORACLE_HOME/dcm/bin
$> dcmctl deployApplication \
-f /home/users/to_deploy/simple-app.war \
-a simple-test -i j2ee1.prod1.us. CertKing .com
E. Enter the following commands on your application server:
$> cd $ORACLE_HOME/dcm/bin
$> dcmctl deployApplication \
-f /home/users/to_deploy/simple-app.war \
-a simple-app -i j2ee1.prod1.us. CertKing .com
Answer: C

# QUESTION 9
A user authenticated to a partner application by Oracle9iAS Single Sign-On accesses
another partner application in the same session.
What does the second application do?
A. Checks the htaccess file to authenticate the user.
B. Uses the mod_osso cookie to authenticate the user.
C. Rewrites the URL and stores it in the SSO database.
D. Checks for the correct URL from the Single Sign-On server.
Answer: D

# QUESTION 10
Most J2EE applications use a database to have a persistent storage for data. Servlets, as
well as JSPs and EJBs, need to communicate with the database.
Which two statements are true? (Choose two)
A. To be portable across application servers J2EE applications use DataSource objects.
B. The JDBC DriverManager class has logical names that make applications that use it
portable.
C. JDBC DriverManager class objects enable portable J2EE applications across
application servers.
D. Every JDBC 2.0 driver has its own implementation of DataSource objects that can be
bound into an external JNDI namespace.
E. Every JDBC 2.0 driver has its own implementation of DriverManager class objects
that can be bound into an external JNDI namespace.
F. Using the JDBC DriverManager class is the recommended way for a J2EE application
to get a connection.
Answer: A, D

# QUESTION 11
Which Oracle9iAS capabilities require an Infrastructure? (Choose all that apply)
A. Single Sign-On
B. Application Server Clusters
C. All Oracle9iAS installation types
D. Oracle9iAS Web Cache Clusters
E. Invoking PL/SQL code via Database Access Descriptors
Answer: A, B, D

# QUESTION 12
Most J2EE applications, servlets as well as JSPs and EJBs, need to communicate with
the database.
Which two are true regarding data sources? (Choose two)
A. J2EE applications use JNDI to look up DataSource objects.
B. J2EE applications retrieve connections to the database through DataSource objects.
C. DataSource objects include the mapping between the logical database connection and
the physical database information.
D. J2EE applications retrieve database connections from the application code itself, that
includes the physical database information.
Answer: B, C

# QUESTION 13
When stopping the Infrastructure instance, which option is correct?
A. Stopping the Infrastructure will stop all of the Oracle9iAS Application Server
instances that use it.
B. You should stop the Infrastructure first, and then stop all of the Oracle9iAS
Application Server instances that use it.
C. You should first stop all of the Oracle9iAS Application Server Instances that use the
Infrastructure, then stop the Infrastructure.
D. Stopping the Infrastructure does not affect the Oracle9iAS Application Server
instances that use it.
Answer: C

# QUESTION 14
Which process of an Oracle9iAS Instance is responsible for managing and tracking the
process in the Instance, as well as propagating events among other Instances in the
Farm?
A. DCM
B. OPMN
C. OC4J
D. The OHS Parent process
E. An OHS Child process
Answer: B

# QUESTION 15
Which six correctly match the item to its description? (Choose six)
A. Dispatcher: Listens for LDAP requests.
B. LDAP server process: Processes LDAP requests.
C. Dispatcher: Sends changes to other OID nodes.
D. Oracle Director Manager: Displays configuration sets.
E. OID Replication process: Sends changes to other OID nodes.
F. LDAP server process: Initiates and terminates OID server processes.
G. OID Control Utility: Accepts commands to start and stop OID instances.
Answer: F

# QUESTION 16
Which two OC4J configuration files are server configuration files? (Choose two)
A. rmi.xml
B. web.xml
C. jms.xml
D. ejb.xml
E. orion-web.xml
Answer: A, C

# QUESTION 17
Which statement is true about the Oracle9iAS Single Sign-On session duration?
A. It is applicable to partner applications only.
B. It is applicable to external applications only.
C. It is applicable to all single sign-on sessions.
D. It is applicable to administrative sessions only.
Answer: C

# QUESTION 18
Exhibit:
You installed one J2EE and Web Cache instance, which is running. Because your
company wants to use the Oracle Internet Directory as a central repository for user
information for an application that should be single sign-on enabled you installed
Oracle9iAS Infrastructure.
Which actions are required in order to use Oracle Internet Directory? (Choose all that
apply)
A. Ensure that the Oracle9iAS Infrastructure instance is up and running.
B. Using Oracle Enterprise Manager Website select the J2EE and Web Cache instance
and associate the J2EE and Web Cache Instance with Oracle9iAS Infrastructure.
C. Using the Oracle Enterprise Manager Web site, select the Oracle9iAS metadata
repository and associate the metadata repository with the J2EE and Web Cache
instance.
D. Stop both instances, J2EE and Web Cache and Oracle9iAS Infrastructure.
Then start the J2EE and Web Cache instance before you start the Oracle9iAS
Infrastructure instance.
E. Restart the J2EE and Web Cache instance to have the instance recognized by
Oracle9iAS Infrastructure.
F. The only required step is to restart both instances, J2EE and Web Cache and
Oracle9iAS Infrastructure.
Answer: A, B

# QUESTION 19
During the installation process, you can choose to install Oracle9iAS Infrastructure.
Which two statements describe the relationship between Oracle9iAS Infrastructure and
Oracle9i Application Server? (Choose two)
A. Oracle9iAS Infrastructure is a prerequisite for all Oracle9i Application Server
Installation types.
B. All Oracle9iAS Instances except the Installation type Portal and Wireless use
Oracle9iAS Infrastructure.
C. Oracle9i Application Server Installation type J2EE and Web Cache does NOT need an
Oracle9iAS Infrastructure as a prerequisite.
D. Oracle9iAS Infrastructure contains Oracle Internet Directory and Oracle Internet
Filesystem to centralized user management.
E. Oracle9i Application Server instances like J2EE and Web Cache need to be single
Oracle9iAS Infrastructure.
F. Oracle9iAS Infrastructure contains a preconfigured database to store all metadata
information necessary for Oracle9iAS instances.
Answer: C, E

# QUESTION 20
Which two are files that Oracle9iAS Web Cache reads at startup? (Choose two)
A. httpd.conf
B. mod_oc4j.xml
C. internal.xml
D. webcache.xml
E. webcacheroot.orc
Answer: A, D

# QUESTION 21
Which two statements describe features of the servlet support in Oracle9iAS Containers
for J2EE (OC4J)? (Choose two)
A. Allows use of WAR files to deploy packaged applications.
B. Requires code changes for distributable web applications.
C. Retains translated servlet code for extended error reporting.
D. Provides simple and complex object relational mapping for entity beans.
E. Replicates state of Web Applications to other servers in the same cluster.
Answer: A, E

# QUESTION 22
Which mod_plsql directive enables you to provide access to a specific PL/SQL
procedure without specifying it in the URL?
A. PlsqlDefaultPage
B. PlsqlDatabaseName
C. PlsqlDatabaseUsername
D. PlsqlDatabasePassword
Answer: A

# QUESTION 23
What does the Delegated Administration Service or Oracle9iAS SSO enable
nonadministrative users to do?
A. Create new SSO servers.
B. Modify SSO user's personal data.
C. Create new Oracle Internet Directories.
D. Modify SSO administrator's schema location.
Answer: B

# QUESTION 24
Exhibit:
Examine the image showing the Oracle Directory Manager Menu and navigation bar.
Oracle Directory Manager has a double window interface. You want to obtain password
credentials about the user appuser created with the Distributed Administration Service
(DAS).
Where do you get the appropriate information?
A. Click the plus sign next to Server Management to expand the subtree, then navigate to
Directory Server, where you get information about the user.
B. Click the plus sign next to Access Control Management, then navigate to the entry
cn=DAS, where you get the information about the user.
C. Click the plus sign next to Entry Management to expand the subtree, then drill down to
the level of cn=Users to obtain the wanted information.
D. Click the plus sign next to Schema Management, then navigate to the entry cn=Users,
where you get the information about the user.
E. Click the plus sign next to Password Policy Management, then navigate to the entry
cn=DAS where you get the information about the user.
F. Click the plus sign next to Password Verifier Management, then navigate to the entry
cn=DAS where you get the information about the user.
Answer: E

# QUESTION 25
You want to use Basic Authentication to protect access to your web site using Oracle
HTTP Server password files. To accomplish this, you will use a or
block container in your httpd.conf configuration file.
Which three Require directives are correct? (Choose three)
A. Require valid-user
B. Require valid_user
C. Require user
D. Require group
Answer: A, C, D

# QUESTION 26
Which component of an Oracle9iAS instance is responsible for accessing the
configuration information for the Farm, reading changes that need to be propagated
throughout the Farm, and updating the configuration information?
A. DCM
B. OPMN
C. OC4J
D. The OHS parent process
E. An OHS child process
Answer: B

# QUESTION 27
Which two protocols are available by default for the Oracle9iAS Containers for J2EE
(OC4J) to receive communication when it is installed as a part of an Oracle9iAS
Instance? (Choose two)
A. HTTP
B. ORMI
C. HTTPS
D. AJP12
E. AJP13
Answer: B, E

# QUESTION 28
In which two directories would you typically find the mod_plsql configuration files?
(Choose two)
A. $ORACLE_HOME/network/admin
B. $ORACLE_HOME/Apache/Apache/conf
C. $ORACLE_HOME/Apache/dads/conf
D. $ORACLE_HOME/Apache/modplsql/conf
E. $ORACLE_HOME/Apache/Apache/htdocs
Answer: B, D

# QUESTION 29
A collection of Oracle9iAS Instances that share the same Infrastructure but do not have
identical configurations is referred to as a _______.
A. Cluster
B. Farm
C. Instance
D. Component Cluster
Answer: B

# QUESTION 30
When you configure your application to run in Single Sign-On environment, you should
register _______.
A. mod_osso as a partner application.

Database :What are the types of indexes available with SQL Server?

2008-08-03T00:46:00.001-07:00

There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.

Database : What is a trigger?

2008-08-03T00:44:00.001-07:00

Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.

Can you give an example of system defined Stored Procedure?

2008-08-03T00:43:00.001-07:00

sp_helpdb , sp_who2, sp_renamedb are a set of system defined stored procedures. We can also have user defined stored procedures which can be called in similar way.

Database : What is a Stored Procedure?

2008-08-03T00:41:00.001-07:00

Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.

Database Performance Monitor (ST04)

2008-08-03T00:36:00.001-07:00

Database Performance Monitor (ST04) provides a database-independent tool to analyze
and tune the following components:
< Memory and buffer usage
< Space usage
< CPU usage
< SQL requests
< Detailed SQL items

1. In the Command field, enter transaction ST04 and choose Enter (or from the SAP standard menu, choose Tools ® Administration ® Monitor ® Performance ® Database ®
ST04 - Activity).

2. An initial overview of database activity is provided which pertains to database,operating system, CPU, and memory.Microsoft SQL Server allows the analysis of specific attributes pertaining to memory, space, I/O, and quality of table reads and writes. This information can signal adjustments necessary to improve performance of the database.

a. Memory Usage Procedure cache and Data cache hit ratio can reflect memory problems.These values should be greater than95 percent for optimal memory usage.
b. Server Engine/Elapsed Shows how hard the CPU has beenworking on Microsoft SQL Serverprocesses. You are interested in theratio of busy : idle time.
c. SQL Requests Allows for snapshots of how SQL queries are utilizing table access pertaining to full table or index scans.A high ratio of full table scans vs. index scans can indicate performance bottlenecks.
d. Detail analysis menu

T-SQL Query : How to retrieve only the Nth row from a table?

2008-07-14T23:14:00.001-07:00

Method 1

SELECT * FROM t1 a
WHERE n = (SELECT COUNT(rowid)
FROM t1 b
WHERE a.rowid >= b.rowid);

Method 2

SELECT * FROM (
SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )

SQL statement : How to Delete duplicates rows from a table?

2008-07-14T23:06:00.001-07:00

Choose one of the following queries to identify or remove duplicate rows from a table leaving only unique records in the table:

Method 1:

Delete all rowids that is BIGGER than the SMALLEST rowid value (for a given key):

SQL> DELETE FROM table_name A WHERE ROWID > (
2 SELECT min(rowid) FROM table_name B
3 WHERE A.key_values = B.key_values);

Method 2:

This method is usually faster. However, remember to recreate all indexes, constraints, triggers, etc. on the table when done.

SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table table_name1;
SQL> rename table_name2 to table_name1;

Method 3:

SQL> delete from my_table t1
SQL> where exists (select 'x' from my_table t2
SQL> where t2.key_value1 = t1.key_value1
SQL> and t2.key_value2 = t1.key_value2
SQL> and t2.rowid > t1.rowid);

Oracle FAQ : How can one dump/ examine the exact content of a database column?

2008-07-14T23:02:00.001-07:00

Table data can be extracted from the database as octal, decimal or hex values:

SELECT DUMP(col1, 10)
FROM tab1
WHERE cond1 = val1;
DUMP(COL1)
-------------------------------------
Typ=96 Len=4: 65,66,67,32

For this example, type=96 is indicating a CHAR column. The last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.

Oracle FAQ : How to select a random collection of rows from a table?

2008-07-14T23:00:00.001-07:00

The following methods can be used to select a random collection of rows from a table:

The SAMPLE Clause

From Oracle 8i, the easiest way to randomly select rows from a table is to use the SAMPLE clause with a SELECT statement. Examples:

SELECT * FROM emp SAMPLE(10);

In the above example, Oracle is instructed to randomly return 10% of the rows in the table.

SELECT * FROM emp SAMPLE(5) BLOCKS;

This example will sample 5% of all formatted database blocks instead of rows.

This clause only works for single table queries on local tables. If you include the SAMPLE clause within a multi-table or remote query, you will get a parse error or "ORA-30561: SAMPLE option not allowed in statement with multiple table references". One way around this is to create an inline view on the driving table of the query with the SAMPLE clause. Example:

SELECT t1.dept, t2.emp
  FROM (SELECT * FROM dept SAMPLE(5)) t1,
       emp t2
 WHERE t1.dep_id = t2.dep_id;

If you examine the execution plan of a "Sample Table Scan", you should see a step like this:

TABLE ACCESS (SAMPLE) OF 'EMP' (TABLE)

ORDER BY dbms_random.value()

This method orders the data by a random column number. Example:

SQL> SELECT * FROM (SELECT ename
  2                   FROM emp
  3                  ORDER BY dbms_random.value())
  4   WHERE rownum <= 3;
ENAME
----------
WARD
MILLER
TURNER

The ORA_HASH() function

The following example retrieves a subset of the data in the emp table by specifying 3 buckets (0 to 2) and then returning the data from bucket 1:

SELECT * FROM emp WHERE ORA_HASH(empno, 2) = 1;

Oracle FAQ : How does one escape special characters when writing SQL queries?

2008-07-14T22:58:00.001-07:00

Escape quotes

Use two quotes for every one displayed. Examples:

SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;
 TEXT
 --------------------
 Franks's Oracle site

 SQL> SELECT 'A ''quoted'' word.' AS text FROM DUAL;
  TEXT
 ----------------
 A 'quoted' word.

 SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;
 TEXT
 -------------------------
 A ''double quoted'' word.

Escape wildcard characters

The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:

SELECT name FROM emp 
 WHERE id LIKE '%/_%' ESCAPE '/';

SELECT name FROM emp 
 WHERE id LIKE '%\%%' ESCAPE '\';

Escape ampersand (&) characters in SQL*Plus

When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text:

SET DEFINE ~
SELECT 'Lorel & Hardy' FROM dual;

Other methods:

Define an escape character:

SET ESCAPE '\'
SELECT '\&abc' FROM dual;

Don't scan for substitution variables:

SET SCAN OFF
SELECT '&ABC' x FROM dual;

Use the 10g Quoting mechanism:

Syntax
 q'[QUOTE_CHAR]Text[QUOTE_CHAR]'
 Make sure that the QUOTE_CHAR doesnt exist in the text.

SELECT q'{This is Orafaq's 'quoted' text field}' FROM DUAL;

What are the difference between DDL, DML and DCL commands?

2008-07-14T22:56:00.000-07:00

DDL - Data Definition Language: statements used to define the database structure or schema. Some examples:

CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object

DML - Data Manipulation Language: statements used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

DCL - Data Control Language. Some examples:

GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command

TCL - Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

DML are not auto-commit. i.e. you can roll-back the operations, but DDL are auto-commit

SQL server 2008 : LAMBDA EXPRESSIONS

2008-07-14T22:34:00.001-07:00

C# 2.0 provided a shorthand method of creating methods and instantiating delegates all in one construct, known as anonymous delegates. C# 3.0 expanded on this idea with a more compact syntactical construct for creating anonymous methods known as lambda expressions. On its most basic level, a lambda expression is simply an anonymous function or method. In Listing 13-8, I've used lambda expressions in two places as arguments to the Average method. The lambda expressions I used were as follows:
p => (float)p.Element("open")
p => (float)p.Element("close")
In both of the preceding lambda expressions, the anonymous function is both declared and instantiated in one expression. Query methods, like the Average method, can accept anonymous functions as parameters— a very powerful feature. Lambda expressions offer many advantages (apart from terseness of code) that .NET 2.0 anonymous delegates do not provide:

SQL server 2008 :LINQ TO XML NAMESPACES

2008-07-14T22:33:00.001-07:00

LINQ to XML provides namespace support through the XNamespace object. To create a namespace, just assign the Uniform Resource Identifier (URI) to an XNamespace variable. To use the namespace, just concatenate it to the element name when querying. LINQ to XML automatically expands the namespace-qualified element name out to its fully qualified name internally, so you don't have to worry about it.

SQL server 2008 : SQLXML XPATH LIMITATIONS

2008-07-14T22:32:00.001-07:00

SQLXML supports a subset of XPath queries. Following is a list of limitations imposed on the SQLXML XPath implementation:

• The only axis specifiers supported are child, parent, attribute, and self.
• Only element and attribute node types are supported.
• Only predicates that return a boolean result are supported. Numeric predicates like [1] are not supported.
• Only the three XPath data types are supported: string, number, and boolean.
• SQLXML 4 does not support the root query (specified by a forward slash /), or descendant-or-self queries (specified by double slashes //). Every query must begin at a top-level schema element type.
• SQLXML does not support queries that generate a Cartesian product.
• The mod and union (|) operators, string functions, and numeric functions are not supported.

SQL server 2008 : GML

2008-07-14T22:31:00.001-07:00

GML is the OGC standard for representation of geospatial data in XML format. SQL Server provides support for a subset of GML, allowing you to create geometry and geography data type instances from GML (or convert them to GML) using built-in data type methods. Y

SQL server 2008 :DTD, XDR, AND XSD

2008-07-14T22:30:00.001-07:00

DTDs provide the most basic standard level of validation available to XML. DTDs provide simple structure and string-based content validation; however, they do not provide data typing functionality. DTDs also use a legacy non-XML format inherited from Standard Generalized Markup Language (SGML). DTDs have the advantage of having been part of the XML recommendation since the beginning, so nearly all XML parsers support them to some degree.

XDR schemas represent Microsoft's first attempt at implementing an early working draft of the W3C XML Schema recommendation. XDR functionality is Microsoft-specific, and you won't find it implemented widely on other platforms. XDR functionality for constraining XML structure and typing content has been superseded by the official W3C XML Schema recommendation, and support for XDR is provided for
backward-compatibility reasons. You normally won't use XDR schemas for new functionality, though you might run into them while supporting legacy applications.

SQL server 2008 : HTTP ENDPOINTS AND AD HOC QUERYING

2008-07-14T22:29:00.001-07:00

The BATCHES option is a powerful option, but it should be used with care. Any time you allow ad hoc querying of your database, you should carefully consider the security ramifications. I would advise against allowing ad hoc querying over HTTP SOAP endpoints unless you have a compelling reason. And even then you should perform a very thorough security review to ensure that no unauthorized access is allowed to your database and to make sure that users cannot execute destructive ad hoc T-SQL code on your server.

SQL server 2008 : SECURE YOUR SQL SERVER ENDPOINTS

2008-07-14T22:28:00.001-07:00

There is some concern from database administrators about exposing SQL Server directly to web traffic via the HTTP SOAP endpoints. This concern is not unreasonable, since you could potentially open up your server to unwanted traffic and attacks from the Internet. However, HTTP SOAP endpoints provide security that is very tightly integrated with SQL Server's built-in security model. If your SQL Server is secure, your exposure to attacks via endpoints is minimized. If your server is not properly secured, endpoints only exacerbate the problem. Of course, an unsecure SQL Server is probably more likely to be attacked using tried-and-true attacks, like SQL injection or password brute-force/dictionary attacks over the standard SQL Server TCP and user datagram protocol ports than via SOAP endpoints.

On the other hand, the vast majority of SQL Servers that are set up to expose web service methods via HTTP SOAP endpoints tend to be set up for internal use only, behind firewalls and with the additional network security provided by network administrators on a local area network or wide area network. If you set up any SQL Server for exposure over the Internet (endpoints enabled or not), make sure you do a thorough security analysis of your server and network to minimize the potential that your server, network, and data can become compromised. Though they are outside the scope of this book, the same type of precautions should be taken when exposing any computer to the Internet, including web servers and other networked computers.

SQL server 2008 : XSL:CHOOSE

2008-07-14T22:27:00.001-07:00

The xsl:choose element is analogous to the SQL searched CASE expression. This element contains one or more xsl:when elements that are analogous to WHEN clauses in SQL searched CASE expressions. A SQL searched CASE expression is one where each WHEN clause is a predicate. Like the SQL searched CASE expression, each xsl:when element takes a test attribute that contains an expression. If the expression for an xsl:when element evaluates to true, the content of that element is used. The xsl:choose element can also contain an xsl:otherwise element which is equivalent to the CASE expression's ELSE clause. If all the xsl:when elements evaluate to false, the content of the xsl:otherwise element is used. XSLT does not require that your expression be a true Boolean expression. Any expression in XSLT can evaluate to an effective Boolean value. Chapter 6 has a discussion of effective Boolean value in XQuery.

SQL server 2008 : DHTML, CSS, AND XHTML

2008-07-14T22:26:00.001-07:00

In 1999, the W3C approved the HTML 4.01 specification for web-based publishing (this recommendation is commonly known as HTML4). In 2000, the W3C quickly followed up with the XHTML (Extensible HTML) standard, which redefines HTML as an XML application. During this same time, vendors were just starting to get serious about implementing the 1996 W3C recommendation for CSS functionality in their browser products.

Historically speaking, attributes played a key role in HTML formatting. All the way up to HTML4, there is a heavy reliance on attributes to specify colors, borders, spacing, position, and just about every other formatting option supported by HTML. With the adoption of XHTML, most of these attributes were deprecated in favor of the more powerful and flexible CSS model. In an attempt to follow modern user interface coding standards, I've used CSS and generated properly formed HTML in the examples of this chapter. All HTML results have
been tested for standards conformance in both Internet Explorer 6 and Firefox 2.0.

SQL server 2008 : FULL-TEXT CONTAINS VS. XQUERY CONTAINS

2008-07-14T22:25:00.001-07:00

The full-text search CONTAINS predicate is not the same as the XQuery contains predicate. The XQuery contains predicate performs a substring match similar to the T-SQL CHARINDEX function. The matches performed by the XQuery contains predicate are case sensitive.
The T-SQL CONTAINS predicate, on the other hand, includes all of the flexibility of the SQL Server full-text search functionality. This includes the ability to perform thesaurus lookups, word stemming, and proximity searches.

The downside to the T-SQL CONTAINS predicate is that you cannot specify node paths to narrow your search using them. The T-SQL full-text search is an all-or-nothing proposition—if you want to search for a word in your XML data using a full-text search, the word can appear anywhere in the XML content. This is why it makes sense to use the T-SQL CONTAINS predicate in conjunction with the XQuery contains predicate for maximum flexibility and performance.

SQL server 2008 : HTTP SOAP Endpoints

2008-07-14T22:23:00.001-07:00

A powerful feature introduced in SQL Server 2005, SQL Server 2008 continues providing support for native HTTP SOAP endpoints, which use the XML-based SOAP protocol. HTTP SOAP endpoints provide an efficient, secure, easy-to-configure option for providing SQL Server–based
web service support. The built-in HTTP SOAP endpoint support makes it much easier to expose SQL Server functionality as web services than was previously possible via the Internet Information Server (IIS)–based web services available in SQL Server 2000.

SQL server 2008 : XQuery and XML DML Support

2008-07-14T22:22:00.001-07:00

The new xml data type provides several methods to allow querying and modification of XML data. These new methods, including the query(), value(), exist(), nodes(), and modify() methods, support XQuery querying, XML shredding, and XML DML manipulation of your XML data. The SQL Server 2008 XQuery implementation is a powerful subset of the W3C XML Query Language specification, featuring support for path expressions, FLWOR (for-let-whereorder- by-return) expressions, standard functions and operators, and XML DML statements.

SQL server 2008 :FOR XML

2008-07-14T22:21:00.001-07:00

SQL Server includes improvements to the legacy FOR XML clause. One improvement is tighter integration with the new xml data type, including options to generate native xml-typed results. FOR XML results can be assigned to variables of the xml data type, with additional support for nesting FOR XML queries, an improvement on the SQL Server 2000 FOR XML clauses, which were limited only to the top level of a SELECT statement. The FOR XML PATH mode, also carried over from SQL Server 2005, is an improvement over the legacy FOR XML EXPLICIT mode. With builtin support for XPath-style expressions, FOR XML PATH makes generating XML in explicit structures
much easier than was possible in SQL Server 2000.

The FOR XML RAW mode has also been improved with additional features, including the ability to rename the default row element name, the ability to explicitly specify the root node, and the ability to retrieve your data in an element-centric format. The FOR XML AUTO and FOR XML EXPLICIT modes have also been improved with additional options and settings.

While some options have been deprecated, several additional options have been added to the FOR XML clauses since the SQL Server 2000 version, including the ELEMENTS XSINIL option, which generates elements for NULLs in the result set, and XMLSCHEMA, which generates an inline XML Schema Definition (XSD) in your XML result. T

SQL server 2008 : XML Indexes

2008-07-14T22:20:00.001-07:00

In the SQL Server XML model, whenever you query or manipulate XML data, the data is first converted to a relational format in a process known as shredding. This process can be time consuming when manipulating large XML documents or when querying large numbers of xml data type instances. SQL Server 2008 supports indexing of xml data type columns. Indexing xml columns helps the SQL optimizer significantly improve query performance on XML data stored in the database. The performance is improved by building an index of your XML data by converting it to a relational format, a process known as preshredding. The XML index preshredding process eliminates the shredding step during a query or XML data manipulation, resulting in much faster and less resource-intensive XML query operations. New DML statements have been added to T-SQL to make XML index management relatively easy.

SQL server 2008 xml Data Type - New feature

2008-07-14T22:18:00.001-07:00

Prior to SQL Server 2005, SQL Server provided extremely limited support for storing, managing, and manipulating XML data. SQL Server 2000 implemented its XML capabilities through implementation of the FOR XML clause and kludgy LOB data type operations combined with
specialized system-stored procedures. SQL Server 2005 introduced the xml data type, promoting XML data storage and manipulations to first-class status in SQL Server.

The xml data type remains one of the most important XML-specific features in SQL Server 2008. The xml data type supports the storage of typed XML documents and fragments that have been validated against an XML schema collection and untyped XML data which has not. The
xml data type can be used to declare columns in a table, T-SQL variables, parameters, and as the return type of a function. Data can also be cast to and from the xml data type. In addition, the xml data type brings with it a set of methods useful for querying, shredding, and manipulating XML data.

What’s New in SQL Server 2008 XML

2008-07-14T22:14:00.001-07:00

SQL Server 2008 provides several enhancements over SQL Server 2000 in terms of XML support and some enhancements over SQL Server 2005. While much of the backward-compatible XML-specific functionality from SQL Server 2000 is available in SQL Server 2008, most of it has been deprecated in favor of the new features and functionality. This section gives a broad overview of the major enhancements to XML support, which include the following items:

• New xml data type
• XML schema collections
• XML indexes
• FOR XML enhancements, including XPath support in the FOR XML PATH clause
• XQuery and XML DML support
• SQLCLR xml data type support
• Improvements to legacy XML functionality, including improvements to the sp_xml_preparedocument procedure
• HTTP Simple Object Access Protocol (SOAP) endpoints

Oracle Admin : Creating Initial Control Files

2008-07-05T23:44:00.000-07:00

The initial control files of an Oracle Database are created when you issue the CREATE DATABASE statement. The names of the control files are specified by the CONTROL_FILES parameter in the initialization parameter file used during database creation. The filenames specified in CONTROL_FILES should be fully specified and are operating system specific. The following is an example of a CONTROL_FILES initialization parameter:

CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
/u02/oracle/prod/control02.ctl,
/u03/oracle/prod/control03.ctl)

If files with the specified names currently exist at the time of database creation, you must specify the CONTROLFILE REUSE clause in the CREATE DATABASE statement, or else an error occurs. Also, if the size of the old control file differs from the SIZE parameter of the new one, you cannot use the REUSE clause.

The size of the control file changes between some releases of Oracle Database, as well as when the number of files specified in the control file changes. Configuration parameters such as MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES affect control file size.

You can subsequently change the value of the CONTROL_FILES initialization parameter to add more control files or to change the names or locations of existing control files.

Oracle : Application Developers Responsibility

2008-07-05T23:42:00.001-07:00

Application developers design and implement database applications. Their responsibilities include the following tasks:

Designing and developing the database application
Designing the database structure for an application
Estimating storage requirements for an application
Specifying modifications of the database structure for an application
Relaying this information to a database administrator
Tuning the application during development
Establishing security measures for an application during development