02010_ch08

02010_ch08 - Database Systems Design Implementation and...

Info iconThis preview shows pages 1–15. Sign up to view the full content.

View Full Document Right Arrow Icon
Database Systems: Design, Implementation, and Management Eighth Edition Chapter 8 Advanced SQL
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
th 2 Objectives In this chapter, you will learn: About the relational set operators UNION, UNION ALL, INTERSECT, and MINUS How to use the advanced SQL JOIN operator syntax About the different types of subqueries and correlated queries How to use SQL functions to manipulate dates, strings, and other data
Background image of page 2
th 3 Objectives (continued) In this chapter, you will learn: (continued) How to create and use updatable views How to create and use triggers and stored procedures How to create embedded SQL
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
th 4 Relational Set Operators UNION INTERSECT MINUS Work properly if relations are union- compatible Names of relation attributes must be the same and their data types must be identical
Background image of page 4
th 5 UNION Combines rows from two or more queries without including duplicate rows Example: SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, FROM CUSTOMER UNION SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, FROM CUSTOMER_2
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
th 6 UNION ALL Produces a relation that retains duplicate rows Example query: SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, FROM CUSTOMER UNION ALL SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, FROM CUSTOMER_2; Can be used to unite more than two queries
Background image of page 6
th 7 Intersect Combines rows from two queries, returning only the rows that appear in both sets Syntax: query INTERSECT query Example query: SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, FROM CUSTOMER INTERSECT SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, FROM CUSTOMER_2
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
th 8
Background image of page 8
th 9 Minus Combines rows from two queries Returns only the rows that appear in the first set but not in the second Syntax: query MINUS query Example: SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, FROM CUSTOMER MINUS SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, FROM CUSTOMER_2
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
th 10 Syntax Alternatives IN and NOT IN subqueries can be used in place of INTERSECT Example: SELECT CUS_CODE FROM CUSTOMER WHERE CUS_AREACODE = ‘615’ AND CUS_CODE IN (SELECT DISTINCT CUS_CODE FROM INVOICE);
Background image of page 10
th 11 SQL Join Operators Join operation merges rows from two tables and returns the rows with one of the following: Have common values in common columns Natural join Meet a given join condition Equality or inequality Have common values in common columns or have no matching values Outer join Inner join : only return rows meeting criteria
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
th 12 Cross Join Performs relational product of two tables Also called Cartesian product Syntax: SELECT column-list FROM table1 CROSS JOIN table2 Perform a cross join that yields specified attributes
Background image of page 12
th 13 Natural Join Returns all rows with matching values in the matching columns Eliminates duplicate columns Used when tables share one or more common attributes with common names Syntax: SELECT column-list FROM table1 NATURAL JOIN table2
Background image of page 13

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
th 14
Background image of page 14
Image of page 15
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 50

02010_ch08 - Database Systems Design Implementation and...

This preview shows document pages 1 - 15. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online