New Document
Computer Science
Computer Catlog
Oracle Catlog

History of Oracle
Introduction to Terms
Oracle Configurations
Roles of Database Administrator
Oracle Architecture
A Brief History of SQL
Dr. Codd's 12 Rules
An Overview of SQL
The SELECT Statement
Expressions, Conditions, and Operators
Functions
Clauses in SQL
Joining Tables
Sub Query
Manipulating Data
Building a Database
Views and Indexes
Controlling Transactions
Database Security
Advanced SQL Topics
Cursors
Stored Procedures
Triggers
Embedded SQL
SQL Tuning
Using Views in Data Dictionary
Using SQL to Generate SQL Statements

Design and Build a 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:

The CREATE

The USE

The ALTER

The DROP

    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:

SYNTAX:

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.

SYNTAX:

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


SYNTAX:

CREATE TABLE new-table AS (SELECT * FROM old-table);

SYNTAX:

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


SYNTAX:

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

SYNTAX:

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


SYNTAX:

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

SYNTAX:

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


Global temporary tables are distinct within SQL sessions.

SYNTAX:

CREATE GLOBAL TEMPORARY TABLE table-name ( ...);

SYNTAX:

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.

SYNTAX:

DECLARE LOCAL TEMPORARY TABLE table-name ( ...);

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:

SYNTAX:

USE employees

    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:

SYNTAX:

To add multiple columns to an existing table:

SYNTAX:

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

SYNTAX:

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


To modify a column in an existing table:

SYNTAX:

ALTER TABLE table-name MODIFY column-name column-type;

SYNTAX:

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


To drop a column in an existing table:

SYNTAX:

ALTER TABLE table-name DROP COLUMN column-name;

SYNTAX:

ALTER TABLE supplier DROP COLUMN supplier-name;

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

Rename column(s) in a table


To rename a column in an existing table:

SYNTAX:

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

SYNTAX:

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:

SYNTAX:

DROP TABLE personal-infos

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

SYNTAX:

DROP DATABASE employees

    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.