Transcript ppt - The Hebrew University of Jerusalem

The Oracle Database System
Building a Database
Database Course
The Hebrew University of Jerusalem
1
Technical Basics
2
Connecting to the Database
At the command line prompt, write:
sqlplus login/[email protected]
In the beginning your password is the same as
your login. You can change your password with
the command:
password
To disconnect use the command:
quit
3
Connecting to the Database
4
Running SQL Files

Instead of typing SQL commands into the
SQLPLUS terminal, you can load commands
from a file


Use the command @file from SQLPLUS to load
the file file.sql
Invoke the SQLPLUS command with the extra
parameter @file to load the file at connection:
sqlplus login/[email protected] @file
5
Spooling the Output

Output can be placed in a file:


spool myFile.out
Spooling can be turned off with:

spool off
6
Tables Creation
7
Creating a Table
The basic format of the CREATE TABLE
command is:
CREATE TABLE TableName(
Column1 DataType1 ColConstraint, …
ColumnN DataTypeN ColConstraint,
TableConstraint1, …
TableConstraintM
);
8
An Example
Note that the
definition is
case insensitive
CREATE TABLE Cars(
License
NUMBER,
Color
VARCHAR2(15));
If you issue the command describe Cars you get:
Name
Null? Type
-------- ----- -----------LICENSE
NUMBER
COLOR
VARCHAR2(15)
9
Data Types
CHAR(n)
String of length n (n <= 2000)
VARCHAR2(n) Variable length string of size <=
n
(n <= 4000)
DATE
Valid dates
CLOB
Character large object (<= 4Gb)
NUMBER
Up to 40 digits
NUMBER(n)
Number of size n
NUMBER(n,m) Number of size n with m digits
after decimal place
10
Constraints in Create Table


Adding constraints to a table enables the
database system to enforce data integrity.
However, adding constraints also makes
inserting data slower.
• Different types of constraints:
* Not Null
* Default Values
* Unique
* Primary Key
* Foreign Key
* Check Condition
11
Not Null Constraint
CREATE TABLE Employee(
SSN
NUMBER NOT NULL,
Fname
VARCHAR2(20),
Lname
VARCHAR2(20),
Gender
CHAR(1),
Salary
NUMBER(5) NOT NULL,
Dept
NUMBER
);
12
Default Values
CREATE TABLE Employee(
SSN
NUMBER NOT NULL,
Fname
VARCHAR2(20),
Lname
VARCHAR2(20),
Gender
CHAR(1) DEFAULT(‘F’),
Salary
NUMBER(5) NOT NULL,
Dept
NUMBER
);
13
Unique Constraint
CREATE TABLE Employee(
SSN
NUMBER UNIQUE NOT NULL,
Fname
VARCHAR2(20),
Lname
VARCHAR2(20),
Gender
CHAR(1) DEFAULT(‘F’),
The name of the
Salary
NUMBER(5) NOT NULL,
constraint
Dept
NUMBER,
constraint Emp_UQ UNIQUE(Fname, Lname)
);
14
Primary Key Constraint
CREATE TABLE Employee(
SSN
NUMBER PRIMARY KEY,
Fname
VARCHAR2(20),
Lname
VARCHAR2(20),
Primary Key implies
Gender
CHAR(1) DEFAULT(‘F’),
NOT NULL and
UNIQUE.
Salary
NUMBER(5) NOT
NULL, There can
only be one primary key.
Dept
NUMBER,
constraint Emp_UQ UNIQUE(Fname, Lname)
);
15
Another Table
CREATE TABLE Department(
DeptNum
NUMBER PRIMARY KEY,
Name
VARCHAR2(20),
ManagerId NUMBER
);
Shouldn’t all department numbers in
Employee appear in Department?
16
Foreign Key Constraint
(Referential Integrity)
CREATE TABLE Employee(
SSN
NUMBER PRIMARY KEY,
Fname
VARCHAR2(20),
Lname
VARCHAR2(20),
Gender
CHAR(1) DEFAULT(‘F’),
Salary
NUMBER(5) NOT NULL,
Must be
Dept
NUMBER,
unique
constraint Emp_UQ UNIQUE(Fname, Lname),
FOREIGN KEY (Dept) REFERENCES
Department(DeptNum)
17
);
Alternative Notation
CREATE TABLE Employee(
SSN
NUMBER PRIMARY KEY,
Fname
VARCHAR2(20),
Lname
VARCHAR2(20),
Gender
CHAR(1) DEFAULT(‘F’),
Salary
NUMBER(5) NOT NULL,
Dept
NUMBER REFERENCES
Department(DeptNum),
constraint Emp_UQ UNIQUE(Fname, Lname)
);
18
Understanding Foreign Keys

The constraint on the last table should be
read as: “The field Dept in Employee is a
foreign key that references the field DeptNum
in Department”

Meaning: Every non-null value in the field
Dept in Employee must appear in the field
DeptNum in Department.
What happens to Employees in
department 312 when Department 312 is
removed from the Department table?
19
Deleting a Referenced Value


If nothing additional is specified, then Oracle
will not allow Department 312 to be deleted if
there are Employees working in this
department.
If the constraint is written as
FOREIGN KEY (Dept) REFERENCES
Department(DeptNum) ON DELETE CASCADE
then Employees working in 312 will be deleted
automatically from the Employee table
20
Cyclic Foreign Keys
We should revise the Department table:
CREATE TABLE Department(
DeptNum
NUMBER PRIMARY KEY,
Name
VARCHAR2(20),
ManagerId NUMBER REFERENCES
Employee(SSN)
);
Do you see a problem in inserting data
now?
21
Solution to Cyclic Constraints
Add one of the constraints later on (after insertion):
ALTER TABLE Department
ADD(FOREIGN KEY (ManagerId)
REFERENCES Employee(SSN));
22
Check Conditions

A check condition is a Boolean expression:

“And”s and “Or”s of conditions of the type X > 5…

On a column: it can refer only to the column

On a table: it can refer only to multiple
columns in the table
23
Check Constraints
CREATE TABLE Employee(
SSN
NUMBER PRIMARY KEY,
Fname
VARCHAR2(20),
Lname
VARCHAR2(20),
Gender
CHAR(1) DEFAULT(‘F’)
CHECK(Gender = ‘F’ or
Gender = ‘M’) ,
Salary
NUMBER(5) NOT NULL,
CHECK (Gender = ‘M’ or Salary > 10000)
);
24
Table Alteration
25
Altering Tables

Table definition can be altered after its
creation






Adding columns
Changing columns’ definition
Dropping columns
Adding constraints
And more…
Use the reserved word ALTER
26
Altering Tables (continues)

Adding a column:
ALTER TABLE Employee ADD (
Mname VARCHAR2(20),
Birthday DATE
);

Cannot be
NOT NULL
unless the
table is empty
Changing columns’ definition:
ALTER TABLE Emplyee Modify (
Mname VARCHAR2(10)
);
27
Altering Tables (continues)

Dropping columns:
ALTER TABLE Employee DROP COLUMN Mname;
Dropping multiple columns:
ALTER TABLE Employee DROP
(Mname, Birthday);

Adding constraints:
ALTER TABLE Department ADD(
FOREIGN KEY (ManagerId)
REFERENCES Employee(SSN));
28
Deleting a Table

To delete the table Employee :
DROP TABLE Employee;
29
User’s Table List


ORACLE may print tables that hold some
general information about the tables in your
database
Such Tables are:


Tab, Cat, User_Tables (too detailed...)
To see the list of all your tables you can print:



SELECT * FROM Cat;
SELECT tname FROM Tab;
SELECT table_name from User_Tables;
30
Table Data Maintenance
31
The Employee Table
> Describe Employee
Name
Null?
-------- -------SSN
FNAME
LNAME
GENDER
SALARY
NOT NULL
Type
-----------NUMBER
VARCHAR2(20)
VARCHAR2(20)
CHAR(1)
NUMBER(5)
32
Inserting a Row

To insert a row into the Employee table:
INSERT INTO
Employee(SSN, Fname, Lname, Salary)
VALUES(121, ‘Sara’, ‘Cohen’,10000);


The remaining columns get default values (or
NULL)
Order is not important
33
Some More Details…

The fields needn’t be specified if values
are specified for all columns and in the
order defined by the table

Example:
INSERT INTO Employee
VALUES(121, ‘Sara’, ‘Cohen’, `F’, 10000);
34
Deleting Rows

General format:
DELETE FROM Table WHERE Cond;
Deletes all rows satisfying Cond from Table

For example, to remove the employee with
SSN 121 from the Employee table:
DELETE FROM Employee WHERE
SSN = 121;
35
Deleting Rows (continues)

To remove all male employees having a
salary greater than 15000 shekels:
DELETE FROM Employee WHERE Case sensitive
Gender = ‘M’ AND Salary > 15000;

We will later discuss WHERE clauses…
36
Updating Rows (continues)



We can update fields of rows in a table
General format:
UPDATE Table SET
Field1=value1,,,FieldN=valueN
WHERE Cond
Now we can reduce salaries instead of firing
employees:
UPDATE Employee SET Salary = 15000
WHERE Gender = ‘M’ AND
Salary > 15000;
37
The ORACLE Bulk Loader



A tool that provides easy insertion of large
amounts of rows into tables.
The idea: the field values of the rows are kept
in a file, the format of which is defined by us.
For example, it can automatically load 3
employees from the file myEmployees.dat
that contains the following lines:
Sara|Cohen|121
Benny|Kimelfeld|134
Yaron|Kanza|156
38
The Control File


The control file is the direct input of the loader
A simple control file:
LOAD DATA
INFILE <dataFile>
[APPEND] INTO TABLE <tableName>
FIELDS TERMINATED BY '<separator>‘
(<list of all attribute names to load>)
39
The Control File (continues)

<dataFile>:
The name of the data file

<tableName>:
The name of the table into which the data will be loaded
(appended if APPEND is specified, or else the table must be
empty)

<separator>:
A string that separates two field values of a row

The attributes are separated by commas and
enclosed in parentheses
40
The Control File (continues)

As an example, the following control file
loads the employees from myEmployees.dat:
LOAD DATA
INFILE myEmployees.dat
INTO TABLE Employees
FIELDS TERMINATED BY '|'
(Fname, Lname, SSN)

The attributes that are unspecified will be set to
NULL
41
The Data File

The Bulk Loader considers every single line
to represent one row in the table


Spaces are not ignored in the data file!


Even an empty line! (which will usually result in
an error)
thus the rows ‘sara| cohen|121’ and
‘sara|cohen|121’ define different functionalities
The NULL value is implied by the NULL
keyword or the empty string
42
The Data File (continues)

The control and the data files can be combined into
one .ctl file using the following format:
LOAD DATA
INFILE *
INTO TABLE Employees
FIELDS TERMINATED BY '|'
(Fname, Lname, SSN)
BEGINDATA
Sara|Cohen|121
Benny|Kimelfeld|134
Yaron|Kanza|156
43
The Bulk Invocation

To invoke the bulk loader, issue the following
command directly from the Unix shell:
sqlldr <yourName> control=<ctlFile>
log=<logFile> bad=<badFile>
 All fields are optional
 File names that have no extension are
automatically extended (by .dat, .log or .bad)

Erroneous lines in the data file are ignored
and written into badFile, and any other
relevant information is written into logFile.
44
Bulk Loader Important Remarks



Before using the Bulk Loader, make sure
your personal ORACLE environment is
properly set up
The tables you fill using the Bulk Loader
should be created prior to the loader
invocation
Before invoking the Bulk Loader you have to
make sure that
NO SQLPLUS SESSIONS ARE OPENNED!
45
Table Printing and
Formatting
46
Format Example

Consider the following table print:
47
Format Example (continues)


Absolutely not readable!
Solution: we write some commands in a format file
and we get:
48
Printing a Table

To print a table use the sql command:

SELECT * FROM table_name;
49
Add a Title

We can add a title to a table printing page using
the command:

TTITLE ‘title’
50
Change a Column’s Heading

We can change a column’s heading using the
command:

COLUMN ‘col_name’ HEADING ‘heading’
51
Define a Column Size

We can define the size of a column:

COLUMN ‘col_name’ format ‘format’

Format Examples:



a18: an ASCII text of size 18
90.99: a number with 4 digits, a decimal point,
minus sign (for negative numbers) and 0
before the dot for numbers smaller than 1
We can ask ORACLE to cut words to fit the
defined space using the command:
 COLUMN ‘col_name’ truncated
52
Define a Column Size
53
The Whole Picture
54