Database and Information Systems Group
Department of Computer Science
University of California, Davis
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.
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.
revised Version 1.01, January 2000, Michael Gertz, Copyright 2000.
1. SQL – Structured Query Language
1.2. Queries (Part I)
1.3. Data Deﬁnition in SQL
1.4. Data Modiﬁcations in SQL
1.5. Queries (Part II)
2. SQL*Plus (Minimal User Guide, Editor Commands, Help System)
3. Oracle Data Dictionary
4. Application Programming
4.1.2 Structure of PL/SQL Blocks
4.1.4 Language Elements
4.1.5 Exception Handling
4.1.6 Procedures and Functions
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 Check Constraints
5.1.2 Foreign Key Constraints
5.1.3 More About Column- and Table Constraints
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
SQL – Structured Query Language
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 . . .
←− Tuple (or Record)
A table is uniquely identiﬁ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 a table 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: • char(n): 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...