Sams Teach Yourself Oracle® PL/SQL in 10 Minutes
Ben Forta
Sams TeachYourself
Oracle?
PL /SQL
in 10 Minutes
800 East 96th Street, Indianapolis, Indiana 46240
Sams Teach Yourself Oracle? PL/SQL in 10 Minutes Copyright ? 2016 by Pearson Education, Inc.
All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.
ISBN-13: 978-0-672-32866-4 ISBN-10: 0-672-32866-6
Library of Congress Control Number: 2015910491
Printed in the United States of America
First Printing September 2015
Trademarks All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Sams Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark.
Warning and Disclaimer Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an "as is" basis. The author and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book or from the use of the programs accompanying it.
Special Sales For information about buying this title in bulk quantities, or for special sales opportunities (which may include electronic versions; custom cover designs; and content particular to your business, training goals, marketing focus, or branding interests), please contact our corporate sales department at corpsales@pearsoned. com or (800) 382-3419.
For government sales inquiries, please contact governmentsales@ .
For questions about sales outside the U.S., please contact international@.
Acquisitions Editor Mark Taber
Managing Editor Kristy Hart
Senior Project Editor Betsy Gratner
Copy Editor Paula Lowell
Indexer Lisa Stumpf
Proofreader Sarah Kearns
Editorial Assistant Vanessa Evans
Cover Designer Mark Shirar
Senior Compositor Gloria Schurick
Contents
Introduction
1
What Is This Book? ................................................................. 1
Who Is This Book For?............................................................. 2
Companion Website ................................................................ 3
Conventions Used in This Book ................................................ 3
1 Understanding SQL
5
Database Basics..................................................................... 5
What Is SQL?........................................................................ 10
Try It Yourself........................................................................ 11
Summary .............................................................................. 12
2 Getting Started with Oracle and PL/SQL
13
What Is Oracle? .................................................................... 13
Getting Set Up ...................................................................... 16
Summary .............................................................................. 20
3 Working with Oracle
21
Creating a Working Environment ............................................. 21
Making the Connection .......................................................... 25
A Quick Introduction to Oracle SQL Developer ......................... 27
Creating and Populating the Example Tables ........................... 28
One More Look at Oracle SQL Developer ................................ 32
Summary .............................................................................. 32
iv
Sams Teach Yourself Oracle PL/SQL in 10 Minutes
4 Retrieving Data
33
The SELECT Statement .......................................................... 33
Retrieving Individual Columns................................................. 33
Retrieving Multiple Columns................................................... 36
Retrieving All Columns ........................................................... 37
Retrieving Distinct Rows ........................................................ 38
Using Fully Qualified Table Names.......................................... 40
Using Comments................................................................... 40
Summary .............................................................................. 42
5 Sorting Retrieved Data
43
Sorting Data ......................................................................... 43
Sorting by Multiple Columns................................................... 45
Specifying Sort Direction........................................................ 47
Summary .............................................................................. 49
6 Filtering Data
51
Using the WHERE Clause......................................................... 51
The WHERE Clause Operators.................................................. 53
Summary .............................................................................. 59
7 Advanced Data Filtering
61
Combining WHERE Clauses...................................................... 61
Using the IN Operator ........................................................... 65
Using the NOT Operator.......................................................... 67
Summary .............................................................................. 68
Contents
v
8 Using Wildcard Filtering
69
Using the LIKE Operator........................................................ 69
Tips for Using Wildcards ........................................................ 74
Summary .............................................................................. 74
9 Searching Using Regular Expressions
75
Understanding Regular Expressions........................................ 75
Using Oracle PL/SQL Regular Expressions .............................. 76
Summary .............................................................................. 89
10 Creating Calculated Fields
91
Understanding Calculated Fields............................................. 91
Concatenating Fields ............................................................. 92
Performing Mathematical Calculations .................................... 96
Summary .............................................................................. 98
11 Using Data Manipulation Functions
99
Understanding Functions ....................................................... 99
Using Functions .................................................................. 100
Summary ............................................................................ 107
12 Summarizing Data
109
Using Aggregate Functions ................................................... 109
Aggregates on Distinct Values.............................................. 117
Combining Aggregate Functions............................................ 118
Summary ............................................................................ 119
vi
Sams Teach Yourself Oracle PL/SQL in 10 Minutes
13 Grouping Data
121
Understanding Data Grouping............................................... 121
Creating Groups .................................................................. 122
Filtering Groups................................................................... 123
Grouping and Sorting........................................................... 126
SELECT Clause Ordering....................................................... 129
Summary ............................................................................ 129
14 Working with Subqueries
131
Understanding Subqueries ................................................... 131
Filtering by Subquery ........................................................... 131
Using Subqueries as Calculated Fields ................................. 136
Summary ............................................................................ 140
15 Joining Tables
141
Understanding Joins ............................................................ 141
Creating a Join.................................................................... 144
Summary ............................................................................ 152
16 Creating Advanced Joins
153
Using Table Aliases ............................................................. 153
Using Different Join Types.................................................... 154
Using Joins with Aggregate Functions ................................... 160
Using Joins and Join Conditions ........................................... 161
Summary ............................................................................ 162
Contents
vii
17 Combining Queries
163
Understanding Combined Queries......................................... 163
Creating Combined Queries.................................................. 164
Summary ............................................................................ 169
18 Inserting Data
171
Understanding Data Insertion............................................... 171
Inserting Complete Rows ..................................................... 172
Inserting Retrieved Data ...................................................... 176
Summary ............................................................................ 178
19 Updating and Deleting Data
179
Updating Data..................................................................... 179
Deleting Data...................................................................... 181
Guidelines for Updating and Deleting Data ............................ 183
Summary ............................................................................ 184
20 Creating and Manipulating Tables
185
Creating Tables ................................................................... 185
Updating Tables .................................................................. 190
Deleting Tables ................................................................... 193
Renaming Tables ................................................................ 193
Summary ............................................................................ 194
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- sams teach yourself oracle pl sql in 10 minutes
- doing sql from pl sql best and worst practices
- oracle xml db best practices to get optimal performance
- database programming with pl sql
- migrating non oracle databases and their applications to
- database system security
- cs405 database programming using oracle 11g
- mastering oracle pl sql practical solutions
- chapter 8 advanced sql
- 60 415 assignment 2 solution sql ddl and pl sql total