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.