Basic Information of SQL server query (Architecture /function/SP/ SQL Syntax )

Table

A table is a collection of related information arranged in rows and columns. Information about each item in the collection is displayed as a row. The columns contain the same category of information for every item in the table. A table has a header row that identifies the category of information in each column.

Database

A database is a collection of information organized into objects. Information within a database is organized into one or more tables, each with a unique name. The information in the tables can be searched, retrieved, and manipulated. Some databases are created by default when the database application is installed, and others can be created and customized to suit business needs.

Server

A server is a computer that provides service to other computers on a network. The server may have a higher-performance capability than other computers on the network. Generally, users do not work on the servers directly. Servers manage resources and provide services to the users who access the information contained in the server.

Client

A client is a computer consisting of the interface that enables users to request the services of the server and display the results returned by the server. The client computer has to be connected to the same network as the server to access the resources available in the server.

Structured Query Language (SQL)

Structured Query Language (SQL) is a language used to communicate with a database. SQL consists of instructions that can be used to retrieve and delete information from the database, and modify information in a database. SQL is made up of three major language components: Data Manipulation Language (DML), Data Definition Language (DDL), and Data Control Language (DCL). So, SQL can be defined as SQL = DML + DDL + DCL.

DML/ DDL/ DCL

DML is used to change or manipulate the data within a database. DML includes commands to select, update, and insert data in a database, and delete data from a database. DDL is used to define the database itself. It includes clauses to create and eliminate tables, and create and eliminate views. This portion of the language is generally used by database designers. DCL is used to control access to data in a database. It includes clauses to grant and revoke database privileges. Generally, database administrators will use this portion of the language.

Query Editor

The Query Editor is a code editor used in the SQL Server Management Studio to communicate with a database. The Query Editor window consists of two panes. The top pane contains the Editor pane, where the SQL statements are entered. The bottom pane contains the Results pane, which displays the results of the queries, and the Messages tab, which displays the information about the query that is being executed.

A SQL statement is an instruction written in SQL using an appropriate syntax. The essential clauses and keywords present in a syntax structure have to be used when framing a SQL statement. Optional clauses can be used if required. A SQL statement is used to retrieve or manipulate the information present in a database.

Query

A query is an instruction that requests information from the tables present in a database. A query requires the column and table names to generate the output. It can include conditions to retrieve specific information.

A SELECT statement is a SQL statement used to retrieve information from a database. A simple SELECT statement is composed of two parts: a SELECT clause that includes all of the column names that are required in the output, and a FROM clause that contains the table name containing the columns. Additional clauses can be added to the SELECT statement if necessary. The order of column names used in the SELECT statement determines the order in which the columns are displayed in the output. When more than one column name is entered, commas are used to separate the column names. When all the columns of a table are required in the output, an asterisk (*) can be used instead of the column names.

Data type

Data type is the classification of data into groups based on their characteristics. The characteristics can include how many characters the value contains, whether it is a number or text, or whether it includes a decimal. The data type determines what calculations can be performed with that data. All values entered into a SQL database can be classified into one of the data types available in MS SQL 2008. The data type of the value entered into a column must match the data type that has been assigned to that column.

A comment is a word or statement entered in the Query Editor window, and this word or statement is not meant to be executed when the query is run. Comments can be used to provide explanation about the code or to temporarily disable parts of a SQL statement. A single-line comment is indicated with two hyphens before the word from where you want to insert the comment. A comment with multiple lines can be enclosed within the /* and */ characters.

Let's say you have a database containing information about all the employees in a company. You may need a list of just the employees in the marketing department, or the employees who joined the company between 2003 and 2006, or the employees who have been with the company for over five years and who have not utilized their full vacation time. Rather than displaying all the information in the database and then manually going through it to try to find the information you need, you can narrow down your search by mentioning the exact specification in the query.

A condition is a search criterion used in a SQL statement to retrieve or manipulate specific information. More than one search criterion can be included in a SQL statement to retrieve the exact information. Search criteria are used to compare the information in the columns to a specific value. Calculations can also be performed on numeric columns before comparing information.

A conditional search involves the following process. First, the SELECT statement along with a condition is entered in the Query Editor window. Next, every row in the table is searched using the condition present in the WHERE clause. Then, the records that match the condition are retrieved from the table. And finally, the retrieved records or the output is then displayed in the Query Editor window.

Operators are symbols or words used in expressions to manipulate values. Operators are mostly used between a word and a value in a search criterion. Operators can be used to perform calculations, compare values, and match patterns.

The WHERE clause is a clause that specifies a condition in a SQL statement. The WHERE clause contains the expression or column name followed by an operator, and then by an expression or value that needs to be compared. More than one condition can be included in the WHERE clause.

Comparison operators are symbols used to compare two expressions or values. The output of a comparison operator is one of three values: TRUE, FALSE, or UNKNOWN. Comparison operators cannot be used with text, ntext, or image data types. In SQL, they are used in conditions.

Arithmetic operators are symbols used to perform mathematical operations on numeric expressions. However, the plus (+) and minus (-) operators can also be used to perform arithmetic operations on datetime and smalldatetime values.

The compound assignment operator is an operator that combines one operator with another and assigns a value to a column or a variable. One of its advantages is that it shortens the length of the code as it declares and initializes the variable in a single statement.

A column alias is a name assigned to a column heading in the output. A column alias can be assigned to any column in a table and is displayed in place of the default column heading. Using an alias, you can easily interpret the contents of the column. The alias can contain any alphanumeric characters along with a few special characters.

Logical operators are operators that test the truth of a condition. Logical operators, like comparison operators, returns a value of either TRUE or FALSE. When more than one logical operator is used in a SQL statement, they are executed in an order that is previously determined. Parentheses can be used to change the order of evaluation.

The AND, OR, and NOT operators are the most commonly used logical operators. The AND and OR operators are used to combine the result of two or more boolean expressions. The AND operator returns TRUE when both the expressions are TRUE, while the OR operator returns TRUE when either of the expressions is TRUE. The NOT operator is used to negate a boolean expression.

The BETWEEN..AND operator is a logical operator that searches for an inclusive range of values. The start value of the range is entered after the BETWEEN keyword and the end value is entered after the AND keyword in the WHERE clause of a SQL statement. The logical operator NOT can be used to retrieve records that fall outside the specified range.

The IN operator is a logical operator that checks whether a column value or expression matches a list of values. The IN operator is entered in the WHERE clause between the column name and the list of values to be matched. The list of values is entered within parentheses, separated by commas. If text is used in the list of values, it is enclosed within single quotes. The data type of the values in the list must match the data type of the column or expression.

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1,value2,...)

NULL is a value that can be stored in a column when the value is either unknown or undefined. When the table information is viewed, the word “NULL” is displayed in the column that contains the null value. It is not the same as zero, blank, or a zero-length character string. When null values are compared, they will not be equal because the value of each NULL is unknown.

The IS NULL clause is a clause that tests for a NULL value. The IS NULL clause is entered in a WHERE clause after the expression or column name to be tested. The NOT operator can be entered between the IS and NULL keywords to check for values that are not null.

SELECT LastName,FirstName,Address FROM Persons

WHERE Address IS NULL

A wildcard is a special character used in a search expression to represent certain characters. Wildcards can be inserted anywhere within a search pattern to locate column values in records that contain a known sequence of characters without having to enter the entire string of characters, or when the entire set of characters is not known. There are four wildcard characters in SQL. Some wildcards substitute for a single character, while others substitute for an unlimited number of characters. More than one wildcard can be used in an expression.

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for a single character
[charlist]	Sets and ranges of characters to match
[^charlist] or [!charlist]	Matches only a character NOT specified within the brackets

SELECT * FROM Customers

WHERE City LIKE 'ber%';

SELECT * FROM Customers

WHERE City LIKE '[a-c]%';

Pattern matching is a method of searching for column values in a record with the help of patterns which are known to contain a specific combination of text or numeric characters. The pattern being searched for can be a single character or a combination of characters, and may include one or more wildcards. Pattern matching tests whether the specified pattern exists anywhere within the value in the database. Pattern matching uses the LIKE operator followed by the pattern enclosed within single quotes. The characters used in the pattern are not case sensitive.

When multiple operators are used in a complex expression, operator precedence determines the sequence in which the operations are performed. A higher-level operator is evaluated before a lower-level operator. If the order of execution is not specified precisely using parentheses, the resulting output may not be correct.

A function is a piece of code with a specified name and optional parameters that operates as a single logical unit. The parameter can be a column name or a value. When more than one input parameter is provided, the parameters are separated by commas. The function performs a designated action and returns a result.

Date functions are used to perform calculations on date columns that contain a date and time input value. These functions return a string, numeric, or date and time value. In date functions, if datetime or smalldatetime values are used, they are enclosed within single quotes.

SQL Server 2008 introduced four new data types that dealt only with the date and time values. These data types focused on the precision of the date and time values. The main aspect of these date and time data types was that they provided a split between the date and time values. With business growing globally, it is important to maintain the time zones as well as the precision of time in nanoseconds. Hence to cater to such requirements, these data types offer support for a large data range and better accuracy.

Datepart is the word that specifies the part of the date to be returned, such as the year, month, day, and hour. Datepart is the input parameter entered in date functions. An abbreviation can be used in date functions instead of entering datepart.

                       DATEPART(datepart,date)

SELECT DATEPART(yyyy,OrderDate) AS OrderYear,

DATEPART(mm,OrderDate) AS OrderMonth,

DATEPART(dd,OrderDate) AS OrderDay,

FROM Orders

WHERE OrderId=1

An aggregate function performs calculations on a set of values and returns a single value. The function is composed of two parts: a name that gives an indication of the calculation to be performed followed by the values or references to the values, enclosed in parentheses. When the query with the aggregate function is executed, the result contains a single row with the summary information. Aggregate functions usually ignore null values.

• 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

A keyword is a word that is reserved for defining, manipulating, and accessing data. When keywords are entered in the Query Editor window, they are displayed in color. Since a keyword has a predefined meaning in SQL, if used outside the predetermined context, it has to be enclosed within double quotes.

The DISTINCT keyword is used to eliminate duplicate values in a list of values. DISTINCT is an optional keyword that can be used in the SELECT statement to retrieve unique rows from a table. In the SUM, AVG, and COUNT functions, DISTINCT is used to eliminate duplicate values before performing calculations. If the DISTINCT keyword is used, it is used with column names and not with arithmetic expressions.

SELECT DISTINCT Employee, Rank
FROM Employees

A COMPUTE clause is a clause that generates totals that appear as additional summary columns at the end of a result set. When used with the BY keyword, it generates control breaks and subtotals in the result set. The COMPUTE clause contains the expression or column name on which the calculation is performed, and the expression or column name must appear in the SELECT statement. The COMPUTE and COMPUTE BY clauses can be used in the same query.

USE AdventureWorks2008R2;

GO

SELECT CustomerID, OrderDate, SubTotal, TotalDue

FROM Sales.SalesOrderHeader

WHERE SalesPersonID = 35

ORDER BY OrderDate

COMPUTE SUM(SubTotal), SUM(TotalDue);

A string is a collection of characters that cannot be used in an arithmetic calculation. The char, varchar, and text data types are used to store strings. The characters can be uppercase or lowercase, numerals, and special characters such as the “at” sign (@), ampersand (&), and exclamation point (!) in any combination. String values used in expressions are enclosed within single quotes. When strings are compared, the case of the string is ignored..

Case conversion functions are functions that convert the case of a string. The LOWER function accepts uppercase characters as the input and converts them to lowercase. The UPPER function accepts lowercase characters as the input and converts them to uppercase. The input parameters provided for the case conversion functions can be a value or a column name.

PRINT lower('BlackWasp')    -- blackwasp

PRINT upper('BlackWasp')    -- BLACKWASP

Leading and trailing spaces are spaces that are present in a column when the data stored in a column is less than the maximum number of characters that the column can contain. Spaces inserted before the value are called leading spaces, while those inserted at the end of the value are called trailing spaces.

A trim function removes the leading and trailing blank spaces that are part of a string. The LTRIM function removes the blank spaces before the value in a column and the RTRIM function removes the blank spaces after the value in a column. The trim function works only on string values.

DECLARE @String1 NVARCHAR(MAX)
SET @String1 = ' String '
SELECT @String1 OriginalString, RTRIM(LTRIM(@String1)) TrimmedValue
GO

Character extraction is the process of extracting specific characters from a string value. The extracted string is called a substring. Characters can be extracted from the beginning, end, or anywhere in the string.

Concatenation is the process of combining two string expressions into one string expression. If there are leading or trailing spaces in either of the expressions, then the other expression which does not contain the leading or trailing spaces is appended following the spaces. The + (String Concatenation) operator is used to concatenate two expressions. Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression.

USE pubs

SELECT (au_lname + ', ' + au_fname) AS Name

FROM authors

ORDER BY au_lname ASC, au_fname ASC

Sorting is a method of arranging column values displayed in the output in either ascending or descending order. Multiple levels of sorting can be performed with a given set of rows.

ORDER BY is a clause used to display rows in a specified sort order. The ORDER BY clause is followed by the column name, and then the optional keyword ASC for ascending order or DESC for descending order. When neither keyword is specified, the ORDER BY clause sorts the rows in ascending order. Multiple levels of sorting can be performed by specifying the column names, one after the other, separating them with commas. The null values present in the columns are treated as the lowest value. The ORDER BY clause, when used, is entered at the end of the SQL statement.

SELECT column_name,column_name

FROM table_name

ORDER BY column_name,column_name ASC|DESC;

Ranking functions are functions that sequentially number the rows in a result set based on the partitioning and ordering of the rows. Depending on the ranking function used in the query, some of the rows might get the same rank value as other rows. A ranking function is always followed by the OVER clause, which determines the partitioning and ordering of the rows before a ranking function is applied. The OVER clause is supported by the PARTITION BY clause, which determines how the rows are grouped for ranking, and the ORDER BY clause, which determines the order of the rows within each partition.

The RANK function is a ranking function that returns a ranking value for each row in a result set. The rank values returned by the RANK function are not continuous. If two or more rows of a table have the same value, they will be assigned the same rank value. In such a case, the ranking value increases as specified by the ORDER BY clause.

RANK ( ) OVER ( [partition_by_clause ] order_by_clause )

      

       USE AdventureWorks2012;

GO

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity

,RANK() OVER

    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank

FROM Production.ProductInventory AS i

INNER JOIN Production.Product AS p

    ON i.ProductID = p.ProductID

WHERE i.LocationID BETWEEN 3 AND 4

ORDER BY i.LocationID;

GO

The DENSE_RANK is a ranking function that performs a task similar to that of the RANK function, but it does not produce gaps in the rank values. Instead, this function consecutively ranks each unique ORDER BY value.

                DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)

SELECT DENSE_RANK()  OVER ( 
   ORDER BY Title) AS [RecordRank],
LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender 
FROM HumanResources.Employee

The ROW_NUMBER is a ranking function that uses an ORDER BY clause and a unique partition value to return a result set, which consists of sequential numbers for each row set. The row number() is subject to change according to the result.

The ROW_NUMBER is a ranking function that uses an ORDER BY clause and a unique partition value to return a result set, which consists of sequential numbers for each row set. The row number() is subject to change according to the result.

ROW_NUMBER ( )

    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

USE AdventureWorks2012;

GO

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,

FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL AND SalesYTD<> 0;

The NTILE is a ranking function that divides the rows in each partition of a result set into a specified number of groups based on a given value and ranks them according to the partition. The NTILE function contains an integer expression as its main argument, which specifies the number of groups into which each partition will be divided. The rows in the result set will be divided evenly among the partitions, but when the number of rows in the result set does not divide exactly into the number of partitions, the rows are distributed in such a way that the larger groups appear first in the result set.

NTILE (integer_expression) OVER ( [<partition_by_clause> ] <order_by_clause> )

USE AdventureWorks2012;

GO

SELECT p.FirstName, p.LastName

,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile

,CONVERT(nvarchar(20),s.SalesYTD,1) AS SalesYTD

    , a.PostalCode

FROM Sales.SalesPerson AS s

INNER JOIN Person.Person AS p

    ON s.BusinessEntityID = p.BusinessEntityID

INNER JOIN Person.Address AS a

    ON a.AddressID = p.BusinessEntityID

WHERE TerritoryID IS NOT NULL

    AND SalesYTD<> 0;

GO

A group is a collection of two or more records combined into one unit based on one or more columns. The records present in each group are listed together in the output. The groups are not sorted in any order, but the records within the group are sorted in ascending order.

GROUP BY is a clause used to group two or more rows displayed in the output based on one or more columns. The GROUP BY clause is followed by a column or a non-aggregate expression that references a column.

                SELECT a, b, c, SUM ( <expression> )

FROM T

GROUP BY ROLLUP (a,b,c);

The CUBE and ROLLUP subclauses are considered to be shortcuts to the pre-defined GROUPING SETS specifications. More precisely, they are abbreviations to the GROUPING SETS subclause. Each of these subclauses has unique operations associated with it.

 The CUBE subclause generates all possible grouping sets obtained from the elements listed in parentheses. This also includes the empty grouping set. However, the result obtained from the CUBE subclause is large because the results are exponential in number. The CUBE subclause is beneficial for complex data analysis.

CUBE

YEAR, MONTH, DAY

YEAR, MONTH

YEAR, DAY

YEAR

MONTH, DAY

MONTH

DAY

()

The ROLLUP subclause, on the other hand, produces a hierarchical series of grouping sets. The ROLLUP subclause is used with the ROLLUP keyword specifying the hierarchy of grouping attributes. This subclause returns 'n+1' grouping sets for 'n' elements in a hierarchical manner.

ROLLUP (YEAR, MONTH, DAY)

YEAR, MONTH, DAY

YEAR, MONTH

YEAR

()

Grouping sets allows you to define multiple groupings within a single query. These grouping sets are introduced as extensions to the GROUP BY clause. These extensions can also include the CUBE and ROLLUP subclauses and the GROUPING_ID function. However, these grouping sets define their own purpose, without which a single query defines only one grouping set in the GROUP BY clause. The grouping set statement will generate a result which is equivalent to the result set generated by using the

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), (Yr), ())

GROUP BY , ROLLUP or CUBE operations. These grouping sets show better performance because they execute a single query for multiple groupings.

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING is a clause used to specify a search condition for a group or an aggregate value. The HAVING clause is generally used with the GROUP BY clause. After the data has been grouped and aggregated, the conditions in the HAVING clause are applied. When the GROUP BY clause is not used, the HAVING clause behaves like a WHERE clause.

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;

CUBE and ROLLUP are operators that are used to display the summary rows along with the rows displayed by the GROUP BY clause. The CUBE or ROLLUP operator is entered after the GROUP BY clause. In the result, the left column value of the summary row is displayed as NULL and the right column value contains the summary value.

When the CUBE operator is used, the number of columns listed in the GROUP BY clause determines the number of summary rows displayed in the output. A summary row is returned for every group and subgroup in the output. So, the number of rows in the output is the same, regardless of the order in which the grouping columns are specified. When the ROLLUP operator is used, groups are summarized in the hierarchical order, from the lowest level in the group to the highest. The group hierarchy is determined by the order in which the grouping columns are specified. Changing the order of the grouping columns can affect the number of rows displayed in the output.

PIVOT and UNPIVOT are relational operators that are used to rearrange the related columns and values of a table. The PIVOT relational operator rotates the unique values from one column of a table into multiple columns in the output in order to perform aggregate functions on any of the columns and display the resultant data in a pivoted table. The UNPIVOT operator performs just the opposite of what the PIVOT operator does by rotating multiple columns into values of a single column.

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,

[0], [1], [2], [3], [4]

FROM

(SELECT DaysToManufacture, StandardCost

    FROM Production.Product) AS SourceTable

PIVOT

(

AVG(StandardCost)

FOR DaysToManufacture IN ([0], [1], [2], [3], [4])

) AS PivotTable;

--Create the table and insert values as portrayed in the previous example.

CREATE TABLE pvt (VendorIDint, Emp1 int, Emp2 int,

    Emp3 int, Emp4 int, Emp5 int);

GO

INSERT INTO pvt VALUES (1,4,3,5,4,4);

INSERT INTO pvt VALUES (2,4,1,5,5,5);

INSERT INTO pvt VALUES (3,4,3,5,4,4);

INSERT INTO pvt VALUES (4,4,2,5,5,4);

INSERT INTO pvt VALUES (5,5,1,5,5,5);

GO

--Unpivot the table.

SELECT VendorID, Employee, Orders

FROM

   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

   FROM pvt) p

UNPIVOT

   (Orders FOR Employee IN

      (Emp1, Emp2, Emp3, Emp4, Emp5)

)ASunpvt;

GO

The UNION operator is an operator that is used to combine the results of two or more queries into a single output. The UNION operator is entered between SQL statements. The number of columns in each query must be identical. In each query, the data type of the respective columns must be compatible. By default, when the UNION operator is used, duplicate rows in each query or different queries are removed from the result. To display these duplicate rows, the ALL operator is entered after the UNION operator.

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

EXCEPT and INTERSECT are operands that return distinct values by comparing the results of two queries. The EXCEPT operand returns distinct values from its left side query that are not found on its right side query. The INTERSECT operand returns distinct values from both the queries. The two basic rules for comparing two queries using the EXCEPT or INTERSECT operand are that the number and the order of the columns must be the same in both queries and the data types must be compatible.

USE AdventureWorks2012;

GO

SELECT ProductID

FROM Production.Product

INTERSECT

SELECT ProductID

FROM Production.WorkOrder ;

USE AdventureWorks2012;

GO

SELECT ProductID

FROM Production.Product

EXCEPT

SELECT ProductID

FROM Production.WorkOrder ;

A join is a method of combining data from two or more tables into one result, based on a condition or a column that is common to both tables. There are four types of joins: cross join, inner join, outer join, and self-join.

The cross join is a join that displays one row for every possible pairing of rows from two tables. In the SQL statement, the CROSS JOIN keyword is entered between the two table names that are joined.

The inner join is a join that displays records from both tables that have matching values. The values of the columns being joined are compared using a comparison operator. In the SQL statement, the INNER JOIN keyword is entered in the FROM clause between the names of the two tables that are joined, followed by the ON clause that contains the condition.

The outer join is a join that selects all the rows from one table along with the matching rows from the second table. The OUTER JOIN keyword is entered in the FROM clause between the names of the two tables that are joined, followed by the ON clause that contains the search condition. One or more columns can be used to join the tables.

The LEFT OUTER JOIN or the LEFT JOIN includes all rows from the first table along with the matching rows in the second table.

The RIGHT OUTER JOIN or the RIGHT JOIN includes all rows from the second table along with the matching data in the first table.

The FULL OUTER JOIN or the FULL JOIN includes all rows from both tables in the result, regardless of the matching value present in either table.

The self join is a join that relates data in a table to itself. In the SQL statement, the table alias is used in the condition to identify the table from which the column needs to be accessed. The table to be joined to itself is assigned two table alias names and then referred to as two tables. The INNER JOIN keyword is entered in the FROM clause between the table names. In a self join, both table names are the same, but the table alias names are different. The join condition is entered in the ON clause.

A report is an organized collection of data extracted from one or more tables by a query so that it can be previewed on-screen, printed, or saved as a file. The output of a query can be saved as an XML report by combining the query result with additional clauses available in SQL Server 2008.

XML stands for eXtensible Markup Language. XML is used to create custom markups, thereby allowing the users to define their own elements. The main purpose of XML is to facilitate the sharing of structured data across the Internet. XML is easily readable because it uses tags, which are self-descriptive. XML is similar to HTML in its coding format, but unlike HTML, which is used to display data, XML is used to define data.

The FOR clause is a clause that is used to return the results of a query either as the BROWSE option or as the XML option. The BROWSE option specifies that updates be allowed while viewing data in a database. The XML option can be used to return the results of a query in an XML file format.

A subquery, also known as an inner query, is a query that is contained within an outer query. A subquery is used when the value needed by the outer query's condition is unknown. The subquery retrieves the value and returns it to the outer query. Subqueries can also return computed values. They are always written within parenthesis and can contain multiple inner queries, with each inner query enclosed within parentheses. You can sort and group records retrieved by the subquery.

A modified comparison operator is a comparison operator whose function is modified by combining it with the ANY or ALL logical operator. The ANY and ALL operators are used only with comparison operators.

--Using =ANY

USE AdventureWorks2008R2;

GO

SELECT Name

FROM Production.Product

WHERE ProductSubcategoryID =ANY

    (SELECT ProductSubcategoryID

     FROM Production.ProductSubcategory

     WHERE Name = 'Wheels') ;

--Using IN

USE AdventureWorks2008R2;

GO

SELECT Name

FROM Production.Product

WHERE ProductSubcategoryID IN

    (SELECT ProductSubcategoryID

     FROM Production.ProductSubcategory

     WHERE Name = 'Wheels') ;

SELECT * FROM Employees WHERE ([Employee ID] <> ALL (SELECT [Employee ID] FROM Employees AS Employees_1 WHERE ([Employee ID] < 10)))

Modified comparison operators are used in the condition of an outer query to compare a value with the maximum or minimum value in the list of values generated by a subquery. The value to be compared is specified before the operator. The choice of comparing a value with the maximum or minimum value in a list depends on the combination of the comparison operator and the ANY or ALL operators used in the search condition.

The combination of the comparison operator and the ANY and ALL operators determines the comparison performed in the condition of an outer query.

The EXISTS operator is a logical operator that is used in the condition of an outer query to check for the existence of records returned by a subquery. In the search condition, column names or constant values are not specified before the EXISTS operator.

You can use the EXISTS operator to display records based on the success or failure of the subquery's condition. If this condition is true, the subquery generates records. The EXISTS operator then confirms the existence of these records by returning the value TRUE. Based on this confirmation, the outer query displays records corresponding to the records returned by the subquery. The SELECT clause of the subquery typically contains an asterisk instead of specific column names.

A correlated subquery is a subquery that is executed simultaneously with the outer query. Unlike other subqueries, the correlated subquery depends on the input from the outer query to return values. A correlated subquery is always written in the search condition of an outer query. The value to be searched, specified in the WHERE clause of the outer query, must always be a constant value and not a column name.

The APPLY operator is a relational operator that enables you to apply a table expression to all rows of an outer table. The APPLY operator can be used with multiple tables and can contain a WHERE condition. The output of a query using an APPLY operator will be similar to a cross join and the table expression could be a table, view, a derived table, or even a function that returns a table.

A nested subquery is a subquery that contains multiple levels of inner queries. Each inner query in a nested subquery is enclosed within parenthesis. The number of closing parentheses at the end of the nested subquery must match the number of inner queries contained within the subquery. An unlimited number of inner queries can be contained within a nested subquery. Nested subqueries are used in situations where an outer query requires the values returned by many levels of subqueries to display the required records.

The INSERT  statement is a SQL statement that is used to insert a record into a table. By executing this statement, you can insert only one record at a time. You can use a single INSERT statement to copy multiple records into a table by adding a SELECT clause to the statement. The records returned by the SELECT clause populate the table indicated in the INSERT clause. The order of the columns specified in the SELECT clause must match the column order in the INSERT statement or in the table from which records are retrieved.

The OUTPUT clause is a SQL clause that enables you to obtain information from rows, which are influenced by the INSERT, UPDATE, or DELETE statements. The OUTPUT clause can be used only in combination with the INSERT, DELETE, or UPDATE statement and will return data modified using these statements in the form of a table.

DELETE Sales.ShoppingCartItem

    OUTPUT DELETED.*;

The UPDATE statement is a SQL statement that modifies the data in a column. The statement consists of two parts—an UPDATE clause with the name of the table whose column values need to be updated, and a SET clause that specifies the column name and the new value assigned to the column.

The DELETE statement is a SQL statement that deletes records from a table. Columns cannot be deleted using the DELETE statement. The name of the target table has to be specified after the DELETE statement. The DELETE statement deletes all records from a table. However, you can delete specific records by retrieving the targeted records using a search condition in a WHERE statement. You can also use a subquery in the search condition to help retrieve the records that need to be deleted.

The TRUNCATE TABLE statement is a SQL statement that deletes all records from a table without disturbing the table structure. The name of the table to be truncated is specified using the TRUNCATE TABLE statement. Although the DELETE statement can also perform this function, when deleting records from a large table, the DELETE statement requires more time than the TRUNCATE TABLE statement to complete the task. Unlike the DELETE statement, the TRUNCATE TABLE statement cannot delete specific records.

The MERGE statement is a SQL statement that can be used to merge data present in two tables or columns. In SQL Server 2008, the MERGE statement can be used to perform the actions of the INSERT, UPDATE, and DELETE statements using a single statement. These actions are atomic and more efficient than executing individual INSERT, UPDATE, and DELETE statements. When using the MERGE statement, you need to specify a source table in the USING clause and a target table in the MERGE INTO clause. A MERGE statement must be terminated using a semicolon.

MERGE Production.ProductInventory AS target

USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod

    JOIN Sales.SalesOrderHeader AS soh

    ON sod.SalesOrderID = soh.SalesOrderID

    AND soh.OrderDate = @OrderDate

    GROUP BY ProductID) AS source (ProductID, OrderQty)

ON (target.ProductID = source.ProductID)

WHEN MATCHED AND target.Quantity - source.OrderQty<= 0

    THEN DELETE

WHEN MATCHED

    THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,

target.ModifiedDate = GETDATE()

OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,

Deleted.Quantity, Deleted.ModifiedDate;

GO

Data integrity is the state of information in a database where all values stored in the database are correct. The integrity of data in a database indicates whether the values are valid and whether they can be used further.

A constraint is a validation mechanism implemented in a column of a table to ensure data integrity. Constraints define rules that test the values specified for a column when inserting, modifying, or deleting a record. If the values do not comply with the rules defined by the constraint, then the records cannot be inserted, updated, or deleted.

A primary key is a column or a combination of columns that stores values to uniquely identify each record in a table. A value entered in any record of a primary key column cannot be identical to a value in any other record of that column. A table can contain only one primary key, but a primary key can contain multiple columns. Such primary keys are called composite keys. A primary key column cannot store null values.

A PRIMARY KEY constraint is a database constraint that creates a primary key in a column or in a combination of columns in a table. By creating a primary key, the constraint ensures that the values stored in the records of a table are not duplicated. You can define a PRIMARY KEY constraint as a column-level constraint or as a table-level constraint when writing the CREATE TABLE statement.

A UNIQUE  constraint is a database constraint which is implemented in a column that needs to store unique values in each record of the column. Although PRIMARY KEY constraints implement the same feature, only one primary key can be created in the table. In contrast, multiple UNIQUE constraints can be created in a table. In addition, columns that are constrained by the UNIQUE constraint can accept null values. The UNIQUE constraint can be defined as a table-level constraint or as a column-level constraint. A UNIQUE constraint can also be referenced by a FOREIGN KEY constraint.

A foreign key is a column that links the records in one table with the records in another table. The table whose column is referenced by the foreign key of another table is called the referenced table. To maintain referential integrity, the values stored by the foreign key must match the values stored in the primary key or unique key columns of the referenced table. The records in a foreign key can store values that exist in other records of the key.

A FOREIGN KEY constraint is a database constraint that creates a foreign key on a table. The FOREIGN KEY constraint is defined when creating a table with the CREATE TABLE statement. The constraint can be defined as a column-level or as a table-level constraint. You cannot modify an existing FOREIGN KEY constraint without deleting the constraint and creating it with a new definition.

A DEFAULT constraint is a database constraint that specifies a default value for a column. The constraint can be defined when creating a table with the CREATE TABLE statement. The default value specified must correspond to the datatype of the constrained column. The DEFAULT constraint can be specified only as a column-level constraint. The constraint is defined with the DEFAULT keyword specified after the column definition, followed by the constant value that is to be used as the default value.

A CHECK constraint is a database constraint that validates the values stored in a column, based on a condition specified with the constraint. The condition is a logical operator that returns a value TRUE or FALSE. If a column value being inserted or modified does not comply with the logical expression, the insert or update will be disallowed. A CHECK constraint can use multiple logical expressions linked with logical operators such as AND and OR. Multiple CHECK constraints can be defined for a column and each constraint is implemented on the column's value in the order in which they are defined.

Sparse columns are used to store NULL values. A sparse column occupies no space when it contains only NULL values. When a sparse column is used to store NOT NULL values, it occupies more space than usual. Sparse columns can be queried using the SELECT statement similar to querying normal columns. You can create a sparse column by including the SPARSE attribute in the column definition.

The SELECT INTO statement is a SQL statement that creates a backup of a table's structure and data into a new table. You can also copy the contents from multiple tables or views into a new table. However, the SELECT INTO statement cannot be used with the COMPUTE clause because the result sets of the tables, generated using the COMPUTE clause will not be stored in the database. Therefore, when new tables are created using the SELECT INTO statement, any calculations produced during the execution of the COMPUTE clause will not appear in the new table.

The DROP TABLE statement is a SQL statement that deletes a table from a database. The name of the table to be dropped must be specified after the DROP TABLE clause. In addition to the records in the table, the table's definition, which includes the column definitions and any constraints provided in the definition, is also deleted when you execute this statement. However, if a table targeted for deletion is being referenced by the foreign key of another table, the referencing foreign key constraint or the referencing table must be dropped before the target table can be deleted.

A view is a virtual table that retrieves and displays records from another table. Unlike database tables, virtual tables do not store values but only display the records stored in another table. The table from which records are chosen for the view is called a source table. The view acts as an interface between a user and the source table. The changes made to the data in the records of a view get reflected in the source table as well. Views are used to retrieve specific records from specific columns if the source table contains a large number of records or columns. Views are also used to present records from multiple tables as a single virtual table.

The CREATE VIEW statement is a SQL statement that enables you to create views. The name of the view is specified immediately after the CREATE VIEW clause. You need to specify a SELECT statement following the view name and the AS keyword. This statement retrieves and displays records in the form of a virtual table. A view can be based on a single table or multiple tables with the table names specified in the FROM clause of the SELECT statement.

SCHEMABINDING is a SQL statement that is used to protect the source table on which a view is created. It is written after the CREATE VIEW statement. The source table, which has been schema bound with a view cannot be altered. When a source table is altered, the view that is created on the table will become unusable. To avoid this, views are generally schema bound.

The TOP keyword is specified in a SELECT clause to limit the number of records displayed. Only the number of records specified by the TOP keyword will be displayed, even if there are more records that meet the search criteria. You can retrieve a specific number of records by indicating a constant value. To retrieve a specific percentage of records, you must specify a constant value followed by the PERCENT keyword. If the SELECT statement in a CREATE VIEW statement contains an ORDER BY clause, you must specify the TOP keyword with the SELECT statement.

An alias is a reference used for a table or a column. An alias helps you avoid confusions, especially when you want to refer to a specific column in a table, while there is another column with the same name in another table. Aliases can be used with the AS keyword or within single quotes if used without the AS keyword.

The ALTER VIEW statement is a SQL statement that enables you to modify a view. By specifying a new SELECT statement with the ALTER VIEW statement, the stored query of a view is modified. The name of the view to be modified is specified with the ALTER VIEW clause. The SELECT statement is specified following the view name and the AS keyword. You can specify a WITH CHECK OPTION clause with the ALTER VIEW statement to ensure that the data in the view complies with the search condition of its SELECT statement.

The DROP VIEW statement is a SQL statement that deletes a view from a database. Before deleting a table, it is essential to delete a view based on the table by using the DROP VIEW statement. The name of the view to be deleted is specified after the DROP VIEW clause.

An index is a data organization mechanism that helps in the speedy retrieval of records. When you create an index on a column of a table, a pointer is assigned to each value of the indexed column. If a query uses a search value stored in an indexed column, it uses the pointer to identify the search value and to retrieve a record. Columns whose values are frequently used by queries as search values must be indexed to speed up record retrieval. Indices are particularly effective in speeding up record retrieval when used on columns of tables with large volumes of records. You can create an index on multiple columns of a table. Without deleting the index, you cannot delete the indexed columns.

The CREATE INDEX statement is a SQL statement that creates an index on a table column. The name of the index is specified immediately after the CREATE INDEX clause. The name of the table that contains the targeted column is specified after the index name and the ON keyword. The column targeted for indexing is specified after the table name and is enclosed within parenthesis. If multiple column names are specified, the names are separated by commas. The CREATE INDEX statement creates a clustered or non-clustered index on the column or columns specified with the statement. You can also choose to create a UNIQUE clustered or non-clustered index.

The DROP INDEX statement is a SQL statement that enables you to delete an index created on a table. The statement can delete all clustered indices except for clustered indices created by a primary key or UNIQUE constraint. The name of the table containing the index and the index name is specified after the DROP INDEX clause. The table name and the index name are separated by a period.

A transaction is a collection of SQL statements that is executed as a single unit. The BEGIN TRAN statement marks the beginning of a transaction. The name of the transaction is specified after the BEGIN TRAN clause. The SAVE TRAN statement denotes a savepoint and marks the end of the transaction. The name of the savepoint is specified after the SAVE TRAN clause. Because of the data modification made by SQL statements in a transaction, the state of the data in a database changes after executing the transaction.

BEGIN { TRAN | TRANSACTION }

    [ {transaction_name | @tran_name_variable }

      [ WITH MARK [ 'description' ] ]

    ]

[ ; ]

The ROLLBACK TRAN statement is a SQL statement that cancels the modification of data implemented by the SQL statements in a transaction. You can revert the modification up to the last savepoint in the transaction. You can also choose to cancel all modifications done in the transaction by rolling back the entire transaction. The name of the transaction or the savepoint must be specified after the ROLLBACK TRAN clause depending on the type of rollback that is required.

USE tempdb;

GO

CREATE TABLE ValueTable ([value] int;)

GO

DECLARE @TransactionNamevarchar(20) = 'Transaction1';

--The following statements start a named transaction,

--insert two rows, and then roll back

--the transaction named in the variable @TransactionName.

--Another statement outside of the named transaction inserts two rows.

--The query returns the results of the previous statements.

BEGIN TRAN @TransactionName

       INSERT INTO ValueTableVALUES(1), (2);

ROLLBACK TRAN @TransactionName;

INSERT INTO ValueTableVALUES(3),(4);

SELECT [value] FROM ValueTable;

DROP TABLE ValueTable;

--Results

--value

-------------

--3

--4

The COMMIT TRAN statement is a SQL statement that saves the data modification done by SQL statements in a transaction. This statement saves the data modifications done by all SQL statements executed after the last saved SQL statement. The name of the transaction is specified after the COMMIT TRAN clause. After the COMMIT TRAN statement is executed for a transaction, you cannot rollback the data modifications made by the SQL statements in the transaction.

DECLARE @TranNameVARCHAR(20);

SELECT @TranName = 'MyTransaction';

BEGIN TRANSACTION @TranName;

USE AdventureWorks2012;

DELETE FROM AdventureWorks2012.HumanResources.JobCandidate

    WHERE JobCandidateID = 13;

COMMIT TRANSACTION @TranName;

GO

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.