sanjeet pandey: February 2013

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

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:

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:

<system.web>

</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.

Home

Wednesday, February 6, 2013

Server control and HTML control.

Explain the difference between Server control and HTML control.

components of web form in ASP.NET

Explain the components of web form in ASP.NET

Describe in brief .NET Framework and its components.

NATURE

CHILD

Tuesday, February 5, 2013

Interview Question & Answer

The SQL UNION Operator

SQL UNION Syntax

SQL UNION ALL Syntax

Introduction

SQL CREATE VIEW Syntax

SQL CREATE VIEW Examples

SQL LEFT JOIN Keyword

SQL LEFT JOIN Syntax

SQL RIGHT JOIN Keyword

SQL RIGHT JOIN Syntax

SQL FULL JOIN Keyword

SQL FULL JOIN Syntax

The SQL SELECT INTO Statement

SQL SELECT INTO Syntax

SQL SELECT INTO Example

SQL SELECT INTO - With a WHERE Clause

SQL SELECT INTO - Joined Tables

SQL FOREIGN KEY Constraint

The DROP INDEX Statement

DROP INDEX Syntax for MS Access:

DROP INDEX Syntax for MS SQL Server:

DROP INDEX Syntax for DB2/Oracle:

DROP INDEX Syntax for MySQL:

The DROP TABLE Statement

The DROP DATABASE Statement

The TRUNCATE TABLE Statement

The ALTER TABLE Statement

SQL ALTER TABLE Syntax

The BETWEEN Operator

SQL BETWEEN Syntax

The SQL SELECT DISTINCT Statement

SQL SELECT DISTINCT Syntax

The WHERE Clause

SQL WHERE Syntax

his is correct: SELECT * FROM Persons WHERE FirstName='Tove' This is wrong: SELECT * FROM Persons WHERE FirstName=Tove

The SQL SELECT Statement

SQL SELECT Syntax

The AND & OR Operators

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

SQL ORDER BY Syntax

SELECT * FROM Persons ORDER BY LastName DESC

The UPDATE Statement

SQL UPDATE Syntax

The DELETE Statement

SQL DELETE Syntax

DELETE FROM table_name or DELETE * FROM table_name

DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob'

The BETWEEN Operator

SQL BETWEEN Syntax

SQL Alias

SQL Alias Syntax for Tables

SQL Alias Syntax for Columns

Alias Example

The AVG() Function

SQL AVG() Syntax

SQL COUNT(column_name) Syntax

SQL COUNT(*) Syntax

SQL COUNT(DISTINCT column_name) Syntax

The FIRST() Function

SQL FIRST() Syntax

The LAST() Function

SQL LAST() Syntax

The MAX() Function

SQL MAX() Syntax

The MIN() Function

SQL MIN() Syntax

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

his is correct:

SELECT * FROM Persons WHERE FirstName='Tove'

This is wrong:

SELECT * FROM Persons WHERE FirstName=Tove

SELECT * FROM Persons
WHERE FirstName='Tove'
AND LastName='Svendson'

SELECT * FROM Persons WHERE
LastName='Svendson'
AND (FirstName='Tove' OR FirstName='Ola')

SELECT * FROM Persons
ORDER BY LastName DESC

DELETE FROM table_name

or

DELETE * FROM table_name

DELETE FROM Persons
WHERE LastName='Tjessem' AND FirstName='Jakob'

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products