Table Names and Properties

Business DataBase Systems Name: ______

Professor Chen

ENHANCED GUIDE TO ORACLE

Chapter 2Creating and Modifying Database Tables

A.Names and Properties

1. Naming Conventions (Table and Field names)

1. from 1 to 30 characters
2. only alphanumeric characters, and special characters ($, _ ,#)
3. must begin with a letter and can not contain blank spaces or hyphens

e.g., Are the following names valid? Why?

customer order

customer-order

#order

II.Data Types

SQL*PLUS commands are NOT case sensitive (but Query within the single quotation are case sensitive).

There are three types of datatype in Oracle: builit-in, library, or user-defined. Built-in are standard, user-defined are specialized types built by users, and library types are types built by a third party and supplied to the user. The followings are most commonly used built-in data types:

1. VARCHAR2

a)variable length character up to <= 4,000 characters

b)must specify maximum width allowed

c)trailing spaces are NOT added ( truncated)

(e.g.) s_name VARCHAR2(20);

an instance: ‘JASON CHEN’

1. CHAR

a)fixed length (<=200 characters)

b)default is 1 if no field size is specified.

c)trailing blank spaces are added.

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

sstate CHAR(2) DEFAULT ‘WI’;

student_gener CHAR;

Question: What data type should be used if there is any chance that all column spaces will NOT be filled?

When use Query: SELECT slname, sfname, ssn, telephone FROM student

WHERE slname = ‘Smith’;

Case is sensitive within the single quotation.

1. NCHAR

a.) CHAR: ASCII code represents each character up to 8-digit (1 byte), binary value and can represent a total of 256 different characters.

b.) NCHAR: Similar to CHAR data type but it supports up to 16-digit (2 bytes) binary character codes for other country’s characters.

4.NUMBER (precision, scale)

10 -130 <= <=10 +126

precision up to 38 decimal places

a)Integer

Number (n)

e.g. sid NUMBER(5)

b)Fixed-point numbers

e.g. current_price NUMBER (5, 2);

259.99 33.89

c) Floating-point Number – with a variable number of decimal places

e.g. s_gpa NUMBER;

3.89 2.7569 3.2

5. DATE

a)Range is from January 1, 4712 BC to December 31, 4712 AD

b)Default data format: DD-MON-YY HH:MI:SS AM

e.g., 02-FEB-01 12:00:00 AM

c) Sample declaration: s_dob DATE;

d) Use one of the following format masks:

TO_DATE (‘ ’, ‘MM/DD/YY’);

TO_DATE (‘ ‘, ‘DD-MON-YYYY’);

TO_DATE (‘ ‘, ‘HH:MI AM’);

1. LOB: Large Object Data Types – used to store binary data such as digitized sound and images.

BLOB - Binary LOB, storing up to 4 GB of binary data in the database.

CLOB – Character LOB, storing up to 4 GB of binary data in the database.

BFILE – Binary File, storing a reference to a binary file that is located outside the database in a file maintained by the operating systems.

NCLOB – Character LOB that supports 2-byte (16-bit) character codes

Item size is not specified, e.g., item_image BLOB;

7. Others: Format Masks (see Table 3-1; page 70)

Basic SQL Commands

SQL (Structured Query Language) is used to manipulate the database. There are two types of SQL commands: Data Definition Language (DDL) and Data Manipulation Language (DML). DDL commands work with the structure of the objects (tables, indexes, views, etc.) in the database. DML commands work with data in the database.

B.INTEGRITY and VALUE CONSTRAINTS

I.Naming convention for constraints

<tablename>_<fieldname>_<constraint id>

Where <constraint id> is:

1.pk PRIMARY KEY

2.fk REFERENCES <tablename> (pk)

3ccCHECK <condition to be checked> (note that cc stands for CHECK CONDITION)

4.nn NOT NULL

5.uk UNIQUE

1 and 2 stated above are integrity constraints whereas 3,4,5 are value constraints.

For examples: Integrity Constraints

1.f_id NUMBER(5) CONSTRAINT faculty_f_id_pk PRIMARY KEY

2 loc_id NUMBER(5) CONSTRAINT faulty_loc_id_fk REFERENCES location (loc_id)

FACULTY fk

F_ID

LOCATION

LOC_ID

Value Constraints

3. a)credits NUMBER(2) CONSTRAINT course_credits_cc

CHECK ((credits>) AND (credits <=18));

b)s_class CHAR (2) CONSRAINT student_s_class_cc

CHECK ((s_class='FR') OR (s_class='SO')

OR (s_class='JR') OR (s_class='SR'));

4. term_id NUMBER(3) CONSRAINT course_section_term_id_fk

REFERENCES term (term_id)

CONSTRAINT course_section_term_id_nn NOT NULL;

1. f_pin NUMBER(4) CONSTRAINT faculty_f_pin_uk UNIQUE;

The syntax of creating a Database Table:

CREATE TABLE <tablename>

(<fieldname1> <data declaration>

CONSTRAINT <integrity constraint declaration>

CONSTRAINT <fieldname1 VALUE CONSTRAINT declaration>

<fieldname2> <data declaration>

CONSTRAINT <integrity constraint declaration>

CONSTRAINT <fieldname2 VALUE CONSTRAINT declaration>

… );

For example, to create the faculty table using SQL:

CREATE TABLE faculty

(f_id NUMBER(5) 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 CHAR(4) CONSTRAINT faculty_f_rank_cc

CHECK ((f_rank = 'ASSO') OR (f_rank = 'FULL')

OR (f_rank = 'ASST') OR (f_rank = 'INST')),

f_pin NUMBER(4));

C.STARTING SQL*Plus

I.Adding Fields to Existing Tables

ALTER TABLE <tablename>

ADD <fieldname> <data declaration>

CONSTRAINT <integrity constraints>

CONSTRAINT <value constraints>;

Note that

a) <…> is something that the user type in

b) <data declaration> may be:

<data type (size)> or

<data type (size) DEFAULT <default value>

e.g.,

1. ALTER TABLE faculty ADD (startdate DATE);

2. integrity constraint

ALTER TABLE enrollment

ADD CONSTRAINT enrollment_csecid_fk FOREIGN KEY (csecid)

REFERENCES course_section(csecid);

3. value constraint

ALTER TABLE student

ADD CONSTRAINT student_s_class_cc

CHECK ((s_class = 'FR') OR (s_class = 'SO')

OR (s_class = 'JR') OR (s_class = 'SR')

OR (s_class = 'GR'));

1. Modifying Exiting Fields

ALTER TABLE <tablename>

MODIFY <fieldname> <new data declaration>

CONSTRAINT <integrity constraints>

CONSTRAINT <value constraints>;

MODIFY clause can be used only to change

1. data type
2. data size,
3. default values, and
4. the NOT NULL check conditions

other check condition modifications, you must

1. DROP the existing column constraint, and then
2. Use the ADD clause to define the new constraint.

1. Dropping Existing Constraints

ALTER TABLE <tablename>

DROP CONSTRAINT <integrity constraints>;

or

ALTER TABLE <tablename>

DROP CONSTRAINT <value constraints>;

Summary of using the ALTER TABLE commands:

1. MOST FREQUENTLY USED SQL Commands

CREATE TABLE <tablename>

(<fieldname1> <data declaration>

CONSTRAINT <integrity constraint declaration>

CONSTRAINT <fieldname1 VALUE CONSTRAINT declaration>

<fieldname2> <data declaration>

CONSTRAINT <integrity constraint declaration>

CONSTRAINT <fieldname2 VALUE CONSTRAINT declaration>

…

… );

SELECT TABLE_NAME

FROM USER_TABLES;

SELECT CONSTRAINT_NAME

FROM USER_CONSTRAINTS;

SELECT CONSTRAINT_NAME

FROM USER_CONSTRAINTS

WHERE TABLE_NAME = ‘<tablename>’;

DESCRIBE <tablename>;

-- make sure follow the order of deleting tables

-- see emptynu.sql or northwoo.sql

DROP TABLE <tablename>;

RENAME <oldtablename> TO <newtablename>;

Chapter 3 Using Oracle to Add, View, and Update Data SQL Scripts

Before you start this chapter you should DROP all the existing tables and recreate tables’ structures (for the purpose of accuracy), you should:

1. Copy the following six files from my web site (or Jepson lab’s server):

1. Dropnorthwoods.sql (Delete tables’ structures and data)
2. Emptynorthwoods.sql (Delete tables’ structures and data and then recreate their structures ONLY)
3. Northwoods.sql (Delete tables’ structures and data and then recreate both structures and data)
4. Dropclearwater.sql
5. Emptyclearwater.sql
6. Clearwater.sql

2.Type the following commands at the SQL prompts:

SQL> START A:\chapter3\emptynu.sql (or)

SQL> @ A:\chapter3\emptynu.sql

1. I. Using SQL*Plus to Insert Data (DML)

Note that you may create all SQL commands in the chapter3 with Notepad and save them as file Commands.SQL. Therefore, you are able to reuse them by copy and paste them.

1. Insert all table fields:

INSERT INTO <tablename>

VALUES (<column 1 value>, <column 2 value>, …);

2. Insert selected table fields:

INSERT INTO <tablename>(<first column name>, <second column name>, …)

VALUES (<first column value>, <second column value>, …);

3. Commit the changes that were saved on the database buffer temporarily:

SQL> COMMIT;

4.Rollback enables the user to return the database to its original state by undoing the effects of all of the commands since the last COMMIT occurred with the command:

SQL> ROLLBACK;

5.ROLLBACK can be used with SAVEPOINT that mark the beginning of a transaction. Any other commands issued between the savepoint and the rollback will not be saved in the database.

SQL> SAVEPOINT <savepoint name>

…

…

SQL> ROLLBACK TO <savepoint name>

1. Rules on entering the data into tables with referential integrity (see figure on the next page):
   

1. Updating Existing Records in a Table

UPDATE <tablename>

SET <column> = <new data value>

WHERE <search condition>;

e.g.,

UPDATE student

SET s_class = ‘SR’

WHERE sid = 102;

DELETE FROM <tablename>

WHERE <search condition>;

e.g.,

DELETE FROM student;

WHERE sid = 102;

How many records will be deleted from the next command?

DELETE FROM <tablename>;

B.Using the SELECT Command to Retrieve Database Data (p. 99)

1. SELECT COMMANDS

-- Retrieving Data from a Single Table

SELECT <column 1, column 2, …>

FROM <ownername.tablename>

WHERE <search condition>;

SELECT <column 1, column 2, …>

FROM <ownername.table1name, ownername.table2name, …>

WHERE <search condition>;

SELECT <column 1, column 2, …>

FROM <ownername.table1name, ownername.table2name, …>

WHERE <search condition>

AND …

OR …;

SELECT <column 1, column 2, …>

FROM <ownername.table1name, ownername.table2name, …>

WHERE NOT <search condition>;

SELECT *

FROM <tablename>

SELECT DISTINCT <fieldname>

FROM <tablename>;

-- Sorting the Query Output

-- Ascending is the default

SELECT <column 1, column 2, …>

FROM <ownername.table1name, ownername.table2name, …>

WHERE <search condition>

ORDER BY <fieldname> <DESC>;

SELECT <column 1, column 2, …>

FROM <ownername.table1name, ownername.table2name, …>

WHERE <search condition>

ORDER BY <fieldname1> <fieldname2>;

SELECT SYSDATE

FROM DUAL;

Note that DUAL is a “utility” table provided by Oracle. It has exactly one column, called DUMMY, and one row. DUAL is used to do things such as:

• Arithmetic

• Obtain system information such as the date

• Return literal values.

You can do this with other tables, but the results would be returned once for each row in the table.

1. Using Calculations in a Query

1. Number Functions

ABS(n)e.g., SELECT ABS(-1) FROM DUAL; Result: 1

POWER(n, power)e.g., SELECT POWER(3,2) FROM DUAL;Result: 9

ROUND(n, precision)e.g., SELECT ROUND(123.476, 1) FROM DUAL; Result: 123.5

TRUNC(n, precision)e.g., SELECT TRUNC(123.476, 1) FROM DUAL;Result: 123.4

e.g.,

SELECT sid, slname, TRUNC((SYSDATE – sdob)/365.25) AS age

FROM student, DUAL

1. Using Date Arithmetic

Specify the shipments are in the period from 8/11/2001 through 8/17/2001.

SELECT shipid, date_expected, quantity_expected

FROM shipping

WHERE date_expected > TO_DATE(‘08/10/2001’, ‘ MM/DD/YYYY’)

AND date_expected <= TO_DATE(‘08/10/2001’, ‘ MM/DD/YYYY’) + 7;

ADD_MONTHS(<known date>, <count>)

e.g., ADD_MONTHS(TO_DATE(‘…’, ‘<mask>’), n); n (count) may be negative

MONTHS_BETWEEN(<date1>, <date2>)

1. Using Group Functions

AVG(fieldname)

COUNT(fieldname)

MAX(fieldname)

MIN(fieldname)

SUM(fieldname)

e.g.,

SELECT bldg_code, room, SUM(capacity),

AVG(capacity), MAX(capacity)

FROM location

WHERE capacity >=5

GROUP BY bldg_code, room;

1. Creating a Column Alias

SELECT <columnname> AS <alias_name>

e.g.,

SELECT csecid, naxenrl, currenrl, (maxenrl – currenrl) AS open

FROM course_section

C. Using Queries to (INNER) Join Multiple Tables

SELECT <column-name1, column-name2, …>

FROM <tablename1>, <tablename2>, …

WHERE <tablename1.column-name = talbename2.column-name>;

Note that the column-name in the WHERE condition should be the same (either pk or fk). If a column-name is on multiple tables, it should be followed by a tablename (e.g., faculty.loc_id)

SELECT <column-name1, column-name2, …>

FROM tablename1, tablename2, tablename3, tablename4, …

WHERE tablename1.column-name = talbename2.column-name

AND tablename3.column-name = talbename4.column-name

…

AND

… ;

Using Set Operators to Combine Query Results

UNION: returns all rows from both queries, but ONLY displays duplicate rows once

UNION ALL : returns all (duplicate) rows from both queries, and displays ALL duplicate rows

INTERSECT: returns all rows that are returned by both queries

MINUS: returns all rows returned by the first query minus the matching rows returned by the second query

They all require that both queries have the same number of display columns in the SELECT statement, and that each column in the first qurey has the same data type as the corresponding column in the second query.

Database Views

A database view is a logical (virtual) table based on a query. It does not store data, but presents it in a format different from the one in which it is stored in the underlying tables.

CREATE VIEW <view_name> AS

<view query specification.;

e.g.,

CREATE VIEW faculty_view AS

SELECT f_id, f_last, f_first, f_mi, loc_id, f_phone, f_rank

FROM faculty;

SELECT bldg_code, room

FROM location, faculty_view

WHERE f_last = 'Williams’

AND f_first = 'Jerry'

AND location.loc_id = facluty_view.loc_id;

Other commonly used database view commands:

SELECT view_name FROM user_views;

SELECT view_name FROM ALL_VIEWS

WHERE owner=‘SYSTEM’;

DROP VIEW <view_name>.;

Security

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.

GRANT <privilege1, privilege2, …>

ON <table_name>

TO <user1, user2, …>;

where privilege may be

ALTER, DELETE, INSERT, REFERENCES, SELECT, UPDATE, and ALL

user may be a user’s name or PUBLIC

REVOKE <privilege1, privilege2, …>

ON <table_name>

FROM <user1, user2, …>;

Sequences

Oracle sequences are sequential lists of numbers that are generated automatically by the database. Sequences are useful for creating unique surrogate keys values for primary key fields when no field exists to use as the primary key.

CREATE SEQUENCE <sequence_name>

START WITH <n>

MAXVALUE <n>

NOMAXVALUE

NOCACHE

NOORDER;

INSERT INTO <table name>

VALUES <seq_name>.NEXTVAL,

<field1 data value>, <field2 data value> ...

GRANT SELECT ON <seq_name>

TO <user 1, user 2, …>;

SELECT <seq_name>.NEXTVALUE

FROM DUAL;

SELECT * FROM user_sequences;

DROP SEQUENCE <seq_name>;

Using Oracle Query Builder to Retrieve Data

Query Builder is a graphical environment that makes creating SQL SELECT statement queries easier. Rather than having to write the query manually, query Builder allows you to select the tables you want to query, and it displays the tables and their associated columns on the screen.

Join conditions are taken care by the Query Builder

• fk links are shown
• query displayed columns may be selected
• search condition
• group function - sum, group, order

Query Builder also generates SQL commands (code generator). See text book for more details.

Optimizing Query Processing

Indexes may be used to improve the efficiency of data searches to meet particular search criteria after the table has been in use for some time. Therefore, the ability to create indexes quickly and efficiently at any time is important. SQL indexes can be created on the basis of any selected attributes. For example,

CREATE INDEX qoh_index

ON inventory (qoh);

Note that this statement defines an index called qoh_index for the qoh column in the inventory table. This index ensures that in the next example SQL only needs to look at row in the database that satisfy the WHERE condition, and is, therefore, quicker to produce an answer.

SELECT *

FROM inventory

WHERE qoh >=100;

You may even create an index that prevents you from using a value that has been used before. Such a feature is especially useful when the index field (attribute) is a primary key whose values must not be duplicated:

CREATE UNIQUE INDEX <index_field>

ON <tablename> (the key field);

Therefore,

• the indexes are defined so as to optimize the processing of SELECT statements. ;
• an index is never explicitly referenced in a SELECT statement; the syntax of SQL does not allow this;
• during the processing of a statement, SQL itself determines whether an existing index will be used;
• an index may be created or deleted at any time;
• when updating, inserting or deleting rows, SQL also maintains the indexes on the tables concerned. This means that, on the one hand, the processing time for SELECT statements is reduced, while, on the other hand, the processing time for update statements (such as INSERT, UPDATE and DELETE) can increase.

SELECT index_name from user_indexes;

Oracle’s Function Keys

Chen: Enhanced Guide to Oracle page-1