Transcript SQL Overview , Oracle Data Type , DDL and Constraints

Lecture4
Lecture5: SQL Overview , Oracle Data
Type , DDL and Constraints
Ref. Chapter6
1
Prepared by L. Nouf Almujally
Real World
Domain
Conceptual
model
(ERD)
Relational
Data Model
Create
schema
(DDL)
Load Data
(DML)
Lecture4
The Process of Database Design
2
• Official pronunciation is ‘S-Q-L‘ or ( see-qual)
• SQL: Structured Query Language
• The standard for relational database management systems
(RDBMS) such as Oracle Database.
• All SQL statements are instructions to the database.
• Easy to learn:
• Consists of standard English words, case insensitive
• It is a non-procedural language:
• you specify what information you require, rather than how
to get it. In other words, SQL does not require you to specify
the access methods to the data
Lecture4
SQL overview
3
• SQL provides statements for a variety of tasks, including:
1. Querying data( Select command)
2. Inserting, updating, and deleting rows in a table (DML)
3. Creating, replacing, altering, and dropping objects(DDL)
4. Controlling access to the database and its objects(DCL)
5. Guaranteeing database consistency and integrity(DCL)
Lecture4
SQL Overview
4
Built-In Oracle Data type Summary
VARCHAR2(size
[BYTE | CHAR])
Description
Variable length character string having
maximum length size bytes.
You must specify size
Max Size: Oracle 9i/10g
4000 bytes
minimum is 1
Fixed length character data of length size
bytes. This should be used for fixed length
data. Such as codes A100, B102...
2000 bytes
Default and minimum size is 1
byte.
Number having precision p and scale s. The
precision p
can range from 1 to 38. The scale s can range
from -84 to
127.
The precision p can range from 1
to 38.
DATE
Valid date range from January 1, 4712 BC to
December
31, 9999 AD.
from January 1, 4712 BC to
December 31, 9999 AD.
TIMESTAMP[timePr
ecsion]
Year, month, and day values of date, as well as
hour,
minute, and second values of time,
Accepted values of
fractional_seconds_precision are
0 to 9. (default = 6)
CHAR(size)
NUMBER(p,s)
Lecture4
Built-in Data type
The scale s can range from -84 to
127.
5
• Syntax for specifying numbers
• Number (precision, scale)
• Precision is the maximum digits of numbers
• Scale specifies the position of decimal point.
• E.g.
• Number(5)
5 digit integer, ex) 12345
• Number(6,2) 6 digit (not including decimal point)
decimal number with 2 digits after the decimal
point , ex) 1234.56
• Can store 1—38 digits precision
Lecture4
NUMBER(p,s)
6
To store strings, you can choose from:
• Char
• stores fixed-length character strings of up to 2000
characters. Eg. char(10)
• should use it to store short strings
• Varchar2
• Stores variable-length strings of up to 4000 characters
long. Eg. Varchar2(50)
• Preferred for most string types
 String values are quoted with single quotes
• Eg ‘12234’, ‘abcd’, ‘a12’
Lecture4
String
7
Date and time
SS
MI
HH
HH24
DD
DAY
D
MM
MON
Month
YY
YYYY
second
Minute
Hour
Military hour
day of month
day of the week
day of the week
month number
month abbreviated
Month spelled out
last 2 digits of year
full year value
0-59
0-59
1-12
1-24
1-31 (depends on month)
Sunday-Saturday
1-7
1-12
Jan—Dec
January-December
eg, 98
eg, 1998
Lecture4
• Oracle uses the date data type to store both date and
time
• Always uses 7 bytes for date-time data.
• Oracle date has rich formats, you need to specify it
8
Date and time
01-dec-2001
01/12/2001
12-01-01 12:30:59
Lecture4
• Example of date format:
• ‘dd-mon-yyyy’
• ‘dd/mm/yyyy’
• ‘mm-dd-yy hh:mi:ss’
• Default format: ‘dd-mon-yyyy’
• Current date and time:
• Sysdate
9
10
Lecture4
Lecture4
Data Definition language ( DDL )
Table Creation
11
• Creating a table
create table table-name (
column-name1 datatype ,
……..
column-nameN datatype );
• Table Name CONDITIONS :
• can not exceed 30 characters long
• Must begin with an alphabetic character
• May contain letters, numbers, $, # and _
• Should not be an oracle reserved word
• Should be descriptive
Lecture4
Data Definition (Creating a table)
12
create table table-name (
column-name1
datatype,
……..
column-nameN
datatype ,
[constraint constraint-name] Primary key (columns-name) );
Lecture4
Identifying Primary Key
OR
create table table-name (
column-name1 datatype [constraint constraint-name] primary key,
……..
column-nameN
datatype);
13
Example
OR
create table Department (
dept_no
char(4),
Dept_name varchar2(25),
CONSTRAINT dept_PK PRIMARY KEY(dept_no));
Lecture4
create table Department (
Dept_no
char(4) PRIMARY KEY,
Dept_name varchar2(25));
14
• An integrity constraint defines a business rule for a table
column.
• When enabled, the rule will be enforced by oracle
• constraints can be specified as row constraints and table
constraints. Tables constraints can, for example, identify
several columns such as Primary key.
• If the results of an INSERT or UPDATE statement violate an
integrity constraint, the statement will be rolled back.
• Note : If you don’t give the constraint name, the system will
generate a name automatically, but the name is hard for
human understanding.
Lecture4
Integrity Constraints
15
Integrity Constraints
1.
2.
3.
4.
CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER VIEW
Lecture4
• Constraint clauses can appear in the following statements:
16
1.
2.
3.
4.
5.
NOT NULL constraint
unique constraint
primary key constraint
foreign key constraint
check constraint
Lecture4
The FIFTH types of integrity constraint
17
• The NOT NULL constraint enforces a column to NOT accept
NULL values.
• The NOT NULL constraint enforces a field to always contain a
value. This means that you cannot insert a new record, or
update a record without adding a value to this field.
Lecture4
NOT NULL constraint
Syntax:
create table table-name (
column-name1 datatype NOT NULL );
18
NOT NULL constraint
CREATE TABLE Persons
( P_Id char(5) NOT NULL,
LastName varchar2(255) NOT NULL,
FirstName varchar2(255),
Address varchar2(255),
City varchar2(255) );
Lecture4
• Example : The following SQL enforces the "P_Id" column and the
"LastName" column to not accept NULL values:
19
• If a column in a table is optional, we can insert a new
record or update an existing record without adding a value
to this column. This means that the field will be saved with
a NULL value.
• NULL values are treated differently from other values.
• NULL is used as a placeholder for unknown or inapplicable
values.
• Note: It is not possible to compare NULL and 0; they are
not equivalent.
• It is not possible to test for NULL values with comparison
operators, such as =, <, or <>.
• We will have to use the IS NULL and IS NOT NULL
operators instead.
Lecture4
SQL NULL
20
• The UNIQUE constraint prohibits multiple rows from having the
same value in the same column or combination of columns but
allows some values to be null.
• The UNIQUE constraint provides a guarantee for uniqueness for a
column or set of columns.
• Unique columns are not automatically NOT NULL
Syntax:
create table table-name (
column-name1
datatype
UNIQUE );
OR
create table table-name (
…….
…….
[constraint constraint-name] UNIQUE (Columns_list) ;
Lecture4
UNIQUE constraint
21
Example
CREATE TABLE Persons
(
P_Id char(5) NOT NULL UNIQUE,
LastName varchar2(255) NOT NULL,
FirstName varchar2(255),
Address varchar2(255),
City varchar2(255),
Unique (LastName, FirstName)
)
Lecture4
• The following SQL creates a UNIQUE constraint on the
"P_Id" column when the "Persons" table is created:
22
• A primary key constraint combines a NOT NULL constraint
and a UNIQUE constraint in a single declaration. That is, it
prohibits multiple rows from having the same value in the
same column or combination of columns and prohibits
values from being null.
• A PRIMARY KEY constraint automatically has a UNIQUE
constraint defined on it.
• Note that you can have many UNIQUE constraints per table,
but only one PRIMARY KEY constraint per table.
Lecture4
Primary key Constraints
23
Primary key Constraints
Primary Key);
OR
Lecture4
Syntax:
create table table-name (
column-name1
datatype
create table table-name (
…….
…….
[constraint constraint-name] Primary Key (Columns_list) ;
24
Example :
create table Department (
dept_no char(4),
Dept_name varchar2(25),
CONSTRAINT dept_PK PRIMARY KEY(dept_no));
Lecture4
Primary key Constraints
25
Foreign Keys constraint
Syntax:
create table table-name (
column-name1
datatype,
……
……
[constraint constraint-name] Foreign key (Column_name)
references referenced_table (column_in_referenced_table) );
Lecture4
foreign key constraint requires values in one table to match
values in another table.
26
Create table Staff(
staff_no char(3),
staff_name varchar2(20),
dept_no char(4),
Constraint staff_fk foreign key (dept_no) references
department (dept_no));
Lecture4
Foreign Keys constraint
27
• When you delete or update a value of the columns
referenced by other tables, the referential integrity
constraints may be violated.
• ON DELETE/UPDATE Clause
• The ON DELETE / ON UPDATE clause lets you
determine how Oracle Database automatically
maintains referential integrity if you remove or update
a referenced primary key value. If you omit this clause,
then Oracle does not allow you to delete referenced
key values in the parent table that have dependent
rows in the child table.
Lecture4
referential integrity constraints
28
referential integrity constraints
parent table and all matching FK rows in the child table.
• ON DELETE/UPDATE SET NULL: delete or update the CK row from the
parent table and set the FK values to NULL in the child table. This is valid
only if the foreign key columns do not have the NOT NULL qualifier
specified.
Lecture4
Four options are supported when the user attempt to delete the CK
and there matching FKs:
• ON DELETE/UPDATE CASCADE: delete or update the CK row from the
• ON DELETE/UPDATE SET Default: delete or update the CK row from
the parent table and set the FK values to the specified default value in the
child table. Valid only if DEFAULT constraint is specified
• No action: Reject the delete operation from the parent table. This is the
default setting if the ON DELETE rule is omitted.
29
PK
• CREATE TABLE EmpMaster
(EmpId CHAR(1) PRIMARY KEY,
EmpName VARCHAR(25));
EmpId
EmpName
1
Ali
2
Slaut
3
John
EmpId
DeptNo
FK
DeptName
AAA
AAA
CCC
Lecture4
1
101
• CREATE TABLE EmpDetails
2
101
(DeptId CHAR(3) PRIMARY KEY,
3
103
DeptName VARCHAR(20)
EmpId CHAR(1) FOREIGN KEY REFERENCES EmpMaster(EmpId)
ON DELETE CASCADE );
• delete from EmpMaster where EmpId=1
EmpMaster
EmpDetails
EmpId
EmpName
EmpId
DeptNo
DeptName
2
Slaut
2
101
AAA
3
John
3
103
CCC
parent
child
30
• CREATE TABLE EmpMaster
(EmpId CHAR(1) PRIMARY KEY,
EmpName VARCHAR(25));
PK
EmpId
EmpName
1
Ali
2
Slaut
3
John
EmpId
DeptNo
FK
DeptName
• delete from EmpMaster where EmpId=1
EmpName
2
Slaut
3
parent
John
AAA
CCC
EmpDetails
EmpMaster
EmpId
AAA
Lecture4
1
101
• CREATE TABLE EmpDetails
2
101
(DeptId CHAR(3) PRIMARY KEY,
3
103
DeptName VARCHAR(20)
EmpId CHAR(1) FOREIGN KEY REFERENCES EmpMaster(EmpId)
ON DELETE SET NULL );
EmpId
DeptNo
DeptName
NULL
101
AAA
2
101
AAA
3
103
CCC
child
31
Check Constraints
• If you define a CHECK constraint on a single column it
allows only certain values for this column.
Lecture4
• The CHECK constraint is used to limit the value range that
can be placed in a column.
• If you define a CHECK constraint on a table it can limit
the values in certain columns based on values in other
columns in the row.
32
Check Constraints
Check (Check_condition)
OR
create table table-name (
…….
…….
[constraint constraint-name] Check (Check_condition)
);
Lecture4
Syntax:
create table table-name (
column-name1
datatype
);
33
Create table staff(
Staff_no char(3),
Staff_name varchar2(20) not null,
Staff_gender char(1) check (staff_gender in (‘M’, ‘F’)),
Staff_salary number(8,2) not null,
Dept_no char(4),
Constraint staff_pk Primary key (staff_no),
Constraint staff_fk Foreign key (dept_no) references department
(dept_no),
Constraint staff_sal check (staff_salary >10000.00));
To allow naming of a
CHECK constraint
Lecture4
Check Constraints Example
34
• The DEFAULT constraint is used to insert a default value
into a column.
• The default value will be added to all new records, if no
other value is specified.
Syntax:
create table table-name (
column-name1
datatype
);
Lecture4
DEFAULT constraint
Default (Default_value)
35
DEFAULT constraint
Lecture4
• Example : The following SQL creates a DEFAULT
constraint on the "City" column when the "Persons"
table is created:
CREATE TABLE Persons
(
P_Id char(5) NOT NULL,
LastName varchar2(255) NOT NULL,
FirstName varchar2(255),
Address varchar2(255),
City varchar2(255) DEFAULT 'Sandnes'
)
36
Create table staff(
Staff_no char(3),
Staff_name varchar2(20) not null,
DateofBirth date,
Staff_nationality char(10) default ‘Saudi’,
Staff_salary number(8,2) not null,
Dept_no char(4),
Constraint staff_pk Primary key (staff_no),
Constraint staff_fk Foreign key (dept_no) references
department (dept_no) on delete set null,
Constraint staff_sal check (staff_salary >10000.00),
UNIQUE(staff_name, DateofBirth));
Lecture4
Example : All Constraints
37
38
Lecture4
Alter table table_name
add (column_specification | constraint,...,
column_specification| constraint);
Alter table table_name
modify (column_specification | constraint,...,
Column_specification | constraint);
Alter table table_name
drop column column_name | drop (column_list);
Alter table table_name
drop primary key;
Alter table table_name
drop constraint constraint_name;
Lecture4
Modifying Table Definitions
39
Examples
alter table orders
modify (quantity number(5));
Lecture4
alter table orders
add (quantity number (3) not null);
alter table orders
drop (quantity);
• Be careful if the table contains data.
40
Dropping Tables
• Drop table table_name [Cascade Constraints];
• If Cascade Constraints is used, the constraints will be
dropped first.
• example
• Drop table Staff;
• Drop table Department;
• Drop table Department cascade constraints;
Lecture4
• Pay attention to referential integrity constraints when
dropping tables.
41
Viewing/enabling/disabling/dropping
Constraints
• To disable/enable a constraint, use
ALTER TABLE Table_name DISABLE CONSTRAINT constraint_name;
ALTER TABLE Table_name ENABLE CONSTRAINT constraint_name;
• To drop a constraint, use
ALTER TABLE Table_name
DROP PRIMARY KEY| UNIQUE (column_name) | CONSTRAINT
constraint_name;
Lecture4
• To view the constraints defined for table Department, type
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name ='DEPARTMENT';
42
Example
ALTER TABLE Persons
DROP CONSTRAINT chk_Person;
Lecture4
• To DROP a CHECK Constraint
• To DROP a FOREIGN KEY Constraint
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders;
43
Viewing Tables and Table Structures
• To see what tables are there in SQL*plus, type
• To see the structure of a table, type
Lecture4
select * from cat;
describe table_name;
or
desc table _name;
44
45
Lecture4
References
Lecture4
• “Database Systems: A Practical Approach to Design,
Implementation and Management.” Thomas Connolly,
Carolyn Begg. 5th Edition, Addison-Wesley, 2009.
46