Transact SQL

This SQL is used to execute queries on databases.  For the purposes of the exam this will usually be done either within visual basic, by being passed into ADO methods, or when defining stored procedures within SQL Server.

• The SELECT statement is used to fetch rows from one or more tables.
• To select rows from a single table use the following form
   
  SELECT [Column List] FROM [Table list] WHERE [Filter] ORDER BY [Column List] [ASC|DESC]
   
• The where clause (Filter) is a conditional expression that determines which rows are returned. These are typically expressions involving column names, SQL operators and standard comparison and boolean operators.  Examples are listed below
• • WHERE age > 55
  • WHERE age > 55 AND  status=0
  • WHERE age<21 AND (status = 0) OR (status IS NULL)
  • WHERE age BETWEEN 21 and 55  *Note: this will include people 21 and 55
  • WHERE status IN (1,2,6,8)
  • WERE lastname LIKE 'S%'   *All people with last name begining with S
• The column list can be names of columns in the table or expressions involving columns that are calculated for each row before being returned.
• Each column value to return is seperated by a comma
• Using a asterix for the column list returns all the colunms in the table
• Column list expressions can be labelled using the AS keyword.  This label determines its column name in the result set
• ORDER BY exp [ASC|DESC] is optional and determines how the order in which the rows are returned.  It can be a single column label  or ordinal position (index as defined in column list), or  a comma seperated list of columns to specify a mutilevel sort.
• Select Example:

  Select CustomerName,Age from Customers where Age>35 orderby CustomerName
   

• To select data from multiple tables JOINS are used:

INSERT statements are used to add rows to a table. Identity fields in the inserted record and NOT returned (which is just stupid as all hell) but can be retrieved by using SELECT @@IDENTITY; or SELECT SCOPE_IDENTITY() in 2005. Watch out for automatic triggers as they may cause the wrong identity to be returned. See Inserting records in ASP with ADO
 

• The following form inserts a single row:
  INSERT INTO tablename (insertcol1,insertcol2) VALUES (value1,value2)
  
• The following form inserts multiple rows from another table:
  INSERT INTO table1 (insertcol1,insertcol2) SELECT col1,col2 FROM table2
   

DELETE is used to remove rows from a table

DELETE FROM table WHERE deletefilter
 

The UPDATE statement is used to change rows already entered into a table

UPDATE table SET column = expression,column = expression WHERE updatefilter

The update filter can specifiy a single row (e.g where id=10034) or multiple rows (e.g. where id > 10000) to be updated

Data Types

• Integers

  bit

  Integer data with either a 1 or 0 value.

  int

  Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

  smallint

  Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).

  tinyint

  Integer data from 0 through 255.

• decimal and numeric

  decimal

  Fixed precision and scale numeric data from -10^38 -1 through 10^38 -1.

  numeric

  A synonym for decimal.

• money and smallmoney

  money

  Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

  smallmoney

  Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.

• Approximate Numerics

• float

  Floating precision number data from -1.79E + 308 through 1.79E + 308.

  real

  Floating precision number data from -3.40E + 38 through 3.40E + 38.

• datetime and smalldatetime

  datetime

  Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.

  smalldatetime

  Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.

• Numerics

• cursor

  A reference to a cursor.

  timestamp

  A database-wide unique number.

  uniqueidentifier

  A globally unique identifier (GUID).

• Character Strings

  char

  Fixed-length non-Unicode character data with a maximum length of 8,000 characters.

  varchar

  Variable-length non-Unicode data with a maximum of 8,000 characters.

  text

  Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.

• Unicode Character Strings

  nchar

  Fixed-length Unicode data with a maximum length of 4,000 characters.

  nvarchar

  Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type that is a synonym for nvarchar(128) and is used to reference database object names.

  ntext

  Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.

• Binary Strings

  binary

  Fixed-length binary data with a maximum length of 8,000 bytes.

  varbinary

  Variable-length binary data with a maximum length of 8,000 bytes.

  image

  Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.

Search Condition (T-SQL)

Is a combination of one or more predicates using the logical operators AND, OR, and NOT.

Syntax

<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)
    }

Arguments

<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. For more information, see NOT.

AND

Combines two conditions and evaluates to TRUE when both of the conditions are TRUE. For more information, see AND.

OR

Combines two conditions and evaluates to TRUE when either condition is TRUE. For more information, see OR.

<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.

<=

Is the operator used to test the condition of one expression being less than or equal to the other expression.

!<

Is the operator used to test the condition of one expression not being less than the other expression.

string_expression

Is a string of characters and wildcard characters.

[NOT] LIKE

Indicates that the subsequent character string is to be used with pattern matching. For more information, see LIKE.

ESCAPE 'escape_character'

Allows a wildcard character to be searched for in a character string instead of functioning as a wildcard. escape_character is the character that is placed in front of the wildcard character to denote this special use.

[NOT] BETWEEN

Specifies an inclusive range of values. Use AND to separate the beginning and ending values. For more information, see BETWEEN.

IS [NOT] NULL

Specifies a search for null values, or for values that are not null, depending on the keywords used. An expression with a bitwise or arithmetic operator evaluates to NULL if any of the operands is NULL.

CONTAINS

Searches columns containing character-based data for precise or “fuzzy” (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, and weighted matches. Can only be used with SELECT statements. For more information, see CONTAINS.

FREETEXT

Provides a simple form of natural language query by searching columns containing character-based data for values that match the meaning rather than the exact words in the predicate. Can only be used with SELECT statements. For more information, see FREETEXT.

[NOT] IN

Specifies the search for an expression, based on the expression's inclusion in or exclusion from a list. The search expression can be a constant or a column name, and the list can be a set of constants or, more commonly, a subquery. Enclose the list of values in parentheses. For more information, see IN.

subquery

Can be considered a restricted SELECT statement and is similar to <query_expresssion> in the SELECT statement. The ORDER BY clause, the COMPUTE clause, and the INTO keyword are not allowed. For more information see SELECT.

ALL

Used with a comparison operator and a subquery. Returns TRUE for <predicate> if all values retrieved for the subquery satisfy the comparison operation, or FALSE if not all values satisfy the comparison or if the subquery returns no rows to the outer statement. For more information, see ALL.

{SOME | ANY}

Used with a comparison operator and a subquery. Returns TRUE for <predicate> if any value retrieved for the subquery satisfies the comparison operation, or FALSE if no values in the subquery satisfy the comparison or if the subquery returns no rows to the outer statement. Otherwise, the expression is unknown. For more information, see SOME | ANY.

EXISTS

Used with a subquery to test for the existence of rows returned by the subquery. For more information, see EXISTS.

Remarks

The order of precedence for the logical operators is NOT (highest), followed by AND, followed by OR. The order of evaluation at the same precedence level is from left to right. Parentheses can be used to override this order in a search condition. For information about how the logical operators operate on truth values, see AND, OR, and NOT.

Examples

A. Use WHERE with LIKE and ESCAPE syntax

This example assumes a description column exists in finances table. To search for the rows in which the description column contains the exact characters g_, use the ESCAPE option because _ is a wildcard character. Without specifying the ESCAPE option, the query would search for any description values containing the letter g followed by any single character other than the _ character.

SELECT *

FROM finances

WHERE description LIKE 'gs_' ESCAPE 'S'

GO

B. Use WHERE and LIKE syntax with Unicode data

This example uses the WHERE clause to retrieve the contact name, telephone, and fax numbers for any companies containing the string snabbköp at the end of the company name.

USE Northwind

SELECT CompanyName, ContactName, Phone, Fax

FROM Customers

WHERE CompanyName LIKE N'%snabbköp'

ORDER BY CompanyName ASC, ContactName ASC

Duplicates

PICList 2008 contributors: o List host: MIT, Site host massmind.org, Top posters @20081203 Apptech, Jinx, Xiaofan Chen, olin piclist, Vitaliy, William \Chops\ Westfield, Tamas Rudnai, JonnyMac, Alan B. Pearce, Gerhard Fiedler, * Page Editors: James Newton, David Cary, and YOU! * Roman Black of Black Robotics donates from sales of Linistep stepper controller kits. * Ashley Roll of Digital Nemesis donates from sales of RCL-1 RS232 to TTL converters. * Monthly Subscribers: Shultz Electronics, Timothy Weber, on-going support is MOST appreciated! * Contributors: Richard Seriani, Sr.

  .