This Book is Available on YakiBooki.

com

https://www.yakibooki.com/download/oracle-12c-sql-3rd-edition/
 5 REASONS
to buy your textbooks
and course materials at

SAVINGS:
1 Prices up to 75% off, daily coupons, and free shipping on orders over $25

CHOICE:
2 Multiple format options including textbook, eBook and eChapter rentals

CONVENIENCE:
3 Anytime, anywhere access of eBooks or eChapters via mobile devices

SERVICE:
4 Free eBook access while your text ships, and instant access to online homework products

STUDY TOOLS:
5 Study tools* for your text, plus writing, research, career and job search resources
* availability varies

Find your course materials and start saving at:

www.cengagebrain.com
Source Code: 14M-AA0107

Engaged with you.

www.cengage.com
Copyright 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
 This Book is Available on YakiBooki.com

ORACLE® 12c: SQL

Joan Casteel

Australia • Brazil • Mexico • Singapore • United Kingdom • United States

Copyright 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
 This Book is Available on YakiBooki.com

This is an electronic version of the print textbook. Due to electronic rights restrictions, some third party content may be suppressed. Editorial
review has deemed that any suppressed content does not materially affect the overall learning experience. The publisher reserves the right to
remove content from this title at any time if subsequent rights restrictions require it. For valuable information on pricing, previous
editions, changes to current editions, and alternate formats, please visit www.cengage.com/highered to search by
ISBN#, author, title, or keyword for materials in your areas of interest.

Important Notice: Media content referenced within the product description or the product text may not be available in the eBook version.

Copyright 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
 This Book is Available on YakiBooki.com
ORACLE® 12c: SQL © 2016, 2010 Cengage Learning
Joan Casteel
WCN: 02-200-203
Vice President, General Manager, Science, ALL RIGHTS RESERVED. No part of this work covered by the
Math & Quantitative Business: Balraj Kalsi copyright herein may be reproduced, transmitted, stored, or used in any
Product Director: Joe Sabatino form or by any means graphic, electronic, or mechanical, including but not
limited to photocopying, recording, scanning, digitizing, taping, Web
Product Manager: Jason Guyler
distribution, information networks, or information storage and retrieval
Content Developer: Lori Bradshaw, S4 Carlisle systems, except as permitted under Section 107 or 108 of the 1976 United
Senior Product Assistant: Brad Sullender States Copyright Act, without the prior written permission of the publisher.
Senior Marketing Manager: Eric La Scola
For product information and technology assistance, contact us at
Marketing Coordinator: William Guiliani Cengage Learning Customer & Sales Support, 1-800-354-9706
Art and Cover Direction, Production For permission to use material from this text or product,
Management, and Composition: submit all requests online at www.cengage.com/permissions
Lumina Datamatics, Inc. Further permissions questions can be emailed to
Intellectual Property permissionrequest@cengage.com

Analyst: Christina Ciaramella

Oracle is a registered trademark, and Oracle 12c, SQL Developer, and
Project Manager: Kathryn Kucharek
SQL*Plus are trademarks or registered trademarks of Oracle Corporation
Manufacturing Planner: Ron Montgomery and/or its affiliates.
Cover Image: © Chris Clor/Blend Images/Corbis Some of the product names and company names used in this book have
been used for identification purposes only and may be trademarks or
registered trademarks of their respective manufacturers and sellers.
Information pertaining to Northwest Airlines was used with their express
permission. No part of it may be reproduced or used in any form without
prior written permission from Cengage Learning.
Library of Congress Control Number: 2015942548
ISBN: 978-1-305-25103-8

Cengage Learning
20 Channel Center Street
Boston, MA 02210
USA

Cengage Learning is a leading provider of customized learning solutions

with employees residing in nearly 40 different countries and sales in more
than 125 countries around the world. Find your local representative at
www.cengage.com

Cengage Learning products are represented in Canada by

Nelson Education, Ltd.

To learn more about Cengage Learning Solutions, visit www.cengage.com

Purchase any of our products at your local college store or at our
preferred online store www.cengagebrain.com

Printed in the United States of America

Print Number: 01 Print Year: 2015

Copyright 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
 This Book is Available on YakiBooki.com
To Scott, a true teacher—one who never stops learning

Copyright 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
 This Book is Available on YakiBooki.com
BRIEF CONTENTS

Preface xiv
Chapter 1 Overview of Database Concepts 1
Chapter 2 Basic SQL SELECT Statements 25
Chapter 3 Table Creation and Management 57
Chapter 4 Constraints 103
Chapter 5 Data Manipulation and Transaction Control 141
Chapter 6 Additional Database Objects 181
Chapter 7 User Creation and Management 225
Chapter 8 Restricting Rows and Sorting Data 255
Chapter 9 Joining Data from Multiple Tables 295
Chapter 10 Selected Single-Row Functions 347
Chapter 11 Group Functions 401
Chapter 12 Subqueries and MERGE Statements 449
Chapter 13 Views 495
Appendix A Tables for the JustLee Books Database 539
Appendix B SQL*Plus and SQL Developer Overview 547
Appendix C Oracle Resources 555
Appendix D SQL*Loader 557
Appendix E SQL Tuning Topics 561
Appendix F SQL in Various Databases 579
Glossary 583
Index 589

Copyright 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
 This Book is Available on YakiBooki.com
TABLE OF CONTENTS

Preface xiv
Chapter 1 Overview of Database Concepts 1
Introduction 2
Database Terminology 2
Database Management System 3
Database Design 4
Entity-Relationship (E-R) Model 5
Database Normalization 6
Relating Tables in the Database 10
Structured Query Language (SQL) 12
Databases Used in This Textbook 13
Basic Assumptions 13
Tables in the JustLee Books Database 14
Topic Sequence 16
Software Used in This Textbook 16
Chapter Summary 17
Review Questions 17
Multiple Choice 18
Hands-On Assignments 21
Advanced Challenge 22
Case Study: City Jail 22

Chapter 2 Basic SQL SELECT Statements 25

Introduction 26
Creating the JustLee Books Database 27
SELECT Statement Syntax 30
Selecting All Data in a Table 31
Selecting One Column from a Table 33
Selecting Multiple Columns from a Table 34
Operations in the SELECT Statement 36
Using Column Aliases 36
Using Arithmetic Operations 39
NULL Values 40
Using DISTINCT and UNIQUE 42
Using Concatenation 44
Chapter Summary 49
Chapter 2 Syntax Summary 49

Copyright 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
 Review Questions 50
Multiple Choice 51
This Book is Available on YakiBooki.com
Hands-On Assignments 54
Advanced Challenge 55
Case Study: City Jail 55

Chapter 3 Table Creation and Management 57

Introduction 58
Table Design 59
Table Creation 63
Defining Columns 63
Viewing a List of Tables: USER_TABLES 65
Viewing Table Structures: DESCRIBE 66
Table Creation with Subqueries 70
CREATE TABLE … AS Command 70
Modifying Existing Tables 72
ALTER TABLE … ADD Command 73
ALTER TABLE … MODIFY Command 74
ALTER TABLE … DROP COLUMN Command 79
ALTER TABLE … SET UNUSED/DROP UNUSED COLUMNS Command 80
Renaming a Table 83
Truncating a Table 85
Deleting a Table 86
Chapter Summary 91
Chapter 3 Syntax Summary 92
Review Questions 93
Multiple Choice 94
Hands-On Assignments 97
Advanced Challenge 97
Case Study: City Jail 98

Chapter 4 Constraints 103

Introduction 104
Creating Constraints 105
Creating Constraints at the Column Level 106
Creating Constraints at the Table Level 106
Using the PRIMARY KEY Constraint 107
Using the FOREIGN KEY Constraint 110
Using the UNIQUE Constraint 115
Using the CHECK Constraint 116
Using the NOT NULL Constraint 119
Including Constraints During Table Creation 121
Adding Multiple Constraints on a Single Column 125
Viewing Constraint Information 126

vi Table of Contents

Copyright 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
 Disabling and Dropping Constraints 128
Using DISABLE/ENABLE 128
This Book is Available on YakiBooki.com
Dropping Constraints 129
Chapter Summary 131
Chapter 4 Syntax Summary 131
Review Questions 133
Multiple Choice 134
Hands-On Assignments 137
Advanced Challenge 139
Case Study: City Jail 139

Chapter 5 Data Manipulation and Transaction Control 141

Introduction 142
Inserting New Rows 143
Using the INSERT Command 143
Handling Virtual Columns 150
Handling Single Quotes in an INSERT Value 152
Inserting Data from an Existing Table 154
Modifying Existing Rows 156
Using the UPDATE Command 156
Using Substitution Variables 158
Deleting Rows 162
Using Transaction Control Statements 163
COMMIT and ROLLBACK Commands 164
SAVEPOINT Command 165
Using Table Locks 168
LOCK TABLE Command 168
SELECT … FOR UPDATE Command 169
Chapter Summary 171
Chapter 5 Syntax Summary 172
Review Questions 173
Multiple Choice 173
Hands-On Assignments 176
Advanced Challenge 177
Case Study: City Jail 178

Chapter 6 Additional Database Objects 181

Introduction 182
Sequences 183
Creating a Sequence 184
Using Sequence Values 189
Setting and Altering Sequence Definitions 192
Altering Sequence Definitions 194
Removing a Sequence 196
Use Identity Columns Instead of Sequences for Primary Key Columns 197

Table of Contents vii

Copyright 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.