Oracle database 12c SQL / Jason Price.

Format:

Book

Author/Creator:

Price, Jason S., author.

Language:

English

Subjects (All):

SQL (Computer program language).

Oracle (Computer file).

Physical Description:

1 online resource (1 volume) : illustrations

Edition:

1st edition

Other Title:

Oracle database 12c structured query language

Place of Publication:

New York : McGraw-Hill Education, [2014]

Language Note:

English

System Details:

text file

Summary:

Write Powerful SQL Statements and PL/SQL Programs Learn how to access Oracle databases through SQL statements and construct PL/SQL programs. Oracle Database 12c SQL offers complete coverage of the latest database features and techniques. Find out how to write SQL statements to retrieve and modify database information, use SQL*Plus and SQL Developer, work with database objects, write PL/SQL programs, use performance optimization techniques, incorporate XML, and more. This Oracle Press guide contains everything you need to know to master SQL. Use SQL statements to access an Oracle database Work with SQL*Plus and SQL Developer Write PL/SQL programs Create tables, sequences, indexes, views, and triggers Design advanced queries containing complex calculations Create database objects to handle abstract data Use date, time stamp, and time interval data types Establish user roles and privileges Handle multimedia files using large objects Tune SQL statements to make them execute faster Generate, process, and store XML data Master the very latest Oracle Database 12 c features Code examples in the book are available for download.

Contents:

Cover

Title Page

Copyright Page

About the Author

Acknowledgments

Contents at a Glance

Contents

Introduction

Chapter 1: Introduction

What is a Relational Database?

Introducing Structured Query Language (SQL)

Using SQL*Plus

Starting SQL*Plus

Starting SQL*Plus from the Command Line

Performing a SELECT Statement Using SQL*Plus

Using SQL Developer

Creating the Store Schema

Examining the Script

Running the Script

Examining the Store Data Definition Language Statements

Adding, Modifying, and Removing Rows

Adding a Row to a Table

Modifying an Existing Row in a Table

Removing a Row from a Table

Connecting to and Disconnecting from a Database

Quitting SQL*Plus

Introducing Oracle PL/SQL

Summary

Chapter 2: Retrieving Information from Database Tables

Performing Single Table SELECT Statements

Retrieving All Columns from a Table

Limiting Rows to Retrieve Using the WHERE Clause

Row Identifiers

Row Numbers

Performing Arithmetic

Performing Date Arithmetic

Using Columns in Arithmetic

Arithmetic Operator Precedence

Using Column Aliases

Combining Column Output Using Concatenation

Null Values

Displaying Distinct Rows

Comparing Values

Using the Not Equal Operator

Using the Greater Than Operator

Using the Less Than or Equal to Operator

Using the ANY Operator

Using the ALL Operator

Using the SQL Operators

Using the LIKE Operator

Using the IN Operator

Using the BETWEEN Operator

Using the Logical Operators

Using the AND Operator

Using the OR Operator

Logical Operator Precedence

Sorting Rows Using the ORDER BY Clause

Performing SELECT Statements That Use Two Tables

Using Table Aliases

Cartesian Products

Performing SELECT Statements That Use More than Two Tables

Join Conditions and Join Types.

Non-Equijoins

Outer Joins

Self Joins

Performing Joins Using the SQL/92 Syntax

Performing Inner Joins on Two Tables Using SQL/92

Simplifying Joins with the USING Keyword

Performing Inner Joins on More than Two Tables Using SQL/92

Performing Inner Joins on Multiple Columns Using SQL/92

Performing Outer Joins Using SQL/92

Performing Self Joins Using SQL/92

Performing Cross Joins Using SQL/92

Chapter 3: Using SQL*Plus

Viewing the Structure of a Table

Editing SQL Statements

Saving, Retrieving, and Running Files

Formatting Columns

Setting the Page Size

Setting the Line Size

Clearing Column Formatting

Using Variables

Temporary Variables

Defined Variables

Creating Simple Reports

Using Temporary Variables in a Script

Using Defined Variables in a Script

Passing a Value to a Variable in a Script

Adding a Header and Footer

Computing Subtotals

Getting Help from SQL*Plus

Automatically Generating SQL Statements

Disconnecting from the Database and Exiting SQL*Plus

Chapter 4: Using Simple Functions

Types of Functions

Using Single-Row Functions

Character Functions

Numeric Functions

Conversion Functions

Regular Expression Functions

Using Aggregate Functions

AVG()

COUNT()

MAX() and MIN()

STDDEV()

SUM()

VARIANCE()

Grouping Rows

Using the GROUP BY Clause to Group Rows

Incorrect Usage of Aggregate Function Calls

Using the HAVING Clause to Filter Groups of Rows

Using the WHERE and GROUP BY Clauses Together

Using the WHERE, GROUP BY, and HAVING Clauses Together

Chapter 5: Storing and Processing Dates and Times

Simple Examples of Storing and Retrieving Dates

Converting Datetimes Using TO_CHAR() and TO_DATE()

Using TO_CHAR() to Convert a Datetime to a String.

Using TO_DATE() to Convert a String to a Datetime

Setting the Default Date Format

How Oracle Interprets Two-Digit Years

Using the YY Format

Using the RR Format

Using Datetime Functions

ADD_MONTHS()

LAST_DAY()

MONTHS_BETWEEN()

NEXT_DAY()

ROUND()

SYSDATE

TRUNC()

Using Time Zones

Time Zone Functions

The Database Time Zone and Session Time Zone

Obtaining Time Zone Offsets

Obtaining Time Zone Names

Converting a Datetime from One Time Zone to Another

Using Timestamps

Using the Timestamp Types

Timestamp Functions

Using Time Intervals

Using the INTERVAL YEAR TO MONTH Type

Using the INTERVAL DAY TO SECOND Type

Time Interval Functions

Chapter 6: Subqueries

Types of Subqueries

Writing Single-Row Subqueries

Subqueries in a WHERE Clause

Using Other Single-Row Operators

Subqueries in a HAVING Clause

Subqueries in a FROM Clause (Inline Views)

Errors You Might Encounter

Writing Multiple-Row Subqueries

Using IN with a Multiple-Row Subquery

Using ANY with a Multiple-Row Subquery

Using ALL with a Multiple-Row Subquery

Writing Multiple-Column Subqueries

Writing Correlated Subqueries

A Correlated Subquery Example

Using EXISTS and NOT EXISTS with a Correlated Subquery

Writing Nested Subqueries

Writing UPDATE and DELETE Statements Containing Subqueries

Writing an UPDATE Statement Containing a Subquery

Writing a DELETE Statement Containing a Subquery

Using Subquery Factoring

Chapter 7: Advanced Queries

Using the Set Operators

The Example Tables

Using the UNION ALL Operator

Using the UNION Operator

Using the INTERSECT Operator

Using the MINUS Operator

Combining Set Operators

Using the TRANSLATE() Function

Using the DECODE() Function

Using the CASE Expression.

Using Simple CASE Expressions

Using Searched CASE Expressions

Hierarchical Queries

The Example Data

Using the CONNECT BY and START WITH Clauses

Using the LEVEL Pseudo Column

Formatting the Results from a Hierarchical Query

Starting at a Node Other than the Root

Using a Subquery in a START WITH Clause

Traversing Upward Through the Tree

Eliminating Nodes and Branches from a Hierarchical Query

Including Other Conditions in a Hierarchical Query

Using Recursive Subquery Factoring to Query Hierarchical Data

Using the ROLLUP and CUBE Clauses

Using the ROLLUP Clause

Using the CUBE Clause

Using the GROUPING() Function

Using the GROUPING SETS Clause

Using the GROUPING_ID() Function

Using a Column Multiple Times in a GROUP BY Clause

Using the GROUP_ID() Function

Using CROSS APPLY and OUTER APPLY

CROSS APPLY

OUTER APPLY

Using LATERAL

Chapter 8: Analyzing Data

Using Analytic Functions

The Example Table

Using the Ranking Functions

Using the Inverse Percentile Functions

Using the Window Functions

Using the Reporting Functions

Using the LAG() and LEAD() Functions

Using the FIRST and LAST Functions

Using the Linear Regression Functions

Using the Hypothetical Rank and Distribution Functions

Using the MODEL Clause

An Example of the MODEL Clause

Using Positional and Symbolic Notation to Access Cells

Accessing a Range of Cells Using BETWEEN and AND

Accessing All Cells Using ANY and IS ANY

Getting the Current Value of a Dimension Using CURRENTV()

Accessing Cells Using a FOR Loop

Handling Null and Missing Values

Updating Existing Cells

Using the PIVOT and UNPIVOT Clauses

A Simple Example of the PIVOT Clause

Pivoting on Multiple Columns

Using Multiple Aggregate Functions in a Pivot.

Using the UNPIVOT Clause

Performing Top-N Queries

Using the FETCH FIRST Clause

Using the OFFSET Clause

Using the PERCENT Clause

Using the WITH TIES Clause

Finding Patterns in Data

Finding V-Shaped Data Patterns in the all_sales2 Table

Finding W-Shaped Data Patterns in the all_sales3 Table

Finding V-Shaped Data Patterns in the all_sales3 Table

Chapter 9: Changing Table Contents

Adding Rows Using the INSERT Statement

Omitting the Column List

Specifying a Null Value for a Column

Including Quote Marks in a Column Value

Copying Rows from One Table to Another

Modifying Rows Using the UPDATE Statement

Returning an Aggregate Function Value Using the RETURNING Clause

Removing Rows Using the DELETE Statement

Database Integrity

Enforcement of Primary Key Constraints

Enforcement of Foreign Key Constraints

Using Default Values

Merging Rows Using MERGE

Database Transactions

Committing and Rolling Back a Transaction

Starting and Ending a Transaction

Savepoints

ACID Transaction Properties

Concurrent Transactions

Transaction Locking

Transaction Isolation Levels

A SERIALIZABLE Transaction Example

Query Flashbacks

Granting the Privilege for Using Flashbacks

Time Query Flashbacks

System Change Number Query Flashbacks

Chapter 10: Users, Privileges and Roles

A Very Short Introduction to Database Storage

Users

Creating a User

Changing a User's Password

Deleting a User

System Privileges

Granting System Privileges to a User

Checking System Privileges Granted to a User

Making Use of System Privileges

Revoking System Privileges from a User

Object Privileges

Granting Object Privileges to a User

Checking Object Privileges Made

Checking Object Privileges Received

Making Use of Object Privileges.

Creating Synonyms.

Notes:

Includes index.

Description based on print version record.

ISBN:

9780071799362

0071799362

OCLC:

857713234