Introduction


Overview

Today s database management systems implement a standard language known as Structured Query Language , or SQL. Among other things, SQL allows you to retrieve, add, update, and delete information in a database. SQL is a standard language recognized by the American National Standards Institute (ANSI), and you can use SQL to access an Oracle, SQL Server, DB2, or MySQL database. In this book, you ll learn how to truly master SQL, and you ll find a wealth of practical examples. You can also get all the scripts and programs featured in this book online (see the later section Retrieving the Examples for details).

With this book, you will

  • Master standard SQL, as well as the extensions developed by Oracle Corporation for use with the specific features of the Oracle database.

  • Explore PL/SQL (Procedural Language/SQL), which is built on top of SQL and enables you to write programs that contain SQL statements.

  • Use SQL*Plus to execute SQL statements, scripts, and reports ; SQL*Plus is a tool that allows you to interact with the database.

  • Execute queries, inserts , updates, and deletes against a database.

  • Create database tables, sequences, indexes, views, and users.

  • Perform transactions containing multiple SQL statements.

  • Define database object types and create object tables to handle advanced data.

  • Use large objects to store up to 128 terabytes of character and binary data, and pointers to external files.

  • Perform complex calculations using analytic functions.

  • Use all the very latest Oracle10 g features, such as the BINARY_FLOAT and BINARY_DOUBLE types, the MODEL clause, and the extensions to large objects and collections.

  • Learn about database security.

  • Implement high-performance tuning techniques to make your SQL statements really fly.

  • Learn the basics of running SQL using Java through JDBC.

This book contains 16 chapters and one appendix.

Chapter 1: Introduction

In this chapter, you ll learn about relational databases, be introduced to SQL, see a few simple queries, use SQL*Plus to execute queries, and briefly see PL/SQL.

Chapter 2: Retrieving Information from Database Tables

You ll explore how to retrieve information from one or more database tables using SELECT statements, use arithmetic expressions to perform calculations, filter rows using a WHERE clause, and sort the rows retrieved from a table.

Chapter 3: Using Simple Functions

In this chapter, you ll learn about some of the Oracle database s built-in functions. A function accepts zero or more input parameters and returns an output parameter. Functions allow you to do things like compute averages and square roots of numbers .

Chapter 4: Storing and Processing Dates and Times

You ll learn how the Oracle database processes and stores dates and times, collectively known as datetimes. You ll also learn about timestamps that allow you to store a specific date and time, and time intervals that allow you to store a length of time.

Chapter 5: Using SQL*Plus

In this chapter, you ll use SQL*Plus to view a table s structure, edit a SQL statement, save and run scripts, format column output, define and use variables , and create reports.

Chapter 6: Subqueries

You ll learn how to place a SELECT statement within an outer SQL statement. The inner SELECT statement is known as a subquery. You ll learn about the different types of subqueries, and see how subqueries allow you to build up very complex statements from simple components .

Chapter 7: Advanced Queries

In this chapter, you ll learn how to perform queries containing advanced operators and functions such as: set operators that combine rows returned by multiple queries, the TRANSLATE() function to convert characters in one string to characters in another string, the DECODE() function to search a set of values for a certain value, the CASE expression to perform if-then-else logic, and the ROLLUP and CUBE clauses to return rows containing subtotals. You ll learn about the analytic functions that enable you to perform complex calculations such as finding the top-selling product type for each month, the top salespersons, and so on. You ll see how to perform queries against data that is organized into a hierarchy. Finally, you ll explore the new Oracle10 g MODEL clause to perform inter-row calculations.

Chapter 8: Changing Table Contents

You ll learn how to add, modify, and remove rows using the INSERT , UPDATE , and DELETE statements, and how to make the results of your transactions permanent using the COMMIT statement or undo their results entirely using the ROLLBACK statement. You ll also learn how an Oracle database can process multiple transactions at the same time.

Chapter 9: Database Security

In this chapter, you ll learn about database users and see how privileges are used to enable users to perform specific tasks in the database.

Chapter 10: Creating Tables, Sequences, Indexes, and Views

You ll learn about tables and sequences, which generate a series of numbers, and indexes, which act like an index in a book and allow you quick access to rows. You ll also learn about views, which are predefined queries on one or more tables. Among other benefits, views allow you to hide complexity from a user , and implement another layer of security by only allowing a view to access a limited set of data in the tables.

Chapter 11: Introducing PL/SQL Programming

In this chapter, you ll explore PL/SQL, which is built on top of SQL and enables you to write stored programs in the database that contain SQL statements. PL/SQL is a third-generation language and contains standard programming constructs.

Chapter 12: Database Objects

You ll learn how to create database object types, which may contain attributes and methods . You ll use object types to define column objects and object tables, and see how to manipulate objects using SQL and PL/SQL.

Chapter 13: Collections

In this chapter, you ll learn how to create collection types, which may contain multiple elements. You ll use collection types to define columns in tables, and see how to manipulate collections using SQL and PL/SQL.

Chapter 14: Large Objects

You ll learn about large objects, which can be used to store up to 128 terabytes of character and binary data or point to an external file. You ll also learn about the older LONG types which are still supported in Oracle10 g for backwards compatibility.

Chapter 15: Running SQL Using Java

In this chapter, you ll learn the basics of running SQL using Java through the Java Database Connectivity (JDBC) applications programming interface, which is the glue that allows a Java program to access a database.

Chapter 16: High Performance SQL Tuning

In this final chapter, you ll see SQL tuning tips that you can use to shorten the length of time your queries take to execute. You ll also learn about the Oracle optimizer and examine how to pass hints to the optimizer.

Appendix: Oracle Data Types

This appendix shows the data types available in Oracle SQL and PL/SQL.




Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net