Concepts -IIII

Difference between stored procedure and function

1) Procedure can return zero or n values whereas function can return one value which is mandatory.
2) Procedures can have input, output parameters for it whereas functions can have only input parameters.
3) Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
4) Functions can be called from procedure whereas procedures cannot be called from function.
5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6) We can go for transaction management in procedure whereas we can't go in function.
7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

Difference between Abstract and Interface

Abstract Class:

-Abstract class provides a set of rules to implement next class
-Rules will be provided through abstract methods
-Abstract method does not contain any definition
-While inheriting abstract class all abstract methods must be override
-If a class contains at least one abstract method then it must be declared as an “Abstract Class”
-Abstract classes cannot be instantiated (i.e. we cannot create objects), but a reference can be created
-Reference depends on child class object’s memory
-Abstract classes are also called as “Partial abstract classes”
-Partial abstract class may contain functions with body and functions without body
-If a class contains all functions without body then it is called as “Fully Abstract Class” (Interface)

Interface:

-If a class contains all abstract methods then that class is known as “Interface”
-Interfaces support like multiple inheritance
-In interface all methods r public abstract by default
-Interfaces r implementable
-Interfaces cannot be instantiated, but a reference can be created

Index types in SQL Server

Clustered Index

Only 1 allowed per table physically rearranges the data in the table to confirm to the index constraints for use on columns that are frequently searched for ranges of data for use on columns with low selectivity.

Non-Clustered Index

Up to 249 allowed per table creates a separate list of key values with pointers to the location of the data in the data pages For use on columns that are searched for single values

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Included Column Index (New in SQL Server 2005)

In SQL Server 2005, the functionality of non-clustered indexes is extended by adding non-key columns to the leaf level of the non-clustered index. Non-key columns can help to create cover indexes. By including non-key columns, you can create non-clustered indexes that cover more queries. The Database Engine does not consider non-key columns when calculating the number of index key columns or index key size. Non-key columns can be included in non-clustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. Another advantage is that using non-key column in index we can have index data types not allowed as index key columns generally.

In following example column Filename is varchar(400), which will increase the size of the index key bigger than it is allowed. If we still want to include in our cover index to gain performance we can do it by using the Keyword INCLUDE.

USE AdventureWorks
GOCREATE INDEX IX_Document_TitleON Production.Document(Title, Revision)
INCLUDE (FileName)

Non-key columns can be included only in non-clustered indexes. Columns can’t be defined in both the key column and they INCLUDE list. Column names can’t be repeated in the INCLUDE list. Non-key columns can be dropped from a table only after the non-key index is dropped first. For Included Column Index to exist there must be at least one key column defined with a maximum of 16 key columns and 1023 included columns.

Avoid adding unnecessary columns. Adding too many index columns, key or non-key as they will affect negatively on performance. Fewer index rows will fit on a page. This could create I/O increases and reduced cache efficiency. More disk space will be required to store the index. Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

Another example to test:

Create following Index on Database AdventureWorks in SQL SERVER 2005

USE AdventureWorks
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address(PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)
GO

Test the performance of following query before and after creating Index. The performance improvement is significant.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCodeBETWEEN '98000'AND '99999';
GO

Interview questions

What are differences between Array list and Hash table?

Ans: 1) Hash table store data as name, value pair. While in array only value is store.

2) To access value from hash table, you need to pass name. While in array, to access value, you need to pass index number.

3) you can store different type of data in hash table, say int, string etc. while in array you can store only similar type of data.

What are differences between system.stringbuilder and system.string?

The main difference is system.string is immutable and system.stringbuilder is a mutable. Append keyword is used in string builder but not in system.string.

Immutable means once we created we cannot modified. Suppose if we want give new value to old value simply it will discarded the old value and it will create new instance in memory to hold the new value.

What are the differences between Application object and session object?

Ans: The session object is used to maintain the session of each user. If one user enter in to the application then they get session id if he leaves from the application then the session id is deleted. If they again enter in to the application they get different session id.
But for application object the id is maintained for whole application.

What are the different types of indexes?

Ans: Two types of indexes are there one is clustered index and non-clustered index

How many types of memories are there in .net?

Ans: Two types of memories are there in .net stack memory and heap memory

Is it possible to set the session out time manually?

Ans: Yes we can set the session out time manually in web.config.

What are differences between function and stored procedure?

Ans:

1) Function returns only one value but procedure returns one or more than one value.

2) Function can be utilized in select statements but that is not possible in procedure.

3) Procedure can have an input and output parameters but function has only input parameters only.

4) Exceptions can be handled by try catch block in procedures but that is not possible in function.

What are the differences between Abstract and interface?

Ans: 1) Abstract cannot be instantiated but we can inherit. Interface it cannot be inherit it can be instantiate

2) Interface contain only declarations no definitions. Abstract contain declarations and definitions.

3) The class which contains only abstract methods is interface class. A class which contains abstract method is called abstract class

4) Public is default access specifier for interface we don’t have a chance to declare other specifiers. In abstract we have chance to declare with any access specifier

Can you Explain Page lifecycle in .net?

Can you Explain .NET architecture in .net?

What is the difference between primary key and unique key with not null?

Ans: There is no difference between primary key and unique key with not null.

What is boxing and unboxing concepts in .net?

Ans: Boxing is a process of converting value type into reference type

Unboxing is a process of converting reference type to value type.

What are the differences between value type and reference type?

Ans: Value type contain variable and reference type are notcontaining value directly in its memory.

Memory is allocated in managed heap in reference type and in value type memory allocated in stack. Reference type ex-class value type-struct, enumeration

Is it possible to host the website from desktop?

Ans: Yes

Why we go for page rendering in Asp.Net Page life cycle?

Ans: Browser understands an only html control that’s why in page rendering we will convert the aspx controls into html controls.

Write a sample query for self join?

Ans: Select e1.ename, e2.empid from emp e1, emp e2 where e1.empid=e2.mgrid;

Can we change the index of primary key on table?

Ans: No

How to change the name of the table or stored procedure in sql?

Ans: sp_renameoldtablenamenewtablename

For changing the column name

Sp_rename ‘tablename.[Oldcolumnname]’,’newcolumnname’,’Column’

Ex:sp_rename ‘tblemp.first’,’namechange’,’Column’

How to find out which index is defined on table?

Ans: sp_helpindextablename

Can you write the program to find the length of string without using library function?

Ans: for (int i=0; str[i]!=”\n”; i++)

{

Count++;

}

What is the difference between scope_identity() and current_identity()?

Ans: Scope_identity and current _identity both are similar and it will return the last identity value generated in the table.

Scope_Identity will return the identity value in table that is currently in scope

What are difference between GET and POST Methods?

Ans:

GET Method ():

1) Data is appended to the URL.
2) Data is not secret.
3) It is a single call system
4) Maximum data that can be sent is 256.
5) Data transmission is faster
6) this is the default method for many browsers

POST Method ():

1) Data is not appended to the URL.
2) Data is Secret
3) it is a two call system.
4) There is no Limit on the amount of data. That is characters any amount of data can be sent.
5) Data transmission is comparatively slow.
6) No default and should be explicitly specified.

What are difference between truncate and delete?

Ans: 1) Delete keep the lock over each row where Truncate keeps the lock on table not on all the row.
2) Counter of the Identity column is reset in Truncate where it is not reset in Delete.
3) Trigger is not fired in Truncate where as trigger is fired in Delete.

4) In TRUNCATE we cannot rollback.

5) In DELETE we can rollback

What is the difference Grid View and between Data Grid (Windows)?

Ans:

1) GridView Control Enables you to add sorting, paging and editing capabilities without writing any code.
2)GridView Control Automatically Supports paging by setting the ‘PagerSetting’ Property.The Page Setting Property supports four Modles

a. Numeric(by default)
b. Next Previous
c. NumericFirstLast
d. Next PreviousLast

3)It is Used in asp.net
4)GridView Supports RowUpdating and RowUpdated Events.
5)GidView is Capable of Pre-Operations and Post-Operations.
6)GridView Has EditTemplates for this control
7)It has AutoFormat

DataGrid(Windows)

1)DataGid Control raises single Event for operations
2)DataGird Supports the SortCommand Events that occur when a column is Soted.
3)DataGrid Supports UpdataCommand Event that occurs when the UpdateButton is clicked for an item in the grid.
4)DataGrid is used in Windows GUI Application.
5)Itdoesnot have EditTemplates for this control
6)It doesnot have AutoFormat

If I write System.exit (0); at the end of the try block, will the finally block still execute?

Ans: No in this case the finally block will not execute because when you say system.exit(0),the control immediately goes out of the program, and thus finally never executes.

What are the different levels of State management in ASP.NET?

Ans:

State management is the process by which you maintain state and page information over multiple requests for the same or different pages.

There are 2 types State Management:

1. Client – Side State Management
This stores information on the client's computer by embedding the information into a Web page, a uniform resource locator (url), or a cookie. The techniques available to store the state information at the client end are listed down below:

a. View State – Asp.Net uses View State to track the values in the Controls. You can add custom values to the view state. It is used by the Asp.net page framework to automatically save the values of the page and of each control just prior to rendering to the page. When the page is posted, one of the first tasks performed by page processing is to restore view state.

b. Control State – If you create a custom control that requires view state to work properly, you should use control state to ensure other developers don’t break your control by disabling view state.

c. Hidden fields – Like view state, hidden fields store data in an HTML form without displaying it in the user's browser. The data is available only when the form is processed.

d. Cookies – Cookies store a value in the user's browser that the browser sends with every page request to the same server. Cookies are the best way to store state data that must be available for multiple Web pages on a web site.

e. Query Strings - Query strings store values in the URL that are visible to the user. Use query strings when you want a user to be able to e-mail or instant message state data with a URL.

2. Server – Side State Management
a. Application State - Application State information is available to all pages, regardless of which user requests a page.

b. Session State – Session State information is available to all pages opened by a user during a single visit.

Both application state and session state information is lost when the application restarts. To persist user data between application restarts, you can store it using profile properties.

Abstract Class:

Abstract class is a class which can’t be instantiate. Class should have “Abstract” key word with the name. In any one of the method of class having abstract method with in it, then it should be define as abstract class. The class which derived the abstract class should have definition of the abstract method. These classes which derived the abstract class and implement the abstract methods call concrete class.
Abstract class may have the definition of function or may not. Below is the simple example of an abstract class

public abstract alassAbstractStudent
    {
        String Roll
        {
            get;
            set;
        }

        String FirstName
        {
            get;
            set;
        }

        String LastName
        {
            get;
            set;
        }


        Public String GetStudentDetails()
             {

                  // Implementation of Method
             }

        public String SaveStudentDetails ()
            {
                  // Implementation of Method
             }

        public abstract String CalculateWage();

    }

So, the class having one abstract method so we need to mention the class as "abstract" .

Difference between Abstract Class and Interface?

Abstract class is a class which can’t be instantiated and which can have methods with definition as well as declaration also. This can be inherited.

As for Example:

public abstract class AbstractStudent
    {
        String Roll
        {
            get;
            set;
        }

        String FirstName
        {
           get;
           set;
        }

        String LastName
        {
           get;
            set;
        }

        Public String GetStudentDetails()
            {
                 // Implementation of Method
             }

        public String SaveStudentDetails ()
            {
                  // Implementation of Method
            }

        public abstract String CalculateWage();

    }

Interface can only contain the methods declaration and can be implemented in the class.

As for Example:

Public interface IStudnet
    {
        String Roll
        {
           get;
            set;
        }

        String FirstName
        {
            get;
            set;
        }

        String LastName
        {
            get;
            set;
        }

        String GetStudentDetails();
        String SaveStudentDetails ();
    }

Below are the few main difference between Abstract Class and Interface

a.    In abstract class method can have definition as well as declaration also. But Interface should have only definition.
b.   All the Methods are Public as default and don’t have any access Modifier Controls in interface, whereas for abstract class we can have access modifier for methods.
c.   Abstract class can have constructor or destructor, whereas interface not.
d.   Abstract class can’t be part of multiple inheritance and we can implement multiple interface.

What do you mean by String objects are immutable?

String objects are immutable as its state cannot be modified once created. Every time when we perform any operation like add, copy, replace, and case conversion or when we pass a string object as a parameter to a method a new object will be created.

Example:

String str = "ABC";

str.Replace("A","X");

Here Replace() method will not change data that "str" contains, instead a new string object is created to hold data "XBC" and the reference to this object is returned by Replace() method.

So in order to point str to this object we need to write below line.

str = str.Replace("A","X");

Now the new object is assigned to the variable str. earlier object that was assigned to str will take care by garbage collector as this one is no longer in used.

What is dll hell problem in .NET and how it will solve?

Ans: Dll hell, is kind of conflict that occurred previously, due to the lack of version supportability of dll for (within) an application

.NET Framework provides operating system with a global assembly cache. This cache is a repository for all the .net components that are shared globally on a particular machine. When a .net component installed onto the machine, the global assembly cache looks at its version, its public key and its language information and creates a strong name for the component. The component is then registered in the repository and indexed by its strong name, so there is no confusion between the different versions of same component, or DLL

What is a Partial class?

Ans: Instead of defining an entire class, you can split the definition into multiple classes by using partial class keyword. When the application compiled, c# compiler will group all the partial classes together and treat them as a single class. There are a couple of good reasons to use partial classes. Programmers can work on different parts of classes without needing to share same physical file

Ex:

Public partial class employee

{

Public void somefunction()

{

}
}

Public partial class employee

{

Public void function ()

{

}

What is difference between constants, read-only and, static?

Constants: The value can’t be changed

Read-only: The value will be initialized only once from the constructor of the class.

Static: Value can be initialized once.

What is the cross page post backing?

Asp.Net 2.0 fixed this with built-in features that allowed us to easily send information from one page to another.

Button control has property PostBackUrlthat can be set to URL of any page in our ASP.Net WebSite where we want to transfer all form values to.
Along with that Asp.Net 2.0 Page class has a property PreviousPagethat allows us to get reference to the Page object that initiated the postback (in other words to get the actual reference to the Page object of the aspx page on which user clicked the Submit button on a HTML form).

So for example lets create two sample pages in our Web Application:

SourcePage.aspx
DestinationPage.aspx

In SoucePage in Html form we will put two TextBox controls (one for First Name and one for Last Name) and one Button component and set its PostBackUrl property to "~/DestinationPage.aspx".

SourcePage.aspx:

<formid="form1"runat="server">

<div>

First Name: <asp:TextBoxID="FirstName"runat="server"></asp:TextBox><br />

Last Name: <asp:TextBoxID="LastName"runat="server"></asp:TextBox><br /><br />

<asp:ButtonID="Button1"runat="server"Text="Submit To Destination Page"PostBackUrl="~/CrossPagePostbacks/DestinationPage.aspx" />

</div>

</form>

When our user clicks the Submit button, all the values from the HTML Form on SourcePage.aspx will be transfered to the DestinationPage.aspx and we will also be able to get reference to the SourcePage.aspx in our DestinationPage with the PreviousPageproperty like this:

So in our DestinationPage.aspx.cs code-behind we can easily access two TextBox controls on SourcePage.aspx and show them in two label controls like this:

protectedvoidPage_Load(object sender, EventArgs e)

{

// first check if we had a cross page postback

if ( (PreviousPage != null) && (PreviousPage.IsCrossPagePostBack) )

{

PagepreviousPage = PreviousPage;

TextBoxfirstName = (TextBox)previousPage.FindControl("FirstName");

TextBoxlastName = (TextBox)previousPage.FindControl("LastName");

// we can now use the values from TextBoxes and display them in two Label controls..

labelFirstName.Text = firstName.Text;

labelLastName.Text = lastName.Text;

}

You probably noticed that we first checked if PreviousPageproperty of current page (DestinationPage.aspx) is NOT NULL, this is done to avoid running our code in case that user opens our DestinationPage.aspx directly, without running a cross page postback.

Also here we checked the anotherPreviousPage property called IsCrossPagePostBack to see if we really had a CrossPagePostback.
(If Server.Transfer is used to redirect to this page, IsCrossPagePostBack property will be set to FALSE.

TIP: We can be completely sure that we have a realCrossPagePostback ONLY IF:

Page.PreviousPageis NOT NULL,
PreviousPage.IsCrossPagePostbackis true

This important to check to avoid errors in code.

Now this is very useful and i'm sure you are eager to use this in your next project. But wait, we are not over yet!

Finding the controls on PreviousPage with FindControl method and type-casting them from object to their real type is a little messy.
It feels like there must be a better solution for this!

And here it is: We can use the <%@ PreviousPageType %> directive in the header of our DestinationPage.aspx like this

<%@PreviousPageTypeVirtualPath="~/SourcePage.aspx"%>

to declare our previous page type, and then we can access Public properties of the PreviousPage without typecasting.
Now all we need to do is to create some public properties on our SourcePage.aspx.cs to expose data/Controls we want to the destionation page:

publicpartialclassSourcePage : System.Web.UI.Page

{

publicstringFormFirstName

{

get { returnFirstName.Text; }

}

publicstringFormLastName

{

get { returnLastName.Text; }

}

And then we can change the Page_Load code in our DestinationPage.aspx to much cleaner code like this:

protectedvoidPage_Load(object sender, EventArgs e)

{

// first check if we had a cross page postback

if ( (PreviousPage != null) && (PreviousPage.IsCrossPagePostBack) )

{

SourcePageprevPage = PreviousPage;

// we can now use the values from textboxes and display them in two Label controls..

labelFirstName.Text = prevPage.FormFirstName;

labelLastName.Text = prevPage.FormLastName;

}

SourcePage type used in the code is offcourse name of the partial class defined is SourcePage.aspx.cs that inherits System.Web.UI.Page that is automatically created for us when we created new WebForm in VisualStudio.

This code is much cleaner and easier to follow, there is no ugly typecasting, just simple property values to use to retrieve the data from previous page.

When should you use Abstract Class vs Interface while programming?

Ans: When we want that sub class must implement all the methods of base class. In such a situation we will implement the interface. In the other hand when we want only some method of base class in our sub class then use base class as abstract class.

What is the difference between application exception and system exception?

Ans: The difference between application exception and system exception is that system exceptions are thrown by CLR and application exceptions are thrown by applications.

What is the difference between authorization and authentication?

Ans: Authorization is a process of allowing or denying resources to particular user or record

Declaration of authorization is

</authorization>

Sometimes authorization allows the unauthorized persons at that time we will use

Authentication means

Authentication is a process where we identify the credentials of user i.e. username, password and create an identity to mention user as an authenticated.

What is the use of n-tier architecture and 3-tier architecture?

Check this article for 3-tier architecture 3 tier architecture example in asp.net

How to get the version of the assembly?

Ans:lbltxt.text=Assembly. GetExecutingAssembly().GetName().Version.ToString();

What is the location of Global Assembly Cache on the system?

Ans: c:\Windows\assembly

What is the serialization?

Ans: Serialization is a process of converting object into a stream of bites.

What is synchronization?

Ans: The mechanism needed to block one thread access to the data. If the data is being accessed by another thread.

Synchronization can be accessed by using system.monitor class

A monitor class methods are enter, exit, pulse for this lock statement is also used

Suppose if we need to synchronize some data at that time we need to place that data in this block

Lock

{

}

Whatever the data has been placed into the lock block that data has been blocked

What are the thread priority levels?

Ans: Thread priority levels are five types

0 - Zero level

1 - Below Normal

2 - Normal

3 - Above Normal

4 - Highest

By Default priority level is 2

What is the difference between .tostring(), Convert.tostring()?

Ans: The basic difference between them is “Convert” function handles NULLS while
“.ToString()” does not it will throw a NULL reference exception error. So as a good coding
practice using “convert” is always safe.

What is Collation?

Ans: Collation refers to a set of rules that determine how the data is sorted and compared.

What is the difference between Primary key and unique key?

Ans: Primary key does not allow the null values but unique key allows one null value.

Primary key will create clustered index on column but unique key will create non-clustered index by default.

How many web.config files are there in 1 project?

Ans: There might be multiple web.config files for a single project depending on the hierarchy of folders inside the root folder of the project, so for each folder we can use one web.config file

What is the difference between throw and throw ex?

What is the difference between view state and hidden field?

Ans: viewstate is secured hidden field is insecure

Viewstate will store large amount of data but hidden filed will store small amount of data.

What is the difference between binary serialization and xml serialization?

What is the Difference between read only and constant variables?

Ans: Read only can assign the values at runtime only.

Constant will assign the values at compile time only.

We cannot modify the both variable values.

What is static keyword in .Net?

Ans: Static is same as constant variable but we can change the value of static variable and we can access the variables without creating any instances

What is the use of business logic layer in 3-tier architecture in .net?

Ans: Though a web site could talk to the data access layer directly, it usually goes through another layer called the business layer. The business layer is vital in that it validates the input conditions before calling a method from the data layer. This ensures the data input is correct before proceeding, and can often ensure that the outputs are correct as well. This validation of input is called business rules, meaning the rules that the business layer uses to make “judgments” about the data.

However, business rules don’t only apply to data validation; these rules apply to any calculations or any other action that takes place in the business layer. Normally, it’s best to put as much logic as possible in the business layer, which makes this logic reusable across applications.

One of the best reasons for reusing logic is that applications that start off small usually grow in functionality. For instance, a company begins to develop a web site, and as they realize their business needs, they later decide to add a smart client application and windows service to supplement the web site. The business layer helps move logic to a central layer for “maximum reusability.”

Whats an assembly? Assemblies are the building blocks of .NET Framework applications; they form the fundamental unit of deployment, version control, reuse, activation scoping, and security permissions. An assembly is a collection of types and resources that are built to work together and form a logical unit of functionality. An assembly provides the common language runtime with the information it needs to be aware of type implementations. To the runtime, a type does not exist outside the context of an assembly.
Describe the difference between inline and code behind – which is best in a loosely coupled solution? ASP.NET supports two modes of page development: Page logic code that is written inside <script runat=server> blocks within an .aspx file and dynamically compiled the first time the page is requested on the server. Page logic code that is written within an external class that is compiled prior to deployment on a server and linked “behind” the .aspx file at run time.
Explain what a diffgramis, and a good use for one? A DiffGram is an XML format that is used to identify current and original versions of data elements. The DataSet uses the DiffGram format to load and persist its contents, and to serialize its contents for transport across a network connection. When a DataSet is written as a DiffGram, it populates the DiffGram with all the necessary information to accurately recreate the contents, though not the schema, of the DataSet, including column values from both the Original and Current row versions, row error information, and row order.
Where would you use an iHTTPModule, and what are the limitations of anyapproach you might take in implementing one? One of ASP.NET’s most useful features is the extensibility of the HTTP pipeline, the path that data takes between client and server. You can use them to extend your ASP.NET applications by adding pre- and post-processing to each HTTP request coming into your application. For example, if you wanted custom authentication facilities for your application, the best technique would be to intercept the request when it comes in and process the request in a custom HTTP module.
In what order do the events of an ASPX page execute. As a developer is it important to undertsand these events? Every Page object (which your .aspx page is) has nine events, most of which you will not have to worry about in your day to day dealings with ASP.NET. The three that you will deal with the most are: Page_Init, Page_Load, Page_PreRender.
Which method do you invoke on the DataAdapter control to load your generated dataset with data?

System.Data.Common.DataAdapter.Fill(System.Data.DataSet);
If my DataAdapter is sqlDataAdapter and my DataSet is dsUsers then it is called this way:
sqlDataAdapter.Fill(dsUsers);

Which template must you provide, in order to display data in a Repeater control? ItemTemplate
How can you provide an alternating color scheme in a Repeater control?

AlternatingItemTemplate Like the ItemTemplate element, but rendered for every other
row (alternating items) in the Repeater control. You can specify a different appearance
for the AlternatingItemTemplate element by setting its style properties.

What property must you set, and what method must you call in your code, in order to bind the data from some data source to the Repeater control?

You must set the DataMember property which Gets or sets the specific table in the DataSource to bind to the control and the DataBind method to bind data from a source to a server control. This method is commonly used after retrieving a data set through a database query.

What base class do all Web Forms inherit from?
System.Web.UI.Page
What method do you use to explicitly kill a user’s session?

The Abandon method destroys all the objects stored in a Session object and releases their resources.
If you do not call the Abandon method explicitly, the server destroys these objects when the session times out.

Syntax: Session.Abandon

How do you turn off cookies for one page in your site?
Use the Cookie.Discard Property which Gets or sets the discard flag set by the server. When true, this
property instructs the client application not to save the Cookie on the user’s hard disk when a session ends.
Which two properties are on every validation control?ControlToValidate&ErrorMessage properties
How do you create a permanent cookie? Setting the Expires property to MinValue means that the Cookie never expires.
Which method do you use to redirect the user to another page without performing a round trip to the client?Server.transfer()
What is the transport protocol you use to call a Web service? SOAP. Transport Protocols: It is essential for the acceptance of Web Services that they are based on established Internet infrastructure. This in fact imposes the usage of of the HTTP, SMTP and FTP protocols based on the TCP/IP family of transports. Messaging Protocol: The format of messages exchanged between Web Services clients and Web Services should be vendor neutral and should not carry details about the technology used to implement the service. Also, the message format should allow for extensions and different bindings to specific transport protocols. SOAP and ebXML Transport are specifications which fulfill these requirements. We expect that the W3C XML Protocol Working Group defines a successor standard.
True or False: A Web service can only be written in .NET. False.
What does WSDL stand for? Web Services Description Language
What property do you have to set to tell the grid which page to go to when using the Pager object?
Where on the Internet would you look for Web services? UDDI repositaries like uddi.microsoft.com, IBM UDDI node, UDDI Registries in Google Directory, enthusiast sites like XMethods.net.
What tags do you need to add within the asp:datagrid tags to bind columns manually? Column tag and an ASP:databound tag.
How is a property designated as read-only? In VB.NET:

Public ReadOnly Property PropertyNameAsReturnType
Get            ‘Your Property Implementation goes in here
End Get
End Property
in C#
publicreturntypePropertyName
{
   get{
           //property implementation goes here
   }
   // Do not write the set implementation
}

Which control would you use if you needed to make sure the values in two different controls matched? Use the CompareValidator control to compare the values
of 2 different controls.
True or False: To test a Web service you must create a windows application or Web application to consume this service? False.

What is Microsoft ADO.NET?
ADO.NET is one of the component in the Microsoft.NET framework which contains following features to Windows, web and distributed applications.
i. Data Access to the applications from Database in connected (Data reader object) and disconnected (Data set and data table) model.
ii. Modify the data in database from application.
What are the Benefits of ADO.Net?
ADO.Net offers following Benefits
Interoperability:
XML Format is one of the best formats for Interoperability.ADO.NET supports to transmit the data using XML format.
Scalability:
ADO.NET works on Dataset that can represent a whole database or even a data table as a disconnected object and thereby eliminates the problem of the constraints of number of databases being connected. In this way scalability is achieved.
Performance:
The performance in ADO.NET is higher in comparison to ADO that uses COM marshalling.
Programmability:
ADO.Net Data components in Visual studio help to do easy program to connect to the database.
Explain different connection objects used in ADO.NET?
Sqlconnection object used to connect to a Microsoft SQL Server database. SQL database connections use the SqlDataAdapter object to perform commands and return data.
OracleConnection object used to connect to Oracle databases. Oracle database connections use the OracleDataAdapter object to perform commands and return data. This connection object was introduced in Microsoft .NET Framework version 1.1.
OleDbConnection object used to connect to a Microsoft Access or third-party database, such as MySQL. OLE database connections use the OleDbDataAdapter object to perform commands and return data.
What are the different steps to access a database through ADO.NET?

Create a connection to the database using a connection object.
Open the Database Connection.
Invoke a command to create a Dataset object using an adapter object.
Use the Dataset object in code to display data or to change items in the database.
Invoke a command to update the database from the Dataset object using an adapter object.
Close the database connection.

What is the difference between Data Reader and Data Adapter?

Data Reader is read only forward only and much faster than Data Adapter.
If you use Data Reader you have to open and close connection explicitly where as if you use Data Adapter the connection is automatically opened and closed.
Data Reader expects connection to database to perform any operation on data where as Data Adapter is disconnected

What is the difference between Execute Reader, Execute Scalar and Execute Non Query methods?
Execute Reader
Execute Reader will be used to return the set of rows, on execution of SQL Query or Stored procedure using command object.
Execute Scalar
Execute Scalar will be used to return the single value, on execution of SQL Query or Stored procedure using command object.
Execute Non Query
If the command or stored procedure performs INSERT, DELETE or UPDATE operations, then we use Execute Non Query method. Execute Non Query method returns an integer specifying the number of rows inserted, deleted or updated.

1. Differences between DLL and EXE?
.exe
1.These are outbound file.
2.Only one .exe file exists per application.
3. .Exe cannot be shared with other applications.

.dll
1.These are inbundfile .
2.Many .dll files may exists in one application.
3. .dll can be shared with other applications.

2. Can an assembly have EXE?
Assembly is nothing but single deployment and self describing. Yes Assembly can have dll/exe.

3. Can a DLL be changed to an EXE?
In short, the answer is no. Unlike an EXE file which contains a single entry point (typically WinMain() or simply main() depending on the type of exe file), a DLL file is a library of functions intended to be linked into a running application. A DLL file can have a nearly infinite (it’s based on file size and such) possible entry points.

4. Compare & contrast rich client (smart clients or Windows-based) & browser-based Web application
When implementing a client/server architecture you need to determine if it will be the client or the server that handles the bulk of the workload. By client, we mean the application that runs on a PC or workstation and relies on a server to perform some operations.
In last week’s Did You Know article we discussed the differences between thick clients (also called fat clients) and thin clients in terms of hardware. The terms thick client and thin client, however, have double meanings, as thick and thin also are used to describe the applications or software. In this article we take a look at the terms thick and thin as related to client application software.

5. Compare Client server application with n-Tier application
All web applications are N-Tier architectures. An N-Tier architecture is really a Client-Server architecture combined with the Layered architecture. The reason why I combine Client-Server and N-Tier here is because they are very much related.
A Client-Server system is one in which the server performs some kind of service that is used by many clients. The clients take the lead in the communication. The basic Client-Server architecture has 2 tiers (Client and Server).
6. Can a try block have more than one catch block?
Yes, It can have as many as you want. It allows you to catch very specific exceptions.

7. Can a try block have nested try blocks?
Yes perfectly legal, but not always it is useful.

8. How do you load an assembly at runtime?Here is the sample code, hope it will help
Loader.AssemblyLoaderassLoader = null;
object[] parms = { AssemblyName }; // string AssebmlyName

assLoader = (Loader.AssemblyLoader)domain.CreateInstanceFromAndUnwrap(
“yourAssemply.dll”, “Loader.AssemblyLoader”, true, bindings, null, parms,
null, null, null);

9. If I am writing in a language like VB or C++, what are the procedures to be followed to support .NET?
If you want your VB6 or C++ code to be used with .NET code your code should be CLS compliant….

What is CLS-Compliant?

One of the greatest advantages of .net is that you can write code in a variety of different languages and it all translates to MSIL at the end and is run by the CLR. That’s a wonderful feature, however languages themselves differ in the kind of features that they support. For example C# supports operator overloading while some languages do not.

To resolve this Microsoft introduced the Common Language Specification. Basically, if your code is CLS Compliant it means that all your code is guaranteed to be callable by any .Net module. You can accomplish this by using the CLSCompliant assembly attribute:

1: [assembly: CLSCompliant(true)]
CLS is a subset of the Common Type System / Common Language Runtime. For more information on making your assemblies CLS Compliant check out this article on MSDN http://msdn2.Microsoft.com/en-us/library/bhc3fa7f(VS.71).aspx

10. How do you view the methods and members of a DLL?
Go to visual studio 2003/2005 command prompt and type “ildasm”. It will open a window. In that window load urdll, it will show all the methods and members.

11. What is shadowing?
Shadowing is either through scope or through inheritance. Shadowing through inheritance is hiding a method of a base class and providing a new implementation for the same. This is the default when a derived class writes an implementation of a method of base class which is not declared as overridden in the base class. This also serves the purpose of protecting an implementation of a new method against subsequent addition of a method with the same name in the base class.’shadows’ keyword is recommended although not necessary since it is the default.

12. What are the collections you’ve used?
.Net collections that you have used.

Questions : 1	What is Code Access Security (CAS)? How does CAS work?
Answers : 1	CAS is the part of the .NET security model that determines whether or not a piece of code is allowed to run, and what resources it can use when it is running. For example, it is CAS that will prevent a .NET web applet from formatting your hard disk. The CAS security policy revolves around two key concepts - code groups and permissions. Each .NET assembly is a member of a particular code group, and each code group is granted the permissions specified in a named permission set. For example, using the default security policy, a control downloaded from a web site belongs to the 'Zone - Internet' code group, which adheres to the permissions defined by the 'Internet' named permission set. (Naturally the 'Internet' named permission set represents a very restrictive range of permissions.)

Questions : 2	What are object pooling and connection pooling and difference? Where do we set the Min and Max Pool size for connection pooling?
Answers : 2	Object pooling is a COM+ service that enables you to reduce the overhead of creating each object from scratch. When an object is activated, it is pulled from the pool. When the object is deactivated, it is placed back into the pool to await the next request. You can configure object pooling by applying the ObjectPoolingAttribute attribute to a class that derives from the System.EnterpriseServices.ServicedComponent class. Object pooling lets you control the number of connections you use, as opposed to connection pooling, where you control the maximum number reached. Following are important differences between object pooling and connection pooling: 1. Creation. When using connection pooling, creation is on the same thread, so if there is nothing in the pool, a connection is created on your behalf. With object pooling, the pool might decide to create a new object. However, if you have already reached your maximum, it instead gives you the next available object. This is crucial behavior when it takes a long time to create an object, but you do not use it for very long. 2. Enforcement of minimums and maximums. This is not done in connection pooling. The maximum value in object pooling is very important when trying to scale your application. You might need to multiplex thousands of requests to just a few objects. (TPC/C benchmarks rely on this.) COM+ object pooling is identical to what is used in .NET Framework managed SQL Client connection pooling. For example, creation is on a different thread and minimums and maximums are enforced.

Questions : 3	What is a WebService and what is the underlying protocol used in it? Namespace?
Answers : 3	Web Services are applications delivered as a service on the Web. Web services allow for programmatic access of business logic over the Web. Web services typically rely on XML-based protocols, messages, and interface descriptions for communication and access. Web services are designed to be used by other programs or applications rather than directly by end user. Programs invoking a Web service are called clients. SOAP over HTTP is the most commonly used protocol for invoking Web services.

Questions : 4	What is serialization in .NET? What are the ways to control serialization?
Answers : 4	Serialization is the process of converting an object into a stream of bytes. Deserialization is the opposite process of creating an object from a stream of bytes. Serialization/Deserialization is mostly used to transport objects (e.g. during remoting), or to persist objects (e.g. to a file or database).Serialization can be defined as the process of storing the state of an object to a storage medium. During this process, the public and private fields of the object and the name of the class, including the assembly containing the class, are converted to a stream of bytes, which is then written to a data stream. When the object is subsequently deserialized, an exact clone of the original object is created. 1. Binary serialization preserves type fidelity, which is useful for preserving the state of an object between different invocations of an application. For example, you can share an object between different applications by serializing it to the clipboard. You can serialize an object to a stream, disk, memory, over the network, and so forth. Remoting uses serialization to pass objects "by value" from one computer or application domain to another. 2. XML serialization serializes only public properties and fields and does not preserve type fidelity. This is useful when you want to provide or consume data without restricting the application that uses the data. Because XML is an open standard, it is an attractive choice for sharing data across the Web. SOAP is an open standard, which makes it an attractive choice.

Questions : 5	Explain SOAP, WSDL, UDDI in brief.Namespace?
Answers : 5	SOAP:-SOAP is an XML-based messaging framework specifically designed for exchanging formatted data across the Internet, for example using request and reply messages or sending entire documents. SOAP is simple, easy to use, and completely neutral with respect to operating system, programming language, or distributed computing platform.After SOAP became available as a mechanism for exchanging XML messages among enterprises (or among disparate applications within the same enterprise), a better way was needed to describe the messages and how they are exchanged. WSDL :-The Web Services Description Language (WSDL) is a particular form of an XML Schema, developed by Microsoft and IBM for the purpose of defining the XML message, operation, and protocol mapping of a web service accessed using SOAP or other XML protocol. WSDL defines web services in terms of "endpoints" that operate on XML messages. The WSDL syntax allows both the messages and the operations on the messages to be defined abstractly, so they can be mapped to multiple physical implementations. The current WSDL spec describes how to map messages and operations to SOAP 1.1, HTTP GET/POST, and MIME. WSDL creates web service definitions by mapping a group of endpoints into a logical sequence of operations on XML messages. The same XML message can be mapped to multiple operations (or services) and bound to one or more communications protocols (using "ports"). UDDI :-The Universal Description, Discovery, and Integration (UDDI) framework defines a data model (in XML) and SOAP APIs for registration and searches on business information, including the web services a business exposes to the Internet. UDDI is an independent consortium of vendors, founded by Microsoft, IBM, and Ariba, for the purpose of developing an Internet standard for web service description registration and discovery. Microsoft, IBM, and Ariba also are hosting the initial deployment of a UDDI service, which is conceptually patterned after DNS (the Internet service that translates URLs into TCP addresses). UDDI uses a private agreement profile of SOAP (i.e. UDDI doesn't use the SOAP serialization format because it's not well suited to passing complete XML documents (it's aimed at RPC style interactions). The main idea is that businesses use the SOAP APIs to register themselves with UDDI, and other businesses search UDDI when they want to discover a trading partner, for example someone from whom they wish to procure sheet metal, bolts, or transistors. The information in UDDI is categorized according to industry type and geographical location, allowing UDDI consumers to search through lists of potentially matching businesses to find the specific one they want to contact. Once a specific business is chosen, another call to UDDI is made to obtain the specific contact information for that business. The contact information includes a pointer to the target business's WSDL or other XML schema file describing the web service that the target business publishes. .

Questions : 6	What is Method Overriding? How to override a function in C#?
Answers : 6	An override method provides a new implementation of a member inherited from a base class. The method overridden by an override declaration is known as the overridden base method. The overridden base method must have the same signature as the override method. Use the override modifier to modify a method, a property, an indexer, or an event. You cannot override a non-virtual or static method. The overridden base method must be virtual, abstract, or override.

Questions : 7	7. How can we check if all validation and controls are valid or proper?How can we force to run all validation control to run?
Answers : 7	By using Page.IsValid() property we can check if all validation and controls are valid or proper by using Page.Validate we can force to run all validation control to run

Questions : 8	What is the difference between Server.Transfer and Response.Redirect? Why would I choose one over the other?
Answers : 8	Server.Transfer transfers page processing from one page directly to the next page without making a round-trip back to the client's browser. This provides a faster response with a little less overhead on the server. Server.Transfer does not update the clients url history list or current url. Response.Redirect is used to redirect the user's browser to another page or site. This performas a trip back to the client where the client's browser is redirected to the new page. The user's browser history list is updated to reflect the new address.

Questions : 9	What is JIT (just in time)? how it works?
Answers : 9	Before Microsoft intermediate language (MSIL) can be executed, it must be converted by a .NET Framework just-in-time (JIT) compiler to native code, which is CPU-specific code that runs on the same computer architecture as the JIT compiler. Rather than using time and memory to convert all the MSIL in a portable executable (PE) file to native code, it converts the MSIL as it is needed during execution and stores the resulting native code so that it is accessible for subsequent calls. The runtime supplies another mode of compilation called install-time code generation. The install-time code generation mode converts MSIL to native code just as the regular JIT compiler does, but it converts larger units of code at a time, storing the resulting native code for use when the assembly is subsequently loaded and executed. As part of compiling MSIL to native code, code must pass a verification process unless an administrator has established a security policy that allows code to bypass verification. Verification examines MSIL and metadata to find out whether the code can be determined to be type safe, which means that it is known to access only the memory locations it is authorized to access.

Questions : 10	Difference between DataReader and DataAdapter / DataSet and DataAdapter?
Answers : 10	You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Using the DataReader can increase application performance and reduce system overhead because only one row at a time is ever in memory. After creating an instance of the Command object, you create a DataReader by calling Command.ExecuteReader to retrieve rows from a data source, as shown in the following example. SqlDataReadermyReader = myCommand.ExecuteReader(); You use the Read method of the DataReader object to obtain a row from the results of the query. while (myReader.Read()) Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1)); myReader.Close(); The DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the data source. It can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application. The DataSet represents a complete set of data including related tables, constraints, and relationships among the tables. The methods and objects in a DataSet are consistent with those in the relational database model. The DataSet can also persist and reload its contents as XML and its schema as XML Schema definition language (XSD) schema. The DataAdapter serves as a bridge between a DataSet and a data source for retrieving and saving data. The DataAdapter provides this bridge by mapping Fill, which changes the data in the DataSet to match the data in the data source, and Update, which changes the data in the data source to match the data in the DataSet. If you are connecting to a Microsoft SQL Server database, you can increase overall performance by using the SqlDataAdapter along with its associated SqlCommand and SqlConnection. For other OLE DB-supported databases, use the DataAdapter with its associated OleDbCommand and OleDbConnection objects.

Questions : 11	Differences between dataset.clone and dataset.copy?
Answers : 11	Clone - Copies the structure of the DataSet, including all DataTable schemas, relations, and constraints. Does not copy any data. Copy - Copies both the structure and data for this DataSet.

Questions : 12	What is State Management in .Net and how many ways are there to maintain a state in .Net? What is view state?
Answers : 12	Web pages are recreated each time the page is posted to the server. In traditional Web programming, this would ordinarily mean that all information associated with the page and the controls on the page would be lost with each round trip. To overcome this inherent limitation of traditional Web programming, the ASP.NET page framework includes various options to help you preserve changes — that is, for managing state. The page framework includes a facility called view state that automatically preserves property values of the page and all the controls on it between round trips. However, you will probably also have application-specific values that you want to preserve. To do so, you can use one of the state management options. Client-Based State Management Options: View State Hidden Form Fields Cookies Query Strings Server-Based State Management Options Application State Session State Database Support

Questions : 13	Difference between web services &remoting? Namespace?
Answers : 13	ASP.NET Web Services .NET Remoting Protocol Can be accessed only over HTTP Can be accessed over any protocol (including TCP, HTTP, SMTP and so on) State Management Web services work in a stateless environment Provide support for both stateful and stateless environments through Singleton and SingleCall objects Type System Web services support only the datatypes defined in the XSD type system, limiting the number of objects that can be serialized. Using binary communication, .NET Remoting can provide support for rich type system Interoperability Web services support interoperability across platforms, and are ideal for heterogeneous environments. .NET remoting requires the client be built using .NET, enforcing homogenous environment. Reliability Highly reliable due to the fact that Web services are always hosted in IIS Can also take advantage of IIS for fault isolation. If IIS is not used, application needs to provide plumbing for ensuring the reliability of the application. Extensibility Provides extensibility by allowing us to intercept the SOAP messages during the serialization and deserialization stages. Very extensible by allowing us to customize the different components of the .NET remoting framework. Ease-of-Programming Easy-to-create and deploy. Complex to program.

Questions : 14	What is MSIL, IL?
Answers : 14	When compiling to managed code, the compiler translates your source code into Microsoft intermediate language (MSIL), which is a CPU-independent set of instructions that can be efficiently converted to native code. MSIL includes instructions for loading, storing, initializing, and calling methods on objects, as well as instructions for arithmetic and logical operations, control flow, direct memory access, exception handling, and other operations. Microsoft intermediate language (MSIL) is a language used as the output of a number of compilers and as the input to a just-in-time (JIT) compiler. The common language runtime includes a JIT compiler for converting MSIL to native code.

Questions : 15	What is strong name?
Answers : 15	A name that consists of an assembly's identity—its simple text name, version number, and culture information (if provided)—strengthened by a public key and a digital signature generated over the assembly.

Questions : 16	What is exception handling?
Answers : 16	When an exception occurs, the system searches for the nearest catch clause that can handle the exception, as determined by the run-time type of the exception. First, the current method is searched for a lexically enclosing try statement, and the associated catch clauses of the try statement are considered in order. If that fails, the method that called the current method is searched for a lexically enclosing try statement that encloses the point of the call to the current method. This search continues until a catch clause is found that can handle the current exception, by naming an exception class that is of the same class, or a base class, of the run-time type of the exception being thrown. A catch clause that doesn't name an exception class can handle any exception. Once a matching catch clause is found, the system prepares to transfer control to the first statement of the catch clause. Before execution of the catch clause begins, the system first executes, in order, any finally clauses that were associated with try statements more nested that than the one that caught the exception. Exceptions that occur during destructor execution are worth special mention. If an exception occurs during destructor execution, and that exception is not caught, then the execution of that destructor is terminated and the destructor of the base class (if any) is called. If there is no base class (as in the case of the object type) or if there is no base class destructor, then the exception is discarded.

Questions : 17	What is the managed and unmanaged code in .net?
Answers : 17	The .NET Framework provides a run-time environment called the Common Language Runtime, which manages the execution of code and provides services that make the development process easier. Compilers and tools expose the runtime's functionality and enable you to write code that benefits from this managed execution environment. Code that you develop with a language compiler that targets the runtime is called managed code; it benefits from features such as cross-language integration, cross-language exception handling, enhanced security, versioning and deployment support, a simplified model for component interaction, and debugging and profiling services.

Questions : 18	What is the namespace threading in .net?
Answers : 18	System.Threading.Thread How to encode string stringss="pervej"; string encode=Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(ss)); Response.Write(encode);

Database (DBMS) interview questions and answers are below

Questions : 1	What is database or database management systems (DBMS)? and - What’s the difference between file and database? Can files qualify as a database?
Answers : 1	Database provides a systematic and organized way of storing, managing and retrieving from collection of logically related information. Secondly the information has to be persistent, that means even after the application is closed the information should be persisted. Finally it should provide an independent way of accessing data and should not be dependent on the application to access the information. Main difference between a simple file and database that database has independent way (SQL) of accessing information while simple files do not File meets the storing, managing and retrieving part of a database but not the independent way of accessing data. Many experienced programmers think that the main difference is that file can not provide multi-user capabilities which a DBMS provides. But if we look at some old COBOL and C programs where file where the only means of storing data, we can see functionalities like locking, multi-user etc provided very efficiently. So it’s a matter of debate if some interviewers think this as a main difference between files and database accept it… going in to debate is probably loosing a job.

Questions : 2	What is SQL ?
Answers : 2	SQL stands for Structured Query Language.SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database.

Questions : 3	What’s difference between DBMS and RDBMS ?
Answers : 3	DBMS provides a systematic and organized way of storing, managing and retrieving from 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 These relations are defined by using “Foreign Keys” in any RDBMS.Many DBMS companies claimed there DBMS product was a 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) fulfills all the twelve CODD rules and are considered as truly RDBMS.

Questions : 4	What are CODD rules?
Answers : 4	In 1969 Dr. E. F. Codd laid down some 12 rules which a DBMS should adhere in order to get the logo of a true RDBMS. Rule 1: Information Rule. "All information in a relational data base is represented explicitly at the logical level and in exactly one way - by values in tables." Rule 2: Guaranteed access Rule. "Each and every datum (atomic value) in a relational data base 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 exact location of field values. But if a DBMS is truly RDBMS you can access the value by specifying the table name, field name, for instance Customers.Fields [‘Customer Name’]. 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.". Rule 4: Dynamic on-line catalog based on the relational model. "The data base 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) Rule 6: .View updating Rule "All views that are theoretically updatable are also updatable by the system." 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." 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." 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." Rule 10: Integrity independence. "Integrity constraints specific to a particular relational data base must be definable in the relational data sub-language and storable in the catalog, not in the application programs." Rule 11: Distribution independence. "A relational DBMS has distribution independence." 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)."

Questions : 5	What are E-R diagrams?
Answers : 5	E-R diagram also termed as Entity-Relationship diagram shows relationship between various tables in the database. .

Questions : 6	How many types of relationship exist in database designing?
Answers : 6	There are three major relationship models:- One-to-one One-to-many Many-to-many

Questions : 7	7.What is normalization? What are different type of normalization?
Answers : 7	There is set of rules that has 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. Following are the three normal forms :- First Normal Form For a table to be in first normal form, data must be broken up into the smallest un possible.In addition to breaking data up into the smallest meaningful values, tables first normal form should not contain repetitions groups of fields. Second Normal form The second normal form states that each field in a multiple field primary keytable must be directly related to the entire primary key. Or in other words,each non-key field should be a fact about all the fields in the primary key. Third normal form A non-key field should not depend on other Non-key field.

Questions : 8	What is denormalization ?
Answers : 8	Denormalization is the process of putting one fact in numerous places (its 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 database.

Questions : 9	Can you explain Fourth Normal Form and Fifth Normal Form ?
Answers : 9	In fourth normal form it should not contain two or more independent multi-v about an entity and it should satisfy “Third Normal form”. Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy.

Questions : 10	Have you heard about sixth normal form?
Answers : 10	If we want relational system in conjunction with time we use sixth normal form. At this moment SQL Server does not supports it directly.

Questions : 11	What are DML and DDL statements?
Answers : 11	DML stands for Data Manipulation Statements. They update data values in table. Below are the most important DDL statements:- =>SELECT - gets data from a database table => UPDATE - updates data in a table => DELETE - deletes data from a database table => INSERT INTO - inserts new data into a database table DDL stands for Data definition Language. They change structure of the database objects like table, index etc. Most important DDL statements are as shown below:- =>CREATE TABLE - creates a new table in the database. =>ALTER TABLE – changes table structure in database. =>DROP TABLE - deletes a table from database => CREATE INDEX - creates an index => DROP INDEX - deletes an index

Questions : 12	How do we select distinct values from a table?
Answers : 12	DISTINCT keyword is used to return only distinct values. Below is syntax:- Column age and Table pcdsEmp SELECT DISTINCT age FROM pcdsEmp

Questions : 13	What is Like operator for and what are wild cards?
Answers : 13	LIKE operator is used to match patterns. A "%" sign is used to define the pattern. Below SQL statement will return all words with letter "S" SELECT * FROM pcdsEmployee WHERE EmpName LIKE 'S%' Below SQL statement will return all words which end with letter "S" SELECT * FROM pcdsEmployee WHERE EmpName LIKE '%S' Below SQL statement will return all words having letter "S" in between SELECT * FROM pcdsEmployee WHERE EmpName LIKE '%S%' "_" operator (we can read as “Underscore Operator”). “_” operator is the character defined at that point. In the below sample fired a query Select name from pcdsEmployee where name like '_s%' So all name where second letter is “s” is returned.

Questions : 14	Can you explain Insert, Update and Delete query?
Answers : 14	Insert statement is used to insert new rows in to table. Update to update existing data in the table. Delete statement to delete a record from the table. Below code snippet for Insert, Update and Delete :- INSERT INTO pcdsEmployee SET name='rohit',age='24'; UPDATE pcdsEmployee SET age='25' where name='rohit'; DELETE FROM pcdsEmployee WHERE name = 'sonia';

Questions : 15	What is order by clause?
Answers : 15	ORDER BY clause helps to sort the data in either ascending order to descending order. Ascending order sort query SELECT name,age FROM pcdsEmployee ORDER BY age ASC Descending order sort query SELECT name FROM pcdsEmployee ORDER BY age DESC

Questions : 16	What is the SQL " IN " clause?
Answers : 16	SQL IN operator is used to see if the value exists in a group of values. For instance the below SQL checks if the Name is either 'rohit' or 'Anuradha' SELECT * FROM pcdsEmployee WHERE name IN ('Rohit','Anuradha') Also you can specify a not clause with the same. SELECT * FROM pcdsEmployee WHERE age NOT IN (17,16)

Questions : 17	Can you explain the between clause?
Answers : 17	Below SQL selects employees born between '01/01/1975' AND '01/01/1978' as per mysql SELECT * FROM pcdsEmployee WHERE DOB BETWEEN '1975-01-01' AND '2011-09-28'

Questions : 18	we have an employee salary table how do we find the second highest from it?
Answers : 18	below Sql Query find the second highest salary SELECT * FROM pcdsEmployeeSalary a WHERE (2=(SELECT COUNT(DISTINCT(b.salary)) FROM pcdsEmployeeSalary b WHERE b.salary>=a.salary))

Questions : 19	What are different types of joins in SQL?
Answers : 19	INNER JOIN Inner join shows matches only when they exist in both tables. Example in the below SQL there are two tables Customers and Orders and the inner join in made on Customers.Customerid and Orders.Customerid. So this SQL will only give you result with customers who have orders. If the customer does not have order it will not display that record. SELECT Customers., Orders. FROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerID LEFT OUTER JOIN Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table. SELECT Customers., Orders. FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID RIGHT OUTER JOIN Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table. SELECT Customers., Orders. FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

Questions : 20	What is “CROSS JOIN”? or What is Cartesian product?
Answers : 20	“CROSS JOIN” or “CARTESIAN PRODUCT” combines all rows from both tables. Number of rows will be product of the number of rows in each table. In real life scenario I can not imagine where we will want to use a Cartesian product. But there are scenarios where we would like permutation and combination probably Cartesian would be the easiest way to achieve it.

Questions : 21	How to select the first record in a given set of rows?
Answers : 21	Select top 1 * from sales.salesperson

Questions : 22	What is the default “-SORT ” order for a SQL?
Answers : 22	ASCENDING

Questions : 23	What is a self-join?
Answers : 23	If we want to join two instances of the same table we can use self-join.

Questions : 24	What’s the difference between DELETE and TRUNCATE ?
Answers : 24	Following are difference between them: =>>DELETE TABLE syntax logs the deletes thus making the delete operations low. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table. So TRUNCATE table is faster as compared to delete table. =>>DELETE table can have criteria while TRUNCATE can not. =>> TRUNCATE table can not have triggers.

Questions : 25	What’s the difference between “UNION” and “UNION ALL” ?
Answers : 25	UNION SQL syntax is used to select information from two tables. But it selects only distinct records from both the table. , while UNION ALL selects all records from both the tables.

Questions : 26	What are cursors and what are the situations you will use them?
Answers : 26	SQL statements are good for set at a time operation. So it is good at handling set of data. But there are scenarios where we want to update row depending on certain criteria. we will loop through all rows and update data accordingly. There’s where cursors come in to picture.

Questions : 27	What is " Group by " clause?
Answers : 27	“Group by” clause group similar data so that aggregate values can be derived.

Questions : 28	What is the difference between “HAVING” and “WHERE” clause?
Answers : 28	“HAVING” clause is used to specify filtering criteria for “GROUP BY”, while “WHERE” clause applies on normal SQL.

Questions : 29	What is a Sub-Query?
Answers : 29	A query nested inside a SELECT statement is known as a subquery and is an alternative to complex join statements. A subquery combines data from multiple tables and returns results that are inserted into the WHERE condition of the main query. A subquery is always enclosed within parentheses and returns a column. A subquery can also be referred to as an inner query and the main query as an outer query. JOIN gives better performance than a subquery when you have to check for the existence of records. For example, to retrieve all EmployeeID and CustomerID records from the ORDERS table that have the EmployeeID greater than the average of the EmployeeID field, you can create a nested query, as shown: SELECT DISTINCT EmployeeID, CustomerID FROM ORDERS WHERE EmployeeID> (SELECT AVG(EmployeeID) FROM ORDERS)

Questions : 30	What are Aggregate and Scalar Functions?
Answers : 30	Aggregate and Scalar functions are in built function for counting and calculations. Aggregate functions operate against a group of values but returns only one value. AVG(column) :- Returns the average value of a column COUNT(column) :- Returns the number of rows (without a NULL value) of a column COUNT(*) :- Returns the number of selected rows MAX(column) :- Returns the highest value of a column MIN(column) :- Returns the lowest value of a column Scalar functions operate against a single value and return value on basis of the single value. UCASE(c) :- Converts a field to upper case LCASE(c) :- Converts a field to lower case MID(c,start[,end]) :- Extract characters from a text field LEN(c) :- Returns the length of a text

Questions : 31	Can you explain the SELECT INTO Statement?
Answers : 31	SELECT INTO statement is used mostly to create backups. The below SQL backsup the Employee table in to the EmployeeBackUp table. One point to be noted is that the structure of pcdsEmployeeBackup and pcdsEmployee table should be same. SELECT * INTO pcdsEmployeeBackup FROM pcdsEmployee

Questions : 32	What is a View?
Answers : 32	View is a virtual table which is created on the basis of the result set returned by the select statement. CREATE VIEW [MyView] AS SELECT * from pcdsEmployee where LastName = 'singh' In order to query the view SELECT * FROM [MyView]

Questions : 33	What is SQlinjection ?
Answers : 33	It is a Form of attack on a database-driven Web site in which the attacker executes unauthorized SQL commands by taking advantage of insecure code on a system connected to the Internet, bypassing the firewall. SQL injection attacks are used to steal information from a database from which the data would normally not be available and/or to gain access to an organization’s host computers through the computer that is hosting the database. SQL injection attacks typically are easy to avoid by ensuring that a system has strong input validation. As name suggest we inject SQL which can be relatively dangerous for the database. Example this is a simple SQL SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x' Now somebody does not put “x” as the input but puts “x ; DROP TABLE members;”. So the actual SQL which will execute is :- SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x' ; DROP TABLE members; Think what will happen to your database.

Questions : 34	What is Data Warehousing ?
Answers : 34	Data Warehousing is a process in which the data is stored and accessed from central location and is meant to support some strategic decisions. Data Warehousing is not a requirement for Data mining. But just makes your Data mining process more efficient. Data warehouse is a collection of integrated, subject-oriented databases designed to support the decision-support functions (DSF), where each unit of data is relevant to some moment in time.

Questions : 35	What are Data Marts?
Answers : 35	Data Marts are smaller section of Data Warehouses. They help data warehouses collect data. For example your company has lot of branches which are spanned across the globe. Head-office of the company decides to collect data from all these branches for anticipating market. So to achieve this IT department can setup data mart in all branch offices and a central data warehouse where all data will finally reside.

Questions : 36	What are Fact tables and Dimension Tables ? What is Dimensional Modeling and Star Schema Design
Answers : 36	When we design transactional database we always think in terms of normalizing design to its least form. But when it comes to designing for Data warehouse we think more in terms of denormalizing the database. Data warehousing databases are designed using Dimensional Modeling. Dimensional Modeling uses the existing relational database structure and builds on that. There are two basic tables in dimensional modeling:- Fact Tables. Dimension Tables. Fact tables are central tables in data warehousing. Fact tables have the actual aggregate values which will be needed in a business process. While dimension tables revolve around fact tables. They describe the attributes of the fact tables.

Questions : 37	What is Snow Flake Schema design in database? What’s the difference between Star and Snow flake schema?
Answers : 37	Star schema is good when you do not have big tables in data warehousing. But when tables start becoming really huge it is better to denormalize. When you denormalize star schema it is nothing but snow flake design. For instance below customeraddress table is been normalized and is a child table of Customer table. Same holds true for Salesperson table.

Questions : 38	What is ETL process in Data warehousing? What are the different stages in “Data warehousing”?
Answers : 38	ETL (Extraction, Transformation and Loading) are different stages in Data warehousing. Like when we do software development we follow different stages like requirement gathering, designing, coding and testing. In the similar fashion we have for data warehousing. Extraction:- In this process we extract data from the source. In actual scenarios data source can be in many forms EXCEL, ACCESS, Delimited text, CSV (Comma Separated Files) etc. So extraction process handle’s the complexity of understanding the data source and loading it in a structure of data warehouse. Transformation:- This process can also be called as cleaning up process. It’s not necessary that after the extraction process data is clean and valid. For instance all the financial figures have NULL values but you want it to be ZERO for better analysis. So you can have some kind of stored procedure which runs through all extracted records and sets the value to zero. Loading:- After transformation you are ready to load the information in to your final data warehouse database.

Questions : 39	What is Data mining ?
Answers : 39	Data mining is a concept by which we can analyze the current data from different perspectives and summarize the information in more useful manner. It’s mostly used either to derive some valuable information from the existing data or to predict sales to increase customer market. There are two basic aims of Data mining:- Prediction: - From the given data we can focus on how the customer or market will perform. For instance we are having a sale of 40000 $ per month in India, if the same product is to be sold with a discount how much sales can the company expect. Summarization: - To derive important information to analyze the current business scenario. For example a weekly sales report will give a picture to the top management how we are performing on a weekly basis?

Questions : 40	Compare Data mining and Data Warehousing ?
Answers : 40	“Data Warehousing” is technical process where we are making our data centralized while “Data mining” is more of business activity which will analyze how good your business is doing or predict how it will do in the future coming times using the current data. As said before “Data Warehousing” is not a need for “Data mining”. It’s good if you are doing “Data mining” on a “Data Warehouse” rather than on an actual production database. “Data Warehousing” is essential when we want to consolidate data from different sources, so it’s like a cleaner and matured data which sits in between the various data sources and brings then in to one format. “Data Warehouses” are normally physical entities which are meant to improve accuracy of “Data mining” process. For example you have 10 companies sending data in different format, so you create one physical database for consolidating all the data from different company sources, while “Data mining” can be a physical model or logical model. You can create a database in “Data mining” which gives you reports of net sales for this year for all companies. This need not be a physical database as such but a simple query.

Questions : 41	What are indexes? What are B-Trees?
Answers : 41	Index makes your search faster. So defining indexes to your database will make your search faster.Most of the indexing fundamentals use “B-Tree” or “Balanced-Tree” principle. It’s not a principle that is something is created by SQL Server or ORACLE but is a mathematical derived fundamental.In order that “B-tree” fundamental work properly both of the sides should be balanced.

Questions : 42	I have a table which has lot of inserts, is it a good database design to create indexes on that table? Insert’s are slower on tables which have indexes, justify it?or Why do page splitting happen?
Answers : 42	All indexing fundamentals in database use “B-tree” fundamental. Now whenever there is new data inserted or deleted the tree tries to become unbalance. Creates a new page to balance the tree. Shuffle and move the data to pages. So if your table is having heavy inserts that means it’s transactional, then you can visualize the amount of splits it will be doing. This will not only increase insert time but will also upset the end-user who is sitting on the screen. So when you forecast that a table has lot of inserts it’s not a good idea to create indexes.

Questions : 43	What are the two types of indexes and explain them in detail? or What’s the difference between clustered and non-clustered indexes?
Answers : 43	There are basically two types of indexes:- Clustered Indexes. Non-Clustered Indexes. In clustered index the non-leaf level actually points to the actual data.In Non-Clustered index the leaf nodes point to pointers (they are rowid’s) which then point to actual data

eRevolution

Search This Blog

Concepts -IIII

Labels

Popular posts from this blog

Creating package in Oracle Database using Toad For Oracle

Resolving 'Setup Account Privileges' error while installing SQL Server

Creating Oracle stored Procedures using TOAD for Oracle