Librería Portfolio

An all-in-one study guide prepares you for the updated Oracle Certified Associate certification

It´s been nearly six years since Oracle updated its cornerstone database software, making the demand for a comprehensive study guide for the OCA 12c certification a top priority. This resource answers that demand. Packed with invaluable insight, chapter review questions, bonus practice exams, hundreds of electronic flashcards, and a searchable glossary of terms, this study guide prepares you for the challenging Oracle certification exams.

Provides you with a solid understanding of restricting and sorting data
Walks you through using conversion functions and conditional expressions
Addresses displaying data from multiple tables, manipulating data, database maintenance, and database backups and recovery
Explores the Oracle database architecture and discusses preparing the database environment, creating an Oracle database, and managing the Oracle instance
Focuses on administering and implementing user security
This must-have study guide thoroughly prepares you to take the dramatically updated Oracle 12c OCA exams.



Table of Contents

Introduction xx
Assessment Test xxxv

Part I Oracle Database 12c: SQL Fundamentals 1

Chapter 1 Introducing Oracle Database 12c RDBMS 3

Relational Database Management Systems 4

Characteristics of a Relational Database 5

Oracle's Implementation of RDBMS and ORDBMS 8

The Oracle Database 12c 9

Oracle Database 12c Implementations 9

Connecting to Oracle Database 13

Database Management Tools 14

Oracle Database 12c in the Cloud 19

Familiarizing SQL*Plus 19

Summary 30

Exam Essentials 31

Review Questions 32

Chapter 2 Introducing SQL 35

SQL Fundamentals 36

Oracle Datatypes 38

Operators and Literals 42

Writing Simple Queries 46

Using the SELECT Statement 46

Filtering Rows 51

Sorting Rows 62

Sorting NULLs 64

Limiting Rows 67

Using Expressions 68

Accepting Values at Runtime 72

Using Ampersand Substitution Variables 73

Saving a Variable for a Session 75

Using Positional Notation for Variables 77

Summary 78

Exam Essentials 78

Review Questions 80

Chapter 3 Using Single-Row Functions 89

Single-Row Function Fundamentals 90

Functions for NULL Handling 91

Using Single-Row Character Functions 95

Character Function Overview 95

Character Function Descriptions 96

Using Single-Row Numeric Functions 108

Numeric Function Overview 108

Numeric Function Descriptions 109

Using Single-Row Date Functions 119

Date Format Conversion 119

Date Function Overview 120

Date Function Descriptions 121

Using Single-Row Conversion Functions 131

Conversion Function Overview 131

Conversion Function Descriptions 133

Using Other Single-Row Functions 154

Miscellaneous Function Overview 154

Miscellaneous Function Descriptions 155

Summary 167

Exam Essentials 168

Review Questions 169

Chapter 4 Using Group Functions 175

Group Function Fundamentals 176

Utilizing Aggregate Functions 177

Grouping Data with GROUP BY 178

Group Function Overview 182

Group Function Descriptions: Part 1 184

Group Function Descriptions: Part 2 194

Limiting Grouped Data with HAVING 207

Creating Superaggregates with CUBE and ROLLUP 208

Nesting Functions 215

Summary 218

Exam Essentials 218

Review Questions 219

Chapter 5 Using Joins and Subqueries 227

Writing Multiple-Table Queries 228

Inner Joins 229

Cartesian Joins 239

Outer Joins 240

Other Multiple-Table Queries 245

Using Set Operators 247

The UNION Operator 248

The UNION ALL Operator 249

The INTERSECT Operator 250

The MINUS Operator 250

Putting It All Together 250

Using Subqueries 252

Single-Row Subqueries 253

Multiple-Row Subqueries 254

Subquery Returns No Rows 256

Correlated Subqueries 258

Scalar Subqueries 259

Multiple-Column Subqueries 267

Subqueries in Other DML Statements 268

Summary 269

Exam Essentials 270

Review Questions 271

Chapter 6 Manipulating Data 281

Using DML Statements 282

Inserting Rows into a Table 283

Updating Rows in a Table 289

Deleting Rows from a Table 294

Merging Rows 297

Understanding Transaction Control 299

Savepoints and Partial Rollbacks 302

Data Visibility 304

Summary 306

Exam Essentials 307

Review Questions 308

Chapter 7 Creating Tables and Constraints 319

Database Objects Overview 320

Schema Objects 322

Using Sequences 324

Built-in Datatypes 325

Character Datatypes 325

Numeric Datatypes 329

Date and Time Datatypes 330

Date Arithmetic 334

Binary Datatypes 336

Row ID Datatypes 337

Creating Tables 338

Naming Tables and Columns 339

Specifying Default Values for Columns 341

Adding Comments 348

Creating a Table from Another Table 348

Modifying Tables 350

Adding Columns 350

Modifying Columns 353

Renaming Columns 354

Dropping Columns 354

Hiding Columns from Table 356

Dropping Tables 357

Renaming Tables 357

Making Tables Read-Only 358

Managing Constraints 361

Creating Constraints 362

Dropping Constraints 367

Enabling and Disabling Constraints 367

Deferring Constraint Checks 369

Summary 373

Exam Essentials 374

Review Questions 375

Part II Oracle Database 12c: Installation and Administration 381

Chapter 8 Introducing Oracle Database 12c Components and Architecture 383

Oracle Database Fundamentals 385

Relational Databases 385

Oracle Database 12c Objects 386

Interacting with Oracle Database 12c 388

Oracle Database 12c Architecture 391

User and Server Processes 394

The Oracle Instance 396

Oracle Storage Structures 410

Summary 424

Exam Essentials 425

Review Questions 426

Chapter 9 Creating and Operating Oracle Database 12c 431

Oracle Database 12c Software Installation 432

Planning the Oracle Database 12c Software Install 433

Using the Oracle Universal Installer 440

Using DBCA to Create an Oracle 12c Database 447

Invoking the Database Configuration Assistant 447

Configuring an Oracle Database Using the DBCA 472

Deleting an Oracle Database Using the DBCA 472

Managing Database Templates Using the DBCA 473

Working with Oracle Database Metadata 475

Data Dictionary Views 476

Dynamic Performance Views 477

Managing Initialization-Parameter Files 479

Locating the Default Parameter File 484

Modifying Initialization-Parameter Values 485

Starting Up and Shutting Down an Oracle Instance 490

Starting Up an Oracle Database 12c Instance 491

Shutting Down an Oracle Database 12c Instance 495

Monitoring the Database Alert Log 497

Summary 503

Exam Essentials 504

Review Questions 506

Chapter 10 Understanding Storage and Space Management 511

Understanding the Physical and Logical Storage 512

Contents of a Data Block 514

Managing Tablespaces 516

Ide