SQL III - 10

Card Set Information

Author:
dau108
ID:
145763
Filename:
SQL III - 10
Updated:
2012-04-05 06:22:37
Tags:
SQL III 10
Folders:

Description:
SQL III - 10
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user dau108 on FreezingBlue Flashcards. What would you like to do?


  1. USING RELATIONAL OPERATOR
    • 􏰀 Combining tables with similar structures
    • 􏰀 Combining tables with different structures
    • 􏰀 Deriving meaningful data from multiple tables
  2. UNION
    The UNION operator is the SQL implementation of relational algebra’s union operator. The UNION operator enables you to draw information from two or more tables that have the same structure. Same structure means

    • 􏰁 The tables must all have the same number of columns.
    • 􏰁 Corresponding columns must all have identical data types and lengths.

    • When these criteria are met, the tables are union compatible. The union of two tables returns all the rows that appear in either table and eliminates duplicates.
    • The UNION DISTINCT operator functions identically to the UNION operator without the DISTINCT keyword. In both cases, duplicate rows are eliminated from the result set.
  3. CORRESPONDING operation
    • SELECT *
    • FROM OUTFIELDER
    • UNION CORRESPONDING (FirstName, LastName, Putouts, Errors, FieldPct)
    • SELECT *

    FROM PITCHER ;

    • The result table holds the first and last names of all the outfielders and pitchers, along with the putouts, errors, and fielding percentage of each player. As with the simple UNION, duplicates are eliminated. Thus, if a player spent
    • some time in the outfield and also pitched in one or more games, the UNION CORRESPONDING operation loses some of his statistics. To avoid this problem, use UNION ALL CORRESPONDING.
  4. INTERSECT


  5. EXCEPT
    • SELECT *
    • FROM OUT
    • EXCEPT CORRESPONDING (PagerID)
    • SELECT *
    • FROM PAGERS;

    This query returns all the rows in the OUT table whose PagerID is not also present in the PAGERS table.
  6. BASIC JOIN
    • The result table is the Cartesian product of the two source tables

  7. EQUI-JOIN




  8. cross join
  9. natural join
    • The natural join is a special case of an equi-join. In the WHERE clause of an equi-join, a column from one source table is compared with a column of a second source table for equality. The two columns must be the same type and length and must have the same name. In fact, in a natural join, all
    • columns in one table that have the same names, types, and lengths as corresponding columns in the second table are compared for equality.
  10. condition join
  11. column-name join


  12. outer join:
    left outer join
    right outer joim
    full outer join
    • left join




    • rigt outer join:
    • I bet you figured out how the right outer join behaves. Right! The right outer join preserves unmatched rows from the right table but discards unmatched rows from the left table. You can use it on the same tables and get the same
    • result by reversing the order in which you present tables to the join:

    full outer join:

    The full outer join combines the functions of the left outer join and the right outer join. It retains the unmatched rows from both the left and the right tables. Consider the most general case of the company database used in the preceding examples. It could have
  13. union join









    • This data arrangement is not particularly enlightening. The employee ID numbers appear three times, and the projects and skills are duplicated for each employee. The inner joins are not well suited to answering this type of question. You can put the union join to work here, along with some strategically chosen SELECT statements, to produce a more suitable result. You begin with
    • the basic union join:

    • SELECT *
    • FROM EMPLOYEE E UNION JOIN PROJECTS P
    • UNION JOIN SKILLS S ;

    Notice that the union join has no ON clause. It doesn’t filter the data, so an ON clause isn’t needed. This statement produces the result shown in Table 10-8

What would you like to do?

Home > Flashcards > Print Preview