MS SQL Server un MS Access SQL valodas dialekts
MS SQL Server un MS Access SQL valodas dialekts
1. MS Access datu bāzes Jet un MS Database Engine.
2. MS Accessizmantošana lietojumu veidošanai priekš MS SQL Server.
3. MS Access un MS SQL Server SQL valodas dialekti.
4. Select tipa vaicājumu iespēju apskats.
4.1. Vienkārši vaicājumi vienai tabulai, atlasot kolonas (laukus) un rindas.
4.2. Vienkārši vaicājumi vairākām tabulām, atlasot kolonas (laukus) un rindas.
4.3. Aprēķinu kolonu (lauku) veidošana, kolonu nosaukumu norāde.
4.4. Atbildes dublējošo rindu izslēgšana lietojot DISTINCT.
4.5. LIKE izmantošana.
4.6. Vienkārši pakārtotie vaicājumi (subqueries):
SELECT rindā;
FROM rindā;
WHERE rindā.
4.7. EXISTS un NOT EXISTS izmantošana.
4.8. Korelētie pakārtotie vaivājumi.
4.9. Grupēšanas GROUP BY lietošana.
4.10. GROUP BY lietošana ar aprēķinu izteiksmi.
4.11. GROUP BY ALL izmantošana.
4.12. ORDER BY izmantošana.
4.13. Grupu noteikumu norāde ar HAVING.
4.14. Pakārtotie vaicājumi HAVING rindā.
4.15. Grupu agregātvērtību aprēķins lietojot COMPUTE un COMPUTE BY.
4.16. Vairāku COMPUTE klauzulu izmantošana.
4.17. GROUP BY un COMPUTE salīdzinājums.
4.18. Klauzulas CUBE lietošana.
4.19. Funkcijas GROUPING izmantošana.
4.20. GROUPING funkcijas lietošana ar klauzulu CUBE.
4.21. ROLLUP operatora lietošana.
4.22. Norāžu optimizatoram izmantošana:
INDEX norāde;
GROUP norāde;
UNION darbības norādes.
4.23. Jaunu tabulu veidošana ar SELECT INTO.
5. MS SQL Server SQL valodas paplāšinājums Transact SQL.
5.1. Transact SQL valodas mainīgie.
5.2. Transact SQL valodas operatori.
5.2. Transact SQL vadības konstrukcijas.
5.3. Kursoru tipi (statiskie, slēdža tipa, virknes, dināmiskie).
5.4. Kursoru lietošana datu izgūšanai no datu bāzes.
5.5. MS SQL Server funkcijas.
5.6. SQL vaicājumu realizēšana Transact SQL vidē.
6. Datu noliktavas, datu vitrīnas un daudzdimensiju vaicājumu valoda (Multidimensional Expresion Language – MXL).
6.1 MXL vispārējā sintakse.
6.2. MXL funkcijas.
6.3. MXL vaicājumu tipi.
7. ADO objektu izmantošana datu izguvei no dažādiem avotiem.
Select tipa vaicājumi
SELECT [ ALL | DISTINCT ] [ TOP integer | TOP integer PERCENT ] [WITH TIES ] select_saraksts [ INTO jauna_tabula ]
FROM tabulveida_datu_avoti
WHERE ierakstu_meklēšanas_noteikumi
GROUP BY [ALL] ierakstu_grupēšanas_noteikumi [ WITH { CUBE |
ROLLUP }]
HAVING ierakstu_grupas_atlases_noteikumi
ORDER BY ierakstu_šķirošanas_noteikumi [ ASC | DESC ]
[ COMPUTE { { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ]
[ BY expression [ ,...n ] ] ]
[ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT }
[ , XMLDATA ]
[ , ELEMENTS ]
[ , BINARY base64 ] } ]
[ OPTION ( < query_hint > [ ,...n ]) ]
SELECT_tipa_vaicājums
UNION [ ALL ]
SELECT_tipa_vaicājums
SELECT Clause
Specifies the columns to be returned by the query.
SELECT [ ALL | DISTINCT ]
[ TOP n [ PERCENT ] [ WITH TIES ] ]
< select_list >
< select_list > ::=
{ *
| { table_name | view_name | table_alias }.*
| { column_name | expression | IDENTITYCOL | ROWGUIDCOL }
[ [ AS ] column_alias ]
| column_alias = expression
} [ ,...n ]
ALL
Specifies that duplicate rows can appear in the result set. ALL is the default.
DISTINCT
Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.
TOP n [PERCENT]
Specifies that only the first n rows are to be output from the query result set. n is an integer between 0 and 4294967295. If PERCENT is also specified, only the first n percent of the rows are output from the result set. When specified with PERCENT, n must be an integer between 0 and 100.
If the query includes an ORDER BY clause, the first n rows (or n percent of rows) ordered by the ORDER BY clause are output. If the query has no ORDER BY clause, the order of the rows is arbitrary.
WITH TIES
Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP ...WITH TIES can only be specified if an ORDER BY clause is specified.
< select_list >
The columns to be selected for the result set. The select list is a series of expressions separated by commas.
*
Specifies that all columns from all tables and views in the FROM clause should be returned. The columns are returned by table or view, as specified in the FROM clause, and in the order in which they exist in the table or view.
table_name | view_name | table_alias.*
Limits the scope of the * to the specified table or view.
column_name
Is the name of a column to return. Qualify column_name to prevent an ambiguous reference, such as occurs when two tables in the FROM clause have columns with duplicate names. For example, the Customers and Orders tables in the Northwind database both have a column named ColumnID. If the two tables are joined in a query, the customer ID can be specified in the select list as Customers.CustomerID.
expression
Is a column name, constant, function, any combination of column names, constants, and functions connected by an operator(s), or a subquery.
IDENTITYCOL
Returns the identity column. If the more than one table in the FROM clause has a column with the IDENTITY property, IDENTITYCOL must be qualified with the specific table name, such as T1.IDENTITYCOL.
ROWGUIDCOL
Returns the row global unique identifier column. If the more than one table in the FROM clause with the ROWGUIDCOL property, ROWGUIDCOL must be qualified with the specific table name, such as T1.ROWGUIDCOL.
column_alias
Is an alternative name to replace the column name in the query result set. For example, an alias such as "Quantity", or "Quantity to Date", or "Qty" can be specified for a column named quantity.
Aliases are used also to specify names for the results of expressions, for example:
USE Northwind
SELECT AVG(UnitPrice) AS 'Average Price'
FROM [Order Details]
column_alias can be used in an ORDER BY clause. However, it cannot be used in a WHERE, GROUP BY, or HAVING clause. If the query expression is part of a DECLARE CURSOR statement, column_alias cannot be used in the FOR UPDATE clause.
INTO Clause
Creates a new table and inserts the resulting rows from the query into it.
The user executing a SELECT statement with the INTO clause must have CREATE TABLE permission in the destination database. SELECT...INTO cannot be used with the COMPUTE. You can use SELECT...INTO to create an identical table definition (different table name) with no data by having a FALSE condition in the WHERE clause.
[ INTO new_table ]
new_table
Specifies the name of a new table to be created, based on the columns in the select list and the rows chosen by the WHERE clause. The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, and value as the corresponding expression in the select list.
When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time SELECT...INTO was executed.
In this release of SQL Server, the select into/bulkcopy database option has no effect on whether you can create a permanent table with SELECT INTO. The amount of logging for certain bulk operations, including SELECT INTO, depends on the recovery model in effect for the database. In previous releases, creating a permanent table with SELECT INTO was allowed only if select into/bulkcopy was set.
select into/bulkcopy is available for backward compatibility purposes, but may not be supported in future releases.
FROM Clause
Specifies the table(s) from which to retrieve rows. The FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).
[ FROM { < table_source > } [ ,...n ] ]
< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
| view_name [ [ AS ] table_alias ]
| rowset_function [ [ AS ] table_alias ]
| OPENXML
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| < joined_table >
< joined_table > ::=
< table_source > < join_type > < table_source > ON < search_condition >
| < table_source > CROSS JOIN < table_source >
| < joined_table >
< join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ]
[ < join_hint > ]
JOIN
< table_source >
Specifies tables, views, derived tables, and joined tables for the SELECT statement.
table_name [ [ AS ] table_alias ]
Specifies the name of a table and an optional alias.
view_name [ [ AS ] table_alias ]
Specifies the name, a view, and an optional alias.
rowset_function [ [ AS ] table_alias ]
Is the name of a rowset function and an optional alias
OPENXML
Provides rowset view over an XML document.
WITH ( < table_hint > [ ,...n ] )
Specifies one or more table hints.
derived_table [ [ AS ] table_alias ]
Is a nested SELECT statement, retrieving rows from the specified database and table(s).
column_alias
Is an optional alias to replace a column name in the result set.
< joined_table >
Is a result set that is the product of two or more tables. For example:
SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1 = tab4.c1
ON tab2.c3 = tab4.c3
For multiple CROSS joins, use parentheses to change the natural order of the joins.
< join_type >
Specifies the type of join operation.
INNER
Specifies that all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.
LEFT [ OUTER ]
Specifies that all rows from the left table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the left table are set to NULL.
RIGHT [ OUTER ]
Specifies that all rows from the right table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the right table are set to NULL.
FULL [ OUTER ]
If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.
< join_hint >
Specifies a join hint or execution algorithm. If is specified, INNER, LEFT, RIGHT, or FULL must also be explicitly specified.
JOIN
Indicates that the specified tables or views should be joined.
ON < search_condition >
Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are often used. For example:
SELECT ProductID, Suppliers.SupplierID
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
When the condition specifies columns, the columns do not have to have the same name or same data type. However, if the data types are not identical, they must be either compatible or types that Microsoft® SQL Server™ can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type using the CAST function.
CROSS JOIN (Dekarta reizinājums)
Specifies the cross-product of two tables. Returns the same rows as if the tables to be joined were simply listed in the FROM clause and no WHERE clause was specified. For example, both of these queries return a result set that is a cross join of all the rows in T1 and T2:
SELECT * FROM T1, T2
SELECT * FROM T1 CROSS JOIN T2
WHERE Clause
Specifies a search condition to restrict the rows returned.
[ WHERE < search_condition > | < old_outer_join > ]
< old_outer_join > ::=
column_name { * = | = * } column_name
< search_condition >
Restricts the rows returned in the result set through the use of predicates. There is no limit to the number of predicates that can be included in a search condition.
< old_outer_join >
Specifies an outer join using the nonstandard product-specific syntax and the WHERE clause. The *= operator is used to specify a left outer join and the =* operator is used to specify a right outer join.
This example specifies a left outer join in which the rows from Tab1, that do not meet the specified condition, are included in the result set:
SELECT Tab1.name, Tab2.id
FROM Tab1, Tab2
WHERE Tab1.id *=Tab2.id
[pic]
Note Using this syntax for outer joins is discouraged because of the potential for ambiguous interpretation and because it is nonstandard. Instead, specify joins in the FROM clause.
It is possible to specify outer joins by using join operators in the FROM clause or by using the non-standard *= and =* operators in the WHERE clause. The two methods cannot both be used in the same statement.
GROUP BY Clause
Specifies the groups into which output rows are to be placed and, if aggregate functions are included in the SELECT clause , calculates a summary value for each group. When GROUP BY is specified, either each column in any non-aggregate expression in the select list should be included in the GROUP BY list, or the GROUP BY expression must match exactly the select list expression.
If the ORDER BY clause is not specified, groups returned using the GROUP BY clause are not in any particular order. It is recommended that you always use the ORDER BY clause to specify a particular ordering of the data.
[ GROUP BY [ ALL ] group_by_expression [ ,...n ] [ WITH { CUBE | ROLLUP } ] ]
ALL
Includes all groups and result sets, even those that do not have any rows that meet the search condition specified in the WHERE clause. When ALL is specified, null values are returned for the summary columns of groups that do not meet the search condition. You cannot specify ALL with the CUBE or ROLLUP operators.
GROUP BY ALL is not supported in queries that access remote tables if there is also a WHERE clause in the query.
group_by_expression
Is an expression on which grouping is performed. group_by_expression is also known as a grouping column. group_by expression can be a column or a nonaggregate expression that references a column. A column alias that is defined in the select list cannot be used to specify a grouping column.
Columns of type text, ntext, and image cannot be used in group_by_expression.
For GROUP BY clauses that do not contain CUBE or ROLLUP, the number of group_by_expression items is limited by the GROUP BY column sizes, the aggregated columns, and the aggregate values involved in the query. This limit originates from the limit of 8,060 bytes on the intermediate work table that is needed to hold intermediate query results. A maximum of 10 grouping expressions is permitted when CUBE or ROLLUP is specified.
CUBE
Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. A GROUP BY summary row is returned for every possible combination of group and subgroup in the result set. A GROUP BY summary row is displayed as NULL in the result, but is used to indicate all values. Use the GROUPING function to determine whether null values in the result set are GROUP BY summary values.
The number of summary rows in the result set is determined by the number of columns included in the GROUP BY clause. Each operand (column) in the GROUP BY clause is bound under the grouping NULL and grouping is applied to all other operands (columns). Because CUBE returns every possible combination of group and subgroup, the number of rows is the same, regardless of the order in which the grouping columns are specified.
ROLLUP
Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. Groups are summarized in a 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 produced in the result set.
Distinct aggregates, for example, AVG(DISTINCT column_name), COUNT(DISTINCT column_name), and SUM(DISTINCT column_name), are not supported when using CUBE or ROLLUP. If used, SQL Server returns an error message and cancels the query.
Datu tabula Darbinieki
1. vaicājuma piemērs ar GROUP BY.
SELECT DZIMUMS, AMATS, SUM(ALGA) Summa
FROM Darbinieki
GROUP BY DZIMUMS, AMATS;
2. vaicājuma piemērs ar CUBE.
SELECT DZIMUMS, AMATS, SUM(ALGA) Summa
FROM Darbinieki
GROUP BY DZIMUMS, AMATS WITH CUBE;
3. vaicājuma piemērs ar ROLLUP
SELECT DZIMUMS, AMATS, SUM(ALGA) Summa
FROM Darbinieki
GROUP BY DZIMUMS, AMATS WITH ROLLUP;
4. vaicājuma piemērs ar funkciju GROUPING.
SELECT DZIMUMS, AMATS, SUM(ALGA) AS Summa,
GROUPING(DZIMUMS) AS Dz, GROUPING(AMATS) AS Am
FROM Darbinieki
GROUP BY DZIMUMS, AMATS WITH ROLLUP;
HAVING Clause
Specifies a search condition for a group or an aggregate. HAVING is usually used with the GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
[ HAVING < search_condition > ]
< search_condition >
Specifies the search condition for the group or the aggregate to meet. When HAVING is used with GROUP BY ALL, the HAVING clause overrides ALL. The text, image, and ntext data types cannot be used in a HAVING clause.
Note Using the HAVING clause in the SELECT statement does not affect the way the CUBE operator groups the result set and returns summary aggregate rows.
Search Condition
Is a combination of one or more predicates using the logical operators AND, OR, and NOT.
< search_condition > ::=
{ [ NOT ] < predicate > | ( < search_condition > ) }
[ { AND | OR } [ NOT ] { < predicate > | ( < search_condition > ) } ]
} [ ,...n ]
< predicate > ::=
{ expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression
| string_expression [ NOT ] LIKE string_expression
[ ESCAPE 'escape_character' ]
| expression [ NOT ] BETWEEN expression AND expression
| expression IS [ NOT ] NULL
| CONTAINS
( { column | * } , '< contains_search_condition >' )
| FREETEXT ( { column | * } , 'freetext_string' )
| expression [ NOT ] IN ( subquery | expression [ ,...n ] )
| expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }
{ ALL | SOME | ANY} ( subquery )
| EXISTS ( subquery )
}
< search_condition >
Specifies the conditions for the rows returned in the result set for a SELECT statement, query expression, or subquery. For an UPDATE statement, specifies the rows to be updated. For a DELETE statement, specifies the rows to be deleted. There is no limit to the number of predicates that can be included in a Transact-SQL statement search condition.
NOT Negates the Boolean expression specified by the predicate.
AND Combines two conditions and evaluates to TRUE when both of the conditions are TRUE.
OR Combines two conditions and evaluates to TRUE when either condition is TRUE.
< predicate > Is an expression that returns TRUE, FALSE, or UNKNOWN.
expression
Is a column name, a constant, a function, a variable, a scalar subquery, or any combination of column names, constants, and functions connected by an operator(s) or a subquery. The expression can also contain the CASE function.
= Is the operator used to test the equality between two expressions.
Is the operator used to test the condition of two expressions not being equal to each other.
!= Is the operator used to test the condition of two expressions not being equal to each other.
> Is the operator used to test the condition of one expression being greater than the other.
>= Is the operator used to test the condition of one expression being greater than or equal to the other expression.
!> Is the operator used to test the condition of one expression not being greater than the other expression.
< Is the operator used to test the condition of one expression being less than the other.
[ ,...n ) ]
< query_hint > ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| { LOOP | MERGE | HASH } JOIN
| FAST number_rows
| FORCE ORDER
| MAXDOP number
| ROBUST PLAN
| KEEP PLAN
| KEEPFIXED PLAN
| EXPAND VIEWS
}
{ HASH | ORDER } GROUP
Specifies that aggregations described in the GROUP BY, DISTINCT, or COMPUTE clause of the query should use hashing or ordering.
{ MERGE | HASH | CONCAT } UNION
Specifies that all UNION operations are performed by merging, hashing, or concatenating UNION sets. If more than one UNION hint is specified, the query optimizer selects the least expensive strategy from those hints specified.
{ LOOP | MERGE | HASH } JOIN
Specifies that all join operations are performed by loop join, merge join, or hash join in the whole query. If more than one join hint is specified, the optimizer selects the least expensive join strategy from the allowed ones.
If, in the same query, a join hint is also specified for a specific pair of tables, this join hint takes precedence in the joining of the two tables although the query hints still must be honored. Thus, the join hint for the pair of tables may only restrict the selection of allowed join methods in the query hint.
FAST number_rows
Specifies that the query is optimized for fast retrieval of the first number_rows (a nonnegative integer). After the first number_rows are returned, the query continues execution and produces its full result set.
FORCE ORDER
Specifies that the join order indicated by the query syntax is preserved during query optimization.
MAXDOP number
Overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option. All semantic rules used with max degree of parallelism configuration option are applicable when using the MAXDOP query hint.
ROBUST PLAN
Forces the query optimizer to attempt a plan that works for the maximum potential row size, possibly at the expense of performance. When the query is processed, intermediate tables and operators may need to store and process rows that are wider than any of the input rows. The rows may be so wide that, in some cases, the particular operator cannot process the row. If this happens, SQL Server produces an error during query execution. By using ROBUST PLAN, you instruct the query optimizer not to consider any query plans that may encounter this problem.
KEEP PLAN
Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes (update, delete, or insert) have been made to a table. Specifying KEEP PLAN ensures that a query will not be recompiled as frequently when there are multiple updates to a table.
KEEPFIXED PLAN
Forces the query optimizer not to recompile a query due to changes in statistics or to the indexed column (update, delete, or insert). Specifying KEEPFIXED PLAN ensures that a query will be recompiled only if the schema of the underlying tables is changed or sp_recompile is executed against those tables.
EXPAND VIEWS
Specifies that the indexed views are expanded and the query optimizer will not consider any indexed view as a substitute for any part of the query. (A view is expanded when the view name is replaced by the view definition in the query text.) This query hint virtually disallows direct use of indexed views and indexes on indexed views in the query plan.
The indexed view is not expanded only if the view is directly referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH (NOEXPAND, INDEX( index_val [ ,...n ] ) ) is specified. Only the views in the SELECT portion of statements, including those in INSERT, UPDATE, and DELETE statements are affected by the hint.
Remarks
The order of the clauses in the SELECT statement is significant. Any of the optional clauses can be omitted, but when used, they must appear in the appropriate order.
SELECT statements are allowed in user-defined functions only if the select lists of these statements contain expressions that assign values to variables that are local to the functions.
A table variable, in its scope, may be accessed like a regular table and thus may be used as a table source in a SELECT statement.
A four-part name constructed with the OPENDATASOURCE function as the server-name part may be used as a table source in all places a table name can appear in SELECT statements.
Some syntax restrictions apply to SELECT statements involving remote tables. The length returned for text or ntext columns included in the select list defaults to the smallest of the actual size of the text, the default TEXTSIZE session setting, or the hard-coded application limit. To change the length of returned text for the session, use the SET statement. By default, the limit on the length of text data returned with a SELECT statement is 4,000 bytes.
SQL Server raises exception 511 and rolls back the current executing statement if either of these occur:
• The SELECT statement produces a result row or an intermediate work table row exceeding 8,060 bytes.
• The DELETE, INSERT, or UPDATE statement attempts action on a row exceeding 8,060 bytes.
In SQL Server, an error occurs if no column name is given to a column created by a SELECT INTO or CREATE VIEW statement.
Selecting Identity Columns
When selecting an existing identity column into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:
• The SELECT statement contains a join, GROUP BY clause, or aggregate function.
• Multiple SELECT statements are joined with UNION.
• The identity column is listed more than once in the select list.
• The identity column is part of an expression.
If any of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. All rules and restrictions for the identity columns apply to the new table.
Old-Style Outer Joins
Earlier versions of SQL Server supported the definition of outer joins that used the *= and =* operators in the WHERE clause. SQL Server version 7.0 supports the SQL-92 standard, which provides join operators in the FROM clause. It is recommended that queries be rewritten to use the SQL-92 syntax.
Processing Order of WHERE, GROUP BY, and HAVING Clauses
This list shows the processing order for a SELECT statement with a WHERE clause, a GROUP BY clause, and a HAVING clause:
1. The WHERE clause excludes rows not meeting its search condition.
2. The GROUP BY clause collects the selected rows into one group for each unique value in the GROUP BY clause.
3. Aggregate functions specified in the select list calculate summary values for each group.
4. The HAVING clause further excludes rows not meeting its search condition.
Permissions
SELECT permissions default to members of the sysadmin fixed server role, the db_owner and db_datareader fixed database roles, and the table owner. Members of the sysadmin, db_owner, and db_securityadmin roles, and the table owner can transfer permissions to other users.
If the INTO clause is used to create a permanent table, the user must have CREATE TABLE permission in the destination database.
CONTAINS
Is a predicate used to search columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINS can search for:
1) A word or phrase.
2) The prefix of a word or phrase.
3) A word near another word.
4) A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
5) A word that has a higher designated weighting than another word.
CONTAINS
( { column | * } , '< contains_search_condition >'
)
< contains_search_condition > ::=
{ < simple_term >
| < prefix_term >
| < generation_term >
| < proximity_term >
| < weighted_term >
}
| { ( < contains_search_condition > )
{ AND | AND NOT | OR } < contains_search_condition > [ ...n ]
}
< simple_term > ::=
word | " phrase "
< prefix term > ::=
{ "word * " | "phrase * " }
< generation_term > ::=
FORMSOF ( INFLECTIONAL , < simple_term > [ ,...n ] )
< proximity_term > ::=
{ < simple_term > | < prefix_term > }
{ { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ]
< weighted_term > ::=
ISABOUT
( { {
< simple_term >
| < prefix_term >
| < generation_term >
| < proximity_term >
}
[ WEIGHT ( weight_value ) ]
} [ ,...n ]
)
column
Is the name of a specific column that has been registered for full-text searching. Columns of the character string data types are valid full-text searching columns.
*
Specifies that all columns in the table registered for full-text searching should be used to search for the given contains search condition(s). If more than one table is in the FROM clause, * must be qualified by the table name.
Specifies some text to search for in column. Variables cannot be used for the search condition.
word
Is a string of characters without spaces or punctuation.
phrase
Is one or more words with spaces between each word.
Note Some languages, such as those in Asia, can have phrases that consist of one or more words without spaces between them.
Specifies a match for an exact word (one or more characters without spaces or punctuation in single-byte languages) or a phrase (one or more consecutive words separated by spaces and optional punctuation in single-byte languages). Examples of valid simple terms are "blue berry", blueberry, and "Microsoft SQL Server". Phrases should be enclosed in double quotation marks (""). Words in a phrase must appear in the same order as specified in as they appear in the database column. The search for characters in the word or phrase is case insensitive. Noise words (such as a, and, or the) in full-text indexed columns are not stored in the full-text index. If a noise word is used in a single word search, SQL Server returns an error message indicating that only noise words are present in the query. SQL Server includes a standard list of noise words in the directory \Mssql\Ftdata\Sqlserver\Config.
Punctuation is ignored. Therefore, CONTAINS(testing, "computer failure") matches a row with the value, "Where is my computer? Failure to find it would be expensive."
Specifies a match of words or phrases beginning with the specified text. Enclose a prefix term in double quotation marks ("") and add an asterisk (*) before the ending quotation mark, so that all text starting with the simple term specified before the asterisk is matched. The clause should be specified this way: CONTAINS (column, '"text*"') The asterisk matches zero, one, or more characters (of the root word or words in the word or phrase). If the text and asterisk are not delimited by double quotation marks, as in CONTAINS (column, 'text*'), full-text search considers the asterisk as a character and will search for exact matches to text*.
When is a phrase, each word contained in the phrase is considered to be a separate prefix. Therefore, a query specifying a prefix term of "local wine *" matches any rows with the text of "local winery", "locally wined and dined", and so on.
Specifies a match of words when the included simple terms include variants of the original word for which to search.
INFLECTIONAL
Specifies that the plural and singular, as well as the gender and neutral forms of nouns, verbs, and adjectives should be matched. The various tenses of verbs should be matched too.
A given within a will not match both nouns and verbs.
Specifies a match of words or phrases that must be close to one another. operates similarly to the AND operator: both require that more than one word or phrase exist in the column being searched. As the words in appear closer together, the better the match.
NEAR | ~
Indicates that the word or phrase on the left side of the NEAR or ~ operator should be approximately close to the word or phrase on the right side of the NEAR or ~ operator. Multiple proximity terms can be chained, for example:
a NEAR b NEAR c
This means that word or phrase a should be near word or phrase b, which should be near word or phrase c.
Microsoft® SQL Server™ ranks the distance between the left and right word or phrase. A low rank value (for example, 0) indicates a large distance between the two. If the specified words or phrases are far apart from each other, the query is considered to be satisfied; however, the query has a very low (0) rank value. However, if consists of only one or more NEAR proximity terms, SQL Server does not return rows with a rank value of 0.
Specifies that the matching rows (returned by the query) match a list of words and phrases, each optionally given a weighting value.
ISABOUT
Specifies the keyword.
WEIGHT (weight_value)
Specifies a weight value which is a number from 0.0 through 1.0. Each component in may include a weight_value. weight_value is a way to change how various portions of a query affect the rank value assigned to each row matching the query. Weighting forces a different measurement of the ranking of a value because all the components of are used together to determine the match. A row is returned if there is a match on any one of the ISABOUT parameters, whether or not a weight value is assigned
AND | AND NOT | OR
Specifies a logical operation between two contains search conditions. When contains parenthesized groups, these parenthesized groups are evaluated first. After evaluating parenthesized groups, these rules apply when using these logical operators with contains search conditions:
• NOT is applied before AND.
• NOT can only occur after AND, as in AND NOT. The OR NOT operator is not allowed. NOT cannot be specified before the first term (for example, CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ).
• AND is applied before OR.
• Boolean operators of the same type (AND, OR) are associative and can therefore be applied in any order.
n
Is a placeholder indicating that multiple contains search conditions and terms within them can be specified.
CONTAINS is not recognized as a keyword if the compatibility level is less than 70.
Operatora CONTAINS lietošanas piemēri
SELECT NOSAUKUMS
FROM Produkti
WHERE CENA = 15.00 AND CONTAINS(IEPAKOJUMS, 'pudele');
B. Use CONTAINS and phrase in
This example returns all products that contain either the phrase "sasquatch ale" or "steeleye stout."
SELECT NOSAUKUMS, DAUDZUMS
FROM Produkti
WHERE CONTAINS(NOSAUKUMS,' "piens"OR "sviests" ');
SELECT NOSAUKUMS
FROM Produkti
WHERE CONTAINS(NOSAUKUMS, ' "ziv*" ');
SELECT NOSAUKUMS
FROM Preces
WHERE CONTAINS(NOSAUKUMS, '"jūras*" OR "upes*"')
E. Use CONTAINS with
This example returns all product names that have the word "Boysenberry" near the word "spread."
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, 'spread NEAR Boysenberry')
F. Use CONTAINS with
This example searches for all products with words of the form dry: dried, drying, and so on.
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ')
G. Use CONTAINS with
This example searches for all product names containing the words spread, sauces, or relishes, and different weightings are given to each word.
SELECT CategoryName, Description
FROM Categories
WHERE CONTAINS(Description, 'ISABOUT (spread weight (.8),
sauces weight (.4), relishes weight (.2) )' )
H. Use CONTAINS with variables
This example uses a variable instead of a specific search term.
DECLARE @SearchWord varchar(30)
SET @SearchWord ='Moon'
SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)
CONTAINSTABLE
Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.
Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.
CONTAINSTABLE ( table , { column | * } , ' < contains_search_condition > '
[ , top_n_by_rank ] )
< contains_search_condition > ::=
{ < simple_term >
| < prefix_term >
| < generation_term >
| < proximity_term >
| < weighted_term >
}
| { ( < contains_search_condition > )
{ AND | AND NOT | OR } < contains_search_condition > [ ...n ]
}
< simple_term > ::=
word | " phrase "
< prefix term > ::=
{ "word * " | "phrase * " }
< generation_term > ::=
FORMSOF ( INFLECTIONAL , < simple_term > [ ,...n ] )
< proximity_term > ::=
{ < simple_term > | < prefix_term > }
{ { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ]
< weighted_term > ::=
ISABOUT
( { {
< simple_term >
| < prefix_term >
| < generation_term >
| < proximity_term >
}
[ WEIGHT ( weight_value ) ]
} [ ,...n ]
)
Arguments
table
Is the name of the table that has been marked for full-text querying. table can be a one-, two-, or three-part database object name. table cannot specify a server name and cannot be used in queries against linked servers.
column
Is the name of the column to search, which resides in table. Columns of the character string data types are valid full-text searching columns.
*
Specifies that all columns in the table that have been registered for full-text searching should be used to search for the given contains search condition(s).
top_n_by_rank
Specifies that only the n highest ranked matches, in descending order, are returned. Applies only when an integer value, n, is specified.
Specifies some text to search for in column. Variables cannot be used for the search condition. For more information, see CONTAINS.
Remarks
The table returned has a column named KEY that contains full-text key values. Each full-text indexed table has a column whose values are guaranteed to be unique, and the values returned in the KEY column are the full-text key values of the rows that match the selection criteria specified in the contains search condition. The TableFulltextKeyColumn property, obtained from the OBJECTPROPERTY function, provides the identity for this unique key column. To obtain the rows you want from the original table, specify a join with the CONTAINSTABLE rows. The typical form of the FROM clause for a SELECT statement using CONTAINSTABLE is:
SELECT select_list
FROM table AS FT_TBL INNER JOIN
CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
The table produced by CONTAINSTABLE includes a column named RANK. The RANK column is a value (from 0 through 1000) for each row indicating how well a row matched the selection criteria. This rank value is typically used in one of these ways in the SELECT statement:
• In the ORDER BY clause to return the highest-ranking rows as the first rows in the table.
• In the select list to see the rank value assigned to each row.
• In the WHERE clause to filter out rows with low rank values.
CONTAINSTABLE is not recognized as a keyword if the compatibility level is less than 70. For more information, see sp_dbcmptlevel.
Permissions
Execute permissions are available only by users with the appropriate SELECT privileges on the table or the referenced table's columns.
Examples
A. Return rank values using CONTAINSTABLE
This example searches for all product names containing the words breads, fish, or beers, and different weightings are given to each word. For each returned row matching this search criteria, the relative closeness (ranking value) of the match is shown. In addition, the highest ranking rows are returned first.
USE Northwind
GO
SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE(Categories, Description,
'ISABOUT (breads weight (.8),
fish weight (.4), beers weight (.2) )' ) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO
B. Return rank values greater than specified value using CONTAINSTABLE
This example returns the description and category name of all food categories for which the Description column contains the words "sweet and savory" near either the word "sauces" or the word "candies." All rows with a category name "Seafood" are disregarded. Only rows with a rank value of 2 or higher are returned.
USE Northwind
GO
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
AND FT_TBL.CategoryName 'Seafood'
ORDER BY KEY_TBL.RANK DESC
C. Return top 10 ranked results using CONTAINSTABLE and Top_n_by_rank
This example returns the description and category name of the top 10 food categories where the Description column contains the words "sweet and savory" near either the word "sauces" or the word "candies."
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
, 10
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
Expressions
A combination of symbols and operators that Microsoft® SQL Server™ evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression.
{ constant
| scalar_function
| [ alias. ] column
| local_variable
| ( expression )
| ( scalar_subquery )
| { unary_operator } expression
| expression { binary_operator } expression
}
constant
Is a symbol that represents a single, specific data value. constant is one or more alphanumeric characters (letters a-z, A-Z, and numbers 0-9) or symbols (exclamation point (!), at sign (@), number sign (#), and so on). Character and datetime values are enclosed in quotation marks, while binary strings and numeric constants are not. For more information, see Constants.
scalar_function
Is a unit of Transact-SQL syntax that provides a specific service and returns a single value. scalar_function can be built-in scalar functions, such as the SUM, GETDATE, or CAST functions, or scalar user-defined functions.
[alias.]
Is the alias, or correlation name, assigned to a table by the AS keyword in the FROM clause.
column
Is the name of a column. Only the name of the column is allowed in an expression; a four-part name cannot be specified.
local_variable
Is the name of a user-defined variable. For more information, see DECLARE @local_variable.
(expression)
Is any valid SQL Server expression as defined in this topic. The parentheses are grouping operators that ensure that all the operators in the expression within the parentheses are evaluated before the resulting expression is combined with another.
(scalar_subquery)
Is a subquery that returns one value.
{unary_operator}
Is an operator that has only one numeric operand:
1) + indicates a positive number.
2) - indicates a negative number.
3) ~ indicates the one's complement operator.
Unary operators can be applied only to expressions that evaluate to any of the data types of the numeric data type category.
{binary_operator}
Is an operator that defines the way two expressions are combined to yield a single result. binary _operator can be an arithmetic operator, the assignment operator (=), a bitwise operator, a comparison operator, a logical operator, the string concatenation operator (+), or a unary operator.
For a simple expression built of a single constant, variable, scalar function, or column name, the data type, collation, precision, scale, and value of the expression is the data type, collation, precision, scale, and value of the referenced element.
When two expressions are combined using comparison or logical operators, the resulting data type is Boolean and the value is one of three values: TRUE, FALSE, or UNKNOWN. When two expressions are combined using arithmetic, bitwise, or string operators, the operator determines the resulting data type.
Complex expressions made up of many symbols and operators evaluate to a single-valued result. The data type, collation, precision, and value of the resulting expression is determined by combining the component expressions, two at a time, until a final result is reached. The sequence in which the expressions are combined is defined by the precedence of the operators in the expression.
Remarks
Two expressions can be combined by an operator if they both have data types supported by the operator and at least one of these conditions is TRUE:
1) The expressions have the same data type.
2) The data type with the lower precedence can be implicitly converted to the data type with the higher data type precedence.
3) The CAST function can explicitly convert the data type with the lower precedence to either the data type with the higher precedence or to an intermediate data type that can be implicitly converted to the data type with the higher precedence.
If there is no supported implicit or explicit conversion, the two expressions cannot be combined.
The collation of any expression that evaluates to a character string is set following the rules of collation precedence.
Loģikas funkcijas CASE lietošana
SELECT Valsts_nosaukums = CASE Tabula_1.VALSTS
WHEN ‘LAT’ THEN ‘Latvija’
WHEN ‘LIE’ THEN ‘Lietuva’
WHEN ‘IGA’ THEN ‘Igaunija’
ELSE ‘Nav zināma’
END, Tabula_1.GADS, …
SELECT Daudzums = CASE WHEN GADS = 2000 THEN 20 000
WHEN GADS = 2001 THEN 40 000
ELSE 0
END, …
Funkcijas ISNULL lietošana
SELECT Samaksa = ISNULL(TABULA_1.ALGA, 0), …
Join Fundamentals
By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft® SQL Server™ 2000 should use data from one table to select the rows in another table.
A join condition defines the way two tables are related in a query by:
• Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
• Specifying a logical operator (=, , and so on) to be used in comparing values from the columns.
Joins can be specified in either the FROM or WHERE clauses. The join conditions combine with the WHERE and HAVING search conditions to control the rows that are selected from the base tables referenced in the FROM clause.
Specifying the join conditions in the FROM clause helps separate them from any other search conditions that may be specified in a WHERE clause, and is the recommended method for specifying joins. A simplified SQL-92 FROM clause join syntax is:
FROM first_table join_type second_table [ON (join_condition)]
join_type specifies what kind of join is performed: an inner, outer, or cross join. join_condition defines the predicate to be evaluated for each pair of joined rows. This is an example of a FROM clause join specification:
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
This is a simple SELECT statement using this join:
SELECT ProductID,
Suppliers.SupplierID,
CompanyName
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
WHERE UnitPrice > $10
AND CompanyName LIKE N'F%'
GO
The select returns the product and supplier information for any combination of parts supplied by a company for which the company name starts with the letter F and the price of the product is more than $10.
When multiple tables are referenced in a single query, all column references must be unambiguous. In the previous example, both the Products and Suppliers table have a column named SupplierID. Any column name that is duplicated between two or more tables referenced in the query must be qualified with the table name. All references to the SupplierID columns in the example are qualified.
When a column name is not duplicated in two or more tables used in the query, references to it do not have to be qualified with the table name. This is shown in the previous example. Such a SELECT statement is sometimes difficult to understand because there is nothing to indicate the table that provided each column. The readability of the query is improved if all columns are qualified with their table names. The readability is further improved if table aliases are used, especially when the table names themselves must be qualified with the database and owner names. This is the same example, except that table aliases have been assigned and the columns qualified with table aliases to improve readability:
SELECT P.ProductID,
S.SupplierID,
panyName
FROM Suppliers AS S JOIN Products AS P
ON (S.SupplierID = P.SupplierID)
WHERE P.UnitPrice > $10
AND panyName LIKE N'F%'
The previous examples specified the join conditions in the FROM clause, which is the preferred method. This query contains the same join condition specified in the WHERE clause:
SELECT P.ProductID,
S.SupplierID,
panyName
FROM Suppliers AS S, Products AS P
WHERE S.SupplierID = P.SupplierID
AND P.UnitPrice > $10
AND panyName LIKE N'F%'
The select list for a join can reference all the columns in the joined tables, or any subset of the columns. The select list is not required to contain columns from every table in the join. For example, in a three-table join, only one table can be used to bridge from one of the other tables to the third table, and none of the columns from the middle table have to be referenced in the select list.
Although join conditions usually have equality comparisons (=), other comparison or relational operators can be specified, as can other predicates. For more information, see Using Operators in Expressions and WHERE.
When SQL Server processes joins, the query engine chooses the most efficient method (out of several possibilities) of processing the join. Although the physical execution of various joins uses many different optimizations, the logical sequence is:
• The join conditions in the FROM clause are applied.
• The join conditions and search conditions from the WHERE clause are applied.
• The search conditions from the HAVING clause are applied.
This sequence can sometimes influence the results of the query if conditions are moved between the FROM and WHERE clauses.
Columns used in a join condition are not required to have the same name or be the same data type. However, if the data types are not identical, they must be compatible, or be types that SQL Server can implicitly convert. If the data types cannot be implicitly converted, the join condition must explicitly convert the data type using the CAST function. For more information about implicit and explicit conversions, see Data Type Conversion.
Most queries using a join can be rewritten using a subquery (a query nested within another query), and most subqueries can be rewritten as joins. For more information about subqueries, see Subquery Fundamentals.
[pic]
Note Tables cannot be joined directly on ntext, text, or image columns. However, tables can be joined indirectly on ntext, text, or image columns by using SUBSTRING. For example, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) performs a two-table inner join on the first 20 characters of each text column in tables t1 and t2. In addition, another possibility for comparing ntext or text columns from two tables is to compare the lengths of the columns with a WHERE clause, for example (where a self-join is performed on the pub_info table):
WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)
Subquery Fundamentals
A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this example a subquery is used as a column expression named MaxUnitPrice in a SELECT statement.
SELECT Ord.OrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice)
FROM Northwind.dbo.[Order Details] AS OrdDet
WHERE Ord.OrderID = OrdDet.OrderID) AS MaxUnitPrice
FROM Northwind.dbo.Orders AS Ord
A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.
Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases, a join approach would yield better results. This is an example showing both a subquery SELECT and a join SELECT that return the same result set:
/* SELECT statement built using a subquery. */
SELECT ProductName
FROM Northwind.dbo.Products
WHERE UnitPrice =
(SELECT UnitPrice
FROM Northwind.dbo.Products
WHERE ProductName = 'Sir Rodney''s Scones')
/* SELECT statement built using a join that returns
the same result set. */
SELECT Prd1.ProductName
FROM Northwind.dbo.Products AS Prd1
JOIN Northwind.dbo.Products AS Prd2
ON (Prd1.UnitPrice = Prd2.UnitPrice)
WHERE Prd2.ProductName = 'Sir Rodney''s Scones'
A subquery nested in the outer SELECT statement has the following components:
• A regular SELECT query including the regular select list components.
• A regular FROM clause including one or more table or view names.
• An optional WHERE clause.
• An optional GROUP BY clause.
• An optional HAVING clause.
The SELECT query of a subquery is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified.
A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query. Individual queries may not support nesting up to 32 levels. A subquery can appear anywhere an expression can be used, if it returns a single value.
If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output (the select list of the outer query).
Statements that include a subquery usually take one of these formats:
• WHERE expression [NOT] IN (subquery)
• WHERE expression comparison_operator [ANY | ALL] (subquery)
• WHERE [NOT] EXISTS (subquery)
In some Transact-SQL statements, the subquery can be evaluated as if it were an independent query. Conceptually, the subquery results are substituted into the outer query (although this is not necessarily how Microsoft® SQL Server™ actually processes Transact-SQL statements with subqueries).
There are three basic types of subqueries. Those that:
• Operate on lists introduced with IN, or those that a comparison operator modified by ANY or ALL.
• Are introduced with an unmodified comparison operator and must return a single value.
• Are existence tests introduced with EXISTS.
Using Variables and Parameters
Transact-SQL has several ways to pass data between Transact-SQL statements. Among these are:
• Transact-SQL local variables.
A Transact-SQL variable is an object in Transact-SQL batches and scripts that can hold a data value. After the variable has been declared, or defined, one Transact-SQL statement in a batch can set the variable to a value and a later statement in the batch can get the value from the variable. For example:
DECLARE @EmpIDVar INT
SET @EmpIDVar = 1234
SELECT *
FROM Employees
WHERE EmployeeID = @EmpIDVar
• Transact-SQL parameters.
A parameter is an object used to pass data between a stored procedure and the batch or script that executes the stored procedure. Parameters can be either input or output parameters. For example:
CREATE PROCEDURE ParmSample @EmpIDParm INT AS
SELECT *
FROM Employees
WHERE EmployeeID = @EmpIDParm
GO
EXEC ParmSample @EmpIDParm = 1234
GO
Applications use application variables and parameter markers to work with the data from Transact-SQL statements.
• Application variables
The application programming languages such as C, C++, Basic, and Java have their own variables for holding data. Applications using the database APIs must move the data returned by Transact-SQL statements into application variables before they can work with the data. This is typically done using a process called binding. The application uses an API function to bind the result set column to a program variable. When a row is fetched the API provider or driver moves the data from the column to the bound program variable.
• Parameter markers
Parameter markers are supported by the ADO, OLE DB, and ODBC-based database APIs. A parameter marker is a question mark (?) placed in the location of an input expression in a Transact-SQL statement. The parameter marker is then bound to an application variable. This allows data from application variables to be used as input in Transact-SQL statements. Parameter markers also let stored procedure output parameters and return codes be bound to application variables. The output data is then returned to the bound variables when the procedure is executed. The DB-Library API also supports binding stored procedure parameter and return codes to program variables.
SELECT Examples
A. Use SELECT to retrieve rows and columns
This example shows three code examples. This first code example returns all rows (no WHERE clause is specified) and all columns (using the *) from the authors table in the pubs database.
USE pubs
SELECT *
FROM authors
ORDER BY au_lname ASC, au_fname ASC
-- Alternate way.
USE pubs
SELECT authors.*
FROM customers
ORDER BY au_lname ASC, au_fname ASC
This example returns all rows (no WHERE clause is specified), and only a subset of the columns (au_lname, au_fname, phone, city, state) from the authors table in the pubs database. In addition, column headings are added.
USE pubs
SELECT au_fname, au_lname, phone AS Telephone, city, state
FROM authors
ORDER BY au_lname ASC, au_fname ASC
This example returns only the rows for authors who live in California and do not have the last name McBadden.
USE pubs
SELECT au_fname, au_lname, phone AS Telephone
FROM authors
WHERE state = 'CA' and au_lname 'McBadden'
ORDER BY au_lname ASC, au_fname ASC
B. Use SELECT with column headings and calculations
These examples return all rows from titles. The first example returns total year-to-date sales and the amounts due to each author and publisher. In the second example, the total revenue is calculated for each book.
USE pubs
SELECT ytd_sales AS Sales,
authors.au_fname + ' '+ authors.au_lname AS Author,
ToAuthor = (ytd_sales * royalty) / 100,
ToPublisher = ytd_sales - (ytd_sales * royalty) / 100
FROM titles INNER JOIN titleauthor
ON titles.title_id = titleauthor.title_id INNER JOIN authors
ON titleauthor.au_id = authors.au_id
ORDER BY Sales DESC, Author ASC
Here is the result set:
Sales Author ToAuthor ToPublisher
----------- ------------------------- ----------- -----------
22246 Anne Ringer 5339 16907
22246 Michel DeFrance 5339 16907
18722 Marjorie Green 4493 14229
15096 Reginald Blotchet-Halls 2113 12983
8780 Cheryl Carson 1404 7376
4095 Abraham Bennet 409 3686
4095 Akiko Yokomoto 409 3686
4095 Ann Dull 409 3686
4095 Burt Gringlesby 409 3686
4095 Dean Straight 409 3686
4095 Marjorie Green 409 3686
4095 Michael O'Leary 409 3686
4095 Sheryl Hunter 409 3686
4072 Johnson White 407 3665
3876 Michael O'Leary 387 3489
3876 Stearns MacFeather 387 3489
3336 Charlene Locksley 333 3003
2045 Albert Ringer 245 1800
2045 Anne Ringer 245 1800
2032 Innes del Castillo 243 1789
375 Livia Karsen 37 338
375 Stearns MacFeather 37 338
375 Sylvia Panteley 37 338
111 Albert Ringer 11 100
NULL Charlene Locksley NULL NULL
(25 row(s) affected)
This is the query that calculates the revenue for each book:
USE pubs
SELECT 'Total income is', price * ytd_sales AS Revenue,
'for', title_id AS Book#
FROM titles
ORDER BY Book# ASC
Here is the result set:
Revenue Book#
--------------- --------------------- ---- ------
Total income is 81859.0500 for BU1032
Total income is 46318.2000 for BU1111
Total income is 55978.7800 for BU2075
Total income is 81859.0500 for BU7832
Total income is 40619.6800 for MC2222
Total income is 66515.5400 for MC3021
Total income is NULL for MC3026
Total income is 201501.0000 for PC1035
Total income is 81900.0000 for PC8888
Total income is NULL for PC9999
Total income is 8096.2500 for PS1372
Total income is 22392.7500 for PS2091
Total income is 777.0000 for PS2106
Total income is 81399.2800 for PS3333
Total income is 26654.6400 for PS7777
Total income is 7856.2500 for TC3218
Total income is 180397.2000 for TC4203
Total income is 61384.0500 for TC7777
(18 row(s) affected)
C. Use DISTINCT with SELECT
This example uses DISTINCT to prevent the retrieval of duplicate author ID numbers.
USE pubs
SELECT DISTINCT au_id
FROM authors
ORDER BY au_id
D. Create tables with SELECT INTO
This first example creates a temporary table named #coffeetabletitles in tempdb. To use this table, always refer to it with the exact name shown, including the number sign (#).
USE pubs
DROP TABLE #coffeetabletitles
GO
SET NOCOUNT ON
SELECT * INTO #coffeetabletitles
FROM titles
WHERE price < $20
SET NOCOUNT OFF
SELECT name
FROM tempdb..sysobjects
WHERE name LIKE '#c%'
Here is the result set:
name
------------------------------------------------------------------------
#coffeetabletitles__________________________________________________________________________________________________000000000028
(1 row(s) affected)
CHECKPOINTing database that was changed.
(12 row(s) affected)
name
------------------------------------------------------------------------
newtitles
(1 row(s) affected)
CHECKPOINTing database that was changed.
This second example creates a permanent table named newtitles.
USE pubs
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'newtitles')
DROP TABLE newtitles
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
USE pubs
SELECT * INTO newtitles
FROM titles
WHERE price > $25 OR price < $20
SELECT name FROM sysobjects WHERE name LIKE 'new%'
USE master
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
Here is the result set:
name
------------------------------
newtitles
(1 row(s) affected)
E. Use correlated subqueries
This example shows queries that are semantically equivalent and illustrates the difference between using the EXISTS keyword and the IN keyword. Both are examples of a valid subquery retrieving one instance of each publisher name for which the book title is a business book, and the publisher ID numbers match between the titles and publishers tables.
USE pubs
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
-- Or
USE pubs
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type = 'business')
This example uses IN in a correlated (or repeating) subquery, which is a query that depends on the outer query for its values. It is executed repeatedly, once for each row that may be selected by the outer query. This query retrieves one instance of each author's first and last name for which the royalty percentage in the titleauthor table is 100 and for which the author identification numbers match in the authors and titleauthor tables.
USE pubs
SELECT DISTINCT au_lname, au_fname
FROM authors
WHERE 100 IN
(SELECT royaltyper
FROM titleauthor
WHERE titleauthor.au_id = authors.au_id)
The above subquery in this statement cannot be evaluated independently of the outer query. It needs a value for authors.au_id, but this value changes as Microsoft® SQL Server™ examines different rows in authors.
A correlated subquery can also be used in the HAVING clause of an outer query. This example finds the types of books for which the maximum advance is more than twice the average for the group.
USE pubs
SELECT t1.type
FROM titles t1
GROUP BY t1.type
HAVING MAX(t1.advance) >= ALL
(SELECT 2 * AVG(t2.advance)
FROM titles t2
WHERE t1.type = t2.type)
This example uses two correlated subqueries to find the names of authors who have participated in writing at least one popular computing book.
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE type = 'popular_comp'))
F. Use GROUP BY
This example finds the total year-to-date sales of each publisher in the database.
USE pubs
SELECT pub_id, SUM(ytd_sales) AS total
FROM titles
GROUP BY pub_id
ORDER BY pub_id
Here is the result set:
pub_id total
------ -----
0736 28286
0877 44219
1389 24941
(3 row(s) affected)
Because of the GROUP BY clause, only one row containing the sum of all sales is returned for each publisher.
G. Use GROUP BY with multiple groups
This example finds the average price and the sum of year-to-date sales, grouped by type and publisher ID.
USE pubs
SELECT type, pub_id, AVG(price) AS 'avg', sum(ytd_sales) AS 'sum'
FROM titles
GROUP BY type, pub_id
ORDER BY type, pub_id
Here is the result set:
type pub_id avg sum
------------ ------ --------------------- -----------
business 0736 2.9900 18722
business 1389 17.3100 12066
mod_cook 0877 11.4900 24278
popular_comp 1389 21.4750 12875
psychology 0736 11.4825 9564
psychology 0877 21.5900 375
trad_cook 0877 15.9633 19566
UNDECIDED 0877 NULL NULL
(8 row(s) affected)
Warning, null value eliminated from aggregate.
H. Use GROUP BY and WHERE
This example puts the results into groups after retrieving only the rows with advances greater than $5,000.
USE pubs
SELECT type, AVG(price)
FROM titles
WHERE advance > $5000
GROUP BY type
ORDER BY type
Here is the result set:
type
------------ --------------------------
business 2.99
mod_cook 2.99
popular_comp 21.48
psychology 14.30
trad_cook 17.97
(5 row(s) affected)
I. Use GROUP BY with an expression
This example groups by an expression. You can group by an expression if the expression does not include aggregate functions.
USE pubs
SELECT AVG(ytd_sales), ytd_sales * royalty
FROM titles
GROUP BY ytd_sales * royalty
ORDER BY ytd_sales * royalty
Here is the result set:
----------- -----------
NULL NULL
111 1110
375 3750
2032 24384
2045 24540
3336 33360
3876 38760
4072 40720
4095 40950
8780 140480
15096 211344
18722 449328
22246 533904
(13 row(s) affected)
J. Compare GROUP BY and GROUP BY ALL
The first example produces groups only for those books that commanded royalties of 10 percent. Because no modern cookbooks have a royalty of 10 percent, there is no group in the results for the mod_cook type.
The second example produces groups for all types, including modern cookbooks and UNDECIDED, although the modern cookbook group does not include any rows that meet the qualification specified in the WHERE clause.
The column that holds the aggregate value (the average price) is NULL for groups that lack qualifying rows.
USE pubs
SELECT type, AVG(price)
FROM titles
WHERE royalty = 10
GROUP BY type
ORDER BY type
Here is the result set:
type
------------ --------------------------
business 17.31
popular_comp 20.00
psychology 14.14
trad_cook 17.97
(4 row(s) affected)
-- Using GROUP BY ALL
USE pubs
SELECT type, AVG(price)
FROM titles
WHERE royalty = 10
GROUP BY all type
ORDER BY type
Here is the result set:
type
------------ --------------------------
business 17.31
mod_cook NULL
popular_comp 20.00
psychology 14.14
trad_cook 17.97
UNDECIDED NULL
(6 row(s) affected)
K. Use GROUP BY with ORDER BY
This example finds the average price of each type of book and orders the results by average price.
USE pubs
SELECT type, AVG(price)
FROM titles
GROUP BY type
ORDER BY AVG(price)
Here is the result set:
type
------------ --------------------------
UNDECIDED NULL
mod_cook 11.49
psychology 13.50
business 13.73
trad_cook 15.96
popular_comp 21.48
(6 row(s) affected)
L. Use the HAVING clause
The first example shows a HAVING clause with an aggregate function. It groups the rows in the titles table by type and eliminates the groups that include only one book. The second example shows a HAVING clause without aggregate functions. It groups the rows in the titles table by type and eliminates those types that do not start with the letter p.
USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING COUNT(*) > 1
ORDER BY type
Here is the result set:
type
------------
business
mod_cook
popular_comp
psychology
trad_cook
(5 row(s) affected)
This query uses the LIKE clause in the HAVING clause.
USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING type LIKE 'p%'
ORDER BY type
Here is the result set:
type
------------
popular_comp
psychology
(2 row(s) affected)
M. Use HAVING and GROUP BY
This example shows using GROUP BY, HAVING, WHERE, and ORDER BY clauses in one SELECT statement. It produces groups and summary values but does so after eliminating the titles with prices under $5. It also organizes the results by pub_id.
USE pubs
SELECT pub_id, SUM(advance), AVG(price)
FROM titles
WHERE price >= $5
GROUP BY pub_id
HAVING SUM(advance) > $15000
AND AVG(price) < $20
AND pub_id > '0800'
ORDER BY pub_id
Here is the result set:
pub_id
------ -------------------------- --------------------------
0877 26,000.00 17.89
1389 30,000.00 18.98
(2 row(s) affected)
N. Use HAVING with SUM and AVG
This example groups the titles table by publisher and includes only those groups of publishers who have paid more than $25,000 in total advances and whose books average more than $15 in price.
USE pubs
SELECT pub_id, SUM(advance), AVG(price)
FROM titles
GROUP BY pub_id
HAVING SUM(advance) > $25000
AND AVG(price) > $15
To see the publishers who have had year-to-date sales greater than $40,000, use this query:
USE pubs
SELECT pub_id, total = SUM(ytd_sales)
FROM titles
GROUP BY pub_id
HAVING SUM(ytd_sales) > 40000
If you want to make sure there are at least six books involved in the calculations for each publisher, use HAVING COUNT(*) > 5 to eliminate the publishers that return totals for fewer than six books. The query looks like this:
USE pubs
SELECT pub_id, SUM(ytd_sales) AS total
FROM titles
GROUP BY pub_id
HAVING COUNT(*) > 5
Here is the result set:
pub_id total
------ -----
0877 44219
1389 24941
(2 row(s) affected)
With this statement, two rows are returned. New Moon Books (0736) is eliminated.
O. Calculate group totals with COMPUTE BY
This example uses two code examples to show the use of COMPUTE BY. The first code example uses one COMPUTE BY with one aggregate function, and the second code example uses one COMPUTE BY item and two aggregate functions.
This example calculates the sum of the prices (for prices over $10) for each type of cookbook, in order first by type of book and then by price of book.
USE pubs
SELECT type, price
FROM titles
WHERE price > $10
AND type LIKE '%cook'
ORDER BY type, price
COMPUTE SUM(price) BY type
Here is the result set:
type price
------------ ---------------------
mod_cook 19.9900
(1 row(s) affected)
sum
---------------------
19.9900
(1 row(s) affected)
type price
------------ ---------------------
trad_cook 11.9500
trad_cook 14.9900
trad_cook 20.9500
(3 row(s) affected)
sum
---------------------
47.8900
(1 row(s) affected)
This example retrieves the book type, publisher identification number, and price of all cookbooks. The COMPUTE BY clause uses two different aggregate functions.
USE pubs
SELECT type, pub_id, price
FROM titles
WHERE type LIKE '%cook'
ORDER BY type, pub_id
COMPUTE SUM(price), MAX(pub_id) BY type
Here is the result set:
type pub_id price
------------ ------ ---------------------
mod_cook 0877 19.9900
mod_cook 0877 2.9900
(2 row(s) affected)
sum max
--------------------- ----
22.9800 0877
(1 row(s) affected)
type pub_id price
------------ ------ ---------------------
trad_cook 0877 20.9500
trad_cook 0877 11.9500
trad_cook 0877 14.9900
(3 row(s) affected)
sum max
--------------------- ----
47.8900 0877
(1 row(s) affected)
P. Calculate grand values using COMPUTE without BY
The COMPUTE keyword can be used without BY to generate grand totals, grand counts, and so on.
This statement finds the grand total of the prices and advances for all types of books over $20.
USE pubs
SELECT type, price, advance
FROM titles
WHERE price > $20
COMPUTE SUM(price), SUM(advance)
You can use COMPUTE BY and COMPUTE without BY in the same query. This query finds the sum of prices and advances by type, and then computes the grand total of prices and advances for all types of books.
USE pubs
SELECT type, price, advance
FROM titles
WHERE type LIKE '%cook'
ORDER BY type, price
COMPUTE SUM(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)
Here is the result set:
type price advance
------------ --------------------- ---------------------
mod_cook 2.9900 15000.0000
mod_cook 19.9900 .0000
(2 row(s) affected)
sum sum
--------------------- ---------------------
22.9800 15000.0000
(1 row(s) affected)
type price advance
------------ --------------------- ---------------------
trad_cook 11.9500 4000.0000
trad_cook 14.9900 8000.0000
trad_cook 20.9500 7000.0000
(3 row(s) affected)
sum sum
--------------------- ---------------------
47.8900 19000.0000
(1 row(s) affected)
sum sum
--------------------- ---------------------
70.8700 34000.0000
(1 row(s) affected)
Q. Calculate computed sums on all rows
This example shows only three columns in the select list and gives totals based on all prices and all advances at the end of the results.
USE pubs
SELECT type, price, advance
FROM titles
COMPUTE SUM(price), SUM(advance)
Here is the result set:
type price advance
------------ --------------------- ---------------------
business 19.9900 5000.0000
business 11.9500 5000.0000
business 2.9900 10125.0000
business 19.9900 5000.0000
mod_cook 19.9900 .0000
mod_cook 2.9900 15000.0000
UNDECIDED NULL NULL
popular_comp 22.9500 7000.0000
popular_comp 20.0000 8000.0000
popular_comp NULL NULL
psychology 21.5900 7000.0000
psychology 10.9500 2275.0000
psychology 7.0000 6000.0000
psychology 19.9900 2000.0000
psychology 7.9900 4000.0000
trad_cook 20.9500 7000.0000
trad_cook 11.9500 4000.0000
trad_cook 14.9900 8000.0000
(18 row(s) affected)
sum sum
--------------------- ---------------------
236.2600 95400.0000
(1 row(s) affected)
Warning, null value eliminated from aggregate.
R. Use more than one COMPUTE clause
This example finds the sum of the prices of all psychology books, as well as the sum of the prices of psychology books organized by publisher. You can use different aggregate functions in the same statement by including more than one COMPUTE BY clause.
USE pubs
SELECT type, pub_id, price
FROM titles
WHERE type = 'psychology'
ORDER BY type, pub_id, price
COMPUTE SUM(price) BY type, pub_id
COMPUTE SUM(price) BY type
Here is the result set:
type pub_id price
------------ ------ ---------------------
psychology 0736 7.0000
psychology 0736 7.9900
psychology 0736 10.9500
psychology 0736 19.9900
(4 row(s) affected)
sum
---------------------
45.9300
(1 row(s) affected)
type pub_id price
------------ ------ ---------------------
psychology 0877 21.5900
(1 row(s) affected)
sum
---------------------
21.5900
(1 row(s) affected)
sum
---------------------
67.5200
(1 row(s) affected)
S. Compare GROUP BY with COMPUTE
The first example uses the COMPUTE clause to calculate the sum for the prices of the different types of cookbooks. The second example produces the same summary information using only GROUP BY.
USE pubs
-- Using COMPUTE
SELECT type, price
FROM titles
WHERE type like '%cook'
ORDER BY type, price
COMPUTE SUM(price) BY type
Here is the result set:
type price
------------ ---------------------
mod_cook 2.9900
mod_cook 19.9900
(2 row(s) affected)
sum
---------------------
22.9800
(1 row(s) affected)
type price
------------ ---------------------
trad_cook 11.9500
trad_cook 14.9900
trad_cook 20.9500
(3 row(s) affected)
sum
---------------------
47.8900
(1 row(s) affected)
This is the second query using GROUP BY:
USE pubs
-- Using GROUP BY
SELECT type, SUM(price)
FROM titles
WHERE type LIKE '%cook'
GROUP BY type
ORDER BY type
Here is the result set:
type
------------ ---------------------
mod_cook 22.9800
trad_cook 47.8900
(2 row(s) affected)
T. Use SELECT with GROUP BY, COMPUTE, and ORDER BY clauses
This example returns only those rows with current year-to-date sales, and then computes the average book cost and total advances in descending order by type. Four columns of data are returned, including a truncated title. All computed columns appear within the select list.
USE pubs
SELECT CAST(title AS char(20)) AS title, type, price, advance
FROM titles
WHERE ytd_sales IS NOT NULL
ORDER BY type DESC
COMPUTE AVG(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)
Here is the result set:
title type price advance
-------------------- ------------ --------------------- ----------------
Onions, Leeks, and G trad_cook 20.9500 7000.0000
Fifty Years in Bucki trad_cook 11.9500 4000.0000
Sushi, Anyone? trad_cook 14.9900 8000.0000
(3 row(s) affected)
avg sum
--------------------- ---------------------
15.9633 19000.0000
(1 row(s) affected)
title type price advance
-------------------- ------------ --------------------- ----------------
Computer Phobic AND psychology 21.5900 7000.0000
Is Anger the Enemy? psychology 10.9500 2275.0000
Life Without Fear psychology 7.0000 6000.0000
Prolonged Data Depri psychology 19.9900 2000.0000
Emotional Security: psychology 7.9900 4000.0000
(5 row(s) affected)
avg sum
--------------------- ---------------------
13.5040 21275.0000
(1 row(s) affected)
title type price advance
-------------------- ------------ --------------------- ----------------
But Is It User Frien popular_comp 22.9500 7000.0000
Secrets of Silicon V popular_comp 20.0000 8000.0000
(2 row(s) affected)
avg sum
--------------------- ---------------------
21.4750 15000.0000
(1 row(s) affected)
title type price advance
-------------------- ------------ --------------------- ----------------
Silicon Valley Gastr mod_cook 19.9900 .0000
The Gourmet Microwav mod_cook 2.9900 15000.0000
(2 row(s) affected)
avg sum
--------------------- ---------------------
11.4900 15000.0000
(1 row(s) affected)
title type price advance
-------------------- ------------ --------------------- ----------------
The Busy Executive's business 19.9900 5000.0000
Cooking with Compute business 11.9500 5000.0000
You Can Combat Compu business 2.9900 10125.0000
Straight Talk About business 19.9900 5000.0000
(4 row(s) affected)
avg sum
--------------------- ---------------------
13.7300 25125.0000
(1 row(s) affected)
sum sum
--------------------- ---------------------
236.2600 95400.0000
(1 row(s) affected)
U. Use SELECT statement with CUBE
This example shows two code examples. The first example returns a result set from a SELECT statement using the CUBE operator. The SELECT statement covers a one-to-many relationship between book titles and the quantity sold of each book. By using the CUBE operator, the statement returns an extra row.
USE pubs
SELECT SUBSTRING(title, 1, 65) AS title, SUM(qty) AS 'qty'
FROM sales INNER JOIN titles
ON sales.title_id = titles.title_id
GROUP BY title WITH CUBE
ORDER BY title
Here is the result set:
title qty
----------------------------------------------------------------- ------
NULL 493
But Is It User Friendly? 30
Computer Phobic AND Non-Phobic Individuals: Behavior Variations 20
Cooking with Computers: Surreptitious Balance Sheets 25
...
The Busy Executive's Database Guide 15
The Gourmet Microwave 40
You Can Combat Computer Stress! 35
(17 row(s) affected)
NULL represents all values in the title column. The result set returns values for the quantity sold of each title and the total quantity sold of all titles. Applying the CUBE operator or ROLLUP operator returns the same result.
This example uses the cube_examples table to show how the CUBE operator affects the result set and uses an aggregate function (SUM). The cube_examples table contains a product name, a customer name, and the number of orders each customer has made for a particular product.
USE pubs
CREATE TABLE cube_examples
(product_name varchar(30) NULL,
customer_name varchar(30) NULL,
number_of_orders int NULL
)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Outback Lager', 'Wilman Kala', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Romero y tomillo', 20)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Wilman Kala', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Outback Lager', 'Wilman Kala', 20)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Wilman Kala', 30)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Eastern Connection', 40)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Outback Lager', 'Eastern Connection', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Wilman Kala', 40)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Filo Mix', 'Romero y tomillo', 50)
First, issue a typical query with a GROUP BY clause and the result set.
USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
FROM cube_examples
GROUP BY product_name, customer_name
ORDER BY product_name
The GROUP BY causes the result set to form groups within groups. Here is the result set:
product_name customer_name
------------------------------ ------------------------------ ----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
(7 row(s) affected)
Next, issue a query with a GROUP BY clause by using the CUBE operator. The result set should include the same information, and super-aggregate information for each of the GROUP BY columns.
USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
FROM cube_examples
GROUP BY product_name, customer_name WITH CUBE
The result set for the CUBE operator holds the values from the simple GROUP BY result set above, and adds the super-aggregates for each column in the GROUP BY clause. NULL represents all values in the set from which the aggregate is computed. Here is the result set:
product_name customer_name
------------------------------ ------------------------------ ----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Filo Mix NULL 150
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Ikura NULL 70
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
Outback Lager NULL 40
NULL NULL 260
NULL Eastern Connection 50
NULL Romero y tomillo 100
NULL Wilman Kala 110
(14 row(s) affected)
Line 4 of the result set indicates that a total of 150 orders for Filo Mix was placed for all customers.
Line 11 of the result set indicates that the total number of orders placed for all products by all customers is 260.
Lines 12-14 of the result set indicate that the total number of orders for each customer for all products are 100, 110, and 50, respectively.
V. Use CUBE on a result set with three columns
This example shows two code examples. The first code example produces a CUBE result set with three columns, and the second example produces a four-column CUBE result set.
The first SELECT statement returns the publication name, title, and quantity of books sold. The GROUP BY clause in this example includes two columns called pub_name and title. There are also two one-to-many relationships between publishers and titles and between titles and sales.
By using the CUBE operator, the result set contains more detailed information about the quantities of titles sold by publishers. NULL represents all values in the title column.
USE pubs
SELECT pub_name, title, SUM(qty) AS 'qty'
FROM sales INNER JOIN titles
ON sales.title_id = titles.title_id INNER JOIN publishers
ON publishers.pub_id = titles.pub_id
GROUP BY pub_name, title
WITH CUBE
Here is the result set:
pub_name title qty
-------------------- ---------------------------------------- ------
Algodata Infosystems But Is It User Friendly? 30
Algodata Infosystems Cooking with Computers: Surreptitious Ba 25
Algodata Infosystems Secrets of Silicon Valley 50
Algodata Infosystems Straight Talk About Computers 15
Algodata Infosystems The Busy Executive's Database Guide 15
Algodata Infosystems NULL 135
Binnet & Hardley Computer Phobic AND Non-Phobic Individu 20
Binnet & Hardley Fifty Years in Buckingham Palace Kitche 20
... ...
NULL Sushi, Anyone? 20
NULL The Busy Executive's Database Guide 15
NULL The Gourmet Microwave 40
NULL You Can Combat Computer Stress! 35
(36 row(s) affected)
Increasing the number of columns in the GROUP BY clause shows why the CUBE operator is an n-dimensional operator. A GROUP BY clause with two columns returns three more kinds of groupings when the CUBE operator is used. The number of groupings can be more than three, depending on the distinct values in the columns.
The result set is grouped by the publisher name and then by the book title. The quantity of each title sold by each publisher is listed in the right-hand column.
NULL in the title column represents all titles. For more information about how to differentiate specific values and all values in the result set, see Example H. The CUBE operator returns these groups of information from one SELECT statement:
• Quantity of each title that each publisher has sold
• Quantity of each title sold
• Quantity of titles sold by each publisher
• Total number of titles sold by all publishers
Each column referenced in the GROUP BY clause has been cross-referenced with all other columns in the GROUP BY clause and the SUM aggregate has been reapplied, which produces additional rows in the result set. Information returned in the result set grows n-dimensionally along with the number of columns in the GROUP BY clause.
[pic]
Note Ensure that the columns following the GROUP BY clause have meaningful, real-life relationships with each other. For example, if you use au_fname and au_lname, the CUBE operator returns irrelevant information, such as the number of books sold by authors with the same first name. Using the CUBE operator on a real-life hierarchy, such as yearly sales and quarterly sales, produces meaningless rows in the result set. It is more efficient to use the ROLLUP operator.
In this second code example, the GROUP BY clause contains three columns cross-referenced by the CUBE operator. Three one-to-many relationships exist between publishers and authors, between authors and titles, and between titles and sales.
By using the CUBE operator, more detailed information is returned about the quantities of titles sold by publishers.
USE pubs
SELECT pub_name, au_lname, title, SUM(qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titles.title_id = titleauthor.title_id INNER JOIN publishers
ON publishers.pub_id = titles.pub_id INNER JOIN sales
ON sales.title_id = titles.title_id
GROUP BY pub_name, au_lname, title
WITH CUBE
The CUBE operator returns this information based on the cross-referenced groupings returned with the CUBE operator:
• Quantity of each title that each publisher has sold for each author
• Quantity of all titles each publisher has sold for each author
• Quantity of all titles each publisher has sold
• Total quantity of all titles sold by all publishers for all authors
• Quantity of each title sold by all publishers for each author
• Quantity of all titles sold by all publishers for each author
• Quantity of each title sold by each publisher for all authors
• Quantity of each title sold by all publishers for each author
[pic]
Note The super-aggregate for all publishers, all titles, and all authors is greater than the total number of sales, because a number of books have more than one author.
A pattern emerges as the number of relationships grow. The pattern of values and NULL in the report shows which groups have been formed for a summary aggregate. Explicit information about the groups is provided by the GROUPING function.
W. Use the GROUPING function with CUBE
This example shows how the SELECT statement uses the SUM aggregate, the GROUP BY clause, and the CUBE operator. It also uses the GROUPING function on the two columns listed after the GROUP BY clause.
USE pubs
SELECT pub_name, GROUPING(pub_name),title, GROUPING(title), SUM(qty) AS 'qty'
FROM sales INNER JOIN titles
ON sales.title_id = titles.title_id INNER JOIN publishers
ON publishers.pub_id = titles.pub_id
GROUP BY pub_name, title
WITH CUBE
The result set has two columns containing 0 and 1 values, which are produced by the GROUPING(pub_name) and GROUPING(title) expressions.
Here is the result set:
pub_name title qty
-------------------- --- ------------------------- --- -----------
Algodata Infosystems 0 But Is It User Friendly? 0 30
Algodata Infosystems 0 Cooking with Computers: S 0 25
Algodata Infosystems 0 Secrets of Silicon Valley 0 50
Algodata Infosystems 0 Straight Talk About Compu 0 15
Algodata Infosystems 0 The Busy Executive's Data 0 15
Algodata Infosystems 0 NULL 1 135
Binnet & Hardley 0 Computer Phobic AND Non-P 0 20
Binnet & Hardley 0 Fifty Years in Buckingham 0 20
... ...
NULL 1 The Busy Executive's Data 0 15
NULL 1 The Gourmet Microwave 0 40
NULL 1 You Can Combat Computer S 0 35
(36 row(s) affected)
X. Use the ROLLUP operator
This example shows two code examples. This first example retrieves the product name, customer name, and the sum of orders placed and uses the ROLLUP operator.
USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
AS 'Sum orders'
FROM cube_examples
GROUP BY product_name, customer_name
WITH ROLLUP
Here is the result set:
product_name customer_name Sum orders
------------------------------ ------------------------------ ----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Filo Mix NULL 150
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Ikura NULL 70
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
Outback Lager NULL 40
NULL NULL 260
(11 row(s) affected)
This second example performs a ROLLUP operation on the company and department columns and totals the number of employees.
The ROLLUP operator produces a summary of aggregates. This is useful when summary information is needed but a full CUBE provides extraneous data or when you have sets within sets. For example, departments within a company are a set within a set.
USE pubs
CREATE TABLE personnel
(
company_name varchar(20),
department varchar(15),
num_employees int
)
INSERT personnel VALUES ('Du monde entier', 'Finance', 10)
INSERT personnel VALUES ('Du monde entier', 'Engineering', 40)
INSERT personnel VALUES ('Du monde entier', 'Marketing', 40)
INSERT personnel VALUES ('Piccolo und mehr', 'Accounting', 20)
INSERT personnel VALUES ('Piccolo und mehr', 'Personnel', 30)
INSERT personnel VALUES ('Piccolo und mehr', 'Payroll', 40)
In this query, the company name, department, and the sum of all employees for the company become part of the result set, in addition to the ROLLUP calculations.
SELECT company_name, department, SUM(num_employees)
FROM personnel
GROUP BY company_name, department WITH ROLLUP
Here is the result set:
company_name department
-------------------- --------------- -----------
Du monde entier Engineering 40
Du monde entier Finance 10
Du monde entier Marketing 40
Du monde entier NULL 90
Piccolo und mehr Accounting 20
Piccolo und mehr Payroll 40
Piccolo und mehr Personnel 30
Piccolo und mehr NULL 90
NULL NULL 180
(9 row(s) affected)
Y. Use the GROUPING function
This example adds three new rows to the cube_examples table. Each of the three records NULL in one or more columns to show only the ROLLUP function produces a value of 1 in the grouping column. In addition, this example modifies the SELECT statement that was used in the earlier example.
USE pubs
-- Add first row with a NULL customer name and 0 orders.
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES ('Ikura', NULL, 0)
-- Add second row with a NULL product and NULL customer with real value
-- for orders.
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES (NULL, NULL, 50)
-- Add third row with a NULL product, NULL order amount, but a real
-- customer name.
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES (NULL, 'Wilman Kala', NULL)
SELECT product_name AS Prod, customer_name AS Cust,
SUM(number_of_orders) AS 'Sum Orders',
GROUPING(product_name) AS 'Grp prod_name',
GROUPING(customer_name) AS 'Grp cust_name'
FROM cube_examples
GROUP BY product_name, customer_name
WITH ROLLUP
The GROUPING function can be used only with CUBE or ROLLUP. The GROUPING function returns 1 when an expression evaluates to NULL, because the column value is NULL and represents the set of all values. The GROUPING function returns 0 when the corresponding column (whether it is NULL or not) did not come from either the CUBE or ROLLUP options as a syntax value. The returned value has a tinyint data type.
Here is the result set:
Prod Cust Sum Orders Grp prod_name Grp cust_name
------------- ------------------ ----------- ------------- -------------
NULL NULL 50 0 0
NULL Wilman Kala NULL 0 0
NULL NULL 50 0 1
Filo Mix Eastern Connection 40 0 0
Filo Mix Romero y tomillo 80 0 0
Filo Mix Wilman Kala 30 0 0
Filo Mix NULL 150 0 1
Ikura NULL 0 0 0
Ikura Romero y tomillo 20 0 0
Ikura Wilman Kala 50 0 0
Ikura NULL 70 0 1
Outback Lager Eastern Connection 10 0 0
Outback Lager Wilman Kala 30 0 0
Outback Lager NULL 40 0 1
NULL NULL 310 1 1
(15 row(s) affected)
Z. Use SELECT with GROUP BY, an aggregate function, and ROLLUP
This example uses a SELECT query that contains an aggregate function and a GROUP BY clause, which lists pub_name, au_lname, and title, in that order.
USE pubs
SELECT pub_name, au_lname, title, SUM(qty) AS 'SUM'
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titles.title_id = titleauthor.title_id INNER JOIN publishers
ON publishers.pub_id = titles.pub_id INNER JOIN sales
ON sales.title_id = titles.title_id
GROUP BY pub_name, au_lname, title
WITH ROLLUP
By using the ROLLUP operator, these groupings are created by moving right to left along the list of columns.
pub_name au_lname title SUM(qty)
pub_name au_lname NULL SUM(qty)
pub_name NULL NULL SUM(qty)
NULL NULL NULL SUM(qty)
NULL represents all values for that column.
If you use the SELECT statement without the ROLLUP operator, the statement creates a single grouping. The query returns a sum value for each unique combination of pub_name, au_lname, and title.
pub_name au_lname title SUM(qty)
Compare these examples with the groupings created by using the CUBE operator on the same query.
pub_name au_lname title SUM(qty)
pub_name au_lname NULL SUM(qty)
pub_name NULL NULL SUM(qty)
NULL NULL NULL SUM(qty)
NULL au_lname title SUM(qty)
NULL au_lname NULL SUM(qty)
pub_name NULL title SUM(qty)
NULL NULL title SUM(qty)
The groupings correspond to the information returned in the result set. NULL in the result set represents all values in the column. The ROLLUP operator returns the following data when the columns (pub_name, au_lname, title) are in the order listed in the GROUP BY clause:
• Quantity of each title that each publisher has sold for each author
• Quantity of all titles each publisher has sold for each author
• Quantity of all titles each publisher has sold
• Total quantity of all titles sold by all publishers for all authors
Here is the result set:
pub_name au_lname title SUM
----------------- ------------ ------------------------------------ ---
Algodata Infosys Bennet The Busy Executive's Database Guide 15
Algodata Infosys Bennet NULL 15
Algodata Infosys Carson NULL 30
Algodata Infosys Dull Secrets of Silicon Valley 50
Algodata Infosys Dull NULL 50
... ...
New Moon Books White Prolonged Data Deprivation: Four 15
New Moon Books White NULL 15
New Moon Books NULL NULL 316
NULL NULL NULL 791
(49 row(s) affected)
The GROUPING function can be used with the ROLLUP operator or with the CUBE operator. You can apply this function to one of the columns in the select list. The function returns either 1 or 0 depending upon whether the column is grouped by the ROLLUP operator.
a. Use the INDEX optimizer hint
This example shows two ways to use the INDEX optimizer hint. The first example shows how to force the optimizer to use a nonclustered index to retrieve rows from a table and the second example forces a table scan by using an index of 0.
-- Use the specifically named INDEX.
USE pubs
SELECT au_lname, au_fname, phone
FROM authors WITH (INDEX(aunmind))
WHERE au_lname = 'Smith'
Here is the result set:
au_lname au_fname phone
-------------------------------------- -------------------- ----------
Smith Meander 913 843-0462
(1 row(s) affected)
-- Force a table scan by using INDEX = 0.
USE pubs
SELECT emp_id, fname, lname, hire_date
FROM employee (index = 0)
WHERE hire_date > '10/1/1994'
b. Use OPTION and the GROUP hints
This example shows how the OPTION (GROUP) clause is used with a GROUP BY clause.
USE pubs
SELECT a.au_fname, a.au_lname, SUBSTRING(t.title, 1, 15)
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id
GROUP BY a.au_lname, a.au_fname, t.title
ORDER BY au_lname ASC, au_fname ASC
OPTION (HASH GROUP, FAST 10)
c. Use the UNION query hint
This example uses the MERGE UNION query hint.
USE pubs
SELECT *
FROM authors a1
OPTION (MERGE UNION)
SELECT *
FROM authors a2
d. Use a simple UNION
The result set in this example includes the contents of the ContactName, CompanyName, City, and Phone columns of both the Customers and SouthAmericanCustomers tables.
USE Northwind
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'SouthAmericanCustomers')
DROP TABLE SouthAmericanCustomers
GO
-- Create SouthAmericanCustomers table.
SELECT ContactName, CompanyName, City, Phone
INTO SouthAmericanCustomers
FROM Customers
WHERE Country IN ('USA', 'Canada')
GO
-- Here is the simple union.
USE Northwind
SELECT ContactName, CompanyName, City, Phone
FROM Customers
WHERE Country IN ('USA', 'Canada')
UNION
SELECT ContactName, CompanyName, City, Phone
FROM SouthAmericanCustomers
ORDER BY CompanyName, ContactName ASC
GO
e. Use SELECT INTO with UNION
In this example, the INTO clause in the first SELECT statement specifies that the table named CustomerResults holds the final result set of the union of the designated columns of the Customers and SouthAmericanCustomers tables.
USE Northwind
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomerResults')
DROP TABLE CustomerResults
GO
USE Northwind
SELECT ContactName, CompanyName, City, Phone INTO CustomerResults
FROM Customers
WHERE Country IN ('USA', 'Canada')
UNION
SELECT ContactName, CompanyName, City, Phone
FROM SouthAmericanCustomers
ORDER BY CompanyName, ContactName ASC
GO
f. Use UNION of two SELECT statements with ORDER BY
The order of certain parameters used with the UNION clause is important. This example shows the incorrect and correct use of UNION in two SELECT statements in which a column is to be renamed in the output.
/* INCORRECT */
USE Northwind
GO
SELECT City
FROM Customers
ORDER BY Cities
UNION
SELECT Cities = City
FROM SouthAmericanCustomers
GO
/* CORRECT */
USE Northwind
GO
SELECT Cities = City
FROM Customers
UNION
SELECT City
FROM SouthAmericanCustomers
ORDER BY Cities
GO
g. Use UNION of three SELECT statements showing the effects of ALL and parentheses
These examples use UNION to combine the results of three tables, in which all have the same 5 rows of data. The first example uses UNION ALL to show the duplicated records, and returns all 15 rows. The second example uses UNION without ALL to eliminate the duplicate rows from the combined results of the three SELECT statements, and returns 5 rows.
The final example uses ALL with the first UNION, and parentheses around the second UNION that is not using ALL. The second UNION is processed first because it is in parentheses, and returns 5 rows because the ALL option is not used and the duplicates are removed. These 5 rows are combined with the results of the first SELECT through the UNION ALL keywords, which does not remove the duplicates between the two sets of 5 rows. The final result has 10 rows.
USE Northwind
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomersOne')
DROP TABLE CustomersOne
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomersTwo')
DROP TABLE CustomersTwo
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomersThree')
DROP TABLE CustomersThree
GO
USE Northwind
GO
SELECT ContactName, CompanyName, City, Phone INTO CustomersOne
FROM Customers
WHERE Country = 'Mexico'
GO
SELECT ContactName, CompanyName, City, Phone INTO CustomersTwo
FROM Customers
WHERE Country = 'Mexico'
GO
SELECT ContactName, CompanyName, City, Phone INTO CustomersThree
FROM Customers
WHERE Country = 'Mexico'
GO
-- Union ALL
SELECT ContactName
FROM CustomersOne
UNION ALL
SELECT ContactName
FROM CustomersTwo
UNION ALL
SELECT ContactName
FROM CustomersThree
GO
USE Northwind
GO
SELECT ContactName
FROM CustomersOne
UNION
SELECT ContactName
FROM CustomersTwo
UNION
SELECT ContactName
FROM CustomersThree
GO
USE Northwind
GO
SELECT ContactName
FROM CustomersOne
UNION ALL
(
SELECT ContactName
FROM CustomersTwo
UNION
SELECT ContactName
FROM CustomersThree
)
GO
-----------------------
|DARB_NUM |DZIMUMS |AMATS |ALGA |
|1 |sieviete |sekret[pic][pic]re |200 |
|2 |v+[pic]rietis |IT speci[pic][pic]lists |250 |
|3 |sieviete |sekret[pic][pic]re |210 |
|4 |v+[pic]rietis |IT speci[pic][pic]lists |260 |
5v+[pic]rieretāre2002vīrietisIT speciālists2503sievietesekretāre2104vīrietisIT speciālists260
|5 |vīrietis |analītiķis |200 |
|6 |sieviete |analītiķis |300 |
|7 |sieviete |analītiķis |350 |
| | | | |
|DZIMUMS |AMATS |Summa |
|sieviete |sekretāre |410 |
|vīrietis |IT speciālists |510 |
|vīrietis |analītiķis |200 |
|sieviete |analītiķis |650 |
|DZIMUMS |AMATS |Summa |
|sieviete |sekretāre |410 |
|sieviete |analītiķis |650 |
|sieviete |NULL |1060 |
|vīrietis |IT speciālists |510 |
|vīrietis |analītiķis |200 |
|vīrietis |NULL |710 |
|NULL |NULL |3540 |
|NULL |sekretāre |410 |
|NULL |analītiķis |850 |
|NULL |IT speciālists |510 |
|DZIMUMS |AMATS |Summa |
|sieviete |sekretāre |410 |
|sieviete |analītiķis |650 |
|sieviete |NULL |1060 |
|vīrietis |IT speciālists |510 |
|vīrietis |analītiķis |200 |
|vīrietis |NULL |710 |
|NULL |NULL |3540 |
|DZIMUMS |AMATS |Summa |Dz |Am |
|sieviete |sekretāre |410 |0 |0 |
|sieviete |analītiķis |650 |0 |0 |
|sieviete |NULL |1060 |0 |1 |
|vīrietis |IT speciālists |510 |0 |0 |
|vīrietis |analītiķis |200 |0 |0 |
|vīrietis |NULL |710 |0 |1 |
|NULL |NULL |3540 |1 |1 |
|UZV |ALGA |
|Koks |200 |
|Zars |150 |
|Celms |250 |
|Sakne |250 |
|Lapa |200 |
| |Sum |
| |1050 |
|UZV |ALGA |
|Koks |200 |
|Celms |250 |
| |sum |
| |450 |
|Zars |150 |
|Sakne |250 |
|Lapa |200 |
| |sum |
| |600 |
|UZV |DZIM |ALGA |FIRMA |
|Celms |sieviete |250 |AAA |
|Koks |sieviete |200 |BBB |
|Lapa |vīrietis |200 |AAA |
|Sakne |vīrietis |250 |BBB |
|Zars |vīrietis |150 |AAA |
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
Related searches
- ms access many to many relationship
- ms access relationships
- ms access vba listbox
- sql server data classification
- sql server data classification tool
- sql server data types
- azure sql server hyperscale
- ms access string functions vba
- sql server 2016 string functions
- ms access string function
- ms access connection string 2016
- ms access sql isnull function