DB Oracle [Lecture – 2]

DDL – With Tables

By: Wahib Ladham

YEU@2024
Contents of this Lecture
Here’s what you’ll find in this Lecture:

1. SQL DDL and Data Types

2. SQL Constraints in DDL.
3. SQL Creating Tables
4. SQL Altering And Renaming Tables.
5. SQL Dropping Tables.
DDL and Data Types
Data Definition Language (DDL)
Specify a new Object:

 Provide object name.

 Specify object attributes and initial constraints.

Can optionally specify schema:

 Create Table [Link] ... ();

 Create Table Employee ... ();

Naming Rules
Table names and column names
 Must begin with a letter
 Must be 1–30 characters long
 Must contain only A–Z, a–z, 0–9, _, $, and #
 Must not duplicate the name of another object owned by the same user
 Must not be an Oracle server reserved word
Naming Guidelines
• Use descriptive names for tables and other database objects.
• Note: Names are case-insensitive. For example:
EMPLOYEES is treated as the same name as eMPloyees or eMpLOYEES.
Data Types
An SQL data type refers to the type
of data which can be stored in a
column of a database table. In a
column, the user can store numeric,
string, binary, etc by defining data
types.
For example integer data, character
data, monetary data, date and time
data, binary strings, and so on.
While creating a database table in a
database, we need to specify
following two attributes to define a
table column –
• Name of the column
• Data type of the column
Data Types
Numeric data types
 Integer numbers: INTEGER, INT, and SMALLINT
 Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION
Character-string data types
 Fixed length: CHAR(n), CHARACTER(n)
 Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)
Boolean data type
 Values of TRUE or FALSE or NULL
DATE data type
 Ten positions , YEAR, MONTH, and DAY in the form YYYY-MM-DD
Additional data types
 Timestamp data type (TIMESTAMP)
 DATE and TIME fields Plus a minimum of six positions for decimal fractions of seconds
Constraints in DDL
Constraints in DDL
Basic constraints:
 Key and referential integrity
constraints
 Constraints prevent the
deletion of a table if there are
dependencies
 Restrictions on attribute
domains and NULLs
 Constraints on individual
tuples within a relation
 The following constraint types
are examples:
Defining Constraints
• Syntax:

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);
• Column-level constraint:

column [CONSTRAINT constraint_name] constraint_type,

• Table-level constraint:
column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
SQL Creating Tables
SQL Create Table statements
The CREATE TABLE statement is used create a new table in the database. A table definition
consists of a list of columns, their types, and any integrity constraints.

Statements that are used to create a table are divided into three forms:

1. Create a New Table statement.

2. Create Table From Select statement.

3. Duplicate a table statement.

4. CREATE table statement with FOREIGN KEY.

Create statement syntax:
CREATE TABLE table_name (
column_1 data_type column_constraint,
column_2 data_type column_constraint,
column_3 data_type column_constraint,
column_4 data_type column_constraint,
column_5 data_type column_constraint,
column_6 data_type column_constraint,
column_7 data_type column_constraint,
column_8 data_type column_constraint,
column_9 data_type column_constraint,
column_10 data_type column_constraint,
column_11 data_type column_constraint
);
Defining Constraints
Column-level constraint:

CREATE TABLE emps(

employee_id NUMBER(6) CONSTRAINT 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) NOT NULL,
CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));
Create a New Table Ex: 1
CREATE TABLE customers(
customer_id number(10) NOT NULL,
customer_name varchar2(50) NOT NULL,
city varchar2(50)
);

CREATE TABLE students (

id number(10) NOT NULL,
name varchar2(40) NOT NULL,
lvl varchar2(10)
);
Create a New Table Ex: 2
CREATE TABLE Persons (
ID NUMBER PRIMARY KEY,
LastName varchar2(40) NOT NULL,
FirstName varchar2(40),
Age NUMBER(3),
Email Varchar2(25) UNIQUE
);

CREATE TABLE Persons (

ID NUMBER NOT NULL,
Name varchar2(40) NOT NULL,
Email varchar2(25),
Age NUMBER,
CONSTRAINT UC_Person UNIQUE (ID,Email)
);
Create a New Table Ex: 3
CREATE TABLE Persons (
ID NUMBER NOT NULL,
LastName varchar2(255) NOT NULL,
FirstName varchar2(255),
Age NUMBER CHECK (Age>=18)
);

CREATE TABLE Emp1 (

ID NUMBER NOT NULL,
Name VARCHAR2 (30) NOT NULL,
Age NUMBER,
GENDER VARCHAR2 (9) check (GENDER in ('Male', 'Female'))
);
Create a New Table Ex: 4
CREATE TABLE Persons (
ID NUMBER NOT NULL,
LastName varchar2(40) NOT NULL,
FirstName varchar2(40),
Age NUMBER,
City varchar(15) DEFAULT ‘Sanaa'
);

CREATE TABLE Orders (

ID NUMBER NOT NULL,
OrderNumber NUMBER NOT NULL,
OrderDate DATE DEFAULT sysdate
);
SQL Altering And
Renaming Tables.
Alter Table statement syntax:
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing
table.

ALTER TABLE table_name

ADD column_name datatype;
Alter Table Examples:
ALTER TABLE - ADD COLUMN :
ALTER TABLE Persons
ADD Email varchar2(25);

ALTER TABLE - DROP COLUMN:

ALTER TABLE Persons
DROP COLUMN Email;

ALTER TABLE - ALTER/MODIFY

COLUMN:
ALTER TABLE table_name
MODIFY column_name datatype;
Alter Table Examples:
ALTER TABLE – ADD CONSTRAINT TO
COLUMN (UNIQUE):
ALTER TABLE Persons ADD UNIQUE (ID);

ALTER TABLE - ADD CONSTRAINT TO 2 COLUMNS

(UNIQUE):
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

ALTER TABLE - DROP CONSTRAINT FROM

COLUMN (UNIQUE):
ALTER TABLE Persons
ALTER COLUMN City DROP UNIQUE;
Alter Table Rename Examples:
ALTER TABLE RENAME – COLUMN SYNTAX:
alter table table_name rename column
old_column_name TO new_column_name;

ALTER TABLE RENAME – COLUMN EXAMPLE:

alter table Orders rename column ID TO Ooder_ID;

ALTER TABLE RENAME – Table EXAMPLE:

RENAME ORDERS TO ORDERING;

SQL Dropping Tables
Drop Table statement syntax:
The DROP TABLE statement is used to drop an existing table in a database.

DROP TABLE table_name;

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

TRUNCATE TABLE table_name;

Drop Table Examples:
TRUNCATE TABLE:

TRUNCATE TABLE Persons;

DROP TABLE :

DROP TABLE Persons;

‫)‪HOME WORK (1‬‬
‫‪Create table as followed:‬‬
‫( ‪Table Books‬‬
‫‪Book id,‬‬
‫‪Book name,‬‬
‫‪Book auth,‬‬
‫‪Book release date,‬‬
‫‪Book category,‬‬
‫)‬

‫الشروط المطلوبة‪:‬‬
‫التعديالت المطلوبة على الجدول‪:‬‬
‫‪ .1‬رقم الكتاب عبارة عن شرط فريد وغير فارغ‪.‬‬
‫‪ .1‬غير اسم الجدول من ‪ Books‬الى ‪Book‬‬
‫‪ .2‬التحقق من التاريخ حال إذا كان أكبر من ‪.2009‬‬
‫‪ .2‬اختصر اسم تاريخ اإلصدار الى مسمى قصير‪.‬‬
‫‪ .3‬التحقق من تنصيف الكتاب بحيث يكون ضمن اآلتي‪:‬‬
‫‪ .3‬غير حجم البيانات الخاصة بالتصانيف من ‪20‬‬
‫(تاريخي – ديني – سياسي – علوم – فيزياء ‪-‬‬
‫الى ‪30‬‬
‫حاسوب)‬
‫‪ .4‬اضف مفتاح رئيسي الى رقم الكتاب‪.‬‬
Resources
SQL at:
Javatpoint
W3schools
dba-oracle
[Link]
stackoverflow