Oracle SQL

Oracle/SQL Tutorial1

Michael Gertz

Database and Information Systems Group

Department of Computer Science

University of California, Davis

[e-mail]

http://www.db.cs.ucdavis.edu

This Oracle/SQL tutorial provides a detailed introduction to the SQL query language and the

Oracle Relational Database Management System. Further information about Oracle and SQL

can be found on the web site www.db.cs.ucdavis.edu/dbs.

Comments, corrections, or additions to these notes are welcome. Many thanks to Christina

Chung for comments on the previous version.

Recommended Literature

George Koch and Kevin Loney: Oracle8 The Complete Reference (The Single Most Comprehensive

Sourcebook for Oracle Server, Includes CD with electronic version of the book), 1299

pages, McGraw-Hill/Osborne, 1997.

Michael Abbey and Michael Corey: Oracle8 : A Beginner's Guide [A Thorough Introduction

for First-time Users], 767 pages, McGraw-Hill/Osborne, 1997.

Steven Feuerstein, Bill Pribyl, Debby Russell: Oracle PL/SQL Programming (2nd Edition),

O'Reilly & Associates, 1028 pages, 1997.

C.J. Date and Hugh Darwen: A Guide to the SQL Standard (4th Edition), Addison-Wesley,

1997.

Jim Melton and Alan R. Simon: Understanding the New SQL: A Complete Guide (2nd Edition,

Dec 2000), The Morgan Kaufmann Series in Data Management Systems, 2000.

1revised Version 1.01, January 2000, Michael Gertz, Copyright 2000.

Contents

1. SQL { Structured Query Language

1.1. Tables 1

1.2. Queries (Part I) 3

1.3. Data Denition in SQL 6

1.4. Data Modications in SQL 9

1.5. Queries (Part II) 11

1.6. Views 19

2. SQL*Plus (Minimal User Guide, Editor Commands, Help System) 20

3. Oracle Data Dictionary 23

4. Application Programming

4.1. PL/SQL

4.1.1 Introduction 26

4.1.2 Structure of PL/SQL Blocks 27

4.1.3 Declarations 27

4.1.4 Language Elements 28

4.1.5 Exception Handling 32

4.1.6 Procedures and Functions 34

4.1.7