Book description
Write powerful SQL statements and PL/SQL programs
Learn to access Oracle databases through SQL statements and construct PL/SQL programs with guidance from Oracle expert, Jason Price. Published by Oracle Press, Oracle Database 11g SQL explains how to retrieve and modify database information, use SQL Plus and SQL Developer, work with database objects, write PL/SQL programs, and much more. Inside, you'll find in-depth coverage of the very latest SQL features and tools, performance optimization techniques, advanced queries, Java support, and XML. This book contains everything you need to master SQL.
- Explore SQL Plus and SQL Developer
- Use SQL SELECT, INSERT, UPDATE, and DELETE statements
- Write PL/SQL programs
- Create tables, sequences, indexes, views, and triggers
- Write advanced queries containing complex analytical functions
- Create database objects and collections to handle abstract data
- Use large objects to handle multimedia files containing music and movies
- Write Java programs to access an Oracle Database using JDBC
- Tune your SQL statements to make them execute faster
- Explore the XML capabilities of the Oracle Database
- Master the very latest Oracle Database 11g features, such as PIVOT and UNPIVOT, flashback archives, and much more
Table of contents
- Cover Page
- Oracle Database 11g SQL
- Copyright Page
- Dedication Page
- About the Technical Editor
- Contents
- Acknowledgments
- Introduction
- 1 Introduction
-
2 Retrieving Information from Database Tables
- Performing Single Table SELECT Statements
- Retrieving All Columns from a Table
- Specifying Rows to Retrieve Using the WHERE Clause
- Row Identifiers
- Row Numbers
- Performing Arithmetic
- Using Column Aliases
- Combining Column Output Using Concatenation
- Null Values
- Displaying Distinct Rows
- Comparing Values
- Using the SQL Operators
- Using the Logical Operators
- 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
-
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
- Summary
-
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
- Creating Simple Reports
- Getting Help from SQL* Plus
- Automatically Generating SQL Statements
- Disconnecting from the Database and Exiting SQL* Plus
- Summary
- 4 Using Simple Functions
- 5 Storing and Processing Dates and Times
- 6 Subqueries
-
7 Advanced Queries
- Using the Set Operators
- Using the TRANSLATE() Function
- Using the DECODE() Function
- Using the CASE Expression
-
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 the Extended GROUP BY Clauses
- Using the ROLLUP Clause
-
Using the 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
- Summary
- 8 Changing Table Contents
-
9 Users, Privileges, and Roles
- Users
- System Privileges
- Object Privileges
-
Roles
- Creating Roles
- Granting Privileges to Roles
- Granting Roles to a User
- Checking Roles Granted to a User
- Checking System Privileges Granted to a Role
- Checking Object Privileges Granted to a Role
- Making Use of Privileges Granted to a Role
- Default Roles
- Revoking a Role
- Revoking Privileges from a Role
- Dropping a Role
- Auditing
- Summary
- 10 Creating Tables, Sequences, Indexes, and Views
- 11 Introducing PL/SQL Programming
-
12 Database Objects
- Introducing Objects
- Creating Object Types
- Using DESCRIBE to Get Information on Object Types
- Using Object Types in Database Tables
-
Using Objects in PL/SQL
- The get_products() Function
- The display_product() Procedure
- The insert_product() Procedure
- The update_product_price() Procedure
- The get_product() Function
- The update_product() Procedure
- The get_product_ref() Function
- The delete_product() Procedure
- The product_lifecycle() Procedure
- The product_lifecycle2() Procedure
- Type Inheritance
- Using a Subtype Object in Place of a Supertype Object
- Other Useful Object Functions
- NOT INSTANTIABLE Object Types
- User-Defined Constructors
- Overriding Methods
- Generalized Invocation
- Summary
-
13 Collections
- Introducing Collections
- Creating Collection Types
- Using a Collection Type to Define a Column in a Table
- Getting Information on Collections
- Populating a Collection with Elements
- Retrieving Elements from Collections
- Using TABLE() to Treat a Collection as a Series of Rows
- Modifying Elements of Collections
- Using a Map Method to Compare the Contents of Nested Tables
- Using CAST() to Convert Collections from One Type to Another
- Using Collections in PL/SQL
- Multilevel Collections
- Oracle Database 10g Enhancements to Collections
- Summary
-
14 Large Objects
- Introducing Large Objects (LOBs)
- The Example Files
- Large Object Types
- Creating Tables Containing Large Objects
- Using Large Objects in SQL
-
Using Large Objects in PL/SQL
- APPEND()
- CLOSE()
- COMPARE()
- COPY()
- CREATETEMPORARY()
- ERASE()
- FILECLOSE()
- FILECLOSEALL()
- FILEEXISTS()
- FILEGETNAME()
- FILEISOPEN()
- FILEOPEN()
- FREETEMPORARY()
- GETCHUNKSIZE()
- GET_STORAGE_LIMIT()
- GETLENGTH()
- INSTR()
- ISOPEN()
- ISTEMPORARY()
- LOADFROMFILE()
- LOADBLOBFROMFILE()
- LOADCLOBFROMFILE()
- OPEN()
- READ()
- SUBSTR()
- TRIM()
- WRITE()
- WRITEAPPEND()
- Example PL/SQL Procedures
- LONG and LONG RAW Types
- Oracle Database 10g Enhancements to Large Objects
- Oracle Database 11g Enhancements to Large Objects
- Summary
-
15 Running SQL Using Java
- Getting Started
- Configuring Your Computer
- The Oracle JDBC Drivers
- Importing the JDBC Packages
- Registering the Oracle JDBC Drivers
- Opening a Database Connection
- Creating a JDBC Statement Object
- Retrieving Rows from the Database
- Adding Rows to the Database
- Modifying Rows in the Database
- Deleting Rows from the Database
- Handling Numbers
- Handling Database Null Values
- Controlling Database Transactions
- Performing Data Definition Language Statements
- Handling Exceptions
- Closing Your JDBC Objects
- Example Program: BasicExample1.java
- Prepared SQL Statements
- Example Program: BasicExample2.java
- The Oracle JDBC Extensions
- Summary
-
16 SQL Tuning
- Introducing SQL Tuning
- Use a WHERE Clause to Filter Rows
- Use Table Joins Rather than Multiple Queries
- Use Fully Qualified Column References When Performing Joins
- Use CASE Expressions Rather than Multiple Queries
- Add Indexes to Tables
- Use WHERE Rather than HAVING
- Use UNION ALL Rather than UNION
- Use EXISTS Rather than IN
- Use EXISTS Rather than DISTINCT
- Use GROUPING SETS Rather than CUBE
- Use Bind Variables
- Comparing the Cost of Performing Queries
- Passing Hints to the Optimizer
- Additional Tuning Tools
- Summary
- 17 XML and the Oracle Database
- A Oracle Data Types
- Index
Product information
- Title: Oracle Database 11g SQL
- Author(s):
- Release date: November 2007
- Publisher(s): McGraw Hill Computing
- ISBN: 9780071596138
You might also like
book
Oracle Database 10g SQL
This volume will take you from novice to master, covering not only how to use SQL …
book
Oracle Database 11g PL/SQL Programming
Design Feature-Rich PL/SQL Applications Deliver dynamic, client/server PL/SQL applications with expert guidance from an Oracle programming …
book
Oracle Database 11g PL/SQL Programming Workbook
Ramp Up Your PL/SQL Programming Skills Master PL/SQL through the hands-on exercises, extensive examples, and real-world …
video
Oracle 11g
In this Oracle 11g video based training course, through the expert tutelage of Lewis Cunningham, you …