Read an Excerpt
Chapter 1: Selecting Data from OracleIn this chapter, you will learn about and demonstrate knowledge in the following areas:
- Overview of SQL and PL/SQL
- Writing basic SQL statements
- Restricting and sorting row data
- Using single-row functions
Overview of SQL and PL/SQL
This section covers the following topics as an overview of SQL and PL/SQL:
- Theoretical and physical aspects of relational databases
- Oracle's RDBMS and ORDBMS implementations
- Usage and benefits of PL/SQL
Theoretical and Physical Aspects of Relational Databases
Oracle finds its roots in relational database theory conceived by E. F. Codd in the 1950s, and extends those theories into an infinite variety of directions, such as data warehousing, online transaction processing, and Web-enabled applications. Undoubtedly, the popularity of this software is part of the reason you are reading this book. This book has the answers to your questions about what an Oracle database is, how it works, and what you can do with it, all of which you'll need to know in order to pass OCP DBA Exam 1.
Software development companies have taken many different approaches to information management. In years gone by, the more popular software packages for data storage and retrieval focused on flat file systems as the storage means of choice, while simultaneously requiring you to define how information is stored and retrieved, using a programming language such as COBOL. Some early breeds of flat file systems included hierarchical storage systems, where data records were stored in a hierarchy similar to the hierarchical directory structure you might see on your PC's hard drive in Windows Explorer. These applications ran on mainframes, and brand names of these older data management packages include IMS from IBM and IDMS from Computer Associates. The language most often used to develop mechanisms to add or manage data in those systems was COBOL.
Those older flat file systems were great for certain tasks like defining parent/child relationships. A parent/child relationship might include the relationship of salespeople within a food service distribution company to the company's customers, or the tracking number for an invoice as it relates to product line items on the customer's order from that food service distribution company. However, some drawbacks to flat file systems stem from the fact that a parent/child relationship cannot model every possible type of data relationship. Within the food service company example, a customer's order may list many different products. Each of those products themselves will probably appear on many different orders. In this case of a "many products to many orders" relationship, which way should the hierarchy be designed? What should be the parent and what should be the child? The usual solution was to create two separate hierarchies, one with product as parent, the other with order as parent. Unfortunately, this often meant maintaining much of the same information in two places. Keeping data content consistent across multiple places where it is kept makes storage and retrieval complex. Another shortcoming of hierarchical databases using flat file systems is that they are not easily adaptable to changing business needs. If the food service distributor creates a new sales system that calls for joint ownership of customer accounts by multiple salespeople, the hierarchical database will need to be redesigned.
Motivated by dissatisfaction with the cumbersome characteristics of hierarchical flat file databases, E. F. Codd, a computer scientist working for IBM in the 1950s, developed an alternative: the relational model. Instead of storing data in hierarchies, Codd proposed storing related data items, such as control numbers and ordered products, in tables. If the tables were designed according to a few simple principles, Codd discovered, they were both intuitive and extremely efficient in storing data. A single data item could be stored in only one place. Over time, many software makers recognized the significance of Codd's work and began developing products that adhered to Codd's model. Since the 1980s, virtually all database software products (including Oracle's) conform to the relational model.
Central to the success of the relational model is the use of a relational database management system, or RDBMS, for storing, retrieving, and manipulating data in a database. Earlier products required organizations to have many COBOL programmers on staff to code mechanisms for managing data retrieval routines that interact directly with the files of the database. In contrast, the RDBMS handles these tasks automatically using a functional programming language called SQL (pronounced either "sequel" or as the letters spelled out). SQL stands for "structured query language," and it allows users to request the data they want according to strict comparison criteria. The following code block shows a typical SQL statement:
Behind the scenes, an RDBMS translates this statement into a series of operations that retrieve the actual data from a file somewhere on the machine hosting your database. This step is called parsing. After parsing is complete, the RDBMS executes the series of operations to complete the requested action. That series of operations may involve some or all of the following tasks:
- Implicit datatype conversion
- Disk reads or disk writes
- Filtering table data according to search criteria
- Index lookups for faster response time
- Sorting and formatting data returned...