[go: up one dir, main page]
Scribd
Upload
35 views37 pages

Database Systems Lecture 8

This document discusses SQL commands for creating and managing database tables. It covers the CREATE TABLE statement for defining a table's structure, including naming rules, data types, and constraints. The ALTER TABLE statement is described for modifying existing tables by adding, removing, or changing columns and constraints. Other statements covered include RENAME to change a table's name, and DROP TABLE to remove a table and its data. Constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK are also summarized. Examples are provided for creating tables and modifying them using these SQL DDL commands.

Uploaded by

Humam Al-Dhubhani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
35 views37 pages

Database Systems Lecture 8

This document discusses SQL commands for creating and managing database tables. It covers the CREATE TABLE statement for defining a table's structure, including naming rules, data types, and constraints. The ALTER TABLE statement is described for modifying existing tables by adding, removing, or changing columns and constraints. Other statements covered include RENAME to change a table's name, and DROP TABLE to remove a table and its data. Constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK are also summarized. Examples are provided for creating tables and modifying them using these SQL DDL commands.

Uploaded by

Humam Al-Dhubhani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 37

Database Systems

T. Amani Al-Kebsi Lecture 8


Using DDL Statements
to Create and Manage Tables
SQL Commands:
SQL Commands:
SQL Commands:
CREATE TABLE Statement

Creating a basic table involves naming the


table and defining its columns and each
column's data type.
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
Data Types

Data Type Description


VARCHAR2(size) Variable-length character data
CHAR(size) Fixed-length character data
NUMBER(p,s) Variable-length numeric data
DATE Date and time values
LONG Variable-length character data (up to 2 GB)
CLOB Character data (up to 4 GB)
RAW and LONG Raw binary data
RAW
BLOB Binary data (up to 4 GB)
BFILE Binary data stored in an external file (up to 4 GB)
TIMESTAMP Date with fractional seconds
Constraints

– Constraints enforce rules at the table level.

– Constraints prevent the deletion of a table if there are dependencies.

– The following constraint types are valid:

› NOT NULL
› UNIQUE
› PRIMARY KEY
› FOREIGN KEY
› CHECK
Constraints

– 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.


NOT NULL Constraint

– The NOT NULL constraint ensures that the column contains


no null values.

– Columns without the NOT NULL constraint can contain null


values by default.

– NOT NULL constraints must be defined at the column


level.
UNIQUE Constraint

– A UNIQUE key integrity constraint requires that every value


in a column or a set of columns (key) be unique—that is,
no two rows of a table can have duplicate values in a
specified column or a set of columns.

– UNIQUE constraints enable the input of nulls unless you


also define NOT NULL constraints for the same columns.
UNIQUE Constraint

– UNIQUE constraints can be defined at the column level or


table level. You define the constraint at the table level
when you want to create a composite unique key.

– A composite key is defined when there is not a single


attribute that can uniquely identify a row. In that case, you
can have a unique key that is composed of two or more
columns, the combined value of which is always unique
and can identify rows.
PRIMARY KEY Constraint

– A PRIMARY KEY constraint creates a primary key for the


table.

– The PRIMARY KEY constraint is a column or a set of


columns that uniquely identifies each row in a table. This
constraint enforces the uniqueness of the column or
column combination and ensures that no column that is
part of the primary key can contain a null value.
FOREIGN KEY Constraint

– The FOREIGN KEY (or referential integrity) constraint


designates a column or a combination of columns as a
foreign key and establishes a relationship with a primary
key or a unique key in the same table or a different table.

– A foreign key value must match an existing value in the


parent table or be NULL.
FOREIGN KEY Constraint

– FOREIGN KEY constraints can be defined at the column or


table constraint level. A composite foreign key must be
created by using the table-level definition.
FOREIGN KEY Constraint
› FOREIGN KEY Constraint: Keywords

– The foreign key is defined in the child table and the table containing the
referenced column is the parent table. The foreign key is defined using a
combination of the following keywords:

✓ FOREIGN KEY is used to define the column in the child table at the
table-constraint level.
✓ REFERENCES identifies the table and the column in the parent table.
✓ ON DELETE CASCADE indicates that when a row in the parent table is
deleted, the dependent rows in the child table are also deleted.
✓ ON DELETE SET NULL indicates that when a row in the parent table is
deleted, the foreign key values are set to null.
FOREIGN KEY Constraint

› FOREIGN KEY Constraint: Keywords (Continue)

– The default behavior is called the restrict rule, which disallows the
update or deletion of referenced data.

– Without the ON DELETE CASCADE or the ON DELETE SET NULL


options, the row in the parent table cannot be deleted if it is
referenced in the child table.
CHECK Constraint

– The CHECK constraint defines a condition that each row


must satisfy.

– A single column can have multiple CHECK constraints that


refer to the column in its definition.

– There is no limit to the number of CHECK constraints that


you can define on a column.
CREATE TABLE Statement
CREATE TABLE Statement

› Example of creating a department table

create table dept(


Dept_no number(5),
Dept_Name varchar2(40),
constraint PK_dept primary key(dept_no))
CREATE TABLE Statement
› Example of creating an employee table

create table emp(


Emp_no number(10),
Emp_name varchar2(50) not null,
Emp_email varchar2(30),
Emp_pass varchar2(10) default 123,
Dept_no number(5),
constraint PK_emp primary key(Emp_no),
constraint Uni_email unique(Emp_email ),
constraint FK_dept foreign key(dept_no) references dept(dept_no) on
delete cascade)
Create Table Using another
Table (Using a Subquery)
A copy of an existing table can be created using a
combination of the CREATE TABLE statement and the
SELECT statement.

The new table has the same column definitions. All columns
or specific columns can be selected.

When you create a new table using existing table, new table
would be populated using existing values in the old table.
Create Table Using another
Table (Using a Subquery)

Syntax:

The basic syntax for creating a table from another table is :


Create Table Using another
Table (Using a Subquery)

• Example

The basic syntax for creating a table from another table is :


Create Table Using another
Table (Using a Subquery)

• Example

The basic syntax for creating a table from another table is :

Create table dept_copy


As
Select * from dept
ALTER TABLE Statement

› Use the ALTER TABLE statement to:

– Add a new column


– Modify an existing column definition
– Rename a column
– Add a constraint to an existing column
– Remove a constraint from a column
– Drop a column
ALTER TABLE Statement

› Alter table to add a new column

• Syntax
Allter table table_name add column_name datatype size

• Example
Alter table emp add phone_num number(10)
ALTER TABLE Statement

› Alter table to edit the datatype and


size of column

• Syntax
Allter table table_name modify column_name datatype size

• Example
Alter table emp modify phone_num number(15)
ALTER TABLE Statement

› Alter table to rename an existing


column

• Syntax
Allter table table_name rename column old_column_name to
new_column_name
• Example
Alter table emp rename column phone_num to emp_phone_num
ALTER TABLE Statement

› Alter table to add a constraint to an


existing column

• Syntax
Allter table table_name add constraint constraint_name
constraint_type(column_name)
• Example
Alter table emp add constraint uni_ph_num unique(emp_phone_num)
ALTER TABLE Statement

› Alter table to remove a constraint


from a column

• Syntax
Allter table table_name drop constraint constraint_name
• Example
Alter table emp drop constraint uni_ph_num
ALTER TABLE Statement

› Alter table to remove a column

• Syntax
Allter table table_name drop column column_name
• Example
Alter table emp drop column emp_phone_num
RENAME Statement

› Renaming a Table

• Syntax
rename old_table_name to new_table_name

• Example
rename emp to employee
DROP TABLE Statement

› Dropping a Table

– The DROP TABLE statement moves a table to the recycle bin or


removes the table and all its data from the database entirely.
DROP TABLE Statement

• Syntax
drop table table_name

• Example
drop table emp
References
SQL TUTORIAL , Simply Easy Learning by tutorialspoint.com.

Oracle Slides.

You might also like