Oracle SQL by example

Format:

Book

Author/Creator:

Rischert, Alice, Author.

Series:

The Prentice Hall Oracle series Oracle SQL by example

Language:

English

Subjects (All):

Oracle (Computer file).

SQL (Computer program language).

Physical Description:

1 online resource (xxxix, 913 p.) : ill.

Edition:

Cuarta edición

Place of Publication:

[Place of publication not identified] Prentice Hall 2009

Language Note:

English

System Details:

text file

Summary:

“The book...has enough depth for even a seasoned professional to pick up enough tips to pay back the price of the book many times over.” –Dr. Paul Dorsey, President, Dulcian, Inc., Oracle Magazine PL/SQL Developer of the Year 2007, and President Emeritus, New York Oracle Users Group “This is a fascinating guide into the world of Oracle SQL with an abundance of well-collected examples. Without a doubt, this book is helpful to beginners and experts alike who seek alternative ways to resolve advanced scenarios.”–Oleg Voskoboynikov, Ph.D., Database Architect The World’s #1 Hands-On Oracle SQL Workbook–Fully Updated for Oracle 11 g Crafted for hands-on learning and tested in classrooms worldwide, this book illuminates in-depth every Oracle SQL technique you’ll need. From the simplest query fundamentals to regular expressions and with newly added coverage of Oracle’s powerful new SQL Developer tool, you will focus on the tasks that matter most. Hundreds of step-by-step, guided lab exercises will systematically strengthen your expertise in writing effective, high-performance SQL. Along the way, you’ll acquire a powerful arsenal of useful skills–and an extraordinary library of solutions for your real-world challenges with Oracle SQL. Coverage includes 100% focused on Oracle SQL for Oracle 11 g , today’s #1 database platform–not “generic” SQL! Master all core SQL techniques including every type of join such as equijoins, self joins, and outer joins Understand Oracle functions in depth, especially character, number, date, timestamp, interval, conversion, aggregate, regular expressions, analytical, and more Practice all types of subqueries, such as correlated and scalar subqueries, and learn about set operators and hierarchical queries Build effective queries and learn fundamental Oracle SQL Developer and SQL*Plus skills Make the most of the Data Dictionary and create tables, views, indexes, and sequences Secure databases using Oracle privileges, roles, and synonyms Explore Oracle 11 g’ s advanced data warehousing features Learn many practical tips about performance optimization, security, and architectural solutions Avoid common pitfalls and understand and solve common mistakes For every database developer, administrator, designer, or architect, regardless of experience!

Contents:

Cover

Contents

Foreword

Preface

CHAPTER 1 SQL and Data

What Is SQL?

Why Learn SQL?

An Introduction to Databases

LAB 1.1 The Relational Database

What Is Data?

How Is Data Organized?

Overview of SQL Language Commands

Lab 1.1 Exercises

Lab 1.1 Exercise Answers

Lab 1.1 Quiz

LAB 1.2 Data Normalization and Table Relationships

Data Normalization

Table Relationships

Database Schema Diagrams

Database Development Context

Lab 1.2 Exercises

Lab 1.2 Exercise Answers

Lab 1.2 Quiz

LAB 1.3 The STUDENT Schema Diagram

The STUDENT Table

The COURSE Table

The SECTION Table

The INSTRUCTOR Table

The ZIPCODE Table

The ENROLLMENT Table

The GRADE_TYPE Table

The GRADE Table

The GRADE_TYPE_WEIGHT Table

The GRADE_CONVERSION Table

Lab 1.3 Exercises

Lab 1.3 Exercise Answers

Lab 1.3 Quiz

Workshop

CHAPTER 2 SQL: The Basics

LAB 2.1 The SQL Execution Environment

Accessing the Oracle Database Server

Getting Started with SQL Developer

SQL and the Oracle Database Server

Creating a Database Connection for SQL Developer

Exploring Database Table Objects

The SQL Developer Worksheet

Commonly Used Data Types

Lab 2.1 Exercises

Lab 2.1 Exercise Answers

Lab 2.1 Quiz

LAB 2.2 The Anatomy of a SELECT Statement

How to Write a SQL Query

Constructing the SQL Statement in SQL Developer

The SQL Worksheet Icons

Eliminating Duplicates with DISTINCT or UNIQUE

Formatting a SQL Statement in SQL Developer

Writing Multiple Statements in the SQL Worksheet

SQL Developer's Statement History

Lab 2.2 Exercises

Lab 2.2 Exercise Answers

Lab 2.2 Quiz

LAB 2.3 An Introduction to SQL*Plus

Why Learn About SQL*Plus?

Starting SQL*Plus

Exiting SQL*Plus

The Remote Database and SQL*Plus

Logon Problems.

Executing SQL Commands Using SQL*Plus

The SQL*Plus Buffer

Formatting SQL*Plus Results

Displaying the Number of Rows Returned

SQL*Plus Commands versus SQL Statements

Saving and Running SQL Statements in SQL*Plus

Discontinuation of the SQL*Plus for Windows GUI Version

Differences Between SQL Developer and SQL*Plus

Lab 2.3 Exercises

Lab 2.3 Exercise Answers

Lab 2.3 Quiz

CHAPTER 3 The WHERE and ORDER BY Clauses

LAB 3.1 The WHERE Clause

Comparison Operators

Logical Operators

Lab 3.1 Exercises

Lab 3.1 Exercise Answers

Lab 3.1 Quiz

LAB 3.2 The ORDER BY Clause

Using the ORDER BY Clause

Sorting Data Using SQL Developer's GUI Functionality

Column Alias

Comments in SQL Statements

Saving Your SQL Statements

Understanding Oracle Error Messages

Reading the Oracle Error Message Carefully

Resolving One Error at a Time

Double-Checking the Syntax of Your Statement

Looking Up the Oracle Error Number

Lab 3.2 Exercises

Lab 3.2 Exercise Answers

Lab 3.2 Quiz

CHAPTER 4 Character, Number, and Miscellaneous Functions

Data Types

Reading Syntax Diagrams

LAB 4.1 Character Functions

The LOWER Function

The UPPER and INITCAP Functions

The LPAD and RPAD Functions

The DUAL Table

The LTRIM, RTRIM, and TRIM Functions

The SUBSTR Function

The INSTR Function

The LENGTH Function

Functions in WHERE and ORDER BY Clauses

Nested Functions

Concatenation

The REPLACE Function

The TRANSLATE Function

The SOUNDEX Function

Which Character Function Should You Use?

SQL Developer Snippets

Searching, Replacing, and Validating Text

Lab 4.1 Exercises

Lab 4.1 Exercise Answers

Lab 4.1 Quiz

LAB 4.2 Number Functions

The ABS Function

The SIGN Function

ROUND and TRUNC Functions

The FLOOR and CEIL Functions.

The MOD Function

Floating-Point Numbers

The REMAINDER Function

Which Number Function Should You Use?

Arithmetic Operators

Lab 4.2 Exercises

Lab 4.2 Exercise Answers

Lab 4.2 Quiz

LAB 4.3 Miscellaneous Single-Row Functions

The NVL Function

The COALESCE Function

The NVL2 Function

The LNNVL Function

The NULLIF Function

The NANVL Function

The DECODE Function

The Searched CASE Expression

Simple CASE Expression

Which Functions and CASE Expressions Should You Use?

Lab 4.3 Exercises

Lab 4.3 Exercise Answers

Lab 4.3 Quiz

CHAPTER 5 Date and Conversion Functions

LAB 5.1 Applying Oracle's Date Format Models

Changing the Date Display Format

Performing a Date Search

Implicit Conversion and Default Date Format

The RR Date Format Mask and the Previous Century

The Time Component

Time and the TRUNC Function

The ANSI DATE and ANSI TIMESTAMP Formats

Lab 5.1 Exercises

Lab 5.1 Exercise Answers

Lab 5.1 Quiz

LAB 5.2 Performing Date and Time Math

The SYSDATE Function

Performing Arithmetic on Dates

The ROUND Function

The EXTRACT Function

Snippets

Lab 5.2 Exercises

Lab 5.2 Exercise Answers

Lab 5.2 Quiz

LAB 5.3 Understanding the TIMESTAMP and TIME ZONE Data Types

The TIMESTAMP Data Type

The TIMESTAMP WITH TIME ZONE Data Type

The TIMESTAMP WITH LOCAL TIME ZONE Data Type

Common Format Masks

Datetime Functions

Extract Functions

Conversion Functions

Datetime Expression

Lab 5.3 Exercises

Lab 5.3 Exercise Answers

Lab 5.3 Quiz

LAB 5.4 Performing Calculations with the Interval Data Types

Using Intervals

EXTRACT and Intervals

Interval Expressions

Determining Overlaps

Lab 5.4 Exercises

Lab 5.4 Exercise Answers

Lab 5.4 Quiz

LAB 5.5 Converting from One Data Type to Another.

Data Type Conversion

The CAST Function

CAST Versus Oracle's Conversion Functions

Formatting Data

Lab 5.5 Exercises

Lab 5.5 Exercise Answers

Lab 5.5 Quiz

CHAPTER 6 Aggregate Functions, GROUP BY, and HAVING Clauses

LAB 6.1 Aggregate Functions

The COUNT Function

The SUM Function

The AVG Function

The MIN and MAX Functions

MIN and MAX with Different Data Types

Aggregate Functions and Nulls

Aggregate Functions and CASE

Aggregate Function Syntax

Lab 6.1 Exercises

Lab 6.1 Exercise Answers

Lab 6.1 Quiz

LAB 6.2 The GROUP BY and HAVING Clauses

The GROUP BY Clause

The HAVING Clause

Nesting Aggregate Functions

Taking Aggregate Functions and Groups to the Next Level

Lab 6.2 Exercises

Lab 6.2 Exercise Answers

Lab 6.2 Quiz

CHAPTER 7 Equijoins

LAB 7.1 The Two-Table Join

Steps to Formulate the SQL Statement

Table Aliases

Narrowing Down Your Result Set

Nulls and Joins

ANSI Join Syntax

The Cartesian Product

Lab 7.1 Exercises

Lab 7.1 Exercise Answers

Lab 7.1 Quiz

LAB 7.2 Joining Three or More Tables

Joining Three or More Tables

ANSI Join Syntax for Joining Three or More Tables

Multicolumn Joins

Joining Across Many Tables

The ANSI Join Versus the Traditional Join Syntax

Using SQL Developer's Query Builder

Different Types of Joins

Lab 7.2 Exercises

Lab 7.2 Exercise Answers

Lab 7.2 Quiz

CHAPTER 8 Subqueries

LAB 8.1 Simple Subqueries

Scalar Subqueries

Subqueries That Return Multiple Rows

Nesting Multiple Subqueries

Subqueries and Joins

Subqueries That Return Multiple Columns

Subqueries and Nulls

ORDER BY Clause in Subqueries

Lab 8.1 Exercises

Lab 8.1 Exercise Answers

Lab 8.1 Quiz

LAB 8.2 Correlated Subqueries

Reviewing Rows with Correlated Subqueries.

The EXISTS Operator

The NOT EXISTS Operator

NOT EXISTS Versus NOT IN

Avoiding Incorrect Results Through the Use of Subqueries

Unnesting Queries

Subquery Performance Considerations

Lab 8.2 Exercises

Lab 8.2 Exercise Answers

Lab 8.2 Quiz

LAB 8.3 Inline Views and Scalar Subquery Expressions

Inline Views

Scalar Subquery Expressions

Lab 8.3 Exercises

Lab 8.3 Exercise Answers

Lab 8.3 Quiz

LAB 8.4 ANY, SOME, and ALL Operators in Subqueries

ANY and SOME

The ALL Operator

Lab 8.4 Exercises

Lab 8.4 Exercise Answers

Lab 8.4 Quiz

CHAPTER 9 Set Operators

LAB 9.1 The Power of UNION and UNION ALL

The UNION Operator

The UNION ALL Operator

ORDER BY and Set Operations

Lab 9.1 Exercises

Lab 9.1 Exercise Answers

Controlling the Sort Order

Data Type Conversions and NULLS

Lab 9.1 Quiz

LAB 9.2 The MINUS and INTERSECT Set Operators

The MINUS Operator

The INTERSECT Operator

Execution Order of Set Operations

Comparing Two Tables

Lab 9.2 Exercises

Lab 9.2 Exercise Answers

Lab 9.2 Quiz

CHAPTER 10 Complex Joins

LAB 10.1 Outer Joins

The ANSI Outer Join

The Oracle Outer Join Operator (+)

The Outer Join and the UNION ALL Operator

The Full Outer Join

Lab 10.1 Exercises

Lab 10.1 Exercise Answers

Lab 10.1 Quiz

LAB 10.2 Self-Joins

Constructing a Self-Join

The Nonequijoin

Lab 10.2 Exercises

Lab 10.2 Exercise Answers

Lab 10.2 Quiz

CHAPTER 11 Insert, Update, and Delete

LAB 11.1 Creating Data and Transaction Control

Inserting an Individual Row

Data Types and Inserts

Inserts and Scalar Subqueries

Inserting Multiple Rows

Inserting into Multiple Tables

Transaction Control

Lab 11.1 Exercises

Lab 11.1 Exercise Answers

Lab 11.1 Quiz

LAB 11.2 Updating and Deleting Data.

Updating Data.

Notes:

Bibliographic Level Mode of Issuance: Monograph

Includes bibliographical references.

Description based on publisher supplied metadata and other sources.

ISBN:

9786612431883

9780137046942

0137046944

9781282431881

1282431889

9780137046966

0137046960

OCLC:

1027128697