Sql server tutorial 10 Working with Functions and Value Expressions - XADAT.COM
15801
post-template-default,single,single-post,postid-15801,single-format-standard,qode-quick-links-1.0,ajax_fade,page_not_loaded,,qode-theme-ver-11.1,qode-theme-bridge,wpb-js-composer js-comp-ver-5.1.1,vc_responsive

Sql server tutorial 10 Working with Functions and Value Expressions No ratings yet.

Sql server tutorial 10 Working with Functions and Value Expressions

Key Skills & Concepts

  • Use Set Functions
  • Use Value Functions
  • Use Value Expressions
  • Use Special Values

In earlier parts of the book, you have been briefly introduced to various value-related
functions and expressions. These values and expressions are used in examples and Try
This exercises in a number of chapters to demonstrate different components of SQL. In
this chapter, I take a closer look at many of these values and expressions, focusing on
those that you are most likely to use as a beginning SQL programmer. You should keep in
mind, however, that this chapter covers only a portion of the many types of functions and
expressions supported by SQL. In addition, SQL implementations can vary greatly with
regard to which SQL functions and expressions they support, how those values and
expressions are implemented, and what nonstandard functions and expressions they
include in their products in addition to the standard ones. Be sure to check the product
documentation to determine what functionality is supported. In general, I include in this
chapter those functions and expressions most commonly supported by SQL
implementations.

Use Set Functions

In Chapter 3, I introduce the concept of a function. As you might recall, a function is a
named operation that performs predefined tasks that you can’t normally perform by using
SQL statements alone. It is a type of routine that takes input parameters, which are
enclosed in parentheses, and returns values based on those parameters. An important
property of functions is that each execution of a function returns exactly one data value,
and this is why functions can be used in place of table column names in the SELECT list
of a query—the function returns a single value for each row processed by the query. You
have already seen examples of functions, such as SUM and AVG. Both of these functions
are known as set functions. A set function, sometimes referred to as an aggregate function,
processes or calculates data and returns the appropriate values. Set functions require that
the data be grouped in some way, such as would be the case if the GROUP BY clause
were used in a SELECT statement. If the rows are not explicitly grouped in some way, the
entire table is treated as one group.
In this section, I discuss five set functions: COUNT, MAX, MIN, SUM, and AVG.
These functions are all commonly supported in SQL implementations. For all the set
functions, I provide examples of how you would use them in the SELECT clause of a
SELECT statement. The examples are based on the table shown in Figure 10-1.

 

Using the COUNT Function

The first set function that we’ll look at is the COUNT function. As the name implies, the
COUNT function counts the number of rows in a table or the number of values in a
column, as specified in a SELECT statement. When you use the COUNT function, you
must specify a column name to count the number of non-null values in a column, or an
asterisk to count all the rows in a table regardless of null values. For example, if you want
to know the total number of rows in the ARTIST_CD_SALES table, you can use the
following.
In this statement, the COUNT function is used with an asterisk—in parentheses—to
count every row in the ARTIST_CD_SALES table and return the total count. The returned
value is listed in the TOTAL_ROWS column, a name given to the column returned in the
query results, as shown in the following results:
As you can see, the query results include only one value (one row with one column), as
can be expected of a set function used without any row grouping. The value of 10
indicates that the ARTIST_CD_SALES table contains 10 rows.

As with any other sort of SELECT statement, you can qualify your query results by
adding the necessary clauses to the statement. For example, suppose you want to find out
how many rows include a NUMBER_SOLD value greater than 20. You can modify your
SELECT statement to include a WHERE clause:
The value returned will now be 7 rather than 10 because only seven rows meet the
search condition specified in the WHERE clause.
You might find that instead of querying the number of rows in a table, you want to
know the number of values in a given column (excluding null values). In this case, you
would specify the column name rather than the asterisk.

When you execute this query, the value returned is again 7. This means that seven
ARTIST_NAME values have a NUMBER_SOLD value greater than 20. However, this
statement doesn’t account for ARTIST_NAME values that might be duplicated. If you
want to arrive at a count that takes into consideration duplicate values, you can add the
DISTINCT keyword to the COUNT function:
This time, a value of 6 is returned rather than 7. This is because the ARTIST_NAME
column includes two instances of the Stevie Wonder value. The column contains only six
unique values that meet the condition set forth in the search criteria.

0 >1>2>3>4>5>6>7>8>9>10>11>12>13>14>15>16>17>18>19>20>

No Comments

Sorry, the comment form is closed at this time.