Top 20 SQL Interview Questions & Answers

SQL is a language for accessing and manipulating database standardized by ANSI. To be successful with database-centric applications (which includes most of the applications Data Warehousing domain), one must be strong enough in SQL. In this article, we will learn more about SQL by breaking the subject in the form of several question-answer sessions commonly asked in Interviewes.

SET UP OF SAMPLE DATA FOR PRACTICING SQL

For the purpose of our demonstration, we will primarily use two database tables with just a few records - EMPLOYEE table and DEPT table. EMPLOYEE table will contain 10 records pertaining to 10 employees with funny sounding names of an imaginary organization and DEPT or Department table will contain 5 departments of that organization. Click here to download the DDL/INSERT statements for this data if you want to practice the below SQLs in your personal computer

Contents of these tables are not same with Oracle emp and dept tables!!

What is the difference between inner and outer join? Explain with example.

Inner Join

Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition (predicate).

Inner join returns rows when there is at least one match in both tables

If none of the record matches between two tables, then INNER JOIN will return a NULL set. Below is an example of INNER JOIN and the resulting set.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE emp.dept_id = dept.id

Department	Employee
HR	Inno
HR	Privy
Engineering	Robo
Engineering	Hash
Engineering	Anno
Engineering	Darl
Marketing	Pete
Marketing	Meme
Sales	Tomiti
Sales	Bhuti

Outer Join

Outer Join can be full outer or single outer

Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).

Notice in our record set that there is no employee in the department 5 (Logistics). Because of this if we perform inner join, then Department 5 does not appear in the above result. However in the below query we perform an outer join (dept left outer join emp), and we can see this department.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)

Department	Employee
HR	Inno
HR	Privy
Engineering	Robo
Engineering	Hash
Engineering	Anno
Engineering	Darl
Marketing	Pete
Marketing	Meme
Sales	Tomiti
Sales	Bhuti
Logistics

The (+) sign on the emp side of the predicate indicates that emp is the outer table here. The above SQL can be alternatively written as below (will yield the same result as above):

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept LEFT OUTER JOIN EMPLOYEE emp
ON dept.id = emp.dept_id  

What is the difference between JOIN and UNION?

SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.

UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables.

SELECT * FROM EMP1
UNION
SELECT * FROM EMP2;

What is the difference between UNION and UNION ALL?

UNION and UNION ALL both unify for add two structurally similar data sets, but UNION operation returns only the unique records from the resulting data set whereas UNION ALL will return all the rows, even if one or more rows are duplicated to each other.

In the following example, I am choosing exactly the same employee from the emp table and performing UNION and UNION ALL. Check the difference in the result.

SELECT * FROM EMPLOYEE WHERE ID = 5
UNION ALL
SELECT * FROM EMPLOYEE WHERE ID = 5

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
5.0	2.0	2.0	Anno	80.0	01-Feb-2012
5.0	2.0	2.0	Anno	80.0	01-Feb-2012

SELECT * FROM EMPLOYEE WHERE ID = 5
UNION 
SELECT * FROM EMPLOYEE WHERE ID = 5

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
5.0	2.0	2.0	Anno	80.0	01-Feb-2012

What is the difference between WHERE clause and HAVING clause?

WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

To understand this, consider this example.
Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause here:

SELECT * FROM DEPT WHERE ID > 3

ID	NAME
4	Sales
5	Logistics

Next, suppose we want to see only those Departments where Average salary is greater than 80. Here the condition is associated with a non-static aggregated information which is “average of salary”. We will need to use HAVING clause here:

SELECT dept.name DEPARTMENT, avg(emp.sal) AVG_SAL
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)
GROUP BY dept.name
HAVING AVG(emp.sal) > 80

DEPARTMENT	AVG_SAL
Engineering	90

As you see above, there is only one department (Engineering) where average salary of employees is greater than 80.

What is the difference among UNION, MINUS and INTERSECT?

UNION combines the results from 2 tables and eliminates duplicate records from the result set.

MINUS operator when used between 2 tables, gives us all the rows from the first table except the rows which are present in the second table.

INTERSECT operator returns us only the matching or common rows between 2 result sets.

To understand these operators, let’s see some examples. We will use two different queries to extract data from our emp table and then we will perform UNION, MINUS and INTERSECT operations on these two sets of data.

UNION

SELECT * FROM EMPLOYEE WHERE ID = 5
UNION 
SELECT * FROM EMPLOYEE WHERE ID = 6

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
5	2	2.0	Anno	80.0	01-Feb-2012
6	2	2.0	Darl	80.0	11-Feb-2012

MINUS

SELECT * FROM EMPLOYEE
MINUS
SELECT * FROM EMPLOYEE WHERE ID > 2

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
1		2	Hash	100.0	01-Jan-2012
2	1	2	Robo	100.0	01-Jan-2012

INTERSECT

SELECT * FROM EMPLOYEE WHERE ID IN (2, 3, 5)
INTERSECT
SELECT * FROM EMPLOYEE WHERE ID IN (1, 2, 4, 5)

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
5	2	2	Anno	80.0	01-Feb-2012
2	1	2	Robo	100.0	01-Jan-2012

What is Self Join and why is it required?

Self Join is the act of joining one table with itself.

Self Join is often very useful to convert a hierarchical structure into a flat structure

In our employee table example above, we have kept the manager ID of each employee in the same row as that of the employee. This is an example of how a hierarchy (in this case employee-manager hierarchy) is stored in the RDBMS table. Now, suppose if we need to print out the names of the manager of each employee right beside the employee, we can use self join. See the example below:

SELECT e.name EMPLOYEE, m.name MANAGER
FROM EMPLOYEE e, EMPLOYEE m
WHERE e.mgr_id = m.id (+)

EMPLOYEE	MANAGER
Pete	Hash
Darl	Hash
Inno	Hash
Robo	Hash
Tomiti	Robo
Anno	Robo
Privy	Robo
Meme	Pete
Bhuti	Tomiti
Hash

The only reason we have performed a left outer join here (instead of INNER JOIN) is we have one employee in this table without a manager (employee ID = 1). If we perform inner join, this employee will not show-up.

How can we transpose a table using SQL (changing rows to column or vice-versa) ?

The usual way to do it in SQL is to use CASE statement or DECODE statement.

How to generate row number in SQL Without ROWNUM

Generating a row number – that is a running sequence of numbers for each row is not easy using plain SQL. In fact, the method I am going to show below is not very generic either. This method only works if there is at least one unique column in the table. This method will also work if there is no single unique column, but collection of columns that is unique. Anyway, here is the query:

SELECT name, sal, (SELECT COUNT(*)  FROM EMPLOYEE i WHERE o.name >= i.name) row_num
FROM EMPLOYEE o
order by row_num

NAME	SAL	ROW_NUM
Anno	80	1
Bhuti	60	2
Darl	80	3
Hash	100	4
Inno	50	5
Meme	60	6
Pete	70	7
Privy	50	8
Robo	100	9
Tomiti	70	10

The column that is used in the row number generation logic is called “sort key”. Here sort key is “name” column. For this technique to work, the sort key needs to be unique. We have chosen the column “name” because this column happened to be unique in our Employee table. If it was not unique but some other collection of columns was, then we could have used those columns as our sort key (by concatenating those columns to form a single sort key).

Also notice how the rows are sorted in the result set. We have done an explicit sorting on the row_num column, which gives us all the row numbers in the sorted order. But notice that name column is also sorted (which is probably the reason why this column is referred as sort-key). If you want to change the order of the sorting from ascending to descending, you will need to change “>=” sign to “<=” in the query.

As I said before, this method is not very generic. This is why many databases already implement other methods to achieve this. For example, in Oracle database, every SQL result set contains a hidden column called ROWNUM. We can just explicitly select ROWNUM to get sequence numbers.

How to select first 5 records from a table?

This question, often asked in many interviews, does not make any sense to me. The problem here is how do you define which record is first and which is second. Which record is retrieved first from the database is not deterministic. It depends on many uncontrollable factors such as how database works at that moment of execution etc. So the question should really be – “how to select any 5 records from the table?” But whatever it is, here is the solution:

In Oracle,

SELECT * 
FROM EMP
WHERE ROWNUM <= 5;

In SQL Server,

SELECT TOP 5 * FROM EMP;

Generic solution,

I believe a generic solution can be devised for this problem if and only if there exists at least one distinct column in the table. For example, in our EMP table ID is distinct. We can use that distinct column in the below way to come up with a generic solution of this question that does not require database specific functions such as ROWNUM, TOP etc.

SELECT  name 
FROM EMPLOYEE o
WHERE (SELECT count(*) FROM EMPLOYEE i WHERE i.name < o.name) < 5

name
Inno
Anno
Darl
Meme
Bhuti

I have taken “name” column in the above example since “name” is happened to be unique in this table. I could very well take ID column as well.

In this example, if the chosen column was not distinct, we would have got more than 5 records returned in our output.

Do you have a better solution to this problem? If yes, post your solution in the comment.

What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?

ROWNUM is a pseudo column present in Oracle database returned result set prior to ORDER BY being evaluated. So ORDER BY ROWNUM does not work.

ROW_NUMBER() is an analytical function which is used in conjunction to OVER() clause wherein we can specify ORDER BY and also PARTITION BY columns.

Suppose if you want to generate the row numbers in the order of ascending employee salaries for example, ROWNUM will not work. But you may use ROW_NUMBER() OVER() like shown below:

SELECT name, sal, row_number() over(order by sal desc) rownum_by_sal
FROM EMPLOYEE o

name	Sal	ROWNUM_BY_SAL
Hash	100	1
Robo	100	2
Anno	80	3
Darl	80	4
Tomiti	70	5
Pete	70	6
Bhuti	60	7
Meme	60	8
Inno	50	9
Privy	50	10

What are the differences among ROWNUM, RANK and DENSE_RANK?

ROW_NUMBER assigns contiguous, unique numbers from 1.. N to a result set.

RANK does not assign unique numbers—nor does it assign contiguous numbers. If two records tie for second place, no record will be assigned the 3rd rank as no one came in third, according to RANK. See below:

SELECT name, sal, rank() over(order by sal desc) rank_by_sal
FROM EMPLOYEE o

name	Sal	RANK_BY_SAL
Hash	100	1
Robo	100	1
Anno	80	3
Darl	80	3
Tomiti	70	5
Pete	70	5
Bhuti	60	7
Meme	60	7
Inno	50	9
Privy	50	9

DENSE_RANK, like RANK, does not assign unique numbers, but it does assign contiguous numbers. Even though two records tied for second place, there is a third-place record. See below:

SELECT name, sal, dense_rank() over(order by sal desc) dense_rank_by_sal
FROM EMPLOYEE o

name	Sal	DENSE_RANK_BY_SAL
Hash	100	1
Robo	100	1
Anno	80	2
Darl	80	2
Tomiti	70	3
Pete	70	3
Bhuti	60	4
Meme	60	4
Inno	50	5
Privy	50	5

Asp.Net: insert images into database and how to retrieve an...

Asp.Net: insert images into database and how to retrieve an...: insert images into database and how to retrieve and bind images to gridview using asp.net save and retrieve images from database using c#...

INSERT IMAGE INTO MS SQL DATABSE USING ASP.NET

Here i am posting both design view and cod behind.........

1.Design Side Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AccessUpload.aspx.cs" Inherits="AccessUpload" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>

<style type="text/css">
body{font-family: tahoma;font-size: 80%;}
.row{clear: both;}
.label{float: left;text-align: right;width: 150px;padding-right: 5px;}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<div class="row">
<span class="label"><label for="FirstName">First Name: </label></span>
<asp:TextBox ID="FirstName" runat="server"></asp:TextBox>
</div> 
<div class="row">
<span class="label"><label for="Surname">Surname: </label></span>
<asp:TextBox ID="Surname" runat="server"></asp:TextBox>
</div> 
<div class="row"> 
<span class="label"><label for="Photo">Photo: </label></span>
<asp:FileUpload ID="PhotoUpload" runat="server" />
</div>
<div class="row">
<span class="label"><label for="Resume">Resume: </label></span>
<asp:FileUpload ID="ResumeUpload" runat="server" />
</div>
<div class="row">
<span class="label">&nbsp;</span>
<asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" />
</div> 
</div>
</form>
</body>
</html>

2.Code Side
protected void Button1_Click(object sender, EventArgs e)
{
if (PhotoUpload.HasFile && ResumeUpload.HasFile)
{
Stream photoStream = PhotoUpload.PostedFile.InputStream;
int photoLength = PhotoUpload.PostedFile.ContentLength;
string photoMime = PhotoUpload.PostedFile.ContentType;
string photoName = Path.GetFileName(PhotoUpload.PostedFile.FileName);
byte[] photoData = new byte[photoLength - 1];
photoStream.Read(photoData, 0, photoLength);

Stream resumeStream = ResumeUpload.PostedFile.InputStream;
int resumeLength = ResumeUpload.PostedFile.ContentLength;
string resumeMime = ResumeUpload.PostedFile.ContentType;
string resumeName = Path.GetFileName(ResumeUpload.PostedFile.FileName);
byte[] resumeData = new byte[resumeLength - 1];
resumeStream.Read(resumeData, 0, resumeLength);

string qry = "INSERT INTO Employees (FirstName, LastName, Photo, PhotoFileName, PhotoMime, Resume, 
ResumeFileName, ResumeMime) VALUES (?,?,?,?,?,?,?,?)";
string connect = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|Northwind.mdb";
using (OleDbConnection conn = new OleDbConnection(connect))
{
OleDbCommand cmd = new OleDbCommand(qry, conn);
cmd.Parameters.AddWithValue("", FirstName.Text);
cmd.Parameters.AddWithValue("", Surname.Text);
cmd.Parameters.AddWithValue("", photoData);
cmd.Parameters.AddWithValue("", photoName);
cmd.Parameters.AddWithValue("", photoMime);
cmd.Parameters.AddWithValue("", resumeData);
cmd.Parameters.AddWithValue("", resumeName);
cmd.Parameters.AddWithValue("", resumeMime);
conn.Open();
cmd.ExecuteNonQuery();
}
}

30 tally erp 9 manual pdf free ebook

30 tally erp 9 manual pdf free ebook download from s3.amazonaws.com

Server control and HTML control.

 Explain the difference between Server control and HTML control.

Answer:

Server events
Server control events are handled in the server whereas HTML control events are handled in the page.

State management
Server controls can maintain data across requests using view state whereas HTML controls have no such mechanism to store data between requests.

Browser detection
Server controls can detect browser automatically and adapt display of control accordingly whereas HTML controls can’t detect browser automatically.

Properties
Server controls contain properties whereas HTML controls have attributes only.

components of web form in ASP.NET

 Explain the components of web form in ASP.NET

Answer:

Server controls
The server controls are Hypertext Markup Language (HTML) elements that include a runat=server attribute. They provide automatic state management and server-side events and respond to the user events by executing event handler on the server.

HTML controls
These controls also respond to the user events but the events processing happen on the client machine.

Data controls
Data controls allow to connect to the database, execute command and retrieve data from database.

System components
System components provide access to system-level events that occur on the server.

 Describe in brief .NET Framework and its components.

Answer:

.NET Framework provides platform for developing windows and web software. ASP.NET is a part of .Net framework and can access all features implemented within it that was formerly available only through windows API. .NET Framework sits in between our application programs and operating system.

The .Net Framework has two main components:

.Net Framework Class Library: It provides common types such as data types and object types that can be shared by all .Net compliant language.

The Common language Runtime: It provides services like type safety, security, code execution, thread management, interoperability services.

NATURE

CHILD

Great ability develops and reveals itself increasingly with every new assignment.

Ability will never catch up with the demand for it.

Interview Question & Answer

SQL

SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

Tables in a database are often related to each other with keys.

A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

Eg.  SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

The SQL UNION Operator The UNION operator is used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order. SQL UNION Syntax SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2 Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL. SQL UNION ALL Syntax SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2 PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.

Introduction Stored procedures are special objects available in sql server. Its a precompiled statements where all the preliminary parsing operations are performed and the statements are ready for execution. Its very fast when compared to ordinary sql statements where the sql statements will undergone a sequence of steps to fetch the data Stored procedure involves various syntax based on the parameters passed. Let me show you a generic syntax for a stored procedure. Code:  Create procedure procName as begin    ----Your query should be return here end CREATE PROCEDURE SPWITHPARAMETER_AND_RETURN_VALUE   (     @EMPID INT,     @IDVAL INT,       @RETURNVALUE INT =0 OUT   )   AS         DECLARE @COUNT INT   BEGIN   SELECT @COUNT=COUNT(*) FROM JOINTABLE WHERE EMPID=@EMPID AND IDVAL=@IDVAL         IF(@COUNT >0)           BEGIN                        SET @RETURNVALUE = 1;                              PRINT @RETURNVALUE                    RETURN @RETURNVALUE                         END          ELSE            BEGIN                 SET @RETURNVALUE = 1;                               PRINT @RETURNVALUE                 RETURN @RETURNVALUE                         END   END

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. SQL CREATE VIEW Syntax CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view. SQL CREATE VIEW Examples If you have the Northwind database you can see that it has several views installed by default. The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table. The view is created with the following SQL: CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM Products WHERE Discontinued=No

How to Add Column in Existing Table ALTER TABLE supplier  MODIFY supplier_name   varchar2(100)     not null; This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

SQL LEFT JOIN Keyword The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2). SQL LEFT JOIN Syntax SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

SQL RIGHT JOIN Keyword The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1). SQL RIGHT JOIN Syntax SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

SQL FULL JOIN Keyword The FULL JOIN keyword return rows when there is a match in one of the tables. SQL FULL JOIN Syntax SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name

The SQL SELECT INTO Statement The SELECT INTO statement selects data from one table and inserts it into a different table. The SELECT INTO statement is most often used to create backup copies of tables. SQL SELECT INTO Syntax We can select all columns into the new table: SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename Or we can select only the columns we want into the new table: SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename SQL SELECT INTO Example Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table. We use the following SQL statement: SELECT * INTO Persons_Backup FROM Persons We can also use the IN clause to copy the table into another database: SELECT * INTO Persons_Backup IN 'Backup.mdb' FROM Persons We can also copy only a few fields into the new table: SELECT LastName,FirstName INTO Persons_Backup FROM Persons SQL SELECT INTO - With a WHERE Clause We can also add a WHERE clause. The following SQL statement creates a "Persons_Backup" table with only the persons who lives in the city "Sandnes": SELECT LastName,Firstname INTO Persons_Backup FROM Persons WHERE City='Sandnes' SQL SELECT INTO - Joined Tables Selecting data from more than one table is also possible. The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders": SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id

SQL Constraints Constraints are used to limit the type of data that can go into a table. Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement). We will focus on the following constraints: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT The next chapters will describe each constraint in detail.

SQL NOT NULL Constraint The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

SQL UNIQUE Constraint The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. SQL UNIQUE Constraint on CREATE TABLE The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created: MySQL: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), UNIQUE (P_Id) )

SQL PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key. SQL PRIMARY KEY Constraint on CREATE TABLE The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created: MySQL: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) ) SQL Server / Oracle / MS Access: CREATE TABLE Persons ( P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

SQL FOREIGN KEY Constraint A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Let's illustrate the foreign key with an example. Look at the following two tables: The "Persons" table: SQL FOREIGN KEY Constraint on CREATE TABLE The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created: MySQL: CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ) SQL Server / Oracle / MS Access: CREATE TABLE Orders ( O_Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, P_Id int FOREIGN KEY REFERENCES Persons(P_Id) ) To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

The DROP INDEX Statement The DROP INDEX statement is used to delete an index in a table. DROP INDEX Syntax for MS Access: DROP INDEX index_name ON table_name DROP INDEX Syntax for MS SQL Server: DROP INDEX table_name.index_name DROP INDEX Syntax for DB2/Oracle: DROP INDEX index_name DROP INDEX Syntax for MySQL: ALTER TABLE table_name DROP INDEX index_name The DROP TABLE Statement The DROP TABLE statement is used to delete a table. DROP TABLE table_name The DROP DATABASE Statement The DROP DATABASE statement is used to delete a database. DROP DATABASE database_name The TRUNCATE TABLE Statement What if we only want to delete the data inside the table, and not the table itself? Then, use the TRUNCATE TABLE statement: TRUNCATE TABLE table_name

The ALTER TABLE Statement The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. SQL ALTER TABLE Syntax To add a column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column): ALTER TABLE table_name DROP COLUMN column_name To change the data type of a column in a table, use the following syntax: ALTER TABLE table_name ALTER COLUMN column_name datatype

SQL Dates  The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database. As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated. Before talking about the complications of querying for dates, we will look at the most important built-in functions for working with dates. MySQL Date Functions The following table lists the most important built-in date functions in MySQL: Function Description NOW() Returns the current date and time CURDATE() Returns the current date CURTIME() Returns the current time DATE() Extracts the date part of a date or date/time expression EXTRACT() Returns a single part of a date/time DATE_ADD() Adds a specified time interval to a date DATE_SUB() Subtracts a specified time interval from a date DATEDIFF() Returns the number of days between two dates DATE_FORMAT() Displays date/time data in different formats SQL Server Date Functions The following table lists the most important built-in date functions in SQL Server: Function Description GETDATE() Returns the current date and time DATEPART() Returns a single part of a date/time DATEADD() Adds or subtracts a specified time interval from a date DATEDIFF() Returns the time between two dates CONVERT() Displays date/time data in different formats SQL Date Data Types MySQL comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MM:SS TIMESTAMP - format: YYYY-MM-DD HH:MM:SS YEAR - format YYYY or YY SQL Server comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MM:SS SMALLDATETIME - format: YYYY-MM-DD HH:MM:SS TIMESTAMP - format: a unique number Note: The date types are chosen for a column when you create a new table in your database!

The BETWEEN Operator The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates. SQL BETWEEN Syntax SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

The SQL SELECT DISTINCT Statement In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table. The DISTINCT keyword can be used to return only distinct (different) values. SQL SELECT DISTINCT Syntax SELECT DISTINCT column_name(s) FROM table_name

The WHERE Clause  The WHERE clause is used to extract only those records that fulfill a specified criterion. SQL WHERE Syntax SELECT column_name(s) FROM table_name WHERE column_name operator value his is correct: SELECT * FROM Persons WHERE FirstName='Tove' This is wrong: SELECT * FROM Persons WHERE FirstName=Tove

The SQL SELECT Statement The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set. SQL SELECT Syntax SELECT column_name(s) FROM table_name and SELECT * FROM table_name

The AND & OR Operators The AND operator displays a record if both the first condition and the second condition is true. The OR operator displays a record if either the first condition or the second condition is true. SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson' SELECT * FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola')

The ORDER BY Keyword The ORDER BY keyword is used to sort the result-set by a specified column. The ORDER BY keyword sort the records in ascending order by default. If you want to sort the records in a descending order, you can use the DESC keyword. SQL ORDER BY Syntax SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC SELECT * FROM Persons ORDER BY LastName DESC

The UPDATE Statement The UPDATE statement is used to update existing records in a table. SQL UPDATE Syntax UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

The DELETE Statement The DELETE statement is used to delete rows in a table. SQL DELETE Syntax DELETE FROM table_name WHERE some_column=some_value Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted! DELETE FROM table_name or DELETE * FROM table_name DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob'

The BETWEEN Operator The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates. SQL BETWEEN Syntax SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

SQL Alias You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names. An alias name could be anything, but usually it is short. SQL Alias Syntax for Tables SELECT column_name(s) FROM table_name AS alias_name SQL Alias Syntax for Columns SELECT column_name AS alias_name FROM table_name Alias Example Assume we have a table called "Persons" and another table called "Product_Orders". We will give the table aliases of "p" and "po" respectively. Now we want to list all the orders that "Ola Hansen" is responsible for. We use the following SELECT statement: SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Hansen' AND p.FirstName='Ola' The same SELECT statement without aliases: SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName FROM Persons, Product_Orders WHERE Persons.LastName='Hansen' AND Persons.FirstName='Ola' As you'll see from the two SELECT statements above; aliases can make queries easier to both write and to read.

SQL Aggregate Functions SQL aggregate functions return a single value, calculated from values in a column. Useful aggregate functions: AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum SQL Scalar functions SQL scalar functions return a single value, based on the input value. Useful scalar functions: UCASE() - Converts a field to upper case LCASE() - Converts a field to lower case MID() - Extract characters from a text field LEN() - Returns the length of a text field ROUND() - Rounds a numeric field to the number of decimals specified NOW() - Returns the current system date and time FORMAT() - Formats how a field is to be displayed Tip: The aggregate functions and the scalar functions will be explained in details in the next chapters.

The AVG() Function The AVG() function returns the average value of a numeric column. SQL AVG() Syntax SELECT AVG(column_name) FROM table_name SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

SQL COUNT(column_name) Syntax The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column: SELECT COUNT(column_name) FROM table_name SQL COUNT(*) Syntax The COUNT(*) function returns the number of records in a table: SELECT COUNT(*) FROM table_name SQL COUNT(DISTINCT column_name) Syntax The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column: SELECT COUNT(DISTINCT column_name) FROM table_name Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

The FIRST() Function The FIRST() function returns the first value of the selected column. SQL FIRST() Syntax SELECT FIRST(column_name) FROM table_name SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders The LAST() Function The LAST() function returns the last value of the selected column. SQL LAST() Syntax SELECT LAST(column_name) FROM table_name  Tip: Workaround if FIRST() function is not supported: SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1 SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders  Tip: Workaround if LAST() function is not supported: SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1

The MAX() Function The MAX() function returns the largest value of the selected column. SQL MAX() Syntax SELECT MAX(column_name) FROM table_name SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

The MIN() Function The MIN() function returns the smallest value of the selected column. SQL MIN() Syntax SELECT MIN(column_name) FROM table_name SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

The SUM() Function The SUM() function returns the total sum of a numeric column. SQL SUM() Syntax SELECT SUM(column_name) FROM table_name SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

The GROUP BY Statement The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. SQL GROUP BY Syntax SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer SELECT Customer,SUM(OrderPrice) FROM Orders The result-set will look like this: Customer SUM(OrderPrice) Hansen 5700 Nilsen 5700 Hansen 5700 Hansen 5700 Jensen 5700 Nilsen 5700

The HAVING Clause The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. SQL HAVING Syntax SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000 Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500. We add an ordinary WHERE clause to the SQL statement: SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Hansen' OR Customer='Jensen' GROUP BY Customer HAVING SUM(OrderPrice)>1500

The UCASE() Function The UCASE() function converts the value of a field to uppercase. SQL UCASE() Syntax SELECT UCASE(column_name) FROM table_name Syntax for SQL Server SELECT UPPER(column_name) FROM table_name Now we want to select the content of the "LastName" and "FirstName" columns above, and convert the "LastName" column to uppercase. We use the following SELECT statement: SELECT UCASE(LastName) as LastName,FirstName FROM Persons The result-set will look like this: LastName FirstName HANSEN Ola SVENDSON Tove PETTERSEN Kari

The LCASE() Function The LCASE() function converts the value of a field to lowercase. SQL LCASE() Syntax SELECT LCASE(column_name) FROM table_name Syntax for SQL Server SELECT LOWER(column_name) FROM table_name Now we want to select the content of the "LastName" and "FirstName" columns above, and convert the "LastName" column to lowercase. We use the following SELECT statement: SELECT LCASE(LastName) as LastName,FirstName FROM Persons The result-set will look like this: LastName FirstName hansen Ola svendson Tove pettersen Kari

The MID() Function The MID() function is used to extract characters from a text field. SQL MID() Syntax SELECT MID(column_name,start[,length]) FROM table_name Parameter Description column_name Required. The field to extract characters from start Required. Specifies the starting position (starts at 1) length Optional. The number of characters to return. If omitted, the MID() function returns the rest of the text ELECT MID(City,1,4) as SmallCity FROM Persons The result-set will look like this: SmallCity Sand Sand Stav

The LEN() Function The LEN() function returns the length of the value in a text field. SQL LEN() Syntax SELECT LEN(column_name) FROM table_name SELECT LEN(Address) as LengthOfAddress FROM Persons

The ROUND() Function The ROUND() function is used to round a numeric field to the number of decimals specified. SQL ROUND() Syntax SELECT ROUND(column_name,decimals) FROM table_name Parameter Description column_name Required. The field to round. decimals Required. Specifies the number of decimals to be returned. SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

The NOW() Function The NOW() function returns the current system date and time. SQL NOW() Syntax SELECT NOW() FROM table_name SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

The FORMAT() Function The FORMAT() function is used to format how a field is to be displayed. SQL FORMAT() Syntax SELECT FORMAT(column_name,format) FROM table_name SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate FROM Products

SQL Statement Syntax AND / OR SELECT column_name(s) FROM table_name WHERE condition AND|OR condition ALTER TABLE ALTER TABLE table_name  ADD column_name datatype or ALTER TABLE table_name  DROP COLUMN column_name AS (alias) SELECT column_name AS column_alias FROM table_name or SELECT column_name FROM table_name  AS table_alias BETWEEN SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 CREATE DATABASE CREATE DATABASE database_name CREATE TABLE CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name2 data_type, ... ) CREATE INDEX CREATE INDEX index_name ON table_name (column_name) or CREATE UNIQUE INDEX index_name ON table_name (column_name) CREATE VIEW CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition DELETE DELETE FROM table_name WHERE some_column=some_value or DELETE FROM table_name  (Note: Deletes the entire table!!) DELETE * FROM table_name  (Note: Deletes the entire table!!) DROP DATABASE DROP DATABASE database_name DROP INDEX DROP INDEX table_name.index_name (SQL Server) DROP INDEX index_name ON table_name (MS Access) DROP INDEX index_name (DB2/Oracle) ALTER TABLE table_name DROP INDEX index_name (MySQL) DROP TABLE DROP TABLE table_name GROUP BY SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value IN SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..) INSERT INTO INSERT INTO table_name VALUES (value1, value2, value3,....) or INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,....) INNER JOIN SELECT column_name(s) FROM table_name1 INNER JOIN table_name2  ON table_name1.column_name=table_name2.column_name LEFT JOIN SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2  ON table_name1.column_name=table_name2.column_name RIGHT JOIN SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2  ON table_name1.column_name=table_name2.column_name FULL JOIN SELECT column_name(s) FROM table_name1 FULL JOIN table_name2  ON table_name1.column_name=table_name2.column_name LIKE SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern ORDER BY SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC] SELECT SELECT column_name(s) FROM table_name SELECT * SELECT * FROM table_name SELECT DISTINCT SELECT DISTINCT column_name(s) FROM table_name SELECT INTO SELECT * INTO new_table_name [IN externaldatabase] FROM old_table_name or SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_table_name SELECT TOP SELECT TOP number|percent column_name(s) FROM table_name TRUNCATE TABLE TRUNCATE TABLE table_name UNION SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2 UNION ALL SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2 UPDATE UPDATE table_name SET column1=value, column2=value,... WHERE some_column=some_value WHERE SELECT column_name(s) FROM table_name WHERE column_name operator value C#

What is dataset?

The DataSet object is central to supporting disconnected, distributed data scenarios with ADO.NET. 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, with XML data, or 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 following illustration shows the DataSetobject model.

What is Data Table?

The DataTable class is a member of the System.Data namespace within the .NET Framework class library. You can create and use a DataTable independently or as a member of a DataSet, and DataTable objects can also be used in conjunction with other .NET Framework objects, including the DataView. You access the collection of tables in a DataSetthrough the Tables property of the DataSet object.

What is Excute Non Query?

ExecuteNonQuery() is one of the most frequently used method in SqlCommand Object and is used for executing statements that do not return result set. ExecuteNonQuery() performs Data Definition tasks as well as Data Manipulation tasks also. The Data Definition tasks like creating Stored Procedures and Views perform by ExecuteNonQuery() . Also Data Manipulation tasks like Insert , Update and Delete perform by ExecuteNonQuery().

What is State Management

Types of State Management  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.  Implementation Procedure  Client – Side State Management:  View State:  The ViewState property provides a dictionary object for retaining values between multiple requests for the same page. When an ASP.NET page is processed, the current state of the page and controls is hashed into a string and saved in the page as a hidden field. If the data is too long for a single field, then ASP.NET performs view state chunking (new in ASP.NET 2.0) to split it across multiple hidden fields. The following code sample demonstrates how view state adds data as a hidden form within a Web page’s HTML:  <input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE” value="/wEPDwUKMTIxNDIyOTM0Mg9kFgICAw9kFgICAQ8PFgIeBFRleHQFEzQvNS8yMDA2IDE6Mzc6MTEgUE1kZGROWHn/rt75XF/pMGnqjqHlH66cdw==" /> Encrypting of the View State: You can enable view state encryption to make it more difficult for attackers and malicious users to directly read view state information. Though this adds processing overhead to the Web server, it supports in storing confidential information in view state. To configure view state encryption for an application does the following:  <Configuration>           <system.web>                  <pages viewStateEncryptionMode="Always"/>           </system.web> </configuration> Alternatively, you can enable view state encryption for a specific page by setting the value in the page directive, as the following sample demonstrates:  <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" ViewStateEncryptionMode="Always"%> View State is enabled by default, but if you can disable it by setting the EnableViewState property for each web control to false. This reduces the server processing time and decreases page size.  Reading and Writing Custom View State Data:  If you have a value that you’d like to keep track of while the user is visiting a single ASP.NET Web page, adding a custom value to ViewState is the most efficient and secure way to do that. However, ViewState is lost if the user visits a different Web page, so it is useful only for temporarily storing values.  Example: Determine the time of last visit to the page  // Check if View State object exists, and display it if it does If (ViewState ["lastVisit"]!= null) Label1.Text = (string)ViewState["lastVisit"]; else Label1.Text = "lastVisit ViewState not defined."; // Define the ViewState object for the next page view ViewState.Add("lastVisit", DateTime.Now.ToString()); Control State: If you create a custom control that requires ViewState, you can use the ControlState property to store state information for your control. ControlState allows you to persist property information that is specific to a control and cannot be turned off like the ViewState property. To use control state in a custom control, your control must override the OnInit method and call the Register-RequiresControlState method during initialization and then override the SaveControl-State and LoadControlState methods.  Hidden fields: ViewState stores information in the Web page using hidden fields. Hidden fields are sent back to the server when the user submits a form; however, the information is never displayed by the Web browser (unless the user chooses to view the page source). ASP.NET allows you to create your own custom hidden fields and store values that are submitted with other form data. A HiddenField control stores a single variable in its Value property and must be explicitly added to the page. You can use hidden fields only to store information for a single page, so it is not useful for storing session data. If you use hidden fields, you must submit your pages to the server using Hypertext Transfer Protocol (HTTP) POST (which happens if the user presses a button) rather than requesting the page using HTTP GET (which happens if the user clicks a link). Unlike view state data, hidden fields have no built-in compression, encryption, hashing, or chunking, so users can view or modify data stored in hidden fields.  Cookies: Web applications can store small pieces of data in the client’s Web browser by using cookies. A cookie is a small amount of data that is stored either in a text file on the client file system (if the cookie is persistent) or in memory in the client browser session (if the cookie is temporary). The most common use of cookies is to identify a single user as he or she visits multiple Web pages.  Reading and Writing Cookies:  A Web application creates a cookie by sending it to the client as a header in an HTTP response. The Web browser then submits the same cookie to the server with every new request.  Create a cookie -> add a value to the Response.Cookies HttpCookieCollection.  Read a cookie -> read values in Request.Cookies.  Example:  // Check if cookie exists, and display it if it does if (Request.Cookies["lastVisit"] != null) // Encode the cookie in case the cookie contains client-side script Label1.Text = Server.HtmlEncode(Request.Cookies["lastVisit"].Value); else Label1.Text = "No value defined"; // Define the cookie for the next visit Response.Cookies["lastVisit"].Value = DateTime.Now.ToString();Response.Cookies["lastVisit"].Expires = DateTime.Now.AddDays(1); If you do not define the Expires property, the browser stores it in memory and the cookie is lost if the user closes his or her browser.  To delete a cookie, overwrite the cookie and set an expiration date in the past. You can’t directly delete cookies because they are stored on the client’s computer.  Controlling the Cookie Scope: By default, browsers won’t send a cookie to a Web site with a different hostname. You can control a cookie’s scope to either limit the scope to a specific folder on the Web server or expand the scope to any server in a domain. To limit the scope of a cookie to a folder, set the Path property, as the following example demonstrates:  Example:  Response.Cookies["lastVisit"].Path = "/Application1"; Through this the scope is limited to the “/Application1” folder that is the browser submits the cookie to any page with in this folder and not to pages in other folders even if the folder is in the same server. We can expand the scope to a particular domain using the following statement:  Example:  Response.Cookies[“lastVisit”].Domain = “Contoso”;  Storing Multiple Values in a Cookie:  Though it depends on the browser, you typically can’t store more than 20 cookies per site, and each cookie can be a maximum of 4 KB in length. To work around the 20-cookie limit, you can store multiple values in a cookie, as the following code demonstrates:  Example:  Response.Cookies["info"]["visit"].Value = DateTime.Now.ToString(); Response.Cookies["info"]["firstName"].Value = "Tony"; Response.Cookies["info"]["border"].Value = "blue"; Response.Cookies["info"].Expires = DateTime.Now.AddDays(1); Running the code in this example sends a cookie with the following value to the Web browser:  (visit=4/5/2006 2:35:18 PM) (firstName=Tony) (border=blue)  Query Strings: Query strings are commonly used to store variables that identify specific pages, such as search terms or page numbers. A query string is information that is appended to the end of a page URL. A typical query string might look like the following real-world example:  http://support.microsoft.com/Default.aspx?kbid=315233  In this example, the URL identifies the Default.aspx page. The query string (which starts with a question mark [?]) contains a single parameter named “kbid,” and a value for that parameter, “315233.” Query strings can also have multiple parameters, such as the following real-world URL, which specifies a language and query when searching the Microsoft.com Web site:  http://search.microsoft.com/results.aspx?mkt=en-US&setlang=en-US&q=hello+world  Value Name | ASP.NET Object | Value  mkt | Request.QueryString[“mkt”] | en-US  setlang | Request.QueryString[“setlang”] | en-US  q | Request.QueryString[“q”] | hello world  Limitations for Query Strings:  1. Some Browsers and client devices impose a 2083 – character limit on the length of the URL.  2. You must submit the page using an HTTP GET command in order for query string values to be available during page processing. Therefore, you shouldn’t add query strings to button targets in forms.  3. You must manually add query string values to every hyperlink that the user might click.  Example:  Label1.Text = "User: " + Server.HtmlEncode(Request.QueryString["user"]) + ", Prefs: " + Server.HtmlEncode(Request.QueryString["prefs"]) + ", Page: " + Server.HtmlEncode(Request.QueryString["page"]); Server - Side State Management:  Application State: ASP.NET allows you to save values using application state, a global storage mechanism that is accessible from all pages in the Web application. Application state is stored in the Application key/value dictionary. Once you add your application-specific information to application state, the server manages it, and it is never exposed to the client. Application state is a great place to store information that is not user-specific. By storing it in the application state, all pages can access data from a single location in memory, rather than keeping separate copies of the data. Data stored in the Application object is not permanent and is lost any time the application is restarted.  ASP.NET provides three events that enable you to initialize Application variables (free resources when the application shuts down) and respond to Application errors:  a. Application_Start: Raised when the application starts. This is the perfect place to initialize Application variables.  b. Application_End: Raised when an application shuts down. Use this to free application resources and perform logging.  c. Application_Error: Raised when an unhandled error occurs. Use this to perform error logging.  Session State: ASP.NET allows you to save values using session state, a storage mechanism that is accessible from all pages requested by a single Web browser session. Therefore, you can use session state to store user-specific information. Session state is similar to application state, except that it is scoped to the current browser session. If different users are using your application, each user session has a different session state. In addition, if a user leaves your application and then returns later after the session timeout period, session state information is lost and a new session is created for the user. Session state is stored in the Session key/value dictionary.  You can use session state to accomplish the following tasks:  i. Uniquely identify browser or client-device requests and map them to individual session instances on the server. This allows you to track which pages a user saw on your site during a specific visit.  ii. Store session-specific data on the server for use across multiple browser or client-device requests during the same session. This is perfect for storing shopping cart information.  iii. Raise appropriate session management events. In addition, you can write application code leveraging these events.  ASP.NET session state supports several different storage options for session data:  a. InProc Stores session state in memory on the Web server. This is the default, and it offers much better performance than using the ASP.NET state service or storing state information in a database server. InProc is fine for simple applications, but robust applications that use multiple Web servers or must persist session data between application restarts should use State Server or SQLServer.  b. StateServer Stores session state in a service called the ASP.NET State Service. This ensures that session state is preserved if the Web application is restarted and also makes session state available to multiple Web servers in a Web farm. ASP.NET State Service is included with any computer set up to run ASP.NET Web applications; however, the service is set up to start manually by default. Therefore, when configuring the ASP.NET State Service, you must set the startup type to Automatic.  c. SQLServer Stores session state in a SQL Server database. This ensures that session state is preserved if the Web application is restarted and also makes session state available to multiple Web servers in a Web farm. On the same hardware, the ASP.NET State Service outperforms SQLServer. However, a SQL Server database offers more robust data integrity and reporting capabilities.  d. Custom Enables you to specify a custom storage provider. You also need to implement the custom storage provider.  e. Off Disables session state. You should disable session state if you are not using it to improve performance.  Advantages Advantages of Client – Side State Management:  1. Better Scalability: With server-side state management, each client that connects to the Web server consumes memory on the Web server. If a Web site has hundreds or thousands of simultaneous users, the memory consumed by storing state management information can become a limiting factor. Pushing this burden to the clients removes that potential bottleneck.  2. Supports multiple Web servers: With client-side state management, you can distribute incoming requests across multiple Web servers with no changes to your application because the client provides all the information the Web server needs to process the request. With server-side state management, if a client switches servers in the middle of the session, the new server does not necessarily have access to the client’s state information. You can use multiple servers with server-side state management, but you need either intelligent load-balancing (to always forward requests from a client to the same server) or centralized state management (where state is stored in a central database that all Web servers access).  Advantages of Server – Side State Management:  1. Better security: Client-side state management information can be captured (either in transit or while it is stored on the client) or maliciously modified. Therefore, you should never use client-side state management to store confidential information, such as a password, authorization level, or authentication status.  2. Reduced bandwidth: If you store large amounts of state management information, sending that information back and forth to the client can increase bandwidth utilization and page load times, potentially increasing your costs and reducing scalability. The increased bandwidth usage affects mobile clients most of all, because they often have very slow connections. Instead, you should store large amounts of state management data (say, more than 1 KB) on the server.