chapter 8 - CGS 2545 Database Concepts Spring 2010 Chapter...

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

View Full Document Right Arrow Icon
CGS 2545: Database Concepts (Chapter 8) Page 1 Mark Llewellyn CGS 2545: Database Concepts Spring 2010 Chapter 8 – Advanced SQL 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/cgs2545/spr2010
Background image of page 1

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

View Full DocumentRight Arrow Icon
CGS 2545: Database Concepts (Chapter 8) Page 2 Mark Llewellyn Objectives Definition of terms. Write multiple table SQL queries Define and use three types of joins Write correlated and noncorrelated subqueries Establish referential integrity in SQL Understand triggers and stored procedures Discuss SQL:2003 enhancements and extensions
Background image of page 2
CGS 2545: Database Concepts (Chapter 8) Page 3 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 3

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

View Full DocumentRight Arrow Icon
CGS 2545: Database Concepts (Chapter 8) Page 4 Mark Llewellyn The following slides create tables for this enterprise data model The last four pages of this set of notes contain screen shots for these for tables from a sample database using these tables. They might make some of the following examples more clear.
Background image of page 4
CGS 2545: Database Concepts (Chapter 8) Page 5 Mark Llewellyn These tables are used in queries that follow
Background image of page 5

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

View Full DocumentRight Arrow Icon
CGS 2545: Database Concepts (Chapter 8) Page 6 Mark Llewellyn For each customer who placed an order, what is the customer’s id, 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 6
CGS 2545: Database Concepts (Chapter 8) Page 7 Mark Llewellyn SQL query entered in Access and executed showing results.
Background image of page 7

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

View Full DocumentRight Arrow Icon
CGS 2545: Database Concepts (Chapter 8) Page 8 Mark Llewellyn List the customer name, ID number, and order number for all customers. Include customer information even for customers that do not have an order.
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 10/05/2010 for the course CGS 2545C CGS 2545c taught by Professor L during the Spring '10 term at University of Central Florida.

Page1 / 36

chapter 8 - CGS 2545 Database Concepts Spring 2010 Chapter...

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