Tag Archives: Cracking tough interview questions

SQL Interview Questions

1 Apr

These are the most common yet tricky questions that you can expect in a .NET/SQL interview.

1)      What is the difference between truncate and Delete?

  1.  Delete will fire delete trigger, truncate will not.
  2. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.Once you truncate you can’t rollback.
  3. If there is an identity column in the table truncate will reset identity to 1, delete will not.

2)      Name the different type of joins

  1. Inner Join
  2. Outer Join  (Left Outer Join and Right Outer Join)
  3. Cross join
  4. Self-Join

3)      What is an Index?

There are two types of indexes. Clustered index and Non Clustered Index. A table can have only 1 Clustered Index while it can have up to 249 & 999 nonclustered indexes on SQL Server 2005 & 2008 respectively. A clustered index stores the actual data rows at the leaf level of the index. Indexes can make Select statement fast but delete, update and insert statements will get slow.

4)      What are different types of Isolation levels in SQL Server

a)     READ UNCOMMITTED

b)      READ COMMITTED

c)       REPEATABLE READ

d)      SERIALIZABLE

e)       SNAPSHOT

 5)      What are different types of constraints in SQL Server?

  1.  Primary key Constraint
  2. Foreign key Constraint
  3. Check Constraint
  4. Unique Key Constraint

6)      What is the difference between where and having clause?

A having clause is typically used when you use group by clause.

Example:

SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = ‘CA’
GROUP BY titles.pub_id
HAVING AVG(titles.price) > 10

WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set

7)      How would you do “Error Handling” in SSIS?

SSIS package could mainly have two types of errors

a) Procedure Error: Can be handled in Control flow through the precedence control and redirecting the execution flow.

b) Data Error: is handled in DATA FLOW TASK buy redirecting the data flow using Error Output of a component.

8)      What is the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column. Major difference is that, primary key does not allow NULLs, but unique key allows NULL.

9)      What are defaults? Is there a column to which a default cannot be bound?

A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them.

You can create default and then bind a column to them.

Example:

//This will create default ” ZipCode ” in database

Create default ZipCode as “78746″

//This will bind the default we created to a column “PostalCode” in table “EmployeeData”
sp_bindefault ZipCode, “EmployeeData.PostalCode”
10)  What is SQL Blocking?
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.
Thanks for visiting my blog. Please feel free to leave suggestions/feedbacks/comments.
If you like my post please click “LIKE” button below.

Check back again for more SQL and .NET Interview Questions. Read more on .NET Questions here .NET Questions

Anyone on Twitter? Follow @MCADDeveloper @Consultpri

Advertisements

Commonly asked .NET Technical Interview Questions and ANSWERS!

20 Mar

Here are some commonly asked .NET Interview Questions which you can expect in a Technical .NET Interview.

What is CLR?
Common Language Runtime.

  • CLR is responsible for managing the execution of .NET programs.
  • It invokes just-in-time (JIT) compiler which is responsible for converting IL code to machine code.
  • The CLR provides additional services including memory management, type safety and exception handling. Memory management is provided using Garbage collector.

What is CLS?
Common Language Specification.

  • Set of basic language features needed by many applications.
  • Helps enhance and ensure language interoperability by defining a set of features that developers can rely on to be available in a wide variety of languages

What is Inheritance?
It is one of the most important features of Object Oriented programming that helps in using existing functionality and we do not have to write same code again and again in all classes. (Please refer to my blog post on inheritance for further explanation)

What are Interfaces?
Interfaces define members without implementations. A class can only inherit from only one class but can implement more than one interface. See more information on inheritance below.

What is Polymorphism?
Through inheritance, a class can be used as more than one type; it can be used as its own type, any base types, or any interface type if it implements interfaces. This is called polymorphism.

What is Reflection?
All .NET compilers produce metadata about the types defined in the modules
they produce. This metadata is packaged along with the module (modules in
turn are packaged together in assemblies), and can be accessed by a
mechanism called reflection. The System.Reflection namespace contains
classes that can be used to interrogate the types for a module/assembly.

Does C# support multiple-inheritance?
No.

C# Tutorials: Inheritance

When you derive a class from a base class, the derived class will inherit all members of the base class except constructors.
Example: Let us define a base class “Location”. This class will have two Properties City and State. We will define two methods in this class, one is Get City which will be a virtual method (Derive class can override it) and other will be abstract method getState (Which must be overridden). Here is the code:

Code for Base Class:
using System;
namespace MyCode
{
public abstract class Location
{
/// <summary>
/// This is constructor
/// </summary>
public Location()
{
}
/// <summary>
/// this is Property
/// </summary>
public string City { get; set; }

/// <summary>
/// this is Property
/// </summary>
public string State { get; set; }

/// <summary>
/// getCity
/// </summary>
/// <returns></returns>
/// Virtual means derived class can override it
public virtual string getCity()
{
return City;
}

/// <summary>
/// getState
/// </summary>
/// <returns></returns>
/// abstract means derived class must overide it
public abstract string getState();

}
}
Now we will write two classes that will derive from the base class (Location). One class will override getCity Method and other will not.
Code for Derived Classes:
using System;
namespace MyCode
{
public class IBMCompanyLocation : Location
{
public IBMCompanyLocation()
{
State = “Texas”;
}
/// <summary>
/// getCity
/// </summary>
/// <returns></returns>
public override string getCity()
{
return “Austin”;
}
/// <summary>
/// getState
/// </summary>
/// <returns></returns>
public override string getState()
{
return State;
}
}
}
using System;
namespace MyCode
{
public class MicrosoftCompanyLocation : Location
{
public MicrosoftCompanyLocation()

{
State = “Washington”;
City = “Seattle”;
}
/// <summary>
/// getState
/// </summary>
/// <returns></returns>
public override string getState()
{
return State;
}
}
}

%d bloggers like this: