Bala's Blog

Chapter 1: Database Concepts

(Q) What is a Database or Database Management System (DBMS)?
Twist: What is the difference between a file and a database? Can files qualify as a database?

Note: Probably these questions are too basic for experienced SQL SERVER guys. But from a fresher’s point of view, it can be a difference between getting a job and being jobless.

1.Database provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information.

2.Secondly, the information has to be persistent, that means even after the application is closed the information should be persisted.

3.Finally, it should provide an independent way of accessing data and should not be dependent on the application to access the information.

Ok, let me spend a few more sentences on explaining the third aspect. Below is a simple figure of a text file that has personal detail information. The first column of the information is Name, second Address and finally Phone Number. This is a simple text file, which was designed by a programmer for a specific application.
It works fine in the boundary of the application. Now, some years down the line a third party application has to be integrated with this file. In order for the third party application to be integrated properly, it has the following options:

· Use the interface of the original application.

· Understand the complete details of how the text file is organized, example the first column is Name, then Address and finallyPhone Number. After analyzing, write a code which can read the file, parse it etc. Hmm, lot of work, right.

That’s what the main difference is between a simple file and a database; database has an independent way (SQL) of accessing information while simple files do not (That answers my twisted question defined above). File meets the storing, managing and retrieving part of a database, but not the independent way of accessing data.

Note: Many experienced programmers think that the main difference is that file cannot provide multi-user capabilities which a DBMS provides. But if you look at some old COBOL and C programs where files were the only means of storing data, you can see functionalities like locking, multi-user etc. provided very efficiently. So it’s a matter of debate. If some interviewers think of this as a main difference between files and database, accept it… going in to debate means probably losing a job.

(Just a note for fresher’s: Multi-user capabilities mean that at one moment of time more than one user should be able to add, update, view and delete data. All DBMS' provides this as in-built functionalities, but if you are storing information in files, it’s up to the application to write logic to achieve these functionalities).

(Q) What is the Difference between DBMS and RDBMS?
As mentioned before, DBMS provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information. RDBMS also provides what DBMS provides, but above that, it provides relationship integrity. So in short, we can say:

RDBMS = DBMS + REFERENTIAL INTEGRITY

For example, in the above Figure 1.1, every person should have an Address. This is a referential integrity between Name and Address. If we break this referential integrity in DBMS and files, it will not complain, but RDBMS will not allow you to save this data if you have defined the relation integrity between person and addresses. These relations are defined by using “Foreign Keys” in any RDBMS.

Many DBMS companies claimed that their DBMS product was RDBMS compliant, but according to industry rules and regulations, if the DBMS fulfills the twelve CODD rules, it’s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc.) fulfill all the twelve CODD rules and are considered truly as RDBMS.

Note: One of the biggest debates is whether Microsoft Access is an RDBMS? We will be answering this question in later section.

(DB)What are CODD Rules?
Twist: Does SQL SERVER support all the twelve CODD rules?
Note: This question can only be asked on two conditions when the interviewer is expecting you to be at a DBA job or you are complete fresher, yes and not to mention the last one he treats CODD rules as a religion. We will try to answer this question from the perspective of SQL SERVER.

In 1969, Dr. E. F. Codd laid down 12 rules, which a DBMS should adhere to in order to get the logo of a true RDBMS.

Rule 1: Information Rule
"All information in a relational database is represented explicitly at the logical level and in exactly one way - by values in tables."

In SQL SERVER, all data exists in tables and are accessed only by querying the tables.

Rule 2: Guaranteed Access Rule
"Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name."

In flat files, we have to parse and know the exact location of field values. But if a DBMS is truly an RDBMS, you can access the value by specifying the table name, field name, for instance Customers.Fields [‘Customer Name’].

SQL SERVER also satisfies this rule. In ADO.NET we can access field information using table name and field names.

Rule 3: Systematic Treatment of Null Values
"Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.”

In SQL SERVER, if there is no data existing, NULL values are assigned to it. Note NULL values in SQL SERVER do not represent spaces, blanks or a zero value; it is a distinct representation of missing information and thus satisfies rule 3 of CODD.

Rule 4: Dynamic On-line Catalog Based on the Relational Model
"The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data."

The Data Dictionary is held within the RDBMS. Thus, there is no need for off-line volumes to tell you the structure of the database.

Rule 5: Comprehensive Data Sub-language Rule
"A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all the following items:

· Data Definition

· View Definition

· Data Manipulation (Interactive and by program)

· Integrity Constraints

· Authorization

· Transaction boundaries ( Begin, commit and rollback)"

SQL SERVER uses SQL to query and manipulate data, which has a well-defined syntax and is being accepted as an international standard for RDBMS.

Note: According to this rule, CODD has only mentioned that some language should be present to support it, but not necessary that it should be SQL. Before the 80’s, different’s database vendors were providing their own flavor of syntax until in 1980, ANSI-SQL came in to standardize this variation between vendors. As ANSI-SQL is quite limited, every vendor including Microsoft introduced their additional SQL syntax in addition to the support of ANSI-SQL. You can see SQL syntax varying from vendor to vendor.

Rule 6: View-updating Rule
"All views that are theoretically updatable are also updatable by the system."

In SQL SERVER, not only views can be updated by the user, but also by SQL SERVER itself.

Rule 7: High-level Insert, Update and Delete
"The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data, but also to the insertion, update and deletion of data."

SQL SERVER allows you to update views that in turn affect the base tables.

Rule 8: Physical Data Independence
"Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods."

Any application program (C#, VB.NET, VB6, VC++ etc) does not need to be aware of where the SQL SERVER is physically stored or what type of protocol it is using, the database connection string encapsulates everything.

Rule 9: Logical Data Independence
"Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit un-impairment are made to the base tables."

Application programs written in C# or VB.NET do not need to know about any structure changes in SQL SERVER database. Example: adding of new field etc.

Rule 10: Integrity Independence

"Integrity constraints specific to a particular relational database must be definable in the relational data sub-language and storable in the catalog, not in the application programs."

In SQL SERVER, you can specify data types (integer, nvarchar, Boolean etc.) which put in data type checks in SQL SERVER rather than through application programs.

Rule 11: Distribution Independence
"A relational DBMS has distribution independence."

SQL SERVER can spread across more than one physical computer and across several networks; but from application programs, it has not a big difference but just specifying the SQL SERVER name and the computer on which it is located.

Rule 12: Non-subversion Rule
"If a relational system has a low-level (single-record-at-a-time) language, that low level cannot be used to subvert or bypass the integrity Rules and constraints expressed in the higher level relational language (multiple-records-at-a-time)."

In SQL SERVER whatever integrity rules are applied on every record are also applicable when you process a group of records using application program in any other language (example: C#, VB.NET, J# etc.).

Readers can see from the above explanation that SQL SERVER satisfies all the CODD rules, some database gurus consider SQL SERVER as not truly being an RDBMS, but that’s a matter of debate.

(Q) Is Access Database a RDBMS?
Access fulfills all rules of CODD, so from this point of view, yes it’s truly an RDBMS. However, many people can contradict it as a large community of Microsoft professionals think that Access is not an RDBMS.

(Q) What is the Main Difference between ACCESS and SQL SERVER?
As mentioned before, Access fulfills all the CODD rules and behaves as a true RDBMS. But there’s a huge difference from an architecture perspective, due to which many developers prefer to use SQL SERVER as the major database rather than Access. Following is the list of architecture differences between them:

· Access uses file server design and SQL SERVER uses the Client / Server model. This forms the major difference between SQL SERVER and ACCESS.
Note: Just to clarify what is client server and file server I will make a quick description of widely accepted architectures. There are three types of architectures:

o Main frame architecture (This is not related to the above explanation but just mentioned as it can be useful during an interview and also for comparing with other architectures)

o File sharing architecture (Followed by ACCESS)

o Client Server architecture (Followed by SQL SERVER).

In Main Frame architecture, all the processing happens on central host server. User interacts through a dumb terminal that only sends keystrokes and information to the host. All the main processing happens on the central host server. So the advantage in such type of architecture is that you need least configuration clients. But the disadvantage is that you need a robust central host server like Main Frames.

In File sharing architecture, which is followed by Access database, all the data is sent to the client terminal and then processed. For instance, if you want to see customers who stay in India, in File Sharing architecture all customer records will be sent to the client PC regardless whether the customer belongs to India or not. On the client PC customer records from India are sorted/filtered out and displayed, in short all processing logic happens on the client PC. Therefore, in this architecture, the client PC should have heavy configuration and it increases network traffic as a lot of data is sent to the client PC. However, the advantage of this architecture is that your server can be of a low configuration.

In client server architecture, the above limitation of the file server architecture is removed. In client server architecture, you have two entities, client and the database server. File server is now replaced by database server. Database server takes up the load of processing any database related activity and the client does any validation aspect of database. As the work is distributed between the entities it increases scalability and reliability. Second, the network traffic also comes down as compared to file server. For example if you are requesting customers from India, database server will sort/ filter and send only Indian customer details to the client, thus bringing down the network traffic tremendously. SQL SERVER follows the client-server architecture.

· The second issue comes in terms of reliability. In Access, the client directly interacts with the Access file, in case there is some problem in the middle of a transaction, there are chances that an Access file can get corrupt. But in SQL SERVER, the engine sits in between the client and the database, so in case of any problems in the middle of a transaction, it can revert back to its original state.
Note: SQL SERVER maintains a transaction log by which you can revert back to your original state in case of any crash.

· When your application has to cater to a huge load demand, highly transactional environment and high concurrency, then its better to go for SQL SERVER or MSDE.

· But when it comes to cost and support, Access stands better than SQL SERVER. In case of SQL SERVER, you have to pay for per client license, but Access runtime is free.

Summarizing: SQL SERVER gains points in terms of network traffic, reliability and scalability whereas Access gains points in terms of cost factor.

(Q) What is the Difference between MSDE and SQL SERVER 2000?
MSDE is a royalty free, redistributable and cut short version of the giant SQL SERVER database. It is primarily provided as a low cost option for developers who need a database server, which can easily be shipped and installed. It can serve as a good alternative for Microsoft Access database as it overcomes quite a few problems which Access has.

Below is a complete list, which can give you a good idea of the differences:

· Size of database: Microsoft Access and MSDE have a limitation of 2GB while SQL SERVER has 1,048,516 TB1.

· Performance degrades in MSDE 2000 when maximum number of concurrent operations goes above 8 or is equal to 8. It does not mean that you cannot have more than eight concurrent operations but the performance degrades. Eight-connection performance degradation is implemented by using SQL SERVER 2000 workload governor (we will be looking into more detail of how it works). As compared to SQL SERVER 2000, you can have 32,767 concurrent connections.

· MSDE does not provide OLAP and Data warehousing capabilities.

· MSDE does not have support facility for SQL mail.

· MSDE 2000 does not have GUI administrative tool such as enterprise manager, Query analyzer or Profiler. But there are roundabout ways by which you can manage MSDE 2000:

o Old command line utility OSQL.EXE

o VS.NET IDE Server Explorer: Inside VS.NET IDE, you have a functionality which can give you a nice GUI administrative tool to manage IDE.

o SQL SERVER WEB Data administrator installs a web based GUI which you can use to manage your database.
For any details refer here.

· SQL-DMO objects can be used to build your custom UI

· There are many third party tools, which provide administrative capability GUI, which is out of scope of the book as it is only meant for interview questions.

· MSDE does not support Full text search.

Summarizing: There are two major differences: The first is the size limitation (2 GB) of the database and second is the concurrent connections (eight concurrent connections) which are limited by using the workload governor. During an interview, this answer will suffice if the interviewer is really testing your knowledge.

(Q) What is SQL SERVER Express 2005 Edition?
Twist: What is the difference between SQL SERVER Express 2005 and MSDE 2000? Note: Normally comparison is when the product is migrating from one version to other version. When SQL SERVER 7.0 was migrating to SQL 2000, asking differences was one of the favorite questions.

SQL SERVER Express edition is a scaled down version of SQL SERVER 2005 and the next evolution of MSDE.

Listed below are some major differences between them:

· MSDE maximum database size is 2GB while SQL SERVER Express has around 4GB.

· In terms of programming language support MSDE has only TSQL, but SQLSERVER Express has TSQL and .NET. In SQL SERVER Express 2005, you can write your stored procedures using .NET.

· SQL SERVER Express does not have connection limitation, which MSDE had and was controlled through the workload governor.

· There was no XCOPY support for MSDE, SQL SERVER Express has it.

· DTS is not present in SQL SERVER express while MSDE has it.

· SQL SERVER Express has reporting services while MSDE does not.

· SQL SERVER Express has native XML support and MSDE does not.

Note: Native XML support means now in SQL SERVER 2005:

· You can create a field with data type XML.

· You can provide SCHEMA to the SQL SERVER fields with XML data type.

· You can use new XML manipulation techniques like XQUERY also called as XML QUERY.

There is a complete chapter on SQL SERVER XML Support, so till then this will suffice.

Summarizing: The major difference is the database size (2 GB and 4 GB), support of .NET support in stored procedures and native support for XML. This much can convince the interviewer that you are clear about the differences.

(DB) What is SQL Server 2000 Workload Governor?
Workload governor limits the performance of SQL SERVER Desktop engine (MSDE) if the SQL engine receives more load than what is meant for MSDE. MSDE was always meant for trial purpose and non-critical projects. Microsoft always wanted companies to buy their full blow version of SQL SERVER, so in order that they can put limitation on MSDE performance and number of connections, they introduced Workload governor.

Workload governor sits between the client and the database engine and counts the number of connections per database instance. If Workload governor finds that the number of connections exceeds eight connections, it starts stalling the connections and slowing down the database engine.

Note: It does not limit the number of connections but makes the connection request go slow. By default 32,767 connections are allowed both for SQL SERVER and MSDE. But it just makes the database engine go slow above eight connections.

What is the Difference between SQL SERVER 2000 and 2005?
Twist: What is the difference between Yukon and SQL SERVER 2000?
Note: This question will be one of the favorites during SQL SERVER interviews. I have marked the points which should be mentioned by developers as PG and DBA for Database Administrator.

Following are some major differences between the two versions:

· (PG) The most significant change is the .NET integration with SQL SERVER 2005. Stored procedures, user-defined functions, triggers, aggregates, and user-defined types can now be written using your own favorite .NET language (VB.NET, C#, J# etc.). This support was not there in SQL SERVER 2000 where the only language was T-SQL. In SQL 2005, you have support for two languages T-SQL and .NET.

· (PG) SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000. It was a separate installation for SQL Server 2000.

· (PG) SQL SERVER 2005 has introduced two new data types varbinary (max) and XML. If you remember in SQL SERVER 2000, we had image and text data types. Problem with image and text data types is that they assign the same amount of storage irrespective of what the actual data size is. This problem is solved using varbinary (max) which acts depending on amount of data. One more new data type is included XML which enables you to store XML documents and does schema verification. In SQL SERVER 2000, developers used varchar or text data type and all validation had to be done programmatically.

· (PG) SQL SERVER 2005 can now process direct incoming HTTP request without IIS Web server. In addition, stored procedure invocation is enabled using the SOAP protocol.

· (PG) Asynchronous mechanism is introduced using server events. In Server event model the server posts an event to the SQL Broker service, later the client can come and retrieve the status by querying the broker.

· For huge databases, SQLSERVER has provided a cool feature called “Data partitioning”. In data partitioning, you break a single database object such as a table or an index into multiple pieces. But for the client application accessing the single database object, “partitioning” is transparent.

· In SQL SERVER 2000, if you rebuilt clustered indexes even the non-clustered indexes where rebuilt. But in SQL SERVER 2005 building the clustered indexes does not build the non-clustered indexes.

· Bulk data uploading in SQL SERVER 2000 was done using BCP (Bulk copy program’s) format files. Now in SQL SERVER 2005 bulk, data uploading uses XML file format.

· In SQL SERVER 2000 there were maximum 16 instances, but in 2005 you can have up to 50 instances.

· SERVER 2005 has support of “Multiple Active Result Sets” also called as “MARS”. In previous versions of SQL SERVER 2000 in one connection, you could only have one result set. Now in one SQL connection, you can query and have multiple results set.

· In previous versions of SQL SERVER 2000, system catalog was stored in the master database. In SQL SERVER 2005, it’s stored in a resource database which is stored as sys object. You cannot access the sys object directly as in the older version we were accessing the master database.

· This is one of the hardware benefits which SQL SERVER 2005 has over SQSERVER 2000 – support of hyper threading. WINDOWS 2003 supports hyper threading; SQL SERVER 2005 can take advantage of the feature unlike SQL SERVER 2000 which did not support hyper threading.
Note: Hyper threading is a technology developed by INTEL which creates two logical processors on a single physical hardware processor.

· SMO will be used for SQL Server Management.

· AMO (Analysis Management Objects) to manage Analysis Services servers, data sources, cubes, dimensions, measures, and data mining models. You can mapm AMO in old SQL SERVER with DSO (Decision Support Objects).

· Replication is now managed by RMO (Replication Management Objects).
Note: SMO, AMO and RMO are all using .NET Framework.

· SQL SERVER 2005 uses current user execution context to check rights rather than ownership link chain, which was done in SQL SERVER 2000.
Note: There is a question on this later see for execution context questions.

· In previous versions of SQL SERVER the schema and the user name was same, but in current, the schema is separated from the user. Now the user owns schema.
Note: There are questions on this, refer “Schema” later.
Note: Ok below are some GUI changes.

· Query analyzer is now replaced by query editor.

· Business Intelligence development studio will be used to create Business intelligence solutions.

· OSQL and ISQL command line utility is replaced by SQLCMD utility.

· SQL SERVER Enterprise manager is now replaced by SQL SERVER Management studio.

· SERVER Manager which was running in system tray is now replaced by SQL Computer manager.

· Database mirror concept is supported in SQL SERVER 2005, which was not present in SQL SERVER 2000.

· In SQL SERVER 2005 Indexes can be rebuilt online when the database is in actual production. If you look back in SQL SERVER 2000, you cannot do insert, update, and delete operations when you are building indexes.

· (PG) Other than Serializable, Repeatable Read, Read Committed, and Read Uncommitted isolation levels, there is one more new isolation level “Snapshot Isolation level”.
Note: We will see “Snapshot Isolation level” in detail in the coming questions.

Summarizing: The major significant difference between SQL SERVER 2000 and SQL SERVER 2005 is in terms of support of .NET Integration, Snap shot isolation level, Native XML support, handling HTTP request, Web service support and Data partitioning. You do not have to really say all the above points during an interview. A sweet summary and you will rock.

(Q) What are E-R diagrams?
E-R diagram also termed as Entity-Relationship diagram shows the relationship between various tables in the database. Example: TablesCustomer and Customer Addresses have a one to many relationship (i.e. one customer can have multiple addresses) this can be shown using the ER diagram. ER diagrams are drawn during the initial stages of a project to forecast how the database structure will shape up. Below is a screen shot of a sample ER diagram of “Asset Management” which ships free with Access.

(Q) How many Types of Relationship Exist in Database Designing?
There are three major relationship models:

· One-to-one

· One-to-many
In this many records in one table correspond to the one record in another table.
Example: Every one customer can have multiple sales. So there exist one-to-many relationships between customer and sales table.

One Asset can have multiple Maintenance. So Asset entity has one-to-many relationship between them as the ER model shows below.

· Many-to-many
In this, one record in one table corresponds to many rows in another table and also vice-versa.
For instance: In a company, one employee can have many skills like Java , C# etc. and also one skill can belong to many employees.

Given below is a sample of many-to-many relationship. One employee can have knowledge of multiple Technology. So in order to implement this, we have one more table Employee Technology which is linked to the primary key of Employee andTechnology table.

(Q) What is Normalization? What are the Different Types of Normalization?

Note: A regular .NET programmer working on projects often stumbles on this question, which is but obvious. The bad part is sometimes the interviewer can take this as a very basic question to be answered and it can be a turning point for the interview. So let's cram it.

It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.

Benefits of Normalizing your database include:

· Avoiding repetitive entries

· Reducing required storage space

· Preventing the need to restructure existing tables to accommodate new data

· Increased speed and flexibility of queries, sorts, and summaries

Note: During an interview, people expect to answer a maximum of three normal forms and that's what is expected practically. Actually you can normalize database to fifth normal form. But believe this book, answering three normal forms will put you in a decent shape during an interview.

The three normal forms as follows:
First Normal Form
For a table to be in first normal form, data must be broken up into the smallest units possible. In addition to breaking data up into the smallest meaningful values, tables in first normal form should not contain repetitions groups of fields.

In the above example, city1 and city2 are repeating. In order for these tables to be in First normal form, you have to modify the table structure as follows. Also note that the Customer Name is now broken down to first name and last name (First normal form data should be broken down to the smallest unit).

Second Normal Form
The second normal form states that each field in a multiple field primary key table must be directly related to the entire primary key. In other words, each non-key field should be a fact about all the fields in the primary key.

In the above table of customer, city is not linked to any primary field.

That takes our database to a second normal form.
Third Normal Form
A non-key field should not depend on another Non-key field. The field Total is dependent on Unit price and qty.

So now the Total field is removed and is the multiplication of Unit price * Qty.

(Q) What is Denormalization?
Denormalization is the process of putting one fact in numerous places (it is vice-versa of normalization). Only one valid reason exists for denormalizing a relational design - to enhance performance. The sacrifice to performance is that you increase redundancy in a database.

(DB) Can you Explain Fourth Normal Form?

Note: Whenever the interviewer is trying to go above the third normal form, there can be two reasons, ego or to fail you. Three normal forms are really enough, practically anything more than that is an overdose.

In fourth normal form, it should not contain two or more independent multi-valued facts about an entity and it should satisfy “Third Normal form”.

So let us try to see what multi-valued facts are. If there are two or more many-to-many relationship in one entity and they tend to come to one place, it is termed as “multi-valued facts”.

In the above table, you can see that there are two many-to-many relationships between Supplier / Product and “Supplier /Location (or in short multi-valued facts). In order for the above example to satisfy the fourth normal form, both the many-to-many relationships should go in different tables.

(DB) Can you Explain Fifth Normal Form?

Note: UUUHHH if you get this question after joining the company, do ask him if he himself really uses it?

Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy.

Example: Dealers sell Product which can be manufactured by various Companies. Dealers in order to sell the Productshould be registered with the Company. So these three entities have a mutual relationship within them.

The above table shows some sample data. If you observe closely, a single record is created using lot of small information. For instance: JM Associate can sell sweets under the following two conditions:

· JM Associate should be an authorized dealer of Cadbury

· Sweets should be manufactured by Cadbury company

These two smaller bits of information form one record of the above given table. So in order for the above information to be “Fifth Normal Form” all the smaller information should be in three different places. Below is the complete fifth normal form of the database.

(DB) What is the Difference between Fourth and Fifth normal form?
Note: There is a huge similarity between Fourth and Fifth normal form, i.e. they address the problem of “Multi-Valued facts”.

“Fifth normal form” multi-valued facts are interlinked and “Fourth normal form” values are independent. For instance in the above two questionsSupplier/Product and Supplier/Location are not linked. While in fifth form, the Dealer/Product/Companiesare completely linked.

(DB) Have you Heard about Sixth Normal Form?
Note: Arrrrggghhh yes there exists a sixth normal form also. But note guys you can skip this statement. Just in case you want to impress the interviewer...

If you want a relational system in conjunction with time, you use sixth normal form. At this moment SQL Server does not support it directly.

(Q) What is Extent and Page?
Twist: What is the relationship between Extent and Page?
Extent is a basic unit of storage to provide space for tables. Every extent has a number of data pages. As new records are inserted new data, pages are allocated. There are eight data pages in an extent. So as soon as the eight pages are consumed, it allocates a new extent with data pages.

While extent is basic unit storage from a database point of view, page is a unit of allocation within extent.

(DB) What are the Different Sections in Page?
Page has three important sections:

· Page header

· Actual data i.e. Data row

· Row pointers or Row offset

Page header has information like timestamp, next page number, previous page number etc.

Data rows are where your actual row data is stored. For every data row, there is a row offset which points to that data row.

(Q) What are Page Splits?
Pages are contained in extent. Every extent will have around eight data pages. But all the eight data pages are not created at once; they are created depending on data demand. So when a page becomes full it creates a new page, this process is called as “Page Split”.

(Q) In which Files does SQL Server Actually Store Data?
Any SQL Server database is associated with two kinds of files: *.mdf and *.ldf. *.mdf files are actual physical database files where your data is stored finally. *.ldf (LOG) files are actually data, which is recorded from the last time data was committed in the database.

(Q) What is Collation in SQL Server?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.

Note: Different languages will have different sort orders.
Case Sensitivity

If A and a, B and b, etc. are treated in the same way, then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.
Accent Sensitivity

If a and A, o and O are treated in the same way, then it is accent-insensitive. A computer treats a and A differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and A 225. The ASCII value of o is 111 and O is 243.
Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
Width Sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.
(DB) Can We Have a Different Collation for Database and Table?

Yes, you can specify different collation sequence for both the entities differently.

Description

An identity column is one whose value is automatically created by the database engine when a new record is added. This makes sure that each record has a unique value for that field. To visually create an identity column, display the table in Design View. In the top section, specify the name of the column. Set its data type to an integer-based type. Usually, the data type used is int.

CREATE TABLE StoreItems(
    ItemID int IDENTITY(1, 1) NOT NULL, 
    Category nvarchar(50),
    ItemName nvarchar(100) NOT NULL,
    Size nvarchar(20),
    UnitPrice money);
GO

Dotnet Framework Questions


What is .Net Platform?
Microsoft .NET is a software development platform based on virtual machine architecture. Dot Net Platform is:
Language Independent – dot net application can be developed different languages (such as C#, VB, C++, etc.)
Platform Independent – dot net application can be run on any operating system which has .net framework installed.
Hardware Independent – dot net application can run on any hardware configuration
It allows us to build windows based application, web based application, web service, mobile application, etc.

What is .Net Framework?
.Net Framework provides a foundation upon which .net application and xml webservices are built and executed.

Two main Components of .Net Framework
Common Language Runtime
Base Class Library.

Name .Net Compliant Languages
Language which supports .Net Programming. Eg: VB, C#, C++, J#, etc.

What do you mean by .Net Application
Application which is developed using .Net Framework.

Describe .Net Framework Class Library
It consist of thousands of Pre-developed classes that can be used to build application.

What is CLS : Common Language Specification (CLS)
It defines features that all .net compatible language should support.

What is CTS : Common Type System (CTS)
All .net supported languages will produce code that is ultimately based on these type.

What is CLR : Common Language Runtime (CLR)
It provides an “managed” environment in which .net application can execute.
It provides following services :
1. Language Integration
2. Memory Management (Memory Allocation and Garbage Collection)
3. Memory Type Safety (Memory Leaks)
4. Security

What is MSIL : Microsoft Intermediate Language (MSIL)
An intermediate language generated by compiler is called MSIL. All .Net assemblies are represented in MSIL. The main Advantage of using MSIL is it provides equal performance for multiple language programming, as code is compiled to native code.

Example: Performance of application developed in C# is similar to VB.net or any other .Net compliant language that is because of MSIL.

Managed Environment : Managed Code in .net
Code that operates within the CLR is called managed code. Managed code benefits from the services that the CLR offers, including garbage collection, memory management, security, etc.

Unmanaged Environment : Unmanaged Code in .net Code that does not operate within the CLR is called unmanaged code. Unmanaged code does not get benefits offered by CLR including garbage collection, memory management, security, etc.

Example: COM components are unmanaged code.

Advantage provided by Dot Net Framework
Language Independent, that is programmer can concentrate more on problem than to learn new language.

How many types of JIT
Three types.
Pre-JIT (Compiles entire code into native code at one stretch)
Ecno-JIT (Compiles code part by part freeing when required)
Normal JIT (Compiles only that part of code when called and places in cache)

How many classes can a single .NET DLL contain?
UnLimited.

Sample Questions Dotnet

1) What is .Net Platform?

Microsoft .NET is a software development platform based on virtual machine architecture. Dot Net Platform is:

à Language Independent – dot net application can be developed different languages (such as C#, VB, C++, etc.)

à Platform Independent – dot net application can be run on any operating system which has .net framework installed.

à Hardware Independent – dot net application can run on any hardware configuration

It allows us to build windows based application, web based application, web service, mobile application, etc.

2) What is .Net Framework?

.Net Framework provides a foundation upon which .net application and xml webservices are built and executed.

3) Two main Components of .Net Framework

1. Common Language Runtime

2. Base Class Library.

5) .Net Compliant Languages – Language which supports .Net Programming. Eg: VB, C#, C++, J#, etc.

6) .Net Application – Application which is developed using .Net Framework.

7) .Net Framework Class Library – It consist of thousands of Pre-developed classes that can be used to build application.

8) Common Language Specification (CLS) – It defines features that all .net compatible language should support.

9) Common Type System (CTS) – All .net supported languages will produce code that is ultimately based on these type.

10) Common Language Runtime (CLR) – It provides an “managed” environment in which .net application can execute.

It provides following services :

1. Language Integration

2. Memory Management (Memory Allocation and Garbage Collection)

3. Memory Type Safety (Memory Leaks)

4. Security

11) Microsoft Intermediate Language (MSIL)

An intermediate language generated by compiler is called MSIL. All .Net assemblies are represented in MSIL. The main Advantage of using MSIL is it provides equal performance for multiple language programming, as code is compiled to native code.

Eg: Performance of application developed in C# is similar to VB.net or any other .Net compliant language that is because of MSIL.

12) Managed Environment

Code that operates within the CLR is called managed code.Managed code benefits from the services that the CLR offers, including garbage collection, memory management, security, etc.

13) Unmanaged Environment

Code that does not operate within the CLR is called unmanaged code. Unmanaged code does not get benefits offered by CLR including garbage collection, memory management, security, etc. Eg. COM components are unmanaged code.

14) Advantage provided by Dot Net Framework

Language Independent, that is programmer can concentrate more on problem than to learn new language.

What is ADO.net

ADO.net is data access architecture for the Microsoft .NET Framework.

Difference between ADOand ADO.net

1. ADO used connected data usage, while ADO.net used disconnected data environment.

1. Data Source: It can be a database, text file, excel spread sheet or an XML file.

2. Data Provider: A set of libraries that is used to communicate with data source. Eg: SQL data provider for SQL, Oracle data provider for Oracle, OLE DB data provider for access, excel or mysql.

3. SQL Connection: It establishes connection.

4. SQL Command: It allows to manipulate database by executing stored procedure or sql statements.

5. SQL DataReader: It provides a forward-only, read-only, connected recordset.

6. DataSet: dataset is a disconnected, in-memory representation of data. It can contain multiple data table from different database.

7. SQL DataAdapter: It populates dataset from data source. It contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database.

8. DataView: It provides a means to filter and sort data within a data table.

Question 21 Would you lie for the company?

TRAPS: This another question that pits two values against one another, in this case loyalty against integrity.

BEST ANSWER: Try to avoid choosing between two values, giving a positive statement which covers all bases instead.

Example: “I would never do anything to hurt the company..”

If aggressively pressed to choose between two competing values,always choose personal integrity. It is the most prized of all values.

Question 22 Looking back, what would you do differently in your life?

TRAPS: This question is usually asked to uncover any life-influencing mistakes, regrets, disappointments or problems that may continue to affect your personality and performance.

You do not want to give the interviewer anything negative to remember you by, such as some great personal or career disappointment, even long ago, that you wish could have been avoided.

Nor do you wish to give any answer which may hint that your whole heart and soul will not be in your work.

BEST ANSWER: Indicate that you are a happy, fulfilled, optimistic person and that, in general, you wouldn’t change a thing.

Example: “It’s been a good life, rich in learning and experience, and the best it yet to come. Every experience in life is a lesson it its own way. I wouldn’t change a thing.”

Question 23 Could you have done better in your last job?

TRAPS: This is no time for true confessions of major or even minor problems.

BEST ANSWER: Again never be negative.

Example: “I suppose with the benefit of hindsight you can always find things to do better, of course, but off the top of my head, I can’t think of anything of major consequence.”

(If more explanation seems necessary)
Describer a situation that didn’t suffer because of you but from external conditions beyond your control.

For example, describe the disappointment you felt with a test campaign, new product launch, merger, etc., which looked promising at first, but led to underwhelming results. “I wish we could have known at the start what we later found out (about the economy turning, the marketplace changing, etc.), but since we couldn’t, we just had to go for it. And we did learn from it…”

Question 24 Can you work under pressure?

TRAPS: An easy question, but you want to make your answerbelievable.

BEST ANSWER: Absolutely…(then prove it with a vivid example or two of a goal or project accomplished under severe pressure.)

Question 25 What makes you angry?

TRAPS: You don’t want to come across either as a hothead or a wimp.

BEST ANSWER: Give an answer that’s suited to both your personality and the management style of the firm. Here, the homework you’ve done about the company and its style can help in your choice of words.

Examples: If you are a reserved person and/or the corporate culture is coolly professional:

“I’m an even-tempered and positive person by nature, and I believe this helps me a great deal in keeping my department running smoothly, harmoniously and with a genuine esprit de corps. I believe in communicating clearly what’s expected, getting people’s commitment to those goals, and then following up continuously to check progress.”

“If anyone or anything is going off track, I want to know about it early.If, after that kind of open communication and follow up, someone isn’t getting the job done, I’ll want to know why. If there’s no good reason, then I’ll get impatient and angry…and take appropriate steps from there. But if you hire good people, motivate them to strive for excellence and then follow up constantly, it almost never gets to that state.”

If you are feisty by nature and/or the position calls for a tough straw boss.

“You know what makes me angry? People who (the fill in the blanks with the most objectionable traits for this type of position)…people who don’t pull their own weight, who are negative, people who lie…etc.”

Question 26 Why aren’t you earning more money at this stage of your career?

TRAPS: You don’t want to give the impression that money is not important to you, yet you want to explain why your salary may be a little below industry standards.

BEST ANSWER: You like to make money, but other factors are even more important.

Example: “Making money is very important to me, and one reason I’m here is because I’m looking to make more. Throughout my career, what’s been even more important to me is doing work I really like to do at the kind of company I like and respect.

(Then be prepared to be specific about what your ideal position and company would be like, matching them as closely as possible to the opportunity at hand.

Question 27 Who has inspired you in your life and why?

TRAPS: The two traps here are unpreparedness and irrelevance. If you grope for an answer, it seems you’ve never been inspired. If you ramble about your high school basketball coach, you’ve wasted an opportunity to present qualities of great value to the company.

BEST ANSWER: Have a few heroes in mind, from your mental “Board of Directors” – Leaders in your industry, from history or anyone else who has been your mentor.

Be prepared to give examples of how their words, actions or teachings have helped inspire your achievements. As always, prepare an answer which highlights qualities that would be highly valuable in the position you are seeking.

Question 28 What was the toughest decision you ever had to make?

TRAPS: Giving an unprepared or irrelevant answer.

BEST ANSWER: Be prepared with a good example, explaining why the decision was difficult…the process you followed in reaching it…the courageous or effective way you carried it out…and the beneficial results.

Question 29 Tell me about the most boring job you’ve ever had.

TRAPS: You give a very memorable description of a very boring job.Result? You become associated with this boring job in the interviewer’s mind.

BEST ANSWER: You have never allowed yourself to grow bored with a job and you can’t understand it when others let themselves fall into that rut.

Example: “Perhaps I’ve been fortunate, but that I’ve never found myself bored with any job I have ever held. I’ve always enjoyed hard work. As with actors who feel there are no small parts, I also believe that in every company or department there are exciting challenges and intriguing problems crying out for energetic and enthusiastic solutions. If you’re bored, it’s probably because you’re not challenging yourself to tackle those problems right under your nose.”

Question 30 Have you been absent from work more than a few days in any previous position?

TRAPS: If you’ve had a problem, you can’t lie. You could easily be found out. Yet admitting an attendance problem could raise many flags.

BEST ANSWER: If you have had no problem, emphasize your excellent and consistent attendance record throughout your career.

Also describe how important you believe such consistent attendance is for a key executive…why it’s up to you to set an example of dedication…and why there’s just no substitute for being there with your people to keep the operation running smoothly, answer questions and handle problems and crises as they arise.

If you do have a past attendance problem, you want to minimize it, making it clear that it was an exceptional circumstance and that it’s cause has been corrected.

To do this, give the same answer as above but preface it with something like, “Other that being out last year (or whenever) because of (your reason, which is now in the past), I have never had a problem and have enjoyed an excellent attendance record throughout my career. Furthermore, I believe, consistent attendance is important because…” (Pick up the rest of the answer as outlined above.).


Question 31 What changes would you make if you came on board?

TRAPS: Watch out! This question can derail your candidacy faster than a bomb on the tracks – and just as you are about to be hired.

Reason: No matter how bright you are, you cannot know the right actions to take in a position before you settle in and get to know the operation’s strengths, weaknesses key people, financial condition, methods of operation, etc. If you lunge at this temptingly baited question, you will probably be seen as someone who shoots from the hip.

Moreover, no matter how comfortable you may feel with your interviewer, you are still an outsider. No one, including your interviewer, likes to think that a know-it-all outsider is going to come in, turn the place upside down and with sweeping, grand gestures, promptly demonstrate what jerks everybody’s been for years.

BEST ANSWER: You, of course, will want to take a good hard look at everything the company is doing before making any recommendations.

Example: “Well, I wouldn’t be a very good doctor if I gave my diagnosis before the examination. Should you hire me, as I hope you will, I’d want to take a good hard look at everything you’re doing and understand why it’s being done that way. I’d like to have in-depth meetings with you and the other key people to get a deeper grasp of what you feel you’re doing right and what could be improved.

“From what you’ve told me so far, the areas of greatest concern to you are…” (name them. Then do two things. First, ask if these are in fact his major concerns. If so then reaffirm how your experience in meeting similar needs elsewhere might prove very helpful).

Question 32 I’m concerned that you don’t have as much experience as we’d like in…

TRAPS: This could be a make-or-break question. The interviewermostly likes what he sees, but has doubts over one key area. If you can assure him on this point, the job may be yours.

BEST ANSWER: This question is related to “The Fatal Flaw” (Question 18), but here the concern is not that you are totally missing some qualifications, such as CPA certification, but rather that your experience is light in one area.

Before going into any interview, try to identify the weakest aspects of your candidacy from this company’s point of view. Then prepare the best answer you possible can to shore up your defenses.

To get past this question with flying colors, you are going to rely on your master strategy of uncovering the employer’s greatest wants and needs and then matching them with your strengths. Since you already know how to do this from Question 1, you are in a much stronger position.

More specifically, when the interviewer poses as objection like this, you should…

1. Agree on the importance of this qualification.

2. Explain that your strength may be indeed be greater than your resume indicates because…

3. When this strength is added to your other strengths, it’s really your combination of qualifications that’s most important.

Then review the areas of your greatest strengths that match up most favorably with the company’s most urgently-felt wants and needs.

This is powerful way to handle this question for two reasons. First, you’re giving your interviewer more ammunition in the area of his concern. But more importantly, you’re shifting his focus away from this one, isolated area and putting it on the unique combination of strengths you offer, strengths which tie in perfectly with his greatest wants.


Question 33 How do you feel about working nights and weekends?

TRAPS: Blurt out “no way, Jose” and you can kiss the job offer goodbye. But what if you have a family and want to work a reasonably normal schedule? Is there a way to get both the job and the schedule you want?

BEST ANSWER: First, if you’re a confirmed workaholic, this question is a softball lob. Whack it out of the park on the first swing by saying this kind of schedule is just your style. Add that your family understands it. Indeed, they’re happy for you, as they know you get your greatest satisfaction from your work.

If however, you prefer a more balanced lifestyle, answer this question with another: “What’s the norm for your best people here?”

If the hours still sound unrealistic for you, ask, “Do you have any top people who perform exceptionally for you, but who also have families and like to get home in time to see them at night?” Chances are this company does, and this associates you with this other “top-performers-who-leave-not-later-than-six” group.

Depending on the answer, be honest about how you would fit into the picture. If all those extra hours make you uncomfortable, say so, but phrase your response positively.

Example: “I love my work and do it exceptionally well. I think the results speak for themselves, especially in …(mention your two or three qualifications of greater interest to the employer. Remember, this is what he wants most, not a workaholic with weak credentials).Not only would I bring these qualities, but I’ve built my whole career on working not just hard, but smart. I think you’ll find me one of the most productive people here.

I do have a family who likes to see me after work and on weekends.They add balance and richness to my life, which in turn helps me be happy and productive at work. If I could handle some of the extra work at home in the evenings or on weekends, that would be ideal.You’d be getting a person of exceptional productivity who meets your needs with strong credentials. And I’d be able to handle some of the heavy workload at home where I can be under the same roof as my family. Everybody would win.”

Question 34 Are you willing to relocate or travel?

TRAPS: Answer with a flat “no” and you may slam the door shut on this opportunity. But what if you’d really prefer not to relocate or travel, yet wouldn’t want to lose the job offer over it?

BEST ANSWER: First find out where you may have to relocate and how much travel may be involved. Then respond to the question.

If there’s no problem, say so enthusiastically.

If you do have a reservation, there are two schools of thought on how to handle it.

One advises you to keep your options open and your reservations to yourself in the early going, by saying, “no problem”. You strategy here is to get the best offer you can, then make a judgment whether it’s worth it to you to relocate or travel.

Also, by the time the offer comes through, you may have other offers and can make a more informed decision. Why kill of this opportunity before it has chance to blossom into something really special? And if you’re a little more desperate three months from now, you might wish you hadn’t slammed the door on relocating or traveling.

The second way to handle this question is to voice a reservation, but assert that you’d be open to relocating (or traveling) for the right opportunity.

The answering strategy you choose depends on how eager you are for the job. If you want to take no chances, choose the first approach.

If you want to play a little harder-to-get in hopes of generating a more enticing offer, choose the second.

Question 35 Do you have the stomach to fire people? Have you had experience firing many people?

TRAPS: This “innocent” question could be a trap door which sends you down a chute and lands you in a heap of dust outside the front door. Why? Because its real intent is not just to see if you’ve got the stomach to fire, but also to uncover poor judgment in hiring which has caused you to fire so many. Also, if you fire so often, you could be a tyrant.

So don’t rise to the bait by boasting how many you’ve fired, unless you’ve prepared to explain why it was beyond your control, and not the result of your poor hiring procedures or foul temperament.

BEST ANSWER: Describe the rational and sensible management process you follow in both hiring and firing.

Example: “My whole management approach is to hire the best people I can find, train them thoroughly and well, get them excited and proud to be part of our team, and then work with them to achieve our goals together. If you do all of that right, especially hiring the right people, I’ve found you don’t have to fire very often.

“So with me, firing is a last resort. But when it’s got to be done, it’s got to be done, and the faster and cleaner, the better. A poor employee can wreak terrible damage in undermining the morale of an entire team of good people. When there’s no other way, I’ve found it’s better for all concerned to act decisively in getting rid of offenders who won’t change their ways.”

Question 36 Why have you had so many jobs?

TRAPS: Your interviewer fears you may leave this position quickly, as you have others. He’s concerned you may be unstable, or a “problem person” who can’t get along with others.

BEST ANSWER: First, before you even get to the interview stage, you should try to minimize your image as job hopper. If there are several entries on your resume of less than one year, consider eliminating the less important ones. Perhaps you can specify the time you spent at previous positions in rounded years not in months and years.

Example: Instead of showing three positions this way:

6/1982 – 3/1983, Position A;
4/1983 – 12/1983, Position B;
1/1984 – 8/1987, Position C;

…it would be better to show simply:

1982 – 1983, Position A;
1984 – 1987 Position C.

In other words, you would drop Position B altogether. Notice what a difference this makes in reducing your image as a job hopper.

Once in front of the interviewer and this question comes up, you must try to reassure him. Describe each position as part of an overall pattern of growth and career destination.

Be careful not to blame other people for your frequent changes. But you can and should attribute certain changes to conditions beyond your control.

Example: Thanks to an upcoming merger, you wanted to avoid an ensuing bloodbath, so you made a good, upward career move before your department came under the axe of the new owners.

If possible, also show that your job changes were more frequent in your younger days, while you were establishing yourself, rounding out your skills and looking for the right career path. At this stage in your career, you’re certainly much more interested in the best long-term opportunity.

You might also cite the job(s) where you stayed the longest and describe that this type of situation is what you’re looking for now.

Question 37 What do you see as the proper role/mission of…
…a good (job title you’re seeking);
…a good manager;
…an executive in serving the community;
…a leading company in our industry; etc.

TRAPS: These and other “proper role” questions are designed to test your understanding of your place in the bigger picture of your department, company, community and profession….as well as the proper role each of these entities should play in its bigger picture.

The question is most frequently asked by the most thoughtfulindividuals and companies…or by those concerned that you’re coming from a place with a radically different corporate culture (such as from a big government bureaucracy to an aggressive small company).

The most frequent mistake executives make in answering is simply not being prepared (seeming as if they’ve never giving any of this a though.)…or in phrasing an answer best suited to their priororganization’s culture instead of the hiring company’s.

BEST ANSWER: Think of the most essential ingredients of success for each category above – your job title, your role as manager, your firm’s role, etc.

Identify at least three but no more than six qualities you feel are most important to success in each role. Then commit your response to memory.

Here, again, the more information you’ve already drawn out about the greatest wants and needs of the interviewer, and the more homework you’ve done to identify the culture of the firm, the more on-target your answer will be.

Question 38 What would you say to your boss if he’s crazy about an idea, but you think it stinks?

TRAPS: This is another question that pits two values, in this case loyalty and honesty, against one another.

BEST ANSWER: Remember the rule stated earlier: In any conflict between values, always choose integrity.

Example: I believe that when evaluating anything, it’s important to emphasize the positive. What do I like about this idea?”

“Then, if you have reservations, I certainly want to point them out, as specifically, objectively and factually as I can.”

“After all, the most important thing I owe my boss is honesty. If he can’t count on me for that, then everything else I may do or say could be questionable in his eyes.”

“But I also want to express my thoughts in a constructive way. So my goal in this case would be to see if my boss and I could make his idea even stronger and more appealing, so that it effectively overcomes any initial reservation I or others may have about it.”

“Of course, if he overrules me and says, ‘no, let’s do it my way,’ then I owe him my full and enthusiastic support to make it work as best it can.”

Question 39 How could you have improved your career progress?

TRAPS: This is another variation on the question, “If you could, how would you live your life over?” Remember, you’re not going to fall for any such invitations to rewrite person history. You can’t win if you do.

BEST ANSWER: You’re generally quite happy with your career progress. Maybe, if you had known something earlier in life (impossible to know at the time, such as the booming growth in a branch in your industry…or the corporate downsizing that would phase out your last job), you might have moved in a certain direction sooner.

But all things considered, you take responsibility for where you are, how you’ve gotten there, where you are going…and you harbor no regrets.

Question 40 What would you do if a fellow executive on your own corporate level wasn’t pulling his/her weight…and this was hurting your department?

TRAPS: This question and other hypothetical ones test your sense of human relations and how you might handle office politics.

BEST ANSWER: Try to gauge the political style of the firm and be guided accordingly. In general, fall back on universal principles of effective human relations – which in the end, embody the way you would like to be treated in a similar circumstance.

Example: “Good human relations would call for me to go directly to the person and explain the situation, to try to enlist his help in a constructive, positive solution. If I sensed resistance, I would be as persuasive as I know how to explain the benefits we can all gain from working together, and the problems we, the company and our customers will experience if we don’t.”

POSSIBLE FOLLOW-UP QUESTION: And what would you do if he still did not change his ways?

ANSWER: “One thing I wouldn’t do is let the problem slide, because it would only get worse and overlooking it would set a bad precedent.I would try again and again and again, in whatever way I could, to solve the problem, involving wider and wider circles of people, both above and below the offending executive and including my own boss if necessary, so that everyone involved can see the rewards for teamwork and the drawbacks of non-cooperation.”

“I might add that I’ve never yet come across a situation that couldn’t be resolved by harnessing others in a determined, constructive effort.”


Question 41 You’ve been with your firm a long time. Won’t it be hard switching to a new company?

TRAPS: Your interviewer is worried that this old dog will find it hard to learn new tricks.

BEST ANSWER: To overcome this objection, you must point to the many ways you have grown and adapted to changing conditions at your present firm. It has not been a static situation. Highlight the different responsibilities you’ve held, the wide array of new situations you’ve faced and conquered.

As a result, you’ve learned to adapt quickly to whatever is thrown at you, and you thrive on the stimulation of new challenges.

To further assure the interviewer, describe the similarities between the new position and your prior one. Explain that you should be quite comfortable working there, since their needs and your skills make a perfect match.

Question 42 May I contact your present employer for a reference?

TRAPS: If you’re trying to keep your job search private, this is the last thing you want. But if you don’t cooperate, won’t you seem as if you’re trying to hide something?

BEST ANSWER: Express your concern that you’d like to keep your job search private, but that in time, it will be perfectly okay.

Example: “My present employer is not aware of my job search and, for obvious reasons; I’d prefer to keep it that way. I’d be most appreciative if we kept our discussion confidential right now. Of course, when we both agree the time is right, then by all means you should contact them. I’m very proud of my record there.

Question 43 Give me an example of your creativity (analytical skill…managing ability, etc.)

TRAPS: The worst offense here is simply being unprepared. Your hesitation may seem as if you’re having a hard time remembering the last time you were creative, analytical, etc.

BEST ANSWER: Remember from Question 2 that you should commit to memory a list of your greatest and most recent achievements, ever ready on the tip of your tongue.

If you have such a list, it’s easy to present any of your achievements in light of the quality the interviewer is asking about. For example, the smashing success you orchestrated at last year’s trade show could be used as an example of creativity, or analytical ability, or your ability to manage.

Question 44 Where could you use some improvement?

TRAPS: Another tricky way to get you to admit weaknesses. Don’t fall for it.

BEST ANSWER: Keep this answer, like all your answers, positive. A good way to answer this question is to identify a cutting-edge branch of your profession (one that’s not essential to your employer’s needs) as an area you’re very excited about and want to explore more fully over the next six months.

Question 45 What do you worry about?

TRAPS: Admit to worrying and you could sound like a loser. Saying you never worry doesn’t sound credible.

BEST ANSWER: Redefine the word ‘worry’ so that it does not reflect negatively on you.

Example: “I wouldn’t call it worry, but I am a strongly goal-oriented person. So I keep turning over in my mind anything that seems to be keeping me from achieving those goals, until I find a solution. That’s part of my tenacity, I suppose.”

Question 46 How many hours a week do you normally work?

TRAPS: You don’t want to give a specific number. Make it to low, and you may not measure up. Too high, and you’ll forever feel guilty about sneaking out the door at 5:15.

BEST ANSWER: If you are in fact a workaholic and you sense this company would like that: Say you are a confirmed workaholic, that you often work nights and weekends. Your family accepts this because it makes you fulfilled.

If you are not a workaholic: Say you have always worked hard and put in long hours. It goes with the territory. It one sense, it’s hard to keep track of the hours because your work is a labor of love, you enjoy nothing more than solving problems. So you’re almost alwaysthinking about your work, including times when you’re home, while shaving in the morning, while commuting, etc.

Question 47 What’s the most difficult part of being a (job title)?

TRAPS: Unless you phrase your answer properly, your interviewer may conclude that whatever you identify as “difficult” is where you are weak.

BEST ANSWER: First, redefine “difficult” to be “challenging” which is more positive. Then, identify an area everyone in your profession considers challenging and in which you excel. Describe the process you follow that enables you to get splendid results…and be specific about those results.

Example: “I think every sales manager finds it challenging to motivate the troops in a recession. But that’s probably the strongest test of a top sales manager. I feel this is one area where I excel.”

“When I see the first sign that sales may slip or that sales force motivation is flagging because of a downturn in the economy, here’s the plan I put into action immediately…” (followed by a description of each step in the process…and most importantly, the exceptional results you’ve achieved.).

Question 48 The “Hypothetical Problem”

TRAPS: Sometimes an interviewer will describe a difficult situation and ask, “How would you handle this?” Since it is virtually impossible to have all the facts in front of you from such a short presentation, don’t fall into the trap of trying to solve this problem and giving your verdict on the spot. It will make your decision-making process seem woefully inadequate.

BEST ANSWER: Instead, describe the rational, methodical process you would follow in analyzing this problem, who you would consult with, generating possible solutions, choosing the best course of action, and monitoring the results.

Remember, in all such, “What would you do?” questions, always describe your process or working methods, and you’ll never go wrong.

Question 49 What was the toughest challenge you’ve ever faced?

TRAPS: Being unprepared or citing an example from so early in your life that it doesn’t score many points for you at this stage of your career.

BEST ANSWER: This is an easy question if you’re prepared. Have a recent example ready that demonstrates either:

1. A quality most important to the job at hand; or

2. A quality that is always in demand, such as leadership, initiative, managerial skill, persuasiveness, courage, persistence, intelligence, etc.

Question 50 Have you consider starting your own business?

TRAPS: If you say “yes” and elaborate enthusiastically, you could be perceived as a loose cannon in a larger company, too entrepreneurial to make a good team player…or someone who had to settle for the corporate life because you couldn’t make a go of your own business.

Also too much enthusiasm in answering “yes” could rouse the paranoia of a small company indicating that you may plan to go out on your own soon, perhaps taking some key accounts or trade secrets with you.

On the other hand, if you answer “no, never” you could be perceived as a security-minded drone who never dreamed a big dream.

BEST ANSWER: Again it’s best to:

1. Gauge this company’s corporate culture before answering and…

2. Be honest (which doesn’t mean you have to vividly share your fantasy of the franchise or bed-and-breakfast you someday plan to open).

In general, if the corporate culture is that of a large, formal, military-style structure, minimize any indication that you’d love to have your own business. You might say, “Oh, I may have given it a thought once or twice, but my whole career has been in larger organizations.That’s where I have excelled and where I want to be.”

If the corporate culture is closer to the free-wheeling, everybody’s-a-deal-maker variety, then emphasize that in a firm like this, you can virtually get the best of all worlds, the excitement of seeing your own ideas and plans take shape…combined with the resources and stability of a well-established organization. Sounds like the perfect environment to you.

In any case, no matter what the corporate culture, be sure to indicate that any desires about running your own show are part of your past, not your present or future.

The last thing you want to project is an image of either a dreamer who failed and is now settling for the corporate cocoon…or the restless maverick who will fly out the door with key accounts, contacts and trade secrets under his arms just as soon as his bankroll has gotten rebuilt.

Always remember: Match what you want with what the position offers. The more information you’ve uncovered about the position, the more believable you can make your case.

Question 51 What are your goals?

TRAPS: Not having any…or having only vague generalities, nothighly specific goals.

BEST ANSWER: Many executives in a position to hire you are strong believers in goal-setting. (It’s one of the reason they’ve achieved so much). They like to hire in kind.

If you’re vague about your career and personal goals, it could be a big turnoff to may people you will encounter in your job search.

Be ready to discuss your goals for each major area of your life:career, personal development and learning, family, physical (health), community service and (if your interviewer is clearly a religious person) you could briefly and generally allude to your spiritual goals (showing you are a well-rounded individual with your values in the right order).

Be prepared to describe each goal in terms of specific milestones you wish to accomplish along the way, time periods you’re allotting for accomplishment, why the goal is important to you, and the specific steps you’re taking to bring it about. But do this concisely, as you never want to talk more than two minutes straight before letting your interviewer back into the conversation.

Question 52 What do you for when you hire people?

TRAPS: Being unprepared for the question.

BEST ANSWER: Speak your own thoughts here, but for the best answer weave them around the three most important qualifications for any position.

1. Can the person do the work (qualifications)?

2. Will the person do the work (motivation)?

3. Will the person fit in (“our kind of team player”)?

Question 53 Sell me this stapler…(this pencil…this clock…or some other object on interviewer’s desk).

TRAPS: Some interviewers, especially business owners and hard-changing executives in marketing-driven companies, feel that good salesmanship is essential for any key position and ask for an instant demonstration of your skill. Be ready.

BEST ANSWER: Of course, you already know the most important secret of all great salesmanship – “find out what people want, then show them how to get it.”

If your interviewer picks up his stapler and asks, “sell this to me,” you are going to demonstrate this proven master principle. Here’s how:

“Well, a good salesman must know both his product and his prospect before he sells anything. If I were selling this, I’d first get to know everything I could about it, all its features and benefits.”

“Then, if my goal were to sell it you, I would do some research on how you might use a fine stapler like this. The best way to do that is by asking some questions. May I ask you a few questions?”

Then ask a few questions such as, “Just out of curiosity, if you didn’t already have a stapler like this, why would you want one? And in addition to that? Any other reason? Anything else?”

“And would you want such a stapler to be reliable?...Hold a good supply of staples?” (Ask more questions that point to the features this stapler has.)

Once you’ve asked these questions, make your presentation citing all the features and benefits of this stapler and why it’s exactly what the interviewer just told you he’s looking for.

Then close with, “Just out of curiosity, what would you consider a reasonable price for a quality stapler like this…a stapler you could have right now and would (then repeat all the problems the stapler would solve for him)? Whatever he says, (unless it’s zero), say, “Okay, we’ve got a deal.”

NOTE: If your interviewer tests you by fighting every step of the way, denying that he even wants such an item, don’t fight him. Take the product away from him by saying, “Mr. Prospect, I’m delighted you’ve told me right upfront that there’s no way you’d ever want this stapler.As you well know, the first rule of the most productive salespeople in any field is to meet the needs of people who really need and want our products, and it just wastes everyone’s time if we try to force it on those who don’t. And I certainly wouldn’t want to waste your time. But we sell many items. Is there any product on this desk you would very much like to own…just one item?” When he points something out, repeat the process above. If he knows anything about selling, he may give you a standing ovation.

Question 54 “The Salary Question” – How much money do you want?
TRAPS: May also be phrases as, “What salary are you worth?”…or,“How much are you making now?” This is your most important negotiation. Handle it wrong and you can blow the job offer or go to work at far less than you might have gotten.

BEST ANSWER: For maximum salary negotiating power, remember these five guidelines:

1. Never bring up salary. Let the interviewer do it first. Good salespeople sell their products thoroughly before talking price.So should you. Make the interviewer want you first, and your bargaining position will be much stronger.

2. If your interviewer raises the salary question too early, before you’ve had a chance to create desire for your qualifications,postpone the question, saying something like, “Money is important to me, but is not my main concern. Opportunity and growth are far more important. What I’d rather do, if you don’t mind, is explore if I’m right for the position, and then talk about money. Would that be okay?”

3. The #1 rule of any negotiation is: the side with more information wins. After you’ve done a thorough job of selling the interviewer and it’s time to talk salary, the secret is to get the employer talking about what he’s willing to pay before you reveal what you’re willing to accept. So, when asked about salary, respond by asking, “I’m sure the company has already established a salary range for this position. Could you tell me what that is?” Or, “I want an income commensurate with my ability and qualifications. I trust you’ll be fair with me. What does the position pay?” Or, more simply, “What does this position pay?”

4. Know beforehand what you’d accept. To know what’s reasonable, research the job market and this position for any relevant salary information. Remember that most executives look for a 20-25%$ pay boost when they switch jobs. If you’re grossly underpaid, you may want more.

5. Never lie about what you currently make, but feel free to include the estimated cost of all your fringes, which could well tack on 25-50% more to your present “cash-only” salary.

Question 55 The Illegal Question

TRAPS: Illegal questions include any regarding your age…number and ages of your children or other dependents…marital status…maiden name…religion…political affiliation…ancestry…national origin…birthplace…naturalization of your parents, spouse or children…diseases…disabilities…clubs…or spouse’s occupation…unless any of the above are directly related to your performance of the job. You can’t even be asked about arrests, though you can be asked about convictions.

BEST ANSWER: Under the ever-present threat of lawsuits, most interviewers are well aware of these taboos. Yet you may encounter, usually on a second or third interview, a senior executive who doesn’t interview much and forgets he can’t ask such questions.

You can handle an illegal question in several ways. First, you can assert your legal right not to answer. But this will frighten or embarrass your interviewer and destroy any rapport you had.

Second, you could swallow your concerns over privacy and answer the question straight forwardly if you feel the answer could help you.For example, your interviewer, a devout Baptist, recognizes you from church and mentions it. Here, you could gain by talking about your church.

Third, if you don’t want your privacy invaded, you can diplomatically answer the concern behind the question without answering the question itself.

Example: If you are over 50 and are asked, “How old are you?” you can answer with a friendly, smiling question of your own on whether there’s a concern that your age my affect your performance. Follow this up by reassuring the interviewer that there’s nothing in this job you can’t do and, in fact, your age and experience are the most important advantages you offer the employer for the following reasons…

Another example: If asked, “Do you plan to have children?” you could answer, “I am wholeheartedly dedicated to my career“, perhaps adding, “I have no plans regarding children.” (You needn’t fear you’ve pledged eternal childlessness. You have every right to change your plans later. Get the job first and then enjoy all your options.)

Most importantly, remember that illegal questions arise from fear that you won’t perform well. The best answer of all is to get the job and perform brilliantly. All concerns and fears will then varnish, replaced by respect and appreciation for your work.

Question 56 The “Secret” Illegal Question

TRAPS: Much more frequent than the Illegal question (see Question 55) is the secret illegal question. It’s secret because it’s asked only in the interviewer’s mind. Since it’s not even expressed to you, you have no way to respond to it, and it can there be most damaging.

Example: You’re physically challenged, or a single mother returning to your professional career, or over 50, or a member of an ethnic minority, or fit any of a dozen other categories that do not strictly conform to the majority in a given company.

Your interviewer wonders, “Is this person really able to handle the job?”…”Is he or she a ‘good fit’ at a place like ours?”…”Will the chemistry ever be right with someone like this?” But the interviewer never raises such questions because they’re illegal. So what can you do?

BEST ANSWER: Remember that just because the interviewer doesn’t ask an illegal question doesn’t mean he doesn’t have it. More than likely, he is going to come up with his own answer. So you might as well help him out.

How? Well, you obviously can’t respond to an illegal question if he hasn’t even asked. This may well offend him. And there’s always the chance he wasn’t even concerned about the issue until you brought it up, and only then begins to wonder.

So you can’t address “secret” illegal questions head-on. But what you can do is make sure there’s enough counterbalancinginformation to more than reassure him that there’s no problem in the area he may be doubtful about.

For example, let’s say you’re a sales rep who had polio as a child and you need a cane to walk. You know your condition has never impeded your performance, yet you’re concerned that your interviewer may secretly be wondering about your stamina or ability to travel. Well, make sure that you hit these abilities very hard, leaving no doubt about your capacity to handle them well.

So, too, if you’re in any different from what passes for “normal”. Make sure, without in any way seeming defensive about yourself that you mention strengths, accomplishments, preferences and affiliations that strongly counterbalance any unspoken concern your interviewer may have.

Question 57 What was the toughest part of your last job?

TRAPS: This is slightly different from the question raised earlier,“What’s the most difficult part of being a (job title…)” because this asks what you personally have found most difficult in your last position. This question is more difficult to redefine into something positive. Your interviewer will assume that whatever you found toughest may give you a problem in your new position.

BEST ANSWER: State that there was nothing in your prior position that you found overly difficult, and let your answer go at that. If pressed to expand your answer, you could describe the aspects of the position you enjoyed more than others, making sure that you express maximum enjoyment for those tasks most important to the open position, and you enjoyed least those tasks that are unimportant to the position at hand.

Question 58 How do you define success…and how do you measure up to your own definition?

TRAPS: Seems like an obvious enough question. Yet many executives, unprepared for it, fumble the ball.

BEST ANSWER: Give a well-accepted definition of success that leads right into your own stellar collection of achievements.

Example: “The best definition I’ve come across is that success is the progressive realization of a worthy goal.”

“As to how I would measure up to that definition, I would consider myself both successful and fortunate…”(Then summarize your career goals and how your achievements have indeed represented a progressive path toward realization of your goals.)

Question 59 “The Opinion Question” – What do you think about …Abortion…The President…The Death Penalty…(or any other controversial subject)?

TRAPS: Obviously, these and other “opinion” questions should never be asked. Sometimes they come up over a combination dinner/interview when the interviewer has had a drink or two, is feeling relaxed, and is spouting off about something that bugged him in today’s news. If you give your opinion and it’s the opposite of his, you won’t change his opinions, but you could easily lose the job offer.

BEST ANSWER: In all of these instances, just remember the tale about student and the wise old rabbi. The scene is a seminary, where an overly serious student is pressing the rabbi to answer the ultimate questions of suffering, life and death. But no matter how hard he presses, the wise old rabbi will only answer each difficult question with a question of his own.

In exasperation, the seminary student demands, “Why, rabbi, do you always answer a question with another question?” To which the rabbi responds, “And why not?”

If you are ever uncomfortable with any question, asking a question in return is the greatest escape hatch ever invented. It throws the onus back on the other person, sidetracks the discussion from going into an area of risk to you, and gives you time to think of your answer or, even better, your next question!

In response to any of the “opinion” questions cited above, merely responding, “Why do you ask?” will usually be enough to dissipate any pressure to give your opinion. But if your interviewer again presses you for an opinion, you can ask another question.

Or you could assert a generality that almost everyone would agree with. For example, if your interviewer is complaining about politicians then suddenly turns to you and asks if you’re a Republican or Democrat, you could respond by saying, “Actually, I’m finding it hard to find any politicians I like these days.”

(Of course, your best question of all may be whether you want to work for someone opinionated.)

Question 60 If you won $10 million lottery, would you still work?

TRAPS: Your totally honest response might be, “Hell, no, are you serious?” That might be so, but any answer which shows you as fleeing work if given the chance could make you seem lazy. On the other hand, if you answer, “Oh, I’d want to keep doing exactly what I am doing, only doing it for your firm,” you could easily inspire your interviewer to silently mutter to himself, “Yeah, sure. Gimme a break.”

BEST ANSWER: This type of question is aimed at getting at your bedrock attitude about work and how you feel about what you do.Your best answer will focus on your positive feelings.

Example: “After I floated down from cloud nine, I think I would still hold my basic belief that achievement and purposeful work are essential to a happy, productive life. After all, if money alone bought happiness, then all rich people would be all happy, and that’s not true.

“I love the work I do, and I think I’d always want to be involved in my career in some fashion. Winning the lottery would make it more fun because it would mean having more flexibility, more options...who knows?”

“Of course, since I can’t count on winning, I’d just as soon create my own destiny by sticking with what’s worked for me, meaning good old reliable hard work and a desire to achieve. I think those qualities have built many more fortunes that all the lotteries put together.”

Question 61 Looking back on your last position, have you done your best work?

TRAPS: Tricky question. Answer “absolutely” and it can seem like your best work is behind you. Answer, “no, my best work is ahead of me,” and it can seem as if you didn’t give it your all.

BEST ANSWER: To cover both possible paths this question can take, your answer should state that you always try to do your best, and the best of your career is right now. Like an athlete at the top of his game, you are just hitting your career stride thanks to several factors. Then, recap those factors, highlighting your strongest qualifications.

Question 62 Why should I hire you from the outside when I could promote someone from within?

TRAPS: This question isn’t as aggressive as it sounds. It represents the interviewer’s own dilemma over this common problem. He’s probably leaning toward you already and for reassurance, wants to hear what you have to say on the matter.

BEST ANSWER: Help him see the qualifications that only you can offer.

Example: “In general, I think it’s a good policy to hire from within – to look outside probably means you’re not completely comfortable choosing someone from inside.

“Naturally, you want this department to be as strong as it possibly can be, so you want the strongest candidate. I feel that I can fill that bill because…(then recap your strongest qualifications that match up with his greatest needs).”

Question 63 Tell me something negative you’ve heard about our company…

TRAPS: This is a common fishing expedition to see what the industry grapevine may be saying about the company. But it’s also a trap because as an outsider, you never want to be the bearer of unflattering news or gossip about the firm. It can only hurt your chances and sidetrack the interviewer from getting sold on you.

BEST ANSWER: Just remember the rule – never be negative – and you’ll handle this one just fine.

Question 64 On a scale of one to ten, rate me as an interviewer.

TRAPS: Give a perfect “10,” and you’ll seem too easy to please. Give anything less than a perfect 10, and he could press you as to where you’re being critical, and that road leads downhill for you.

BEST ANSWER: Once again, never be negative. The interviewer will only resent criticism coming from you. This is the time to show your positivism.

However, don’t give a numerical rating. Simply praise whatever interview style he’s been using.

If he’s been tough, say “You have been thorough and tough-minded, the very qualities needed to conduct a good interview.”

If he’s been methodical, say, “You have been very methodical and analytical, and I’m sure that approach results in excellent hires for your firm.”

In other words, pay him a sincere compliment that he can believebecause it’s anchored in the behavior you’ve just seen.