Oracle SQL by example

Oracle SQL by example

“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 fasci...

Descripción completa

Detalles Bibliográficos
Otros Autores: Rischert, Alice Author (author)
Formato: Libro electrónico
Idioma:Inglés
Publicado: [Place of publication not identified] Prentice Hall 2009
Edición:Cuarta edición
Colección:The Prentice Hall Oracle series Oracle SQL by example
Materias:
Oracle (Computer file)
SQL (Computer program language)
Engineering & Applied Sciences
Computer Science
Ver en Biblioteca Universitat Ramon Llull:https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009629135906719
Tabla de Contenidos:
  • 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
  • Workshop
  • 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
  • Workshop
  • 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
  • Workshop
  • 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
  • Workshop
  • 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
  • Workshop
  • 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
  • Workshop
  • 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
  • Workshop
  • 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
  • Workshop
  • 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
  • Workshop
  • 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.

Ejemplares similares