Lab Session

1
Introduction To Oracle

• Lab Session

2
Introduction to Databases

• A database is a collection of Data
  (Information).  Examples of databases, which we
  use in our daily life, is  an Attendance
  Register, Telephone Directory, Muster Rule.
• Database Management System(DBMS) A database
  management system is a collection of programs
  written to manage a database. That is, it acts as
  a interface between user and database.

3
ORACLE

• Oracle is an Object-Relational Database
  Management System. It is the leading RDBMS vendor
  worldwide. Nearly half of RDBMS  worldwide market
  is owned by Oracle. 

4
ORACLE DATABASE

• Every Oracle Database Contains Logical and
  Physical Structures. Logical Structures are
  tablespaces, Schema objects, extents and
  segments. Physical Structures are Datafiles, Redo
  Log Files, Control File.
• A database is divided into logical storage units
  called tablespaces, which group related logical
  structures together. Each Tablespace in turn
  consists of one are more datafiles.

5
Creating Tables

• A table is the data structure that holds data in
  a relational database. A table is composed of
  rows and columns.

6
Designing Tables

• Consider the following guidelines when designing
  your tables
• Use descriptive names for tables, columns,
  indexes, and clusters.
• Be consistent in abbreviations and in the use of
  singular and plural forms of table names and
  columns.
• Document the meaning of each table and its
  columns with the COMMENT command.
• Normalize each table.
• Select the appropriate datatype for each column.
  
• Define columns that allow nulls last, to
  conserve storage space.
• Cluster tables whenever appropriate, to conserve
  storage space and optimize performance of SQL
  statements.

7
Datatypes

• Before creating a Table you have to decide what
  type of data each column can contain. This is
  known as datatype.  Lets Discuss what datatypes
  are available in Oracle.

Datatype  Description 
CHAR (size ) Fixed-length character data of length size bytes or characters. Fixed for every row in the table maximum size is 2000 bytes per row, default size is 1 byte per row.
VARCHAR2 (size) Variable-length character data, with maximum length size bytes or characters.  Variable for each row, up to 4000 bytes per row.
8
Datatypes
Datatype  Description
NCHAR (size) Fixed-length Unicode character data of length size characters. Fixed for every row in the table. The upper limit is 2000 bytes per row. Default is 1 character.  
NVARCHAR2 (size)  Variable-length Unicode character data of length size characters. A maximum size must be specified. Variable for each row. The upper limit is 4000 bytes per row. Default is 1 character.  
CLOB   Single-byte character data. Up to 232 - 1 bytes, or 4 gigabytes.
NCLOB   Unicode national character set (NCHAR) data. Up to 232 - 1 bytes, or 4 gigabytes.  
LONG  Variable-length character data. Variable for each row in the table, up to 232 - 1 bytes, or 2 gigabytes, per row.
9
Datatypes
Datatype Description
NUMBER (p, s) Variable-length numeric data. Maximum precision p and/or scale s is 38. Variable for each row. The maximum space required for a given column is 21 bytes per row. 
DATE  Fixed-length date and time data, ranging from Jan. 1, 4712 B.C.E. to Dec. 31, 4712 C.E. Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-RR) specified by the NLS_DATE_FORMAT parameter.  
TIMESTAMP (precision)   A value representing a date and time, including fractional seconds. (The exact resolution depends on the operating system clock.) Varies from 7 to 11 bytes, depending on the precision.
BLOB    Unstructured binary data. Up to 232 - 1 bytes, or 4 gigabytes. 
BFILE   Binary data stored in an external file . Up to 232 - 1 bytes, or 4 gigabytes. 
ROWID  Binary data representing row addresses . Fixed at 10 bytes
10
Creating Tables in Oracle

• Once you have designed the table and decided
  about datatypes use the following SQL command to
  create a table.
• For example, the following statement creates a
  table named Emp.
• CREATE TABLE Emp (
•    Empno      NUMBER(5),
•    Ename      VARCHAR2(15),
•    Hiredate   DATE,
•    Sal        NUMBER(7,2)
•   )

11
Inserting Rows

• To insert rows in the table you can use SQL 
  INSERT command.
• For example the following statement creates a row
  in the above table.
• SQLgtinsert into emp values (101,Sami,3400)
• To insert rows continuously in SQL Plus you can
  give the following command.
• SQLgtinsert into emp values (empno,name,sal)
• These Empno, name and sal  are known as
  substitution variables. That is SQLPlus will
  prompt you for these values and then rewrites the
  statement with supplied values.

12
Other Queries

• To see the rows you have inserted give the
  following command.
• SQLgt Select from emp 
• To see the structure of the table i.e. column
  names and their datatypes and widths. Give the
  following command.
• SQLgtdesc  emp
• To see how many tables are in your schema give
  the following command.
• SQLgt select from cat or
• SQLgtselect from tab