The SQL Language

Data Definition Language

Elaborated by : Ines BAKLOUTI
Translated and Reformed by : Nesrine BOUAZIZI

— 2023-2024 —
 Plan
01 02 03
Introduction Tables Views
- Creation of a table - Creation and modification, of a view
- Deletion (or dropping) of a table - Deletion (or dropping) of a table
- Modification (or altering) of a table
- Renaming of a table

04 05 06
Sequences Indexes Synonyms
- Creation of sequence - Creation of index -Creation and deletion of synonym
- Modification of sequence - Deletion of index
- Use of sequence
- Deletion of sequence
 01
Introduction
▪ SQL = Structured Query Language
• A data definition language (DDL)

• A data manipulation language (DML)

• A data query language (DQL)

• A data control language (DCL)

▪ DDL = Data Definition Language

• Creation of a data structure (Create)

• Modification of the structure of a database object (Alter)

• Deletion of a data structure (Drop)

 02
TABLES
 Creation of a table
Syntax
CREATE TABLE [schema.]<table name>
(<column name> type [DEFAULT expr],
<column name> type [DEFAULT expr],
. . . . . .) ;

Example
CREATE TABLE Students
(StudentID number,
last_name varchar2(10),
first_name varchar2(10)
);
 Table Description
Syntax

- To view the table description :

DESCRIBE (or DESC ) Students;

Result
 Data Types
Data Type Description
CHAR [(size [BYTE | CHAR])] Fixed size ranging from 1 to 2000
NCHAR [(size)] Fixed size ranging from 1 to 2000
VARCHAR2 (size) Variable size ranging from 1 to 4000
NVARCHAR2 (size) Variable size ranging from 1 to 4000
NUMBER[(precision [, scale]]) A number with precision p and scale s. Precision ranges
from 1 to 38. Scale varies from -84 to 127
BINARY_FLOAT 32-bit floating-point number. This type requires 5 bytes.

BINARY_DOUBLE 64-bit floating-point number. This type requires 9 bytes

LONG Character data with a size <= 2GB

DATE Date ranging from 1/1/4712 BC to 12/31/9999 AD
TIMESTAMP Year, month, day, hour, minute, and second, with a fraction
of a second
 Creation of a table from a subquery

Syntax

CREATE TABLE [schema.]<table name>

(<column name> type [DEFAULT expr],
<column name> type [DEFAULT expr],
......
) AS subquery;
 Creation of a table from a subquery
Example

▪ CREATE TABLE emp AS SELECT * FROM employees WHERE

department_id = 20;
▪ SELECT * FROM emp;

Result
 Constraints
▪ There are five types of constraints:
- NOT NULL
- UNIQUE
- CHECK
- PRIMARY KEY
- FOREIGN KEY

▪ There are two levels of constraint definition:

1. Column-level constraint: Integrity constraint included in the column definition.
2. Table-level constraint: Integrity constraint included in the table definition.
Syntax:
CREATE TABLE [schema.]table (column_name type [DEFAULT expr] [column-level constraint], ... [column-
level constraint][,...]);

▪ Constraints can be defined:

- During the creation of the table.
- After the creation of the table.
 Constraints : the NOT NULL constraint
▪ The NOT NULL constraint can only be defined at the column level, not at the table level.

Example

▪ CREATE TABLE Suppliers (supplier_id number(10) NOT NULL,

name varchar2(20) NOT NULL, contact varchar2(50));

▪ CREATE TABLE Suppliers (supplier_id number(10) CONSTRAINT

nn_suppliers NOT NULL, name varchar2(20) NOT NULL, contact
varchar2(50));
 Constraints : the UNIQUE constraint
▪ A unique integrity constraint requires that each value in a column or in a set of columns be unique. A unique
constraint allows NULL unless you define NOT NULL constraints.

Example : At the column level

▪ CREATE TABLE Suppliers (supplier_id number(10) UNIQUE, name

varchar2(20) NOT NULL, contact varchar2(50));

▪ CREATE TABLE Suppliers (supplier_id number(10) CONSTRAINT

uq_suppliers UNIQUE, name varchar2(20) NOT NULL, contact
varchar2(50));
 Constraints : the UNIQUE constraint

Example : At the table level

Example : At the column level

▪ CREATE TABLE Suppliers (supplier_id number(10), name

varchar2(20) NOT NULL, contact varchar2(50), CONSTRAINT
uq_suppliers UNIQUE(supplier_id));
 Constraints : the CHECK constraint
▪ The CHECK constraint defines a condition that each row of the table must satisfy.
▪ The following expressions are not allowed:
1. Function calls such as SYSDATE.
2. Queries that reference other values in different rows.

Example : At the column level

▪ CREATE TABLE Suppliers

(supplier_id number(10) CHECK (supplier_id between 10 and 1000),
name varchar2(20) not null,
contact varchar2(50));
 Constraints : the CHECK constraint
Example : At the column level with CONSTRAINT keyword
▪ CREATE TABLE Suppliers
(supplier_id number(10) CONSTRAINT ck_suppliers CHECK (supplier_id
between 10 and 1000),
name varchar2(20) not null,
contact varchar2(50));

Example : At the table level

▪ CREATE TABLE Suppliers
(supplier_id number(10),
name varchar2(20) not null,
contact varchar2(50),
CONSTRAINT ck_suppliers CHECK (supplier_id between 10 and 1000));
Constraints : the PRIMAY KEY constraint
▪ The PRIMARY KEY constraint creates a primary key for the table.
▪ Only one primary key can be created per table.
▪ The primary key can consist of one or more columns, and none of the columns included in
the primary key can be NULL.

Example : At the column level

▪ CREATE TABLE Suppliers (
supplier_id number(10) PRIMARY KEY,
name varchar2(20) not null,
contact varchar2(50));
Constraints : the PRIMAY KEY constraint
Example : At the column level with CONSTRAINT keyword
▪ CREATE TABLE Suppliers (
supplier_id number(10) CONSTRAINT pk_suppliers PRIMARY KEY,
name varchar2(20) not null,
contact varchar2(50));

Example : At the table level

▪ CREATE TABLE Suppliers (
supplier_id number(10),
name varchar2(20) not null,
contact varchar2(50),
CONSTRAINT pk_suppliers PRIMARY KEY(supplier_id));
At the column level:

Constraints : the FOREIGN KEY constraint

Syntax : At the column level

CREATE TABLE <table_name>

(
<col1> <type> CONSTRAINT <fk_constraint_name>
REFERENCES <parent_table_name> (<col>)
ON DELETE {CASCADE | SET NULL | SET DEFAULT | RESTRICT}
ON UPDATE {CASCADE | SET NULL | SET DEFAULT | RESTRICT},
<col2> <type>,
...,
);
Constraints : the FOREIGN KEY constraint
Syntax : At the table level
CREATE TABLE <table_name>
(
<col1> <type>,
<col2> <type>,
...,
CONSTRAINT <fk_constraint_name> FOREIGN KEY (<col1>, <col2>, ..., <coln>)
REFERENCES <parent_table_name> (<col1>, <col2>, ..., <coln>)
ON DELETE {CASCADE | SET NULL | SET DEFAULT | RESTRICT}
ON UPDATE {CASCADE | SET NULL | SET DEFAULT | RESTRICT}
);
Constraints : the FOREIGN KEY constraint
▪ FOREIGN KEY: Defines the column in the child table at the table constraint level.
▪ REFERENCES: Identifies the table and column in the parent table.

Please Note:

▪ Create parent tables before child tables.

▪ Delete child tables before parent tables.
the FOREIGN KEY constraint : keyword ON DELETE

Possible options with the ON DELETE keyword:

▪ CASCADE: Deletes dependent rows in the child table when a row in the parent table
is deleted.
▪ SET NULL: Converts values of dependent foreign keys to NULL. This constraint
can only be executed if all foreign key columns in the target table accept NULL
values.
▪ SET DEFAULT: Sets the default value (following this parameter) in the foreign
table row if a corresponding key value is deleted (this option is not supported in
Oracle).
▪ RESTRICT: Raises an error if a corresponding key value is deleted (this option is
not supported in Oracle) .
the FOREIGN KEY constraint : keyword ON UPDATE

Possible options with the ON UPDATE keyword (this option is not supported
in Oracle):

▪ CASCADE: Updates dependent rows in the child table when a row in the parent
table is updated.
▪ SET NULL: Converts values of dependent foreign keys to NULL. This constraint
can only be executed if all foreign key columns in the target table accept NULL
values.
▪ SET DEFAULT: Sets the default value (following this parameter) in the foreign
table row if a corresponding key value is updated.
▪ RESTRICT: Raises an error if a corresponding key value is updated.
Constraints : the FOREIGN KEY constraint
Example 1 :
CREATE TABLE Suppliers (
supplier_id number(10) not null,
name varchar2(50) not null,
contact varchar2(50),
CONSTRAINT pk_supplier PRIMARY KEY (supplier_id, name)
);
CREATE TABLE Products (
product_id number(10) not null,
supplier_id number(10) not null,
name varchar2(50) not null,
CONSTRAINT fk_products FOREIGN KEY (supplier_id, name) REFERENCES
Suppliers (supplier_id, name)
);
Constraints : the FOREIGN KEY constraint
Example 2 :
CREATE TABLE Suppliers (
supplier_id number(10) not null PRIMARY KEY,
name varchar2(50) not null,
contact varchar2(50)
);

CREATE TABLE Products (

product_id number(10) not null,
supplier_id number(10) not null,
CONSTRAINT fk_products FOREIGN KEY (supplier_id) REFERENCES
Suppliers (supplier_id) ON DELETE CASCADE
);
 Modification (or altering) of a table

The ALTER TABLE statement allows for the modification of the table structure.

▪ For columns:
• Add a column (ADD)
• Modify an existing column (MODIFY) – specific to Oracle
• Drop a column (DROP)

▪ For constraints:
• Add a constraint (ADD CONSTRAINT)
• Drop a constraint (DROP CONSTRAINT)
Modification (or altering) of a table : Add a Column
Syntax :
▪ ALTER TABLE <table_name> ADD (
<column1> <type1> [DEFAULT <expr1>],
<column2> <type2> [DEFAULT <expr2>],
...);

Example :
▪ ALTER TABLE Suppliers ADD (
address varchar2(50),
telephone number(8) NOT NULL
);
Modification (or altering) of a table : Modify a Column
Modifying a column can involve:
• The data type,
• The size,
• The default value of a column (affects only new insertions into the table).

Syntax :
▪ ALTER TABLE <table_name> MODIFY (
<column1> <type1> [DEFAULT <expr1>],
<column2> <type2> [DEFAULT <expr2>],
...);

Example :
▪ ALTER TABLE Suppliers MODIFY (
address varchar2(100),
telephone number(13));
Modification (or altering) of a table : Drop a Column

Syntax :
▪ ALTER TABLE <table_name> DROP (column1, column2, ...);

Example :

▪ ALTER TABLE Suppliers DROP (address, telephone);

Modification (or altering) of a table : Add a Constraint
Syntax :
▪ ALTER TABLE <table_name> ADD [CONSTRAINT <constraint_name>]
<constraint_type> (<column_name>);

Examples :
▪ ALTER TABLE Suppliers ADD CONSTRAINT uq_suppliers UNIQUE(supplier_id);
▪ ALTER TABLE Suppliers ADD CONSTRAINT ck_suppliers CHECK (supplier_id
between 10 and 1000);
▪ ALTER TABLE Suppliers ADD CONSTRAINT pk_suppliers PRIMARY
KEY(supplier_id);
▪ ALTER TABLE Products ADD CONSTRAINT fk_products FOREIGN KEY
(supplier_id, name) REFERENCES Suppliers(supplier_id, name);

Please Note :
▪ To add a NULL/NOT NULL constraint, the MODIFY option is used
Example :
ALTER TABLE Suppliers MODIFY contact CONSTRAINT nn_suppliers_contact
NOT NULL;
Modification (or altering) of a table : Drop a Constraint

Syntax :
▪ ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;

Examples :

▪ ALTER TABLE Suppliers DROP CONSTRAINT ck_suppliers;

▪ ALTER TABLE Suppliers DROP CONSTRAINT pk_suppliers;
Modification (or altering) of a table : Enable/Disable a Constraint
Syntax :
▪ ALTER TABLE <table_name> ENABLE | DISABLE CONSTRAINT
<constraint_name>;

Example:
CREATE TABLE T1(a1 number PRIMARY KEY, b1 varchar2(10));
CREATE TABLE T2(a2 varchar2(10) PRIMARY KEY, b2 number CONSTRAINT fk_T2
REFERENCES T1(a1));
ALTER TABLE T1 ADD CONSTRAINT fk_T1 FOREIGN KEY (b1) REFERENCES T2(a2);
-- To insert rows into these two tables, it is necessary to disable one of the FOREIGN KEY
constraints:
ALTER TABLE T1 DISABLE CONSTRAINT fk_T1;
-- Insert rows into T1 and T2:
INSERT INTO T1 VALUES(1, 'a');
INSERT INTO T1 VALUES(2, 'b');
INSERT INTO T2 VALUES('b', 1);
-- Enable the fk_T1 constraint:
ALTER TABLE T1 ENABLE CONSTRAINT fk_T1;
-- The verification of inserted values occurs during the activation of the fk_T1 constraint.
 Deletion of a table

Syntax :
▪ DROP TABLE <table_name>;

Please Note :
▪ There is also the TRUNCATE command, which allows you to empty the table.
Syntax:
TRUNCATE TABLE <table_name>;
 Deletion of a table

Syntax :
▪ DROP TABLE <table_name>;

Please Note :
▪ There is also the TRUNCATE command, which allows you to empty the table.
Syntax:
TRUNCATE TABLE <table_name>;
 Renaming of a table

Syntax :
▪ RENAME <old_name> TO <new_name>;

Example :
▪ RENAME Suppliers TO TheSuppliers;
03
VIEWS
 Views
▪ A view is a logical table over one or more other tables or views. Only the definition of the view
(query) is stored in the database, not the data of the view. It restricts access to the database,
facilitates the creation of complex queries, and presents the same data in different forms.

▪ There are two types of views:

1. Simple View:
• Uses a single table.
• Does not contain any functions or data groups.
• Allows the execution of DML (UPDATE, DELETE, INSERT) statements.

2. Complex View:
• Uses multiple tables.
• Contains functions or data groups.
• Does not allow DML instructions (UPDATE, DELETE, INSERT).
 Creation and modification of a view
Syntax :
▪ CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW <view name> [(alias [,
alias], . . . )]
AS SELECT <query> [WITH CHECK OPTION [CONSTRAINT <constraint
name>]]
[WITH READ ONLY [CONSTRAINT <constraint name>]] ;

▪ FORCE: creates the view that tables exist or not, alias: indicates the names of the
expressions selected by the view query. The number of aliases must be equal to the number
of selected expressions.
▪ WITH CHECK OPTION: allows the insertion and updating of rows only for the rows that
the view can access (checking the conditions of the WHERE clause).
▪ CONSTRAINT: gives a constraint name to the restrictions WITH CHECK OPTION and
WITH READ ONLY (more understandable error messages).
▪ WITH READ ONLY: no DML operation can be executed.
 Creation and modification of a view
Examples :
---Create a table named "Suppliers"
CREATE TABLE Suppliers ( supplier_id NUMBER(10) PRIMARY KEY, name VARCHAR2(20)
NOT NULL, contact VARCHAR2(50), region_code NUMBER(3) );

---Create or replace a view named "view1_Suppliers_10" for suppliers with region code 10
CREATE OR REPLACE VIEW view1_Suppliers_10 AS SELECT supplier_id AS numero, name AS
nom, region_code AS region FROM Suppliers WHERE region_code = 10;

---Create or replace a view named "view2_Suppliers_10" for suppliers with region code 10 with
CHECK OPTION and CONSTRAINT "ck10"
CREATE OR REPLACE VIEW view2_Suppliers_10 AS SELECT supplier_id AS numero, name AS
nom, region_code AS region FROM Suppliers WHERE region_code = 10 WITH CHECK OPTION
CONSTRAINT ck10;
---Try to insert into "view2_Suppliers_10 " with a value violating the WHERE clause
INSERT INTO view2_Suppliers_10 VALUES (100, 'Daniel', 100); -- ⇒ORA-01402: view WITH
CHECK OPTION - violation of WHERE clause
-- Try to insert into "view2_Suppliers_10" with a value satisfying the WHERE clause
INSERT INTO view2_Suppliers_10 VALUES (100, 'Daniel', 10); -- ⇒1 row inserted.
 Creation and modification of a view

Example :
--- Create or replace a read-only view named "view3_Suppliers_10" for suppliers with
region code 10
CREATE OR REPLACE VIEW view3_Suppliers_10 AS SELECT supplier_id AS
numero, name AS nom, region_code AS region FROM Suppliers WHERE
region_code = 10 WITH READ ONLY;
 Deletion of a View

Syntax :
▪ DROP VIEW <view name>;

Example :

▪ DROP VIEW view3_Suppliers_10;

 04
SEQUENCES
 Sequences

▪ A sequence:
• Automatically generates unique numbers.
• Is shareable among multiple users and potentially among multiple tables.
• Allows for the creation of a primary key value.
 Creation of a Sequence
Syntax :
▪ CREATE SEQUENCE <sequence name>
[INCREMENT BY <step>]
[START WITH <value>]
[{MAXVALUE <max value> | NOMAXVALUE}]
[{MINVALUE <min value> | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE <cache> | NOCACHE}] ;

INCREMENT BY: Defines the interval between numbers (increment step).

START WITH: The first number of the sequence.
MAXVALUE | NOMAXVALUE: Maximum value the sequence can reach (or no maximum value).
MINVALUE | NOMINVALUE: Minimum value the sequence can reach (or no minimum value).
CYCLE | NOCYCLE: Specifies whether the sequence can continue to generate values after reaching
the maximum (or minimum) value (the default is NOCYCLE).
CACHE | NOCACHE: Number of pre-allocated values kept in memory (the default is cache=20).
 Creation of a Sequence
Examples

▪ CREATE SEQUENCE sequence1 INCREMENT BY 1 START WITH 1

MAXVALUE 5;

▪ CREATE SEQUENCE sequence2 INCREMENT BY 5 START WITH 10

MAXVALUE 100 NOCACHE NOCYCLE;

▪ CREATE SEQUENCE sequence3 INCREMENT BY 1 START WITH 1

MAXVALUE 20 CACHE 10 CYCLE;

▪ CREATE SEQUENCE sequence4 INCREMENT BY 5 START WITH -10

MINVALUE -20 MAXVALUE 5 CACHE 2 CYCLE;

Please Note :
▪ The cache value must be less than or equal to the number of values in a cycle.
 Modification of Sequence

Syntax :
▪ ALTER SEQUENCE <sequence name>
[INCREMENT BY <step>]
[START WITH <value>]
[{MAXVALUE <max value> | NOMAXVALUE}]
[{MINVALUE <min value> | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE <cache> | NOCACHE}] ;

Example :
▪ ALTER SEQUENCE sequence1 MAXVALUE 10 CYCLE;
 Use of a Sequence
▪ The usage of a sequence is done through "pseudo-columns" CURRVAL and
NEXTVAL. They are referred to as pseudo-columns because they are manipulated
somewhat like a table column, although they are not actual table columns.
▪ CURRVAL: Returns the current value of the sequence.
▪ NEXTVAL: Increments the sequence and returns the new value.

Example :
• CREATE TABLE Tseq(a number, b varchar2(5));

▪ CREATE SEQUENCE seq INCREMENT BY 5 START WITH -10 MINVALUE -

20 MAXVALUE 5 CACHE 2 CYCLE;

▪ INSERT INTO Tseq VALUES (seq.NEXTVAL, 'a');

▪ SELECT * FROM Tseq; ----------------------------------------------→

▪ SELECT seq.CURRVAL FROM dual; →

 Deletion of a Sequence

Syntax :
▪ DROP SEQUENCE <sequence name>;

Example :

▪ DROP SEQUENCE seq ;

 05
INDEXEX
 Indexes

▪ An index is a database object that speeds up the search for rows. It consists of two
fields:
- The index key
- The address of the data block containing the key

▪ An index can be created immediately after the creation of a table or on a table that
already contains rows.

▪ An index can cover multiple columns, and the access key will be the concatenation
of the different columns.

▪ Indexes are logically and physically independent of the tables they index.
 Creation of an Index
▪ The creation of indexes can be:
Automatic: A unique index is automatically created when defining a PRIMARY KEY
constraint or UNIQUE constraint.
Manual: A non-unique index can be created manually (index keys can be duplicated).

Syntax :
▪ CREATE [UNIQUE] INDEX <index name> ON <table name>(col1, col2,...);

Example :
▪ CREATE UNIQUE INDEX idx_Suppliers ON Suppliers (contact);
⇒ This example ensures that duplicate values are not allowed in the "contact"
column, even if there is no UNIQUE constraint on this column.

Please Note :
It is possible to rename an index.
Syntax: ALTER INDEX <old name> RENAME TO <new name>;
 Creation of an Index

▪ Create an index if:

- The column is frequently used in the WHERE clause or a join condition.
- The column contains a large number of NULL values.
- Two or more columns are often used together in a WHERE clause or a join condition.
- The table is large, and most queries need to retrieve less than 2 to 4% of the rows.

▪ Do not create an index if:

- The table is small.
- The table is frequently updated.
- The columns are not often used as conditions in queries.
- Most queries are expected to retrieve a very large percentage of rows.
 Deletion of an Index

Syntax :
▪ DROP INDEX <index name>;

Example :

▪ DROP INDEX idx_Suppliers ;

 05
SYNONYMS
 Synonyms

▪ A synonym is an alias for an object in the database or schema, a kind of shortcut.

▪ The object can be a table, a view, a sequence, a procedure, a function, a package, etc.
▪ The synonym can be public or private.
- Public: Accessible from all schemas and users.
- Private: Accessible only from the schema in which it has been created.

▪ Create synonyms to:

- Conceal the true name and location of objects.
- Simplify object names.
- Avoid prefixing queries with the owner's name.
 Creation and Deletion of a Synonym

Syntax : Creation
▪ CREATE [OR REPLACE] [PUBLIC] SYNONYM <synonym name> FOR
[schema.]<object name>;

Syntax : Deletion

▪ DROP SYNONYM <synonym name> ;