Database Tutorial

Objectives

There are six objectives for the fifth week. The best way to accomplish these objectives is two fold. First, you need to read the articles and web pages referenced through links in the summary page. This also includes any subordinate pages referenced through links. Second, you need to do the work and use the tools to copy existing scripts, edit scripts, and test scripts.

• Learn how two table joins work.
• Learn how to perform cross joins.
• Learn how to perform inner joins.
• Learn how to perform outer joins.
• Learn how to perform self joins between two or more copies of a single table.
• Learn how to perform filtered non-equijoins.

Reading Assignments

You should prepare by reading the following articles. There will be many new terms. You may want to check the Glossary in the Oracle Database 12c PL/SQL Programming book to understand most of those new terms. After your preparation, you should work through the examples provided for you in the reading.

• Prepare (Reading):
• Understand the logic that supports various join patterns.
• Understand how to use cases support two join patterns:
  • ANSI 89 SQL Joins: Placing the joining clause in the WHERE clause.
  • ANSI 92 SQL Joins: Placing the joining clauses in the ON or USING subclause of the FROM clause.
• Teach One Another (Lab):
  • Copy, edit, and test the SQL test script.
  • Explore develop different scripts for different use case scenarios.
• Ponder and Prove (Internalization):
  • Compare and contrast the insertion use cases.
  • Examine and understand the insertion script variations.

Reference Materials

After you’ve read the article and tutorials, you should experiment with the examples provided in the article or tutorials. The second week’s lab starts with copying, editing, and testing unit and integration testing scripts:

• Reading
  • SQL Joins
• Articles
  • Two Table Joins
    • Cross Join
    • Inner Join
    • Natural Join
    • Left Join
    • Right Join
    • Full Join
    • Self Join
    • Non-equijoin Join
  • Three Table Joins
    • Inner Join
    • Self Join
• Tutorials
  • Discover Relations
• Lab
  • Description
  • Help Section
  • Instructions

The lab relies on your ability to navigate in the SQL*Plus command-line interface and the ability to edit a script file.

You should read through the Quiz 5: Review Notes to ensure you understand Week 5 material before moving on to Week 6. Please feel free to identify and comment on anything that would benefit form different wording or supplemental ideas.