ddl statements,ddl statements in sql,ddl statements in oracle,ddl create,ddl database

The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. Let's take a look at the structure and usage of four basic DDL commands:

CREATE

Installing a database management system (DBMS) on a computer allows you to create and manage many independent databases. For example, you may want to maintain a database of customer contacts for your sales department and a personnel database for your HR department.

The CREATE command can be used to establish each of these databases on your platform. For example, the command:

CREATE TABLE table-name(
column1 datatype null/not null,
column2 datatype null/not null,
...);

Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.

CREATE TABLE supplier(
supplier-id numeric(10) not null,
supplier-name varchar2(50) not null,
contact-name varchar2(50));

You can also create a table from an existing table by copying the existing table's columns.

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

Copying all columns from another table

CREATE TABLE suppliers
AS (SELECT * FROM companies WHERE id > 1000);

This would create a new table called suppliers that included all columns from the companies table.

If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Copying selected columns from another table

CREATE TABLE new-table AS (SELECT column-1, ... column-n FROM old-table);

CREATE TABLE suppliers
AS (SELECT id, address, city, state, zip FROM companies WHERE id > 1000);

This would create a new table called suppliers, but the new table would only include the specified columns from the companies table.

Again, if there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Copying selected columns from multiple tables

CREATE TABLE new-table AS (SELECT column-1, column2, ... column-n
FROM old-table-1, old-table-2, ... old-table-n);

CREATE TABLE suppliers
AS (SELECT companies.id, companies.address, categories.cat-type
FROM companies, categories
WHERE companies.id = categories.id AND companies.id > 1000);

This would create a new table called suppliers based on columns from both the companies and categories tables.

Global temporary tables

CREATE GLOBAL TEMPORARY TABLE supplier
( supplier-id numeric(10) not null,
supplier-name varchar2(50) not null,
contact-name varchar2(50) ) ;

This would create a global temporary table called supplier .

Local temporary tables

Local temporary tables are distinct within modules and embedded SQL programs within SQL sessions.

USE

The USE command allows you to specify the database you wish to work with within your DBMS. For example, if we're currently working in the sales database and want to issue some commands that will affect the employees database, we would preface them with the following SQL command:

It's important to always be conscious of the database you are working in before issuing SQL commands that manipulate data.

ALTER

Adding column(s) in a table

Once you've created a table within a database, you may wish to modify the definition of it. The ALTER command allows you to make changes to the structure of a table without deleting and recreating it. Take a look at the following command:

ALTER TABLE table-name ADD (
column-1 column-definition,
column-2 column-definition,
...
column-n column-definition );

ALTER TABLE supplier ADD ( supplier-name varchar2(50), city varchar2(45) );

This will add two columns (supplier-name and city) to the supplier table

Modifying column(s) in a table

ALTER TABLE supplier MODIFY supplier-name varchar2(100) not null;

This will modify the column called supplier-name to be a data type of varchar2(100) and force the column to not allow null values.

Drop column(s) in a table

This will drop the column called supplier-name from the table called supplier.

Rename column(s) in a table

ALTER TABLE table-name RENAME COLUMN old-name to new-name;

ALTER TABLE supplier RENAME COLUMN supplier-name to sname;

This will rename the column called supplier-name to sname.

DROP

The final command of the Data Definition Language, DROP, allows us to remove entire database objects from our DBMS. For example, if we want to permanently remove the personal-info table that we created, we'd use the following command:

Similarly, the command below would be used to remove the entire employees database:

Use this command with care! Remember that the DROP command removes entire data structures from your database. If you want to remove individual records, use the DELETE command of the Data Manipulation Language.

Better to know some

... than all

Design and Build a Database