Oracle PL/SQL in 10 Minutes, Sams Teach Yourself by Forta, Ben -- Read -- Imperial Library of Trantor

Index

About This eBook Title Page Copyright Page Contents About the Author Acknowledgments We Want to Hear from You! Reader Services Introduction

What Is This Book? Who Is This Book For? Companion Website Conventions Used in This Book

Lesson 1. Understanding SQL

Database Basics

What Is a Database? Tables Columns and Datatypes Rows Primary Keys

What Is SQL? Try It Yourself Summary

Lesson 2. Getting Started with Oracle and PL/SQL

What Is Oracle?

Client-Server Software PL/SQL Client Tools

Getting Set Up

What Software Do You Need? Obtaining the Software Installing the Software

Summary

Lesson 3. Working with Oracle

Creating a Working Environment

Creating a Dedicated Oracle Instance Creating a Custom Workspace

Making the Connection A Quick Introduction to Oracle SQL Developer Creating and Populating the Example Tables

Obtaining the Same Table Scripts Create the Tables Populate the Tables

One More Look at Oracle SQL Developer Summary

Lesson 4. Retrieving Data

The SELECT Statement Retrieving Individual Columns Retrieving Multiple Columns Retrieving All Columns Retrieving Distinct Rows Using Fully Qualified Table Names Using Comments Summary

Lesson 5. Sorting Retrieved Data

Sorting Data Sorting by Multiple Columns Specifying Sort Direction Summary

Lesson 6. Filtering Data

Using the WHERE Clause The WHERE Clause Operators

Checking Against a Single Value Checking for Nonmatches Checking for a Range of Values Checking for No Value

Summary

Lesson 7. Advanced Data Filtering

Combining WHERE Clauses

Using the AND Operator Using the OR Operator Understanding Order of Evaluation

Using the IN Operator Using the NOT Operator Summary

Lesson 8. Using Wildcard Filtering

Using the LIKE Operator

Searching with the Percent Sign (%) Wildcard Searching with the Underscore (_) Wildcard

Tips for Using Wildcards Summary

Lesson 9. Searching Using Regular Expressions

Understanding Regular Expressions Using Oracle PL/SQL Regular Expressions

Basic Character Matching Performing OR Matches Matching One of Several Characters Matching Ranges Matching Special Characters Matching Character Classes Matching Multiple Instances Anchors

Summary

Lesson 10. Creating Calculated Fields

Understanding Calculated Fields Concatenating Fields

Using Aliases

Performing Mathematical Calculations Summary

Lesson 11. Using Data Manipulation Functions

Understanding Functions Using Functions

Text Manipulation Functions Date and Time Manipulation Functions Numeric Manipulation Functions

Summary

Lesson 12. Summarizing Data

Using Aggregate Functions

The AVG() Function The COUNT() Function The MAX() Function The MIN() Function The SUM() Function

Aggregates on Distinct Values Combining Aggregate Functions Summary

Lesson 13. Grouping Data

Understanding Data Grouping Creating Groups Filtering Groups Grouping and Sorting SELECT Clause Ordering Summary

Lesson 14. Working with Subqueries

Understanding Subqueries Filtering by Subquery Using Subqueries as Calculated Fields Summary

Lesson 15. Joining Tables

Understanding Joins

Understanding Relational Tables Why Use Joins?

Creating a Join

The Importance of the WHERE Clause Using Inner Joins Joining Multiple Tables

Summary

Lesson 16. Creating Advanced Joins

Using Table Aliases Using Different Join Types

Self Joins Natural Joins Outer Joins

Using Joins with Aggregate Functions Using Joins and Join Conditions Summary

Lesson 17. Combining Queries

Understanding Combined Queries Creating Combined Queries

Using UNION UNION Rules Including or Eliminating Duplicate Rows Sorting Combined Query Results

Summary

Lesson 18. Inserting Data

Understanding Data Insertion Inserting Complete Rows Inserting Retrieved Data Summary

Lesson 19. Updating and Deleting Data

Updating Data Deleting Data Guidelines for Updating and Deleting Data Summary

Lesson 20. Creating and Manipulating Tables

Creating Tables

The Basics of Table Creation Working with NULL Values Specifying Default Values

Updating Tables

Primary Keys Revisited Defining Foreign Keys

Deleting Tables Renaming Tables Summary

Lesson 21. Using Views

Understanding Views

Why Use Views View Rules and Restrictions

Using Views

Using Views to Simplify Complex Joins Using Views to Reformat Retrieved Data Using Views to Filter Unwanted Data Using Views with Calculated Fields Updating Views

Summary

Lesson 22. Working with Stored Procedures

Understanding Stored Procedures Why Use Stored Procedures Using Stored Procedures

Basic Stored Procedure Syntax Using Programming Constructs in Stored Procedures Building Intelligent Stored Procedures Dropping Stored Procedures

Summary

Lesson 23. Using Cursors

Understanding Cursors Working with Cursors

Creating Cursors Opening and Closing Cursors Fetching Cursor Data Using Cursor Data

Summary

Lesson 24. Using Triggers

Understanding Triggers Creating Triggers Dropping Triggers Using Triggers

INSERT Triggers DELETE Triggers UPDATE Triggers Multi-Event Triggers More on Triggers

Summary

Lesson 25. Managing Transaction Processing

Understanding Transaction Processing Controlling Transactions

Using ROLLBACK Using COMMIT Using Savepoints

Summary

Lesson 26. Managing Security

Understanding Access Control Managing Users

Creating User Accounts Deleting User Accounts Setting Access Rights Changing Passwords

Summary

Appendix A. The Example Tables

Understanding the Sample Tables

The vendors Table The products Table The customers Table The orders Table The orderitems Table The productnotes Table

Appendix B. Oracle PL/SQL Datatypes

String Datatypes Numeric Datatypes

Date and Time Datatypes Binary Datatypes

Appendix C. Oracle PL/SQL Reserved Words and Keywords Index Code Snippets

← Prev
Back
Next →

← Prev
Back
Next →