Table Creation

Types of Tables

→ Relational Tables

→ Object Table
→ Temporary Table – save data through session or a transaction
→ Index organized table – save table with it's index order by index
→ Cluster Table – joins data from multiple tables ordered by cluster index
→ External Table – access from an external file like CSV, TXT files
→ XML Table – access data as XML file
→ Partitioned Table – split large table to multiple tables

Methods of Tables Creation

1. Tools
→ SQL Plus command line tool
→ Enterprise Manager Web Based tool
→ SQL Developer Java Based Application
→ Third Party tools – like “Toad”

2. Scripted
→ @createsalestbl.sql

3. Create Table as Subquery

Syntax create_table::=

1. Example: Creating a Table

CREATE TABLE hr.admin_emp (
         empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(15) NOT NULL,
         ssn        NUMBER(9) ENCRYPT,
         job        VARCHAR2(10),
         mgr        NUMBER(5),
         hiredate   DATE DEFAULT (sysdate),
         photo      BLOB,
         sal        NUMBER(7,2),
         hrly_rate  NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),
         comm       NUMBER(7,2),
         deptno     NUMBER(3) NOT NULL
                     CONSTRAINT admin_dept_fkey REFERENCES hr.departments
                     (department_id))
   TABLESPACE admin_tbs
   STORAGE ( INITIAL 50K);

COMMENT ON TABLE hr.admin_emp IS 'Enhanced employee table';

2. Creating a Temporary Table

The ON COMMIT clause indicates if the data in the table is
     a. transaction-specific (the default) - DELETE ROWS
     b. session-specific - PRESERVE ROWS

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS from user_tablespaces;

Tables

• Hold relation data

• Composed of rows and columns
• Row – individual instances of information
• Columns – individual types of information
• Referred to as 'heap organized' table
• Datatypes
  • CHAR – fixed width of character
  • VARCHAR2 – variable width of character
  • Number(p,s)
    • precision(max number of digits)
    • scale (number of digits to the right)
    • Eg. NUMBER(5.2) = 456.78 But NOT 4567.8
  • DATE – containing date and time information
• Tables and column Naming Rules
  • Names between 1 and 30 characters
  • only alphanumeric and #,_,$
  • Must begin with letter between A and Z
  • Reserved words can't be used
  • Most of these rules can be overridden by using
• CREATE TABLE syntax

CREATE TABLE table_name (

column 1 datatype,

column 2 datatype,

…...

column datatype

);

create table test_table (

column1 char(5),

column2 varchar2(5),

column3 number(5,2),

column4 date

);

desc test_table;

select * from test_table;

Partitions

• Partitioning – the act of breaking a large table into manageable pieces
• Must be based on a partition key
• Not preferable in every situation

- types of partitions

1. Rang - based on range of values
2. Hash - based on hashing algorithm
3. List - based on fixed list of values
4. Composite - combinations of types into sub-partitions may be in one level based in rang partitioning and next level hash partitioning