Oracle/SQL Tutorial - Emory University

1 Oracle/SQL Tutorial1 Michael GertzDatabase and Information Systems GroupDepartment of Computer ScienceUniversity of California, Oracle/SQL Tutorial provides a detailed introduction to the SQL query language and theOracle Relational Database Management System. Further information about oracle and SQLcan be found on the web , corrections, or additions to these notes are welcome. Many thanks to ChristinaChung for comments on the previous LiteratureGeorge Koch and Kevin Loney:Oracle8 The Complete Reference(The Single Most Compre-hensive Sourcebook for oracle Server, Includes CD with electronic version of the book), 1299pages, McGraw-Hill/Osborne, Abbey and Michael Corey:Oracle8 : A Beginner s Guide [A Thorough Introductionfor First-time Users], 767 pages, McGraw-Hill/Osborne, Feuerstein, Bill Pribyl, Debby Russell.

2 OraclePL/SQL Programming(2nd Edition),O Reilly & Associates, 1028 pages, Date and Hugh Darwen:A Guide to the SQL Standard(4th Edition), Addison-Wesley, Melton and Alan R. Simon:Understanding the New SQL: A Complete Guide(2nd Edition,Dec 2000), The Morgan Kaufmann Series in Data Management Systems, Version , January 2000, Michael Gertz, Copyright SQL Structured Query Queries (Part I) Data Definition in Data Modifications in Queries (Part II) Views192. SQL*Plus (Minimal User Guide, Editor Commands, Help System)203.

3 oracle Data Dictionary234. Application Structure of PL/SQL Language Exception Procedures and Programming in Embedded SQL and Pro*C395. Integrity Constraints and Integrity Check Foreign Key More About Column- and Table Structure of Example Programming Triggers556. System Storage Management and Logical Database Physical Database Steps in Processing an SQL Creating Database Objects631 SQL Structured Query TablesIn relational database systems (DBS) data are represented usingtables(relations).

4 A queryissued 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 uniquely identified by its name and consists ofrowsthat contain the stored informa-tion, each row containing exactly onetuple(orrecord). A table can have one or more made up of a column name and a data type, and it describes an attribute of thetuples. The structure of a table, also calledrelation schema, thus is defined by its type of information to be stored in a table is defined by the data types of the attributesat table creation uses the termstable, row, andcolumnforrelation, tuple, andattribute, respectively.

5 Inthis Tutorial we will use the terms table can have up to 254 columns which may have different or same data types and sets ofvalues (domains), respectively. Possible domains are alphanumeric data (strings), numbers anddate the following basic data types: char(n): Fixed-length character data (string),ncharacters long. The maximum size fornis 255 bytes (2000 inOracle8). Note that a string of typecharis always padded onright with blanks to full length ofn. (+can be memory consuming).Example:char(40) varchar2(n): Variable-length character string.

6 The maximum size fornis 2000 (4000 inOracle8). Only the bytes used for a string require :varchar2(80) number(o,d): Numeric data type for integers and overall number of digits,d= number of digits to the right of the decimal values:o=38,d= 84 to + :number(8),number(5,2)Note that, ,number(5,2) cannot contain anything larger than without result-ing in an error. Data types derived fromnumberareint[eger],dec[imal],smallin tandreal. date: Date data type for storing date and default format for a date is: : 13-OCT-94 , 07-JAN-98 1 long: Character data up to a length of 2GB.

7 Only onelongcolumn is allowed per :InOracle-SQL there is no data typeboolean. It can, however, be simulated by usingeitherchar(1) ornumber(1).As long as no constraint restricts the possible values of an attribute, it may have the specialvaluenull(for unknown). This value is different from the number 0, and it is also differentfrom the empty string .Further properties of tables are: the order in which tuples appear in a table is not relevant (unless a query requires anexplicit sorting). a table has no duplicate tuples (depending on the query, however, duplicate tuples canappear in the query result).

8 Adatabase schemais a set of relation schemas. The extension of adatabase schemaat databaserun-time is called adatabase instanceordatabase, for Example DatabaseIn the following discussions and examples we use an example database to manage informationabout employees, departments and salary scales. The corresponding tables can be createdunder the UNIX shell using the commanddemobld. The tables can be dropped by issuingthe commanddemodropunder the UNIX tableEMPis used to store information about the attributes, the following data types are defined:EMPNO:number(4),ENAME:varchar2(3 0),JOB:char(10),MGR:number(4),HIREDATE:d ate,SAL:number(7,2),DEPTNO:number(2)Each row (tuple) from the table is interpreted as follows: an employee has a number, a name,a job title and a salary.

9 Furthermore, for each employee the number of his/her manager, thedate he/she was hired, and the number of the department where he/she is working are tableDEPT stores information about departments (number, name, and location):DEPTNODNAMELOC10 STORECHICAGO20 RESEARCHDALLAS30 SALESNEW YORK40 MARKETINGBOSTONF inally, the tableSALGRADE contains all information about the salary scales, more precisely, themaximum and minimum salary of each Queries (Part I)In order to retrieve the information stored in the database, the SQL query language is used.

10 Inthe following we restrict our attention to simple SQL queries and defer the discussion of morecomplex queries to Section SQL a query has the following (simplified) form (components in brackets [ ] are optional):select[distinct]<column(s)>from<table>[where<condition>][order by<column(s) [asc|desc]>] Selecting ColumnsThe columns to be selected from a table are specified after the keywordselect. This operationis also calledprojection. For example, the queryselectLOC, DEPTNO fromDEPT;lists only the number and the location for each tuple from the relationDEPT.