Saturday 30 November 2013

SQL Interview Questions and Answers

1. What is DBMS ?

The database management system is a collection of programs that enables user to store, retrieve, update and delete information from a database.

2. What is RDBMS ?

Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed or reassembled in many different ways without having to reorganize the database tables. Data from relational database can be accessed using an API , Structured Query Language (SQL).

3. What is SQL ?

Structured Query Language(SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard.

4. What are the Advantages of SQL

SQL is not a proprietary language used by specific database vendors. Almost every major DBMS supports SQL, so learning this one language will enable programmers to interact with any database like ORACLE, SQL ,MYSQL etc.
SQL is easy to learn. The statements are all made up of descriptive English words, and there aren't that many of them.
SQL is actually a very powerful language and by using its language elements you can perform very complex and sophisticated database operations.

5. what is a field in a database ?
A field is an area within a record reserved for a specific piece of data.

Examples: Employee Name, Employee ID etc

7. What is a Record in a database ?
A record is the collection of values / fields of a specific entity: i.e. an Employee, Salary etc.

8. What is a Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.

9. What is a Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be considered to be similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself; so when the trigger is fired because of data modification, it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger

10. What are the Different Types of Triggers?

There are two types of Triggers :

1. DML Trigger

There are two types of DML Triggers

Instead of Trigger

Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete.

After Trigger

After triggers execute following the triggering action, such as an insert, update, or delete.

2. DDL Trigger

This type of trigger is fired against Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always After Triggers.

11. What is a View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data as well as updating or deleting rows.The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

12. What is an Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries.

13. What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server databases using T-SQL Statements. With a linked server, you can create very clean, easy–to-follow SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedures sp_addlinkedserver, sp_addlinkedsrvlogin will be used to add new Linked Server.

14. What is a Cursor?

A cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor, we need to perform some steps in the following order:
Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor

15. What is User-defined Functions?

User-defined Functions allow defining its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

16. What is the Difference between a Function and a Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

17. What are Primary Keys and Foreign Keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental aspect of all keys and constraints. A table can have only one primary key.

Foreign keys are a method of ensuring data integrity and manifestation of the relationship between tables.

18. What are Different Types of Join?

Cross Join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Inner Join

A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.

Outer Join

A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:
Left Outer Join: In Left Outer Join, all the rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.
Right Outer Join: In Right Outer Join, all the rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
Full Outer Join: In Full Outer Join, all the rows in all joined tables are included, whether they are matched or not.

Self Join

This is a particular case when one table joins to itself with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join

19. What are Pessimistic Lock and Optimistic Lock?

Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn’t changed before you write the record back. If the record is dirty (i.e. different version to yours), then you abort the transaction and the user can re-start it.

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks.

20. What are Different Types of Locks?

Shared Locks: Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
Update Locks: Used on resources that can be updated. It prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Exclusive Locks: Used for data-modification operations, such as INSERT, UPDATE, or DELETE. It ensures that multiple updates cannot be made to the same resource at the same time.
Intent Locks: Used to establish a lock hierarchy. The types of intent locks are as follows: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
Schema Locks: Used when an operation dependent on the schema of a table is executing. The types of schema locks are schema modification (Sch-M) and schema stability (Sch-S).
Bulk Update Locks: Used when bulk-copying data into a table and the TABLOCK hint is specified.

21. What is the Difference between a HAVING clause and a WHERE clause?

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query, whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

22. What is the difference between UNION and UNION ALL?

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between UNION and UNION ALL is that UNION ALL will not eliminate duplicate rows, instead it just pulls all rows from all the tables fitting your query specifics and combines them into a table

23. What is BCP? When is it Used?

BCP or BulkCopy is a tool used to copy huge amounts of data from tables and views. BCP does not copy the complete structures from source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

24. What is NOT NULL Constraint?

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

25. What is CHECK Constraint?

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

26.What is NOLOCK?

When the NOLOCK hint is included in a SELECT statement, no locks are taken on data when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data

27. How will you Handle Error in SQL SERVER 2008?

SQL Server now supports the use of TRY…CATCH constructs for providing rich error handling. TRY…CATCH lets us build error handling at the level we need, in the way we need to by setting a region where if any error occurs, it will break out of the region and head to an error handler. The basic structure is as follows:

BEGIN TRY

<code>

END TRY

BEGIN CATCH

<code>

END CATCH

So if any error occurs in the TRY block, then execution is diverted to the CATCH block, and the error can be resolved.

28. What is RAISEERROR?

RaiseError generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically.

29 . What is Use of DBCC Commands?

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server. DBCC commands are used to perform the following tasks.
Maintenance tasks on database, index, or filegroup.
Tasks that gather and display various types of information.
Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
Miscellaneous tasks such as ena
bling trace flags or removing a DLL from memory

30. How to Copy the Tables, Schema and Views from one SQL Server to Another?

There are multiple ways to do this.
“Detach Database” from one server and “Attach Database” to another server.
Manually script all the objects using SSMS and run the script on a new server.
Use Wizard of SSMS

31. How to Copy Data from One Table to Another Table?

There are multiple ways to do this.

INSERT INTO SELECT

This method is used when table is already created in the database earlier and data have to be inserted into this table from another table. If columns listed in the INSERT clause and SELECT clause are same, listing them is not required.

SELECT INTO

This method is used when table is not created earlier and it needs to be created when data from one table must be inserted into a newly created table from another table. The new table is created using the same data types as those in selected columns

32. What is OLTP?

OLTP is abbreviation of On-Line Transaction Processing. This system is an application that modifies data At the very instant it is received and has a large number of concurrent users.

33. What is OLAP?

OLAP is abbreviation of Online Analytical Processing. This system is an application that collects, manages, processes and presents multidimensional data for analysis and management purposes.

34. What is Difference between Commit and Rollback when Used in Transactions?

The usual structure of the TRANSACTION is as follows:

BEGIN TRANSACTION

Operations :

COMMIT TRANSACTION or ROLLBACK TRANSACTION

When Commit is executed, every statement between BEGIN and COMMIT becomes persistent to database. When Rollback is executed, every statement between BEGIN and ROLLBACK are reverted to the state when BEGIN was executed.

Friday 27 September 2013

WCF(Window Communication Foundation) Interview Question & Answers

1. What is WCF ?

Stands for Windows Communication Foundation.

Its code name is “Indigo”.

It is a framework for building, configuring and deploying interoperable distributed services.

It enables you to write more secure flexible services without any code change (using configuration).

It also provide built-in support for logging. You can enable/disable logging using configuration.

WCF = Web Service + Remoting + MSMQ + COM+
(or)
WCF = ASMX + .Net Remoting + WSE + Messaging + Enterprise Services

2. What is Contract ? What are the types of Contract ?

It is the agreement between client and service which specifies:

[ServiceContract] - which services are exposed to the client.

[OperationContract] - which operations the client can perform on the service.

[DataContract] – which data types are passed to and from the service.

[MessageContract] - allow the service to interact directly with messages. Message contracts can be typed or untyped and are useful in interoperability cases when another party has alreadydictated some explicit (typically proprietary) message format.

[FaultContract] -which errors are raised by the service and how the service handles andpropagates errors to its clients.

3. Difference between WCF and ASP.NET Web Service

Here are the 10 important differences between WCF Services and ASP.NET Web Services:

4. What are the transport schemes supported by WCF ? Give example of address for each scheme.

Following are the transport schemes supported by WCF:

HTTP/HTTPS - http://localhost:8001/MyService
TCP - net.tcp://localhost:8002/MyService
IPC - net.pipe://localhost/MyPipe
Peer network
MSMQ - net.msmq://localhost/private/MyQueue
Service bus - sb://MyNamespace.servicebus.windows.net/

5. What is binding ?

A binding is the set of configurations regarding the transport protocol, message encoding, communication pattern, reliability, security, transaction propagation, and interoperability.

6. What are the types of bindings supported by WCF ? What are their advantages and disadvantages ?

Binding	Feature	Suitable For	Transport	Message encoding	Security Mode	Resource Manager	Transaction Flow
BasicHttpBinding	Not secure by default.	Communication with WS-Basic Profile conformant Web Services like ASMX.	HTTP	Text	None	X	X
WSHttpBinding	Secure, Interoperable.	Non-duplex service contracts.	HTTP	Text	Message	Disabled	WS-Atomic
WSDualHttpBinding	Secure, Interoperable.	Duplex service contracts or communication through SOAP intermediaries.	HTTP	Text	Message	Enabled	WS-Atomic transaction
WSFederationHttpBinding	Secure, Interoperable.	Supports the WS-Federation protocol, enabling organizations that are in a federation to efficiently authenticate and authorize users.	HTTP	Text	Message	Disabled	WS-Atomic transaction
NetTcpBinding	Secure, Optimized.	Cross-machine communication between WCF applications.	TCP	Binary	Transport	Disabled	Ole transaction.
NetPeerTcpBinding	Secure.	Multi-machine communication.	P2P	Binary	Transport	X	X
NetNamedPipesBinding	Secure, Reliable, Optimized.	On-machine communication between WCF applications.	Named Pipes	Binary	Transport	X	Ole transaction.
NetMsmqBinding		Cross-machine communication between WCF applications.	MSMQ	Binary	Message	X	X
MsmqIntegrationBinding	Does not use a WCF message encoding – instead it lets you choose a pre-WCF serialization format.	Cross-machine communication between a WCF application and existing MSMQ applications.	MSMQ	Pre-WCF format	Transport	X	X

7. What is Endpoint in WCF ?

Endpoint = Address (A) + Binding (B) + Contract (C)
Address specifies where the services is hosted.
Binding specifies how to access the hosted service. It specifies the transport, encoding, protocol etc.
Contract specifies what type of data can be sent to or received from the service.
Eg:

<endpoint name="BasicHttpGreetingService" address="http://localhost:5487/MyService/GreetingService.svc" binding="basicHttpBinding" contract="MyNamespace.MyService.IGreetingService" />

8. Explain Address in detail ?

It is the url which specifies the location of the service. Client can use this url to connect to the service and invoke the service methods.

Eg: http://localhost:5487/MyService/GreetingService.svc

9. Explain Binding in detail ?

It specifies how to access the hosted service. There are following characteristics of binding:

Transport defines the communication protocol to be used to communicate between service and client. It may be HTTP, TCP, MSMQ, NamedPipes etc. It is mandatory to define transport.

Encoding defines the technique used to encode the data before communicating it from one end to the other.

Protocol defines the configurations like reliability, security, transaction, timouts, message size etc.

10. What is binding configuration ?

You can customize the binding used by endpoint using config file. For example, you can enable/disable transaction for the binding used by endpoint. All you need is to configure binding element in config file similar as below:

<bindings>
  <netTcpBinding>
    <binding name = "TransactionalTCP" transactionFlow = "true" />
  </netTcpBinding>
</bindings>

11. What is default endpoints ?

If the service host does not define any endpoints (neither in config nor programmatically) but does provide at least one base address, WCF will by default add endpoints to the service. These are called the default endpoints. WCF will add an endpoint per base address per contract, using the base address as the endpoint’s address. WCF will infer the binding from the scheme of the base address. For HTTP, WCF will use the basic binding. Note that the default bindings will affect the default endpoints. WCF will also name the endpoint by concatenating the binding name and the contract name.

12. How can we enable/disable metadata publishing of our WCF service ?

You can enable enable meta data publishing for a WCF service two ways:

Configure metadata publishing for a service that uses default endpoints. Specify the ServiceMetadataBehavior in the configuration file but do not specify any endpoints.

<configuration>
 <system.serviceModel>
   <behaviors>
     <serviceBehaviors>
       <behavior name="CustomServiceBehavior">
         <serviceMetadata httpGetEnabled="True" />
         <serviceDebug includeExceptionDetailInFaults="False" />
       </behavior>
     </serviceBehaviors>
   </behaviors>
 </system.serviceModel>
</configuration>

Configure metadata publishing for a service that uses explicit endpoints. Specify the ServiceMetadataBehavior in the configuration file and a mex endpoint.

<configuration>
 <system.serviceModel>
   <services>
     <service name="MyNamespace.MyService.GreetingService" behaviorConfiguration="CustomServiceBehavior">
       <endpoint address="" binding="wsHttpBinding" contract="MyNamespace.MyService.IGreetingService" />
       <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
     </service>
   </services> 
   <behaviors>
     <serviceBehaviors>
       <behavior name="CustomServiceBehavior">
         <serviceMetadata httpGetEnabled="True" />
       </behavior>
     </serviceBehaviors>
   </behaviors>
 </system.serviceModel>
</configuration>

Supported bindings for mex endpoint are mexHttpBinding, mexHttpsBinding, mexNamedPipeBinding andmexTcpBinding.

13. How can you generate proxy class and configuration file for WCF service ?

WCF provides an utility svcutil.exe which can be used to generate proxy class and configuration file. Eg:

SvcUtil http://localhost:8002/MyService/ /out:Proxy.cs /noconfig

14. Is there any tool provided by Microsoft for editing configuration file ?

Yes. Microsoft provides an utility “SvcConfigEditor.exe” that can edit any configuration file.

15. How can you test your new WCF service without writing any client application ?

Microsoft provides a tool which can be used to test any WCF service. To use this tool, open visual studio command prompt and execute the command “wcftestclient.exe“. It will open a window where you can add many WCF services and test. You can also provide values for input parameters of WCF methods.

16. Which bindings support reliability and message ordering ?

Binding name Supports
reliability Default
reliability Supports ordered
delivery Default Ordered
delivery

BasicHttpBinding No N/A No N/A

NetTcpBinding Yes Off Yes On

NetNamedPipeBinding No N/A(On) Yes N/A(On)

WSHttpBinding Yes Off Yes On

NetMsmqBinding No N/A No N/A

17. How can you configure reliability using .config file ?

<bindings>
  <netTcpBinding>
    <binding name = "ReliableTCP">
      <reliableSession enabled = "true"/>
    </binding>
  </netTcpBinding>
</bindings>

18. How to create a service contract and operation contract ? Can you give an example ?

We can create a contract using an interface by applying [ServiceContract] and [OperationContract] attributes on Interface and Methods respectively.

[ServiceContract]
public interface IGreetingService
{
    [OperationContract]
    string GreetMe(string userName);
}

public class GreetingService : IGreetingService
{
    public string GreetMe(string userName)
    {
        return string.Format("Welcome {0}", userName);
    }
}

If we do not want to create interface then we can apply the attributes on a class itself.

[ServiceContract]
public class GreetingService
{
    [OperationContract]
    public string GreetMe(string userName)
    {
        return string.Format("Welcome {0}", userName);
    }
}

19 . Can you give an example of DataContract ?

[DataContract]
public enum Color
{
  [EnumMember]
  Red,

  [EnumMember]
  Green,

  [EnumMember]
  Blue
}
[DataContract]
public class Shape
{
  [DataMember]
  public string Name { get; set; }

  [DataMember]
  public Color FillColor { get; set; }

  [DataMember]
  public double Area { get; set; }
}

20. What is MessageContract ? Can you give an example ?

WCF uses SOAP messages to transfer information from client to server and vice-versa. It converts data contract to SOAP messages. SOAP message contains Envelope, Header and Body. SOAP envelope contains name, namespace, header and body element. SOAP Header contains important information which are related to communication but not directly related to message. SOAP body contains information which is used by the target.

SOAP Envelope = Name + Namespace  + Header + Body

However there are some cases when you want to have control over the SOAP messages. You can achieve this using MessageContract.

[MessageContract]
public class Shape
{
    [MessageHeader]
    public string ID;
    [MessageBodyMember]
    public string Name;
    [MessageBodyMember]
    public double Area;
}

In above example, ID will be added as header, Name and Area as body in SOAP envelope.

When you use MessageContract then you have control over the SOAP message. However some restrictions are imposed as below:

You can have only one parameter for a service operation if you are using MessageContract.
You can return either void or MessageContract type from service operation. Service operation can not return DataContract type.
Service operation can accept and return only MessageContract type.

Some important points about MessageContract:

You can mention the MessageHeader or MessageBodyMember to be signed or Encrypted using ProtectionLevel property.
The order of the body elements are alphabetical by default. But you can control the order, using Order property in the MessageBody attribute.

21. What is FaultContract ?

In most of the cases you would like to convey the details about any error which occurred at service end. By default, WCF exceptions do not reach client. However you can use FaultContract to send the exception details to client.

[DataContract()]
public class CustomError
{
  [DataMember()]
  public string ErrorCode;
  [DataMember()]
  public string Title;
  [DataMember()]
  public string ErrorDetail;
}

[ServiceContract()]
public interface IGreetingService
{
  [OperationContract()]
  [FaultContract(typeof(CustomError))]
  string Greet(string userName);
}

public class GreetingService : IGreetingService
{
  public string Greet(string userName)
  {
    if (string.IsNullOrWhiteSpace(userName))
    {
        var exception = new CustomError()
        {
            ErrorCode = "401",
            Title = "Null or empty",
            ErrorDetail = "Null or empty user name has been provided"
        };
        throw new FaultException<CustomError>(exception, "Reason : Input error");
    }
    return string.Format("Welcome {0}", userName);
  }
}

22. What are session modes in WCF ? How can you make a service as sessionful ?

ServiceContract attribute offers the property SessionMode which is used to specify the session mode. There are three session modes supported by WCF:

Session.Allowed(default): Transport sessions are allowed, but not enforced. Service will behave as a per-session service only if the binding used maintains a transport-level session.
Session.Required: Mandates the use of a transport-level session, but not necessarily an application-level session.
Session.NotAllowed: Disallows the use of a transport-level session, which precludes an application-level session. Regardless of the service configuration, the service will always behave as a per-call service.

[ServiceContract(SessionMode = SessionMode.NotAllowed)]
interface IMyContract
{...}

[ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall)]
class MyService : IMyContract
{...}