Interview Questions For 2 Years Experience in SQL and C#

I appeared for an interview that was for 2 years experience and had 4 technical rounds. Here my purpose is to share the questions that I encountered. However, I have given brief answers just for reference.

Question 1

Orders table
OrderId int Checked
Deptno int Checked
Amount int Checked

sales table
orderId int Checked
salesmanId int Checked

Get the highest earning salesman in each department.

Answer

select salesmanId, Deptno from sales inner join orders on sales.orderId=orders.OrderId where amount in (select MAX(Amount) from sales inner join orders on sales.orderId=orders.OrderId group by Deptno)

Question 2: When will union and union all behave the same?

Answer

Union will give a record only once even if it occurs several times. Union all will give a record as many times as it occurs. So union and union all will behave the same when each record is distinct in all the participating tables.

select name, id from Emp1 union select name, id from Emp2

select name, id from Emp1 union all select name, id from Emp2

Case1

Emp1

ID	Name
11	N1
12	N2

Emp2

ID	Name
21	p1
22	p2

Union Output and union all output:

ID	Name
11	N1
12	N2
13	p2
14	p2

Case2

Emp1

ID	Name
11	N1
12	N2
11	N1

Emp2

ID	Name
21	p1
22	p2

Union all output:

ID	Name
11	N1
12	N2
11	N1
21	p1
22	p2

Union output:

ID	Name
11	N1
12	N2
21	p1
22	p2

Case3

Emp1

ID	Name
11	N1
12	N2

Emp2

ID	Name
21	p1
22	p2
12	N2

Union all output:

ID	Name
11	N1
12	N2
21	p1
22	p2
12	N2

Union output:

ID	Name
11	N1
12	N2
21	p1
22	p2

In case 1 both union and union all will behave the same. But, for case2 and case3 union and union all will behave differently.

Question 3: What are SQL joins?

Answer

• Inner join
  
  When you want only matching records of both the tables, use inner join.
  
  
• Outer join
  
  When we expect both matching and unmatching records of both the tables, we need to join using outer join. Again, outer join is of different types, depending on unmatching records of which table is expected.
  
  
• Left outer join
  
  When matching records of the right table and all records of the left table is expected.
  
  
• Right outer join
  
  When matching records of the left table and all records of the right table is expected.
  
  
• Full outer join
  
  When all records of both the tables are expected.

Question 4: When does a session actually start?

Answer

A session actually starts when a visitor requests your site for the first time. A new session starts when the request doesn't contain any SessionID or the sessionID references an expired session. The Session_OnStart event in Global.asax can be used for tracking session-related information.

Question 5: How is a session maintained?

Answer

When a web app is requested for the first time, the server creates a sessionID and saves it in the cookie of the client browser. This sessionID is sent to the server in all the subsequent requests. If cookieless is made true, sessionID is sent in the URL else the cookie file is sent. This way the session is maintained with SessionID.

Question 6: What are the various session modes in ASP.NET?

• In-proc:
  
  Session data is stored in the same machine as that of the server. So session data is lost, when the server restarts. Session data is overhead on the server.
  
  
• State server:
  
  Session data is stored in a separate machine.
  
  
• SQL Server:
  
  Session data is stored in a SQL Server database and kept centrally.

Question 7: You have a user control that has 2 fields. The user will enter data and you need to validate this data and show valid/invalid data in a page. Tell the various ways to do this.



Answer

You can cache the element on the page, process data on the user control and then write the response on the cached element. This can be done by both AJAX and postback.

Question 8: Write a method to reverse each word of a string without using predefined functions. You can use ToCharArray().

Answer

private static string ReverseEachWord(string StringToReverse)
{
   StringToReverse += " ";
   StringBuilder ReversedString = new StringBuilder();
   char[] ArrStr = StringToReverse.ToCharArray();
   StringBuilder WordToReverse = new StringBuilder();
   StringBuilder ReversedWord = new StringBuilder();
   foreach(char Temp in ArrStr)
   {
      if(Temp != ' ')
      {
         WordToReverse.Append(Temp);
      }
      else
      {
         for(int i=WordToReverse.Length-1; i>=0 ; i--)
         {
            ReversedWord.Append(WordToReverse[i]);
         }
         if(ReversedString.Length == 0)
         {
            ReversedString.Append(ReversedWord);
         }
         else
         {
            ReversedString.Append(" " + ReversedWord);
         }
         WordToReverse = new StringBuilder();
         ReversedWord = new StringBuilder();
      }
   }
   return ReversedString.ToString();
}

Question 9: Name the ActionResults you know.

Answer

• View
• PartialView
• Content
• Empty
• JSON
• Redirect
• RedirectToRoute
• File

Question 10: What are Html helpers?

Answer

Html helpers are like ASP controls, but these are light weight.

Question 11: Why do we need Http handlers and models?

Answer

To process requests in a way different than the regular IIS way.

Question 12: The default order in which the view is searched.

Answer

If Home/Index is requested, views will be searched in the following order:

~/Views/Home/Index.cshtml
~/Views/Shared/Index.cshtml
~/Views/Shared/Home/Index.cshtml

Question 13: Session.Abandon() vs Clear().

Answer

Session.Abandon() destroys the current session by firing a Session_End event. It releases the SessionState object and its items to free the resources.

Session.Clear( ) just clears the session data (gives a null value to the session) without killing it. It still holds the SessionState and resources associated with it.

Session ID will remain the same in both of the cases as long as the browser is not closed.

session

Question 14: What are the various ways to send data from a controller to view?

Answer

• ViewBag: It is a dynamic property.
  
  Controller:: ViewBag.Name = “Rasmita”;
  View:: @ViewBag.Name
  
  
• ViewData: It is a Dictionary object derived from the ViewDataDictionary class. It requires type casting and null check for complex data types.
  
  Controller:: ViewData[“Name”] = “Rasmita”;
  View:: @ViewData[“Name”]
  
  ViewBag and ViewData spans for a server call.
  
  
• TempData: It is a dictionary derived from the TempDataDictionary class. It stores data in a short live session and spans for a HTTP request. It is used when moving from a controller to another or from an action method to another. It is for the current and subsequent request only. It requires type casting and null check for complex data types.
  
  ViewBag-ViewData-TempData

Question 15: How to retain a TempData value

Answer

Using Peek and Keep.

If you read the TempData and then call the Keep() or you read the TempData by using Peek(), the TempData will be persisted.

@TempData[“Name”];
TempData.Keep(“Name”);

Or:

TempData.Peek(“Name”)

Question 16: Stored Procedure vs Function

Answer

• A Stored Procedure can return zero, single or multiple values. But, a function must return a single value.
  
• A Stored Procedure can have both input and output parameters whereas Functions can have only input parameters.

• Functions allow only a SELECT command whereas a Stored Procedure allows SELECT and other DML commands (INSERT, UPDATE and DELETE).
  
• A Stored Procedure can call a function, but a function can't call a Stored Procedure.
  
• A Stored Procedure can use try-catch block to handle exceptions. But try-catch is not allowed in functions.
  
• Functions can be used in a SELECT statement, but procedures are not allowed in a select-statement.
  
• SQL transactions can be used in Stored Procedures but not in Functions.

Question 17: What are the various ways to send content from one page to another?

Answer

Response. Redirect()
Server.Transfer()
WebClient.DownloadFile()

Question 18: How to call a code behind method asynchronously

Answer

• Create a public static method and decorate it with the WebMethod Attribute.
  
• Add an asp:ScriptManager control with “EnablePageMethods = true” in the aspx page.
  
• Now either you can make a jQuery ajax request to it or can call from JavaScript using the PageMethods class.
  

  [WebMethod]
  public static string Operate()
  {
     return “Hi”;
  }
  
  <asp:ScriptManager runat=”server” EnablePageMethods = true></asp:ScriptManager>
  
  Window.PageMethods.Operate();

Question 19: What does asp:ScriptManager do to call a WebMethod?

Answer

JavaScript proxies are automatically generated by the ScriptManager that can call the page methods in the code-behind page. The proxy is accessed on the client-side using a special class generated by the ScriptManager called PageMethods.

ScriptManager

Question 20: What are all the things that AJAX can't do?

Answer

• AJAX is not supported by old browsers that don't support the XMLHttpRequest JavaScript object.
  
• AJAX will not work if JavaScript is disabled in the browser.
  
• Back functionality will not work with ajax since dynamic pages don't register themselves with the browser history engine.
  
• If the page is bookmarked, the content implemented with AJAX will not reflect.

Question 21: What are Session Modes advantages over the others?

Answer

InProc: Session data is stored in the same web server. Performance-wise it's the best mode. But session data will be lost once the server is restarted. Again, since session data is stored in the same server, it’s an overhead on the server, so more data can’t be saved in the session.

StateServer: Session data is serialized and stored in a separate server so an issue with IIS will not hamper the session data. The aspnet_state.exe service needs to be up and running. Due to serialization and de-serialization, performance-wise it’s slow.

SqlServer: This is a secure and reliable mode. Session data is serialized and saved in a SQL Server database. The overhead is with serializing and de-serializing data. Since data is stored in the database, it’s centrally available. This is preferred if frequent server restarts are needed or data must be shared with multiple servers.

Session Modes

Question 22: Two interfaces have the same method. A class implements both of these interfaces. You need to override both of these methods in your class. How can you do this? If another class inherits this class, how can you call both of these methods?

Answer

public interface Interface1
{
   void Add();
}
public interface Interface2
{
   void Add();
}
public class BaseClass : Interface1, Interface2
{
   void Interface1.Add()
   {
      Console.WriteLine("interface1");
   }
   void Interface2.Add()
   {
      Console.WriteLine("interface2");
   }
}
class Program: BaseClass
{
   static void Main(string[] args)
   {
      var Obj = new BaseClass();
      var ObjIface1 = (Interface1)Obj;
      var ObjIface2 = (Interface2)Obj;

      ObjIface1.Add();
      ObjIface2.Add();
   }
}

Question 23: What is late binding?

Answer

This comes with inheritance and done with the same method name and signature in both the base and child classesd. The Virtual keyword in the base class method and the override keyword in the child class method. This is also known as dynamic polymorphism or method overriding.

Question 24

Given:

Class test
{
   String str1 = “Hello”;
   Public static void Main()
   {
      Str1 +=” World”;
      Console.WriteLine(Str1);
   }
}

What will be the output?

Answer

Compilation error: non-static fields can’t be accessed in static context

Question 25: Sealed vs Static

Answer

A Sealed class can’t be inherited. A static class can’t be instantiated.

Question 26: virtual vs override

Answer

When you want a base class method to be over-ridden in a child class, the method in the base class should have the “virtual” key and the method in the child class should have the “override” keyword.

virtual-override

Question 27: The usage of partial keyword

Answer

The “partial” keyword is used with a class, interface or structure when you want to have its definition split within the same namespace. A partial class can have a partial method when you need to have the method declaration in one partial class and its body in another partial class. More...

Question 28: What is OOP?

Answer

Object Oriented Programming with the following main features:

1. Encapsulation: Enclosing all data members (fields and properties) and actions on them (methods) together in a class.
   
   
2. Abstraction: Exposing the operations, while hiding its internal implementation details.
   
   
3. Inheritance: Reusing properties and methods of a class in another.
   
   
4. Polymorphism: The same method behaving differently in different scenarios.

Question 29: How to secure a WCF service

Answer

There are 2 ways to secure a WCF service: Securing the message sent over and securing the transmission medium.

WCF security

If you know the application domain or IP of the WCF client, UrlReferrer can be used to allow only the respective applications to request your WCF service.

In the operation contract, you can put a if condition for the main logic such as:

ArrAllowedClients.Contains(HttpContext.Current.Request.UrlReferrer.AbsoluteUri)

where, ArrAllowedClients holds the set of allowed IPs or application domains.

Question 30: Describe the Page life cycle

Answer

PreInit: Sets the Master page and theme property dynamically.

Init: Each controls the UniqueID set. This event is fired first for the inner-most element in the DOM, then the parent controls and finally for the page itself.

InitComplete: Changes to the view state and any functionality that requires all the controls to be loaded can be done here.

PreLoad: ViewState of the page and all the controls are loaded. Postback data is also loaded.

Load: The Load event is raised for the page object itself and then recursively for all the child controls until the page itself and all it’s controls are loaded.

Dynamic controls can be created here.

IsPostBack andIsValid values can be checked to avoid redundant code.

Here all the control values are restored.

PreRender: Allows final changes to the page and it’s controls. This event is fired for the page first and then for the controls.

Render: Generates the client-side HTML, DHTML and script that are necessary to display the page on the browser.

UnLoad: This event is fired for the controls and then for the page itself. All the unused objects are disposed.

Page Life cycle

Question 31: MVC life cycle?

Answer

The RouteTable collection is created when the MVC application is requested for the first time. For the subsequent requests, UrlRoutingModule intercepts the request. From the requested URL, this module determines which controller and which action method is requested and executes the respective ActionMethod.

ASP.Net MVC life-cycle

Question 32: What does AutoEventWireUp in the page directive tag mean?

Answer

If “AutoEventWireUp = true”, you don’t need to attach event handlers to the events. Automatically, the event handler will be executed when the event fires up. You just need to override the required event handler.

Question 33: If you have 10 users requesting your application, how many times Application_Start event will be fired?

Answer

Application_Start will be fired only once at the application level. In other words, when the application is requested for the first time.

Question 34: Property vs field

Answer

Fields are private elements whereas properties are public elements with getter and/or setter.

Question 35: for vs foreach

Answer

foreach is generally used with a collection.

for uses lesser stack space than foreach since foreach creates a local copy of the item to traverse through and the item that changes in the loop.

Question 36: String is immutable. What does that mean? Explain with an example.

Answer

A string value can’t be changed. Each time you change a string variable, it actually creates a new string instance and dereference the old value.



Now Str1 will have the value “Hello World”. But, “Hello ” also exists in memory but it is not referenced by any variable.

Question 37: How to declare a global variable in JavaScript?

Answer

Either you can define the variable outside functions or you can simply skip the “var” keyword in the variable declaration.

Question 38: alert (5+”5”)

Answer

55 as JavaScript is not a strongly typed language.
Here is an example of string concatenation that shows that why the answer here is 55:
5+5+”5” => 105
5+”5”+5 => 555