03. Komponentet SQL

SQL’s Basic Objects

Data Types

Transact-SQL Functions

Scalar Operators

NULL Values

SQL’s Basic Objects

The language of Database Engine, Transact-SQL, has the same basic features as other

common programming languages:

c Literal values (also called constants)

c Delimiters

c Comments

c Identifiers

c Reserved keywords

Literal Values

A literal value is an alphanumerical, hexadecimal, or numeric constant.

ExamPLE 4.1

Some valid string constants and hexadecimal constants follow:

'Philadelphia'

"Berkeley, CA 94710"

'9876'

'Apostrophe is displayed like this: can''t' (note the two consecutive single quotation marks)

0x53514C0D

ExamPLE 4.2

The following are not string constants:

'AB'C' (odd number of single quotation marks)

'New York" (same type of quotation mark—single or double—must be used at each

end of the string)

ExamPLE 4.3

The following are numeric constants:

130

–130.00

–0.357E5 (scientific notation—nEm means n multiplied by 10m)

22.3E-3

Delimiters

In addition to enclosing strings, double quotation marks can also be used as delimiters for so-called delimited

identifiers. Delimited identifiers are a special kind of identifier usually used to allow the

use of reserved keywords as identifiers

Comments

There are two different ways to specify a comment in a Transact-SQL statement.

Using the pair of characters /* and */ marks the enclosed text as a comment.

the characters -- (two hyphens) indicate that the remainder of the current line is a comment.

Identifiers

In Transact-SQL, identifiers are used to identify database objects such as databases,

tables, and indices. They are represented by character strings that may include up to 128

characters and can contain letters, numerals,

The character # at the beginning of a table or stored procedure name denotes a temporary

object, while @ at the beginning of a name denotes a variable.

Reserved Keywords

Each programming language has a set of names with reserved meanings, which must be

written and used in the defined format. Names of this kind are called reserved keywords.

Data Types

All the data values of a column must be of the same data type.Transact-SQL uses different

data types, which can be categorized as follows:

c Numeric data types

c Character data types

c Temporal (date and/or time) data types

c Miscellaneous data types

c DECIMAL with VARDECIMAL storage type

Numeric Data Types

Numeric data types are used to represent numbers.

INTEGER Represents integer values that can be stored in 4 bytes. The range of values is –2,147,483,648 to

2,147,483,647. INT is the short form for INTEGER.

SMALLINT Represents integer values that can be stored in 2 bytes. The range of values is –32768 to 32767.

TINYINT Represents nonnegative integer values that can be stored in 1 byte. The range of values is 0 to 255.

BIGINT Represents integer values that can be stored in 8 bytes. The range of values is –263 to 263 – 1.

DECIMAL(p,[s]) Describes fixed-point values. The argument p (precision) specifies the total number of digits with

assumed decimal point s (scale) digits from the right. DECIMAL values are stored, depending on the

value of p, in 5 to 17 bytes. DEC is the short form for DECIMAL.

NUMERIC(p,[s]) Synonym for DECIMAL.

REAL Used for floating-point values. The range of positive values is approximately 2.23E – 308 to 1.79E +

308, and the range of negative values is approximately –1.18E – 38 to –1.18E + 38 (the value zero

can also be stored).

FLOAT[(p)] Represents floating-point values, like REAL. p defines the precision with p < 25 as single precision

(4 byte) and p >= 25 as double precision (8 byte).

MONEY Used for representing monetary values. MONEY values correspond to 8-byte DECIMAL values and

are rounded to four digits after the decimal point.

SMALLMONEY Corresponds to the data type MONEY but is stored in 4 bytes.

Character Data Types

There are two general forms of character data types. They can be strings of single-byte

characters or strings of Unicode characters.

CHAR[(n)] Represents a fixed-length string of single-byte characters, where n is the number of characters

inside the string. The maximum value of n is 8000. CHARACTER(n) is an additional equivalent form

for CHAR(n). If n is omitted, the length of the string is assumed to be 1.

VARCHAR[(n)] Describes a variable-length string of single-byte characters (0 < n ≤ 8000). In contrast to the CHAR

data type, the values for the VARCHAR data type are stored in their actual length. This data type has

two synonyms: CHAR VARYING and CHARACTER VARYING.

NCHAR[(n)] Stores fixed-length strings of Unicode characters. The main difference between the CHAR and NCHAR

data types is that each character of the NCHAR data type is stored in 2 bytes, while each character of

the CHAR data type uses 1 byte of storage space. Therefore, the maximum number of characters in a

column of the NCHAR data type is 4000.

NVARCHAR[(n)] Stores variable-length strings of Unicode characters. The main difference between the VARCHAR and

the NVARCHAR data types is that each NVARCHAR character is stored in 2 bytes, while each VARCHAR

character uses 1 byte of storage space. The maximum number of characters in a column of the

NVARCHAR data type is 4000.

Temporal Data Types

Transact-SQL supports the following temporal data types:

c DATETIME

c SMALLDATETIME

c DATE

c TIME

c DATETIME2

c DATETIMEOFFSET

miscellaneous Data Types

Transact-SQL supports several data types that do not belong to any of the data type

groups described previously:

c Binary data types

c BIT

c Large object data types

c CURSOR

c UNIQUEIDENTIFIER

c SQL_VARIANT

c TABLE

c XML

c Spatial (e.g., GEOGRAPHY and GEOMETRY) data types

c HIERARCHYID

c TIMESTAMP data type

c User-defined data types

Binary and BIT Data Types

BINARY[(n)] Specifies a bit string of fixed length with exactly n bytes (0 < n ≤ 8000).

VARBINARY[(n)] Specifies a bit string of variable length with up to n bytes (0 < n ≤ 8000).

BIT Used for specifying the Boolean data type with three possible values: FALSE, TRUE, and NULL.

Large Object Data Types

Large objects (LOBs) are data objects with the maximum length of 2GB. These objects

are generally used to store large text data and to load modules and audio/video files.

Use the data types VARCHAR(MAX), NVARCHAR(MAX), and

VARBINARY(MAX)

Use the so-called text/image data type

UNIQUEIDENTIFIER Data Type

As its name implies, a value of the UNIQUEIDENTIFIER data type is a unique

identification number stored as a 16-byte binary string.

SQL_VARIANT Data Type

The SQL_VARIANT data type can be used to store values of various data types at the

same time, such as numeric values, strings, and date values.Transact-SQL supports the SQL_VARIANT_PROPERTY function, which displays the attached information for each value of an SQL_VARIANT column.

TIMESTAMP Data Type

The TIMESTAMP data type specifies a column being defined as VARBINARY(8) or

BINARY(8), depending on nullability of the column.Thus, TIMESTAMP columns can be used to determine the relative time

when rows were last changed.

Transact-SQL Functions

Transact-SQL functions can be either aggregate functions or scalar functions. The

following sections describe these function types.

aggregate Functions

c AVG Calculates the arithmetic mean (average) of the data values contained

within a column. The column must contain numeric values.

c MAX and MIN Calculate the maximum and minimum data value of the

column, respectively. The column can contain numeric, string, and date/time

values.

c SUM Calculates the total of all data values in a column. The column must

contain numeric values.

c COUNT Calculates the number of (non-null) data values in a column. The

only aggregate function not being applied to columns is COUNT(*). This function

returns the number of rows (whether or not particular columns have NULL

values).

c COUNT_BIG Analogous to COUNT, the only difference being

that COUNT_BIG returns a value of the BIGINT data type.

Scalar Functions

c Numeric functions

c Date functions

c String functions

c System functions

c Metadata functions

Numeric Functions

Numeric functions within Transact-SQL are mathematical functions for modifying

numeric values.

ABS(n) Returns the absolute value (i.e., negative values are returned as positive) of the numeric expression

n. Example:

SELECT ABS(–5.767) = 5.767, SELECT ABS(6.384) = 6.384

COS(n) Calculates the cosine of n. n and the resulting value belong to the FLOAT data type.

POWER(x,y) Calculates the value xy. Examples:

SELECT POWER(3.12,5) = 295.65

SELECT POWER(81,0.5) = 9

RAND Returns a random number between 0 and 1 with a FLOAT data type.

SQRT(n) Calculates the square root of n. Example:

SELECT SQRT(9) = 3

ETJ.

Date Functions

GETDATE() Returns the current system date and time. Example:

SELECT GETDATE() = 2008-01-01 13:03:31.390

DATEPART(item,date) Returns the specified part item of a date date as an integer. Examples:

SELECT DATEPART(month, '01.01.2005') = 1 (1 = January)

SELECT DATEPART(weekday, '01.01.2005') = 7 (7 = Sunday)

DATENAME(item,date) Returns the specified part item of the date date as a character string. Example:

SELECT DATENAME(weekday, '01.01.2005') = Saturday

DATEDIFF(item,dat1,dat2) Calculates the difference between the two date parts dat1 and dat2 and returns the result

as an integer in units specified by the value item. Example:

SELECT DATEDIFF(year, BirthDate, GETDATE()) AS age FROM employee; -> returns the age

of each employee.

DATEADD(i,n,d) Adds the number n of units specified by the value i to the given date d. Example:

SELECT DATEADD(DAY,3,HireDate) AS age FROM employee; -> adds three days to the

starting date of employment of every employee (see the sample database).

String Functions

ASCII(character) Converts the specified character to the equivalent integer (ASCII) code. Returns an integer.

Example:

SELECT ASCII('A') = 65

CHAR(integer) Converts the ASCII code to the equivalent character. Example:

SELECT CHAR(65) = 'A'.

CHARINDEX(z1,z2) Returns the starting position where the partial string z1 first occurs in the string z2. Returns 0

if z1 does not occur in z2. Example:

SELECT CHARINDEX('bl', 'table') = 3.

LEN(z) Returns the number of characters, instead of the number of bytes, of the specified string

expression, excluding trailing blanks.

REPLACE(str1,str2,str3) Replaces all occurrences of the str2 in the str1 with the str3. Example:

SELECT REPLACE('shave' , 's' , 'be') = behave

STR(f,[len [,d]]) Converts the specified float expression f into a string. len is the length of the string including

decimal point, sign, digits, and spaces (10 by default), and d is the number of digits to the

right of the decimal point to be returned. Example:

SELECT STR(3.45678,4,2) = '3.46'

SUBSTRING(z,a,length) Creates a partial string from string z starting at the position a with a length of length.

Example:

SELECT SUBSTRING('wardrobe',1,4) = 'ward'

System Functions

Most system functions use an internal numeric identifier (ID), which is

assigned to each database object by the system at its creation.

CURRENT_TIMESTAMP Returns the current date and time. Example:

SELECT CURRENT_TIMESTAMP = '2008-01-01 17:22:55.670'

CURRENT_USER Returns the name of the current user.

ISNULL(expr, value) Returns the value of expr if that value is not null; otherwise, it returns value

(see Example 5.22).

ISNUMERIC(expression) Determines whether an expression is a valid numeric type.

NEWID() Creates a unique ID number that consists of a 16-byte binary string intended to store

values of the UNIQUEIDENTIFIER data type.

Metadata Functions

OBJECT_ID(obj_name) Returns the identifier of the database object obj_name. Example:

SELECT OBJECT_ID('products') = 453576654

COL_NAME(tab_id, col_id) Returns the name of a column belonging to the table with the ID tab_id and

column ID col_id. Example:

SELECT COL_NAME(OBJECT_ID('employee') , 3) = 'emp_lname'

COLUMNPROPERTY

(id, col, property)

Returns the information about the specified column. Example:

SELECT COLUMNPROPERTY(object_id('project'), 'project_no', 'PRECISION') = 4

DB_ID([db_name]) Returns the identifier of the database db_name. If no name is specified, the

identifier of the current database is returned. Example:

SELECT DB_ID('AdventureWorks') = 6

DB_NAME([db_id]) Returns the name of the database with the identifier db_id. If no identifier is

specified, the name of the current database is displayed. Example:

SELECT DB_NAME(6) = 'AdventureWorks'

Global Variables

Transact-SQL supports many global variables, which have to be preceded by

the prefix @@.

@@CONNECTIONS Returns the number of login attempts since starting the system.

@@ERROR Returns the information about the return value of the last executed Transact-SQL statement.

@@ROWCOUNT Returns the number of rows that have been affected by the last Transact-SQL statement

executed by the system.

The bitwise operators for manipulating bit strings are listed here, and Example 4.8

shows how they are used:

∼ Complement (i.e., NOT)

& Conjunction of bit strings (i.e., AND)

| Disjunction of bit strings (i.e., OR)

∧ Exclusive disjunction (i.e., XOR or Exclusive OR)

ExamPLE 4.8

~(1001001) = (0110110)

(11001001) | (10101101) = (11101101)

(11001001) & (10101101) = (10001001)

(11001001) ^ (10101101) = (01100100)

E.4.1

What is the difference between the numeric data types INT, SMALLINT, and TINYINT?

E.4.2

What is the difference between the data types CHAR and VARCHAR? When should

you use the latter (instead of the former) and vice versa?

E.4.3

How can you set the format of a column with the DATE data type so that its values

can be entered in the form 'yyyy/mm/dd'?

In the following two exercises, use the SELECT statement in the Query Editor

component window of SQL Server Management Studio to display the result of all

system functions and global variables. (For instance, SELECT host_id() displays the

ID number of the current host.)

E.4.4

Using system functions, find the ID number of the test database

E.4.5

Using the system variables, display the current version of the database system software

and the language that is used by this software.

E.4.6

Using the bitwise operators &, |, and ^, calculate the following operations with the bit

strings:

(11100101) & (01010111)

(10011011) | (11001001)

(10110111) ^ (10110001)