The Oracle Database System

1
The Oracle Database System
2
Connecting to the Database

• At the command line prompt, write
• sqlplus login/password_at_stud.cs

• In the beginning your password is the same as
  your login. You can change your password with the
  command
• password

3
Creating a Table

• The basic format of the CREATE TABLE
• command is
• CREATE TABLE TableName(
• Column1 DataType1 ColConstraint,
• ColumnN DataTypeN ColConstraint,
• TableConstraint1,
• TableConstraintM
• )

4
An Example
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)

5
Data Types
6
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

7
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 )
8
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 )
9
Unique Constraint
CREATE TABLE Employee( SSN NUMBER UNIQUE NOT
NULL, Fname VARCHAR2(20), Lname VARCHAR2(20
), Gender CHAR(1) DEFAULT(F), Salary NUMBER(5)
NOT NULL, Dept NUMBER, constraint Emp_UQ
UNIQUE(Fname, Lname) )
10
Primary Key Constraint
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, constraint Emp_UQ
UNIQUE(Fname, Lname) )
Primary Key also implies NOT NULL. There can only
be one primary key.
11
Another Table
CREATE TABLE Department( DeptNum NUMBER PRIMARY
KEY, Name VARCHAR2(20), ManagerId NUMBER )
12
Foreign Key Constraint
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, constraint Emp_UQ
UNIQUE(Fname, Lname), FOREIGN KEY (Dept)
REFERENCES Department(DeptNum) )
13
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) )
14
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.

15
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
16
Remembering ER-Diagrams
name
address
Owns
Person
Car
model
ssn
since
license
color
CREATE TABLE Owns( SSN NUMBER REFERENCES
Person(SSN), License NUMBER REFERENCES
Car(License), Since DATE)
17
Cyclic Foreign Keys
We should revise the Department table
CREATE TABLE Department( DeptNum NUMBER PRIMARY
KEY, Name VARCHAR2(20), ManagerId NUMBER
REFERENCES Employee(SSN) )
18
Solution to Cyclic Constraints
Add one of the constraints later on (after
insertion) ALTER TABLE Department ADD(FOREIGN
KEY (ManagerId) REFERENCES Employee(SSN))
19
Check Conditions

• A check condition is a Boolean expression
• Ands and Ors of conditions of the type X gt 5
• On a column it can refer only to the column
• On a table it can refer only to multiple columns
  in the table

20
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 gt 10000) )
21
Deleting a Table

• To delete the table Employee
• DROP TABLE Employee

22
Inserting Data Into a Table
23
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)
• The fields neednt be specified if values are
  specified for all columns and in the order
  defined by the table

24
Some More Details

• An example of inserting into the Owns table
• INSERT INTO Owns
• VALUES(121, 4545, 01-DEC-02)

• We can also use the Oracle Loader Details in Ex2

25
Querying the Data
26
Example Tables Used
27
Basic SQL Query
SELECT Distinct target-list FROM
relation-list WHERE condition

• relation-list A list of relation names (possibly
  with a range-variable after each name)
• target-list A list of fields of relations in
  relation-list
• condition A Boolean condition
• DISTINCT Optional keyword to delete duplicates

28
Basic SQL Query
SELECT Distinct A1,,An FROM R1,,Rm WHERE C
This translates to the expression in relational
algebra ?A1,,An (?C(R1 xx Rm))
29
Sailors Who Reserved Boat 103
SELECT sname FROM Sailors, Reserves WHERE
Sailors.sid Reserves.sid and bid 103
?sname(?Sailors.sid Reserves.sid ? bid 103
(Sailors x Reserves))
30
Sailors x Reserves
31
Range Variables
SELECT S.sname FROM Sailors S, Reserves R
WHERE S.sid R.sid and R.bid 103

• Range variables are good style.
• They are necessary if the same relation appears
  twice in the FROM clause

32
Sailors Whove Reserved a Boat
SELECT sid FROM Sailors S, Reserves R WHERE
S.sid R.sid
33
Expressions and Strings
SELECT age, (age-5)2 as age1 FROM Sailors,
WHERE sname LIKE B_B

• Expressions in SELECT clause
• Renaming of column in result
• String comparison _ is a single character and
  is 0 or more characters

34
Sailors whove reserved a red or green boat
SELECT S.sid FROM Sailors S, Boats B, Reserves
R WHERE S.sid R.sid and R.bid B.bid
and (B.color red or B.color green)
?sid(?color red ? color green (Sailors
?? Reserves ?? Boats))
35
Sailors whove reserved red or green boat
SELECT S.sid FROM Sailors S, Boats B, Reserves
R WHERE S.sid R.sid and R.bid B.bid
and B.color red UNION SELECT S.sid FROM
Sailors S, Boats B, Reserves R WHERE S.sid
R.sid and R.bid B.bid and B.color
green
What would happen if we wrote MINUS? Or INTERSECT?
36
The Second Version in Relational Algebra
?sid(?color red (Sailors ?? Reserves ??
Boats)) ? ?sid(?color green (Sailors ??
Reserves ?? Boats))
37
Sailors whove reserved red and green boat
SELECT S.sid FROM Sailors S, Boats B1, Reserves
R1, Boats B2, Reserves R2 WHERE S.sid
R1.sid and R1.bid B1.bid and B1.color
red and S.sid R2.sid and R2.bid B2.bid
and B2.color green
38
Nested Queries
Names of sailors whove reserved boat 103
SELECT S.sid FROM Sailors S WHERE S.sid IN
(SELECT R.sid FROM Reserves R WHERE
R.bid 103)
What would happen if we wrote NOT IN?
The SELECT, FROM and WHERE clauses can have
sub-queries. They are computed using nested
loops.
39
Correlated Nested Queries
Names of sailors whove reserved boat 103
SELECT S.sid FROM Sailors S WHERE EXISTS
(SELECT FROM Reserves R WHERE R.bid
103 and S.sid R.sid)
What would happen if we wrote NOT EXISTS?
40
More Set-Comparison Queries
Sailors who are not the youngest
SELECT FROM Sailors S1 WHERE S1.age gt ANY
(SELECT S2.age FROM Sailors S2)
We can also use op ALL (op is gt, lt, , gt, lt, or
ltgt).
41
Some Small Details
42
Input and Output Files

• Commands can be put in a file and then read into
  Oracle
• start commands.sql

• Output can be placed in a file
• spool commands.out
• Spooling can be turned off with
• spool off