《ModernDatabaseManagement11thEditionJeffreyA.Hoffer,V....ppt》由会员分享，可在线阅读，更多相关《ModernDatabaseManagement11thEditionJeffreyA.Hoffer,V....ppt（45页珍藏版）》请在三一办公上搜索。
1、 2013 Pearson Education,Inc.Publishing as Prentice Hall,1,Chapter 7:advanced sql,Modern Database Management11th EditionJeffrey A.Hoffer,V.Ramesh,Heikki Topi,Define termsWrite single and multiple table SQL queriesDefine and use three types of joinsWrite noncorrelated and correlated subqueriesUndersta
2、nd and use SQL in procedural languages(e.g.PHP,PL/SQL)Understand triggers and stored proceduresDiscuss SQL:2008 standard and its enhancements and extensions,2,Objectives,Processing Multiple Tables,Joina relational operation that causes two or more tables with a common domain to be combined into a si
3、ngle table or view Equi-joina join in which the joining condition is based on equality between values in the common columns;common columns appear redundantly in the result tableNatural joinan equi-join in which one of the duplicate columns is eliminated in the result table,3,The common columns in jo
4、ined tables are usually the primary key of the dominant table and the foreign key of the dependent table in 1:M relationships.,Processing Multiple Tables,Outer joina join in which rows that do not have matching values in common columns are nonetheless included in the result table(as opposed to inner
5、 join,in which rows must have matching values in order to appear in the result table)Union joinincludes all columns from each table in the join,and an instance for each row of each table,4,5,Figure 7-2Visualization of different join types with results returned in shaded area,The following slides cre
6、ate tables for this enterprise data model,6,(from Chapter 1,Figure 1-3),7,These tables are used in queries that follow,Figure 7-1 Pine Valley Furniture Company Customer_T and Order_T tables with pointers from customers to their orders,7,Chapter 7,2013 Pearson Education,Inc.Publishing as Prentice Hal
7、l,Equi-Join Example,For each customer who placed an order,what is the customers name and order number?,8,Customer ID appears twice in the result,Equi-Join Example alternative syntax,9,INNER JOIN clause is an alternative to WHERE clause,and is used to match primary and foreign keys.An INNER join will
8、 only return rows from each table that have matching rows in the other.This query produces same results as previous equi-join example.,Natural Join Example,For each customer who placed an order,what is the customers name and order number?,10,Note:From Fig.7-1,you see that only 10 Customers have link
9、s with orders.Only 10 rows will be returned from this INNER join,OUTER JOIN EXAMPLE,List the customer name,ID number,and order number for all customers.Include customer information even for customers that do have an order.,11,Unlike INNER join,this will include customer rows with no matching order r
10、ows,12,Outer Join Results,Unlike INNER join,this will include customer rows with no matching order rows,12,Chapter 7,2013 Pearson Education,Inc.Publishing as Prentice Hall,Multiple Table Join Example,Assemble all information necessary to create an invoice for order number 1006,13,14,Figure 7-4 Resul
11、ts from a four-table join(edited for readability),Self-Join Example,15,The same table is used on both sides of the join;distinguished using table aliases,Self-joins are usually used on tables with unary relationships.,16,Figure 7-5 Example of a self-join,Processing Multiple Tables Using Subqueries,S
12、ubqueryplacing an inner query(SELECT statement)inside an outer queryOptions:In a condition of the WHERE clauseAs a“table”of the FROM clauseWithin the HAVING clauseSubqueries can be:Noncorrelatedexecuted once for the entire outer queryCorrelatedexecuted once for each row returned by the outer query,1
13、7,Subquery Example,Show all customers who have placed an order,18,The IN operator will test to see if the CUSTOMER_ID value of a row is included in the list returned from the subquery,Join vs.Subquery,Some queries could be accomplished by either a join or a subquery,19,Join version,Subquery version,
14、20,Figure 7-6 Graphical depiction of two ways to answer a query with different types of joins,21,Figure 7-6 Graphical depiction of two ways to answer a query with different types of joins,Correlated vs.Noncorrelated Subqueries,Noncorrelated subqueries:Do not depend on data from the outer queryExecut
15、e once for the entire outer queryCorrelated subqueries:Make use of data from the outer queryExecute once for each row of the outer queryCan use the EXISTS operator,22,23,Figure 7-8a Processing a noncorrelated subquery,A noncorrelated subquery processes completely before the outer query begins.,23,Ch
16、apter 7,2013 Pearson Education,Inc.Publishing as Prentice Hall,Correlated Subquery Example,Show all orders that include furniture finished in natural ash.,24,A correlated subquery always refers to an attribute from a table referenced in the outer query,25,Figure 7-8b Processing a correlated subquery
17、,Subquery refers to outer-query data,so executes once for each row of outer query,Note:Only the orders that involve products with Natural Ash will be included in the final results.,25,Chapter 7,2013 Pearson Education,Inc.Publishing as Prentice Hall,Another Subquery Example,Show all products whose st
18、andard price is higher than the average price,26,Union Queries,Combine the output(union of multiple queries)together into a single result table,27,28,Figure 7-9 Combining queries using UNION,Note:With UNION queries,the quantity and data types of the attributes in the SELECT clauses of both queries m
19、ust be identical.,28,Chapter 7,2013 Pearson Education,Inc.Publishing as Prentice Hall,Conditional Expressions Using Case Syntax,This is available with newer versions of SQL,previously not part of the standardFigure 7-10,29,Tips for Developing Queries,Be familiar with the data model(entities and rela
20、tionships)Understand the desired resultsKnow the attributes desired in resultsIdentify the entities that contain desired attributesReview ERDConstruct a WHERE equality for each linkFine tune with GROUP BY and HAVING clauses if neededConsider the effect on unusual data,30,Query Efficiency Considerati
21、ons,Instead of SELECT*,identify the specific attributes in the SELECT clause;this helps reduce network traffic of result setLimit the number of subqueries;try to make everything done in a single query if possibleIf data is to be used many times,make a separate query and store it as a view,31,Guideli
22、nes for Better Query Design,Understand how indexes are used in query processingKeep optimizer statistics up-to-dateUse compatible data types for fields and literalsWrite simple queriesBreak complex queries into multiple simple partsDont nest one query inside another queryDont combine a query with it
23、self(if possible avoid self-joins),32,Guidelines for Better Query Design(cont.),Create temporary tables for groups of queriesCombine update operationsRetrieve only the data you needDont have the DBMS sort without an indexLearn!Consider the total query processing time for ad hoc queries,33,Ensuring T
24、ransaction Integrity,Transaction=A discrete unit of work that must be completely processed or not processed at allMay involve multiple updatesIf any update fails,then all other updates must be cancelledSQL commands for transactionsBEGIN TRANSACTION/END TRANSACTIONMarks boundaries of a transactionCOM
25、MITMakes all updates permanentROLLBACKCancels updates since the last COMMIT,34,35,Figure 7-12 An SQL Transaction sequence(in pseudocode),35,Chapter 7,2013 Pearson Education,Inc.Publishing as Prentice Hall,Data Dictionary Facilities,System tables that store metadataUsers usually can view some of thes
26、e tablesUsers are restricted from updating themSome examples in Oracle 11gDBA_TABLES descriptions of tablesDBA_CONSTRAINTS description of constraintsDBA_USERS information about the users of the systemExamples in Microsoft SQL Server 2008sys.columns table and column definitionssys.indexes table index
27、 informationsys.foreign_key_columns details about columns in foreign key constraints,36,SQL:2008 Enhancements/Extensions,User-defined data types(UDT)Subclasses of standard types or an object typeAnalytical functions(for OLAP)CEILING,FLOOR,SQRT,RANK,DENSE_RANK,ROLLUP,CUBE,SAMPLE,WINDOWimproved numeri
28、cal analysis capabilitiesNew Data TypesBIGINT,MULTISET(collection),XMLCREATE TABLE LIKEcreate a new table similar to an existing oneMERGE,37,Programming extensionsPersistent Stored Modules(SQL/PSM)Capability to create and drop code modulesNew statements:CASE,IF,LOOP,FOR,WHILE,etc.Makes SQL into a pr
29、ocedural languageOracle has propriety version called PL/SQL,and Microsoft SQL Server has Transact/SQL,38,SQL:2008 Enhancements(cont),Routines and Triggers,RoutinesProgram modules that execute on demandFunctionsroutines that return values and take input parametersProceduresroutines that do not return
30、 values and can take input or output parametersTriggersroutines that execute in response to a database event(INSERT,UPDATE,or DELETE),39,40,Figure7-13 Triggers contrasted with stored procedures(based on Mullins 1995),Procedures are called explicitly,Triggers are event-driven,Source:adapted from Mull
31、ins,1995.,40,Chapter 7,2013 Pearson Education,Inc.Publishing as Prentice Hall,41,Figure 7-14 Simplified trigger syntax,SQL:2008,Figure 7-15 Syntax for creating a routine,SQL:2008,41,Chapter 7,2013 Pearson Education,Inc.Publishing as Prentice Hall,42,42,Chapter 7,2013 Pearson Education,Inc.Publishing
32、 as Prentice Hall,Embedded and Dynamic SQL,Embedded SQLIncluding hard-coded SQL statements in a program written in another language such as C or JavaDynamic SQLAbility for an application program to generate SQL code on the fly,as the application is running,43,Reasons to Embed SQL in 3GL,Can create a more flexible,accessible interface for the userPossible performance improvementDatabase security improvement;grant access only to the application instead of users,44,45,Copyright 2013 Pearson Education,Inc.Publishing as Prentice Hall,
