《《现代数据库管理(英文版)》课件—07》由会员分享，可在线阅读，更多相关《《现代数据库管理(英文版)》课件—07（39页珍藏版）》请在金锄头文库上搜索。
1、1 1Chapter 7:Advanced SQLModern Database Management10th Edition2 2Objectivesn nDefine termsDefine termsn nWrite single and multiple table SQL queriesWrite single and multiple table SQL queriesn nDefine and use three types of joinsDefine and use three types of joinsn nWrite noncorrelated and correlat
2、ed subqueriesWrite noncorrelated and correlated subqueriesn nEstablish referential integrity in SQLEstablish referential integrity in SQLn nUnderstand triggers and stored proceduresUnderstand triggers and stored proceduresn nDiscuss SQL:200n standard and its extension of Discuss SQL:200n standard an
3、d its extension of SQL-92SQL-923 3Processing Multiple TablesJoinsn nJoina relational operation that causes two or a relational operation that causes two or more tables with a common domain to be combined more tables with a common domain to be combined into a single table or view into a single table
4、or view n nEqui-joina join in which the joining condition is a join in which the joining condition is based on equality between values in the common based on equality between values in the common columns; common columns appear redundantly in columns; common columns appear redundantly in the result t
5、ablethe result tablen nNatural joinan equi-join in which one of the an equi-join in which one of the duplicate columns is eliminated in the result tableduplicate columns is eliminated in the result tableThe common columns in joined tables are usually the primary key of the dominant table and the for
6、eign key of the dependent table in 1:M relationships4 4Processing Multiple TablesJoinsn nOuter joina join in which rows that do not a join in which rows that do not have matching values in common columns are have matching values in common columns are nonetheless included in the result table (as none
7、theless included in the result table (as opposed to opposed to innerinner join, in which rows must have join, in which rows must have matching values in order to appear in the result matching values in order to appear in the result table)table)n nUnion joinincludes all columns from each includes all
8、 columns from each table in the join, and an instance for each row of table in the join, and an instance for each row of each tableeach table5 5Figure 7-2Figure 7-2Visualization of different join types with results Visualization of different join types with results returned in shaded areareturned in
9、 shaded area6 6The following slides create tables for this enterprise data model(from Chapter 1, Figure 1-3)7 7These tables are used in queries that followFigure 7-1 Pine Valley Furniture Company Customer_T and Order_T tables with pointers from customers to their ordersEqui-Join Examplen nFor each c
10、ustomer who placed an order, what is the customers name and order number?8 8Customer ID appears twice in the result9 9n nFor each customer who placed an order, what is the For each customer who placed an order, what is the customers name and order number?customers name and order number?Join involves
11、 multiple tables in FROM clauseNatural Join ExampleON clause performs the equality check for common columns of the two tablesNote: from Fig. 7-1, you see that only 10 Customers have links with orders Only 10 rows will be returned from this INNER join1010n nList the customer name, ID number, and orde
12、r List the customer name, ID number, and order number for all customers. Include customer number for all customers. Include customer information even for customers that do have an order.information even for customers that do have an order.Outer Join Example LEFT OUTER JOIN clause causes customer dat
13、a to appear even if there is no corresponding order dataUnlike INNER join, this will include customer rows with no matching order rows1111ResultsUnlike INNER join, this will include customer rows with no matching order rows1212n nAssemble all information necessary to create an Assemble all informati
14、on necessary to create an invoice for order number 1006invoice for order number 1006Four tables involved in this joinMultiple Table Join ExampleEach pair of tables requires an equality-check condition in the WHERE clause, matching primary keys against foreign keys1313Figure 7-4 Results from a four-t
15、able join (edited for readability)From CUSTOMER_T tableFrom ORDER_T tableFrom PRODUCT_T tableSelf-Join Example1414The same table is used on both sides of the join; distinguished using table aliasesSelf-joins are usually used on tables with unary relationships1515Processing Multiple Tables Using Subq
16、ueriesn nSubqueryplacing an inner query (SELECT Subqueryplacing an inner query (SELECT statement) inside an outer querystatement) inside an outer queryn nOptions:Options:n nIn a condition of the WHERE clauseIn a condition of the WHERE clausen nAs a “table” of the FROM clauseAs a “table” of the FROM clausen nWithin the HAVING clauseWithin the HAVING clausen nSubqueries can be:Subqueries can be:n nNoncorrelatedexecuted once for the entire outer Noncorrelatedexecuted once for the entire outer query
