Tutorial sql

Oracle SQL Tutorial1
Michael Gertz Database and Information Systems Group Department of Computer Science University of California, Davis gertz@cs.ucdavis.edu
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 sitewww.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.

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 MichaelCorey: 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: AComplete Guide 2nd Edition, Dec 2000, The Morgan Kaufmann Series in Data Management Systems, 2000.
1

Recommended Literature

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

Contents
1. SQL Structured Query Language 1.1. Tables 1.2. Queries Part I 1.3. Data De nition in SQL 1.4. Data Modi cations in SQL 1.5. Queries Part II 1.6. Views 2. SQL*Plus Minimal UserGuide, Editor Commands, Help System 3. Oracle Data Dictionary 4. Application Programming 4.1. PL SQL 4.1.1 Introduction 4.1.2 Structure of PL SQL Blocks 4.1.3 Declarations 4.1.4 Language Elements 4.1.5 Exception Handling 4.1.6 Procedures and Functions 4.1.7 Packages 4.1.8 Programming in PL SQL 4.2. Embedded SQL and Pro*C 5. Integrity Constraints and Triggers 5.1. Integrity Constraints 5.1.1 CheckConstraints 5.1.2 Foreign Key Constraints 5.1.3 More About Column- and Table Constraints 5.2. Triggers 5.2.1 Overview 5.2.2 Structure of Triggers 5.2.3 Example Triggers 5.2.4 Programming Triggers 6. System Architecture 6.1. Storage Management and Processes 6.2. Logical Database Structures 6.3. Physical Database Structures 6.4. Steps in Processing an SQL Statement 6.5. Creating Database Objects 1 36 9 11 19 20 23 26 27 27 28 32 34 36 38 39 46 47 49 50 50 53 55 58 60 61 63 63

1 SQL Structured Query Language
1.1 Tables
In relational database systems DBS data are represented using tables relations . A query issued against the DBS also results in a table. A table has the following structure: Column 1 Column 2 : : : Column n

, Tuple or Record
::: ::: ::: :::
A table is uniquelyidenti ed by its name and consists of rows that contain the stored information, each row containing exactly one tuple or record . A table can have one or more columns. A column is made up of a column name and a data type, and it describes an attribute of the tuples. The structure of a table, also called relation schema, thus is de ned by its attributes. The type of information to be stored in atable is de ned by the data types of the attributes at table creation time. SQL uses the terms table, row, and column for relation, tuple, and attribute, respectively. In this tutorial we will use the terms interchangeably. A table can have up to 254 columns which may have di erent or same data types and sets of values domains, respectively. Possible domains are alphanumeric data strings,numbers and date formats. Oracle o ers the following basic data types:

charn: Fixed-length character data string, n characters long. The maximum size for n is 255 bytes 2000 in Oracle8. Note that a string of type char is always padded on

right with blanks to full length of n.  can be memory consuming. Example: char40 varchar2n: Variable-length character string. The maximum size...