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.
If you execute the package with debugging (press F5), the
package should succeed and appear as shown here:
