Microsoft SQL Server 2000 Super Management Manual (14)

14. Retrieve data using T-SQL

SELECT statement

Use the T-SQL function

SELECT's other usage

Summary of this chapter

In this chapter, you will learn how to use Transcat-SQL (T-SQL) SELECT statements to retrieve information, which cover many selective clauses, search criteria, and functions used in SELECT statements. These elements help you find the real-needed information when you ask for queries.

SELECT statement

Although the SELECT statement is primarily used to retrieve specific information, it can also be used to assign values ​​to this domain variable or call function, which is introduced in the last

When you decide which information you want to return from the table, you can join any other options. These options include: When using an index, specify which information lines to include in the WHERE clause, specify whether the data is required to be sorted, specify whether it only needs to pass different information. For information on query optimization, see Chapter 35.

Let us speak from the different options in the SELECT statement and the search example of each clause. The database used in this chapter is Pubs and Northwind, which is automatically created when you install Microsoft SQL Server 2000. You can use SQL Server Enterprise Manager to view information sheets for Pubs and Northwind libraries to familiarize with these two databases.

SELECT statement is composed of several selective clauses. In most cases, a SELECT statement includes at least one SELECT clause and the from clause. These two clauses determine which data line or which information line needs to be retrieved, and from which information list is retrieved. For example, for the AUTHORS data table of the Pubs database, a simple Select statement is as follows:

SELECT AU_FNAME, AU_LNAME

From authors

If you use the OSQL command information column (parap with it in Chapter 13), don't forget to use the Go command to perform the statement. To use OSQL, the complete T-SQL syntax of SELECT statements is as follows:

USE PUBS

SELECT AU_FNAME, AU_LNAME

From authors

Go

Description

Since the keyword is not case sensitive, you can use it, but it is best to keep consistent, so that your syntax is easy to read. Therefore, keywords in this book use uppercase letters.

When you use SELECT statements (for example, using OSQL or SQL Server query Analyzer), the result is displayed in various data lines, while each of the data is indicated. (For the introduction of T-SQL, OSQL and Query Analyzer, please refer to Chapter 13)

SELECT clause

SELECT clause consists of selective quotes, and the selected list you request. Select List is a list containing an expression or data line to indicate which information rows need to be postdated in the Select clause. The following describes the selective quotes and the selected list.

Quoter

SELECT clause Use the following two quotes to control the data sheet:

DistINCT only passes the unique information column. If the selected list includes several data lines, the information column will be considered unique when the data value of at least one of the information lines is different. If there are two data columns, they must be the same in each of the values. Top N [Percent] The first n data column of the result collection. If Percent is specified, it will only pass the previous Number of data columns. When using percent, n must be a number between 0 and 100. If the query includes a ORDER BY clause (the ORDER BY clause will be described later in this chapter), the information column will be quenched in order, then pass back from the results of the rank N data columns or one percent of N data. The following T-SQL syntax shows the SELECT clause example, which is executed three times, each uses different quotes. Use the Distinct quotes when queries, the second use TOP 50 Percent quotes, the third use of TOP 5 quotes.

Select Distinct Au_fname, Au_lname

From authors

Go

SELECT TOP 50 Percent au_fname, au_lname

From authors

Go

SELECT TOP 5 au_fname, au_lname

From authors

Go

The first query is transmitted back to 23 data columns, and each material is unique. The second transmission of 12 data columns (approximately 50%, unconditional entry), the third pass back 5 data columns. Select the list As described above, the selected list is a list of arithmetic or data lines, which is used to specify which data in the SELECT clause needs to return. The operation can be a list of data line names, functions, or constants. Selecting a list can include several arithmetic or data line names, separated from each other. The above example uses the following selection list:

AU_FNAME, AU_LNAME

Asternship or multi-use symbols You can use an asterisk (*) or multi-use symbol to specify all the information and data lines that need to be sent back to the FROM clause and the view table. For example, you can use the following query:

SELECT *

From sales

Go

This chapter will describe the situation of not only one data sheet when SELECT * statement is listed. Identitycol and RowGuidcol Retrieve values ​​from the Identity Column in the Identity Column in the data sheet, simply use the IdentityCol operation in the selected list. Below is an example of querying the Northwind database, where the Identification line is defined in the Employess data sheet:

Use northwind

Go

Select IdentityCol

From Employees

Go

The result set will follow:

Employeeid

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

3

4

8

1

2

6

Seduce

5

9

(Impact 9 data columns)

Please note that the data line of the result set is in the tabular and the database of the data line with the Identity property, in this case, EMPLOYEEID. Similarly, you can use the General Unique Identification Code (GUID) data line with the ROWGUIDCOL arithmetic retrieval data column in the selected list, that is, the data line with the ROWGUIDCOL attribute. There must be a UNIQUEIDENTIFIER data type to have a ROWGUIDCOL attribute. Data Division Using the Data Division You can make the data line shown in the result set to your desirable look. Using the alias allows the information in the output line more clearly, assigns the tabular to the data line used in the function, or references these alias in the Order By clause. When you have the same name in multiple different data sheets, you may need to include the name of the data sheet in the output of the data line header in the output. Have an example of using an alias. Let's take a look at the LName Data in the Employee Table in the Pubs Database. If you do the following queries: USE PUBS

Go

SELECT LNAME

From Employee

Go

Will get the following results:

lname

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

Cruz

Roulet

Devon

...

O'Rourke

Ashworth

Latimer

(Impact 43 data columns)

To make the data line title LNAME in the display result, "Employee Last Name", to emphasize the substantive significance of LNAME, available as keywords:

SELECT LNAME AS "Employee Last Name"

From Employee

Go

The result of this command is:

Employee Last Name

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

Cruz

Roulet

Devon

...

O'Rourke

Ashworth

Latimer

(Impact 43 data columns)

You can also use other types of arithmetic and data firing names in the selected list, and as the reference data of the Order By clause. Suppose there is a function in the selected list, you can use the AS keyword to specify a data run name that describes this function. If the function does not use the data, there is no information line title at all. For example, the following statement specifies the output of the maximum of the Max function:

SELECT MAX (JOB_ID) AS "Maximum Job Id"

From Employee

Go

This alias is quoted because it consists of multiple words, and there is space. If the alias does not contain spaces, you can do not need to quotation. You can reference the data firing name specified in the SELECT clause as the quotes of the order by the order. This trick is very useful when the selected list contains a function that needs to be sorted. For example, the following command will retrieve the sales amount of each bookstore and sort the size of the output results. The alias assigned in the selected list will be applied to the Order By clause.

SELECT SUM (Qty) As Quantity_Of_Books, Stor_ID

From sales

GROUP BY STOR_ID

Order by quantity_of_books

Go

In this example, the alias does not contain spaces, so we don't need to add quotes for alias. If we don't specify an alias for the SUM (QTY) line of the query, the following example will output the same result, but the SUM data has no title:

SELECT SUM (Qty), Stor_ID from Sales

GROUP BY STOR_ID

Order by Sum (Qty)

Go

Remember, the data line name allows you to assign the title to the output data, but only in the name of the name; no matter what they do not affect the results of the query. From clause from the origin of the FROM clause, the name of the data sheet and the viewing table used to select the information. Each Select clause requires the use of from clauses unless there is no data line name inside the selected list, and there is only constant, variables, mathematical operations. You have seen some examples of using from clauses, but from clauses can also contain derived data sheets, Join and alias. Derived table derivative data sheet is a result set of another Select clause in the FROM clause. The result set of the SELECT clauses included is considered a data sheet, and the SELECT clause other than the information table can choose its information. The following query uses a derived data sheet to find the store name, this store provides at least one possible discount method.

USE PUBS

Go

Select s.stor_name

From Stores As S, (Select Stor_ID, Count (Distinct DiscountType

AS D_COUNT

From discounts

GROUP BY STOR_ID) AS D

Where s.stor_id = D.Stor_id and

D.d_count> = 1

Go

If this command is executed, you will see a piece of data is selected, indicating that there is only one store in the database - the only discount. Note that this query uses a quick-book method to represent the data table name (S represents the Store Data table; D represents the discounts data sheet). This speed diary is called a Table AliaS, and the in this chapter will discuss this.

Note It does not have a derivative data sheet in the WHERE clause. The SELECT statement in the WHERE clause is to be a search condition. The

Join Table is a result set after two more data sheet joint operations. The coupling method can be performed between the data sheets is: internal connection, complete external connection, left external connection, right external connection, and cross-linking. Each of the coupling is briefly described below. Inner Join is a preset coupled form; it specifies that only the information columns only in accordance with the ON condition table can be included in the result set, and the non-conforming the information column is excluded. To specify a join, you can use the Join keyword. Use the ON keyword to define the basic search criteria. The following query is coupled to Stores and Discounts two data sheets to show which stores provide discounts and discounts (preset to internal coupling, indicating that only information columns that match the ON search criteria are back).

Select S.Stor_ID, D.DiscountType

From Stores S Join Discounts D

ON S.Stor_id = D.Stor_ID

Go

The result set is as follows:

STOR_ID DiscountType

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

8042 Customer Discount

You can see that there is only one store to offer discounts, and there is only one discount. In the unique information column of the backup, it is in line with the STORES data sheet in the Store table, which is in line with the SOTR_ID obtained in the Discounts table, so this stor_id and its discounttype are later. Complete external joint fully external connection (FULL OUTER JOIN) is specified whether it is a matte column (a data listed in the ON search criteria) or the non-conforming data column (not satisfying the ON information) is included in the result set. For non-conformable data columns, NULL will be displayed in the non-conformable data line. In this example, NULL means that the store does not provide any discount so that it has a SOTR_ID value in the Stores table, but there is no value in the Discounts data table. NULL also represents any shops in this form in this form of discounts. The following query uses the same query as the previous internal connection, but this time we have to specify full external connection: select s.stor_id, d.discounttype

From Stores S Full Outer Join Discounts D

ON S.Stor_id = D.Stor_ID

Go

The result set is as follows:

STOR_ID DiscountType

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

Null Initial Customer

Null Volume discount

6380 NULL

7066 NULL

7067 NULL

7131 NULL

7896 NULL

8042 Customer Discount

Results The set shows that only one data column is fully compliant. Other information columns will have a null value in a column. Left external connection Left Outer Join (LEFT OUTER JOIN) Removes all data columns of the matched data columns and designated tables on the left of the Join key. Use the same query as described above, here we specify the left external connection, as shown below:

Select S.Stor_ID, D.DiscountType

From Stores S Left Outer Join Discounts D

ON S.Stor_id = D.Stor_ID

Go

The result set is as follows:

STOR_ID DiscountType

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

6380 NULL

7066 NULL

7067 NULL

7131 NULL

7896 NULL

8042 Customer Discount

Results The concentration contains a list of ON conditions and other information columns in the Stores Data table, which are columns in the discounts data sheet, which is not in line with STOR_ID (these data columns of the discounttype column NULL). Right Outer Join is opposite to the left external connection, which is transmitted back to all data columns in the information sheet specified in the right side of the Join key. The same query will be used below to explain the right external connection:

Select S.Stor_ID, D.DiscountType

From Stores S Right Outer Join Discounts D

ON S.Stor_id = D.Stor_ID

Go

The result set is as follows:

STOR_ID DiscountType

------------------------- Null Initial Customer

Null Volume discount

8042 Customer Discount

The result set shows the ON conditional information columns and other information columns in the Discounts Data Sheet, which are columns that there is no STORES-based Stor_ID (NULL). Cross Join is the cross product of the two data sheets when the crosses join is not specified by the WHERE clause. If there is a WHERE clause, the crosslink is similar to the internal connection. When there is no WHERE clause, all columns and information columns will be backatically followed by these two data sheets: Each of the first data sheets is similar to each of the second data sheets. So the number of results set is the number of data columns multiplied by the second data sheet in the first data sheet. In order to understand cross-link, a few new examples will be given below. Let us take a look at the cross-linking of the WHERE clause, then raise three examples of cross-linking associated with the WHERE clause. The following query is a simple example. Perform these three queries and pay attention to the number of data columns output for each query.

SELECT *

From stores

Go

SELECT *

From sales

Go

SELECT *

From Stores Cross Join Sales

Go

Note If the FROM clause contains two data sheets, its effect is equivalent to a cross join, for example:

SELECT *

From stores, sales

Go

To avoid a lot of messy information (if the part we need is far more), you need to add a WHERE clause to narrow the range of queries, as described below:

SELECT *

From sales cross Join Stores

Where sales.stor_id = store.stor_id

Go

This statement is only transmitted back to the information column that meets the conditions in the WHERE clause, which reduces the results to only 21 data columns. The WHERE clause makes the cross-linking with the inner coupling effect (ie, only the information column that satisfies the search criteria is back). This query will return to the data sheet in the Sales tab and the stored STORES data sheet with the same STOR_ID, which will be together. The non-conforming information will not be returned. In order to further reduce the result set, you can add the data sheet name in front of the asterisk (*), specifying all the data columns and data lines that will be selected, as shown in the example below. You can also insert a point in selecting a list of data sheets through the data table name and data line name.

Select Sales. *, Stores.city

From sales cross Join Stores

Where sales.stor_id = store.stor_id

Go

This query passed back to all the information in the Sales data sheet and attached to the Stores Table Information Column with the same stor_id's city data line. In fact, the result set contains the Sales tab that complies with SALES-ID data columns and the city where the attached store is located. The following is the same query that does not contain an asterisk (*), only the Sales table is selected.

SELECT SALES.Stor_id, Stores.city

From sales cross Join Stores

Where sales.stor_id = store.stor_idgo

Data Sheet Alias ​​We have seen several examples of using information table alias. The AS keyword is selective (the result of from TableName as Alias ​​is the same as from TableName Alias). Let's take another look at the query of the right external connection>, this query uses an alias:

Select S.Stor_ID, D.DiscountType

From Stores S Right Outer Join Discounts D

ON S.Stor_id = D.Stor_ID

Go

These two data sheets contain a STOR_ID data line. To distinguish from the Stor_ID data line in which the data sheet is referenced in the query, you must provide a data sheet name or insert a point after the information form name (.) Plus the data line name. In this example, S represents the Stores data sheet, and D represents the discounts data sheet. When specifying a certain column, S or D is added before the data line name indicates which information table belongs to this data. The same query containing the AS key is as follows:

Select S.Stor_ID, D.DiscountType

From Stores As S Right Outer Join Discounts As D

ON S.Stor_id = D.Stor_ID

Go

The INTO clause is now entering the first true selective clause of SELECT statement: INTO clause. Use Select

INTO syntax to retrieve data from one or more data tables and output the result data column to a new information list. When executing Select ... INTO statements, the new information table will automatically generate and follow the data line definitions in the selected list. Each of the new data sheets has the same data type with the source data line, and the data line name in the selected list is the same. Users must have Creat Table permissions in the target database to perform SELECT ... INTO. How to set the permissions in Chapter 30. You can use SELECT ... INTO to store the results in a temporary or permanent data sheet. For a national temporary storage table (only the current connection or user can see), you must add $ ## before the information indicates. For a national temporary storage table (see any user), you must add two ## (##) before the data sheet. When all users who are using the same temporary data sheet are disconnected from the SQL server, the temporary information table will be automatically deleted. To select a permanent data sheet, you don't have to add the preamble for the data sheet name, but the Select INTO / BULK COPY option for the target database must be selected. To select this option for the Pubs Database, you can do the following SQL query:

SP_DBOPTION PUBS, "Select Into / Bulkcopy", TRUE

Go

You can also use SQL Server Enterprise Manager to select this option as follows:

Click the PUBS database in the Enterprise Manager window and press the right button to select the content after the fast display function, which can display the properties of the Pubs database, as shown in Figure 14-1 (this picture has appeared in Chapter 9, at that time A database is established and its file growth option is specified). Press the option tab, as shown in Figure 14-2, and select a large number of logins in the drop-down menu of the model. Other options remain originally set. Press to determine. The following query uses SELECT ... INTO to create a permanent data table named EMP_INFO, which contains all the names of all employees, as well as their duty instructions (in the Pubs database):

Select Employee.fname, Employee.lname, Jobs.job_Desc

INTO EMP_INFO

From Employee, Jobs

WHERE EMPLOYE.JOB_ID = jobs.job_id

Go

Figure 14-1 Database properties window "General" tab

Figure 14-2 Database Property Window "Options" tab

The EMP_INFO data table contains three data -FNAME, LNAME, and JOB_DESC, which is the same as the data sheet defined by the original data sheet (Employee and Jobs). To make the new information table becomes a domain temporary data sheet, you must add a # symbol before the data table name, such as #emp_info; to be a ## symbol, such as ## EMP_INFO. WHERE clause and search criteria WHERE clause defines the information column returned by the query according to the search criteria. In this section, we will say a lot of monthly operations available in search criteria.

Note The search criteria is not only available in the WHERE clause in the SELECT statement, or it can be used for Update and the delete statement. (Update and Delete statement will be discussed in 20 chapters)

Let us first review some terms. Search conditions can include unlimited number of words through incorporation logical operators and NOT. The predicate refers to an operational formula that is transmitted back to true, false, and unknown values. An EXPRESSION can be a data line name, a constant, a pure quantity function (a function of a value), variable, pure query (subquery back to a data line) or through operator combines these elements The overall formed. In this section, words or arithmeters are expressed in an arithmetic species. The equation and inequality operator of the comparative operation operator are listed in Table 14-1.

Table 14-1 Comparative Operation Operators

Conditions of Operators = Check if the two arithmeters are equal <> check whether the two arithmeters do not wait! = Check if the two arithmeters are inequality (with <>)> Whether the previous operation is greater than the latter arithmetic > = Whether the previous operation is greater than or equal to the latter arithmetic!> Check whether the previous operation is not more than the latter arithmetic model

A simple WHERE clause can be used to compare two arithmeters equals the operator (=). For example, the following SELECT statement can be used to verify the value of the data type of the data per data column LNAME data line. If it is LATIMER, it is returned to True (the data column that is back to TRUE will be included in the result set).

SELECT *

From Employewhere Lname = "LATIMER"

Go

The query of this example will be transferred back to a list of columns. Latimer must add quotation marks because it is a string.

Note SQL Server preset accepting single quotes (') and dual quotation marks ("), such as' latimer' or" latimer ". This book uses only double quotes to avoid confusion. You can use the set quoted_identifier option to keep keywords The name of the object and only use single quotes. Set this option to ON (OFF is a preset value).

The following query is not equal to the operator (<>), the information behavior integer data type is another JOB_ID.

SELECT JOB_DESC

From jobs

Where job_id <> 1

Go

This query will be sent back in the information column in the JOB_ID not equal to 1 in the JOBS data table. In this example, there are 13 data columns to be returned. If there is a NULL value in a certain data column, it does not equal it to another, so this information column will also be returned. Logical operator logic operator and OR test two arithmetics and transmits a true, false, or unknown's cloth value based on the results. The NOT logic operator will pass the opposite of the operation. The data in Figure 14-3 lists each of the possible values ​​returned by the AND, OR and NOT operators. In the AND OR table, the data line on the left is the first arithmetic value, the uppermost data column is the second arithmetic value, and the strip of the two is the Burf value of the result. The NOT form is more direct. UnkNown is the result of the NULL value in the arithmetic.

Figure 14-3 Operation results of and, or NOT

The following query is an example of using the AND logic operator in the WHERE clause:

SELECT JOB_DESC, Min_LVL, MAX_LVL

From jobs

WHERE min_lvl> = 100 and

MAX_LVL <= 225

Go

As shown in Figure 14-3, let the AND operation back to TRUE, two conditions must be TRUE. In this query, 4 data columns are returned. In the next query, an OR operation will detect whether the publisher is located in Washington D.c. or is located in Massha. If there is one value true in two inspections, this information column will be back.

Select P.Pub_name, P.State, T.Title

From Publishers P, Titles T

Where p.State = "DC" OR

p.State = "ma" and

T.PUB_ID = P.PUB_ID

Go

This query passed the 23 data column. The NOT operation is simply transmitted back to the opposite of the fabric operational calculation. For example, to check all the author's budget book name, you can use the NOT operator in the following method:

SELECT T.TITLE, R. ROYALTY

From titles t, ROYSCHED R

Where t.title_id = r.Title_ID and NOT

R. ROYALTY <20

Go

In addition to these operators, there are also multiple T-SQL keywords available for search criteria. This section explains the most commonly used keywords, and will also give examples. The Like Like keyword specifies the mode comparison in the search criteria. Pattern matching is a comparison detection between a compared arithmetic and the model specified in the search, the syntax is as follows: Like

If the comparative mode is compliant, the Burlin value of TRUE is passed. Otherwise, return false. Comparison is required to be a string data type. If not, SQL Server will automatically convert it as a string data type. The mode is a real string operation. String operation is defined as a string consisting of a character or a multi-purpose character. The multimetric character is a characteristic of the special meaning in the string operation. Table 14-2 The data lists the multi-purpose characters available in the mode:

Table 14-2 T-SQL million

Multi-Direction Description% percentage symbol; compares to zero or several vollaves _ bottom line; with any single-word alignment [] range of universal character, single-word alignment in the specified range or collection For example, [MP] or [MnOP] represents any of M, N, O, and P. [^] Does not in the range of 10,000 yuan; and any of the characters that do not include any of the forts or collections, such as [^ MP] or [^ mnop], except for other M, N, O, and P Ending a word.

To learn more about the LIKE keyword and the multi-purpose character, please see a few examples. To find the author's last name starting with S as the Authors Data Sheet, you can use the following query:

SELECT AU_LNAME

From authors

WHERE AU_LNAME LIKE "S%"

Go

The result will be:

AU_LNAME

-----

Smith

Straight

Stringer

In this query, S% indicates that the return is started with S, and then the last name of any character.

Description This example assumes that you are using the preset in dictionary order, and it is not case sensitive. If you specify other sorting methods, your results may be different, but the principle of using the LIKE key is the same.

To search for the ID author information starting with 724, if you know each ID, you can use the social security number (3 numbers-2 numbers -4 numbers), you can use the bottom line (_) universal character, as follows Down:

SELECT *

From authors

Where au_id like "724-_ _ _ _ _ _"

Go

The result set will contain two data columns, and the AU_ID value is 724-08-9931 and 724-80-9391. Let us now look at an example of using [] universal character. To search for the authors of any letters of any letters between A to M, you can combine [] and% universal character:

SELECT AU_LNAME

From authors

Where au_lname like "[a-m]%"

Go

The result set will contain 14 data columns with the name starting with A-M. (If you use case sensitive sort, you get 13 data columns.) If you use [^] multi-purpose character to replace [] Wanli-by-unit, you will get a similar query, you will get the surname, which except A to M is starting. As follows:

SELECT AU_LNAME

From authors

Where au_lname like "[^ a-m]%"

Go

This query is transmitted to the 9 data column. If you use case-sensitive sorting, you can get all the names that meet the conditions but not case-sensitive, you can use a query for the first letter as lowercorn or uppercase, as shown in: SELECT AU_LNAME

From authors

WHERE AU_LNAME LIKE "[A-M]%" OR

AU_LNAME LIKE "[A-M]%"

Go

The result set contains Del Castillo, and if only the case-sensitive query method is used, it will not be able to do this. The LIKE keyword can also be placed after the Not Operator. NOT LIKE passed the information column that does not meet the search criteria. For example, to find a book name not starting with THE, you can use NOT LIKE, as shown in the following query:

SELECT TITLE

From titles

WHERE TITLE NOT LIKE "The%"

Go

This query will pass 15 data columns. You can use the like keyword with creative. But carefully test your query to determine that the transferred information is the part you really need. If it is missing the NOT or ^ character that should have, the result is just the opposite of the value you expect. I miss the result of the original% of the furniture, you must also pay attention to the use of the front and rear spaces. The Escape Escape keyword allows you to make a moderate alignment for a multimetric character itself, such as ^,%, [, and _. After the ESCAPE keyword, you will then specify the character you want to avoid, and these characters in the string operation must be compared by word meaning. For example, in the Titles data list, you can find all the information columns in the bottom line in the Title data line, you can use the following query:

SELECT TITLE

From titles

Where title Like "% E_%" Escape "E"

Go

This query will not be transferred back, because there is no book name in the database contains the lower bottom line. BetWeen BetWeen keywords are usually used with ANDD to specify the range for search criteria. The syntax is as follows:

Between and

When the Test_ Expression operation is greater than or equal to the Begin_ Expression operation and less than or equal to the END_ Expression operation, the results of the search cruculin is true; otherwise the result is false. The following query uses the BetWeen keyword to find the title between 5 and $ 25:

SELECT price, TITLE

From titles

WHERE price Between 5.00 and 25.00

Go

This query will pass 14 data columns. You can also use NOT with BetWeen to find information that is not within the specified range. For example, looking for a title of 20 to 30 USD (ie, the price is less than $ 20 or more than $ 30), you can use the following query:

SELECT price, TITLE

From titles

Where Price Not Between 20.00 and 30.00

Go

When you use the BetWeen keyword, the Test_Expression operation must have the same data type as the Begin_Expression arithmetic calculation and the End_Expression operation. In the just example, the PRICE data line is divided into Money, so the Begin_ Expression arithmetic and End_ Expression arithmetic model must be a number that can be compared to or implicitted (Implicit Conversion) for Money data type . You cannot use the price in the Test_ Expression operation to use the BEGIN_EXPRESSION arithmetic use string or CHAR data type; otherwise SQL Server will return an error message. Note If the implicit conversion is feasible, SQL Server will automatically convert the data type to be used when needed. Implied conversion automatically converts the data type to another to be compatible. Compare can be compared after conversion. For example, when a smallint data type is compared with an int calm information, SQL Server converts Smallint to int data type before comparing. If you do not provide an implicit conversion, you can use the CAST or Convert function to specify the conversion data line. Regarding which of SQL Server is implied, it can be implied, which data is displayed, which can be explicitly converted, and "Cast" can be indexed in SQL Server ", and select Cast and Convert in the topic dialogue in the found topic.

The last example related to the BetWeen keyword is to use a string in search criteria. To find out the author's surname from Bennet to McBadden, you can use the following query:

SELECT AU_LNAME

From authors

WHERE AU_LNAME BETWEEN "Bennet" and "mcbadden"

Go

The range of BetWeen will contain the starting value and the end value, so the Bennet and McBadden existing in the data sheet are also within the query results. Is Null Is Null Keywords are used in search criteria to have NULL values ​​in the specified data line. For example, in the Titles Data Sheet Looking for Notes Data, there is no information (ie NOTES value null), you can use the following query:

SELECT TITLE, NOTES

From titles

Where notes is null

Go

The result will be:

Title Notes

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

The Psychology of Computer Cooling NULL

As you can see, the NULL value of the Notes data line is displayed as NULL in the result set. NULL is not the actual value in the data line - it only indicates that there is a NULL value in the data line. To find the book name of Notes data, the NOTES value is not NULL's book name), using is not null, as shown below:

SELECT TITLE, NOTES

From titles

Where notes is not null

Go

The result set shows that there is at least one of the NOTES data lines in its Notes data line, so there is no NULL value in the Notes data line. The in kind key is used in search criteria to determine if a given test operator is in conforming to any value in the subquery or value list. If you find a match, True will be returned. The NOT IN is followed back to the opposite result of IN, so if the test operator is not found in the subquery numerical list, TRUE will be returned. The syntax is as follows: in ()

or

in ()

Subquery is a result of only a SELECT statement that is only back to a data line. The subquery must be included in the parentheses. The same is true for the value, which is included in the parentheses and separated in a comma. Data from the subquery or value list, its data type must be the same as the Test_Expression operation. If necessary, SQL Server performs implied conversion. You can use the IN keyword and value list to find the JOB_ID number of 3 specific work descriptions, queries as follows:

SELECT JOB_ID

From jobs

WHERE JOB_DESC in ("Operations Manager",

"Marketing Manager",

"Designer")

Go

The value of this query is: ("Operations Manager", "Marketing Manager", "Designer"), for the condition of the Job ID, must be the value in the JOB_DESC data line in the data column is one of the three one. Compared to the two OR operand to complete the query, the in keyword allows your query to read, as shown below:

SELECT JOB_ID

From jobs

Where job_desc = "Operations Manager" OR

Job_Desc = "Marketing Manager" OR

Job_Desc = "Designer"

Go

The following query uses the in keyword twice in a statement - a targeting subquery, and the value in the subquery is represented:

SELECT FNAME, LNAME --outer Query

From Employee

WHERE JOB_ID IN (SELECT JOB_ID - Inner Query, OR Subquery

From jobs

WHERE JOB_DESC in ("Operations Manager",

"Marketing Manager",

"Designer"))

Go

The result set of subqueries will be found - in this case, it is a collection of JOB_ID values. The collection of JOB_ID values ​​generated by this subquery will not be transferred to the screen, and the outer query will use it as an arithmetic to use in its own in-search criteria. The final result set will contain all the professional titles of Operations Manager, Marketing Manager or Designer's name. The result set is as follows:

FName Lname

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

PEDRO AFONSO

Lesley brown

Palle Ibsenkarin Josephs

Maria larsson

Elizabeth Lincoln

Patricia Mckenna

Roland Mendel

Helvetius Nagy

Miguel Paolino

Daniel Tonini

(Impact 11 data columns)

IN can also be used with NOT operators. For example, to pass all publisher names in California, Texas or Illinois, do the following queries:

Select pub_name

From publishers

WHERE State Not in ("CA",

"TX",

"IL")

Go

The query will return the five data columns of one of the three states in the list of STATE data line values. If your database option ANSI NULLS is set to ON, the result will only pass the three data columns. This is because there is a state value of two data columns in the five-data column in the original result set, and NULL is not selected when the ANSI NULLS is set to ON. To determine why your ANSI NULLS settings in the PUBS database can do the following system pre-depreciation:

SP_DBOPTION "PUBS", "ANSI NULLS"

Go

If the ANSI NULLS is set to OFF, it can be changed to ON using the following statement:

SP_DBOPTION "Pubs", "ANSI NULLS", TRUE

Go

To change ON to OFF, replace TRUE in False. The exists exisss keyword is used to detect the existence of the data sheet in the subquery after it. The syntax is as follows:

EXISTS ()

If there is any information column to meet this one of the queries, TRUE will be returned. To choose the author name that has published a book, you can use the following query:

SELECT AU_FNAME, AU_LNAME

From authors

WHERE EXISTS (SELECT AU_ID

From titleauthor

WHERE TITLEAUTHOR.AU_ID = authors.au_id)

Go

The author name that is stored in the Authors Data Sheet, but the author's name that is not published (information listed in Titleauthor) will not be selected. If there is no information column in the subquery being selected, the result set will be given to the outer query. (Zero-Information Columns Selected) Contains and Fretext Contains and FreeText Keywords are used to conduct a full-text search in the character-based data-based data line. They provide greater flexibility than the Like keyword. For example, the Contains keyword allows you to search for words that are not fully compliant with the specified font or the chronic, but a fuzzy alignment). FreeText allows you to search for several texts with a specified string part or all match (or fuzzy comparison). These words do not need to fully meet the specified search strings, nor does it require the same order as the text in the string. These two keywords have multiple purposes and provide a lot of options for full-text search, but these topics have exceeded the discussion scope of this chapter.

Related Information About Contains and FreeText Keywords, you can index "Contains" and "FreEtext" on SQL Server "online book" GROUP BY clause Group BY clause After the WHERE clause, the data column in the indicator result is set to group in accordance with the specified group data line. If the SELECT clause uses a summary function, each group calculates a total value and is displayed in the output. (AGGREGATE FUNCTION) Performs a calculation and return value; these functions will be described in detail later in this chapter.)

Note Each of the data lines in the selection list - In addition to the data line used in the summary function - must be specified as a group data in the Group By clause; otherwise SQL Server will return an error message. If this rule is not followed (the specified Group By data line must group each of the data in the list), the output will not be displayed in a reasonable way.

GROUP BY is most useful when the SELECT clause contains a summary function. Let's take a look at the SELECT statement of each book sales with Group By clause:

SELECT TITLE_ID, SUM (QTY)

From sales

GROUP BY TITLE_ID

Go

The result set is as follows:

Title_id

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

BU1032 15

BU1111 25

Bu2075 35

BU7832 15

MC2222 10

MC3021 40

PC1035 30

PC8888 50

PS1372 20

PS2091 108

PS2106 25

PS3333 15

PS7777 25

TC3218 40

TC4203 20

TC7777 20

(Impact 16 data columns)

This query does not have a WHERE clause - you don't need it. The result set shows a Title_ID data line and a summary information line without a title. For each different title_id, the total sales volume of each book is displayed in the summary data line. For example, Title_ID is built twice in the Sales data sheet - once in the QTY Data Line, 5 this, the second time sold in another order. The summary function of the SUM summary of these two sales depends on the total amount and is displayed in the summary data line. To add a title for the summary information, you can use the AS keyword:

SELECT TITLE_ID, SUM (Qty) AS "Total Sales"

From sales

GROUP BY TITLE_ID

Go

The summary of the summary information of the results now is Total Sales:

Title_id Total Sales

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

BU1032 15

BU1111 25

Bu2075 35

BU7832 15

MC2222 10

MC3021 40

PC1035 30

PC8888 50

PS1372 20

PS2091 108

PS2106 25

PS3333 15

PS7777 25

TC3218 40

TC4203 20

TC7777 20

(Impact 16 data columns)

You can include more than one data line to make group nests in the group BY clause. Nesting Groups means that the result set will work in groups in the order specified by each group data. For example, to find the average price of books that have been planted according to the type and publishing commercial group, you can perform the following query:

SELECT TYPE, PUB_ID, AVG (Price) AS "Average Price"

From titles

Group by Type, Pub_ID

Go

The result set is as follows:

TYPE PUB_ID AVERAGE PRICE

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

Business 0736 2.99

Psychology 0736 11.48

Undecided 0877 NULL

MOD_COOK 0877 11.49

Psychology 0877 21.59

TRAD_COOK 0877 15.96

Business 1389 17.31

Popular_comp 1389 21.48

(Impact 8 data columns)

Note that the books of psychology and commercial types have appeared because they are grouped in different publishers. The NULL value of the undecided type indicates that there is no price in the data sheet, so there is no average value. The Group By clause provides a selective keyword all that specifies that the result set will contain all groups, even if they may not meet the search criteria. Groups that do not have information columns that match the search criteria will have null in the summary data to facilitate discerning. For example, to display average price of a 2% book (and other conditions that do not match other conditions, the summary information behavior null), and the book is first in the type of publisher ID, and perform the following query:

SELECT TYPE, PUB_ID, AVG (Price) AS "Average Price"

From titles

Where royalty = 12

GROUP BY ALL TYPE, PUB_ID

Go

The result set is as follows:

TYPE PUB_ID AVERAGE PRICE

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

Business 0736 null

Psychology 0736 10.95

Undecided 0877 NULL

MOD_COOK 0877 19.99

Psychology 0877 NULL

TRAD_COOK 0877 Nullbusiness 1389 NULL

Popular_comp 1389 NULL

(Impact 8 data columns)

All types of books are displayed in the output, and the NULL value is displayed without 12% of books. If we don't use the keyword all, the result set will only include a 2% book type:

TYPE PUB_ID AVERAGE PRICE

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

Psychology 0736 10.95

MOD_COOK 0877 19.99

(Impact 2 data columns)

Group By clause often uses the Having clause, we will discuss below. Having clause HAVING is used to specify a search for a group or a summary function. Having is usually used after the Group By clause, the main purpose is to specify the search criteria to be detected for a result of the group. If the search condition is before using grouping, the WHERE clause is more efficient than Having. Having tips have reduced the number of data that must be groupized. If there is no Group By clause, Having can only be used with the summary function of the selected list. In this case, the HAVING clause is the same as the WHERE clause. If haven is not used in these cases, SQL Server will return to error information. The syntax of the Having clause is as follows:

Having

The search_condition here has the same meaning as the search criteria described in the

Note You can use a summary function in the SELECT and HAVING clauses, but in the WHERE clause.

The following query uses a Having clause to select average price of more than $ 15 publishers:

SELECT TYPE, PUB_ID, AVG (Price) AS "Average Price"

From titles

Group by Type, Pub_ID

Having AVG (Price)> 15.00

Go

The result set is as follows:

TYPE PUB_ID AVERAGE PRICE

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

Psychology 0877 21.59

TRAD_COOK 0877 15.96

Business 1389 17.31

Popular_comp 1389 21.48

(Impact 4 data columns)

You can also use the logical operator with the HAVING clause. Plus the query of the And operator as follows:

SELECT TYPE, PUB_ID, AVG (Price) AS "Average Price"

From titles

Group by Type, Pub_ID

Having AVG (Price)> = 15.00 and avg (price) <= 20.00

Go

The result set is as follows:

TYPE PUB_ID AVERAGE PRICE

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

TRAD_COOK 0877 15.96

Business 1389 17.31

(Impact 2 data columns)

You can also use the BetWeen clause instead of and, the result is the same:

SELECT TYPE, PUB_ID, AVG (Price) AS "Average Price"

From titles

Group by Type, Pub_ID

Having AVG (Price) Between 15.00 and 20.00

Go

If you do not have a Group By clause when using Having, you must include a summary function in the selected list and the Having clause. For example, you want to select a variety of books that are greater than $ 20, you can do the following queries:

Select SUM (Price)

From titles

WHERE TYPE = "MOD_COOK"

Having SUM (Price)> 20

Go

If the arithmetic SUM (Price)> 20 is placed in the WHERE clause, SQL Server will return to error information (not allowed to use a summary function in the WHERE clause).

Description Remember, using the haVing clause is to add a search condition to the result set obtained by the GROUP BY clause, or to detect a summary function. Other situations should be used to specify search criters with a WHERE clause, which will be more efficient and avoiding errors in SQL Server.

Order By Sentence Order By Sentence to Specify the sorting method of the data column of the result set. You can use the ASC or DESC option to specify whether the boom (from a small to large) or the power (from large to small). When there is no selected, it is preset to the power. You can specify a lot of information in the Order By clause, and the result will be sorted according to the first data line. If the first data has a repetition value, it is sorted according to the second data line, and so on. As can be seen in the following, this sort will be more meaningful if the Order By is used together with Group By. First let's take a look at the ORDER BY clause, use a document, according to the author's surname to raise the pharmacy:

SELECT AU_LNAME, AU_FNAME

From authors

Order by au_lname ASC

Go

The result set will be arranged in the alphabetical order of the surname. Remember If you set the case where the sort order is set when you install SQL Server, you will have an impact on, for example, the last name of Del Castillo. If the result of the result is not only one, just add the name of these data, and use a comma-zone in the Order By clause. The following query selects the name of Job_ID and the employee and sorted them first, according to the name of the name:

Select Job_ID, Lname, Fname

From Employee

Order by job_ifd, lname, fname

Go

The result set is as follows:

Job_id Lname FName

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

2 Cramer Philip

3 Devon Ann4 Chang Francisco

5 Henriot Paul

5 hernadez carlos

5 labrune janine

5 Lebihan Laurence

5 Muller Rita

5 OTTLIEB SVEN

5 Pontes Maria

6 Ashworth Victoria

6 KartTunen Matti

6 Roel Diego

6 Roulet Annette

7 Brown Lesley

7 Ibsen Palle

7 larsson Maria

7 Nagy Helvetius

...

13 Accenti Paolo

13 O'Rourke Timothy

13 SCHMITT Carine

14 Afonso Pedro

14 Josephs Karin

14 lincoln elizabeth

(Impact 43 data columns)

The results of this query can not be sorted according to the name, because there is no two last names in the same job_id. Let us now look at how to use the ORDER BY 字 和 and the group BY clause and the summary function:

SELECT TYPE, PUB_ID, AVG (Price) AS "Average Price"

From titles

Group by Type, Pub_ID

ORDER by Type

Go

The result set is as follows:

TYPE PUB_ID AVERAGE PRICE

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

Undecided 0877 NULL

Business 0736 2.99

Business 1389 17.31

MOD_COOK 0877 11.49

Popular_comp 1389 21.48

Psychology 0736 11.48

Psychology 0877 21.59

TRAD_COOK 0877 15.96

(Impact 8 data columns)

The results of the query will be arranged in alphabetical order (liter) of books. And please note that in this query, Type and Pub_ID must be included in the Group By clause because they are not part of the summary function. If there is no PUB_ID data line in the Group By clause, SQL Server will display an error message similar to Figure 14-4. In the Order By clause, you cannot use a summary function or subquery, but if you give a summary function in the SELECT clause, you can use it in the Order By clause, as shown below: SELECT TYPE, PUB_ID, AVG (Price) AS "average price"

From titles

Group by Type, Pub_ID

Order by "average price"

Go

The result set is as follows:

TYPE PUB_ID AVERAGE PRICE

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

Undecided 0877 NULL

Business 0736 2.99

Psychology 0736 11.48

MOD_COOK 0877 11.49

Psychology 0877 21.59

TRAD_COOK 0877 15.96

Business 1389 17.31

Popular_comp 1389 21.48

(Impact 8 data columns)

Figure 14-4 Error message displayed when PUB_ID does not contain pub_id in the group BY sentence

Now, the result is sorted by average price. NULL values ​​are considered to be the lowest, so it is on top.

Note The actual result of the ORDER BY clause retrieval will depend on the sort order set by SQL Server.

UNION operator Union is considered an operator instead of a clause. It is used to merge more than two query results to a result set. You must follow the following two rules when using Union: All queries have the same number of data. The data line corresponding to each query must be compatible with each other. In the SELECT clause, the data line combined with UNITION will correspond to each other: the first data of the first SELECT clause corresponds to the first data of the SELECT clause, and the second data line is behind SELECT. The second data line corresponds to this, in this type. As such, each Select clause combined with Union has the same number of lines, and one by one corresponds. In addition, the corresponding data must have a compatible data type, that is, the two corresponding data rows do not have the same data type, that is, SQL Server can implicit one of them. The following example uses Union combined with two SELECT results set, which is used to retrieve the City and State data in the Publisher and Stores data sheets:

Select City, State

From publishers

Union Select City, State

From stores

Go

The result set is as follows:

City State

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

Fremont Calos Gatos CA

Portland OR

Remulade WA

Seattle WA

Tustin CA

Chicago IL

Dallas TX

Munchen Null

Boston MA

New York Ny

Paris null

Berkeley CA

Washington DC

(Impact 14 data columns)

Explain that the output order of this query may differ from the SQL Server sequence order you use.

In the Publisher and Stores data sheets, CITY and State have the same data type (CHAR), which does not require conversion data. The data line head title for UNION results set is taken from the first SELECT statement. If you want to build an alias for the title, put it in the first SELECT statement, as shown below:

Select City As "All Cities", State As "All States"

From publishers

Union Select City, State

From stores

Go

The result set is as follows:

All Cities All States

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

Fremont CA

Los Gatos CA

Portland OR

Remulade WA

Seattle WA

Tustin CA

Chicago IL

Dallas TX

Munchen Null

Boston MA

New York Ny

Paris null

Berkeley CA

Washington DC

(Impact 14 data columns)

You don't need to use the same data line in all two Select clauses of Union. For example, you can choose the city and state data line in the Stores data sheet, select the CITY and Country data lines in the Publishers table, as shown below:

Select City, Country

From publishers

Union Select City, State

From stores

Go

The result set is as follows:

City country

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

Fremont CA

Los Gatos CA

Portland OR

Remulade WA

Seattle WA

Tustin CA

New York USA

PARIS FRANCE

Boston USA

Munchen Germanywashington USA

Chicago USA

Berkeley USA

Dallas USA

(Impact 14 data columns)

Note In this result, the top six columns come from queries to the Store task, and eight columns are queries for the Publishers sheet. The status of the State Data is a CHAR; Country Data Way's data type is VARCHAR. Because the data type must be compatible, SQL Server will perform implicit data type, so that the two data lines become VARCHAR type. The data is headed in CITY and Country, which is provided by the data line in the first SELECT clause. But in the results, the country information line is better for State or Country. There is a selective keyword that can be used with Union: all. If all the ALL is specified, all the same information columns will appear in the result set, in other words, all the data columns are included. If ALL is not specified, the preset value is to remove repeated columns in the result set. You can only use ORDER BY in the last Union statement, and cannot be used in each Select statement. This restriction guarantees that the final result set will only sort all the results once. On the other hand, GROUP BY and HAVIN can only be used in individual statements, they only affect their respective result sets but cannot affect the final result set. The following UNION combines two results that each containing a SELECT statement that has a Group By clause.

SELECT TYPE, Count (Title) AS "Number of Titles"

From titles

GROUP BY TYPE

Union Select Pub_Name, Count (Titles.title)

From Publishers, Titles

Where publishers.pub_id = titles.pub_id

GROUP BY PUB_NAME

Go

The result set is as follows:

TYPE NUMBER OF TITLES

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

Algodata Infosystems 6

Binnet & Hardley 7

New Moon Books 5

Psychology 5

MOD_COOK 2

TRAD_COOK 3

Popular_comp 3

Undecided 1

Business 4

(Impact 9 data columns)

The result set shows the number of items (the top three columns) of each publisher actual publishing book, and display the number of items for each category. Note Use Group by in each of each query. Union can also be used in more than two SELECT statements. When you create UNION, pay attention to confirm that all the data lines and data types of these queries are correct. Related information Please refer to the related files of all available T-SQL keywords and parameters in SQL Server "Online Books".

Using the T-SQL function Now you have been quite familiar with the basic clauses of the SELECT statement (more in Chapter 20), let's take a look at the T-SQL function in the SELECT clause, so you will There is a greater elasticity when establishing a query. These functions have the following categories: configuration, data indicators (CURSOR), date and time, security, interrupt data, system, system statistics, text and image, mathematics, data column, string, and summary functions. These functions can perform some operations of calculation, conversion, or other, or back a certain information. There are a lot of functions you can use, but we only discuss common summary functions in this section.

Related Information To get information on these types and functions that are not listed, you can search for "functions" on the index page of the book online.

As mentioned earlier, the summary function is calculated for a group of values ​​and transmits a single value. They can be specified in the selected list, but most of the cases are used to include Group By clauses in the statement. Some of the aforementioned query examples use a summary function AVG and Count. Information Table 14-3 lists the available summary functions.

Table 14-3 Summary function

Function Description AVG Reploys In addition to the average of values ​​other than NULL values. Count the number of items in the arithmetic calculator (same as the number of columns). Count_big is similar to a count function. The difference is to return to the Bigint data type count value, not int data type. Grouping passes an additional special information line. Use only when the Cube or Rollup operator is included in the Group By clause. The maximum value in the MAX backup operation. The minimum of the MIN backburses. The statistical standard deviation of all the values ​​in the STDEV backup operation. This function assumes a sampling sample for the value used in the calculation. The basic standard deviation of the Stdevp backup arithmetic calculation. This function assumes that the value used in the calculation is the statistical parent. The sum of the summary of the SUM backup operation. The statistical variation of VAR backup arithmetic calculation. This function assumes a sampling sample for the value used in the calculation. The number of statistical parent variability of all values ​​in the VARP backup operation. This function assumes that the value used in the calculation is the statistical parent.

The COUT function has a special user: the number of all data columns in the calculation of the data sheet. Use (*) after count, as shown below:

SELECT Count (*)

From publishers

Go

The result set is as follows:

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

8

The result set shows that the Publishers data is characterized by 8 data columns. AVG, Count, max, min and sum functions can be used with selective keywords all and distinct. For each function, all means that this function must be applied to all values ​​in this arithmetic, indicating that the repetition value will be ignored, the same value will only be calculated once. If the option is not specified, the preset is all ALL. The following example uses the Max and MIN functions together in the selected list to find the price difference between the most expensive and the cheapest book:

SELECT MAX (Price) - MIN (Price) AS "Price Difference"

From titles

Go

The result set is as follows:

Price Difference

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

19.96

In this example, you can get the total number of items ordered in all stores: select stores.stor_name, SUM (Sales.qty) AS "Total Items Ordered"

From Sales, Stores

Where sales.stor_id = store.stor_id

GROUP BY Stor_name

Go

The result set is as follows:

STOR_NAME TOTAL ITEMS Ordered

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

Barnum's 125

Bookbeat 80

DOC-U-MAT: Quality Laundry and Books 130

Eric The Read Books 8

Fricative bookshop 60

News & Brews 90

(Impact 6 data columns)

Description Remember, SQL Server provides a number of functions. If you want to perform a special job, check the SQL Server "Online Book" has no ready-made built-in functions.

SELECT's other usage SELECT statements are mainly used to perform queries, retrieved from the database. These queries can be very simple or complicated. SELECT can also be used to set the value of the variable or the call function. You can use the SELECT statement to set a value of a native variable in a transaction or pre-save program, although a better way is to use SET statements. The unit variable must use @ as its first character. For example, using SET gives this machine variable @count value of 0, the statement used is as follows:

Set @count = 0

Go

You can use SELECT to give a domain variable to a value returned by a query. For example, a domain variant @price is specified as the highest price obtained from the Price Data Row, using the following statement:

SELECT @price = max (price)

From items

Go

When setting one of the domain variables in the SELECT statement, the best case is that the query is only transferred back to a list. If there is a plurality of data columns to be returned, the present domain variable will be set to the value of the last column. You can also use the SELECT call function, such as the function GetDate provided by the system. The following statement call GATDATE Search Current Date and Time:

SELECT getDate ()

Go