SQL interview question and answers

SQL interview question and answers
SQL
SQL is an English like language consisting of commands to store, retrieve, maintain & regulate access to your database.

SQL*Plus
SQL*Plus is an application that recognizes & executes SQL commands & specialized SQL*Plus commands that can customize reports, provide help & edit facility & maintain system variables.

NVL
NVL : Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER.

Date Functions
Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE.

Character Functions
Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.

TTITLE & BTITLE
TTITLE & BTITLE are commands to control report headings & footers.

COLUMN
COLUMN command define column headings & format data values.

BREAK
BREAK command clarify reports by suppressing repeated values, skipping lines & allowing for controlled break points.

COMPUTE
command control computations on subsets created by the BREAK command.

SET
SET command changes the system variables affecting the report environment.

SPOOL
SPOOL command creates a print file of the report.

JOIN
JOIN is the form of SELECT command that combines info from two or more tables.
Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.
Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.
Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.
Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table.
Self Join joins a table to itself as though it were two separate tables.

Union
Union is the product of two or more tables.

Intersect
Intersect is the product of two tables listing only the matching rows.

Minus
Minus is the product of two tables listing only the non-matching rows.

Correlated Subquery
Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement.

Multiple columns
Multiple columns can be returned from a Nested Subquery.

Sequences
Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.

Synonyms
Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience.
Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.

Indexes
Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes. Concatenated index max. is 16 columns.

Data types
Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits.
Cannot Query on a long column.
Char, Varchar2 Max. size is 2000 & default is 1 byte.
Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.
Long Character data of variable length upto 2GB.
Date Range from Jan 4712 BC to Dec 4712 AD.
Raw Stores Binary data (Graphics Image & Digitized Sound). Max. is 255 bytes.
Mslabel Binary format of an OS label. Used primarily with Trusted Oracle.

Order of SQL statement execution
Where clause, Group By clause, Having clause, Order By clause & Select.

Transaction
Transaction is defined as all changes made to the database between successive commits.

Commit
Commit is an event that attempts to make data in the database identical to the data in the form. It involves writing or posting data to the database and committing data to the database. Forms check the validity of the data in fields and records during a commit. Validity check are uniqueness, consistency and db restrictions.

Posting
Posting is an event that writes Inserts, Updates & Deletes in the forms to the database but not committing these transactions to the database.

Rollback
Rollback causes work in the current transaction to be undone.

Savepoint
Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.

Set Transaction
Set Transaction is to establish properties for the current transaction.

Locking
Locking are mechanisms intended to prevent destructive interaction between users accessing data. Locks are used to achieve.

Consistency
Consistency : Assures users that the data they are changing or viewing is not changed until the are thro' with it.

Integrity
Assures database data and structures reflects all changes made to them in the correct sequence. Locks ensure data integrity and maximum concurrent access to data. Commit statement releases all locks. Types of locks are given below.
Data Locks protects data i.e. Table or Row lock.
Dictionary Locks protects the structure of database object i.e. ensures table's structure does not change for the duration of the transaction.
Internal Locks & Latches protects the internal database structures. They are automatic.
Exclusive Lock allows queries on locked table but no other activity is allowed.
Share Lock allows concurrent queries but prohibits updates to the locked tables.
Row Share allows concurrent access to the locked table but prohibits for a exclusive table lock.
Row Exclusive same as Row Share but prohibits locking in shared mode.
Shared Row Exclusive locks the whole table and allows users to look at rows in the table but prohibit others from locking the table in share or updating them.
Share Update are synonymous with Row Share.

Deadlock
Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.

Mutating Table
Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement. Constraining Table is a table that a triggering statement might need to read either directly for a SQL statement or indirectly for a declarative Referential Integrity constraints. Pseudo Columns behaves like a column in a table but are not actually stored in the table. E.g. Currval, Nextval, Rowid, Rownum, Level etc.

SQL*Loader
SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file. The control file describes the data to be loaded. It describes the Names and format of the data files, Specifications for loading data and the Data to be loaded (optional). Invoking the loader sqlload username/password controlfilename . Read More!

Datastructure Linked list interview questions

Data Structure or Linkedlist interview questions
1)How do you reverse a singly linked list? How do you reverse a doubly linked list? Write a C program to do the same.
2)Given only a pointer to a node to be deleted in a singly linked list, how do you delete it?
3)How do you sort a linked list? Write a C program to sort a linked list.
4)How to declare a structure of a linked list?
5)Write a C program to implement a Generic Linked List.
6)How do you reverse a linked list without using any C pointers?
7)How would you detect a loop in a linked list? Write a C program to detect a loop in a linked list.
8)How do you find the middle of a linked list? Write a C program to return the middle of a linked list.
9)If you are using C language to implement the heterogeneous linked list, what pointer type will you use?
10)How to compare two linked lists? Write a C program to compare two linked lists.
11)How to create a copy of a linked list? Write a C program to create a copy of a linked list.
12)Write a C program to free the nodes of a linked list.
13)Can we do a Binary search on a linked list?
14)Write a C program to return the nth node from the end of a linked list.
15)How would you find out if one of the pointers in a linked list is corrupted or not?
16)Write a C program to insert nodes into a linked list in a sorted fashion.
17)Write a C program to remove duplicates from a sorted linked list.
18)How to read a singly linked list backwards?
19)How can I search for data in a linked list? Read More!

IBM DB2 interview questions,Faqs

Question :What do you mean by NOT NULL WITH DEFAULT? When will you use it?
Answer :This column cannot have nulls and while insertion, if no value is supplied then it wil have zeroes, spaces or date/time depending on whether it is numeric, character or date/time. Use it when you do not want to have nulls but at the same time cannot give values all the time you insert this row
Question :What is the difference between SYNONYM and ALIAS?
Answer :SYNONYM: is dropped when the table or tablespace is dropped. Synonym is available only to the creator. ALIAS: is retained even if table or tablespace is dropped. ALIAS can be created even if the table does not exist. It is used mainly in distributed environment to hide the location info from programs. Alias is a global object & is available to all.
Question :What is index cardinality? ?
Answer :The number of distinct values a column or columns contain. 103) What is a synonym ? Synonym is an alternate name for a table or view used mainly to hide the leading qualifier of a table or view.. A synonym is accessible only by the creator.
Question :What is filter factor?
Answer :one divided by the number of distinct values of a column.
Question :What are simple, segmented and partitioned table spaces ?
Answer :Simple Tablespace: Can contain one or more tables Rows from multiple tables can be interleaved on a page under the DBAs control and maintenance Segmented Tablespace: Can contain one or more tables Tablespace is divided into segments of 4 to 64 pages in increments of 4 pages. Each segment is dedicated to single table. A table can occupy multiple segments Partitioned Tablespace: Can contain one table Tablespace is divided into parts and each part is put in a separate VSAM dataset.
Question :What is FREEPAGE and PCTFREE in TABLESPACE creation?
Answer :PCTFREE: percentage of each page to be left free FREEPAGE: Number of pages to be loaded with data between each free page
Question :What is an inner join, and an outer join ?
Answer :Inner Join: combine information from two or more tables by comparing all values that meet the search criteria in the designated column or columns of on e table with all the clause in corresponding columns of the other table or tables. This kind of join which involve a match in both columns are called inner joins. Outer join is one in which you want both matching and non matching rows to be returned. DB2 has no specific operator for outer joins, it can be simulated by combining a join and a correlated sub query with a UNION. Read More!

J2ME interview questions,faqs

J2ME interview questions,faqs
Question :What is WTP ?
Answer :Wireless Transaction Protocol (WTP) is WAP's Transaction protocol that works between the session protocol WSP and security protocol WTLS. WTP chops data packets into lower level datagrams and concatenates received datagrams into useful data. WTP also keeps track of received and sent packets and does re-transmissions and acknowledgment sending when needed.
Question :What is WTLS ?
Answer :Wireless Transport Layer Security protocal (WTLS) does all cryptography oriented features of WAP. WTLS handles encryption/decryption, user authentication and data integrity. WTLS is based on the fixed network Transport Layer Security protocal (TLS), formerly known as Secure Sockets Layer (SSL).
Question :What is WSP ?
Answer :Wireless Session Protocol (WSP) implements session services of WAP. Sessions can be connection-oriented and connectionless and they may be suspended and resumed at will.
Question :What is WMLScript ?
Answer :WMLScript is a subset of the JavaScript scripting language designed as part of the WAP standard to provide a convenient mechanism to access mobile phone's peripheral functions.
Question :What is WML ?
Answer :The Wireless Markup Language (WML) is a simple language used to create applications for small wireless devices like mobile phones. WML is analogous to HTML in the World Wide Web.
Question :What is WMA ?
Answer :The Wireless Messaging API (WMA) is a set of classes for sending and receiving Short Message Service messages. See also SMS.
Question :What is WDP ?
Answer :Wireless Datagram Protocol (WDP) works as the transport layer of WAP. WDP processes datagrams from upper layers to formats required by different physical datapaths, bearers, that may be for example GSM SMS or CDMA Packet Data. WDP is adapted to the bearers available in the device so upper layers don't need to care about the physical level. Read More!

Why isn't the java.sql.DriverManager class being found? -JDBC

This problem can be caused by running a JDBC applet in a browser that supports the JDK 1.0.2, such as Netscape Navigator 3.0. The JDK 1.0.2 does not contain the JDBC API, so the DriverManager class typically isn't found by the Java virtual machine running in the browser.
Here's a solution that doesn't require any additional configuration of your web clients. Remember that classes in the java.* packages cannot be downloaded by most browsers for security reasons. Because of this, many vendors of all-Java JDBC drivers supply versions of the java.sql.* classes that have been renamed to jdbc.sql.*, along with a version of their driver that uses these modified classes. If you import jdbc.sql.* in your applet code instead of java.sql.*, and add the jdbc.sql.* classes provided by your JDBC driver vendor to your applet's codebase, then all of the JDBC classes needed by the applet can be downloaded by the browser at run time, including the DriverManager class.
This solution will allow your applet to work in any client browser that supports the JDK 1.0.2. Your applet will also work in browsers that support the JDK 1.1, although you may want to switch to the JDK 1.1 classes for performance reasons. Also, keep in mind that the solution outlined here is just an example and that other solutions are possible. Read More!

JDBC Interview faqs

JDBC Interview faqs
What is JDBC?
JDBC may stand for Java Database Connectivity. It is also a trade mark. JDBC is a layer of abstraction that allows users to choose between databases. It allows you to change to a different database engine and to write to a single API. JDBC allows you to write database applications in Java without having to concern yourself with the underlying details of a particular database.
What's the JDBC 3.0 API?
The JDBC 3.0 API is the latest update of the JDBC API. It contains many features, including scrollable result sets and the SQL:1999 data types.
JDBC (Java Database Connectivity) is the standard for communication between a Java application and a relational database. The JDBC API is released in two versions; JDBC version 1.22 (released with JDK 1.1.X in package java.sql) and version 2.0 (released with Java platform 2 in packages java.sql and javax.sql). It is a simple and powerful largely database-independent way of extracting and inserting data to or from any database.
Does the JDBC-ODBC Bridge support the new features in the JDBC 3.0 API?
The JDBC-ODBC Bridge provides a limited subset of the JDBC 3.0 API.
Can the JDBC-ODBC Bridge be used with applets?
Use of the JDBC-ODBC bridge from an untrusted applet running in a browser, such as Netscape Navigator, isn't allowed. The JDBC-ODBC bridge doesn't allow untrusted code to call it for security reasons. This is good because it means that an untrusted applet that is downloaded by the browser can't circumvent Java security by calling ODBC. Remember that ODBC is native code, so once ODBC is called the Java programming language can't guarantee that a security violation won't occur. On the other hand, Pure Java JDBC drivers work well with applets. They are fully downloadable and do not require any client-side configuration.
Finally, we would like to note that it is possible to use the JDBC-ODBC bridge with applets that will be run in appletviewer since appletviewer assumes that applets are trusted. In general, it is dangerous to turn applet security off, but it may be appropriate in certain controlled situations, such as for applets that will only be used in a secure intranet environment. Remember to exercise caution if you choose this option, and use an all-Java JDBC driver whenever possible to avoid security problems.
How do I start debugging problems related to the JDBC API?
A good way to find out what JDBC calls are doing is to enable JDBC tracing. The JDBC trace contains a detailed listing of the activity occurring in the system that is related to JDBC operations.
If you use the DriverManager facility to establish your database connection, you use the DriverManager.setLogWriter method to enable tracing of JDBC operations. If you use a DataSource object to get a connection, you use the DataSource.setLogWriter method to enable tracing. (For pooled connections, you use the ConnectionPoolDataSource.setLogWriter method, and for connections that can participate in distributed transactions, you use the XADataSource.setLogWriter method.)
What is new in JDBC 2.0?
With the JDBC 2.0 API, you will be able to do the following:
Scroll forward and backward in a result set or move to a specific row (TYPE_SCROLL_SENSITIVE,previous(), last(), absolute(), relative(), etc.)
Make updates to database tables using methods in the Java programming language instead of using SQL commands.(updateRow(), insertRow(), deleteRow(), etc.)
Send multiple SQL statements to the database as a unit, or batch (addBatch(), executeBatch())
Use the new SQL3 datatypes as column values like Blob, Clob, Array, Struct, Ref.
How can I use the JDBC API to access a desktop database like Microsoft Access over the network?
Most desktop databases currently require a JDBC solution that uses ODBC underneath. This is because the vendors of these database products haven't implemented all-Java JDBC drivers.
The best approach is to use a commercial JDBC driver that supports ODBC and the database you want to use. See the JDBC drivers page for a list of available JDBC drivers.
The JDBC-ODBC bridge from Sun's Java Software does not provide network access to desktop databases by itself. The JDBC-ODBC bridge loads ODBC as a local DLL, and typical ODBC drivers for desktop databases like Access aren't networked. The JDBC-ODBC bridge can be used together with the RMI-JDBC bridge, however, to access a desktop database like Access over the net. This RMI-JDBC-ODBC solution is free.
Are there any ODBC drivers that do not work with the JDBC-ODBC Bridge?
Most ODBC 2.0 drivers should work with the Bridge. Since there is some variation in functionality between ODBC drivers, the functionality of the bridge may be affected. The bridge works with popular PC databases, such as Microsoft Access and FoxPro.
What causes the "No suitable driver" error?
"No suitable driver" is an error that usually occurs during a call to the DriverManager.getConnection method. The cause can be failing to load the appropriate JDBC drivers before calling the getConnection method, or it can be specifying an invalid JDBC URL--one that isn't recognized by your JDBC driver. Your best bet is to check the documentation for your JDBC driver or contact your JDBC driver vendor if you suspect that the URL you are specifying is not being recognized by your JDBC driver.
In addition, when you are using the JDBC-ODBC Bridge, this error can occur if one or more the the shared libraries needed by the Bridge cannot be loaded. If you think this is the cause, check your configuration to be sure that the shared libraries are accessible to the Bridge. Read More!

How ? when resigning a job

How and do's when resigning a job

If you have ever resigned from a job in the past, you know that the second you hand in your letter of resignation that you are being watched; that is if you are not escorted out first. Regardless of how your last hours are spent at the company, be sure to leave with class. You always wanted to be remembered as a professional. Leaving on bad terms could prompt the company to give you a bad reference in the future.
Listed below are some of the things that you should and should not do when resigning from a job.
* Always prepare in advance for your resignation. You need to get all of your personal property out of your office, as well as all of your personal files deleted from your computer. Do this in advance in case you are locked out of your office, or your computer passwords get changed.
* Give the appropriate two weeks notice. Most companies appreciate this gesture, and like to have your resignation in writing. Remember to never jeopardize your new position by sticking around at the old one too long.
* Offer to help in whatever way possible. You may be able to assist in finding someone to replace you, or help train a new employee.
* Ask for letters of recommendation so that you can use them in the future. This does not always work, especially if your employer is angered that you are leaving.
* Never resign from your position until you have another one lined up. It is not easy to take back a resignation.
* Never criticize the company you are leaving. There is no reason to bad mouth anyone at your former company. Just go about your daily routine until your time is up.
* Counteroffers are tricky use your judgment and experience about the management to decide if you can accept or not. Remember that you are leaving the company for a reason in the first place.
* Do not take anything that does not belong to you, not even a pen or pencil! Read More!

Resignation letter sample

Your name/address/contact details
Resignation Letter

Employer name/address
Date:
Dear ...................

Re Intention to leave (insert date)

In accordance with the terms of my written contract, I hereby give you ....... weeks/months notice of my intention to leave. My last day will be ...........

I have decided to improve my skills in .... area and have decided to return to study. I have accepted a college/university place due to start in (insert date). While I realise this may cause some short-term disruption to your organisation, I do feel that the new skills and qualifications could be very beneficial to you and would like you to consider rehiring me after I have completed my course.

It has been a pleasure working for you and my return to study could be very good for both of us in the long term. In the meantime, be assured I will do my best to ensure my duties and responsibilities are covered before I leave.

I would like to thank you for the opportunity to be a part of your team and wish you and your colleagues all the very best for the future.

Yours sincerely
................... (full name)
(Post title) Read More!

Sample Resignation Letter

Sample Resignation Letter

Today's Date
Your address
Address of the company
you are resigning from
Dear Your Manager,

I am writing in order to inform you of my resignation from current position title. My last day of work will be effective resignation date. I signed an employment contract with New Company.

I would like to express my appreciation for your excellent leadership and counsel during the duration of stay at Company Name. I name learned a lot about skills you have learned. These skills will be of great value to me in my career.

I have been fortunate to be employed by Name of Company I have learned that putting the customer first is of vital importance in today's competitive environment. Please acknowledge this letter of resignation and I will do my utmost to finish the outstanding projects and for a smooth hand over.

Sincerely,
Your Name
Your position Read More!