Using DDL Statements in SQL: A Comprehensive Guide | Lecture notes Database Management Systems (DBMS)

http ://www.tu torialspoint.com/sql_certificate/using_ddl_statements.h tm Copy right © tutorialspoint.com

USING DDL STATEMENTS

Using DDL Statements to Create and Manage Tables

A schema is the collection of multiple database objects,which are known as schema objects.These

objects have direct acce ss by their owner schema.Below table lists the schema objects.

Table - to store data

View - to project data in a desired form at from one or more tables

Sequence - to generate numeric values

Index - to improve performance of queries on the tables

Synonym - alternative name of an object

One of the first steps in creating a database is to create the tables that will store an organization's

data.Databa se design involves identifying system user requirements for various organizational

systems such as order entry, inventory management, and accounts receivable. Regardless of

database size and complexity, each database is comprised of tables.

Creating the table

To create a table in the database,a DBA must have certain information in hand - the table name,

column name, column data types, and column sizes. All this information can be modified later

using DDL commands.

Table Naming Conventions -

The name you choose for a table must follow these standard rule s:

The name must begin with a letter A-Z or a-z

Can contain numbers and underscores

Can be in UPPER of lower case

Can be up to 30 characters in length

Cannot use the same name of another existing object in your schema

Must not be a SQL reserved word

Following the above guidelines, 'EMP85' can be a valid table name.But 85EMP is not.Similarly,

UPDATE cannot be a chosen as a table name since it a SQL reserved keyword.

CREATE TABLE statement

The CREATE TABLE is a DDL statement which is used to create tables in the database.The table

gets created as soon as the CREATE TABLE script is executed and is ready to hold the data

onwards.The user must have the CREATE TABLE system privilege to create the table in its own

schema .But to create a table in any user's schema, user must have CREATE ANY TABLE schema.

Here is the syntax of a basic CREATE TA BLE statement.There may be many additional clauses to

explicitly provide the storage specifications or segment values.

CREATE TABLE [schema.]table

( { column datatype [DEFAULT expr] [column_constraint] ...

| table_constraint}

[, { column datatype [DEFAULT expr] [column_constraint] ...

| table_constraint} ]...)

[AS subquery]

Partial preview of the text

Download Using DDL Statements in SQL: A Comprehensive Guide and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!

USING DDL STATEMENTS USING DDL STATEMENTS

Using DDL Statements to Create and Manage Tables

A schema is the collection of multiple database objects,which are known as schema objects.These

objects have direct access by their owner schema.Below table lists the schema objects.

Table - to store data

View - to project data in a desired format from one or more tables

Sequence - to generate numeric values

Index - to improve performance of queries on the tables

Synonym - alternative name of an object

One of the first steps in creating a database is to create the tables that will store an organization's

data.Database design involves identifying system user requirements for various organizational

systems such as order entry, inventory management, and accounts receivable. Regardless of

database size and complexity, each database is comprised of tables.

Creating the table

To create a table in the database,a DBA must have certain information in hand - the table name,

column name, column data types, and column sizes. All this information can be modified later

using DDL commands.

Table Naming Conventions -

The name you choose for a table must follow these standard rules:

The name must begin with a letter A-Z or a-z

Can contain numbers and underscores

Can be in UPPER of lower case

Can be up to 30 characters in length

Cannot use the same name of another existing object in your schema

Must not be a SQL reserved word

Following the above guidelines, 'EMP85' can be a valid table name.But 85EMP is not.Similarly,

UPDATE cannot be a chosen as a table name since it a SQL reserved keyword.

CREATE TABLE statement

The CREATE TABLE is a DDL statement which is used to create tables in the database.The table

gets created as soon as the CREATE TABLE script is executed and is ready to hold the data

onwards.The user must have the CREATE TABLE system privilege to create the table in its own

schema.But to create a table in any user's schema, user must have CREATE ANY TABLE schema.

Here is the syntax of a basic CREATE TABLE statement.There may be many additional clauses to

explicitly provide the storage specifications or segment values.

CREATE TABLE [schema.]table ( { column datatype [DEFAULT expr] [column_constraint] ... | table_constraint} [, { column datatype [DEFAULT expr] [column_constraint] ... | table_constraint} ]...) [AS subquery]

In the above syntax, DEFAULT specifies default value which can be used during INSERT statement

if the column is ignored. It cannot contain references to other table columns or pseudo columns

CURRVAL , NEXTVAL , LEVEL , andROWNUM except SYSDATE and USER, or date constants that are not

fully specified.

Constraints are the rules defined optionally at the column level or table level coveredlaterinthischapter

.These rules are checked during any data action Insert , update on the table and raise error to abort

the action upon its violation.

For example, the CREATE TABLE statement below creates a table EMP_TEST. Note the column

specifications, data type and precision.

CREATE TABLE SCOTT.EMP_TEST

(EMPID NUMBER,

ENAME VARCHAR2( 100 ),

DEPARTMENT_ID NUMBER,

SALARY NUMBER,

JOB_ID VARCHAR2( 3 ),

HIREDATE DATE,

COMM NUMBER);

A user can refer the tables from other user's schema by prefixing the username or schema with

the table name.For example, a user GUEST wishes to query the employee name and salary from

the EMP_TEST table which is owned by SCOTT. He can issue the below query -

SELECT ENAME, SALARY,

FROM GUEST.EMP_TEST;

A column can hold a default value during the time of table creation.It helps to restrict the NULL

values getting into the column. Default value can be deduced from either a literal, expression or

SQL function which must return a compatible data type to the column. In the below CREATE TABLE

statement, note that the LOCATION_ID column has default value 100.

CREATE TABLE SCOTT.DEPARTMENT

(DEPARTMENT_ID NUMBER,

DNAME VARCHAR2 ( 100 ),

LOCATION_ID NUMBER DEFAULT 100 );

CTAS - Create table using subquery

A table can be created from an existing table in the database using a subquery option.It copies the

table structure as well as the data from the table. Data can also be copied based on conditions.The

column data type definitions including the explicitly imposed NOT NULL constraints are copied into

the new table.

The below CTAS script creates a new table EMP_BACKUP. Employee data of department 20 gets

copied into the new table.

CREATE TABLE EMP_BACKUP

AS

SELECT * FROM EMP_TEST

WHERE department_id= 20 ; Data types

Data types are used to specify the basic behavior of a column in the table.On a broader

basis,column behavior can either belong to number,character or a date family.There are multiple

other subtypes which belong to these families.

Number data type

The NUMBER datatype encompasses both integer,fixed-point,and floating-point numeric

values.Early versions of Oracle defined different datatypes for each of these different types of

numbers,but now the NUMBER datatype serves all of these purposes.Choose the NUMBER datatype

Syntax: COLUMN [data type] [NOT NULL] UNIQUE constraint

Sometimes it is necessary to enforce uniqueness for a column value that is not a primary key

column.The UNIQUE constraint can be used to enforce this rule and Oracle will reject any rows that

violate the unique constraint.Unique constraint ensures that the column values are distinct, without

any duplicates.

Syntax:

Column Level:

COLUMN [data type] [CONSTRAINT ] [UNIQUE]

Table Level: CONSTRAINT [constraint name] UNIQUE columnname

Note: Oracle internally creates unique index to prevent duplication in the column values.Indexes

would be discussed later in PL/SQL.

CREATE TABLE TEST

NAME VARCHAR2( 20 )

CONSTRAINT TEST_NAME_UK UNIQUE,

In case of composite unique key,it must be defined at table level as below.

CREATE TABLE TEST

NAME VARCHAR2( 20 ),

STD VARCHAR2( 20 ) ,

CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD)

Primary Key

Each table must normally contain a column or set of columns that uniquely identifies rows of data

that are stored in the table.This column or set of columns is referred to as the primary key.Most

tables have a single column as the primary key.Primary key columns are restricted against NULLs

and duplicate values.

Points to be noted -

A table can have only one primary key.

Multiple columns can be clubbed under a composite primary key.

Oracle internally creates unique index to prevent duplication in the column values.Indexes

would be discussed later in PL/SQL.

Syntax:

Column level:

COLUMN [data type] [CONSTRAINT PRIMARY KEY]

Table level:

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

The following example shows how to use PRIMARY KEY constraint at column level.

CREATE TABLE TEST

( ID NUMBER CONSTRAINT TEST_PK PRIMARY KEY,

The following example shows how to define composite primary key using PRIMARY KEY constraint

at the table level.

CREATE TABLE TEST

CONSTRAINT TEST_PK PRIMARY KEY (ID)

Foreign Key

When two tables share the parent child relationship based on specific column, the joining column

in the child table is known as Foreign Key.This property of corresponding column in the parent

table is known as Referential integrity.Foreign Key column values in the child table can either be

null or must be the existing values of the parent table.Please note that only primary key columns of

the referenced table are eligible to enforce referential integrity.

If a foreign key is defined on the column in child table then Oracle does not allow the parent row to

be deleted,if it contains any child rows.However,if ON DELETE CASCADE option is given at the time

of defining foreign key,Oracle deletes all child rows while parent row is being deleted.Similarly,ON

DELETE SET NULL indicates that when a row in the parent table is deleted, the foreign key values

are set to null.

Syntax:

Column Level:

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

Table level:

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

The following example shows how to use FOREIGN KEY constraint at column level.

CREATE TABLE TEST

(ccode varchar2( 5 ) CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode), ... );

Usage of ON DELETE CASCADE clause

CREATE TABLE TEST

(ccode varchar2( 5 ) CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode) ON DELETE CASCADE, ... ); Check constraint

The below ALTER TABLE statement drop the primary key.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

The below ALTER TABLE statement switches the table mode to read only.

ALTER TABLE EMP_NEW READ ONLY;

Read Only Tables

Read only tables came as an enhancement in Oracle 11g.It allows the tables to be used for read

only purpose. In earlier oracle versions, tables were made read only by granting SELECT privilege

to the other users, but owner still had the read write privilege.But now,if a table is set as Read

only,even owner doesn't have access on data manipulation.

Syntax: ALTER TALE [TABLE NAME] READ ONLY ALTER TALE [TABLE NAME] READ WRITE Illustration SQL>CREATE TABLE ORATEST (id NUMBER) SQL>INSERT INTO ORATEST VALUES ( 1 ); SQL>ALTER TABLE ORATEST READ ONLY; SQL> INSERT INTO ORATEST VALUES ( 2 ); INSERT INTO ORATEST VALUES ( 2 )

ERROR at line 1 : ORA- 12081 : update operation not allowed on table "TEST"."ORATEST" SQL> UPDATE ORATEST SET id = 2 ; UPDATE ORATEST SET id = 2

ERROR at line 1 : ORA- 12081 : update operation not allowed on table "TEST"."ORATEST" SQL> DELETE FROM ORATEST; DELETE FROM ORATEST

ERROR at line 1 : ORA- 12081 : update operation not allowed on table "TEST"."ORATEST" SQL> TRUNCATE TABLE ORATEST; TRUNCATE TABLE ORATEST

ERROR at line 1 : ORA- 12081 : update operation not allowed on table "TEST"."ORATEST" SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 ( 50 )); ALTER TABLE ORATEST ADD (description VARCHAR2 ( 50 ))

ERROR at line 1 : ORA- 12081 : update operation not allowed on table "TEST"."ORATEST" SQL> ALTER TABLE ORATEST READ WRITE; Table altered. SQL> DELETE FROM ORATEST;

1 row deleted. DROP TABLE statement

The DROP TABLE statement is used to remove a table from the database. The dropped table and

its data remain no longer available for selection.Dropped table can be recovered using

FLASHBACK utility,if available in recyclebin.Dropping a table drops the index and triggers

associated with it.

Syntax: DROP TABLE [TABLE NAME] [PURGE]

The below statement will drop the table and place it into the recyclebin.

DROP TABLE emp_new;

The below statement will drop the table and flush it out from the recyclebin also.

DROP TABLE emp_new PURGE; Processing math: 100%

Using DDL Statements in SQL: A Comprehensive Guide, Lecture notes of Database Management Systems (DBMS)

Related documents

Partial preview of the text

Download Using DDL Statements in SQL: A Comprehensive Guide and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!

USING DDL STATEMENTS USING DDL STATEMENTS

Using DDL Statements to Create and Manage Tables

A schema is the collection of multiple database objects,which are known as schema objects.These

objects have direct access by their owner schema.Below table lists the schema objects.

Table - to store data

View - to project data in a desired format from one or more tables

Sequence - to generate numeric values

Index - to improve performance of queries on the tables

Synonym - alternative name of an object

One of the first steps in creating a database is to create the tables that will store an organization's

data.Database design involves identifying system user requirements for various organizational

systems such as order entry, inventory management, and accounts receivable. Regardless of

database size and complexity, each database is comprised of tables.

Creating the table

To create a table in the database,a DBA must have certain information in hand - the table name,

column name, column data types, and column sizes. All this information can be modified later

using DDL commands.

Table Naming Conventions -

The name you choose for a table must follow these standard rules:

The name must begin with a letter A-Z or a-z

Can contain numbers and underscores

Can be in UPPER of lower case

Can be up to 30 characters in length

Cannot use the same name of another existing object in your schema

Must not be a SQL reserved word

Following the above guidelines, 'EMP85' can be a valid table name.But 85EMP is not.Similarly,

UPDATE cannot be a chosen as a table name since it a SQL reserved keyword.

CREATE TABLE statement

The CREATE TABLE is a DDL statement which is used to create tables in the database.The table

gets created as soon as the CREATE TABLE script is executed and is ready to hold the data

onwards.The user must have the CREATE TABLE system privilege to create the table in its own

schema.But to create a table in any user's schema, user must have CREATE ANY TABLE schema.

Here is the syntax of a basic CREATE TABLE statement.There may be many additional clauses to

explicitly provide the storage specifications or segment values.

In the above syntax, DEFAULT specifies default value which can be used during INSERT statement

if the column is ignored. It cannot contain references to other table columns or pseudo columns

CURRVAL , NEXTVAL , LEVEL , andROWNUM except SYSDATE and USER, or date constants that are not

fully specified.

Constraints are the rules defined optionally at the column level or table level coveredlaterinthischapter

.These rules are checked during any data action Insert , update on the table and raise error to abort

the action upon its violation.

For example, the CREATE TABLE statement below creates a table EMP_TEST. Note the column

specifications, data type and precision.

CREATE TABLE SCOTT.EMP_TEST

(EMPID NUMBER,

ENAME VARCHAR2( 100 ),

DEPARTMENT_ID NUMBER,

SALARY NUMBER,

JOB_ID VARCHAR2( 3 ),

HIREDATE DATE,

COMM NUMBER);

A user can refer the tables from other user's schema by prefixing the username or schema with

the table name.For example, a user GUEST wishes to query the employee name and salary from

the EMP_TEST table which is owned by SCOTT. He can issue the below query -

SELECT ENAME, SALARY,

FROM GUEST.EMP_TEST;

A column can hold a default value during the time of table creation.It helps to restrict the NULL

values getting into the column. Default value can be deduced from either a literal, expression or

SQL function which must return a compatible data type to the column. In the below CREATE TABLE

statement, note that the LOCATION_ID column has default value 100.

CREATE TABLE SCOTT.DEPARTMENT

(DEPARTMENT_ID NUMBER,

DNAME VARCHAR2 ( 100 ),

LOCATION_ID NUMBER DEFAULT 100 );

A table can be created from an existing table in the database using a subquery option.It copies the

table structure as well as the data from the table. Data can also be copied based on conditions.The

column data type definitions including the explicitly imposed NOT NULL constraints are copied into

the new table.

The below CTAS script creates a new table EMP_BACKUP. Employee data of department 20 gets

copied into the new table.

CREATE TABLE EMP_BACKUP

AS

SELECT * FROM EMP_TEST

Data types are used to specify the basic behavior of a column in the table.On a broader

basis,column behavior can either belong to number,character or a date family.There are multiple

other subtypes which belong to these families.