SQL Server Basics-1

Document Sample
SQL Server Basics-1 Powered By Docstoc

S.No Clause Name       Description
1    ORDER BY Clause   Specifies the sort for the result set. The ORDER BY clause is
                                  invalid in views, inline functions, derived tables, and
                                  subqueries, unless TOP is also specified.
2    GROUP BY Clause   Specifies the groups into which output rows are to be placed and, if
                                  aggregate functions are included in the SELECT clause
                                  <select list>, calculates a summary value for each group.
                                  When GROUP BY is specified, either each column in
                                  any non-aggregate expression in the select list should be
                                  included in the GROUP BY list, or the GROUP BY
                                  expression must match exactly the select list expression.

                       Note: If the ORDER BY clause is not specified, groups returned
                                  using the GROUP BY clause are not in any particular
                                  order. It is recommended that you always use the
                                  ORDER BY clause to specify a particular ordering of the
3    WHERE Clause      Specifies a search condition to restrict the rows returned
4    HAVING Clause     Specifies a search condition for a group or an aggregate. HAVING
                                  is usually used with the GROUP BY clause. When
                                  GROUP BY is not used, HAVING behaves like a
                                  WHERE clause.
5    INTO Clause       Creates a new table and inserts the resulting rows from the query
                                  into it.

                       The user executing a SELECT statement with the INTO clause
                                 must have CREATE TABLE permission in the
                                 destination database. SELECT...INTO cannot be used
                                 with the COMPUTE.

                       We can use SELECT...INTO to create an identical table definition
                                  (different table name) with no data by having a FALSE
                                  condition in the WHERE clause.
6    SELECT Clause     Specifies the columns to be returned by the query
7    FOR Clause        FOR clause is used to specify either the BROWSE or the XML
                                  option (BROWSE and XML are unrelated options).
8    FROM Clause       Specifies the table(s) from which to retrieve rows. The FROM
                                  clause is required except when the select list contains
                                  only constants, variables, and arithmetic expressions (no
                                  column names).
9    COMPUTE Clause    Generates totals that appear as additional summary columns at the
                                  end of the result set. When used with BY, the COMPUTE
                                  clause generates control-breaks and subtotals in the
                                  result set. You can specify COMPUTE BY and
                                  COMPUTE in the same query.
10   OPTION Clause     Specifies that the indicated query hint should be used throughout
                                  the entire query. Each query hint can be specified only
                                  once, although multiple query hints are permitted. Only
                                         one OPTION clause may be specified with the
                                         statement. The query hint affects all operators in the
                                         statement. If a UNION is involved in the main query, only
                                         the last query involving a UNION operator can have the
                                         OPTION clause. If one or more query hints causes the
                                         query optimizer to not generate a valid plan, error 8622
                                         is produced.

                              Caution: Because the query optimizer usually selects the best
                                       execution plan for a query, it is recommended that
                                       <join_hint>, <query_hint>, and <table_hint> be used only
                                       as a last resort by experienced database administrators


   An operator is a symbol specifying an action that is performed on one or more expressions.
Microsoft® SQL Server™ 2000 uses these operator categories:

   •   Arithmetic operators

   •   Assignment operator

   •   Bitwise operators

   •   Comparison operators

   •   Logical operators

   •   String concatenation operator

   •   Unary operators

S.No Operator Name               Description
1    Arithmetic operators        Arithmetic operators perform mathematical operations on two
                                            expressions of any of the data types of the numeric
                                            data type category.

                                 Operator      Meaning
                                 + (Add)       Addition
                                 -             Subtraction.

                                 *             Multiplication.

                                 / (Divide)    Division
                                 %             Returns the integer remainder of a division. For
                                                  example, 12 % 5 = 2 because the
                                                  remainder of 12 divided by 5 is 2.
                          The plus (+) and minus (-) can also be used to perform arithmetic
                                     operations on datetime and smalldatetime values.
2   Assignment operator   Transact-SQL has one assignment operator, the equals sign
                                    (=). In this example, the @MyCounter variable is
                                    created. Then, the assignment operator sets
                                    @MyCounter to a value returned by an expression.

                          DECLARE @MyCounter INT
                          SET @MyCounter = 1

                          The assignment operator can also be used to establish the
                                   relationship between a column heading and the
                                   expression defining the values for the column. This
                                   example displays two column headings named
                                   FirstColumnHeading and SecondColumnHeading.
                                   The string xyz is displayed in the
                                   FirstColumnHeading column heading for all rows.
                                   Then, each product ID from the Products table is
                                   listed in the SecondColumnHeading column

                          USE Northwind
                          SELECT FirstColumnHeading = 'xyz',
                             SecondColumnHeading = ProductID
                          FROM Products

3   Bitwise operators     Bitwise operators perform bit manipulations between two
                                    expressions of any of the data types of the integer
                                    data type category.

                            Operator               Meaning
                            & (Bitwise AND)        Bitwise AND (two operands).
                            | (Bitwise OR)         Bitwise OR (two operands).
                            ^ (Bitwise             Bitwise exclusive OR (two operands).
                                       ve OR)
                            The operands for bitwise operators can be any of the data
                                       types of the integer or binary string data type
                                       categories (except for the image data type), with
                                       the exception that both operands cannot be any
                                       of the data types of the binary string data type
                                       category. The table shows the supported
                                       operand data types.
                             Left operand            Right operand
                             binary                  int, smallint, or tinyint
                             bit                     int, smallint, tinyint, or bit
                             int                     int, smallint, tinyint, binary, or
                             smallint                int, smallint, tinyint, binary, or varbinary
                             tinyint                 int, smallint, tinyint, binary, or varbinary
                             varbinary               int, smallint, or tinyint
4   Comparison operators   Comparison operators test whether or not two expressions are
                                     the same. Comparison operators can be used on all
                                     expressions except expressions of the text, ntext, or
                                     image data types.
                            Operator              Meaning
                            = (Equals)            Equal to
                            > (Greater Than)      Greater than
                            < (Less Than)         Less than
                            >= (Greater Than or Greater than or equal to
                             <= (Less Than or        Less than or equal to
                             <> (Not Equal To)       Not equal to
                            != (Not Equal To)        Not equal to (not SQL-92 standard)
                            !< (Not Less             Not less than (not SQL-92 standard)
                            !> (Not Greater        Not greater than (not SQL-92
                                        Than)                standard)
                           The result of a comparison operator has the Boolean data
                                      type, which has three values: TRUE, FALSE, and
                                      UNKNOWN. Expressions that return a Boolean
                                      data type are known as Boolean expressions.
                                      Unlike other SQL Server data types, a Boolean data
                                      type cannot be specified as the data type of a table
                                      column or variable, and cannot be returned in a
                                      result set.

                           When SET ANSI_NULLS is ON, an operator that has one or two NULL
                                     expressions returns UNKNOWN. When SET ANSI_NULLS is
                                     OFF, the same rules apply, except an equals operator returns
                                     TRUE if both expressions are NULL. For example, NULL =
                                     NULL returns TRUE if SET ANSI_NULLS is OFF.

                           Expressions with Boolean data types are used in the WHERE
                           clause to filter the rows that qualify for the search conditions
                           and in control-of-flow language statements such as IF and
                           WHILE, for example:

                           USE Northwind
                           DECLARE @MyProduct int
                           SET @MyProduct = 10
                           IF (@MyProduct <> 0)
                              SELECT *
                              FROM Products
                              WHERE ProductID = @MyProduct

5   Logical operators      Logical operators test for the truth of some condition. Logical
                                     operators, like comparison operators, return a
                                     Boolean data type with a value of TRUE or FALSE.

                            Operator            Meaning
                            ALL                 TRUE if all of a set of comparisons are
                            AND                 TRUE if both Boolean expressions are
                            ANY                 TRUE if any one of a set of comparisons
                                                           are TRUE.
                            BETWEEN             TRUE if the operand is within a range
                            EXISTS              TRUE if a subquery contains any rows.
                            IN                  TRUE if the operand is equal to one of a
                                                           list of expressions.
                            LIKE                TRUE if the operand matches a pattern.
                            NOT                 Reverses the value of any other Boolean
                            OR                  TRUE if either Boolean expression is
                            SOME                TRUE if some of a set of comparisons
                                                           are TRUE.
6   String concatenation   The string concatenation operator allows string concatenation
              operator                with the addition sign (+), which is also known as
                                      the string concatenation operator. All other string
                                      manipulation is handled through string functions
                                      such as SUBSTRING.

                           By default, an empty string is interpreted as an empty string in
                                     INSERT or assignment statements on data of the
                                     varchar data type. In concatenating data of the
                                     varchar, char, or text data types, the empty string is
                                     interpreted as an empty string. For example, 'abc' +
                                     '' + 'def' is stored as 'abcdef'. However, if the
                                     sp_dbcmptlevel compatibility level setting is 65,
                                     empty constants are treated as a single blank
                                     character and 'abc' + '' + 'def' is stored as 'abc def'
7   Unary Operators        Unary operators perform an operation on only one expression
                                     of any of the data types of the numeric data type

                            Operator               Meaning
                                     + (Positive)          Numeric value is positive.
                                     - (Negative)          Numeric value is negative.
                                     ~ (Bitwise NOT)       Returns the ones complement of the
                                   The + (Positive) and - (Negative) operators can be used on
                                            any expression of any of the data types of the
                                            numeric data type category. The ~ (Bitwise NOT)
                                            operator can be used only on expressions of any of
                                            the data types of the integer data type category.

                                                OPERATOR PRECEDENCE

 When a complex expression has multiple operators, operator precedence determines the sequence in
which the operations are performed. The order of execution can significantly affect the resulting value.

  Operators have these precedence levels. An operator on higher levels is evaluated before an
operator on a lower level:

   •   + (Positive), - (Negative), ~ (Bitwise NOT)

   •   * (Multiply), / (Division), % (Modulo)

   •   + (Add), (+ Concatenate), - (Subtract)

   •   =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)

   •   ^ (Bitwise Exclusive OR), & (Bitwise AND), | (Bitwise OR)

   •   NOT

   •   AND


   •   = (Assignment)

  When two operators in an expression have the same operator precedence level, they are evaluated
left to right based on their position in the expression. For example, in the expression used in the SET
statement of this example, the subtraction operator is evaluated before the addition operator.

DECLARE @MyNumber int
SET @MyNumber = 4 - 2 + 27
-- Evaluates to 2 + 27 which yields an expression result of 29.
SELECT @MyNumber

  Functions in programming languages are subroutines used to encapsulate frequently performed logic.
Any code that must perform the logic incorporated in a function can call the function rather than having to
repeat all of the function logic

    Microsoft® SQL Server™ 2000 supports two types of functions:

Built-in Functions:

Operate as defined in the Transact-SQL Reference and cannot be modified. The functions can be
referenced only in Transact-SQL statements using the syntax defined in the Transact-SQL Reference.

Microsoft® SQL Server™ 2000 has built-in functions to perform certain operations. The
function categories are:

S.No Built-in Functions Name         Description
1    Aggregate functions             Perform operations that combine multiple values into one.
                                                Examples are COUNT, SUM, MIN, and MAX.
2        Configuration functions     Scalar functions that return information about configuration
3        Cursor functions            Return information about the status of a cursor
4        Date and time functions     Manipulate datetime and smalldatetime values
5        Mathematical functions      Perform trigonometric, geometric, and other numeric
6        Meta data functions         Return information on the attributes of databases and
                                                database objects
7        Rowset functions            Return rowsets that can be used in the place of a table
                                                reference in a Transact-SQL statement
8        Security functions          Return information about users and roles
9        String functions            Manipulate char, varchar, nchar, nvarchar, binary, and
                                                varbinary values
10       System functions            Operate on or report on various system level options and
11       System statistical          Return information regarding the performance of SQL
                   functions                    Server
12       Text and image functions    Manipulate text and image values

User-defined functions :

         Allows us to define our own Transact-SQL functions using the CREATE FUNCTION statement.

         User-defined functions take zero or more input parameters, and return a single
         value. Some user-defined functions return a single, scalar data value, such as an
         int, char, or decimal value.

         For example, this statement creates a simple function that returns a decimal:

         CREATE FUNCTION CubicVolume
         -- Input dimensions in centimeters.
            (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
             @CubeHeight decimal(4,1) )
         RETURNS decimal(12,3) -- Cubic Centimeters.
            RETURN ( @CubeLength * @CubeWidth * @CubeHeight )

    Types of User-Defined Functions

     SQL Server 2000 supports three types of user-defined functions:

     •   Scalar functions

     •   Inline table-valued functions

     •   Multistatement table-valued functions

A user-defined function takes zero or more input parameters and returns either a scalar value or a
table. A function can have a maximum of 1024 input parameters. When a parameter of the function
has a default value, the keyword default DEFAULT must be specified when calling the function to
get the default value. This behavior is different from parameters with default values in stored
procedures in which omitting the parameter also implies the default value. User-defined functions
do not support output parameters.

S.No User-Defined Function               Description
1    Scalar function                     Returns a single data value of the type defined in a
                                                   RETURNS clause. All scalar data types, including
                                                   bigint and sql_variant, can be used. The
                                                   timestamp data type, user-defined data type, and
                                                   nonscalar types, such as table or cursor, are not
                                                   supported. The body of the function, defined in a
                                                   BEGIN...END block, contains the series of
                                                   Transact-SQL statements that return the value.
                                                   The return type can be any data type except text,
                                                   ntext, image, cursor, and timestamp
2        Inline table-valued             Table-valued functions return a table. For an inline table-
                     function                      valued function, there is no function body; the
                                                   table is the result set of a single SELECT
3        Multistatement table-           For a multistatement table-valued function, the function
                   valued function                 body, defined in a BEGIN...END block, contains
                                                   the TRANSACT-SQL statements that build and
                                                   insert rows into the table that will be returned

Shared By:
Description: This provides a brief introduction of SQL Server basic concepts in a easy format.