C H A P T E R

1
Guide to Oracle 10g
Chapter 2 Creating and Modifying Database Tables
2
Database Objects

• An Oracle database consists of multiple user
  accounts
• Each user account owns database objects
• Tables
• Views
• Stored programs
• Etc.

3
Database Queries

• Query command to perform operation on database
  object
• Create
• Modify
• View
• Delete
• Structured Query Language (SQL)
• Standard query language for relational databases

4
SQL Command Types

• Data Definition Language (DDL)
• Used to create and modify the structure of
  database objects
• Data Manipulation Language (DML)
• Used to insert, update, delete, and view database
  data

5
DDL Commands

• Used to create and modify the structure of
  database objects
• CREATE
• ALTER
• DROP
• DDL commands execute as soon as they are issued,
  and do not need to be explicitly saved

6
DML Commands

• Used to insert, view, and modify database data
• INSERT
• UPDATE
• DELETE
• SELECT
• DML commands need to be explicitly saved or
  rolled back
• COMMIT
• ROLLBACK

7
User Accounts

• Each Oracle database user has a user schema
• Area in the database where the users database
  objects are stored
• Identified by a unique username and protected by
  a password
• Each user schema is granted specific privileges

8
Types of Database Privileges

• System Privileges
• Control the operations that the user can perform
  within the database
• Connecting to the database (Create Session),
  creating new tables, shutting down the database,
  etc.
• Object Privileges
• Granted on individual database objects
• Controls operations that a user can perform on a
  specific object (insert data, delete data, etc.)
• When you create an object in your user schema,
  you can then grant object privileges on that
  object to other database users

9
Break Time SQL Plus

• Oracle SQL command line utility for issuing SQL
  commands
• Starting SQL Plus

LOGON to YOUR Oracle Account
10
How to Access Your Oracle Account
1. Click the START button, point to Programs 2.
Select Oracle Oracle10g, then 3. Click
Application Development, then 4. Select SQL PLUS
User Name Password Host string
11
Creating New User Accounts

• Done by DBA
• Syntax
• CREATE username IDENTIFIED BY password

12
Oracle Naming Standard

• Oracle database objects must adhere to the Oracle
  Naming Standard
• 1 to 30 characters long
• Must begin with a character
• Can contain characters, numbers, and the symbols
  , _, and

13
Defining Database Tables

• To create a table, you must specify
• Table name
• Field names
• Field data types
• Field sizes
• Constraints

14
Table and Field Names

• Must follow the Oracle Naming Standard
• Each table in a user schema must have a unique
  name within that user schema
• Each field in a table must have a unique name
  within that table

15
Creating a Table
CREATE TABLE tablename (fieldname1
data_type, (fieldname2 data_type, )
16
Oracle Data Types

• Data type specifies type of data stored in a
  field
• Date, character, number.
• LONG, RAW, LONG RAW, BLOB
• Uses
• Error checking
• Efficient use of storage space

17
Oracle Character Data Types

• VARCHAR2
• columnname VARCHAR2(max_size)
• Variable-length character strings
• Max_size can be between 1 and 4,000 characters
• Must specify the size
• No trailing blank spaces are added
• If more than max_size data is inserted, an error
  occurs.
• Example declaration
• student_name VARCHAR2(30)

18
Character Data Types

• CHAR
• columnname CHAR(max_size)
• Fixed-length character data
• Max_size can be between 1 and 2000 characters
• Max_size is optional. Default is 1.
• Adds trailing blank spaces to pad width
• If more than max_size data is inserted, an error
  occurs.
• Example declaration
• student_gender CHAR(2)

19
Character Subtypes
Examples VARCHAR2(5) Smith or
Smi CHAR(5) Smith or Smi
20
Question Which query will possibly generate
student information?
s_last CHAR(15) SELECT s_last, s_first,
s_address FROM student WHERE s_last Smith
s_last VARCHAR2(15) SELECT s_last, s_first,
s_address FROM student WHERE s_last Smith

• What data type should be used if there is any
  chance that all column spaces will NOT be filled?
• Answer VARCHAR2

21
Character Data Types

• 3. NVARCHAR2 and NCHAR
• Analogous to VARCHAR2 and CHAR but use Unicode
  rather than ASCII
• Used to hold character data in languages other
  than English (Japanese).

22
Number Data Type

• NUMBER
• stores negative, positive, fixed, and floating
  point numbers values between 10-130 and 10126
• General declaration format
• variable_name NUMBER(precision, scale)

23
NUMBER Data Types

• Number type (integer, fixed point, floating
  point) specified by precision and scale
• Precision total number of digits on either side
  of the decimal point. It does not include the
  decimal point itself or any commas or any
  formatting symbols.
• Scale number of digits to right of decimal point

24
Integer Numbers

• Whole number with no digits to right of decimal
  point
• Precision is maximum width
• Scale is omitted
• Sample declaration
• s_age NUMBER (2)

25
Fixed Point Numbers

• Contain a specific number of decimal places
• Precision is maximum width
• Scale is number of decimal places
• Sample declaration
• item_price NUMBER(5, 2)
• 259.99 33.89 (decimal point is not included)

26
Floating Point Numbers

• Contain a variable number of decimal places
• Precision and scale are omitted
• Sample declaration
• s_GPA NUMBER

27
Date Data Type

• DATE
• Stores dates from 1/1/4712 BC to 12/31/4712 AD
• Stores both a date and time component
• Default date format
• DD-MON-YY HHMISS AM
• example 05-JUN-03 120000 AM
• Sample declaration
• s_dob DATE

28
Specifying Date and Time Values

• If no time value is given when a new date is
  inserted, default value is 120000 AM
• If no date value is given when a new time is
  inserted, default date is first day of current
  month

29
TIMESTAMP Data Type

• The same as Date DT, but it stores also
  fractional seconds.
• Field Timestamp(Fr_Se_Precision)
• E.g ship_dt Timestamp(2)
• Fractional Seconds Precision default value is 6
  (If omitted).

30
Interval Year to Month Data Type

• Field Interval Year(Y_Pr) To Month.
• Y_Pr Year Precision(Default 6).
• E.g elapsed Interval Year(2) To Month.
• Possible Values
• 02-11 add 2 years and 11 months to a known
  date.
• -11-4subtract 11 years and 4 months.

31
Interval Day to Second Data Type

• Field Interval Day(D_Pr) To Second(Fr_Se_pr).
• D_Pr Day Precision(Default 2).
• Fr_Se_Pr Fractional Seconds Precision (Default
  6).
• Possible value
• -04 032032.00
• (Days HoursMinutesSeconds.Fractions)

32
Large Object (LOB) Data Types

• Binary Large Object (BLOB)
• Stores up to 4 GB of binary data
• Character Large Object (CLOB)
• Stores up to 4 GB of character data
• BFILE
• Stores a reference to a binary file maintained in
  the operating system
• NCLOB
• Character LOB that supports 16-bit character code

33
6. Large Object (LOB) Data Types
Ex f_image BLOB
34
Declaring LOB Data Fields

• Item size is not specified
• Examples
• item_image BLOB
• item_image BFILE

35
Creating a Database Table

• Syntax
• CREATE TABLE table_name
• ( fieldname1 datatype,
• fieldname2 datatype, )
• Example
• CREATE TABLE my_students
• ( s_id NUMBER(6),
• s_name VARCHAR2(30),
• s_dob DATE,
• s_class CHAR(2))

36
Constraints

• Rules that restrict the values that can be
  inserted into a field
• Types of constraints
• Integrity define primary and foreign keys
• Value specify values or ranges of values that
  can be inserted

37
Constraint Levels

• Table constraint
• Restricts the value of a field with respect to
  all other table records
• Example primary key value must be unique for
  each record
• Column constraint
• Restricts values in a specific column
• Example values in an S_GENDER field must be M
  or F

38
Constraint Names

• Internal name used by DBMS to identify the
  constraint
• Each constraint name in a user schema must be
  unique
• If you do not name a constraint, the system will
  automatically generate an unintuitive name starts
  with SYS_Cn. n is a numeric value.

39
Constraint Names

• Constraint naming convention
• tablename_fieldname_constraintID
• Constraint ID values
• Primary key pk
• Foreign key fk
• Check condition cc
• Not NULL nn
• Unique uk
• Example constraint name
• my_students_s_id_pk

40
Constraint Names
10g too
41
Primary Key Constraints

• Table-level
• Defining a primary key
• CONSTRAINT constraint_name PRIMARY KEY
• Example
• s_id NUMBER(6)
• CONSTRAINT student_s_id_pk PRIMARY KEY

42
Primary Key Constraints

• Can be defined when field is declared

43
Primary Key Constraints

• Can also be defined after all table field
  definitions are completed

44
Composite Primary Keys

• Syntax
• CONSTRAINT constraint_name
• PRIMARY KEY (field1, field2)
• Must be defined after fields that compose key are
  defined

45
Foreign Key Constraints

• Table-level
• Can only be defined after field is defined as a
  primary key in another table
• Syntax
• CONSTRAINT constraint_name
• REFERENCES primary_key_table_name (field_name)

46
Foreign Key Constraints

• Can be defined when field is declared

NOTE faculty TABLE MUST EXIST BEFORE my_students.
47
Foreign Key Constraints

• Can also be defined after all table field
  definitions are completed

48
Value Constraints

• Column-level
• Restricts data values that can be inserted in a
  field
• In general, avoid value constraints because they
  make the database very inflexible

49
Types of Value Constraints

• Check condition restricts to specific values
• Example s_gender (M or F)
• CONSTRAINT my_students_s_gender_cc
• CHECK (s_gender M) OR (s_gender F)
• Not NULL specifies that a field cannot be NULL
• Example
• CONSTRAINT my_students_s_dob_nn
• NOT NULL

50
Types of Value Constraints

• Default specifies a default value that is
  inserted automatically unless the user insert an
  other value
• Example Must be created in the column
  declaration, NOT a separate command beginning
  with CONSTRAINT.
• s_state CHAR(2) DEFAULT WI
• Unique
• Table constraint
• Specifies that a non-primary key field must have
  a unique value
• CONSTRAINT consultant_c_email_uk UNIQUE (c_email)
• (Primary key constrain does not allow NULL, but
  this does)

51
SQLPlus

• Oracle SQL command line utility for issuing SQL
  commands
• Starting SQLPlus

52
Using SQLPlus

• All commands must be terminated with a semicolon
  ()
• Use a text editor and copy and paste commands
• Character data is case sensitive and must be in
  single quotes
• M
• Sarah
• Commands are NOT case sensitive.

53
Exiting SQLPlus

• Type exit at SQLgt prompt
• or
• Click Close button on SQLPlus window

54
Oracle Help Resources

• Ora.hlp file
• Oracle Technology Network (OTN)
• http//otn.oracle.com

55
Create a Table
56
Using Notepad

• Useful to use Notepad to edit sql commands
• Commands can be edited without retyping
• Commands can be saved
• Saving multiple sql commands in a file creates a
  script

57
Study
Can you create TABLE student now?
58
Table Creation Sequence

• When creating tables with foreign key references,
  create referenced tables first
• Always create tables without foreign keys before
  those with foreign keys

59
Viewing Table Information

• Viewing a tables structure
• DESCRIBE table_name

60
Oracle Data Dictionary

• Contains tables that describe the database
  structure
• Is in the SYSTEM user schema
• Is automatically updated as users create and
  modify tables
• Cannot be updated directly
• Contains views that allow users to retrieve
  information about the database structure
• View is a db object that the DBMS bases on an
  actual db table and which enables the DBMS to
  present the table data in a different format
  based on user needs. It can serve to hide some
  table columns in which the user has no interest
  or doesnt have privileges to view.

61
Data Dictionary Views

• Views present data in different formats depending
  on the privileges of the user
• USER shows all objects belonging to the current
  user
• ALL shows all objects belonging to the current
  user, as well as objects current user has
  privileges to manipulate
• DBA allows users with DBA privileges to view
  objects of all database users

62
Querying the Data Dictionary Views

• Syntax
• SELECT view_columnname1, view_columnname2,
• FROM prefix_object

Try DESCRIBE user_tables to see the details of
that table.
63
Viewing Information About Tables

• General command
• SELECT view_columnname1, view_columnname2,
• FROM prefix_object
• view_columnname1, view_columnname2, are the name
  of the fields you want to retrieve from the view.
• prefix either USER, ALL or DBA.
• object the type of the DB object you are
  examining. See next slide for these objects.
• Ex select table_name from user_tables
• Table_name is a field name in the table
  user_tables.
• User_tables is a table saves the names of all the
  table you (user) create.

64
Database Objects with Data Dictionary View
Object Name Object Type
OBJECTS All database objects
TABLES Database tables
INDEXES Table indexes created to improve query performance
VIEWS Database views
SEQUENCES Sequences created to automatically generate surrogate key values
USERS Database users
CONSTRAINTS Table constraints
CONS_COLUMNS Table columns that have constraints
IND_COLUMNS Indexed columns
TAB_COLUMNS All table columns
65
Viewing Constraints on One Table
Try DESCRIBE user_constraints to see the
details of that table.
66
Modifying Tables

• Unrestricted actions some specifications can
  always be modified.
• Renaming tables
• Adding new columns
• Increasing column sizes
• Dropping columns
• Dropping constraints

67
Modifying Tables

• Restricted actionsspecifications modified only
  in certain situations
• Dropping tables
• Only allowed if table does not contain any fields
  that are referenced as foreign keys, or if
  foreign key constraints are dropped
• Changing a columns data specification
• Only allowed if existing data is compatible with
  new data specification
• Decreasing column sizes
• Only allowed if column does not contain any data
• Adding constraints
• Only allowed if existing data meets requirements
  of new constraint

68
Deleting and Renaming Tables

• To delete
• DROP TABLE tablename
• Use with caution. It is a restricted actions, can
  not be dropped if it contains a foreign key.
  Delete the constraint and then drop the table or
  use cascade.
• To delete foreign key constraints, add
• CASCADE CONSTRAINTS
• To rename
• RENAME old_tablename TO new_tablename
• DBMS automatically transfers to the new table
  integrity constraints, indexes, and privileges
  that referenced the old table.
• Views and stored program units that reference the
  old table name become Invalid.

69
Altering Tables

• Adding a new field
• ALTER TABLE tablename
• ADD (fieldname field_specification)

70
Altering Tables

• Modifying an existing field
• ALTER TABLE tablename
• MODIFY (fieldname new_field_specification)

• Can only change data type to compatible data type
  (i.e. varchar2 to char)

71
Altering Tables

• Deleting an existing field
• ALTER TABLE tablename
• DROP COLUMN fieldname

ALTER TABLE faculty ADD (faculty_rank
VARCHAR2(4))
72
Renaming a Column

• To rename a field
• ALTER TABLE tablename
• RENAME COLUMN old_fieldname TO new_fieldname

Ex ALTER TABLE faculty RENAME COLUMN
faculty_rank to f_rank
73
Adding and Deleting Constraints

• Add a constraint
• ALTER TABLE tablename
• ADD CONSTRAINT constraint_name
  constraint_definition
• Remove a constraint
• ALTER TABLE tablename
• DROP CONSTRAINT constraint_name

Examples ALTER TABLE faculty ADD CONSTRAINT
faculty_f_pin_uk UNIQUE (f_pin) ALTER TABLE
faculty DROP CONSTRAINT faculty_f_pin_uk
74
Enabling and Disabling Constraints

• When modifying a database it can be useful to
  disable constraints
• Constraints are enabled by default
• To disable a constraint
• ALTER TABLE tablename
• DISABLE CONSTRAINT constraint_name
• To enable a constraint
• ALTER TABLE tablename
• ENABLE CONSTRAINT constraint_name

• ALTER TABLE faculty
• DISABLE CONSTRAINT faculty_loc_id_fk
• ALTER TABLE faculty
• ENABLE CONSTRAINT faculty_loc_id_fk

• DROP TABLE faculty
• CASCADE CONSTRAINTS
• EXIT

75
Deleting Tables

• Syntax to delete table if no table fields are
  referenced as foreign keys
• DROP TABLE tablename
• Syntax to delete table and constraints if table
  contains fields that are referenced as foreign
  keys
• DROP TABLE tablename CASCADE CONSTRAINTS

76
Summary

• SQL commands include
• Data description language (DDL) commands create,
  modify, Deleted database objects
• Data manipulation language (DML) commands
  insert, update, delete, view database data
• To create a table
• specify the table name, the name of each data
  field, and the data type and size of each data
  field
• Data types ensure correct data values
• Constraints restrict values of database fields
• SQLPlus commands are not case sensitive

77
Summary (cont.)

• Errors include line number, position, error code
• Use DESCRIBE command to display a tables
  fieldnames and data types
• Tables can be modified or deleted but some
  changes are restricted

78
End Of Chapter 2