Database Tutorial

Objectives

There are six objectives for the tenth 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 to map columns to effect inner joins.
• Learn how to map columns by translating the relationship through other columns.
• Learn how to create runtime tables with queries.
• Learn how to create common table expressions (CTEs) with the WITH clause and queries.
• Learn how to embed a query in a SELECT statement.
• Learn how to use the INSERT statement with subqueries.

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 nature and structure of a runtime table.
• Understand the WITH clause and common table expressions (CTEs).
• Understand how to embed a query as a runtime table or common table expression (CTE).
• Teach One Another (Lab):
  • Create SELECT statements with runtime tables.
  • Create INSERT statement that use multiple row subqueries.
• Ponder and Prove (Internalization):
  • Compare and contrast methods and practices of runtime tables.
  • Compare and contrast runtime tables and common table expressions (CTEs).
  • Examine and understand how to use embedded queries to access existing data for use in new or modified tables.

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
  • Runtime Tables
• Articles
  • Mapping Joins
  • Translating Joins
• Tutorials
  • WITH Clause
  • Embedded Query
• 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. Your student images have had the necessary files placed in the proper location to support your lab assignment.

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