Database Foundations

6-2
Structured Query Language (SQL)

Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

Objectives
• This lesson covers the following objectives:
−Describe how data is organized in a relational database
−Explain the various relational database terminologies
−Define the structured query language and its functions
−Describe how SQL processing takes place
−Identify the tools used to access the
relational database

DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 3
Structured Query Language (SQL)
How Is Data Organized in Relational Databases?
• Data is stored in a two-dimensional matrix known as a
table

Table name: EMPLOYEES

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID
10 Administration 200
Oracle
20 Marketing 201
Server
Table name: DEPARTMENTS
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
100 Steven King SKING
101 Neena Kochhar NKOCHHAR
102 Lex De Haan LDEHAAN

DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 4
Structured Query Language (SQL)
How Is Data Organized in Relational Databases?
• DBMS software is used to manage reading and
manipulating data

Table name: EMPLOYEES

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID
10 Administration 200
Oracle
20 Marketing 201
Server
Table name: DEPARTMENTS
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
100 Steven King SKING
101 Neena Kochhar NKOCHHAR
102 Lex De Haan LDEHAAN

DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 5
Structured Query Language (SQL)
Relational Database Terminology
2 3 4
EMPLOYEE FIRST LAST SALARY COMMISSION DEPARTMENT
_ID _NAME _NAME _PCT _ID
100 Steven King 24000 - 90
101 Neena Kochhar 17000 - 90
102 Lex De Haan 17000 - 5 90
200 Jennifer Whalen 4400 - 10
205 Shelley Higgins 12000 6 - 110
206 William Gietz 8300 - 110
1149 Eleni Zlotkey 10500 .2 80
174 Ellen Abel 11000 .3 80
201 Michael Hartstein 13000 - 20

Note : Refer to notes for explanations to number notations

DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 6
Structured Query Language (SQL)
 Relating Multiple Tables
• Each row of data in a table can be uniquely identified
by a primary key
• You can logically relate data from multiple tables using
foreign keys
Table name: EMPLOYEES Table name: DEPARTMENTS
EMPLOYEE_ FIRST_ LAST_ DEPARTMENT_ DEPARTMENT_ DEPARTMENT_ MANAGER_
ID NAME NAME ID ID NAME ID
100 Steven King 90 60 IT 103
101 Neena Kochhar 90 90 Executive 100
103 David Austin 60

Foreign key Primary key

Primary key

DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 7
Structured Query Language (SQL)
What Is SQL?
• In a relational database, you do not specify the access
route to the tables, and you do not need to know how
the data is arranged physically
• To access the database, you execute a SQL statement,
which is the American National Standards Institute
(ANSI) standard language for operating relational
databases
• SQL is also compliant with ISO Standard (SQL 1999)

DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 8
Structured Query Language (SQL)
What Is SQL?
• Structured query language (SQL) is the set-based,
declarative language used to access data in an Oracle
database
• SQL provides an interface to a relational database and
provides statements that help work with the database
• SQL is:
−Efficient, easy to learn, and use
−Functionally complete
(With SQL, you can define,
retrieve, and manipulate data in
the tables)

DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 9
Structured Query Language (SQL)
Functions of SQL
• Creating, replacing, altering, and dropping database
objects
• Inserting, updating, and deleting rows in a table
• Querying data stored in the database
• Controlling access to the database and
database objects
• Guaranteeing database consistency
and integrity

DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 10
Structured Query Language (SQL)
Types of SQL Commands
• DDL (Data Definition Language) – defines database
structures
• DML (Data Manipulation Language) – manipulates data
(INSERT, UPDATE, DELETE)
• DQL (Data Query Language) – SELECTs data
• DCL (Data Control Language) – controls user access
• TCL (Transactional Control Language) – manages
database transactions

DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 11
Structured Query Language (SQL)
SQL Processing
• Stages of SQL processing

Row Source
Parsing Optimization Execution
Generation

* See definitions of stages in notes

DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 12
Structured Query Language (SQL)
Accessing Data in the Oracle Database Server
• Specialized tools installed on users' computers allow
them to access data on the Oracle Database server.
These tools are called clients and are used to send SQL
instructions (commands) to the server
• Three tools are:

SQL*Plus
* Note: In this class we will be using Oracle Application Express

DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 13
Structured Query Language (SQL)
Case Scenario: Need to Extract Data

Sean, by now you should understand

the need to use SQL to access data in a
relational database. Can you think of
Faculty scenarios where retrieving data from a
database table would be necessary?

Sure. Let me think of some use cases

Student

DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 14
Structured Query Language (SQL)
Use Cases
• Think of and discuss the use of databases to store data
and produce information in these and other areas

Hospitals Retail

Airports Schools
DFo 6-2 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 15
Structured Query Language (SQL)
Database Foundations
6-3
Data Definition Language (DDL)

Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

Objectives
• This lesson covers the following objectives:
−Identify the steps needed to create database tables
−Describe the purpose of the data definition language (DDL)
−List the DDL operations needed to build and maintain a
database's tables

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 17
Data Definition Language (DDL)
Database Objects

Object Description
Table Is the basic unit of storage; consists of rows
View Logically represents subsets of data from one
or more tables
Sequence Generates numeric values
Index Improves the performance of some queries
Synonym Gives an alternative name to an object

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 18
Data Definition Language (DDL)
Naming Rules for Tables and
Columns
• Table names and column names must:
−Begin with a letter
−Be 1–30 characters long
−Contain only A–Z, a–z, 0–9, _, $, and #
−Not duplicate the name of another object
owned by the same user
−Not be an Oracle server–reserved word

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 19
Data Definition Language (DDL)
CREATE TABLE Statement
• To issue a CREATE TABLE statement, you must have:
−The CREATE TABLE privilege
−A storage area

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr][, ...]);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 20
Data Definition Language (DDL)
CREATE TABLE Statement
• Specify in the statement:
−Table name
−Column name, column data type, column size
−Integrity constraints (optional)
−Default values (optional)
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 21
Data Definition Language (DDL)
Creating Tables
• Create the table:
CREATE TABLE dept(
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
create_date DATE DEFAULT SYSDATE
);

• To confirm that the table was created, run the

DESCRIBE command

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 22
Data Definition Language (DDL)
Creating Tables
• Confirm table creation:

DESCRIBE dept;

Table Column Data Type Length Precision Scale Primary Key Nullable Default Comment

DEPT DEPTNO NUMBER - 2 0 - - -

DNAME VARCHAR2 14 - - - - -

LOC VARCHAR2 13 - - - - -

CREATE_DATE DATE 7 - - - SYSDATE -

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 23
Data Definition Language (DDL)
Data Types
Data Type Description
VARCHAR2(size) Variable-length character data
(A maximum size must be specified; minimum size is 1.)
Maximum size: 32767 bytes
CHAR(size) Fixed-length character data of length (size) bytes. (Default and
minimum size is 1; maximum size is 2,000)
NUMBER(p, s) Variable-length numeric data. Precision is p, and scale is s.
(Precision is the total number of decimal digits, and scale is the
number of digits to the right of the decimal point; precision can
range from 1 to 38, and scale can range from -84 to 127.)
DATE Date and time values to the nearest second between January 1,
4712 B.C, and December 31, 9999 A.D.
LONG Variable-length character data (up to 2 GB)

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 24
Data Definition Language (DDL)
Data Types
Data Type Description
CLOB A character large object (CLOB) containing single-byte or multibyte characters. Maximum size
is (4 GB - 1) * (DB_BLOCK_SIZE); stores national character set data.
NCLOB A CLOB containing Unicode characters. Both fixed-width and variable-width character sets are
supported, both using the database national character set. Maximum size is
(4 GB - 1) * (database block size); stores national character set data.
RAW (Size) Raw binary data of length size bytes. You must specify size for a RAW value. Maximum size:
32767 bytes if MAX_SQL_STRING_SIZE = EXTENDED
4000 bytes if MAX_SQL_STRING_SIZE = LEGACY
LONG RAW Raw binary data of variable length up to 2 GB.
BLOB A binary large object. Maximum size is (4 GB - 1) * (DB_BLOCK_SIZE initialization parameter (8
TB to 128 TB)).
BFILE Binary data stored in an external file (up to 4 GB).
ROWID Base 64 string representing the unique address of a row in its table. This data type is primarily
for values returned by the ROWID pseudocolumn

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 25
Data Definition Language (DDL)
Example: Creating a Table with Different Data Types

CREATE TABLE print_media(

product_id NUMBER(6),
id NUMBER(6),
desc VARCHAR2(100),
composite BLOB,
msourcetext CLOB,
finaltext CLOB,
photo BLOB,
graphic BFILE
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 26
Data Definition Language (DDL)
Date Data Types

Data Type Description

TIMESTAMP Enables storage of time as a date with fractional seconds. It stores the
year, month, day, hour, minute, the second value of the DATE data
types, and the fractional seconds value. There are several variations of
this data type, such as WITH TIMEZONE and WITH LOCALTIMEZONE.
INTERVAL YEAR TO Enables storage of time as an interval of years and months. Used to
MONTH represent the difference between two datetime values in which the
only significant portions are the year and month.
INTERVAL DAY TO Enables storage of time as an interval of days, hours, minutes, and
SECOND seconds; used to represent the precise difference between two
datetime values.
TIMESTAMP WITH Variant of TIMESTAMP that includes a time zone region name or time
TIME ZONE zone offset in its value.

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 27
Data Definition Language (DDL)
Examples: Date Data Types
• Example of TIMESTAMP data type:
CREATE TABLE table_ts(
c_id NUMBER(6),
c_ts TIMESTAMP
);

INSERT INTO table_ts

VALUES(1, '01-JAN-2003 2:00:00');

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 28
Data Definition Language (DDL)
Examples: Date Data Types
• Example of a table with TIMESTAMP, INTERVAL YEAR
TO MONTH and INTERVAL DAY TO SECOND columns:
CREATE TABLE time_table(
start_time TIMESTAMP,
duration_1 INTERVAL DAY (6) TO SECOND (5),
duration_2 INTERVAL YEAR TO MONTH
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 29
Data Definition Language (DDL)
DEFAULT Option
• Specify a default value for a column during CREATE
TABLE
• This option prevents null values from entering the
columns when a row is inserted without a value for the
column
... hire_date DATE DEFAULT SYSDATE, ...

• Literal values, expressions, or SQL functions are legal

values

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 30
Data Definition Language (DDL)
DEFAULT Option
• Another column's name or a pseudocolumn are illegal
values
• The default data type must match the column data
type
CREATE TABLE hire_dates(
id NUMBER(8),
hire_date DATE DEFAULT SYSDATE
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 31
Data Definition Language (DDL)
Case Scenario: Creating Tables

How about creating

the tables for the
simplified library
database?

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 32
Data Definition Language (DDL)
Case Scenario: Creating Tables
CREATE TABLE AUTHORS
CREATE TABLE BOOK_TRANSACTIONS ( ID NUMBER(3),
( ID VARCHAR2(6), NAME VARCHAR2(60)
TRAN_DATE DATE DEFAULT SYSDATE, );
TYPE VARCHAR2(10),
BOOK_ID VARCHAR2(6),
MEMBER_ID NUMBER(4)
);

CREATE TABLE PUBLISHERS

( ID NUMBER(2),
NAME VARCHAR2(100)
);

CREATE TABLE MEMBERS

( ID NUMBER(4),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50),
STREET_ADDRESS VARCHAR2(50), CREATE TABLE BOOKS
CITY VARCHAR2(20), ( ID VARCHAR2(6),
STATE VARCHAR2(2), TITLE VARCHAR2(255),
ZIP VARCHAR2(10)); PUBLISHER_ID NUMBER(2),
AUTHOR_ID NUMBER(3)
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 33
Data Definition Language (DDL)
Case Scenario: Creating Tables
CREATE TABLE authors(
id NUMBER(3),
name VARCHAR2(60)
); Successful
creation of
CREATE TABLE members( tables
id NUMBER(4),
first_name VARCHAR2(50),
last_name VARCHAR2(50),
street_address VARCHAR2(50),
city VARCHAR2(20),
Creating
state VARCHAR2(2),
Tables zip VARCHAR2(10)
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 34
Data Definition Language (DDL)
Case Scenario: Creating Tables
CREATE TABLE publishers(
id NUMBER(2),
name VARCHAR2(100) NOT NULL
);
Successful
creation of
CREATE TABLE books( tables
id VARCHAR2(6),
title VARCHAR2(255)NOT NULL,
publisher_id NUMBER(2),
author_id NUMBER(3)
Creating );
Tables

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 35
Data Definition Language (DDL)
Including Constraints
• Constraints enforce rules at the table level
• Constraints ensure the consistency and integrity of the
database
• The following constraint types are valid:
−NOT NULL
−UNIQUE
−PRIMARY KEY
−FOREIGN KEY
−CHECK

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 36
Data Definition Language (DDL)
Data Integrity Constraints
Constraints Description
NOT NULL The column cannot contain a null value
UNIQUE The values for a column or a combination of columns
must be unique for all rows in the table
PRIMARY KEY The column (or a combination of columns) must
contain the unique AND IS NOT NULL value for all
rows
FOREIGN KEY The column (or a combination of columns) must
establish and enforce a reference to a column or a
combination of columns in another (or the same)
table
CHECK A condition must be true

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 37
Data Definition Language (DDL)
Constraint Guidelines
• Name a constraint (otherwise, Constraint Type
the Oracle server generates a SYS_C0014370 Primary Key
name in the SYS_Cn format)
• Constraints are easier to reference if given a
meaningful name. (Ex. employee_employee_id_pk)
• Create a constraint at either of the following times:
−At the same time as the creation of the table
−After the creation of the table
• Define a constraint at the column or table level
• View a constraint in the data dictionary

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 38
Data Definition Language (DDL)
Constraint Guidelines
• Column-level constraints are included when the
column is defined
• Table-level constraints are defined at the end of the
table definition, and must refer to the column or
columns on which the constraint pertains
• Functionally, a column-level constraint is the same as a
table-level constraint
• NOT NULL constraints can be defined only at the
column level
• Constraints that apply to more than one column must
be defined at the table level

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 39
Data Definition Language (DDL)
Defining Constraints
• CREATE TABLE with CONSTRAINTS syntax:

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 40
Data Definition Language (DDL)
Defining Constraints
• Column-level constraint syntax:
column [CONSTRAINT constraint_name] constraint_type,

• Table-level constraint syntax:

column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 41
Data Definition Language (DDL)
Examples: Defining Constraints
• Column-level constraint:
CREATE TABLE employees(
employee_id NUMBER(6)CONSTRAINT emp_emp_id_pk
PRIMARY KEY,
first_name VARCHAR2(20),
...
);

• Table-level constraint:
CREATE TABLE employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
...
job_id VARCHAR2(10),
CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id)
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 42
Data Definition Language (DDL)
NOT NULL Constraint
• Ensures that null values are not permitted for the
column:
EMPLOYEE_ FIRST_ SALARY COMMISSION DEPARTMENT EMAIL PHONE_ HIRE_
ID NAME _PCT _ID NUMBER DATE
100 Steven 24000 - 90 SKING 515.123.4567 17-Jun-1987
101 Neena 17000 - 90 NKOCHHAR 515.123.4568 21-Sep-1989
102 Lex 17000 - 90 LDEHAAN 515.123.4569 13-Jan-1993
200 Jennifer 4400 - 10 JWHALEN 515.123.4444 17-Sep-1987
205 Shelley 12000 - 110 SHIGGINS 515.123.8080 07-Jun-1994
206 William 8300 - 110 WGIETZ 515.123.8181 07-Jun-1994
141 Trenna 3500 - 50 TRAJS 650.121.8009 17-Oct-1995
Absence of NOT NULL constraint (Any row
NOT NULL constraint can contain a null value for this column.)
(Primary Key enforces
NOT NULL
NOT NULL constraint.)
constraint

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 43
Data Definition Language (DDL)
NOT NULL Constraint
• Can be defined ONLY at the column level:
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE CONSTRAINT hire_date_nn
NOT NULL,
...
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 44
Data Definition Language (DDL)
UNIQUE Constraint
• A UNIQUE key integrity constraint requires that every
value in a column or a set of columns be unique;
• If the UNIQUE constraint has more than one column,
that group of columns is called a composite unique key
• UNIQUE constraints enable the input of nulls
• A null in a column (or in all columns of a composite
UNIQUE key) always satisfies a UNIQUE constraint

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 45
Data Definition Language (DDL)
UNIQUE Constraint

UNIQUE constraint
EMPLOYEES
EMPLOYEE_ID LAST_NAME EMAIL
100 King SKING
101 Kochhar NKOCHHAR
102 De Haan LDEHAAN
200 Whalen JWHALEN
205 Higgins SHIGGINS
…
INSERT INTO

208 Smith JSMITH Allowed

209 Smith JSMITH Not allowed: already exists

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 46
Data Definition Language (DDL)
UNIQUE Constraint
• Defined at either the table level or the column level:
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25),
email VARCHAR2(25) CONSTRAINT
emp_email_uk UNIQUE,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE, OR
...
CONSTRAINT emp_email_uk UNIQUE(email)
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 47
Data Definition Language (DDL)
PRIMARY KEY Constraint

• A PRIMARY KEY constraint creates a

primary key for the table
• Only one primary key can be created
for each table
• The PRIMARY KEY constraint is a
column or a set of columns
that uniquely identifies each row
in a table
• No column that is part of the primary
key can contain a null value
DFo 6-3 • A composite primary key must be
Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 48
Data Definition Language (DDL)
PRIMARY KEY Constraint
DEPARTMENTS PRIMARY KEY

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

10 Administration 200 1700

20 Marketing 201 1800
50 Shipping 124 1500
60 IT 103 1400
… Not allowed
(null value) INSERT INTO
NULL Public Accounting 124 2500

50 Finance 124 1500

Not allowed (50 already exists)
• Note: See Slide 27 for primary key constraint coding examples

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 49
Data Definition Language (DDL)
FOREIGN KEY Constraint
• The FOREIGN KEY (or referential integrity) constraint
designates a column or a combination of columns as a
foreign key
• Establishes a relationship with a primary key in the
same table or a different table
• Here are the guidelines for foreign key constraints:
−A foreign key value must match an existing value in the
parent table or be NULL
−Foreign keys are based on data values and are purely logical,
rather than physical, pointers

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 50
Data Definition Language (DDL)
FOREIGN KEY Constraint
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

10 Administration 200 1700

20 Marketing 201 1800
50 Shipping 124 1500
… …
FOREIGN KEY
SALARY
EMPLOYEE_ID DEPARTMENT_ID
100 24000 90
101 17000 90
102
… 17000 90

Not allowed
200 Ford 9 (9 does not exist)
INSERT INTO
200 Ford 60 Allowed
DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 51
Data Definition Language (DDL)
FOREIGN KEY Constraint
• Defined at the table level :
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25),
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE,
...
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 52
Data Definition Language (DDL)
FOREIGN KEY Constraint
• Defined at the column level:
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25),
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE,
...
department_id NUMBER(4) CONSTRAINT emp_dept_fk
REFERENCES departments(department_id)
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 53
Data Definition Language (DDL)
FOREIGN KEY Constraint: Keywords
• FOREIGN KEY: Defines the column in the child table at
the table-constraint level
• REFERENCES: Identifies the table and column in the
parent table
• ON DELETE CASCADE: Deletes the dependent rows in
the child table when a row in the parent table is
deleted
• ON DELETE SET NULL: Converts dependent foreign key
values to null

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 54
Data Definition Language (DDL)
CHECK Constraint
• It defines a condition that each row must satisfy
• It cannot reference columns from other tables

CREATE TABLE employees(

...
salary NUMBER(8,2) CONSTRAINT emp_salary_min
CHECK (salary > 0),
...
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 55
Data Definition Language (DDL)
CREATE TABLE: CHECK Constraint Example

CREATE TABLE employees(

employee_id NUMBER(6),
last_name VARCHAR2(25),
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE,
...
CONSTRAINT hire_date_min CHECK
(hire_date > '01-JAN-2018’)
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 56
Data Definition Language (DDL)
Case Scenario: Creating Tables

How about adding the

constraints to the
simplified library
database tables?

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 57
Data Definition Language (DDL)
Case Scenario: Adding Constraints
CREATE TABLE authors(
id NUMBER(3),
name VARCHAR2(60),
CONSTRAINT atr_id_pk PRIMARY KEY (ID)
);

CREATE TABLE members(

id NUMBER(4),
first_name VARCHAR2(50),
last_name VARCHAR2(50),
street_address VARCHAR2(50),
city VARCHAR2(20),
state VARCHAR2(2),
zip VARACHAR2(10),
CONSTRAINT mbr_id_pk PRIMARY KEY (ID)
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 58
Data Definition Language (DDL)
Case Scenario: Adding Constraints
CREATE TABLE publishers(
id NUMBER(2),
name VARCHAR2(100) NOT NULL,
CONSTRAINT plr_id_pk PRIMARY KEY (ID)
) ;
CREATE TABLE books(
id VARCHAR2(6),
title VARCHAR2(255)NOT NULL,
publisher_id NUMBER(2),
author_id NUMBER(3),
CONSTRAINT bok_id_pk PRIMARY KEY (ID),
CONSTRAINT bok_atr_fk FOREIGN KEY (author_id)
REFERENCES authors(id),
CONSTRAINT bok_plr_fk FOREIGN KEY (publisher_id)
REFERENCES publishers(id)
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 59
Data Definition Language (DDL)
Case Scenario: Adding Constraints
CREATE TABLE book_transactions(
id VARCHAR2(6),
tran_date DATE DEFAULT SYSDATE NOT NULL,
type VARCHAR2(10) ,
book_id VARCHAR2(6) ,
member_id NUMBER(4),
CONSTRAINT btn_id_pk PRIMARY KEY (ID),
CONSTRAINT bok_btn_fk FOREIGN KEY (book_id)
REFERENCES books(id),
CONSTRAINT bok_mbr_fk FOREIGN KEY (member_id)
REFERENCES members(id)
);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 60
Data Definition Language (DDL)
Data Definition Language
• Creating tables is part of SQL’s Data Definition
Language
• Other DDL statements include :
−ALTER : to modify an object’s structure
−DROP : to remove an object from the database
−RENAME : to rename a database object

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 61
Data Definition Language (DDL)
ALTER TABLE Statement
• Use the ALTER TABLE statement to change the table
structure:
−Add a column
−Modify an existing column definition
−Define a default value for the new column
−Drop a column
−Rename a column
−Change a table to read-only status

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 62
Data Definition Language (DDL)
ALTER TABLE Statement
• Use the ALTER TABLE statement to add, modify, or drop
columns:
ALTER TABLE table
ADD (column data type [DEFAULT expr]
[, column data type]...);

ALTER TABLE table

MODIFY (column data type [DEFAULT expr]
[, column data type]...);

ALTER TABLE table

DROP (column [, column] …);

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 63
Data Definition Language (DDL)
Adding a Column
• You use the ADD clause to add columns:
ALTER TABLE employees
ADD termination_date DATE;

• The new column becomes the last column:

EMPLOYEE_ID LAST_NAME HIRE_DATE TERMINATION_DATE

100 King 17-Jun-1987 -

101 Kochhar 21-Sep-1989 -
102 De Haan 13-Jan-1993 -
200 Whalen 17-Sep-1987 -

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 64
Data Definition Language (DDL)
Modifying a Column
• You can change a column's data type, size, and default
value:
ALTER TABLE employees
MODIFY first_name VARCHAR2(30);

• A changed default value affects only subsequent

insertions in the table.
• The modifications are subject to certain conditions

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 65
Data Definition Language (DDL)
Dropping a Column
• Use the DROP COLUMN clause to drop columns that
you no longer need:
ALTER TABLE employees
DROP (termination_date);

EMPLOYEE_ID LAST_NAME HIRE_DATE

100 King 17-Jun-1987

101 Kochhar 21-Sep-1989
102 De Haan 13-Jan-1993
200 Whalen 17-Sep-1987
…

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 66
Data Definition Language (DDL)
SET UNUSED Option
• The SET UNUSED option marks one or more columns
as unused so that they can be dropped at a time when
the demand on system resources is lower
• You use the SET UNUSED option to mark one or more
columns as unused
• You use the DROP UNUSED COLUMNS option to
remove the columns that are marked as unused

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 67
Data Definition Language (DDL)
SET UNUSED Option
ALTER TABLE <table_name>
SET UNUSED(<column_name> [ , <column_name>]);
OR
ALTER TABLE <table_name>
SET UNUSED COLUMN <column_name> [ , <column_name>];

ALTER TABLE dept

SET UNUSED (dname);

ALTER TABLE <table_name>

DROP UNUSED COLUMNS;

ALTER TABLE dept

DROP UNUSED COLUMNS;

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 68
Data Definition Language (DDL)
Case Scenario: Altering Tables

Sean, I was reviewing the AUTHORS table

and realized that.
The author's email address field is missing.
Faculty
The author’s name column length needs to
be increased.
Can you make these changes?

Sure, I can do it. Because the modification is

adding a new column and is increasing the
column length, this should not be an issue.
Student

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 69
Data Definition Language (DDL)
Case Scenario: Altering Tables

Looks like the

table is modified:

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 70
Data Definition Language (DDL)
Read-Only Tables
• You can use the ALTER TABLE syntax to:
−Put a table in read-only mode, which prevents DDL or DML
changes during table maintenance
−Put the table back into read/write mode

ALTER TABLE dept READ ONLY;

-- perform table maintenance and then

-- return table back to read/write mode

ALTER TABLE dept READ WRITE;

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 71
Data Definition Language (DDL)
Dropping a Table
• Moves a table to the recycle bin
• Removes the table and its data if the PURGE clause is
specified
• Invalidates dependent objects and removes object
privileges on the table

DROP TABLE dept;

DFo 6-3 Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 72
Data Definition Language (DDL)