Oracle

Oracle Database 10g: SQL Fundamentals I
Student Guide • Volume II

D17108GC21 Edition 2.1 December 2006 D48184

Authors Chaitanya Koratamaddi Nancy Greenberg Technical Contributors and Reviewers Wayne Abbott Christian Bauwens Claire Bennett Perry Benson Brian Boxx Zarko Cesljas Dairy Chan Laszlo Czinkoczki Joel Goodman Matthew Gregory Sushma Jagannath Angelika Krupp Isabelle MarchandMalika Marghadi Valli Pataballa Bryan Roberts Helen Robertson Lata Shivaprasad John Soltani Priya Vennapusa Narayanan Radhakrishnan Editors Arijit Ghosh Raj Kumar Graphic Designer Rajiv Chandrabhanu Publisher Giri Venugopal

Copyright © 2006, Oracle. All rights reserved. Disclaimer This document contains proprietary information and is protected by copyright and other intellectual property laws. Youmay copy and print this document solely for your own use in an Oracle training course. The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the expressauthorization of Oracle. The information contained in this document is subject to change without notice. If you find any problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not warranted to be error-free. Restricted Rights Notice If this documentation is delivered to the United States Government oranyone using the documentation on behalf of the United States Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract. Trademark Notice Oracle, JDEdwards, and PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Contents

Preface I Introduction Lesson Objectives I-2 Goals of the Course I-3 Oracle10g I-4 Oracle Database 10g I-6 Oracle Application Server 10g I-7 Oracle Enterprise Manager 10g Grid Control I-8 Relational and ObjectRelational Database Management Systems I-9 Oracle Internet Platform I-10 System Development Life Cycle I-11 Data Storage on Different Media I-13 Relational Database Concept I-14 Definition of a Relational Database I-15 Data Models I-16 Entity Relationship Model I-17 Entity Relationship Modeling Conventions I-19 Relating Multiple Tables I-21 Relational Database Terminology I-23 Relational DatabaseProperties I-25 Communicating with an RDBMS Using SQL I-26 Oracle’s Relational Database Management System I-27 SQL Statements I-28 Tables Used in the Course I-29 Summary I-30 Retrieving Data Using the SQL SELECT Statement Objectives 1-2 Capabilities of SQL SELECT Statements 1-3 Basic SELECT Statement 1-4 Selecting All Columns 1-5 Selecting Specific Columns 1-6 Writing SQL Statements 1-7 Column HeadingDefaults 1-8

1

iii

Arithmetic Expressions 1-9 Using Arithmetic Operators 1-10 Operator Precedence 1-11 Defining a Null Value 1-12 Null Values in Arithmetic Expressions 1-13 Defining a Column Alias 1-14 Using Column Aliases 1-15 Concatenation Operator 1-16 Literal Character Strings 1-17 Using Literal Character Strings 1-18 Alternative Quote (q) Operator 1-19 Duplicate Rows 1-20 SQL andiSQL*Plus Interaction 1-21 SQL Statements Versus iSQL*Plus Commands 1-22 Overview of iSQL*Plus 1-23 Logging In to iSQL*Plus 1-24 iSQL*Plus Environment 1-25 Displaying Table Structure 1-26 Interacting with Script Files 1-28 iSQL*Plus History Page 1-32 Setting iSQL*Plus Preferences 1-34 Setting the Output Location Preference 1-35 Summary 1-36 Practice 1: Overview 1-37 2 Restricting and Sorting Data...