SQL Quarries

Document Sample
SQL Quarries Powered By Docstoc
					           SOFTWARE TESTING Documents
                           SUB QUERIES

       A sub query can be defined as a group of nested SELECT statements inside a
SELECT, INSERT, UPDATE or DELETE statement. Sub query can also be used inside
the WHERE or HAVING clauses of the outer SELECT, INSERT, UPDATE or DELETE
statements. SELECT statements containing one or more sub queries are called
nested queries.

The command syntax is:

        (SELECT [ALL|DISTINCT] suquery_select_list
        [FROM {table_nmae | view_name}
        [WHERE clause]
        GROUP BY clause]
        [HAVING clause])

        A subquery must be enclosed within parentheses and cannot use ORDER BY,
COMPUTE BY or FOR BROWSE clauses. SQL Server does not implement any
restriction on level of nesting while using subqueries, SQL Server imposes
restrictions on the number of tables or views used in a subquery or a join.

SQL Server evaluates the inner query first and returns the result to the outer query
for the final result set.
The outer query always depends on the evaluation result of the subquery.

Subqueries can be divided into three catefories depending upon the values they

      Sub queries that operate on lists: this type of query returns single-column-
       multiple values results and are implemented using the IN clause. The syntax
       is as follows:

                      WHERE expression [NOT] IN (subquery)

      Subqueries that are introduced with an unmodified comparison o-erator: this
       type of query returns single column-single value results for outer query
       evaluation   and     is   implemented    using    unmodified   comparison
       operators(operators without the ANY or ALL keywords) the syntax is as

          WHERE expression comparison_operator [ANY|ALL] (subquery)

      Subqueries that check for the existence of data: this type of query checks for
       the existence of records in a table that are used in the inner query, and
       returns either a TRUE or a FALSE VALUE based on the existence of data. This
       is implemented using the EXISTS keyword. The syntax is as follows:\

       Visit: for QTP Documents                                  1
            SOFTWARE TESTING Documents
SubQueries With IN
         WHERE [NOT] EXISTS (subquery)

        A subquery introduced with IN returns zero or more values. Consider the
following example where all author ID’S, from the TITLEAUTHOR table, are displayed
whose books are sold:

 SELECT au_id
      FROM titleauthor
      WHERE title_id IN (SELECT title_id FROM sales)

SQL Server returns a list of all title IDs to the main query then lists all the authors,
whose books are sold, in the result set.

Consider the following example where the server returns a list of publisher IDs to the
main query, and then determines whether each publisher’s pub_id is in that list:

         SELECT publisher=pub_name
         FROM publishers
         WHERE pub_id IN ( SELECT pub_id FROM titles WHERE type=’business’)

The inner query is evaluated first and then the result set is sent to the outer query.

Consider another subquery with the IN clause:

 SELECT type=type, Average=AVG(ytd_sales)
       FROM titles
       WHERE type IN (SELECT type FROM titles
 WHERE title=” the busy executive’s database guide” or title=’Is Anger the
 Enemy?’) GROUUP BY type

The inner query is evaluated first and then the result set is sent to the outer query.

The NOT IN clause is used in the same way as the IN clause. Consider the following

                SELECT pub_id, pub_name
                FROM publishers
                WHERE pub_id NOT IN (SELECT pub_id FROM titles
                WHERE type=’mod_cook’)

      Visit: for QTP Documents                                           2
           SOFTWARE TESTING Documents
Sub Queries with EXISTS

        The subquery, when used with the EXISTS clause, always returns data in
terms of TRUE OR FALSE and passes the status to the outer query to produce the
results set. The subquery returns a TRUE value if the result set contains any rows.

The query introduced with the EXISTS keyword differs from other queries. The
EXISTS keyword is not preceded by any column name, constant or there expression
and it contains an asterisk (*) in the SELECT list.

        1. SELECT pub_name
        FROM publishers
        WHERE EXISTS (SELECT * FROM titles WHERE type=’business’)

        2. SELECT pub_name
        FROM publishers
        WHERE EXISTS (SELECT * FROM publishers WHERE City=’Paris’)

Aggregate functions can also be used in subqueries. Consider the following example
which displays the titles of all those books for which the advance is more than the
average advance of business related books.

        SELECT Title=title
        FROM titles
        WHERE advance>(SELECT AVG (advance) from titles
                           WHERE type=’business’)

Subquery Restrictions:

The restrictions imposed are:

      The column list of the select statement of a subquery introduced with the
       comparison operator can include only one column.
      The column used in the WHERE clause of the outer query should be
       compatible with the column used in the select list of the inner query.
      The DISTINCT keyword cannot be used with the subqueries that include the
       GROUP BY clause.
      The ORDER BY clause, GROUP BY clause and INTO keyword cannot be used in
       a subquery, because a subquery cannot manipulate its result internally.
      Do not specify more than one column name in the subquery introduced with
       the EXISTS keyword.
      A view created with a subquery cannot be updated.

Nested Sub Queries:
      A sub query can contain one or more subqueries. There is no restriction in the
number of subqueries that you can include with SELECT, INSERRT, UPDATE or
DELETE statements.

       Visit: for QTP Documents                                 3
            SOFTWARE TESTING Documents

 1. SELECT ‘Author Name’=SUBSTRING (au_fname, 1,1)+’.’+au_lastname
 FROM authors WHERE au_id IN(SELECT au_id FROM titleauthor
       WHERE title=’Net Etiquette’))

 2. SELECT ‘Author ID’=au_id, Name=SUBSTRING (au_fnmae, 1,1) +
 ‘.’+au_lname FROM authors WHERE au_id IN (SELECT au_id FROM
 titleauthor WHERE type=’business’))

           Example                                       Description
SELECT title_id=title_id, Title=title       Lists all the titles along with their IDs
FROM title                                  from the titles table where price is
WHERE prie>ALL(SELECT price                 greater than the maximum price of books
FROM titles                                 published by the publisher with publisher
WHERE pub_id=’0736’)                        ID 0736.
SELECT title_ID = title_id, Title = title   Lists all the titles along with their titles
FROM titles                                 IDs from the titles table where price is
WHERE price >ANY (SELECT price              greater than the minimum price of books
FROM titles                                 published by the publisher with publisher
WHERE pub_id = `0736`)                      ID 0736.

SELECT publisher_ID = pub_id, Name =        Lists all the publishers where city is sane
pub_name                                    as of any author.
FROM publishers


A SELECT statement with an INTO clause is used to store the result set in a new
table without a data definition process. The SELECT INTO statement creates a new
table, if the table already exists then the operation fails.
The syntax of the SELECT INTO statement is:

 SELECT columns_list
      INTO new_table_name
      FROM table_names
      WHERE conditions

The SELECT INTO clause creates a permanent table if the select into/bulk copy
database option is set. If the select into/bulkcopy database option is not set, then
only local temporary tables (prefixed by #), and global temporary tables (prefixed by
##) can be created.

       Visit: for QTP Documents                                     4
            SOFTWARE TESTING Documents
The select into/bulkcopy database option can be set using the following command:

 sp_dboption ‘pubs’, ‘select into / bulkcopy ‘, true

 SELECT title_id, title
INTO newtitles
From titles
WHERE price >$15

Column names in the new table can be changed while specifying the columns in the
SELECT statement. Consider the following example with the new column names:

 SELECT Title _ID = title _id, Title_Name =title
 INTO new titles 1
 FROM titles
 WHERE advance >$7000


   SQL Server provides a unique operator known as the UNION operator that is used
to combine the result set or more queries.

The syntax is:

 SELECT column_list [INTO new_table_name]
              [FROM clause]
              [WHERE clause]
              [GROUP BY clause]
              [HAVING clause]….]
       [ORDER BY clause]
       [COMPUTE clause]

By default, the result set of the UNION operator removes the duplicate rows from the
queries combined, until an ALL clause is specified with the UNION operator.

The queries combined with the UNION operator must contain an equal number of
columns or expressions, and they must be compatible with each other

There are two tables: region_east and region_west. The region_east table contains
the employee id, name and address of the employees of the eastern region. The
region_west table contains the employee id, name and the address of the employees
of the western region.

       Visit: for QTP Documents                                  5
             SOFTWARE TESTING Documents
The region_east table contains the following data:

  emp_id      emp_name                            emp_add
   - - - - - - - - - - - - - - - - - - - - - - - - -- -- - - - - - - - - - - - - - - - - -
   E001         George                       323, Park Avenue Street
   E002          Jack                        475, Barbara Lines
   E003          Nancy                         68, Bank Street

   The region_west table contains the following data:

   Emp_id      emp_name                             emp_add
   - - - - - - - - - - - - - - - - - -- - -      - - - - - - - - - - -- - - - - - - - -
   W001        Maria                             45, Canara Street
   W002       James                               12, Blue Lines
   W003       Jill                                98, Civil Lines

The following statement displays employee ids and names of the both the regions:
east and the west.

SELECT emp_id emp_name FROM region_east
SELECT emp_id, emp_name FROM region_west

SELECT ‘Employee ID’ =emp_id, ‘Employee Name’=emp_name FROM region_east
SELECT emp_, emp_name FROM region_west

Rules Regarding the use of the UNION Operator

            The restrictions imposed by SQL Server on the use of the UNION
operator are listed below:

       Corresponding columns in the individual queries of a UNION statement must
        occur in the same order, because UNION compares the columns in the order
        specified in the individual queries.
       The columns or expressions used in one query must be equal in number, and
        should be compatible with the columns or expressions of other queries.
       The first query in the UNION statement can only contain the INTO clause to
        store the final result set. The INTO clause cannot be used with any query
        other than the first query while implementing the UNION operator.
       The ORDER BY and COMPUTE BY clauses cannot be used in an individual
        query. These clauses can be used only at the end of the last query to
        summarize and order the final result set.
       The GROUP BY and HAVING clauses can only be used with individual queries
        and cannot be used for the final result to be set.
       The UNION operator can also be used with the INSERT statement.

       Visit: for QTP Documents                                            6

Shared By:
Description: SQL Quarries for software database testers