chapter 5 - part 2 - COP 4710 Database Systems Fall 2007...

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

View Full Document Right Arrow Icon
COP 4710: Database Systems (Chapter 5) Page 1 Mark Llewellyn COP 4710: Database Systems Fall 2007 Chapter 5 – Introduction To SQL – Part 2 School of Electrical Engineering and Computer Science University of Central Florida Instructor : Dr. Mark Llewellyn [email protected] HEC 236, 407-823-2790 http://www.cs.ucf.edu/courses/ccop4710/fall2007
Background image of page 1

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

View Full DocumentRight Arrow Icon
COP 4710: Database Systems (Chapter 5) Page 2 Mark Llewellyn Processing Multiple Tables – Joins • Join – a relational operation that causes two or more tables with a common domain to be combined into a single table or view • Equi-join – 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 – an equi-join in which one of the duplicate columns is eliminated in the result table • Outer join – a join in which rows that do not have matching values in common columns are nonetheless included in the result table (as opposed to inner join, in which rows must have matching values in order to appear in the result table) • Union join – includes all columns from each table in the join, and an instance for each row of each table The common columns in joined tables are usually the primary key of the dominant table and the foreign key of the dependent table in 1:M relationships
Background image of page 2
COP 4710: Database Systems (Chapter 5) Page 3 Mark Llewellyn The following slides create tables for this enterprise data model
Background image of page 3

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

View Full DocumentRight Arrow Icon
COP 4710: Database Systems (Chapter 5) Page 4 Mark Llewellyn These tables are used in queries that follow
Background image of page 4
COP 4710: Database Systems (Chapter 5) Page 5 Mark Llewellyn • For each customer who placed an order, what is the customer’s name and order number? SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM CUSTOMER_T, ORDER_T WHERE CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID; Join involves multiple tables in FROM clause Natural Join Example WHERE clause performs the equality check for common columns of the two tables
Background image of page 5

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

View Full DocumentRight Arrow Icon
COP 4710: Database Systems (Chapter 5) Page 6 Mark Llewellyn Results
Background image of page 6
COP 4710: Database Systems (Chapter 5) Page 7 Mark Llewellyn • List the customer name, ID number, and order number for all customers. Include customer information even for customers that do have an order SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM CUSTOMER_T, LEFT OUTER JOIN ORDER_T ON CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID; Outer Join Example (Microsoft Syntax) LEFT OUTER JOIN syntax with ON keyword instead of WHERE Æ causes customer data to appear even if there is no corresponding order data
Background image of page 7

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

View Full DocumentRight Arrow Icon
COP 4710: Database Systems (Chapter 5) Page 8 Mark Llewellyn • List the customer name, ID number, and order number for all customers. Include customer information even for customers that do have an order SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM CUSTOMER_T, ORDER_T WHERE CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID(+); Outer Join Example (Oracle Syntax) Outer join in Oracle uses regular join syntax, but adds (+) symbol to the side that will have the missing data
Background image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 02/22/2009 for the course COP 4710 taught by Professor Dr. mark llewellyn during the Fall '07 term at University of Central Florida.

Page1 / 55

chapter 5 - part 2 - COP 4710 Database Systems Fall 2007...

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

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