Chapter 2: Creating and Modifying Database Tables

1
Chapter 2Creating and ModifyingDatabase Tables

• Jason C. H. Chen, Ph.D.
• Professor of MIS
• School of Business Administration
• Gonzaga University
• Spokane, WA 99258
• chen_at_jepson.gonzaga.edu

2
Objectives

• Become acquainted with Structured Query Language
  (SQL)
• Learn about Oracle10g user schemas
• Learn how to define Oracle10g database tables
• Create database tables using SQLPlus
• Learn how to debug Oracle10g SQL commands and
  use Oracle Corporation online help resources
• Learn how to view information about your database
  tables using Oracle10g data dictionary views
• Modify and delete database tables using SQLPlus

3
Database Objects and Queries

• An Oracle database consists of multiple user
  accounts
• Each user account owns database objects
• Tables
• Views
• Stored programs, etc.
• Query command to perform operation on database
  object
• Structured Query Language (SQL)
• Industry standard query language for most of
  relational databases
• Consists of about 30 commands
• SQL-99
• Most recent version

4
Basic SQL Concepts and Commands
SQL (Structured Query Language) is used to
manipulate the database. There are two basic
types of SQL commands DDL commands work with
the structure of the objects (tables, indexes,
views) in the database. DML commands work with
the data in the database (i.e.,manipulate the
data). Reserved words - SQL command words

• Data ___________ Language (DDL)
• Data ___________ Language (DML)

5
SQL Components
ORACLE (SQL Components)
D__L
D.B.
D__L
D__L
6
DDL Commands

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

7
DML Commands

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

8
Security -Granting Table Privileges

• Security is the prevention of unauthorized access
  to the database. Within an organization, the
  database administrator determines the types of
  access various users need for the database.
• Some users might be able to retrieve and update
  data in the database. Other users might be able
  to retrieve any data from the database but not
  make any changes to it. Still other users might
  be able to access only a portion of the database.

9
Oracle10g User Accounts

• User ______ - identified by a unique username and
  password
• User _____ - all of the objects that the user
  creates and stores in the database
• Database _____
• Also called schema objects
• Objects in user schema
• Object owner has privileges to perform all
  possible actions on an object

10
Break Time SQL Plus

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

LOGON to YOUR Oracle Account
11
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 ltyour lastnamegt
• Password ltgt (to be announced)
• Host string SBAORA10

12
Types of Database Privileges

• System Privileges
• Control the operations that the user can perform
  within the database
• Create user accounts
• Connecting to the database, 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

Read pp. 112-114
13
A. Names and PropertiesWhy need a name?
14
Creating a Table

• CREATE TABLE tablename
• (fieldname1 data_type (size),
• fieldname2 data_type (size),
• )

15
Defining Oracle10g Database Tables

• To create a table, you must specify
• ______ name
• ______ names
• Field _________
• Field ______

• ______
• restrictions on the data values that a field can
  store

16
I. Names and Properties Conventions
Series of rules Oracle Corporation established
for naming all database objects

• From ________ characters
• Only alphanumeric characters, and special
  characters ( , _, )
• Must begin with a _____ and can not contain blank
  spaces or hyphens

• Are the following names valid? Why?
• customer order
• customer-order
• order

17
Oracle 10g Data Types

• Data type
• Specifies kind of data that column stores
• Provides means for error checking
• Enable DBMS to use storage space more efficiently
  by internally storing different types of data in
  different ways
• Basic types
• _________
• _________
• _________
• Large object

18
II. Data Types

• Built-in
• provided by the system
• Library
• built by the software vendor or a third party
• User-defined
• built by users

19
Basic Built-In Data Types

• Character
• VARCHAR2
• CHAR
• NCHAR2 / NCHAR
• Numeric
• NUMBER
• DATE
• OTHERS
• LOB, BLOB, LONG, RAW, LONG RAW

20
Character Data Types

• 1. VARCHAR2
• Stores ______-length character data up to a
  maximum of 4,000 characters
• Values in different records can have a different
  number of characters
• fieldname VARCHAR2(maximum_size)
• (e.g.) emp_name VARCHAR2(20)
• an instance Jason Chen

21
Character Data Types (cont.)

• 2. CHAR
• ______-length character data (lt 2000
  characters)
• default is 1 if no field size is specified
• Data values for different records all have the
  same number of characters
• DBMS adds trailing blank spaces to the end of the
  entry to make the entry fill the maximum_size
  value
• Data longer than maximum_size causes an error
• fieldname CHAR(maximum_size)
• pros use data storage space more efficiently and
  processed faster
• cons causes inconsistent query results in other
  Oracle applications
• e.g. s_class CHAR(2)
• SR JR SO FR GR
• s_state CHAR(2) DEFAULT WI
• student_gender CHAR

22
Character Subtypes
Examples VARCHAR2(5) Smith or
Smi CHAR(5) Smith or Smi
LONG Smith... Note that you do not specify
a size for LONG. To include a single quote in a
literal character string, use two in a row This
is Heralds string.
23
Question Which query will possibly generate
student information?
s_last VARCHAR2(15) SELECT s_last, s_first,
s_address FROM student WHERE s_last Smith
s_last CHAR(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 ________________

L
24

• When use Query
• SELECT s_last, s_first, ssn, telephone FROM
  student
• WHERE s_last Smith
• Case is sensitive within the single quotation.
• SQL Plus commands are NOT case sensitive, but
  Query within the single quotation are case
  sensitive.

25
4. Number Data Types

• Stores negative, positive, fixed, and floating
  point numbers between
• 10 -130 lt
  lt10 126
• precision up to 38 decimal places
• General Syntax
• fieldname NUMBER (precision,
  scale)
• Integer fieldname NUMBER(precision)
• Fixed point fieldname NUMBER(precision,scale
  )
• Floating point fieldname NUMBER

26
Number Data Types (examples)

• a) Integer Number (n)
• e.g. s_id NUMBER(5)
• 12345
• b) Fixed-point numbers
• e.g. current_price NUMBER (5, 2)
• 259.99 33.89
• c) Fixed-point numbers (cont.)
• e.g. total_mileage NUMBER (5, 1)
• 259.9 33.8
• d) Floating-point Number with a variable number
  of decimal places
• e.g. s_gpa NUMBER
• 3.89 2.7569 3.2

27
5. Date and Time Data Types

• Date, time data subtypes
• Store actual date and time values
• DATE
• TIMESTAMP
• Interval data subtypes (see course_section in
  the northwoods database)
• Store elapsed time interval between two datetime
  values
• INTERVAL YEAR TO MONTH
• TO_YMINTERVAL(3-2) (ch.3, p.95)
• INTERVAL DAY TO SECOND

28
Date and Time Data Types (cont.)

• DATE
• Dates from December 31, 4712 BC to December 31,
  4712 AD
• Default format DD-MON-YY
• Default time format HHMISS A.M.
• fieldname DATE
• Sample declaration
• s_dob DATE
• Use one of the following format masks
• TO_DATE ( ,
  MM/DD/YY)
• TO_DATE ( ,
  DD-MON-YYYY)
• TO_DATE ( ,
  HHMI AM)

29
6. Large Object (LOB) Data Types

• Store binary data such as
• Digitized sounds or images
• References to binary files from word processor or
  spreadsheet
• General syntax
• columnname Lob_data_type

30
Homework

• Explain Clearwater Traders database (pk fk
  etc.)
• Show the result the order of entering the data
  for Clearwater Traders database
• Your homework
• Study the Northwoods University database
• Write the order of entering the data for
  Northwoods University database

31
B. Constraints

• In the class we will study two databases from the
  text.
• Northwoods University database
• Clearwater Traders database

32
What is a Constraint?

• A ________________________ that can be inserted
  into a field
• A mechanism used to protect
• the relationship between data within an Oracle
  table, or
• the correspondence between data in two different
  tables.
• For example, the state entered must be one of the
  50 states in the U.S.

33
Types of Constraints

• _________ constraints define primary and foreign
  keys
• _______ constraints define specific data values
  or data ranges that must be inserted into columns
  and whether values must be unique or not NULL
• _______ constraint restricts the data value with
  respect to all other values in the table
• __________ constraint limits the value that can
  be placed in a specific field, irrespective of
  values that exist in other table records

34
I. Naming conventions for constraints

• lttablenamegt_ltfieldnamegt_ltconstraint idgt
• Where ltconstraint idgt is
• pk PRIMARY KEY
• fk REFERENCES lttablenamegt (pk)
• cc CHECK ltcondition to be checkedgt (note that
  cc stands for CHECK CONDITION)
• nn NOT NULL
• uk UNIQUE

e.g., s_id NUMBER (6) CONSTRAINT student_s_id_pk
PRIMARY KEY
35
Integrity Constraints

• Define primary key fields
• Specify foreign keys and their corresponding
  table and column references
• Specify composite keys

36
Creating a Table

• CREATE TABLE tablename
• (fieldname1 data_type (size),
• fieldname2 data_type (size),
• )

Create a table with the following
information Name of the table
my_students Fields 1) s_id number with 6
digits, 2) s_name character with 30 chars, 3)
s_class with 2 chars, 4) s_dob
37
Primary Key Constraints

• Table-level
• Can be defined when field is declared
• Syntax
• CONSTRAINT constraint_name PRIMARY KEY

• Create a table with the following information
• Name of the table my_students
• Fields s_id number with 6 digits, s_name
  character with 30 chars, s_class with 2 chars,
  s_dob

• SQLgt CREATE TABLE my_students
• (s_id NUMBER(6) CONSTRAINT
  my_students_s_id_pk PRIMARY
• KEY,
• 3 s_name VARCHAR2(30),
• s_class CHAR(2),
• s_dob DATE)

38
Primary Key Constraints (cont.)

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

• SQLgt CREATE TABLE my_students
• (s_id NUMBER(6) CONSTRAINT
  my_students_s_id_pk PRIMARY
• KEY,
• 3 s_name VARCHAR2(30),
• s_class CHAR(2),
• s_dob DATE)

Practice Type in one of the command.

• SQLgt CREATE TABLE my_students
• 2 (s_id NUMBER(6),
• 3 s_name VARCHAR2(30),
• s_class CHAR(2),
• s_dob DATE,
• CONSTRAINT my_students_s_id_pk PRIMARY KEY
  (s_id))

39
Common ConstraintID Abbreviations

• Constraint definitions should be placed either
• At end of CREATE TABLE command after table
  columns declared
• Within each column definition

40
Primary Key Constraints (cont.)
You will learn how to create the following SQL
(DDL) command -- chapter 2, Figure 2-3 p.
60 CREATE TABLE location (loc_id
NUMBER(6), bldg_code VARCHAR2(10), room
VARCHAR2(6), capacity NUMBER(5), CONSTRAINT
location_loc_id_pk PRIMARY KEY (loc_id))
-- chapter 2, Figure 2-7 p. 66 DESCRIBE
location SQLgt SELECT TABLE_NAME FROM USER_TABLES
41

• For examples Integrity Constraint
• loc_id NUMBER(6) CONSTRAINT faulty_loc_id_fk
  REFERENCES location (loc_id)

or CONSTRAINT
faulty_loc_id_fk FOREIGN KEY (loc_id) REFERENCES
location (loc_id)
42
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)

43
Foreign Key Constraints

• Can be defined when field is declared

CREATE TABLE faculty (f_id NUMBER(6) CONSTRAINT
faculty_f_id_pk PRIMARY KEY(f_id), f_last
VARCHAR2(30), f_first VARCHAR2(30), f_mi
CHAR(1), loc_id NUMBER(5) CONSTRAINT
faculty_loc_id_fk REFERENCES location(loc_id) f_p
hone VARCHAR2(10), f_rank VARCHAR2(8), f_pin
NUMBER(4), f_image BLOB, ) -- chapter 2, Figure
2-7 p. 66 DESCRIBE location DESCRIBE
faculty SQLgt SELECT TABLE_NAME FROM USER_TABLES
N
44
Foreign Key Constraints (cont.)

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

-- Figure 2-6, p.65 CREATE TABLE faculty (f_id
NUMBER(6), f_last VARCHAR2(30), f_first
VARCHAR2(30), f_mi CHAR(1), loc_id
NUMBER(5), f_phone VARCHAR2(10), f_rank
VARCHAR2(8), f_pin NUMBER(4), f_image BLOB,
CONSTRAINT faculty_f_id_pk PRIMARY
KEY(f_id), CONSTRAINT faculty_loc_id_fk FOREIGN
KEY (loc_id) REFERENCES location(loc_id)) SQLgt
SELECT TABLE_NAME FROM USER_TABLES
45
Composite Primary Keys

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

-- compare version on p.56 CREATE TABLE
ENROLLMENT (s_id VARCHAR2(6), c_sec_id
NUMBER(6), grade CHAR(1), CONSTRAINT
enrollment_pk PRIMARY KEY (s_id,
c_sec_id), CONSTRAINT enrollment_sid_fk FOREIGN
KEY (s_id) REFERENCES student(s_id), CONSTRAI
NT enrollment_c_sec_id_fk FOREIGN KEY (c_sec_id)
REFERENCES course_section (c_sec_id))
46

Integrity and Value Constraints Other Examples
(cont.)
CREATE TABLE faculty (f_id NUMBER(6) CONSTRAINT
faculty_f_id_pk PRIMARY KEY, f_last VARCHAR2(30)
CONSTRAINT faculty_f_last_nn NOT NULL, f_first
VARCHAR2(30) CONSTRAINT faculty_f_first_nn NOT
NULL, f_mi CHAR(1), loc_id NUMBER(5) CONSTRAINT
faculty_loc_id_fk REFERENCES location(loc_id), f
_phone VARCHAR2(10), f_rank VARCHAR2(4)
CONSTRAINT faculty_f_rank_cc CHECK ((f_rank
INST') OR (f_rank ASST') OR (f_rank 'ASSO')
OR (f_rank FULL')), f_pin NUMBER(4) CONSTRAINT
faculty_f_pin_uk UNIQUE, f_image BLOB)
47
Naming conventions for constraints

• lttablenamegt_ltfieldnamegt_ltconstraint idgt
• Where ltconstraint idgt is
• pk PRIMARY KEY
• fk REFERENCES lttablenamegt (pk)
• cc CHECK ltcondition to be checkedgt (note that
  cc stands for CHECK CONDITION)
• nn NOT NULL
• uk UNIQUE

e.g., s_state CHAR(2) DEFAULT WI
48
Types of Value Constraints

• Check condition restricts to specific values
• CONSRAINT student_s_class_cc CHECK
  ((s_class'FR') OR (s_class'SO') OR
  (s_class'JR') OR (s_classSR'))
• CONSTRAINT course_credits_cc CHECK ((creditsgt?)
  AND (credits lt18))
• Not NULL specifies that a field cannot be NULL
• s_last VARCHAR2(30) CONSTRAINT student_s_last_nn
  NOT NULL

49
Value Constraints Other Examples (cont.)

• 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
Summary on Value Constraints (cont.)

• Check conditions field value must be a specific
  value or fall within a range of values
• NOT NULL constraints specify whether a field
  value can be NULL
• Default constraints specify that a field has a
  default value that the DBMS automatically inserts
  for every record, unless the user specifies an
  alternate value
• Unique constraints specify that a field must
  have a unique value for every table record

51
Types of Value Constraints (cont.)

• Default specifies a default value that is
  inserted automatically
• s_state CHAR(2) DEFAULT WI
• Unique
• Table constraint
• Specifies that a non-primary key field must have
  a unique value
• CONSTRAINT term_term_desc_uk UNIQUE (term_desc)

52
Creating Database Tables Using SQLPlus

• Type SQL commands at the SQL prompt
• End each command with a semicolon ()
• Press Enter to submit commands
• Not case sensitive

• 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

53
Log On to SQLPlus
54
Practice Time
Read Figures 2-3, 2-5, on pp. 60-63 Then create
a right table as shown in Figure 2-3 (p.60) ---
next slide
55
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

56
Study
Can you create TABLE student now?
Copy Data Files (entire OraData folder) to C
drive and floppy from the following path \My
Network Place\Entire Network\Microsoft Windows
Network\ Gonzaga\Jepsonnt2\chen\bmis441\OraData
57
Homework
Email me with one attachment (Oracle2_Spool_Lname
_Fname.) to chen_at_jepson.gonzaga.edu with subject
title of bmis441_Oracle2 mbus673_Oracle2
Problem-Solving Cases

• Read and Practice Chapters 1, 2
• 1. Run two script files dropclearwater.sql and
  dropnorthwoods.sql
• 2. Study how to create DDL from the file
  emptyclearwater.sql
• 3. Determine the order of entering the data for
  northwoods database.
• 4. Read Oracle assignment and create two script
  files (2Case1.sql and 2Case2.sql) for the
  northwoods database. Execute and test one table a
  time and make sure they are all running
  successfully.
• 3. When you done, spool the script files (see
  next slide for spooling instructions) and email
  the file (Oracle2_Spool_Lname_Fname.lst) to me
  by the midnight before the next class.

58
Hints on Oracle2 HW
-- script to create part of NORTHWOODS database
structure -- BMIS441, ROW 1 --File name
2Case1.sql -- modified 8/11/2006 jchen DROP
TABLE enrollment CASCADE CONSTRAINTS DROP TABLE
course_section CASCADE CONSTRAINTS DROP TABLE
term CASCADE CONSTRAINTS DROP TABLE course
CASCADE CONSTRAINTS DROP TABLE student CASCADE
CONSTRAINTS DROP TABLE faculty CASCADE
CONSTRAINTS DROP TABLE location CASCADE
CONSTRAINTS
CREATE TABLE location (loc_id NUMBER(6), bldg_code
VARCHAR2(10), room VARCHAR2(6), capacity
NUMBER(5), CONSTRAINT ) CREATE TABLE
faculty ( f_image BLOB, ) CREATE TABLE
student ( time_enrolled INTERVAL YEAR TO
MONTH,
59
How to Spool your Script and Output Files

• After you tested files of 2Case1.sql and
  2Case2.sql successfully, follow the instructions
  below to spool both script and output files
• 0. Run dropnorthwoods.sql again (since you
  have created 7 tables)
• 1. Click File, Spool then Spool File
• 2. Enter the spooled file name
  c/Oradata/Oracle2_Spool_Lname_Fname.lst
  (default file extension)
• 3. open 2Case1.sql file
• 4. copy and paste all the SQL commands (including
  all comments) to the SQLPLUS
• 5. Repeat steps 3 and 4 for the second script
  file (i.e., 2Case2.sql)
• 6. Click on File, Spool then Spool Off
• Do NOT double click the spooled file, with the
  note pad open, select FILE, OPEN then open the
  spooled file (since SPSSs default file extension
  is LST)

60
Errors - Oracle Help Resources

• When an error occurs error information is
  displayed
• Line number
• Position on line
• Error code
• Description of error
• Error codes
• 3 letter prefix (I.e. ORA)
• 5 digit code
• More information on errors can be found at
  http//otn.oracle.com
• You then select Documentation then click
  Search
• Finally, enter ORA-errorcode (e.g. ORA-09222)
  on Enter a word of phrase box
• (You might be asked for your username and
  password that you have created previously)

61
http//www.oracle.com/technology/documentation/ind
ex.html
62
(No Transcript)
63
Exiting SQLPlus

• Three ways to exit SQLPlus
• Type exit at the SQL prompt
• Click File on the menu bar, and then click Exit
• Click the Close button on the program window
  title bar
• Database session ends when SQLPlus exits

64
Viewing Information About Tables

• describe tablename displays column names and
  data types
• Data dictionary tables that contain information
  about the structure of the database.
• USER shows the objects in the current users
  schema
• ALL shows both objects in the current users
  schema and objects that the user has privileges
  to manipulate
• DBA allows users who are database administrators
  to view information about all database objects

--chapter 2, Figure 2-7 p. 66 DESCRIBE
location DESCRIBE faculty
65
Viewing Constraints on One Table
-- chapter 2, Figure 2-10 p. 70 SELECT
constraint_name, table_name, constraint_type FROM
user_constraints
p.71
66
Modifying and Deleting Database Tables

• Modify existing database tables by
• Changing the name of a table
• Adding new columns
• Deleting columns that are no longer needed
• Changing the data type or maximum size of an
  existing column
• Unrestricted action some specifications can
  always be modified
• Restricted action specifications modified only
  in certain situations

67
Deleting and Renaming Tables

• To delete
• Drop table tablename
• Use with caution
• 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.

Figure 2-12
RENAME faculty TO new_faculty
68
Adding Fields to Existing Tables

• To add a field
• ALTER TABLE tablename ADD(fieldname
  data_declaration constraints)

-- Ch.2, p.75 ALTER TABLE faculty ADD
(start_date DATE)
69
Modifying Existing Field Data Definitions

• Can only change datatype to compatible data type
  (i.e. varchar2 to char)
• ALTER tablename MODIFY(fieldname
  new_data_declaration)

-- chapter 2, p. 76 ALTER TABLE faculty MODIFY
(f_rank CHAR(4)) -- chapter 2, p. 76 ALTER
TABLE faculty RENAME COLUMN f_rank TO
faculty_rank
70
Deleting a Field

• ALTER TABLE tablename DROP COLUMN fieldname
• Can be used to rename a field first drop, then
  add

-- Ch.2, (Extra) ALTER TABLE faculty DROP COLUMN
f_rank ALTER TABLE faculty ADD (faculty_rank
VARCHAR2(4))
71
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

-- chapter 2, p. 77 ALTER TABLE faculty ADD
CONSTRAINT faculty_f_pin_uk UNIQUE
(f_pin) ALTER TABLE faculty DROP CONSTRAINT
faculty_f_pin_uk
72
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

-- p.78 ALTER TABLE faculty DISABLE CONSTRAINT
faculty_loc_id_fk ALTER TABLE faculty ENABLE
CONSTRAINT faculty_loc_id_fk
DROP TABLE faculty CASCADE CONSTRAINTS EXIT
73
(No Transcript)
74
SQL Commands

• CREATE TABLE lttablenamegt
• DROP TABLE lttablenamegt
• RENAME lttablenamegt TO ltnewtablenamegt
• Use the following commands to check your tables
• SELECT table_name FROM user_tables
• DESCRIE lttablenamegt

75
SQL Commands

• SELECT constraint_name
• FROM user_constraints
• SELECT constraint_name
• FROM user_constraints
• WHERE TABLE_NAME lttablenamegt

case sensitive within the quotation
76
SQL Commands (cont.)

• ALTER TABLE lttablenamegt
• ADD ltfieldnamegt ltdata declarationgt
• ALTER TABLE lttablenamegt
• ADD/MODIFY ltfieldnamegt ltdata declarationgt
• CONSTRAINT ltintegrity constraintsgt
• CONSTRAINT ltvalue constraintsgt
• EXIT or QUIT

77
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

78
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