Tweet
Hire Me on Freelancer.com

Wednesday, 24 October 2012

Mostly asked SQL Server 2005 2008 Interview Qusetion and Answers (Part 1)

Asp.net interview, .NET Interview Questions and Answers, asp.net interview questions, asp.net interview questions and answers, C# interview questions and answers, IIS interview questions and answers, sql server interview questions and answers, vb.net interview questions and answers, XML interview questions and answers, Asp.Net 3.5,Asp.Net 4.0,Asp.Net4.5, Asp.Net C#, VB.Net.



SQL: Queries

1. Write a query to select the second highest salary from a table.
Answer: SELECT max (salary) AS salary2 FROM orders WHERE salary < (SELECT max (salary) AS salary1 FROM orders)

2. Write a query to select the 5th highest salary from a table.
Answer: SELECT min (salary) AS high5 FROM employee WHERE salary IN (SELECT DISTINCT TOP 5 salary FROM orders ORDER BY salary DESC)

3. How to find duplicate records with the number they are duplicated?
Answer: SELECT Id, count (*) as number records from table group by id having count (*) > 1.

SQL: Questions

1. What is the difference between Delete and Truncate command in SQL?
Answer: Delete command and truncate command both will delete the data, however the truncate command cannot be rolled back as delete can be. The delete command can be used for selected records using the where clause but with the truncate command we have to lose data. DELETE statement is a logged operation and hence takes more time then truncate.

2. What is Magic Table in SQL?
Answer: The insert and Delete commands are known as magic tables in SQL.

3. Can Primary key is a Foreign Key on the same table?
Answer: Yes, Consider a category table in an e-commerce web site.
Category_Id, Category Name, Parent_Category_ID. In this table all the parent categories are also categories. When we create a self join category id will be treated as foreign key to the same table.

4. What is Normalization? What are its rules?
Answer: Normalization is the technique in the database design where the idea is to reduce the redundancy of non key data items across the table.

1.       Rule 1: There should be a one-to-one relationship between the instances of an entity and the rows of the table.
2.       Rule 2: A field should have the same meaning in each row of the table.
3.       Rule 3: Each table should represent at most one entity.
4.       Rule 4: Multiple instances of an entity should be represented by multiple rows in a table.
5.       Rule 5: Joins should be based only on primary and foreign-key equality.
6.       Rule 6: Make sure keys are linked correctly.

5. What are the advantages and disadvantages of Normalization?
Answer: There are several advantages of normalization as under:

1.       Faster sorting and index creation.
2.       A larger number of clustered indexes.
3.       Narrower and more compact indexes.
4.       Fewer indexes per tables, which improve the performance of INSERT, UPDATE, and DELETE statements
5.       Fewer null values and less opportunity for inconsistency, which increase database compactness.
6.       Beside the above benefits there are few disadvantages as well:
7.       Increased amount of Normalization increases the amount of complexity of joins between tables and that hinders the performance.

6. What are the conditions to achieve the normalization?
Answer: There are few conditions to achieve the normalization:

1.       There should be a unique row identifier.
2.       A table should store only data for a single type of entity. For e.g. details for book’s publisher and book’s author should be saved under different table.
3.       A table should avoid columns which can be null-able.
4.       A table should avoid duplication of data and columns.

7. What is a Stored Procedure? State its advantage.
Answer: A stored procedure is a set of pre-compiled SQL commands (query statements), which are stored in the server. It is faster than the loose SQL statements processed on client, as it is pre-compiled. It can execute more than one SQL commands once as they are bundled in a single entity. We can use control statements within the stored procedure, which will allow us to repeat some SQL command. It can send return values depending upon the result. Stored procedures are used to reduce network traffic.

8. What is a Trigger?
Answer: Triggers are a special type of stored procedure, which gets invoked upon a certain event. They can be performed upon an INSERT, UPDATE and DELETE.

9. What is a Clustered Index?
Answer: The data rows are stored in order based on the clustered index key. Data stored is in a sequence of the index. In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the key values. A table can contain only one clustered index. A clustered index usually provides faster access to data than does a non-clustered index

10. What is a Non-Clustered Index?
Answer: The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. In a non-clustered index, the physical order of the rows in the table is not same as the logical (indexed) order of the key values.

11. Describe the three levels of data abstraction?
They are three levels of abstraction:

1.       Physical level: The lowest level of abstraction describes how data are stored.
2.       Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
3.       View level: The highest level of abstraction describes only part of entire database.

12. What is DDL (Data Definition Language)?
Answer: A data base schema which is specified by a set of definitions expressed by a special language is called DDL. Data Definition Language (DDL) is used to define and manage all the objects in an SQL database.

13. What is DML?
Answer: It is a special language used to manipulate the Data. Data Manipulation Language (DML), which is used to select, insert, update, and delete data in the objects defined using DDL.

14. What is a PRIMARY KEY?
Answer: The PRIMARY KEY is the column(s) used to uniquely identify each row of a table.

15. What is a FOREIGN KEY?
Answer: A FOREIGN KEY is one or more columns whose values are based on the PRIMARY or CANDITATE KEY values from the database.

16. What is a UNIQUE KEY?
Answer: A UNIQUE KEY is one or more columns that must be unique for each row of the table.

17. What is the difference between UNIQUE and PRIMARY KEY?
Answer: The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed. In case of PRIMARY KEY columns entry of duplicate as well as <NULL> value is also restricted.
18. What is a VIEW?
Answer: A View is a database object that is a logical representation of a table. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.
19. What is a ROWID?
Answer: ROWID is the logical address of a row, and it is unique within the database.

20. What is INDEX?
Answer: INDEX is a general term for an SQL feature used primarily to speed up execution and impose UNIQUENESS upon data. You can use an index to gain fast access to specific information in a database table. An index is a structure that orders the values of one or more columns in a database table. The index provides pointers to the data values stored in specified columns of the table, and then orders those pointers according to the sort order you specify.
21. What is a cursor?
Answer: An entity that maps over a result set and establishes a position on a single row within the result set. After the cursor is positioned on a row, operations can be performed on that row, or on a block of rows starting at that position. The most common operation is to fetch (retrieve) the current row or block of rows.
22. The Difference between ‘Count’ and ‘Count (*)’?
Answer: ‘Count’: Counts the number of non-null values. ‘Count (*)’: Counts the number of rows in the table, including null values and duplicates.

i will appreciate your comments and time for using my blog.
"Necessity is the mother of Invention"

Mostly asked Asp.Net Interview Qusetion and Answers 3.5 , 4.0 (Part - 3)




Asp.net interview, .NET Interview Questions and Answers, asp.net interview questions, asp.net interview questions and answers, C# interview questions and answers, IIS interview questions and answers, sql server interview questions and answers, vb.net interview questions and answers, XML interview questions and answers, Asp.Net 3.5,Asp.Net 4.0,Asp.Net4.5, Asp.Net C#, VB.Net.
 


Tricky Questions

1. If I’m developing an application that must accommodate multiple security levels though secure login and my ASP.NET web application is spanned across three web-servers (using round-robin load balancing) what would be the best approach to maintain login-in state for the users?
Answer: You can use the security state maintained using a database. (Use Authentication mode as database)

2. What’s the difference between Code behind=”MyCode.aspx.cs” and src=”MyCode.aspx.cs” mce_src=”MyCode.aspx.cs”?
Answer: Visual Studio uses the Code behind attribute to distinguish the page source or programming logic from the design. Also the src attribute will make the page compile on every request. That is the page will not be compiled in advance and stored in the bin as a dll instead it will be compiled at run time.

3. Suppose you want a certain ASP.NET function executed on Mouse Over over a certain button or textbox. Where do you add an event handler?
Answer: Every web control has an ability to add the attributes on client side which will execute on client side and run a client side script like a JavaScript function.
btnSubmit.Attributes.Add (“onMouseOver”,”someClientCode () ;”) //For on mouse over of a button
TextBox1.Attributes.Add(“onFocus”,“ClearText();”) //For on focus of a text box

4. Explain what a diffgram is and a good use for one?
Answer: The DiffGram is one of the two XML formats that you can use to render Dataset object contents to XML. For reading database data to an XML file to be sent to a Web Service.

5. What base class do all Web Forms inherit from?
Answer: The Page class.

6. Name two properties common in every validation control?
Answer: ControlToValidate and Text property.

7. What tags do you need to add within the Data grid tags to bind columns manually?
Answer: Set AutoGenerateColumns Property to false on the Data grid tag

8. What tag do you use to add a hyperlink column to the Data Grid?
Answer: asp: HyperLinkColumn

9. What is the transport protocol you use to call a Web service?
Answer: SOAP (Simple Object Access Protocol) is the preferred protocol.

10. Where on the Internet would you look for Web services?
Answer: http://www.uddi.org

11. Which control would you use if you needed to make sure the values in two different controls matched?
Answer: CompareValidator Control

12. What are the assembly entry points? An Assembly can have how many entry points at a time?
Answer: An assembly can have only one entry point from DllMain, Win Main or Main.

13. What does an assembly contain?
Answer:
• Manifest – The metadata describing the information below.
• Assembly name – Aids in versioning and visibility scope.
• Version information – The version number is integrated into the assembly’s identity.
• Types – Boundaries and scopes of methods, classes, properties, events, attributes.
• Locale – Information describing language/culture.
• Cryptographic Hash – Public key encoded hash acting as version/security check.
• Security Permissions – The permissions within the assembly determine the permissions that can be granted for all aspects of the assembly contents.

14. What does an assembly manifest contains?
Answer: It contains assembly name, version number (major.minor.build.revision) and culture Information. It also specifies the strong name information, which is useful for shared assemblies, and list of files, an assembly contains. It also provides information for type references in an assembly and other referenced assemblies.

15. Which tool is used to deploy an assembly, so as the assembly is available to all the application?
Answer: The GacUtil.exe is the tool, which allows us to add any assembly to the windows GAC.

16. How many catch statements can be associated with single try statement?
Answer: There can be a zero or more catch statement for each try statement. So it has not limit to the number of catch statement per try statement.

17. What is Console and System a Class/a Data Member/a routine/a namespace or a type?
Answer: Console is a class and System is namespace.

18. How many values can be returned from a method in C#?
Answer: Only one value can be returned from method, however you can use ref or out variable to change more than one value in called method.

19. How to declare a variable named this in C#, with data type string?
Answer: string @this;

20. Can we change the dimension of Array at run time like Array [3, 4]?
Answer: Yes, We can change only the first position of array dimension.

21. What keyword is used to accept a variable number of parameter in a method?
Answer: “params” keyword is used as to accept variable number of parameters.

22. What is a Namespace? What is the use of a namespace?
Answer: Namespaces are logical grouping of classes and other types in hierarchical structure. Namespaces are useful to avoid collision or ambiguity among the classes and type names. Another use of the namespace is to arrange a group of classes for a specific purpose.

23. What does a keyword using works for?
Answer: Using is just a convention or a short-cut method which allows us to access the classes in a namespace by referencing it once. So whenever we want use the classes or methods in them, we can avoid typing the entire namespace hierarchy. However it is not a good practice when there are likely chances to have name ambiguity or collision of class names.

24. What is Enums in C#?
Answer: Enums or Enumerators are used to declare a set of related constants (default start with 0); they are only available with primitive data types like int and short etc.

25. What is Delegates?
Answer: Delegates are a type-safe, object-oriented implementation of function pointers and are used in many situations where a component needs to call back to the component that is using it. Delegates are generally used as basis of events, which allow any delegate to easily be registered for as event.

26. Which are the namespaces that are imported automatically by Visual Studio in ASP.Net?
Answer: There are 7 namespaces which are imported automatically.

1.       System
2.       System. Collections
3.       System.IO
4.       System.web
5.       System.web.UI
6.       System.web.UI.HTMLControls
7.       System.web.UI.WebControls

27. Which namespaces are used for data access?

1.       System. Data
2.       System.Data.OleDB
3.       System.Data.SQLClient

28. What do you mean by boxing and un-boxing?
Answer: C# provides us with Value types and Reference Types. Value Types are stored on the stack and Reference types are stored on the heap. The conversion of value type to reference type is known as boxing and converting reference type back to the value type is known as un-boxing.
e.g.
int x = 10;
object o = x ; // Implicit boxing
object o = (object) x; // Explicit Boxing
x = o; // Implicit Un-Boxing
x = (int)o; // Explicit Un-Boxing

29. What are the different methods available under sqlcommand class to access the data?

1.       ExecuteReader –Used where one or more records are returned – SELECT Query.
2.       ExecuteNonQuery – Used where it affects a state of the table and no data is being queried – INSERT, UPDATE, DELETE, CREATE and SET queries.
3.       ExecuteScalar – Used where it returns a single record(a single value normally) – SQL Functions like MIN(), NAX()

30. What are the different types of Session state management options available with ASP.NET?
Answer: ASP.NET provides In-Process & Out-of-Process state management,
Also known as “In-Proc” and “Out-Proc”. In-Proc stores the session in memory of the web server, that is on the same server the ASP.Net page is.
On the other hand Out-Proc session state management stores the session data on external data source, which can be a SQL Server or Server State Service. Out-of-Process state management requires the objects stored in session, must be serializable.

31. What is Remoting? Give Example.
Answer: Remoting is a means by which one operating system process, or program, can communicate with another process. The two processes can exist on the same computer or on two computers connected by a LAN or the Internet. Web services are probably the best known type of remoting, but they are not the only option.

32. What is Marshalling?
Answer: Marshaling is a process of making an object in one process (the server) available to another process (the client). There are two ways to achieve the marshalling.

i. Marshal by value: the server creates a copy of the object passes the copy to the client. When a client makes a call to an object marshaled by value (MBV), the server creates an exact copy and sends that copy to the client. The client can then use the object’s data and executable functionality directly within its own process or application domain without making additional calls to the server. Objects that the application accesses frequently are best remoted using MBV.

ii. Marshal by reference: the client creates a proxy for the object and then uses the proxy to access the object. When a client makes a call to an object marshaled by reference (MBR), the .NET framework creates a proxy in the client’s application domain and the client uses that proxy to access the original object on the server. Large objects that the application accesses relatively infrequently are good candidates for MBR.

33. What is a Static class? What are its features?
Answer: Static class is a class which can be used or accessed without creating an instance of the class.
Important Features:

1.       Static class only contains static members and a private constructor.
2.       Static class cannot be instantiated.
3.       Static classes are sealed by default and therefore cannot be inherited.

34. What is sealed class? What are its features?
Answer: Sealed classes are those classes which can not be inherited and thus any sealed class member can not be derived in any other class. A sealed class cannot also be an abstract class.
In C# structs are implicitly sealed; therefore, they cannot be inherited.

35. Can we declare a method as sealed?
Answer: In C# a method can’t be declared as sealed. However when we override a method in a derived class, we can declare the overridden method as sealed. By declaring it as sealed, we can avoid further overriding of this method.
E.g.
Using System;
Class MyClass1
{
Public int x;
Public int y;
Public virtual void Method () {
Console.WriteLine (“virtual method”) ;}
}
Class My Class: MyClass1
{
Public override sealed void Method () {
Console.WriteLine (“sealed method”) ;}
}
Class Main Class
{Public static void Main () {
MyClass1 mC = new My Class ();
mC.x = 110;
mC.y = 150;
Console.WriteLine (“x = {0}, y = {1}”, mC.x, mC.y);
mC.Method () ;}
}

36. What is a Dataset?
Answer: A Dataset is an in memory representation of data loaded from any data source.

37. What is a Data Table?
Answer: A Data Table is a class in .NET Framework and in simple words a Data Table object represents a table from a database.

38. If you want to view an Assembly how to you go about it? What is ILDASM?
Answer: You can use the MSIL Disassembler (Ildasm.exe) to view Microsoft intermediate language (MSIL) information in a file. If the file being examined is an assembly, this information can include the assembly’s attributes, as well as references to other modules and assemblies. This information can be helpful in determining whether a file is an assembly or part of an assembly, and whether the file has references to other modules or assemblies.

39. Where is version information stored of an assembly?
Answer: The version number is stored in the assembly manifest along with other identity information, including the assembly name and public key, as well as information on relationships and identities of other assemblies connected with the application.

40. Is versioning applicable to private assemblies?
Answer: No

41. How to create a shared assembly or add an assembly to GAC?
Answer: There are several ways an assembly can be added to GAC.

1.       Use .msi installer designed to work with the global assembly cache.
2.       Use GACUtil.exe provided by the .NET Framework SDK.
3.       Use Windows Explorer to drag assemblies into the cache.


43. What is reflection?
Answer: 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. Reflection is ability to find information about types contained in an assembly at run time.

44. How can I produce an assembly?
Answer: Simply compile your class/module with the following command.
C#.Net – CSC /t: library yourclassname.cs
VB.Net – VBC /t: library yourmodulename.vb

45. What is an Application Domain? How they get created?
Answer: An Application Domain can be thought of as a lightweight processes controlled by the .Net runtime. Application Domains are usually created by hosts like Windows Shell, ASP.NET and IE. When you run a .NET application from the command-line, the host is the Shell. The Shell creates a new Application Domain for every application.

46. Do I have any control over the garbage collection algorithm?
Answer: Yes, we have a limited control over the GC algorithm, For example, the System.GC class exposes a Collect method – this forces the garbage collector to collect all unreferenced objects immediately.

47. What is a life span of a static variable?
Answer: A static variable’s life span is till the class is in memory.

48. What is a Page Life Cycle of an ASP.Net page?
Answer: There are various stages described as under.

1.       Init
2.       LoadViewState
3.       LoadPostBackData
4.       Load
5.       RaisePostBackDataChangedEvent
6.       RaisePostBackEvents
7.       Pre-Render
8.       SaveViewState
9.       Render
10.   Unload

49. Can the action attribute of a server-side <form>tag be set to a value and if not how can you possibly pass data from a form to a subsequent Page?
Answer: No, Assigning value will not work because will be overwritten at the time of rendering. We can assign value to it by register a startup script which will set the action value of form on client-side. On other hand we can use Server.Transfer or Response. Redirect.

50. How do you turn off cookies in one page of your asp.net application?
Answer: We may not use them at the max, However to allow the cookies or not, is client side functionality.

51. Which method do you use to redirect to user to another page without performing a round trip to Client?
Answer: Server. Transfer (“AnotherPage.aspx”).


53. Should Validation occur on Client/Server Side for Date Input?
Answer: Both. Client-side reduces extra round-trip. Server-Side ensures prevention against hacking and failure against automated requests.

54. What are the web form events?
Answer:

1.       The first event that occurs in the life of a Web Form is the Init event. This is raised so that we can have initialization code for the page. The controls on the page are not yet created at this point. This event is raised once for each user of the page.
2.       The Load event follows the Init event. Subsequently, it is raised each time the page is requested. When this event is raised, all child controls of the Web Form are loaded and accessible. You should be able to retrieve data and populate the controls so that they can render themselves on the page when sent back to the client.
3.       The PreRender event happens just before the page is rendered and sent back to the client. We don’t often handle this event; however, it depends on the situation.
4.       The last event in the life of a Web Form is the Unload event. This happens when the page is unloaded from memory. Final cleanup should be done here.


i will appreciate your comments and time for using my blog.
"Necessity is the mother of Invention".