Sql Study Matetial
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 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.
1
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 Definition in SQL
1.4. Data Modifications in SQL
1.5. Queries (Part II)
1.6. Views
1
3
6
9
11
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
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
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 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.
1
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 Definition in SQL
1.4. Data Modifications in SQL
1.5. Queries (Part II)
1.6. Views
1
3
6
9
11
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
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