Ocp - Sql&pl_sql(vol1)

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Volume One S Participant Guide

Edition 1.1 M03989 T1001E11

Authors

Copyright EĂOracle Corporation, 1992, 1996. All rights reserved.

Neena Kochhar Debby Kramer

This documentation contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and discloĆ sure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the folĆ lowing legend is applicable:

Technical Contributors and Reviewers

Restricted Rights Legend

Christian Bauwens Debra Bowman Lenny Brunson Jackie Collins Ralf Durben Brian Fry Anthony Holbrook Karlene Jensen Sarah Jones Glenn Maslen Sundar Nagarathnam Sandra Schrick Ulrike Schwinn Rosemarie Truman Jenny Tsai Laura Van Deusen

Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights softĆ ware under Federal law, and as set forth in subparagraph (c) (1) (ii) of DFARS 252.227Ć7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of the Worldwide Education Services group of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the DeĆ partment of Defense, then it is delivered with Restricted Rights," as defined in FAR 52.227Ć14, Rights in DataĆGeneral, including Alternate III (June 1987). The information in this document is subject to change without notice. If you find any problems in the documentation, please report them in writing to Worldwide Education Services, Oracle Corporation, 500 Oracle Parkway, Box 659806, RedĆ wood Shores, CA 94065. Oracle Corporation does not warrant that this document is error free. SQL*Plus, PL/SQL, Procedure Builder, Developer/2000, Oracle7 Server, Oracle Server, Discoverer/2000, and Designer/2000 are trademarks or registered tradeĆ marks of Oracle Corporation. All other products or company names are used for identification purposes only, and may be trademarks of their respective owners.

Publishers Stephanie Jones Kimberly Lee Jennifer Robertson Mark Turangan

Contents Volume 1 Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Profile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Related Publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Typographic Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . I

xi xii xviii xix

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

IĆ1

Course Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Course Outline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Relational Database Concept . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . System Development Cycle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . What Is Oracle? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Oracle Product Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL, SQL*Plus, and PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summit Sporting Goods Demonstration Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

IĆ3 IĆ5 IĆ9 IĆ17 IĆ19 IĆ21 IĆ23 IĆ29 IĆ33

1. Selecting Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

1Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Basic Query Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Arithmetic Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Column Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Concatenation Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Literal Character String . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Managing Null Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Preventing the Selection of Duplicate Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL*Plus Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Logging in to SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Displaying Table Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL*Plus Editing Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL*Plus File Commands and Online Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

1Ć3 1Ć5 1Ć13 1Ć21 1Ć23 1Ć25 1Ć27 1Ć31 1Ć35 1Ć37 1Ć39 1Ć41 1Ć43 1Ć45 1Ć51 1Ć53 1Ć55

2. Limiting Selected Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Ordering Rows with the ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Limiting Selected Rows with the WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . Comparison Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2Ć3 2Ć5 2Ć11 2Ć13

iii

Negating Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Querying Data with Multiple Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Rules of Precedence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2Ć17 2Ć19 2Ć27 2Ć29 2Ć33 2Ć35 2Ć37

3. Single Row Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

3Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Single Row Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Character Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Number Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Oracle Date Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Using Arithmetic Operators with Dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Date Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TO_CHAR Function with Date Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TO_CHAR Function with Number Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TO_NUMBER and TO_DATE Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Nesting Single Row Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Nesting Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

3Ć3 3Ć5 3Ć7 3Ć9 3Ć17 3Ć23 3Ć25 3Ć27 3Ć33 3Ć35 3Ć45 3Ć49 3Ć51 3Ć53 3Ć55 3Ć57 3Ć59

4. Displaying Data from Multiple Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

4Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . What Is a Cartesian Product? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Simple Join Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Using Table Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . NonĆEquijoin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Returning Records with No Direct Match . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Joining a Table to Itself . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

4Ć3 4Ć5 4Ć7 4Ć9 4Ć17 4Ć19 4Ć21 4Ć25 4Ć27 4Ć29 4Ć31

5. Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5Ć3 5Ć5 5Ć7

iv

The GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Illegal Queries Using Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Groups Within Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The HAVING Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5Ć13 5Ć19 5Ć23 5Ć25 5Ć31 5Ć33 5Ć34

6. Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

6Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . How Are Nested Subqueries Processed? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Single Row Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Errors with Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Multiple Row Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . HAVING Clause with Nested Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

6Ć3 6Ć5 6Ć7 6Ć9 6Ć11 6Ć15 6Ć17 6Ć19 6Ć21 6Ć23 6Ć25

Volume 2 7. Specifying Variables at Runtime . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

7Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Substitution Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Single Ampersand Substitution Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Defining User Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Passing Values into a Script File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

7Ć3 7Ć5 7Ć7 7Ć9 7Ć15 7Ć21 7Ć23 7Ć25 7Ć26

8. Overview of Data Modeling and Database Design . . . . . . . . . . . . . . . . . . . . . . . .

8Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . System Development Cycle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Entity Relationship Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Entity Relationship Model Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Integrity Constraints and Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Designing the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

8Ć3 8Ć5 8Ć7 8Ć9 8Ć11 8Ć13 8Ć15 8Ć23 8Ć29 8Ć39

v

9. Creating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

9Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Oracle7 Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating a Table from a Table Instance Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating a Table from Rows in Another Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Confirming Table Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

9Ć3 9Ć5 9Ć7 9Ć13 9Ć15 9Ć25 9Ć31 9Ć33 9Ć35 9Ć37 9Ć39

10. Oracle Data Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

10Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Querying the Data Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Checking Constraints on a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 10 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

10Ć3 10Ć5 10Ć7 10Ć13 10Ć17 10Ć19 10Ć21

11. Manipulating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

11Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Adding a New Row to a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Copying Rows from Another Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Updating Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Deleting Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Transaction Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Committing Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Rolling Back Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Rolling Back Changes to a Savepoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Statement Level Rollback . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 11 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

11Ć3 11Ć5 11Ć7 11Ć19 11Ć21 11Ć29 11Ć35 11Ć39 11Ć43 11Ć45 11Ć47 11Ć49 11Ć51 11Ć53

12. Altering Tables and Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

12Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Adding a Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Modifying a Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Adding and Dropping a Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

12Ć3 12Ć5 12Ć7 12Ć9 12Ć11

vi

Disabling and Enabling a Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Dropping a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Renaming and Truncating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Adding a Comment to a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 12 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

12Ć15 12Ć17 12Ć19 12Ć21 12Ć23 12Ć25 12Ć27

13. Creating Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

13Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating a Sequence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Using a Sequence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Altering a Sequence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Removing a Sequence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 13 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

13Ć3 13Ć5 13Ć7 13Ć13 13Ć17 13Ć19 13Ć21 13Ć23 13Ć25

14. Creating Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

14Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Performing DML Operations on a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Confirming View Names and Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Removing a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 14 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

14Ć3 14Ć5 14Ć7 14Ć15 14Ć21 14Ć23 14Ć25 14Ć27 14Ć29

15. Creating Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

15Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . When Is the Index Used? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Index Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Confirming Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Removing an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 15 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

15Ć3 15Ć5 15Ć7 15Ć9 15Ć13 15Ć17 15Ć19 15Ć21 15Ć23 15Ć25

16. Controlling User Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

16Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

16Ć3 16Ć5

vii

System Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . What Is a Role? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Changing Your Password . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Granting Object Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Confirming Privileges Granted . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Revoking Object Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating a Synonym for an Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 16 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

16Ć7 16Ć11 16Ć13 16Ć15 16Ć21 16Ć23 16Ć25 16Ć29 16Ć31 16Ć33

17. Summary of SQL and SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

17Ć1

Summary of SQL and SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 17 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

17Ć3 17Ć9 17Ć11

Volume 3 18. Overview of PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

18Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . PL/SQL Block Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The PL/SQL Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . About Procedure Builder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

18Ć3 18Ć5 18Ć9 18Ć15 18Ć17

19. Basics of Procedure Builder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

19Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Oracle Procedure Builder Capabilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Procedure Builder Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Object Navigator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Interpreter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Program Unit Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Stored Program Unit Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Debugging a Stored Program Unit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Performing Debug Actions in the Interpreter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Setting a Breakpoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Examining Local Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 19 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

19Ć3 19Ć5 19Ć7 19Ć9 19Ć15 19Ć23 19Ć27 19Ć31 19Ć33 19Ć35 19Ć37 19Ć43 19Ć45 19Ć47 19Ć49

20. Modularizing Programming with Subprograms . . . . . . . . . . . . . . . . . . . . . . . . . . .

20Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

20Ć3 20Ć5

viii

Creating a Subprogram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating a Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Comparing Functions and Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating a Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Invoking Subprograms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 20 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

20Ć7 20Ć9 20Ć15 20Ć17 20Ć21 20Ć29 20Ć31 20Ć33

21. Developing a Simple PL/SQL Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

21Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Declaring PL/SQL Variables and Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Declaring Scalar Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Declaring Composite Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . PL/SQL Block Syntax Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Assigning Values to Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Programming Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 21 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

21Ć3 21Ć5 21Ć7 21Ć9 21Ć15 21Ć25 21Ć29 21Ć43 21Ć49 21Ć51 21Ć53

22. Interacting with Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

22Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Retrieving Data Using PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SELECT Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Manipulating Data Using PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Controlling Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 22 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

22Ć3 22Ć5 22Ć7 22Ć15 22Ć19 22Ć23 22Ć27 22Ć31 22Ć33 22Ć35

23. Controlling Flow in PL/SQL Blocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

23Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The IF Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Building Logical Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Loop Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 23 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

23Ć3 23Ć5 23Ć7 23Ć13 23Ć17 23Ć29 23Ć31 23Ć33

ix

24. Processing Queries by Using Explicit Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . .

24Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Controlling Explicit Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Explicit Cursor Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Cursors and Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Cursors with Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Cursor FOR Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Applying the WHERE CURRENT OF Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 24 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

24Ć3 24Ć5 24Ć7 24Ć17 24Ć21 24Ć23 24Ć25 24Ć27 24Ć29 24Ć31 24Ć33

25. Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

25Ć1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Exception Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Trapping Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Trapping Predefined Oracle7 Server Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . Trapping NonĆPredefined Oracle7 Server Exceptions . . . . . . . . . . . . . . . . . . . . . . . . Trapping UserĆDefined Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Error Trapping Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Propagating Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 25 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

25Ć3 25Ć5 25Ć7 25Ć9 25Ć11 25Ć15 25Ć17 25Ć19 25Ć21 25Ć23 25Ć25 25Ć27

26. Summary of PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

26Ć1

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 26 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

26Ć3 26Ć9 26Ć11

Volume 4 A

x

Practice Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

AĆ1

Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 1 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 2 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 3 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 4 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 5 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 6 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 7 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 9 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 10 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

AĆ3 AĆ4 AĆ8 AĆ20 AĆ29 AĆ39 AĆ46 AĆ55 AĆ59 AĆ63

B

C

Practice 11 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 12 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 13 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 14 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 15 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 16 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 17 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 19 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 20 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 21 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 22 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 23 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 24 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 25 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice 26 Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

AĆ68 AĆ82 AĆ88 AĆ97 AĆ105 AĆ109 AĆ116 AĆ154 AĆ155 AĆ158 AĆ163 AĆ170 AĆ177 AĆ186 AĆ192

Table Descriptions and Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

BĆ1

Summit Sporting Goods Database Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . S_CUSTOMER Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . S_DEPT Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . S_EMP Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . S_IMAGE Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . S_INVENTORY Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . S_ITEM Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . S_ORD Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . S_PRODUCT Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . S_REGION Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . S_WAREHOUSE Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

BĆ3 BĆ4 BĆ7 BĆ8 BĆ10 BĆ11 BĆ16 BĆ20 BĆ22 BĆ25 BĆ26

Using SQL*Plus to Create Reports and Manage PL/SQL Code . . . . . . . . . . . .

CĆ1

Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Entering Commands in SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Comparison of SQL and SQL*Plus Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL*Plus SET Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Declaring and Creating PL/SQL Blocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Entering Commands in SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Passing Input and Output Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Debugging in SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Displaying Contents of a Subprogram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Executing Stored Subprograms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

CĆ3 CĆ5 CĆ7 CĆ9 CĆ11 CĆ15 CĆ23 CĆ25 CĆ27 CĆ31 CĆ35 CĆ37 CĆ41 CĆ43

xi

D

Practice C . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Practice C Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

CĆ45 CĆ47

Related Products and Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

DĆ1

Related Products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Related Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

DĆ3 DĆ5

Glossary Index

xii

Preface

Profile Before You Begin This Course Before you begin this course, you should be able to use a graphical user interface (GUI). Required prerequisites are familiarity with data processing concepts and techniques. How This Course Is Organized Introduction to Oracle: SQL and PL/SQL Using Procedure Builder is an instructor-led course featuring lecture and hands-on exercises. The concepts and skills introduced are reinforced by online demonstrations and written practice sessions. How This Book Is Organized Lesson

Aim

Lesson 1: Selecting Rows

In order to extract data from the database you need to use the Structured Query Language (SQL) SELECT command. You will want to create SELECT statements that you can use time and time again. You will also see how to save your statements for later use.

Lesson 2: Limiting Selected Rows

xiv

This lesson will cover how to restrict the rows and columns that are displayed, as well as how to specify the order in which the rows are presented.

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Profile

continued

Lesson

Aim

Lesson 3: Single Row Functions

Functions make the basic query block more powerful and are used to manipulate data values. This is the first of two lessons that explore functions. You will focus on single row character, number, and date functions, as well as those functions that convert data from one type to another, for example, character data to numeric.

Lesson 4: Displaying Data from Multiple This lesson will cover how to obtain data Tables from more than one table, using the many different methods available. Lesson 5: Group Functions

This lesson further addresses functions. You will focus on obtaining summary information, such as averages, for groups of rows. You will discuss how to group rows in a table into smaller sets, and how to specify search criteria for groups of rows.

Lesson 6: Subqueries

This lesson covers more advanced features of the SELECT statement. You can write subqueries in the WHERE clause of another SQL statement to obtain values based on an unknown conditional value.

Lesson 7: Specifying Variables at Runtime

You can create a command file containing a WHERE clause to restrict the rows displayed. To change the condition each time the command file is run, you use substitution variables. Substitution variables can replace values in the WHERE clause, a text string, and even a column or a table name.

Preface

xv

Profile

continued

Lesson

Aim

Lesson 8: Overview of Data Modeling and Database Design

Before you build your tables, you design your database. You examine the data modeling process and relational database concepts, and define normalization. You also translate an entity relationship model into a relational database design.

Lesson 9: Creating Tables

You will create tables. You will also build integrity constraints, which are rules governing what can and cannot be done with the data.

Lesson 10: Oracle Data Dictionary

The Oracle data dictionary is one of the most important components of the Oracle7 Server. It consists of a set of tables and views that provide a read-only reference to the database.

Lesson 11: Manipulating Data

Once your tables have been created, you will need to add new rows, make changes to rows in a table, or delete rows by using data manipulation commands. This lesson covers using SQL commands to make changes to data. A number of these data manipulation commands make up a transaction, which you may either save or delete using transaction controls.

Lesson 12: Altering Tables and Constraints

After you create your tables, you may need to change the table structures because you omitted a column, your column definition needs to be changed, or you want to enable or disable constraints. This lesson will demonstrate how you can amend table structures as well as add and remove constraints.

xvi

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Profile

continued

Lesson

Aim

Lesson 13: Creating Sequences

Many applications require the use of unique numbers as primary key values. You can either build code into the application to handle this requirement or use a sequence to generate unique numbers. This lesson covers creating and using sequences that crate unique numbers.

Lesson 14: Creating Views

In this lesson, you will see how views can be used to present data to users in a variety of ways. In addition, you will see how integrity constraints can be enforced, if using a view to insert, update, or delete data.

Lesson 15: Creating Indexes

If you want to improve the performance of some queries, you should consider creating an index. You can also use indexes to enforce uniqueness on a column or a collection of columns.

Lesson 16: Controlling User Access

This lesson describes the Oracle7 Server decentralized security system. Using the commands covered in this lesson, you can control database access to specific objects and add new users with different levels of access privileges. You can provide alternative names for objects by using the CREATE SYNONYM command.

Preface

xvii

Profile

continued

Lesson

Aim

Lesson 17: Summary of SQL and SQL*Plus (optional)

This lesson reviews the basic commands covered in the course so far.

Lesson 18: Overview of PL/SQL

Overview lesson of how to create and use PL/SQL program units and subprograms using Oracle Procedure Builder.

Lesson 19: Basics of Procedure Builder

A key feature of procedural programming is the ability to create and debug code quickly and easily. Procedure Builder provides all of the functionality necessary for you to successfully develop and debug PL/SQL programs. This lesson enables you to manipulate PL/SQL code using Procedure Builder.

Lesson 20: Modularizing Programming with Subprograms

Modularity allows you to break your code into manageable, well-defined units. Each of these units in PL/SQL has two types of subprograms called procedures and functions. You will learn the structure of subprograms and how to invoke them.

Lesson 21: Developing a Simple PL/SQL Block

Create a simple PL/SQL block after learning the various elements that compose a block.

Lesson 22: Interacting with Oracle

Access the database and control transactions through SQL statements in PL/SQL.

xviii

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Profile

continued

Lesson

Aim

Lesson 23: Using Control Structures

Control the flow of your PL/SQL block by using conditional statements and loops.

Lesson 24: Processing Queries with Use a multiple row SELECT statement Explicit Cursors within PL/SQL to process many rows. Declare and control explicit cursors, which are used in loops, including the cursor FOR loop. Lesson 25: Error Handling

When you execute PL/SQL code, you may encounter errors. The error causes the PL/SQL block to halt with an exception. You can trap the exception and perform actions conditionally using exception handlers.

Lesson 26: Summary of PL/SQL (optional)

Review the topics covered in the course. Create a PL/SQL-based application for manipulating and maintaining information in your database.

Preface

xix

Related Publications Oracle Publications Title

Part Number

Oracle7 Server SQL Reference, Release 7.3

A32538

SQL*Plus User’s Guide and Reference, Release 3.3

A42562–1

PL/SQL User’s Guide and Reference, Release 2.3

A32542

Oracle7 Server SQL Language Quick Reference

5421–70–1292

SQL*Plus Quick Reference, Release 3.3

A42561

Oracle Procedure Builder 1.5 Developer’s Guide

A32485

Oracle7 Server Concepts Manual, Release 7.3

A32534

Oracle Press: Oracle Beginner’s Guide

A31178–1

Oracle Press: Oracle Complete Reference

A10197–1

Additional References D

System Release Bulletins

D

Installation and User’s Guides

D

read.me Files

D

International Oracle User’s Group (IOUG) Articles

D

Oracle Magazine

xx

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Typographic Conventions Typographic Conventions Within Text Convention

Object or Term Example

Uppercase

Commands, Use the SELECT command to view functions, information stored in the LAST_NAME column names, column of the S_EMP table. table names, PL/SQL objects, schemas

Lowercase, italic

File names, where: role syntax variables, usernames, passwords

Initial cap

Triggers and button names

is the name of the role to be created.

Assign a When–Validate–Item trigger to the S_ORD block. Press the Cancel button.

Italic

Books, names of courses and manuals, and emphasized words or phrases

Quotation marks Lesson module titles referenced within a course Italic bold

Preface

For further information on the subject see: Oracle7 Server SQL Language Reference Manual Do not save changes to the database. This subject is covered in Lesson 3, “Working with Objects.”

The first time a The algorithm inserts the new key. glossary word is referred to in a section

xxi

Typographic Conventions

continued

Typographic Conventions Within Code Convention

Object or Term Example

Uppercase

Commands, functions

Lowercase, italic

Syntax variables SQL> CREATE ROLE role;

Lowercase

Column names, . . . table names, file OG_ACTIVATE_LAYER names, PL/SQL (OG_GET_LAYER (’prod_pie_layer’)) objects . . .

SQL> SELECT userid 2 FROM s_emp;

SQL> SELECT last_name 2 FROM s_emp; SQLDBA> DROP USER scott 2> IDENTIFIED BY tiger; Bold

Text that must be entered by a user.

SQL> SELECT userid 2 FROM s_emp;

Vertical bar

Separates alternative syntax elements

OFF | ON

Brackets

Optional items list in syntax

[OFF | ON]

Braces

Mandatory items list in syntax

{OFF | ON}

Underlining

Default value in syntax

{OFF | ON}

xxii

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Typographic Conventions

continued

Symbols Used in This Document Guidance relating to the subject matter, such as hints or advice.

For more information, see a publication related to the subject matter

This icon indicates a warning relating to the subject matter. An example warning might be “When deleting rows, word your WHERE clause carefully.”

Used for a series of instructions that must be followed in sequential order.

Preface

xxiii

xxiv

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

I Introduction

IĆ2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Course Objectives At the end of this course, you should be able to D

Describe the relational database approach, concepts, terminology, and operators.

D

Create database structures such as tables and views using Structured Query Language (SQL).

D

Store, retrieve, and update data in the database.

D

Develop PL/SQL blocks of application code using Procedure Builder to manipulate data.

Introduction

IĆ3

IĆ4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Course Outline The course starts with an overview. The first module ends on Day 3 with an optional summary covering the SQL and SQL*Plus content. The second module ends on Day 5 with an optional summary of the PL/SQL section.

DAY 1 Lesson 1

Selecting Rows

Lesson 2

Limiting Selected Rows

Lesson 3

Single Row Functions

Lesson 4

Displaying Data from Multiple Tables

DAY 2 Lesson 5

Group Functions

Lesson 6

Subqueries

Lesson 7

Specifying Variables at Runtime

Lesson 8

Overview of Data Modeling and Database Design

Lesson 9

Creating Tables

Lesson 10

Oracle Data Dictionary

DAY 3 Lesson 11

Manipulating Data

Lesson 12

Altering Tables and Constraints

Lesson 13

Creating Sequences

Lesson 14

Creating Views

Lesson 15

Creating Indexes

Lesson 16

Controlling User Access

Lesson 17

Summary of SQL and SQL*Plus (optional)

Introduction

IĆ5

IĆ6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Course Outline

continued

DAY 4 Lesson 18

Overview of PL/SQL

Lesson 19

Basics of Procedure Builder

Lesson 20

Modularizing Programming with Subprograms

Lesson 21

Developing a Simple PL/SQL Block

Lesson 22

Interacting with Oracle

DAY 5 Lesson 23

Controlling Flow in PL/SQL Blocks

Lesson 24

Processing Queries by Using Explicit Cursors

Lesson 25

Error Handling

Lesson 26

Summary of PL/SQL (optional)

Introduction

IĆ7

IĆ8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Relational Database Concept The principles of the relational model were first outlined by Dr. E. F. Codd in a June 1970 paper called “A Relational Model of Data for Large Shared Data Banks.” In this paper, Dr. Codd proposed the relational model for database systems. The more popular models used at that time were hierarchical and network, or even simple flat file data structures. Relational database management systems (RDBMS) soon became very popular, especially for their ease of use and flexibility in structure. In addition, there were a number of innovative vendors, such as Oracle, who supplemented the RDBMS with a suite of powerful application development and user products, providing a total solution. Concepts of the Relational Model D

Collections of objects or relations store the data.

D

A set of operators can act on the relations to produce other relations.

D

A relational database must possess data integrity so that its data must be accurate and consistent.

An example of a relation is a table. To retrieve data from the tables, use relational operation SQL commands. Relational Database Functions D

Manage the storage of data

D

Control access to the data

D

Provide a means to retrieve and modify the data

For more information, see The Relational Model for Database Management Version 2, E. F. Codd, Addison Wesley.

Introduction

IĆ9

IĆ10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Relational Database Concept

continued

Database Terminology Concept

Description

Table

A table is the basic storage structure of an RDBMS, consisting of one or more columns and zero or more rows.

Row

A row is a combination of column values in a table; for example, the information about one department in the table S_DEPT. A row is sometimes called a “record”.

Column

A column represents one kind of data in a table; for example, the department name in the example table S_DEPT. It is described with a column name and holds data of a specific type and size.

Field

At the intersection of a row and a column, you find a field. The field can contain data. If there is no data in the field, it is said to contain a null value.

Primary Key

A primary key is the column or set of columns that uniquely identifies each row in a table; for example a department number. It must contain a value.

Foreign Key

A foreign key is a column or set of columns that refers to a primary key in the same table or in another table. You create them to enforce relational database design rules.

Guidelines D

No duplicate values are allowed in a primary key.

D

Primary keys generally cannot be changed.

D

Foreign keys are based on data values and are purely logical, not physical pointers.

D

A foreign key value must match an existing primary key value or else be NULL.

Introduction

IĆ11

IĆ12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Relational Database Concept

continued

Relational Database Properties A database is a collection of individual, named objects, such as tables. You do not specify the access route to the tables, and do not need to know how the data is arranged physically. To access the database, you execute a Structured Query Language (SQL) command, which is the American National Standards Institute (ANSI) standard language for operating upon relational databases. The language contains a large set of operators for partitioning and combining relations. The database can be easily modified by using the SQL commands. The relational model allows for full data independence. Abridged List of Database Objects Object

Description

Table

Basic unit of storage composed of rows and columns.

View

Logically represents subsets of data from one or more tables.

Sequence

Generates primary key values.

Index

Improves the performance of queries.

Synonym

Alternate name for an object.

Program unit

Procedure, function, or package of SQL and PL/SQL statements grouped together.

Table Properties A single table is composed of rows and columns. The intersection of the rows and columns are the field values. Each row should be identified by a primary key, which allows no duplicate rows. The order of the rows is insignificant; by default, the data is ordered in the order in which it was inserted. Each column is uniquely named. Column order is insignificant when storing data; specify the column order when the data is retrieved. Field values cannot be broken into smaller components.

Introduction

IĆ13

IĆ14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Relational Database Concept

continued

Protect Data Integrity Ensure that users perform only operations that leave the database in a correct and consistent state with data integrity constraints. All data integrity constraints should be enforced by the database server or the application software. Integrity Constraint Type

Description

Entity

No part of a primary key can be NULL and the value must be unique.

Referential

Foreign key values must match a primary key or be NULL.

Column

Values in the column must match the defined datatype.

User-defined

Values must comply with the business rules.

Introduction

IĆ15

IĆ16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

System Development Cycle From concept to production, develop a database by using the system development cycle. The cycle contains multiple stages of development. This top-down, systematic approach to database development transforms business information requirements into an operational database. Stages of Development Stage

Action

Strategy and Analysis

Study and analyze the business requirements. Build models of the system.

Design

Build a database design based on the model.

Build and Document

Produce the working, tested software. Write user documentation, help screens, and operations manual.

Transition

Engage in a user acceptance testing phase.

Production

Operate the production system.

Introduction

IĆ17

Server

É ÉÉ

IĆ18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

What Is Oracle? Oracle is a database company that offers a comprehensive set of application building and end-user products and services aimed at providing complete information technology solutions. Oracle applications are portable across a wide range of platforms and operating systems, from personal computers to large parallel processors. About the Oracle7 Server Oracle provides a flexible RDBMS called the Oracle7 Server. Its features allow you to store and manage information with all the advantages of a relational structure plus PL/SQL, an engine that provides you with the ability to store and execute database objects, such as procedures and triggers. The Server offers users the options of retrieving data based on optimization techniques. It includes security features that control how a database is accessed and used. Other features are that it has consistency and protection of data through locking mechanisms, and a two-phase commit process for distributed databases across a network. Oracle applications may run on the same computer as the Oracle7 Server. Alternatively, you can run applications on a system local to the user and run the Oracle7 Server on another system (client-server architecture). In this client-server environment, a wide range of computing resources can be used. For example, a form-based airline reservation application can run on a client personal computer while accessing flight data that is conveniently managed by an Oracle7 Server on a central computer. For more information, see Oracle7 Server Concepts Manual.

Introduction

IĆ19

DSS Tools

IĆ20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Oracle Product Set All application development and end-user products available within the Oracle product set allow you to access the database, either directly or indirectly, through SQL commands. Discoverer/2000 A set of products for end users and decision support analysts that supports rapid querying and reporting, multi-dimensional analysis of Data Warehousing, and powerful data mining through a low maintenance meta-layer. Developer/2000 A set of application development tools that allow developers to create screen-based applications for users, reporting features, and graphical displays of charts, images, and drawings. Designer/2000 A family of tools to aid the analysis, design, and generation of Oracle applications. For more information, see Guide to Oracle Products and Services.

Introduction

IĆ21

Server

ÉÉÉÉ SQL and PL/SQL Scripts

IĆ22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL, SQL*Plus, and PL/SQL SQL, SQL*Plus, and PL/SQL commands are used to access and manipulate data stored in an Oracle database. SQL*Plus, SQL, and PL/SQL Language or Tool

Description

SQL

A command language for communication with the Oracle7 Server from any tool or application. Oracle SQL contains many extensions.

SQL*Plus

An Oracle tool that recognizes and submits SQL and PL/SQL statements to the Server for execution and contains its own command language.

PL/SQL

An Oracle procedural language for writing application logic and manipulating data outside the database.

Features of SQL D

Can be used by a range of users, including those with little or no programming experience

D

Is a non-procedural language

D

Reduces the amount of time required for creating and maintaining systems

D

Is an English-like language

Features of SQL*Plus D

Accepts ad hoc entry of statements

D

Accepts SQL and PL/SQL input from files

D

Edits SQL statements with a line editor

D

Controls environmental settings

D

Formats query results into basic reports

D

Interacts with end users

D

Accesses remote databases

Introduction

IĆ23

IĆ24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL, SQL*Plus, and PL/SQL

continued

SQL is the industry standard language for relational databases. The American National Standards Institute (ANSI) adopted it in 1986. The International Standards Organization (ISO) has also adopted it. All major RDBMSs support some form of SQL and most RDBMS vendors intend to comply with the ANSI standard. SQL Commands There are many commands available in SQL. The table below describes the commands covered in this course. Command

Description

SELECT

Retrieves data from the database. Most commonly used command.

INSERT UPDATE DELETE

Enters new rows, changes existing rows, and removes unwanted rows from tables in the database, respectively. Collectively known as Data Manipulation Language (DML) commands.

CREATE ALTER DROP RENAME TRUNCATE

Sets up, changes, and removes data structures from tables. Collectively known as Data Definition Language (DDL) commands.

COMMIT ROLLBACK SAVEPOINT

Manage the changes made by DML statements. Changes to the data can be grouped together into logical transactions.

GRANT REVOKE

Gives or removes access rights to both the Oracle database and the structures within it. Collectively known as Data Control Language (DCL) commands.

For more information, see Oracle7 Server SQL Reference, Release 7.3 and Oracle7 SQL Quick Reference Guide.

Introduction

IĆ25

IĆ26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL, SQL*Plus, and PL/SQL

continued

SQL*Plus Command Categories Category

Purpose

Environment

Affects the general behavior of SQL statements for the session.

Format

Formats query results.

File manipulation

Saves, loads, and runs script files.

Execution

Sends SQL or PL/SQL commands from SQL buffer to Oracle7 Server.

Edit

Modifies SQL commands in the buffer.

Interaction

Allows users to create and pass variables to SQL statements, print variable values, and print messages to the screen.

Miscellaneous

Various commands to connect to the database, manipulate the SQL*Plus environment, and display column definitions.

PL/SQL Block Structure PL/SQL provides a host of programming constructs to manipulate data. Each group of commands is packaged in a PL/SQL block. Block Section

Description

Header

Contains the subprogram name, type, and arguments. Only used for subprograms.

Declarative

Contains the local identifiers for the block.

Executable

Contains the SQL statements and PL/SQL control statements.

Exception

Performs actions when errors occur.

Introduction

IĆ27

IĆ28

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summit Sporting Goods Demonstration Tables The course is based on the fictitious Summit Sporting Goods Company. The diagram on the facing page is an entity relationship (ER) model for Summit Sporting Goods Company. In an effective business system data is divided into discrete categories or entities. An ER model is an illustration of various entities in a business and the relationships between them. The entity relationship model on the facing page represents the main tables and relationships covered in the course. The business narrative describes the company’s database needs. Business Narrative “I’m a manager of a sporting goods wholesale company that operates worldwide to fill orders of retail sporting goods stores. The stores are our customers (some of our people prefer to call them our clients). Right now we have fifteen customers worldwide, but we’re trying to expand our customer base. Our two biggest customers are Big John’s Sports Emporium in San Francisco, CA, USA, and Womansport in Seattle, Washington, USA. For each customer, we must track an identification number and customer name. We may track an address (including the city, state, postal code, and country) and phone number. We maintain warehouses in different regions to best fill the orders of our customers. For each order, we must track an order number. We may track the date ordered, date shipped, and payment type when the information is available. Right now, we have the world divided into five regions: North America, South America, Africa/Middle East, Asia, and Europe. That’s all we track, just the region number and region name. We try to assign each customer to a region so we’ll generally know the best location from which to fill each order. Each warehouse must have a warehouse number. We may track an address (including city, state, postal code, and country) and phone number. We currently have only one warehouse per region, but we’re hoping to have more soon.” “I manage the order entry functions for our wholesale sporting goods business. My department is responsible for placing and tracking the orders when our customers call. For each department, we must track the department number and name. Sometimes, our customers just mail us the orders when they are not in a rush, but most often they call us or fax us an order. We are hoping to expand our business by providing immediate turnaround of order information to our clients. We can promise to ship by the next day as long as the goods are in stock (or inventory) at one of our warehouse locations. When the information is available, we track the amount in stock, the reorder point, maximum stock, the reason we are out of stock, and the date we restocked the item. When the goods are shipped, we intend to fax the shipping information automatically through our shipping system. No, I don’t manage that area. Continued

Introduction

IĆ29

IĆ30

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summit Sporting Goods Demonstration Tables

continued

Business NarrativeĊcontinued My department just ensures that our customers have the correct billing information and verifies that their account is in good credit standing. We may also record general comments about a customer.” “We do make sure all the items the customers have requested are in stock. For each item, we track an item number. We may also track the item price, quantity, and quantity shipped if the information is available. If they are in stock, we want to process the order and tell our clients what the order number is and how much their order total is. If the goods are not in stock, the customer tells us whether we should hold the entire order for a full shipment, or to process the partial order.” “The accounting department is responsible for maintaining the customer information, especially for assigning new customer numbers. My department is allowed to update the customer information only when a customer places an order and their billing or ship to addresses have changed. No, we are not responsible for collections. That’s all handled by accounts receivable. I also think that the sales representatives get involved as their commission depends on customers who pay! For each sales representative, or employee, we must know the employe number and last name. Occasionally, we need to know the first name, user name, start date, title, and monthly salary. We may also track the employee’s commission percent and any comments about the individual.” “Our order entry personnel are well versed in our product line. We hold frequent meetings with marketing so they can inform us of new products. This results in greater customer satisfaction because our order entry operators can answer a lot of questions. This is possible because we deal with a few select customers and maintain a specialty product line. For each product, we must know the product number and name. Occasionally, we must also know the description, suggested price, and unit of sale. We would also like to track very long descriptions of our products and pictures of our products, when necessary.”

Introduction

IĆ31

IĆ32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary Relational database management systems are composed of objects. They are managed by operations and governed by data integrity constraints. Oracle Corporation produces products and services to meet your relational database management system needs. The main product is the Oracle7 Server. The Server allows you to store and manage information by using SQL and the PL/SQL engine for procedural constructs. SQL Oracle7 Server supports ANSI standard SQL and contains extensions. SQL is the language used to communicate with the Server to access, manipulate, and control data access. SQL*Plus SQL*Plus is an Oracle tool to execute SQL and PL/SQL statements. It also contains supporting commands to format queries, set the environment, and edit SQL commands in the SQL buffer. PL/SQL The PL/SQL language extends the SQL language by offering block structured procedural constructs combined with SQL non-procedural capabilities.

Introduction

IĆ33

IĆ34

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

1 Selecting Rows

1Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives In order to extract data from the database you need to use the Structured Query Language (SQL) SELECT command. You may need to restrict the columns that are displayed. This lesson explains all of the commands you will use to perform these actions. You will want to create SELECT statements that can be used time and time again. In this lesson you will also see how to save your statements for later use. At the end of this lesson, you should be able to D

Write a SELECT statement to query the database.

D

Perform arithmetic calculations using SQL arithmetic operators.

D

Handle null values.

D

Specify alternative column headings using aliases.

D

Concatenate columns.

D

Edit SQL statements in the SQL*Plus buffer and create command files.

Selecting Rows

1Ć3

1Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Basic Query Block A SELECT statement retrieves information from the database, implementing all algebraic operators. Syntax SELECT FROM

[DISTINCT] {*,column [alias],....} table;

where: SELECT

is a list of at least one column.

DISTINCT

suppresses duplicates.

*

selects all columns.

column

selects the named column.

alias

gives selected columns a different heading.

FROM table

specifies the table containing the columns.

Selecting Rows

1Ć5

1Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Basic Query Block

continued

Writing SQL Commands By following these simple rules and guidelines, you will be able to construct valid statements that are easy both to read and to edit. D SQL commands may be entered on one or many lines. D Clauses are usually placed on separate lines for readability and ease of editing. D D D

D D

D

Tabs and indents can be used to make code more readable. Command words cannot be split across lines or abbreviated. Keywords and commands typically are entered in uppercase; all other words, such as table names and columns, are entered in lowercase. SQL commands are not case sensitive, unless indicated. An SQL command is entered at the SQL prompt, and subsequent lines are numbered. This is called the SQL buffer. Only one statement can be current at any time within the buffer, and the statement can be executed in a number of ways: D Place a semicolon (;) at the end of last clause. D

Place a semicolon or slash on the last line in the buffer.

D

Place a slash at the SQL prompt.

D

Issue a SQL*Plus RUN command at the SQL prompt.

For more information, see Oracle Applications: Coding Standards, Release 10G.

Selecting Rows

1Ć7

Simplest SELECT statement contains the following two clauses:

1Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Basic Query Block

continued

In its simplest form, a SELECT statement must include the following: D A SELECT clause, which specifies the columns to be displayed. D

A FROM clause, which specifies the table containing the columns listed in the SELECT clause.

Selecting All Columns and Rows The asterisk (*) selects all columns from the table. Example List all columns and all rows from the S_DEPT table. SQL> SELECT 2 FROM

ID ------10 31 32 33 34 35 41 42 43 44 45 50

* s_dept;

NAME REGION_ID --------------- --------Finance 1 Sales 1 Sales 2 Sales 3 Sales 4 Sales 5 Operations 1 Operations 2 Operations 3 Operations 4 Operations 5 Administration 1

12 rows selected.

Selecting Rows

1Ć9

1Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Basic Query Block

continued

Selecting Specific Columns You restrict the query to display only certain columns by specifying the column names, separated by commas, in the SELECT clause. Example Display all department numbers, employee last names, and manager numbers in the S_EMP table. SQL> SELECT 2 FROM

DEPT_ID ------50 41 31 10 50 41 42 43 44 ...

dept_id, last_name, manager_id s_emp;

LAST_NAME MANAGER_ID ------------ ---------Velasquez Ngao 1 Nagayama 1 Quick-To-See 1 Ropeburn 1 Urguhart 2 Menchu 2 Biri 2 Catchpole 2

25 rows selected.

Specify the columns you want to see, in the order in which you want to see them, in the SELECT clause. Do not forget to use the comma as a column name separator. Column Heading Defaults Character and date column headings and data are left-justified within a column and numbers are right-justified. Character and date column headings may be truncated, but number headings may not be truncated. The column labels appear in uppercase by default. You can override the column label display with an alias.

Selecting Rows

1Ć11

1Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Arithmetic Expressions You may need to modify the way data is displayed, perform calculations, or look at what-if scenarios. This is possible using arithmetic expressions. An arithmetic expression may contain column names, constant numeric values, and the arithmetic operators. Arithmetic Operators These are the arithmetic operators available in SQL. You may use arithmetic operators in any clause of a SQL statement except the FROM clause. Operators

Description

+

Add

-

Subtract

*

Multiply

/

Divide

Selecting Rows

1Ć13

1Ć14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Arithmetic Expressions

continued

Example Use the multiplication operator to display annual salary figures and their commission percentage for all employees. SQL> SELECT 2 FROM

last_name, salary * 12, commission_pct s_emp;

LAST_NAME SALARY*12 COMMISSION_PCT ------------ ---------- -------------... Havel 15684 Magee 16800 10 Giljum 17880 12.5 Sedeghi 18180 10 Nguyen 18300 15 Dumas 17400 17.5 Maduro 16800 ... Notice that the resultant calculated column SALARY*12 is not a new column in the originating table, but is for display only.

Selecting Rows

1Ć15

1Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Arithmetic Expressions

continued

Operator Precedence If an arithmetic expression contains more than one operator, multiplication and division are evaluated first. If operators within an expression are of the same priority, then evaluation is from left to right. Example Display the last name, salary, and annual compensation of employees. Calculate the annual compensation as 12 multiplied by the monthly salary, plus a one-time bonus of $100. SQL> SELECT 2 FROM

last_name, salary, 12 * salary + 100 s_emp;

LAST_NAME SALARY 12*SALARY+100 ------------ ---------- ------------Velasquez 2500 30100 Ngao 1450 17500 Nagayama 1400 16900 Quick-To-See 1450 17500 Ropeburn 1550 18700 Urguhart 1200 14500 ... Note: Use parentheses to reinforce the standard order of precedence and to improve clarity. For example, the expression above can be written as (12 * salary) + 100 with no change in the result.

Selecting Rows

1Ć17

1Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Arithmetic Expressions

continued

Override the rules of precedence with parentheses to specify the order in which operators are executed. Example Display the last name, salary, and annual compensation of employees. Calculate the annual compensation as monthly salary plus a monthly bonus of $100, multiplied by 12. SQL> SELECT 2 FROM

last_name, salary, 12 * (salary + 100) s_emp;

LAST_NAME SALARY 12*(SALARY+100) ------------ ---------- --------------Velasquez 2500 31200 Ngao 1450 18600 Nagayama 1400 18000 Quick-To-See 1450 18600 Ropeburn 1550 19800 Urguhart 1200 15600 ...

Selecting Rows

1Ć19

1Ć20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Column Aliases When displaying the result of a query, SQL*Plus normally uses the selected column’s name as the heading. In many cases, that heading may be difficult to understand or even meaningless. You can change a column’s heading by using a column alias. Specify the alias after the column in the SELECT list using a space as a separator. By default, alias headings will be forced to uppercase and cannot contain blank spaces, unless the alias is enclosed in double quotation marks (“ ”). Example Display the last name, salary, and annual compensation of employees. Calculate the annual compensation as monthly salary plus a monthly bonus of $100, multiplied by 12. Name the column ANNUAL_SALARY. SQL> SELECT 2 3 FROM

last_name, salary, 12 * (salary + 100) AS ANNUAL_SALARY s_emp;

Note: You can include the AS keyword before the alias name to comply with ANSI SQL 92 standards. Column Aliases with Double Quotation Marks If the alias contains spaces, special characters (such as # or $), or is case-sensitive, enclose the alias in double quotation marks (“ ”). SQL> SELECT 2 3 FROM

Selecting Rows

last_name, salary, 12 * (salary + 100) ”Annual Salary” s_emp;

1Ć21

1Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Concatenation Operator You can link columns to other columns, arithmetic expressions, or constant values to create a character expression by using the concatenation operator (||). Columns on either side of the operator are combined to make one single output column. Example Display the full names of the employees with the heading Employees. SQL> SELECT 2 FROM

first_name||last_name AS ”Employees” s_emp;

Employees -------------------------------------------------CarmenVelasquez LaDorisNgao MidoriNagayama MarkQuick-To-See AudryRopeburn MollyUrguhart ... The AS keyword before the alias name makes the SELECT clause easier to read.

Selecting Rows

1Ć23

1Ć24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Literal Character String A literal is any character, expression, or number included in the SELECT list that is not a column name or a column alias. It is printed for each row returned. Literal strings of free-format text can be included in the query result and are treated like a column in the SELECT list. Date and character literals must be enclosed within single quotation marks (‘ ’); number literals must not. Example Display the full names of the employees and their titles with the heading Employees. Be sure to add punctuation. SQL> SELECT 2 3 FROM

first_name || ’ ’ || last_name || ’, ’|| title ”Employees” s_emp;

Employees --------------------------------Carmen Velasquez, President LaDoris Ngao, VP, Operations Midori Nagayama, VP, Sales Mark Quick-To-See, VP, Finance Audry Ropeburn, VP, Administration Molly Urguhart, Warehouse Manager ...

Selecting Rows

1Ć25

1Ć26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Managing Null Values If a row lacks a data value for a particular column, that value is said to be null, or to contain null. A null value is a value that is unavailable, unassigned, unknown, or inapplicable. In the COMMISSION_PCT column, you notice that only Sales Representatives earn commission. Other employees are not entitled to earn commission. A null value represents that fact. A null value is not the same as zero or a space. Zero is a number, and a space is a character. Columns of any datatype can contain null values, unless the column was defined as NOT NULL or as PRIMARY KEY when the table was created. Null Values in Arithmetic Expressions If any column value in an expression is null, the result is null. For example, if you attempt to perform division with zero, you will get an error. However, if you divide by null, the result is null. Example Display the last name, salary, title, and calculated commission. SQL> SELECT 2 3 FROM

LAST_NAME -----------... Havel Magee Giljum Sedeghi Nguyen Dumas Maduro ...

last_name, title, salary*commission_pct/100 COMM s_emp;

TITLE COMM --------------------- ---------Warehouse Manager Sales Representative Sales Representative Sales Representative Sales Representative Sales Representative Stock Clerk

140 186.25 151.5 228.75 253.75

For more information, see Oracle7 Server SQL Reference, Release 7.3, “Elements of SQL.”

Selecting Rows

1Ć27

1Ć28

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Managing Null Values

continued

NVL Function In order to convert a null value to an actual value, use the NVL function. Syntax NVL (expr1, expr2) where: expr1

is the source value or expression that may contain null.

expr2

is the target value for converting null.

Note: You can use the NVL function to convert any datatype, but the return value is always the same as the datatype of expr1. Example To calculate values for all employees from the previous example, use the NVL function to convert null values to zero. SQL> SELECT 2 3 FROM LAST_NAME -----------... Havel Magee Giljum ...

last_name, title, salary * NVL(commission_pct,0)/100 COMM s_emp; TITLE COMM --------------------- ---------Warehouse Manager Sales Representative Sales Representative

0 186.25 186.25

NVL Conversions for Various Datatypes Datatype

Conversion Example

NUMBER

NVL(number_column,9)

DATE

NVL(date_column,‘01-JAN-95’)

CHAR or VARCHAR2

NVL(character_column,‘Unavailable’)

Selecting Rows

1Ć29

SQL> SELECT 2 FROM

1Ć30

DISTINCT name s_dept;

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Preventing the Selection of Duplicate Rows Unless you indicate otherwise, SQL*Plus displays the results of a query without eliminating duplicate rows. Example Displaying All Rows Display all department names in the S_DEPT table. SQL> SELECT 2 FROM

name s_dept;

NAME -----------------Finance Sales Sales Sales Sales Sales Operations . . . 12 rows selected The DISTINCT Keyword To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT command word. Example Displaying Unique Rows Display all unique department names in the S_DEPT table. SQL> SELECT 2 FROM

DISTINCT name s_dept;

NAME ----------------------Administration Finance Operations Sales

Selecting Rows

1Ć31

1Ć32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Preventing the Selection of Duplicate Rows

continued

DISTINCT with Multiple Columns You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all selected columns. Example Display all the different combinations of job titles and department numbers. SQL> SELECT 2 FROM

DEPT_ID ------10 31 31 32 33 34 34 35 41 41 41 42 42 43 43 44 44 45 45 50 50

DISTINCT dept_id, title s_emp;

TITLE --------------------VP, Finance Sales Representative VP, Sales Sales Representative Sales Representative Sales Representative Stock Clerk Sales Representative Stock Clerk VP, Operations Warehouse Manager Stock Clerk Warehouse Manager Stock Clerk Warehouse Manager Stock Clerk Warehouse Manager Stock Clerk Warehouse Manager President VP, Administration

21 rows selected.

Selecting Rows

1Ć33

1Ć34

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL*Plus Commands In this lesson, you saw how SQL commands are executed within a product called SQL*Plus. SQL*Plus is a SQL and PL/SQL command execution environment with additional features. You can use a number of SQL*Plus commands when writing even the most basic of SQL statements. This section covers some basic SQL*Plus commands to help you to D

Describe the table structure.

D

Edit SQL in the buffer.

D

Save files containing SQL for editing purposes.

D

Execute saved files.

D

Load SQL commands from a file into the SQL buffer.

D

Obtain online help

For more information, see SQL*Plus User’s Guide and Reference, Release 3.3.

Selecting Rows

1Ć35

1Ć36

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Logging in to SQL*Plus How you invoke SQL*Plus depends upon which type of operating system or windows environment you are running. Log In Through a Windows Environment You double-click the SQL*Plus icon in the window manager, then enter the username, password, and database, if required.

Log In Through a Command Line Environment Once you log on to your machine, at the operating system prompt enter the SQL*Plus command. sqlplus [username [/password [@database]]] where: username password

is your database username. is your database password. If you enter your password here, it is visible. @database is the database connect string. Note: To ensure the integrity of your password, do not enter it at the operating system prompt. Instead, only enter your username. Enter your password at the Password prompt. Once you are successfully logged in SQL*Plus, you see the following message: SQL*Plus: Version 3.1.2 Production on Fri May 12th 15:31:32 1995 Copyright (c) Oracle Corporation 1979, 1992, All rights reserved. SQL>

Selecting Rows

1Ć37

SQL> DESCRIBE s_dept

1Ć38

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Displaying Table Structure In SQL*Plus, you can display the structure of a table using the DESCRIBE command. The result of the command is to see the column names, datatypes, and whether a column must contain data. DESC[RIBE] tablename where: tablename

is the name of any existing table, view, or synonym accessible to the user.

Example Display information about the structure of the S_DEPT table. SQL> DESCRIBE s_dept

Name --------------ID NAME REGION_ID where: Null? Type

Null? Type -------- -------------------NOT NULL NUMBER(7) NOT NULL VARCHAR2(25) NUMBER(7) indicates that a column must contain data. displays the datatype for a column.

Datatype

Description

NUMBER (p,s)

Number value having a maximum number of digits p, and the number of digits to the right of the decimal point s.

VARCHAR2(s)

Variable length character value of maximum size s.

DATE

Date and time value between January 1, 4712 B.C. and December 31, 4712 A.D.

CHAR(s)

Fixed length character value of size s.

For more information, see Oracle 7 Server SQL Reference, Release 7.3.

Selecting Rows

1Ć39

1Ć40

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL*Plus Editing Commands When you enter a SQL command, it is stored in a part of memory called the SQL buffer and remains there until you enter a new command. SQL*Plus commands are entered one line at a time and are not stored in the SQL buffer. Guidelines D

If you press [RETURN] before completing a command, SQL*Plus will prompt you with a line number.

D

You terminate the SQL buffer by either entering one of the terminator characters (semicolon or slash), or pressing [RETURN] twice. You will now see the SQL prompt.

SQL*Plus Editing Commands Command

Description

A[PPEND] text

Adds text to the end of the current line.

C[HANGE] / old / new /

Changes old text to new in the current line.

C[HANGE] / text /

Deletes text from the current line.

CL[EAR] BUFF[ER]

Deletes all lines from the SQL buffer.

DEL

Deletes current line.

DEL n

Deletes one line (specified by n)

DEL m n

Deletes a range of lines (m to n)

I[NPUT]

Inserts an indefinite number of lines.

I[NPUT] text

Inserts a line consisting of text.

L[IST]

Lists all lines in the SQL buffer.

L[IST] n

Lists one line (specified by n).

L[IST] m n

Lists a range of lines (m to n).

R[UN]

Displays and runs the current SQL command in the buffer.

n

Specifies the line to make the current line.

n text

Replaces line n with text.

0 text

Inserts a line before line 1.

Selecting Rows

1Ć41

1Ć42

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL*Plus File Commands and Online Help SQL commands act as the vehicle to the Oracle Server. SQL*Plus commands are used to control the environment, format query results, and manage files. You can use the commands identified in the following table. File Commands Command

Description

SAV[E] filename [.ext] [REP[LACE]|APP[END]]

Saves current contents of SQL buffer to a file. Use APPEND to add to an existing file; use REPLACE to overwrite an existing file. The default file extension is .sql.

GET filename [.ext]

Writes the contents of a previously saved file to the SQL buffer. The default extension for the filename is .sql.

STA[RT] filename [.ext]

Runs a previously saved command file.

@ filename

Runs a previously saved command file (same as START).

EDIT

Invokes the editor and saves the buffer contents to a file named afiedt.buf.

ED[IT] [filename[.ext]]

Invokes editor to edit contents of a saved file.

SPO[OL] [filename[.ext]| OFF|OUT]

Stores query results in a file, OFF closes the spool file. OUT closes the spool file and sends the file results to the system printer.

EXIT

Leaves SQL*Plus.

Note: You can change the text editor invoked by changing the value of the SQL*Plus variable _EDITOR by using the DEFINE command. You can enter only one SQL*Plus command per SQL prompt. SQL*Plus commands are not stored in the buffer. To continue a SQL*Plus command on the next line, end the current line with a hyphen (-).

Selecting Rows

1Ć43

1Ć44

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Creating a Report COLUMN Command Control the display of a column in a report by using the COLUMN command. For example, you can change the heading, width, and format. Syntax COL[UMN] [{column|alias} [option ...]]

COLUMN Command Options Option

Description

CLE[AR]

Clears any column formats.

FOR[MAT] format

Changes the display of the column data.

HEA[DING] text

Sets the column heading. A vertical line (|) will force a line feed in the heading if you do not use justification.

JUS[TIFY] {align}

Justifies the column heading (not the data) to be left, center, or right.

NOPRI[NT]

Hides the column.

NUL[L] text

Specifies text to be displayed for null values.

PRI[NT]

Shows the column.

TRU[NCATED]

Truncates the string at the end of the first line of display.

WRA[PPED]

Wraps the end of the string to the next line.

WOR[D_WRAPPED]

Same as WRAPPED, but ensures that words do not split.

Format elements used in the FORMAT option are discussed on the next page. If you have a lengthy command, you can continue it on the next line by ending the current line with a hyphen (-).

Selecting Rows

1Ć45

1Ć46

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Creating a Report

continued

Display or Clear Settings To show or clear the current COLUMN command settings, use the following commands: Command

Description

COL[UMN] column

Displays the current settings for the specified column.

COL[UMN]

Displays the current settings for all columns.

COL[UMN] column CLE[AR] Clears the settings for the specified column. CLE[AR] COL[UMN]

Selecting Rows

Clears the settings for all columns.

1Ć47

1Ć48

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Creating a Report

continued

Sample Column Format Model Elements Element Description

Example

Result

An

Sets a display width of n for character and date columns.

N/A

N/A

9

Represents a single zero-suppression digit.

999999

1234

0

Enforces leading zero.

099999

01234

$

Represents a floating dollar sign.

$9999

$1234

L

Represents local currency.

L9999

L1234

.

Represents the position of the decimal point.

9999.99

1234.00

,

Represents the thousand separator.

9,999

1,234

The Oracle7 Server displays a string of pound signs (#) in place of a whole number whose digits exceed the number of digits provided in the format model. It will also display pound signs in place of a value whose format model is alphanumeric, but whose actual value is numeric.

Selecting Rows

1Ć49

1Ć50

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary In this lesson, you have learned about retrieving information from a database table with the SELECT statement. SELECT [DISTINCT] {*, column [alias], ...} FROM table; where: SELECT

is a list of at least one column.

DISTINCT

suppreses duplicates.

*

selects all columns.

column

selects the named column.

alias

gives selected column a different heading.

FROM table

specifies the table containing the columns.

SQL*Plus SQL*Plus is an execution environment you can use to send SQL commands to the database server and to edit and to save SQL commands. Commands may be executed from the SQL prompt or from a script file.

Selecting Rows

1Ć51

1Ć52

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview This is the first of many practices. In most cases, the questions show the output that is required. The solutions (if you require them) can be found in Appendix A. Practices are intended to introduce all topics covered in the lesson. The first six questions are paper–based. In any practice, there may be “if you have time” questions. Do these only if you have completed all other questions within the allocated time and would like a further challenge to your skills. Practice Contents D

Selecting all data from different tables.

D

Describing the structure of tables.

D

Performing arithmetic calculations and specifying different column names.

D

Editing commands in the buffer.

Please take this practice slowly and precisely. You can experiment with saving and running command files. If you have any questions at any time, please attract the instructor’s attention. Paper-Based Questions For questions 1–2 circle either True or False.

Selecting Rows

1Ć53

1Ć54

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 1 1. 2. 3.

SQL commands are always held in a buffer. True / False SQL*Plus commands assist with querying data. True / False Show the structure of the S_DEPT table. Select all information from the S_DEPT table. ID --------10 31 32 33 34 35 41 42 43 44 45 50

NAME REGION_ID ------------------------- --------Finance 3 Sales 1 Sales 2 Sales 3 Sales 4 Sales 5 Operations 1 Operations 2 Operations 3 Operations 4 Operations 5 Administration 1

12 rows selected. 4.

Show the structure of the S_CUSTOMER table. Using this table, perform the following actions. a.

Retrieve all information from the S_CUSTOMER table.

b.

Display the name and phone number for each customer.

Selecting Rows

1Ć55

Practice 1

continued

4.—Continued c.

Display the phone number and name for each customer, with phone number appearing first. PHONE -------------------55-2066101 81-20101 91-10351 1-206-104-0103 852-3692888 33-2257201 234-6036201 49-527454 809-352689 52-404562 42-111292 20-1209211 1-415-555-6281 1-716-555-7171 7-3892456

NAME ------------------------Unisports Simms Atheletics Delhi Sports Womansport Kam’s Sporting Goods Sportique Sweet Rock Sports Muench Sports Beisbol Si! Futbol Sonora Kuhn’s Sports Hamada Sport Big John’s Sports Emporium Ojibway Retail Sporta Russia

15 rows selected.

1Ć56

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

2 Limiting Selected Rows

2Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives While retrieving data from the database, you may need to restrict the rows of data that are displayed or specify the order in which the rows are displayed. This lesson explains the commands you will use to perform these actions. At the end of this lesson you should be able to D

Sort row output using the ORDER BY clause.

D

Enter search criteria using the WHERE clause.

Limiting Selected Rows

2Ć3

If the ORDER BY clause is not used, the sort order is undefined, and the Oracle7 Server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order.

2Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Ordering Rows with the ORDER BY Clause The order of rows returned in a query result is undefined. The ORDER BY clause may be used to sort the rows. If used, you must place the ORDER BY clause last. You can specify an expression or use position to sort. Syntax SELECT expr FROM table [ORDER BY {column,expr} [ASC|DESC]]; where: ORDER BY

specifies the order in which the retrieved rows are displayed.

ASC

orders the rows in ascending order. This is the default order.

DESC

orders the rows in descending order.

Example Query the employee table for employee last name, department number, and the hire date for all employees. Sort the results by employee last name. SQL> SELECT last_name, dept_id, start_date 2 FROM s_emp 3 ORDER BY last_name;

LAST_NAME DEPT_ID START_DAT ------------ ------- --------Biri 43 07-APR-90 Catchpole 44 09-FEB-92 Chang 44 30-NOV-90 Dancs 45 17-MAR-91 Dumas 35 09-OCT-91 Giljum 32 18-JAN-92 Havel 45 27-FEB-91 ...

Limiting Selected Rows

2Ć5

2Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Ordering Rows with the ORDER BY Clause

continued

Default Ordering of Data The default sort order is ascending: D Numeric values are displayed with the lowest values first, for example 1-999. D Date values are displayed with the earliest value first, for example 01-JAN-92 before 01-JAN-95. D Character values are displayed in alphabetical order, for example A first and Z last. D In Oracle7, null values are displayed last for ascending sequences and first for descending sequences. Reversing the Default Order To reverse the order in which rows are displayed, the command word DESC is specified after the column name in the ORDER BY clause. Example Query the employee table for employee last name, department number, and the hire date for all employees. Sort the results by the most recently hired employee. SQL> SELECT 2 FROM 3 ORDER BY

last_name, dept_id, start_date s_emp start_date DESC;

LAST_NAME DEPT_ID ------------ ------... Urguhart 41 Chang 44 Patel 34 Menchu 42 ... 25 rows selected.

START_DAT --------18-JAN-91 30-NOV-90 17-OCT-90 14-MAY-90

Ordering with Column Aliases You can use a column alias in the ORDER BY clause. This feature was made available in Oracle7 release 7.0.16.

Limiting Selected Rows

2Ć7

2Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Ordering Rows with the ORDER BY Clause

continued

Ordering by Position Another method for sorting query results is to sort by position. This is especially useful when sorting by a long expression. Rather than typing the expression again, you can specify its position in the SELECT list. SQL> SELECT last_name, salary*12 2 FROM s_emp 3 ORDER BY 2; Ordering by Many Columns You can sort query results by more than one column. The sort limit is the number of columns in the table. In the ORDER BY clause, specify the columns, and separate the column names using commas. If you want to reverse the order of a column, specify DESC after its name or position. You can order by columns that are not in the SELECT list. Example Display the last name, department number, and salary of all employees. Order the result by the department number, then in descending order by salary. SQL> SELECT last name, dept_id, salary 2 FROM s_emp 3 ORDER BY dept_id, salary DESC;

LAST_NAME DEPT_ID SALARY ------------ ------- ---------Quick-To-See 10 1450 Nagayama 31 1400 Magee 31 1400 Giljum 32 1490 Sedeghi 33 1515 Nguyen 34 1525 Patel 34 795 ...

Limiting Selected Rows

2Ć9

2Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Limiting Selected Rows with the WHERE Clause You can restrict the rows returned from the query by using the WHERE clause. A WHERE clause contains a condition that must be met, and directly follows the FROM clause. Syntax SELECT FROM [WHERE [ORDER BY

expr table condition(s)] expr];

where: WHERE

restricts the query to rows that meet a condition.

condition

is composed of column names, expressions, constants, and comparison operators.

Comparison Operators Comparison operators are divided into two categories: logical and SQL. They are used in the WHERE clause to compare one expression to another using the following format. Syntax ...WHERE expr operator value

Example WHERE conditions ...WHERE

Limiting Selected Rows

dept_id = 42

2Ć11

2Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Comparison Operators Character Strings and Dates Character strings and dates in the WHERE clause must be enclosed in single quotation marks (‘ ’). Number constants, however, must not. Example Write a query to show the first and last names, and title for the employee named “Magee.” SQL> SELECT 2 FROM 3 WHERE

first_name, last_name, title s_emp last_name = ’MAGEE’;

no rows selected All character strings are case sensitive. Therefore, change the last name to be initial capitals in order to acquire a match. SQL> SELECT 2 FROM 3 WHERE

first_name, last_name, title s_emp last_name = ’Magee’;

FIRST_NAME LAST_NAME TITLE ------------ ------------- --------------------Colin Magee Sales Representative

Limiting Selected Rows

2Ć13

2Ć14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Comparison Operators

continued

Comparison Operators Logical operators test the following conditions: Operator

Meaning

=

Equal to

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to

SQL Operators There are four SQL operators that operate with all datatypes: Operator

Meaning

BETWEEN...AND...

Between two values (inclusive)

IN(list)

Match any of a list of values

LIKE

Match a character pattern

IS NULL

Is a null value

Logical Operators Operator

Meaning

AND

If both component conditions return TRUE, then the result is TRUE.

OR

If either component condition returns TRUE, then the result is TRUE.

NOT

Returns the opposite condition.

Limiting Selected Rows

2Ć15

2Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Negating Expressions You may find that it is easier to find the rows that do not meet a condition, rather than those that do. Use the comparison and SQL operators with a negative expression. Negating Logical Operators Operator

Description

!=

Not equal to (VAX, UNIX, PC)

^= :=

Not equal to (IBM)

<>

Not equal to (all operating systems)

NOT colname =

Not equal to

NOT colname >

Not greater than

Negating SQL Operators Operator

Description

NOT BETWEEN...AND...

Not between two specified values

NOT IN (list)

Not in specified list of values

NOT LIKE

Not like comparison string

IS NOT NULL

Is not a null value

If you want to compare a known value to a null value, use either IS or IS NOT NULL comparison operators. If you compare null values using the other operators, the result is always FALSE. For example, COMMISSION_PCT! = NULL is always FALSE because a null value may not be either equal or unequal to any other value, even another null value. Note that an error is not raised, the result is simply always FALSE.

Limiting Selected Rows

2Ć17

SQL> SELECT 2 FROM 3 WHERE 4

2Ć18

first_name, last_name, start_date s_emp start_date BETWEEN ’09-may-91’ AND ’17-jun-91’;

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL Operators The BETWEEN Operator You can display rows based on a range of values using the BETWEEN operator. The range you specify contains a lower range and an upper range. Example Display the first name, last name, and start date of employees whose start date is between May 9, 1991 and June 17, 1991, inclusive. SQL> SELECT 2 FROM 3 WHERE 4

first_name, last_name, start_date s_emp start_date BETWEEN ’09-may-91’ AND ’17-jun-91’;

Values specified with the BETWEEN operator are inclusive. You must specify the lower limit first. The IN Operator To test for values in a specified list, use the IN operator. Example Display the department number, name, and region number of departments in regions 1 or 3. SQL> SELECT 2 FROM 3 WHERE

id, name, region_id s_dept region_id IN (1,3);

If characters or dates are used in the list, they must be enclosed in single quotation marks (‘ ’).

Limiting Selected Rows

2Ć19

SQL> SELECT 2 FROM 3 WHERE

2Ć20

last_name s_emp last_name LIKE ’M%’;

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL Operators

continued

The LIKE Operator You may not always know the exact value to search for. You can select rows that match a character pattern by using the LIKE operator. The character pattern matching operation is referred to as a wildcard search. Two symbols can be used to construct the search string. Symbol

Description

%

Represents any sequence of zero or more characters.

_

Represents any single character.

Example Display all employee last names beginning with “M.” SQL> SELECT FROM WHERE

last_name s_emp last_name LIKE ’M%’;

LAST_NAME -----------Menchu Magee Maduro Markarian

Example Display all employee last names that do not contain an “a” within the name. SQL> SELECT 2 FROM 3 WHERE

Limiting Selected Rows

last_name s_emp last_name NOT LIKE ’%a%’;

2Ć21

2Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL Operators

continued

The LIKE operator can be used as a shortcut for some BETWEEN comparisons. Example Display the last name and start date for employees who started with the company in 1991. SQL> SELECT 2 FROM 3 WHERE

last_name, start_date s_emp start_date LIKE ’%91’;

Combine Wildcard Characters The % and _ symbols may be used in any combination with literal characters. Example Display the last names of employees whose last name has an “a” as the second letter. SQL> SELECT 2 FROM 3 WHERE

last_name s_emp last_name LIKE ’_a%’;

The ESCAPE Option When you need to have an exact match for the actual “%” and “_” characters, use the ESCAPE option. This option specifies what the ESCAPE character is. Example Display the names of companies whose name contains “X_Y.” SQL> SELECT 2 FROM 3 WHERE

Limiting Selected Rows

name s_customer name LIKE ’%X\_Y%’ ESCAPE ’\’;

2Ć23

2Ć24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL Operators

continued

IS NULL Operator The IS NULL operator tests for values that are null. A null value means the value is unavailable, unassigned, unknown, or inapplicable. Therefore, you cannot test with “=” because a null value cannot be equal or unequal to any value. Example Display the customer number, name, and credit rating of all customers who do not have a sales representative. SQL> SELECT 2 FROM 3 WHERE

id, name, credit_rating s_customer sales_rep_id = NULL;

no rows selected

SQL> SELECT 2 FROM 3 WHERE

id, name, credit_rating s_customer sales_rep_id = ’’;

no rows selected

SQL> SELECT 2 FROM 3 WHERE

id, name, credit_rating s_customer sales_rep_id IS NULL;

ID NAME CREDIT_RA ------- -------------------- --------207 Sweet Rock Sports GOOD Example Display all employee last names, titles, and commission percentages who make a commission. SQL> SELECT 2 FROM 3 WHERE

Limiting Selected Rows

last_name, title, commission_pct s_emp commission_pct IS NOT NULL;

2Ć25

SQL> 2 3 4

2Ć26

SELECT FROM WHERE AND

last_name, salary, dept_id, title s_emp dept_id = 41 title = ’Stock Clerk’;

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Querying Data with Multiple Conditions You may need to specify complex criteria by combining several search conditions. The AND and OR operators may be used to make compound logical expressions. The AND operator returns TRUE if both conditions evaluate to TRUE, whereas the OR operator returns TRUE if either condition is TRUE. In the following two examples, the conditions are the same, but the operator is different. See how the result is dramatically changed. Example 1 Display the last name, salary, department number, and title for all stock clerks in department 41. SQL> 2 3 4

SELECT FROM WHERE AND

last_name, salary, dept_id, title s_emp dept_id = 41 title = ’Stock Clerk’;

Example 2 Display the last name, salary, department number, and title for all employees who are either stock clerks or who are in department 41. SQL> 2 3 4

SELECT FROM WHERE OR

last_name, salary, dept_id, title s_emp dept_id = 41 title = ’Stock Clerk’;

Note: OR is a less restrictive clause. Consequently, more rows may be returned. For more information, see Oracle7 Server SQL Reference, Release 7.3, “Logical Operators.”

Limiting Selected Rows

2Ć27

Order Evaluated

2Ć28

Operator

1

All comparison operators.

2

AND

3

OR

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Rules of Precedence You may combine AND and OR operators in the same logical expression. The results of all of the conditions are combined in the order determined by the precedence of the connecting operators. Where operators of equal precedence are used next to each other, they are performed from left to right. Each AND is performed first then each OR is performed. AND has a higher precedence than OR. Rules of Precedence Order Evaluated

Operator

1

All comparison operators (=, <>, >, >=, <, <=, IN, LIKE, IS NULL, BETWEEN)

2

AND

3

OR

Note: When using a negating expression, comparison operators still evaluate first. Override precedence rules by placing part of an expression in parentheses; the Oracle Server evaluates expressions in parentheses first. Whenever you are in doubt about which of two operations will be performed first when an expression is evaluated, use parentheses to clarify your statements.

Limiting Selected Rows

2Ć29

2Ć30

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Rules of Precedence

continued

Example 1 Display the last name, salary, and department number for those employees in department 44 who earn 1000 or more, as well as all employees in department 42. SQL> 2 3 4 5

SELECT FROM WHERE AND OR

last_name, salary, dept_id s_emp salary >= 1000 dept_id = 44 dept_id = 42;

LAST_NAME SALARY DEPT_ID ------------ ------------ -------Menchu 1250 42 Catchpole 1300 44 Nozaki 1200 42 Patel 795 42

Example 2 Display the last name, salary, and department number for those employees in department 44 or 42 who earn 1000 or more. SQL> 2 3 4 5

SELECT FROM WHERE AND OR

last_name, salary, dept_id s_emp salary >= 1000 (dept_id = 44 dept_id = 42);

LAST_NAME SALARY DEPT_ID ------------ ------------ -------Menchu 1250 42 Catchpole 1300 44 Nozaki 1200 42

Limiting Selected Rows

2Ć31

2Ć32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary In this lesson, you have learned about sorting rows and restricting the rows returned by the SELECT statement. You have also learned how to implement comparison operators. Syntax SELECT FROM [WHERE [ORDER BY

[DISTINCT] {*,column [alias],....} table condition(s)] {column,expr} [ASC|DESC]];

where: SELECT

is a list of at least one column.

DISTINCT

suppresses duplicates.

*

selects all columns.

column

selects the named column.

alias

gives selected columns a different heading.

FROM table

specifies the table containing the columns.

WHERE

restricts the query to rows that meet a condition.

condition

is composed of column names, expressions, constants, and comparison operators.

ORDER BY

specifies the order in which the retrieved rows are displayed.

ASC

orders rows in ascending order.

DESC

orders the rows in descending order.

Limiting Selected Rows

2Ć33

2Ć34

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview This practice gives you a variety of exercises using the WHERE clause and the ORDER BY clause. Practice Contents D

Selecting data and changing the order of rows displayed. Using the WHERE clause to restrict rows, with a combination of logical and SQL operators.

D

Using column aliases.

D

PaperĆBased Questions For questions 1–3, circle either True or False.

Limiting Selected Rows

2Ć35

2Ć36

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 2 1. 2.

You cannot order by a column that you have not selected. True / False This SELECT statement will execute successfully. True / False SQL> Select 2 From 3 Where

3.

This SELECT statement will execute successfully. True / False SQL> select 2 from 3 where

4.

last_name, title, salary Ann_sal s_emp last_name = ’Dancs’;

* s_emp salary*12 = 9600;

There are four coding errors in this statement. Can you identify them? SQL> 2 3 4 5

SELECT FROM WHERE AND

Limiting Selected Rows

id, last_name, salary x 12 ANNUAL SALARY s_emp sal > 3000 start_date LIKE %84;

2Ć37

Practice 2 5.

continued

Use the S_CUSTOMER table and perform the following actions. a. Create a query to display the name, customer number, and credit rating for all companies represented by sales representative 11. Save your SQL statement to a file named p2q5. b.

Run your query in the file p2q5.

c.

Load p2q5 into the SQL buffer. Name the column headings Company, Company ID, and Rating. Rerun your query. Re-save your query as p2q5. Company Company ID -------------------------- ––––––––––– Womansport 204 Beisbol Si! 209 Big John’s Sports Emporium 213 Ojibway Retail 214

d. 6.

Rating –––––––– GOOD GOOD GOOD GOOD

Retrieve p2q5 into the SQL buffer. Order the query results in descending order by customer number. Run your query.

Show the structure of the S_EMP table. a. Display the user name for employee 23. b.

Display the first name, last name, and department number of the employees in departments 10 and 50 in alphabetical order of last name. Merge the first name and last name together, and title the column Employees. Employees ----------------------------------Mark Quick-To-See Audry Ropeburn Carmen Velasquez

DEPT_ID --------10 50 50

c.

Display all employees whose last names contain an “s.”

d.

Display the user names and start date of employees hired between May 14, 1990 and May 26, 1991. Order the query results by start date ascending order.

2Ć38

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 2

continued

If you have time, complete the following exercises. 7.

8.

Use the S_EMP table to perform the following actions. a.

Write a query to show the last name and salary of all employees who are not making between 1000 and 2500 per month.

b.

List the last name and salary of employees who earn more than 1350 who are in department 31, 42, or 50. Label the last name column Employee Name, and label the salary column Monthly Salary.

c.

Display the last name and start date of every employee who was hired in 1991.

d.

Display the full name of all employees with no manager.

Show the structure of S_PRODUCT table. a.

Alphabetically display all products having a name beginning with Pro.

b.

Display all product names and short descriptions for all descriptions containing the word “bicycle.”

c.

Display all short descriptions. Compare the results from Exercise 8b. Did your response in Exercise 8b return all descriptions containing bicycle?

Limiting Selected Rows

2Ć39

2Ć40

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

3 Single Row Functions

3Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives Functions make the basic query block more powerful and are used to manipulate data values. This is the first of two lessons that explore functions. You will focus on single row character, number, and date functions, as well as those functions that convert data from one type to another, for example, character data to numeric. At the end of this lesson, you should be able to D

Explain the various types of functions available in SQL.

D

Identify the basic concepts of using functions.

D

Use a variety of character, number, and date functions in SELECT statements.

D

Explain the conversion functions and how they might be used.

Single Row Functions

3Ć3

3Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview Functions are a very powerful feature of SQL and can be used to D

Perform calculations on data.

D

Modify individual data items.

D

Manipulate output for groups of rows.

D

Alter date formats for display.

D

Convert column datatypes.

There are two distinct types of functions: D

Single row functions.

D

Multiple row functions.

Single Row Functions These functions operate on single rows only, and return one result per row. There are different types of single row functions. We will cover those listed below.

D

Character Number Date

D

Conversion

D D

Multiple Row Functions These functions manipulate groups of rows to give one result per group of rows. For more information, see Oracle7 Server SQL Reference, Release 7.3 for the complete list of available functions and syntax.

Single Row Functions

3Ć5

3Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Single Row Functions Single row functions are used to manipulate data items. They accept one or more arguments and return one value for each row returned by the query. An argument may be one of the following: D

A user-supplied constant

D

A variable value A column name An expression

D D

Features of Single Row Functions D D

They act on each row returned in the query. They return one result per row.

D

They may return a data value of a different type than that referenced. They may expect one or more user arguments. You can nest them.

D

You can use them in SELECT, WHERE, and ORDER BY clauses.

D D

Syntax function_name (column|expression, [arg1, arg2,...]) where: function_name

is the name of the function.

column

is any named database column.

expression

is any character string or calculated expression.

arg1, arg2

is any argument to be used by the function.

Single Row Functions

3Ć7

3Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Character Functions Single row character functions accept character data as input and can return both character and number values. Function

Purpose

LOWER(column|expression)

Converts alpha character values to lowercase.

UPPER(column|expression)

Converts alpha character values to uppercase.

INITCAP(column|expression)

Converts alpha character values to uppercase for the first letter of each word, all other letters in lowercase.

CONCAT(column1|expression1, column2|expression2)

Concatenates the first character value to the second character value. Equivalent to concatenation operator (||).

SUBSTR(column|expression,m[,n])

Returns specified characters from character value starting at character position m, n characters long. If m is negative, the count starts from the end of the character value.

LENGTH(column|expression)

Returns the number of characters in value.

NVL(column|expression1,column|ex Converts the the first value if null to the pression2) second value. Note: This list is a subset of the available character functions. For more information, see Oracle7 Server SQL Reference, Release 7.3, “Character Functions.”

Single Row Functions

3Ć9

3Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Character Functions

continued

Example Display the first and last name in lowercase, userid in initial capitalization, and title in uppercase for all vice presidents. SQL> SELECT 2 3 4 FROM 5 WHERE

LOWER(first_name||’ ’||last_name) VP, INITCAP(userid) USERID, UPPER(title) TITLE s_emp title LIKE ’VP%’;

VP --------------------ladoris ngao midori nagayama mark quick-to-see audry ropeburn

Single Row Functions

USERID -------Lngao Mnagayam Mquickto Aropebur

TITLE ------------------VP, OPERATIONS VP, SALES VP, FINANCE VP, ADMINISTRATION

3Ć11

3Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Character Functions

continued

Example Display the first name and last name of all employees with the last name of Patel. SQL> SELECT 2 FROM 3 WHERE

first_name, last_name s_emp last_name = ’PATEL’;

no rows returned

SQL> SELECT 2 FROM 3 WHERE

first_name, last_name s_emp UPPER(last_name) = ’PATEL’;

FIRST_NAME -------------------Vikram Radha

LAST_NAME -------------------Patel Patel

Note: The name is displayed as it was stored in the database. To display the name in uppercase, the UPPER function must be used in the SELECT clause as well.

Single Row Functions

3Ć13

3Ć14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Character Functions

continued

Example Display the name and country of all customers with a good credit rating. Concatenate name and country. SQL> SELECT 2 FROM 3 WHERE

CONCAT (name, country) CUSTOMER s_customer credit_rating = ’GOOD’;

CUSTOMER ------------------------------------------------Delhi SportsIndia Sweet Rock SportsNigeria

Example Display the product name and length of name for all products where the first three characters are Ace. SQL> SELECT 2 FROM 3 WHERE

name, LENGTH(name) s_product SUBSTR(name,1,3) = ’Ace’;

NAME LENGTH(NAME) -------------------- -----------Ace Ski Boot 12 Ace Ski Pole 12

Single Row Functions

3Ć15

3Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Number Functions Number functions accept numeric input and return numeric values. This section describes some of the number functions. Function

Purpose

ROUND(column|expression,n)

Rounds the column, expression, or value to n decimal places. If n is omitted, no decimal places. If n is negative, numbers to left of the decimal point are rounded.

TRUNC(column|expression,n)

Truncates the column or value to n decimal places, or if n is omitted, no decimal places. If n is negative, numbers left of the decimal point are truncated to zero.

MOD(m,n)

Returns the remainder of m divided by n.

Note: This list is a subset of the available number functions. For more information, see Oracle7 Server SQL Reference, Release 7.3, “Number Functions.”

Single Row Functions

3Ć17

3Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Number Functions

continued

The TRUNC and ROUND functions work with similar arguments. If the second argument is 0 or is missing, then the value is truncated or rounded to zero decimal places. If the second argument is 2, then the value is truncated or rounded to two decimal places, or to the hundredths. Conversely, if the second argument is -2, then the value is truncated or rounded to two decimal places to the left, or to the hundreds. Note: ROUND and TRUNC may also be used with date functions. You will see examples later in this lesson. Example Display the value 45.923 rounded to the hundredth, no, and ten decimal places. SQL> SELECT 2 3 FROM

ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) SYS.DUAL;

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1) --------------- --------------- ---------------45.92 46 50

Example Display the value 45.923 truncated to the hundredth, no, and ten decimal places. SQL> SELECT 2 3 FROM

TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) SYS.DUAL;

TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1) --------------- --------------- ---------------45.92 45 40

SYS.DUAL is a dummy table. It will be covered in detail later in this lesson.

Single Row Functions

3Ć19

3Ć20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Number Functions

continued

Example Calculate the remainder of the ratio of salary to commission for all employees whose salary is more than 1400. SQL> SELECT 2 FROM 3 WHERE

last_name, MOD(salary,commission_pct) s_emp salary > 1400;

LAST_NAME MOD(SALARY,COMMISSION_PCT) ------------ -------------------------Velasquez Ngao Quick-To-See Ropeburn Giljum 2.5 Sedeghi 5 Nguyen 10 Dumas 15 8 rows selected.

Single Row Functions

3Ć21

3Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Oracle Date Format Oracle Date Storage Oracle stores dates in an internal numeric format, representing the following: D D D D D

Century Year Month Day Hours

Minutes D Seconds The default display and input format for any date is DD-MON-YY. Valid Oracle dates are between January 1, 4712 B.C. and December 31, 4712 A.D. D

SYSDATE SYSDATE is a date function that returns the current date and time. You can use SYSDATE just as you would use any other column name. For example, you can display the current date by selecting SYSDATE from a table. It is customary to select SYSDATE from a dummy table called DUAL. DUAL The DUAL table is owned by the user SYS and may be accessed by all users. It contains one column, DUMMY, and one row with the value “X.” The DUAL table is useful when you want to return a value once only, for instance, the value of a constant, pseudo-column, or expression that is not derived from a table with user data. Example Display the current date using the DUAL table. SQL> SELECT 2 FROM

Single Row Functions

SYSDATE SYS.DUAL;

3Ć23

3Ć24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Using Arithmetic Operators with Dates Since the database stores dates as numbers, you can perform calculations using arithmetic operators such as addition and subtraction. You can add and subtract number constants as well as dates. Arithmetic Operations on Dates You can perform the following operations: Operation

Result

Description

date + number

date

Adds a number of days to a date.

date - number

date

Subtracts a number of days from a date.

date - date

number of days

Subtracts one date from another.

date + number/24

date

Adds a number of hours to a date.

Example For employees in department 43, display the last name and number of weeks employed. SQL> SELECT 2 FROM 3 WHERE

LAST_NAME -----------Biri Markarian Newman

last_name, (SYSDATE-start_date)/7 WEEKS s_emp dept_id = 43;

WEEKS ---------297.226498 238.083641 230.083641

Note: SYSDATE is a SQL function that returns the current date and time. Your results may differ from the examples.

Single Row Functions

3Ć25

3Ć26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Date Functions Date functions operate on Oracle dates. All date functions return a value of DATE datatype except MONTHS_BETWEEN, which returns a numeric value. Function

Purpose

MONTHS_BETWEEN(date1, date2)

Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The non-integer part of the result represents a portion of the month.

ADD_MONTHS(date,n)

Adds n number of calender months to date. n must be an integer and can be negative

NEXT_DAY(date,‘char’)

Finds the date of the next specified day of the week (‘char’) following date. char may be a number representing a day or a character string.

LAST_DAY(date)

Finds the date of the last day of the month that contains date.

ROUND(date[,‘fmt’])

Finds the date of first day of the month contained in date when no format model fmt is specified. If fmt = YEAR, finds first day of year containing date. This is useful when comparing dates that may have different times.

TRUNC(date[,‘fmt’])

Returns date with the time set to midnight if no format model fmt is specified. This function is useful when you want to remove the time portion of the date.

Note: This list is a subset of the available date functions. The format models are covered later in this chapter. Examples of format models are month or year. For more information, see Oracle7 Server SQL Reference, Release 7.3, “Date Functions.”

Single Row Functions

3Ć27

3Ć28

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Date Functions

continued

Example For all employees employed less than 48 months, display the employee number, start date, number of months employed, and the 6 month review date. SQL> SELECT id, start_date, 2 MONTHS_BETWEEN (SYSDATE,start_date) TENURE, 3 ADD_MONTHS(start_date,6) REVIEW 4 FROM s_emp 5 WHERE MONTHS_BETWEEN (SYSDATE,start_date)<48;

ID -----9 12 14 16

START_DAT --------09-FEB-92 18-JAN-92 22-JAN-92 07-FEB-92

TENURE ---------44.1163486 44.826026 44.6969937 44.1808647

REVIEW --------09-AUG-92 18-JUL-92 22-JUL-92 07-AUG-92

Example For inventory items that have been restocked, display in chronological order the product number, first Friday after the restock date, and the last day of the month when it was restocked. SQL> 2 3 4 5 6

SELECT

product_id, NEXT_DAY (restock_date,’FRIDAY’), LAST_DAY (restock_date) FROM s_inventory WHERE restock_date IS NOT NULL ORDER BY restock_date;

PRODUCT_ID ---------30433 40422 50532

NEXT_DAY( --------11-SEP-92 12-FEB-93 16-APR-93

Single Row Functions

LAST_DAY( --------30-SEP-92 28-FEB-93 30-APR-93

3Ć29

3Ć30

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Date Functions

continued

The ROUND and TRUNC functions can be used for number and date values. When using these functions with dates, they round or truncate to the specified format model. Therefore, you can round dates to the nearest year or month. Example Compare the start dates for all employees who started in 1991. Display the employee number, start date, and month started using both the ROUND and TRUNC functions. SQL> SELECT 2 3 4 FROM 5 WHERE

ID -----3 6 10 13 15 18 19 20 21 24 25

id, start_date, ROUND(start_date, ’MONTH’), TRUNC(start_date, ’MONTH’) s_emp start_date LIKE ’%91’;

START_DAT --------17-JUN-91 18-JAN-91 27-FEB-91 18-FEB-91 09-OCT-91 09-FEB-91 06-AUG-91 21-JUL-91 26-MAY-91 17-MAR-91 09-MAY-91

ROUND(STA --------01-JUL-91 01-FEB-91 01-MAR-91 01-MAR-91 01-OCT-91 01-FEB-91 01-AUG-91 01-AUG-91 01-JUN-91 01-APR-91 01-MAY-91

TRUNC(STA --------01-JUN-91 01-JAN-91 01-FEB-91 01-FEB-91 01-OCT-91 01-FEB-91 01-AUG-91 01-JUL-91 01-MAY-91 01-MAR-91 01-MAY-91

11 rows selected.

Single Row Functions

3Ć31

3Ć32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Conversion Functions SQL provides three functions to convert a value from one datatype to another. Function

Purpose

TO_CHAR(number|date,[‘fmt’])

Converts a number or date value to a VARCHAR2 character string with format model fmt.

TO_NUMBER(char)

Converts a character string containing digits to a number.

TO_DATE(char,[‘fmt’])

Converts a character string representing a date to a date value according to the fmt specified. If fmt is omitted, format is DD-MON-YY.

Note: This list is a subset of the available conversion functions. For more information, see Oracle7 Server SQL Reference, Release 7.3, “Conversion Functions.”

Single Row Functions

3Ć33

3Ć34

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

TO_CHAR Function with Date Formats Displaying a Date in a Specific Format Previously, all Oracle date values were displayed in the DD-MON-YY format. The TO_CHAR function allows you to convert a date from this default format to one specified by you. Guidelines D

The format model must be enclosed in single quotation marks and is case-sensitive.

D

The format model can include any valid date format element. Be sure to separate the date value from the format model by a comma.

D

The names of days and months in the output are automatically padded with blanks.

D

To remove padded blanks or to suppress leading zeros, use the fill mode fm element.

D

You can resize the display width of the resulting character field with the SQL*Plus COLUMN command.

D

The resultant column width is 80 characters by default.

Example Display the order number and date ordered for all orders taken by sales representative 11. Format the date to display the date as 08/92. SQL> 2 3

SELECT FROM WHERE

ID ------100 105 109 110 111

ID,TO_CHAR(date_ordered,’MM/YY’) ORDERED s_ord sales_rep_id = 11;

ORDERED ------------------08/92 09/92 09/92 09/92 09/92

Single Row Functions

3Ć35

3Ć36

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

TO_CHAR Function with Date Formats

continued

Sample Valid Date Format Elements Element

Description

SCC or CC

Century; S prefixes BC date with -.

Years in dates YYYY or SYYYY

Year; S prefixes BC date with -.

YYY or YY or Y

Last 3, 2, or 1 digit(s) of year.

Y,YYY

Year with comma in this position.

IYYY, IYY, IY, I

4, 3, 2, or 1 digit year based on the ISO standard.

SYEAR or YEAR

Year spelled out; S prefixes BC date with -.

BC or AD

BC/AD indicator.

B.C. or A.D.

BC/AD indicator with periods.

Q

Quarter of year.

MM

Month, 2-digit value.

MONTH

Name of month padded with blanks to length of 9 characters.

MON

Name of month, 3-letter abbreviation.

RM

Roman numeral month.

WW or W

Week of year or month.

DDD or DD or D

Day of year, month, or week.

DAY

Name of day padded with blanks to length of 9 characters.

DY

Name of day; 3-letter abbreviation.

J

Julian day; the number of days since 31 December 4713 BC.

Single Row Functions

3Ć37

3Ć38

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

TO_CHAR Function with Date Formats

continued

Time Formats Use the formats listed in the following tables to display time information and literals, and to change numerals to spelled numbers. Element

Description

AM or PM

Meridian indicator.

A.M. or P.M.

Meridian indicator with periods.

HH or HH12 or HH24

Hour of day or hour (1-12) or hour (0-23)

MI

Minute (0-59).

SS

Second (0-59).

SSSSS

Seconds past midnight (0-86399).

Other Formats Element

Description

/ . ,

Punctuation is reproduced in the result.

“ of the ”

Quoted string is reproduced in the result.

Using the FM Prefix The FM prefix suppresses blank padding in month and day names, leaving a variable-length result. It also suppresses leading zeros in numbers. A second occurrence of FM turns blank padding on again. Specifying Suffixes to Influence Number Display Suffix

Description

TH

Ordinal number (for example, DDTH for 4TH).

SP

Spelled-out number (for example, DDSP for FOUR).

SPTH or THSP

Spelled-out ordinal numbers (for example, DDSPTH for FOURTH).

Single Row Functions

3Ć39

3Ć40

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

TO_CHAR Function with Date Formats

continued

The RR Date Format Element The RR date format is similar to the YY element, but it allows you to specify different centuries. You can use the RR date format element instead of YY, so that the century of the return value varies according to the specified two-digit year and the last two digits of the current year. The table on the opposite page summarizes the behavior of the RR element. Current Year

Given Date

Interpreted (RR)

Interpreted (YY)

1994

27-OCT-95

1995

1995

1994

27-OCT-17

2017

1917

2001

27-OCT-17

2017

2017

Single Row Functions

3Ć41

3Ć42

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

TO_CHAR Function with Date Formats

continued

Example Display the names and hire dates for all employees hired in 1991. The hire date should look like 7 of February 1991. SQL> SELECT 2 3 FROM 4 WHERE

LAST_NAME -----------Nagayama Urguhart Havel ...

last_name, TO_CHAR(start_date, ’fmDD ”of” Month YYYY’) HIREDATE s_emp start_date LIKE ’%91’;

HIREDATE -------------------17 of June 1991 18 of January 1991 27 of February 1991

Example Modify the above example to display the dates in a format that looks like Seventh of February 1991 08:00:00 AM. SQL> SELECT last_name, TO_CHAR(start_date, 2 ’fmDdspth ”of” Month YYYY fmHH:MI:SS AM’) 3 HIREDATE 4 FROM s_emp 5 WHERE start_date LIKE ’%91’;

LAST_NAME -----------Nagayama Urguhart Havel ...

Single Row Functions

HIREDATE -----------------------------------Seventeenth of June 1991 12:00:00 AM Eighteenth of January 1991 12:00:00 AM Twenty-Seventh of February 1991 12:00:00

3Ć43

3Ć44

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

TO_CHAR Function with Number Formats When working with number values such as character strings, you should convert those numbers to the character datatype using the TO_CHAR function, which translates a value of NUMBER datatype to VARCHAR2 datatype. This technique is especially useful with concatenation. Number Format Elements If you are converting a character datatype that converts digits into a number, you can use the elements listed below. Element

Description

Example

Result

9

Numeric position (number of 9s determine display width).

999999

1234

0

Display leading zeros.

099999

001234

$

Floating dollar sign.

$999999

$1234

L

Floating local currency symbol.

L999999

FF1234

.

Decimal point in position specified.

999999.99

1234.00

,

Comma in position specified.

999,999

1,234

MI

Minus signs to right (negative values). 999999MI

1234-

PR

Parenthesize negative numbers.

999999PR

<1234>

EEEE

Scientific notation (format must specify four Es).

99.999EEEE

1.234E+03

V

Multiply by 10 n times (n=no of 9s after V).

9999V99

123400

B

Display zero values as blank, not 0.

B9999.99

1234.00

Single Row Functions

3Ć45

3Ć46

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

TO_CHAR Function with Number Formats

continued

Example Display a message indicating that the order, represented by its number, was filled for all orders shipped on September 21, 1992. Be sure to include the total for the order. SQL> SELECT 2 3 4 FROM 5 WHERE

’Order ’||TO_CHAR(id)|| ’ was filled for a total of ’ ||TO_CHAR(total,’fm$9,999,999’) s_ord date_shipped = ’21-SEP-92’;

NOTE -----------------------------------------------------Order 107 was filled for a total of $142,171 Order 110 was filled for a total of $1,539 Order 111 was filled for a total of $2,770

Guidelines D

The Oracle7 Server displays a string of pound signs (#) in place of a whole number whose digits exceed the number of digits provided in the format model.

D

The Oracle7 Server rounds the stored decimal value to the number of decimal spaces provided in the format model.

Single Row Functions

3Ć47

3Ć48

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

TO_NUMBER and TO_DATE Functions You may want to convert a character string to either a number or a date format. To accomplish this task, you use the TO_NUMBER or TO_DATE functions, respectively. The format model you choose will be based on the previously demonstrated format elements. Example Show all orders placed on September 7, 1992. Convert the date string you see here to the date format. SQL> SELECT id, total, date_ordered 2 FROM s_ord 3 WHERE date_ordered = 4 TO_DATE(’September 7, 1992’,’Month dd, YYYY’);

ID TOTAL DATE_ORDE ------ ---------- --------106 15634 07-SEP-92 107 142171 07-SEP-92 108 149570 07-SEP-92

Single Row Functions

3Ć49

Step 1 = Result 1 Step 2 = Result 2 Step 3 = Result 3

3Ć50

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Nesting Single Row Functions Nesting Functions Single row functions can be nested to any depth. Nested functions are evaluated from the innermost level to the outermost level. Some examples follow to show you the flexibility of these functions. Example Display the last name in upper case and part of the department name of all vice presidents as a string under the title Vice Presidents. SQL> SELECT 2 3 FROM 4 WHERE

CONCAT(UPPER(last_name), SUBSTR(title,3)) ”Vice Presidents” s_emp title LIKE ’VP%’;

Vice Presidents ---------------------------------------------------NGAO, Operations NAGAYAMA, Sales QUICK-TO-SEE, Finance ROPEBURN, Administration

Single Row Functions

3Ć51

3Ć52

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Nesting Functions Example Display the head of the company, who has no manager. Display that there is no manager number for that name. SQL> SELECT 2 3 FROM 4 WHERE

last_name, NVL(TO_CHAR(manager_id),’No Manager’) s_emp manager_id IS NULL;

LAST_NAME NVL(TO_CHAR(MANAGER_ID),’NOMANAGER’) ------------ ---------------------------------------Velasquez No Manager Notice that the entire expression becomes the column heading since no column alias was given. Example Display the date of the next Friday that is six months from the order date. The resultant date should look like Friday, March 12th, 1993. SQL> SELECT 2 3 4 5 FROM 6 ORDER BY

TO_CHAR(NEXT_DAY(ADD_MONTHS (date_ordered,6),’FRIDAY’), ’fmDay, Month ddth, YYYY’) ”New 6 Month Review” s_ord date_ordered;

New 6 Month Review ---------------------------------... Friday, March 5th, 1993 Friday, March 5th, 1993 Friday, March 12th, 1993 ...

Single Row Functions

3Ć53

3Ć54

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary Single Row Functions Single row functions can be nested to any level. Single row functions can manipulate D

Character data. D

D

Number data. D

D

LOWER, UPPER, INITCAP, CONCAT, SUBSTR, LENGTH ROUND, TRUNC, MOD

Date data. D

MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC

D

Date values can also use arithmetic operators

Conversion functions can convert character, date, and numeric values. D TO_CHAR, TO_DATE, TO_NUMBER SYSDATE and DUAL SYSDATE is a date function that returns the current date and time. It is customary to select SYSDATE from a dummy table called DUAL.

Single Row Functions

3Ć55

3Ć56

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview This practice is designed to give you a variety of exercises using different functions available for character, number, and date datatypes. Practice Contents D

Creating queries that require the use of numeric, character, and date functions

D

Using concatenation with functions

D

Writing case-insensitive queries to test the usefulness of character functions if you are unsure exactly how character date is stored

D

Performing calculations of years and months of service for an employee

D

Determining the review date for an employee

Remember that for nested functions, the results are evaluated from the innermost function to the outermost function.

Single Row Functions

3Ć57

3Ć58

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 3 1.

Single row functions work on many rows to produce a single result. True / False

2.

You can use all of the arithmetic operators on date values. True / False

3.

What is the name of the function that holds the current date?

4.

Display the employee number, last name, and salary increased by 15% and expressed as a whole number. ID -----1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

LAST_NAME NEW SALARY ------------ -----------------Velasquez 2875 Ngao 1668 Nagayama 1610 Quick-To-See 1668 Ropeburn 1783 Urguhart 1380 Menchu 1438 Biri 1265 Catchpole 1495 Havel 1503 Magee 1610 Giljum 1714 Sedeghi 1742 Nguyen 1754 Dumas 1668 Maduro 1610 Smith 1081 Nozaki 1380 Patel 914 Newman 863 Markarian 978 Chang 920 Patel 914 Dancs 989 Schwartz 1265

25 rows selected.

Single Row Functions

3Ć59

Practice 3 5.

continued

Display the employee last name and title in parentheses for all employees. The report should look like the output below. EMPLOYEE ---------------------------------------------------Biri(Warehouse Manager) Catchpole(Warehouse Manager) Chang(Stock Clerk) Dancs(Stock Clerk) Dumas(Sales Representative) Giljum(Sales Representative) Havel(Warehouse Manager) Maduro(Stock Clerk) Magee(Sales Representative) Markarian(Stock Clerk) Menchu(Warehouse Manager) Nagayama(Vp, Sales) Newman(Stock Clerk) Ngao(Vp, Operations) Nguyen(Sales Representative) Nozaki(Stock Clerk) Patel(Stock Clerk) Patel(Stock Clerk) Quick-To-See(Vp, Finance) Ropeburn(Vp, Administration) Schwartz(Stock Clerk) Sedeghi(Sales Representative) Smith(Stock Clerk) Urguhart(Warehouse Manager) Velasquez(President) 25 rows selected.

3Ć60

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 3 6.

continued

Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Format the dates to appear in the format similar to Eighth of May 1992. LAST_NAME -----------Velasquez Ngao Nagayama Quick-To-See Ropeburn Urguhart Menchu Biri Catchpole Havel Magee Giljum Sedeghi Nguyen Dumas Maduro Smith Nozaki Patel Newman Markarian Chang Patel Dancs Schwartz

START_DAT --------03-MAR-90 08-MAR-90 17-JUN-91 07-APR-90 04-MAR-90 18-JAN-91 14-MAY-90 07-APR-90 09-FEB-92 27-FEB-91 14-MAY-90 18-JAN-92 18-FEB-91 22-JAN-92 09-OCT-91 07-FEB-92 08-MAR-90 09-FEB-91 06-AUG-91 21-JUL-91 26-MAY-91 30-NOV-90 17-OCT-90 17-MAR-91 09-MAY-91

REVIEW -----------------------------Tenth of September 1990 Tenth of September 1990 Twenty-Third of December 1991 Eighth of October 1990 Tenth of September 1990 Twenty-Second of July 1991 Nineteenth of November 1990 Eighth of October 1990 Tenth of August 1992 Second of September 1991 Nineteenth of November 1990 Twentieth of July 1992 Nineteenth of August 1991 Twenty-Seventh of July 1992 Thirteenth of April 1992 Tenth of August 1992 Tenth of September 1990 Twelfth of August 1991 Tenth of February 1992 Twenty-Seventh of January 1992 Second of December 1991 Third of June 1991 Twenty-Second of April 1991 Twenty-Third of September 1991 Eleventh of November 1991

25 rows selected.

Single Row Functions

3Ć61

Practice 3 7.

continued

Display the product name for products that have “ski” in the name. NAME -------------------Ace Ski Boot Pro Ski Boot Bunny Ski Pole Ace Ski Pole Pro Ski Pole

3Ć62

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 3

continued

If you have time, complete the following exercises. 8.

For each employee, calculate the number of months between today and the date the employee was hired. Order your result by the number of months employed. Round the number of months up to the closest whole number.

LAST_NAME MONTHS_WORKED ------------ ------------Catchpole 46 Maduro 46 Nguyen 47 Giljum 47 Dumas 50 Patel 52 Newman 53 Nagayama 54 Markarian 55 Schwartz 55 Dancs 57 Havel 58 Sedeghi 58 Nozaki 58 Urguhart 59 Chang 61 Patel 62 Menchu 67 Magee 67 Quick-To-See 68 Biri 68 Ngao 69 Smith 69 Ropeburn 69 Velasquez 69 25 rows selected.

Note: Your MONTHS_WORKED may differ from the solution because your SYSDATE may return a different value.

Single Row Functions

3Ć63

Practice 3 9.

continued

Display the last name for all employees and the day of the week they started. Order the results by the day of the week starting with Monday. LAST_NAME -----------Nagayama Menchu Sedeghi Magee Patel Havel Patel Nguyen Dumas Ngao Smith Schwartz Urguhart Chang Maduro Velasquez Quick-To-See Biri Nozaki Giljum Ropeburn Newman Dancs Markarian Catchpole

START_DAT --------17-JUN-91 14-MAY-90 18-FEB-91 14-MAY-90 06-AUG-91 27-FEB-91 17-OCT-90 22-JAN-92 09-OCT-91 08-MAR-90 08-MAR-90 09-MAY-91 18-JAN-91 30-NOV-90 07-FEB-92 03-MAR-90 07-APR-90 07-APR-90 09-FEB-91 18-JAN-92 04-MAR-90 21-JUL-91 17-MAR-91 26-MAY-91 09-FEB-92

DAY ---------MONDAY MONDAY MONDAY MONDAY TUESDAY WEDNESDAY WEDNESDAY WEDNESDAY WEDNESDAY THURSDAY THURSDAY THURSDAY FRIDAY FRIDAY FRIDAY SATURDAY SATURDAY SATURDAY SATURDAY SATURDAY SUNDAY SUNDAY SUNDAY SUNDAY SUNDAY

25 rows selected.

3Ć64

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 3 10.

continued

Write a query that produces the following for each employee: <employee name> earns <salary> monthly but wants<3 times salary>. For example: ALLEN earns $1,100 monthly but wants $3,300. Dream Salaries --------------------------------------------------VELASQUEZ earns $2,500 monthly but wants $7,500. NGAO earns $1,450 monthly but wants $4,350. NAGAYAMA earns $1,400 monthly but wants $4,200. QUICK-TO-SEE earns $1,450 monthly but wants $4,350. ROPEBURN earns $1,550 monthly but wants $4,650. URGUHART earns $1,200 monthly but wants $3,600. MENCHU earns $1,250 monthly but wants $3,750. BIRI earns $1,100 monthly but wants $3,300. CATCHPOLE earns $1,300 monthly but wants $3,900. HAVEL earns $1,307 monthly but wants $3,921. MAGEE earns $1,400 monthly but wants $4,200. GILJUM earns $1,490 monthly but wants $4,470. SEDEGHI earns $1,515 monthly but wants $4,545. NGUYEN earns $1,525 monthly but wants $4,575. DUMAS earns $1,450 monthly but wants $4,350. MADURO earns $1,400 monthly but wants $4,200. NOZAKI earns $1,200 monthly but wants $3,600. MARKARIAN earns $850 monthly but wants $2,550. PATEL earns $795 monthly but wants $2,385. SMITH earns $1,000 monthly but wants $3,000. PATEL earns $795 monthly but wants $2,385. NEWMAN earns $750 monthly but wants $2,250. CHANG earns $800 monthly but wants $2,400. DANCS earns $860 monthly but wants $2,580. SCHWARTZ earns $1,100 monthly but wants $3,300. 25 rows selected.

Single Row Functions

3Ć65

3Ć66

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

4 Displaying Data from Multiple Tables

4Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives This lesson will cover how to obtain data from more than one table, using the many different methods available. At the end of this lesson, you should be able to D

Write SELECT statements to access data from more than one table using equality and non-equality joins.

D

View data that would not normally meet a join condition by using outer joins.

D

Join a table to itself.

Displaying Data from Multiple Tables

4Ć3

4Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview When data from more than one table in the database is required, a join condition is used. Rows in one table may be joined to rows in another table according to common values existing in corresponding columns, that is to say primary and foreign key columns. There are two main types of join conditions: D

Equijoins

D

Non-equijoins

Additional join methods include the following: D

Outer joins

D

Self joins

D

Set operators

For more information about set operators, attend Advanced SQL and SQL*Plus course.

Displaying Data from Multiple Tables

4Ć5

4Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

What Is a Cartesian Product? When a join condition is invalid or omitted completely, the result is a Cartesian Product, in which all combinations of rows will be displayed. All rows in the first table are joined to all rows in the second table. And Why Should You Care? A Cartesian product tends to generate a large number of rows, and its result is rarely useful. You should always include a valid join condition in a WHERE clause, unless you have a specific need to combine all rows from all tables. SQL> SELECT 2 FROM

name, last_name s_dept, s_emp;

... 300 rows selected.

Displaying Data from Multiple Tables

4Ć7

4Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Simple Join Query To display data from two or more related tables, write a simple join condition in the WHERE clause. Syntax SELECT table.column, table.column... FROM table1, table2 WHERE table1.column1 = table2.column2; where: table.column table1.column1 = table2.column2

denotes the table and column from which data is retrieved. is the condition that joins (or relates) the tables together.

Guidelines D

When writing a SELECT statement that joins tables, precede the column name with the table name for clarity and to enhance database access.

D

If the same column name appears in more than one table, then the column name must be prefixed with the table name.

D

To join tables together, you need a minimum of the number of join conditions summarized as the number of tables minus one. Therefore, to join four tables, a minimum of three joins would be required. This rule may not apply if your table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.

For more information, see Oracle7 Server SQL Language Reference Manual, “SELECT.”

Displaying Data from Multiple Tables

4Ć9

Server

ÉÉÉÉ

4Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Simple Join Query

continued

Equijoin In order to determine the name of an employee’s department, you compare the value in the DEPT_ID column in the S_EMP table with the ID values in the S_DEPT table. The relationship between the S_EMP and S_DEPT tables is an equijoin, that is values in the DEPT_ID column on both tables must be equal. Frequently, these columns are primary and foreign key complements. Example Join together the employee and department tables to display the employee name, department number, and department name. SQL> SELECT 2 3 FROM 4 WHERE

s_emp.last_name, s_emp.dept_id, s_dept.name s_emp, s_dept s_emp.dept_id = s_dept.id;

LAST_NAME DEPT_ID NAME ------------ ------- -------------------Velasquez 50 Administration Ngao 41 Operations Nagayama 31 Sales Quick-To-See 10 Finance Ropeburn 50 Administration Urguhart 41 Operations Menchu 42 Operations Biri 43 Operations Catchpole 44 Operations Havel 45 Operations Magee 31 Sales Giljum 32 Sales Sedeghi 33 Sales ... 25 rows selected. Every employee now has their respective department name displayed. The rows of the S_EMP table are combined with the rows of the S_DEPT table, and rows are only returned if the values of S_EMP.DEPT_ID and S_DEPT.ID are equal.

Displaying Data from Multiple Tables

4Ć11

4Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Simple Join Query

continued

Qualifying Ambiguous Column Names You need to qualify the names of the columns in the WHERE clause with the table name to avoid ambiguity. Without the table prefixes, the ID column could be from either the S_DEPT or the S_EMP table. It is necessary to add the table prefix to execute your query. If there are no names that are the same between the two tables, then there is no need to qualify the columns. However, you will gain improved performance by using the table prefix. Example Display the department number, region number, and region name for all departments. SQL> SELECT 2 3 4 FROM 5 WHERE

s_dept.id ”Department ID”, s_region.id ”Region ID”, s_region.name ”Region Name” s_dept, s_region s_dept.region_id = s_region.id;

Department ID Region ID Region Name ------------- ---------- --------------------------10 3 Africa / Middle East 31 1 North America 32 2 South America 33 3 Africa / Middle East 34 4 Asia 35 5 Europe 41 1 North America 42 2 South America 43 3 Africa / Middle East 44 4 Asia 45 5 Europe 50 1 North America 12 rows selected. The requirement to qualify ambiguous column names is also applicable to columns that may be ambiguous in a SELECT or ORDER BY clause.

Displaying Data from Multiple Tables

4Ć13

Server

ÉÉ ÉÉ

4Ć14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Simple Join Query

continued

Additional Search Conditions In addition to the join, you may have additional criteria for your WHERE clause. Since the join is required to obtain the matches, you need to add your additional conditions by using the AND operator. Table aliases help to keep SQL code smaller, therefore using less memory. Example Display employee Menchu’s last name, department number, and department name. SQL> 2 3 4 5

SELECT FROM WHERE AND

s_emp.last_name, s_emp.dept_id, s_dept.name s_emp, s_dept s_emp.dept_id = s_dept.id INITCAP(s_emp.last_name) = ’Menchu’;

LAST_NAME DEPT_ID NAME ------------ ------- -------------------Menchu 42 Operations Display the last name, region name, and commission percent of all employees who earn a commission. SQL> 2 3 4 5 6

SELECT FROM WHERE AND AND

LAST_NAME -----------Magee Giljum Sedeghi Nguyen Dumas

s_emp.last_name, s_region.name, s_emp.commission_pct s_emp, s_dept, s_region s_emp.dept_id = s_dept.id s_dept.region_id = s_region.id s_emp.commission_pct > 0;

NAME COMMISSION_PCT -------------------- -------------North America 10 South America 12.5 Africa / Middle East 10 Asia 15 Europe 17.5

Displaying Data from Multiple Tables

4Ć15

4Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Using Table Aliases Qualifying column names with table names can be very time consuming, particularly if table names are lengthy. Use table aliases instead. Like column aliases, table aliases are a method of giving the table another name for the purpose of the SELECT statement. Once you use the table alias, you must continue to qualify every column reference with the table alias. Example Display the customer name, region number, and region name for all customers. Provide column aliases, and use a table alias to shorten the table references. SQL> SELECT 2 3 4 FROM 5 WHERE

c.name ”Customer Name”, c.region_id ”Region ID”, r.name ”Region Name” s_customer c, s_region r c.region_id = r.id;

Guidelines D

Table aliases can be up to 30 characters in length, but the shorter they are the better.

D

If a table alias is used for a particular table name in the FROM clause, then that table alias must be substituted for the table name throughout the SELECT statement.

D

Table aliases should be meaningful.

D

The table alias is only valid for the current SELECT statement.

Displaying Data from Multiple Tables

4Ć17

4Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

NonĆEquijoin The relationship between the EMP and SALGRADE tables is a non-equijoin, in that no column in EMP corresponds directly to a column in SALGRADE. The relationship is obtained using an operator other than equal (=). Example Create a non-equijoin to evaluate an employee’s salary grade. The salary must be between any pair of the low and high salary ranges. SQL> SELECT 2 FROM 3 WHERE

ENAME ---------SMITH ADAMS JAMES WARD MARTIN MILLER ALLEN TURNER JONES BLAKE CLARK SCOTT FORD KING

e.ename, e.job, e.sal, s.grade emp e, salgrade s e.sal BETWEEN s.losal AND s.hisal;

JOB SAL GRADE --------- ---------- ---------CLERK 800.00 1 CLERK 1,100.00 1 CLERK 950.00 1 SALESMAN 1,250.00 2 SALESMAN 1,250.00 2 CLERK 1,300.00 2 SALESMAN 1,600.00 3 SALESMAN 1,500.00 3 MANAGER 2,975.00 4 MANAGER 2,850.00 4 MANAGER 2,450.00 4 ANALYST 3,000.00 4 ANALYST 3,000.00 4 PRESIDENT 5,000.00 5

14 rows selected. Other operators such as <= and >= could be used, however BETWEEN is the simplest. Remember to specify the low value first and the high value last when using BETWEEN. Table aliases have been specified for performance reasons, not because of possible ambiguity.

Displaying Data from Multiple Tables

4Ć19

4Ć20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Returning Records with No Direct Match If a row does not satisfy a join condition, then the row will not appear in the query result. For example, in the equijoin condition of S_EMP and S_CUSTOMER, Sweet Rock Sports does not appear because there is no sales representative for that customer. Outer Join The missing row(s) can be returned if an outer join operator is used in the join condition. The operator is a plus sign enclosed in parentheses (+), and is placed on the “side” of the join that is deficient in information. The operator has the effect of creating one or more NULL rows, to which one or more rows from the non-deficient table can be joined. Syntax SELECT table.column, table.column FROM table1, table2 WHERE table1.column = table2.column(+);

or SELECT table.column, table.column FROM table1, table2 WHERE table1.column(+) = table2.column; where: table1.column = table2.column (+)

Displaying Data from Multiple Tables

is the condition that joins (or relates) the tables together. is the outer join symbol; it can be placed on either side of the WHERE clause condition, but not on both sides. Place the outer join symbol following the name of the table without the matching rows.

4Ć21

Server

ÉÉ É

4Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Returning Records with No Direct Match

continued

Example Display the sales representative name and employee number and the customer name for all customers. Include the customer name even if the customer has not been assigned a sales representative. SQL> 2 3 4

SELECT FROM WHERE ORDER BY

e.last_name, e.id, c.name s_emp e, s_customer c e.id (+) = c.sales_rep_id e.id;

LAST_NAME ID NAME –––––––– ––– –––––––––––––––––––––––––––––––––––– Magee 11 Womansport Magee 11 Beisbol Si! Magee 11 Ojibway Retail Magee 11 Big John’s Sports Emporium Giljum 12 Unisports Giljum 12 Futbol Sonora Sedeghi 13 Hamada Sport Nguyen 14 Simms Atheletics Nguyen 14 Delhi Sports Dumas 15 Kam’s Sporting Goods Dumas 15 Sportique Dumas 15 Muench Sports Dumas 15 Sporta Russia Dumas 15 Kuhn’s Sports Sweet Rock Sports 15 rows selected.

Outer Join Restrictions D

The outer join operator can only appear on one side of the expression—the side that has information missing. It returns those rows from one table which have no direct match in the other table.

D

A condition involving an outer join may not use the IN operator or be linked to another condition by the OR operator.

Displaying Data from Multiple Tables

4Ć23

Server

ÉÉ É

4Ć24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Joining a Table to Itself You can join a table to itself by using table aliases to simulate as if the table were two separate tables. This allows rows in a table to be joined to rows in the same table. Self Join In order to simulate two tables in the FROM clause, the example contains an alias for the same table, S_EMP. This is an example of good naming conventions. In this example, the WHERE clause contains the join that means “where a worker’s manager number matches the employee number for the manager.” Example Display the names of employees and their respective managers. SQL> SELECT 2 3 FROM 4 WHERE

worker.last_name||’ works for ’|| manager.last_name s_emp worker, s_emp manager worker.manager_id = manager.id;

Ngao works for Velasquez Nagayama works for Velasquez Quick-To-See works for Velasquez Ropeburn works for Velasquez Urguhart works for Ngao Menchu works for Ngao Biri works for Ngao Catchpole works for Ngao Havel works for Ngao Magee works for Nagayama Giljum works for Nagayama Sedeghi works for Nagayama Nguyen works for Nagayama Dumas works for Nagayama ... 24 rows selected.

Displaying Data from Multiple Tables

4Ć25

4Ć26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary There are multiple ways to join tables. The common thread, though, is that you want to link them through a condition in the WHERE clause. The method you choose will be based on the required result and the data structures you are using. Syntax SELECT table.column, table.column... FROM table1, table2 WHERE table1.column1 = table2.column2; where: table.column table1.column1 = table2.column2

denotes a table and column from which data is retrieved. is the condition that joins (or relates) the tables together.

Types of Joins D

Equijoin

D

Non-equijoin

D

Outer join

D

Self join

Cartesian Products Omission of the WHERE clause will result in a Cartesian product, in which all combinations of rows will be displayed. Table Aliases D

Table aliases speed up database access. You tell the Oracle Server exactly where to go to find columns.

D

Table aliases can help to keep SQL code smaller, therefore conserving memory.

Displaying Data from Multiple Tables

4Ć27

4Ć28

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview This practice is intended to give you practical experience extracting data from more than one table. You will be required to join and restrict rows in the WHERE clause. Practice Contents D

Joining tables using an equijoin

D

Performing outer and self joins

D

Adding additional conditions

Displaying Data from Multiple Tables

4Ć29

4Ć30

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 4 Use the S_EMP, S_DEPT, S_CUSTOMER, S_REGION, S_ORD, S_ITEM, and S_PRODUCT tables to complete the following exercises. 1.

Write a report containing each employee’s last name, department number, and name of their department. LAST_NAME DEPT_ID NAME ------------ ------- -------------------Velasquez 50 Administration Ngao 41 Operations Nagayama 31 Sales Quick-To-See 10 Finance Ropeburn 50 Administration Urguhart 41 Operations Menchu 42 Operations Biri 43 Operations Catchpole 44 Operations Havel 45 Operations Magee 31 Sales Giljum 32 Sales Sedeghi 33 Sales Nguyen 34 Sales Dumas 35 Sales Maduro 41 Operations Smith 41 Operations Nozaki 42 Operations Patel 42 Operations Newman 43 Operations Markarian 43 Operations Chang 44 Operations Patel 34 Sales Dancs 45 Operations Schwartz 45 Operations 25 rows selected.

Displaying Data from Multiple Tables

4Ć31

Practice 4 2.

continued

Write a query to display the last name, department name, and region name of all employees who earn a commission. LAST_NAME -----------Magee Giljum Sedeghi Nguyen Dumas

3.

NAME -------------Sales Sales Sales Sales Sales

NAME -------------------North America South America Africa / Middle East Asia Europe

Display the employee name and department name for Smith. LAST_NAME NAME ------------ ---------------------Smith Operations

4.

Display the product name, product number, and quantity ordered of all items in order number 101. Label the quantity column ORDERED. NAME --------------------------------Grand Prix Bicycle Tires Pro Curling Bar Prostar 10 Pound Weight Prostar 100 Pound Weight Major League Baseball Griffey Glove Cabrera Bat

ID ORDERED ----- ------30421 15 40422 30 41010 20 41100 35 50169 40 50417 27 50530 50

7 rows selected.

4Ć32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 4 5.

continued

Display the customer number and the last name of their sales representative. Order the list by last name. ID ----205 206 208 211 215 201 204 214 209 213 202 203 212

LAST_NAME --------------Dumas Dumas Dumas Dumas Dumas Giljum Magee Magee Magee Magee Nguyen Nguyen Sedeghi

13 rows selected.

Displaying Data from Multiple Tables

4Ć33

Practice 4

continued

If you have time, complete the following exercises. 6.

Display the customer number, customer name, and order number of all customers and their orders. Display the customer number and name, even if they have not placed an order. Customer ID ----------201 202 203 204 204 205 206 207 208 208 209 210 210 211 212 213 214 215

Customer Name Order ID ------------------------------ ---------Unisports 97 Simms Atheletics 98 Delhi Sports 99 Womansport 100 Womansport 111 Kam’s Sporting Goods 101 Sportique 102 Sweet Rock Sports Muench Sports 103 Muench Sports 104 Beisbol Si! 105 Futbol Sonora 106 Futbol Sonora 112 Kuhn’s Sports 107 Hamada Sport 108 Big John’s Sports Emporium 109 Ojibway Retail 110 Sporta Russia

18 rows selected.

4Ć34

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 4 7.

continued

Display all employees by last name name and employee number along with their manager’s last name and manager number. EMP_NAME EMP_ID ---------–––––– ––––––– Ngao 2 Nagayama 3 Quick-To-See 4 Ropeburn 5 Urguhart 6 Menchu 7 Biri 8 Catchpole 9 Havel 1O Magee 11 Giljum 12 Sedeghi 13 Nguyen 14 Dumas 15 Maduro 16 Smith 17 Nozaki 18 Patel 19 Newman 20 Markarian 21 Chang 22 Patel 23 Dancs 24 Schwartz 25

MGR_NAME ––––––––––––– Velasquez Velasquez Velasquez Velasquez Ngao Ngao Ngao Ngao Ngao Nagayama Nagayama Nagayama Nagayama Nagayama Urguhart Urguhart Menchu Menchu Biri Biri Catchpole Catchpole Havel Havel

MGR_ID ––––––– 1 1 1 1 2 2 2 2 2 3 3 3 3 3 6 6 7 7 8 8 9 9 10 10

24 rows selected.

Displaying Data from Multiple Tables

4Ć35

Practice 4 8.

continued

Modify the solution to exercise 7 to also display Velasquez, who has no manager. Employee Name Employee ID ---------–––––––––––––––––– Velasquez 1 Ngao 2 Nagayama 3 Quick-To-See 4 Ropeburn 5 Urguhart 6 Menchu 7 Biri 8 Catchpole 9 Havel 10 Magee 11 Giljum 12 Sedeghi 13 Nguyen 14 Dumas 15 Maduro 16 Nozaki 18 Markarian 21 Patel 23 Smith 17 Patel 19 Newman 20 Chang 22 Dancs 24 Schwartz 25

Manager Name Manager_ID ––––––––––––– –––––––––– Velasquez Velasquez Velasquez Velasquez Ngao Ngao Ngao Ngao Ngao Nagayama Nagayama Nagayama Nagayama Nagayama Urguhart Menchu Biri Catchpole Urguhart Menchu Biri Catchpole Havel Havel

1 1 1 1 2 2 2 2 2 3 3 3 3 3 6 7 8 9 6 7 8 9 10 10

25 rows selected.

4Ć36

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 4 9.

continued

Display all customers and the products and quantities they ordered for those customers whose order totaled more than 100,000. Customer ---------------------Womansport Womansport Womansport Womansport Womansport Womansport Womansport Kuhn’s Sports Kuhn’s Sports Kuhn’s Sports Kuhn’s Sports Kuhn’s Sports Hamada Sport Hamada Sport Hamada Sport Hamada Sport Hamada Sport Hamada Sport Hamada Sport Big John’s Sports Emporium Big John’s Sports Emporium Big John’s Sports Emporium Big John’s Sports Emporium Big John’s Sports Emporium Big John’s Sports Emporium Big John’s Sports Emporium

PRODUCT_ID –––––––––– 10011 10013 10021 30326 41010 30433 10023 20106 20201 30421 30321 20108 20510 41080 41100 32861 20512 32779 30321 10011 30426 50418 32861 30326 10012 10022

QUANTITY –––––––– 500 400 500 600 250 450 400 50 130 55 75 22 9 50 42 57 18 60 85 150 500 43 50 1500 600 300

26 rows selected.

Displaying Data from Multiple Tables

4Ć37

4Ć38

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

5 Group Functions

5Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives This lesson further addresses functions. You will focus on obtaining summary information, such as averages, for groups of rows. You will discuss how to group rows in a table into smaller sets, and how to specify search criteria for groups of rows. At the end of this lesson, you should be able to D

Identify the available group functions.

D

Explain the use of group functions.

D

Use the GROUP BY clause to force statistics to be displayed for different groups.

D

Use the HAVING clause to include or exclude grouped rows.

Group Functions

5Ć3

5Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview Unlike single row functions, group functions operate on sets of rows to give one result per group. These sets may be the whole table or the table split into groups. Group functions appear in both SELECT lists and HAVING clauses. Group Functions D

AVG

D

COUNT

D

MAX

D

MIN

D

STDDEV

D

SUM

D

VARIANCE

GROUP BY and HAVING Clauses in the SELECT Statement By default, all the rows in a table are treated as one group. Use the GROUP BY clause in the SELECT statement to divide rows into smaller groups. Additionally, to restrict the result groups returned, use the HAVING clause. Syntax SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY

column, group_function table condition] group_by_expression] group_condition] column];

where: group_by_expression group_condition

Group Functions

specifies columns whose values determine the basis for grouping rows. restricts the groups of rows returned to those groups for which the specified condition is TRUE.

5Ć5

5Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Group Functions Each of the functions accepts an argument. The following table identifies the options you can use in the syntax. Function

Description

AVG(DISTINCT|ALL|n)

Average value of n, ignoring null values.

COUNT(DISTINCT|ALL|expr|*)

Number of rows, where expr evaluates to something other than null. Count all selected rows using *, including duplicates and rows with nulls.

MAX(DISTINCT|ALL|expr)

Maximum value of expr.

MIN(DISTINCT|ALL|expr)

Minimum value of expr.

STDDEV(DISTINCT|ALL|n)

Standard deviation of n, ignoring null values.

SUM(DISTINCT|ALL|n)

Sum values of n, ignoring null values.

VARIANCE(DISTINCT|ALL|n)

Variance of n, ignoring null values.

Guidelines D

DISTINCT makes the function consider only non-duplicate values; ALL makes it consider every value including duplicates. The default is ALL and therefore does not need to be specified.

D

The datatypes for the arguments may be CHAR, VARCHAR2, NUMBER, or DATE where expr is listed.

D

All group functions except COUNT(*) ignore null values. To substitute a value for null values, use the NVL function.

Group Functions

5Ć7

5Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Group Functions

continued

Example Display the average, highest, lowest, and sum of the monthly salaries for all sales representatives. SQL> SELECT 2 3 FROM 4 WHERE

AVG(salary), MAX(salary), MIN(salary), SUM(salary) s_emp UPPER(title) LIKE ’SALES%’;

AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY) ----------- ----------- ----------- ----------1476 1525 1400 7380 Note: You can use AVG and SUM functions against columns that store numeric data. Example Display the employee last name that is the first and the employee last name that is the last in an alphabetized list of all employees. SQL> SELECT 2 FROM

MIN(last_name), MAX(last_name) s_emp;

MIN(LAST_NAME) MAX(LAST_NAME) ------------------------- ------------------------Biri Velasquez Note: You can use MAX and MIN functions for any datatype.

Group Functions

5Ć9

5Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Group Functions

continued

COUNT Function The COUNT function has two formats: COUNT(*) and COUNT(expr). COUNT(*) returns the number of rows in a table, including duplicate rows and rows containing null values. Example Display the number of employees in department 31. SQL> SELECT 2 FROM 3 WHERE

COUNT(*) s_emp dept_id = 31;

COUNT(*) ---------2 In contrast, COUNT(expr) returns the number of non-null rows in the column identified by expr. Example Display the number of employees in department 31 who can earn a commission. SQL> SELECT 2 FROM 3 WHERE

COUNT(commission_pct) s_emp dept_id = 31;

COUNT(COMMISSION_PCT) --------------------1

Group Functions

5Ć11

5Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The GROUP BY Clause You use the GROUP BY clause to divide the rows in a table into smaller groups. You can then use the group functions to return summary information for each group. Syntax SELECT FROM [WHERE [GROUP BY [ORDER BY

column, group_function table condition] group_by_expression] column];

where: group_by_expression

specifies columns whose values determine the basis for grouping rows.

Guidelines D

If you include a group function in a SELECT clause, you cannot select individual results as well unless the individual column appears in the GROUP BY clause. You will receive an error message if you fail to include the column list.

D

Using a WHERE clause, you can pre-exclude rows before dividing them into groups.

D

You must include the columns in the GROUP BY clause.

D

You cannot use the positional notation or column alias in the GROUP BY clause.

D

By default, rows are sorted by ascending order of the GROUP BY list. You can override this by using the ORDER BY clause.

Group Functions

5Ć13

5Ć14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The GROUP BY Clause

continued

Example Display each possible customer credit rating and the number of customers in each credit rating category. Label the column # Cust. SQL> SELECT 2 FROM 3 GROUP BY

credit_rating, COUNT(*) ”# Cust” s_customer credit_rating;

CREDIT_RA # Cust --------- ---------EXCELLENT 9 GOOD 3 POOR 3

Example Display the job titles and total monthly salary for each job title, excluding vice presidents. Sort the list by the total monthly salary. SQL> 2 3 4 5

SELECT FROM WHERE GROUP BY ORDER BY

title, SUM(salary) PAYROLL s_emp title NOT LIKE ’VP%’ title SUM(salary);

TITLE PAYROLL --------------------- ---------President 2500 Warehouse Manager 6157 Sales Representative 7380 Stock Clerk 9490

Group Functions

5Ć15

5Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The GROUP BY Clause

continued

The GROUP BY column does not have to be in the SELECT clause as the following examples shows, but the results are not very meaningful. Add the TITLE column so that the results are more meaningful. Example Display the maximum salary for each title group, without displaying the title. SQL> SELECT 2 FROM 3 GROUP BY

MAX(salary) s_emp title;

MAX(SALARY) ----------2500 1525 1400 ... 8 rows selected. Example Display the maximum salary for each title group and display the title. SQL> SELECT 2 FROM 3 GROUP BY

title, MAX(salary) s_emp title;

TITLE MAX(SALARY) --------------------- ----------President 2500 Sales Representative 1525 Stock Clerk 1400 ... 8 rows selected.

Group Functions

5Ć17

5Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Illegal Queries Using Group Functions Whenever you use a mixture of individual items (REGION_ID) and group functions (COUNT) in the same SELECT statement, you must include a GROUP BY clause that specifies the individual items (in this case, REGION_ID). If the GROUP BY clause is missing, then the error message “not a single-group group function” would appear and an asterisk (*) would point to the offending column. Example Display each region and the number of departments in that region. SQL> SELECT region_id, COUNT(name) 2 FROM s_dept; SELECT region_id, COUNT(name) * ERROR at line 1: ORA-00937: not a single-group group function Correct the above error by adding the GROUP BY clause. Now, REGION_ID is the name of a group. SQL> SELECT region_id, COUNT(name) 2 FROM s_dept 3 GROUP BY region_id;

REGION_ID COUNT(NAME) --------- ----------1 4 2 2 3 2 4 2 5 2 Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause.

Group Functions

5Ć19

5Ć20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Illegal Queries Using Group Functions

continued

The WHERE clause cannot be used to restrict groups. Example Display the department number and average salary for those departments that have an average salary more than 2000. SQL> SELECT dept_id, AVG(salary) 2 FROM s_emp 3 WHERE AVG(salary) > 2000 4 GROUP BY dept_id; WHERE AVG(salary) > 2000 * ERROR at line 3: ORA-00934: group function is not allowed here Instead, use the HAVING clause to limit those rows. SQL> 2 3 4

SELECT FROM GROUP BY HAVING

dept_id, AVG(salary) s_emp dept_id AVG(salary) > 2000;

DEPT_ID AVG(SALARY) ------- ----------50 2025

Group Functions

5Ć21

5Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Groups Within Groups You can return summary results for groups and subgroups by listing more than one GROUP BY column. In the example, you count the number of people not only within the department, but also by the job category. The order in which the columns are listed in the GROUP BY clause determines the default sort order. Example Display the number of employees for each job category within each department. SQL> SELECT 2 FROM 3 GROUP BY

DEPT_ID ------... 34 34 35 41 41 ...

dept_id, title, COUNT(*) s_emp dept_id, title;

TITLE COUNT(*) --------------------- ---------Sales Representative Stock Clerk Sales Representative Stock Clerk VP, Operations

1 1 1 2 1

Example Display the number of employees for each department within each job category. SQL> SELECT 2 FROM 3 GROUP BY

title, dept_id, COUNT(*) s_emp title, dept_id;

TITLE DEPT_ID COUNT(*) --------------------- ------- ---------... Sales Representative 34 1 Sales Representative 35 1 Stock Clerk 34 1 ...

Group Functions

5Ć23

5Ć24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The HAVING Clause You use the HAVING clause to specify which groups are to be displayed. Therefore, you further restrict the groups on the basis of aggregate information. Syntax SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY

column, group_function table condition] group_by_expression] group_condition] column];

where: group_condition

restricts the groups of rows returned to those groups for which the specified condition is TRUE.

The Oracle7 Server performs the following steps when you use the HAVING clause: D

Rows are grouped.

D

The group function is applied.

D

The groups that match the criteria in the HAVING clause are displayed.

The HAVING clause may precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.

Group Functions

5Ć25

5Ć26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The HAVING Clause

continued

Example Display the job titles and total monthly salary for each job title with a total payroll exceeding 5000. Do not include vice presidents, and sort the list by the total monthly salary. SQL> 2 3 4 5 6

SELECT FROM WHERE GROUP BY HAVING ORDER BY

title, SUM(salary) PAYROLL s_emp title NOT LIKE ’VP%’ title SUM(salary) > 5000 SUM(salary);

TITLE PAYROLL --------------------- ---------Warehouse Manager 6157 Sales Representative 7380 Stock Clerk 9490

Group Functions

5Ć27

5Ć28

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The HAVING Clause

continued

In the following example, there is no group function in the SELECT list, but there are HAVING and GROUP BY clauses. Since a group function is referenced in the HAVING clause, the GROUP BY clause is required. Example Display all department numbers for departments with a total monthly payroll more than 4000. SQL> 2 3 4

SELECT FROM GROUP BY HAVING

dept_id s_emp dept_id SUM(salary) > 4000;

DEPT_ID ------41 50

Group Functions

5Ć29

5Ć30

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary There are seven group functions available in SQL: D

AVG

D

COUNT

D

MAX

D

MIN

D

STDDEV

D

SUM

D

VARIANCE

Syntax SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY

column, group_function table condition] group_by_expression] group_condition] column];

You can create subgroups by using the GROUP BY clause. Groups can be excluded using the HAVING clause. Place the HAVING and GROUP BY clauses after the WHERE clause in a statement. Place the ORDER BY clause last. Oracle7 Server evaluates the clauses in the following order: 1.

If the statement contains a WHERE clause, the server establishes the candidate rows.

2.

The server identifies the groups specified in the GROUP BY clause.

3.

The HAVING clause further restricts result groups that do not meet the group criteria in the HAVING clause.

Group Functions

5Ć31

5Ć32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview At the end of this practice, you should be familiar with using group functions and selecting groups of data. Practice Contents D

Showing different queries that use all group functions, with the exception of STDDEV and VARIANCE

D

Grouping by rows to achieve more than one result

D

Excluding groups by using the HAVING clause

Note: Column aliases used for the queries.

Group Functions

5Ć33

Practice 5 1.

Determine the validity of the following statements. Circle either True or False. a. Group functions work across many rows to produce one result. True / False b. Group functions include nulls in calculations. True / False

Use the HAVING clause to exclude rows from a group calculation. True / False d. Use the HAVING clause to exclude groups of rows from the display. True / False 2. Display the highest and lowest order totals in the S_ORD table. Label the columns Highest and Lowest, respectively. c.

Highest Lowest ---------- ---------1020935 377 3.

Write a query to display the minimum and maximum salary for each job type ordered alphabetically. JOB MAXIMUM MINIMUM ------------------------- ---------- ---------President 2500 2500 Sales Representative 1525 1400 Stock Clerk 1400 750 VP, Administration 1550 1550 VP, Finance 1450 1450 VP, Operations 1450 1450 VP, Sales 1400 1400 Warehouse Manager 1307 1100 8 rows selected.

4.

Determine the number of managers without listing them. Number of Managers -----------------8

5Ć34

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 5 5.

continued

Display the number of line items in each order under each order number, labeled Number of Items. ORD_ID NUMBER OF ITEMS ---------- --------------97 2 98 1 99 4 100 7 101 7 102 2 103 2 104 4 105 3 106 6 107 5 108 7 109 7 110 2 111 2 112 1 16 rows selected.

6.

Display the manager number and the salary of the lowest paid employee for that manager. Exclude any groups where the minimum salary is less than 1000. Sort the output by salary. MANAGER_ID –––––––––– 2 1 3

Group Functions

LOWEST PAID EMPLOYEE ––––––––––––––––––––– 1100 1400 1400 2500

5Ć35

Practice 5 7.

continued

What is the difference between the highest and lowest salaries? DIFFERENCE ---------1750

If you have time, complete the following exercises. 8.

Display the product number and number of times it was ordered, labeled Times Ordered. Only show those products that have been ordered at least three times. Order the data by the number of products ordered. PRODUCT_ID TIMES ORDERED ---------- -------------20106 3 20108 3 20201 3 20510 3 50273 3 30421 3 20512 3 30321 4 8 rows selected.

9.

Retrieve the region number, region name, and the number of departments within each region. ID ----1 2 3 4 5

5Ć36

NAME # OF DEPT --------------------------- ---------------North America 4 South America 2 Africa / Middle East 2 Asia 2 Europe 2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 5 10.

continued

Display the order number and total item count for each order of 100 or more items. For example, if order number 99 contains quantity 30 of one item, and quantity 75 of another item, then the total item count for that order is 105. ORD_ID TOTAL ITEM COUNT ---------- ----------------97 1050 99 165 100 3100 101 217 102 145 106 392 107 332 108 321 109 3143 9 rows selected.

11.

Display the customer name and the number of orders for each customer. NAME NUMBER OF ORDERS -------------------------------- ----------------Beisbol Si! 1 Big John’s Sports Emporium 1 Delhi Sports 1 Futbol Sonora 2 Hamada Sport 1 Kam’s Sporting Goods 1 Kuhn’s Sports 1 Muench Sports 2 Simms Athletics 1 Ojibway Retail 1 Sportique 1 Unisports 2 Womansport 2 13 rows selected.

Group Functions

5Ć37

5Ć38

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

6 Subqueries

6Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives This lesson covers more advanced features of the SELECT statement. You can write subqueries in the WHERE clause of another SQL statement to obtain values based on an unknown conditional value. At the end of this lesson, you should be able to D

Write nested subqueries to query data based on unknown criteria.

D

Use subqueries in data manipulation statements.

D

Order data with subqueries.

Subqueries

6Ć3

Server

ÉÉ ÉÉÉÉ ÉÉ

6Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview A subquery is a SELECT statement that is embedded in a clause of another SQL statement.You can build powerful commands out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself. You can place the subquery in a number of SQL command clauses: D

WHERE clause

D

HAVING clause

D

FROM clause of a SELECT or DELETE statement

Subqueries

6Ć5

6Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Subqueries Subqueries are very useful for writing SELECT statements that query values based on an unknown conditional value. You can use the subquery to find out the values of the unknown data. Syntax SELECT FROM WHERE

where: operator

select_list table expr operator (SELECT select_list FROM table); includes a comparison operator such as >, =, or IN.

Note: Comparison operators fall into two classes: single row (>, =, >=, <, <>, <=) and multiple row (IN, NOT IN) operators. The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main or outer query. Guidelines D

Enclose a subquery within parentheses.

D

Place the subquery after the comparison operator.

D

Do not add an ORDER BY clause to a subquery. You can have only one ORDER BY clause for a SELECT statement, and if specified, it must be the last clause in the main SELECT statement.

Subqueries

6Ć7

43

6Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

How Are Nested Subqueries Processed? A SELECT statement can be considered as a query block. This example consists of two query blocks: the main query and the inner query. Example Retrieve the last name and title of the employees in the same department as Biri. SQL> SELECT 2 FROM 3 WHERE 4 5 6 1.

The nested SELECT statement or query block is executed first, producing a query result: 43. SQL> SELECT 2 FROM 3 WHERE

2.

last_name, title s_emp dept_id = (SELECT dept_id FROM s_emp WHERE UPPER(last_name)=’BIRI’);

dept_id s_emp UPPER(last_name)=’BIRI’;

The main query block is then processed and uses the value returned by the nested subquery to complete its search condition. In fact, the main query would finally look like this to the Oracle7 Server: SQL> SELECT 2 FROM 3 WHERE

Subqueries

last_name, title s_emp dept_id = 43;

6Ć9

6Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Single Row Subqueries A single row subquery returns only one row from the nested SELECT statement. This type of subquery uses a single row operator. Example Display the last name of employees who have the same title as Smith. SQL> SELECT 2 FROM 3 WHERE 4 5 6

last_name, title s_emp title = (SELECT title FROM s_emp WHERE last_name = ’Smith’);

LAST_NAME ----------------------Maduro Smith Nozaki Patel Newman Markarian Chang Patel Dancs Schwartz

TITLE -------------------Stock Clerk Stock Clerk Stock Clerk Stock Clerk Stock Clerk Stock Clerk Stock Clerk Stock Clerk Stock Clerk Stock Clerk

10 rows selected. Note: The subquery that returned the value of Stock Clerk is called a single row subquery. When a subquery returns only one row, you should use a single row comparison or logical operator such as: =, >, <, >=, or <=.

Subqueries

6Ć11

Server

ÉÉÉÉ

6Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Single Row Subqueries

continued

You can display data from a main query by using a group function in a subquery to return a single row. The subquery is in parentheses and is placed after the comparison operator. Example Display the last name, job title, and salary for all employees who make less than the average salary. SQL> SELECT 2 FROM 3 WHERE 4 5

LAST_NAME ------------------Urguhart Menchu Biri Smith Nozaki Patel Newman Markarian Chang Patel Dancs Schwartz

last_name, title, salary s_emp salary < (SELECT AVG(salary) FROM s_emp);

TITLE SALARY --------------------- ----------Warehouse Manager 1200 Warehouse Manager 1250 Warehouse Manager 1100 Stock Clerk 940 Stock Clerk 1200 Stock Clerk 795 Stock Clerk 750 Stock Clerk 850 Stock Clerk 800 Stock Clerk 795 Stock Clerk 860 Stock Clerk 1100

12 rows selected.

Subqueries

6Ć13

6Ć14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Errors with Subqueries One common error with subqueries is more than one row returned for a single row subquery. Example The subquery returns more than one row and uses a single row comparison operator. To correct this error, change the = to IN. SQL> 2 3 4 5 6 7

SELECT FROM WHERE

last_name, first_name, title s_emp dept_in = (SELECT ID FROM s_dept WHERE name = ’Finance’ OR region_id = 2);

ORA-1427: single-row subquery returns more than one row

Subqueries

6Ć15

6Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Multiple Row Subqueries Subqueries that return more than one row are called multiple row subqueries. Be sure to use a multiple row operator, such as IN, instead of a single row operator. The IN operator expects one or more values. Example Find all employees who are in the finance department or in region 2. SQL> SELECT 2 FROM 3 WHERE 4 5 6 7

LAST_NAME ––––––––––––– Quick–To–See Menchu Giljum Nozaki Patel

last_name, first_name, title s_emp dept_in IN (SELECT ID FROM s_dept WHERE name = ’Finance’ OR region_id = 2);

FIRST_NAME ––––––––––– Mark Roberta Henry Akira Vikram

TITLE ––––––––––––––––––– VP, Finance Warehouse Manager Sales Representative Stock Clerk Stock Clerk

You use a multiple row comparison operator, such as IN, because a list of values is returned from the subquery.

Subqueries

6Ć17

6Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

HAVING Clause with Nested Subqueries You can use subqueries not only in the WHERE clause, but also in the HAVING clause. The Oracle7 Server executes the subquery, and the results are returned into the main query’s HAVING clause. Example Display all the departments that have an average salary bill greater than that of department 32. SQL> 2 3 4 5 6 7

SELECT FROM GROUP BY HAVING

dept_id, AVG(salary) s_emp dept_id AVG(salary) > (SELECT AVG(salary) FROM s_emp WHERE dept_id = 32);

DEPT_ID AVG(SALARY) ------- ----------33 1515 50 2025 Example Find the job with the lowest average salary. SQL> 2 3 4 5 6 7

SELECT FROM GROUP BY HAVING

title, AVG(salary) s_emp title AVG(salary) = (SELECT MIN(AVG(salary)) FROM s_emp GROUP BY title);

TITLE AVG(SALARY) --------------------- ----------Stock Clerk 955

Subqueries

6Ć19

6Ć20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary Subqueries are useful when a query is based on unknown criteria. Syntax SELECT FROM WHERE

select_list table expr operator (SELECT select_list FROM table);

Subqueries D

Can pass one row of data to a main statement that contains a single row operator, such as =, <>, >, >=, <, or <=.

D

Can pass multiple rows of data to a main statement that contains a multiple row operator, such as IN.

D

Are processed first by the Oracle7 Server, and the WHERE or HAVING clause uses the results.

D

Can contain group functions.

Subqueries

6Ć21

6Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview In this practice, you will write complex queries using nested SELECT statements. Practice Contents D

Creating subqueries to query values based on unknown criteria

D

Identifying what values exist in one set of data and not in another by using subqueries

You may want to consider creating the inner query first for these questions. Make sure that it runs and produces the data you anticipate before coding the outer query.

Subqueries

6Ć23

6Ć24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 6 1.

Answer the following questions a.

Which query runs first with a subquery?

b.

How many times does the first query run?

c.

You cannot use the equality (=) operator if the inner query returns more than one value. True / False i.

If the answer is True, why and what operator should be used?

ii.

If the answer is False, why?

These exercises use the S_EMP, S_DEPT, S_ORD, S_ITEM, and S_PRODUCT tables. 2.

Display the first name, last name, and start date for all employees in the same department as Magee.

3.

Display the employee number, first name, last name, and user name for all employees with salaries above the average salary.

4.

Display last name, department number, and title for all employees assigned to region 1 or region 2.

5.

Display the last name and salary for all employees that report to LaDoris Ngao.

If you have time, complete the following exercises. 6.

Display the employee number, first name, and last name for all employees with a salary above the average salary and that work with any employee with a last name that contains a “t.”

7.

Display the customer number, customer name, credit rating, and sales representative last name for all customers who are located in the North America region or have Nguyen as their sales representative.

Subqueries

6Ć25

Practice 6

continued

If you have time, complete the following exercises. 8.

Display the name and short description for any product that did not appear on an order in the month of September, 1992.

9.

Display the customer name and credit rating for all customers of sales representative Andre Dumas.

10.

Display each sales representative’s last name in region 1 and region 2, their customers’ names and each customer’s total sales orders.

6Ć26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder