Home

PDF Ebook Oracle/SQL Tutorial

Submitted by antoq on Fri, 07/10/2009 - 08:56

A table is uniquely identified 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 defined by its attributes. The type of information to be stored in a table is defined 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 different or same data types and sets of values (domains), respectively. Possible domains are alphanumeric data (strings), numbers and date formats. Oracle offers 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 of n. (+ can be memory consuming). Example: char(40)
  • varchar2(n): Variable-length character string. The maximum size for n is 2000 (4000 in Oracle8). Only the bytes used for a string require storage. Example: varchar2(80)
  • number(o, d): Numeric data type for integers and reals. o = overall number of digits, d = number of digits to the right of the decimal point. Maximum values: o =38, d= ?84 to +127. Examples: number(8), number(5,2) Note that, e.g., number(5,2) cannot contain anything larger than 999.99 without resulting in an error. Data types derived from number are int[eger], dec[imal], smallint and real.
  • date: Date data type for storing date and time. The default format for a date is: DD-MMM-YY. Examples: ’13-OCT-94’, ’07-JAN-98’.
  • long: Character data up to a length of 2GB. Only one long column is allowed per table.

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 19

2. SQL*Plus (Minimal User Guide, 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 Check Constraints
      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

Download
PDF Ebook Oracle/SQL Tutorial

»

Posted in :