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.

Google Online Preview   Download