Chapter 8 Multiple Table Queries and Pratt Chapter 4

1
Chapter 8 Multiple Table Queries and Pratt
Chapter 4

• By
• Tony Baker
• Shancona Cranford
• Josh Currier
• Vanessa Haggans

2
Chapter 8-Advanced SQL

• The most frequently used relational operation,
  which brings together data from two or more
  related tables into one resultant table, is
  called a join.

3
SQL

• SQL specifies a join implicitly by referring in a
  WHERE clause to the matching of common clause of
  the SELECT command is also used for multiple
  table operations. SELECT command is also used
  for multiple table operations. An important rule
  of thumb in forming join conditions is the
  following There should be one condition within
  the WHERE clause for each pair of tables being
  joined.

4
Types of Joins

• Equi-join- is a join in which the joining
  condition is based on equality between values in
  the common columns. Common columns appear
  (redundantly) in the result table.
• Natural join- same as equi-join except one of the
  duplicate columns is eliminated in the result
  table.

5
Types of Joins

• Outer join- a join in which rows that do not have
  matching values in common columns are
  nevertheless included in the result table.
• Union join- a join that is not implemented in all
  DBMS products yet its results will be a table
  that includes all of the data from each table
  that is joined

6
Subquery Technique

• Besides the joining technique, SQL also provides
  the subquery technique, which involves placing an
  inner query (SELECT, FROM, WHERE) within a WHERE
  or HAVING clause of another (outer) query.
  Sometimes either techniques may be used to
  accomplish the same result.

7
Subqueries

• Nested subqueries-where multiple SELECT
  statements are nested within a single query, are
  useful for more complex query situations.
• Correlated subqueries-a special form of the
  subquery, which requires that a value be known
  from the outer query before the inner query can
  be processed.
• Other subqueries-process the inner query, return
  a result to the next outer query, and then that
  outer query is processed.

8
Transition Integrity

• A transition is the complete set of closely
  related update commands that must all be done, or
  none of them done, for the database to remain
  valid.

9
Data Dictionary Facilities

• RDBMSs store database definition information in
  system-created tables we can consider these
  system tables as a data dictionary. Becoming
  familiar with the systems tables for any RDBMS
  being used will provide valuable information,
  whether you are a user or a database
  administrator.

10
Triggers

• Triggers and routines are very powerful database
  objects because they are stored in the database
  and controlled by the DBMS. Both triggers and
  routines consist of blocks of procedural code.
  Trigger code is stored in the database and runs
  automatically whenever the triggering event, such
  as an UPDATE, occurs.

11
Routines

• SQL-invoked routines can be either procedures or
  functions. The terms procedures and function are
  used in the same manner as they are in other
  programming languages. A function returns one
  value and has only input parameters. A procedure
  may have input parameters, output parameters, and
  parameters that are both input and output
  parameters

12
Querying Multiple Tables

• One common way to retrieve data from more than
  one table is to join the tables together by
  finding rows in the tables that have identical
  values in matching columns.

• To join tables you must
• Indicate in the SELECT clause all columns to
  display.
• List in the FROM clause all tables involved in
  the query.
• Give condition(s) in the WHERE clause to restrict
  the data to be retrieved to only those rows that
  have common values in matching columns.

13
SQL SELECT CUSTOMER_NUMBER, CUSTOMER_LAST,
CUSTOMER.FIRST, 2 SALES_REP.SLSREP_NUMBER,
SALES_REP.LAST, SALES_REP.FIRST 3 FROM
CUSTOMER, SALES_REP 4 WHERE
CUSTOMER.SLSREP_NUMBER SALES_REP.SLSREP_ CUS
LAST FIRST SL LAST FIRST -----
---------- -------- ----- ----------
----------- 124 Adams Sally 03 Jones
Mary 412 Adams Sally 03
Jones Mary 622 Martin Dan 03
Jones Mary 256 Samuels Ann 06
Smith William 315 Daniels Tom 06
Smith William 567 Dinh Tran
06 Smith William 587 Galvez Mara
06 Smith William 311 Charles Don
12 Diaz Miguel 405 Williams Al
12 Diaz Miguel 522 Nelson
Mary 12 Diaz Miguel 10 rows
selected.
14
Find the order number and order date for every
order that contains Number BT04?
Using IN to select order information
SQL SELECT ORDER_NUMBER, ORDER_DATE 2
FROM ORDERS 3 WHERE ORDER_NUMBER IN
4 ( SELECT ORDER_NUMBER 5 FROM
ORDER_LINE 6 WHERE PART_NUMBER
BT04)

• ORDER ORDER_DAT
• ----------- -------------------
• 02-SEP-02
• 12500 05-SEP-02

15
Using EXISTS to select order information
SQL SELECT ORDER_NUMBER, ORDER_DATE 2
FROM ORDERS 3 WHERE EXISTS 4 (
SELECT 5 FROM ORDER_LINE 6
WHERE ORDERS.ORDER_NUMBER ORDER_LINE.ORDER_NUMBE
R 7 AND PART_NUMBER BT04)

• ORDER ORDER_DAT
• ----------- -------------------
• 02-SEP-02
• 12500 05-SEP-02

Using EXISTS operator provides another approach
to the problem.
16
Joining three tables
SQL SELECT ORDERS.ORDER_NUMBER, ORDER_DATE
2 FROM ORDER_LINE, ORDERS, PART 3
WHERE ORDER-LINE.ORDER_NUMBER
ORDERS.ORDER_NUMBER 4 AND
ORDER_LINE.PART_NUMBER PART.PART_NUMBER 5
AND WAREHOUSE_NUMBER 3

• ORDER ORDER_DAT
• ----------- -------------------
• 02-SEP-02
• 05-SEP-02
• 12495 04-SEP-02

17
Using an Alias

• An alias is an alternative name given to a
  certain word such as S for SALES_REP or C for
  CUSTOMER.
• It is useful for two reasons First for
  simplicity, and second for ease in joining tables.

18
Joining a Table to Itself

• An example of this is when you need to find every
  pair of customers with the same first and last
  name.
• To do this you must change the FROM clause
• FROM CUSTOMER F, CUSTOMER S
• This is treated by SQL as a Query from two tables.

19
Set Operations

• Consists of three normal operations Union,
  Intersection, and Difference
• The Union of two tables is a table containing
  every row that is in either the first table or
  the second table, or both.
• The intersection (intersect) of two tables is a
  table containing every row that is in both
  tables.
• The Difference (minus) of two tables is the set
  of every row that is in the first table but not
  in the second table.

20
ALL and ANY

• You can use the ALL and ANY operators with
  subqueries to produce a single column of numbers.
• The ALL command only produces numbers that
  satisfy all values of the subquery.
• The ANY command produces numbers that satisfy any
  value of the subquery.

21
Tonyas Question

• What is the UNION clause used for?

22
Vanessas Question

• What are the advantages of SQL-invoked routines?

23
SHANCONAS QUESTION
How and why would a person give an alias to a
MVCH table?
24
Joshs Question

• What is the difference between the ALL and ANY
  commands?