4.3 Data Definition Language

4.3 DDL – CREATE, ALTER, and DROP statements.

DDL statements are those which are used to create, modify and drop the definitions or structures of various tables, views, indexes and other elements of the DBMS.

The CREATE TABLE statement

Creates a new table.

General form:

CREATE TABLE

(*);

* - table element may be attribute with its data-type and size or any integrity constraint on attributes.

Some CREATE TABLE statements on the Case Example

Query:

CREATE TABLE customers

( cust# NUMBER(6) NOT NULL,

custname CHAR(30) ,

city CHAR(20));

- This query Creates a table CUSTOMERS with 3 fields - cust#, custname and city. Cust# cannot be null

Query:

CREATE TABLE ord_sep <------------------- AS SELECT * from ord_aug;

Creates a new table ord_sep, which has the same structure of ord_aug. The data in ord_aug is copied to the new table ord_sep.

- This query Creates table ORD_SEP as a cpy of ORD-AUG. Copies structure as well as data.

Query:

CREATE TABLE ord_sep <------------------ AS SELECT * from ord_aug WHERE 1 = 2;

Creates a new table ord_sep, which has the same structure of ord_aug. No data in ord_aug is copied to the new table since there is no row which satisfies the 'always false' condition 1 = 2.

- This query Creates table ORD_SEP as a copy of ORD_AUG, but does not copy any data as the WHERE clause is never satisfied.

The ALTER TABLE statement

Alters the structure of an existing table.

General form:

ALTER TABLE

ADD | MODIFY (

Examples of ALTER TABLE statement.

Query:

ALTER TABLE customers MODIFY custname CHAR(35); <-------------

Modifies the data type/size of an attribute in the table

- This query changes the custname field to a character field of length 35. Used for modifying field lengths and attributes.

Query:

ALTER TABLE customers ADD (phone number(8), <------------------ credit_rating char(1));

Adds two new attributes to the Customers table. Here, for existing tuples (if any), the new attribute will take NULL values since no DEFAULT value is mentioned for the attribute.

- This query adds two new fields - phone & credit_rating to the customers table.

The DROP TABLE statement

DROPS an existing table.

General form:

DROP TABLE ;

Example:

Query:

DROP TABLE ord_sep;

- The above query drops table ORD_SEP from the database

Creating & Dropping Views

A view is a virtual relation created with attributes from one or more base tables.

SELECT * FROM myview1; at any given time will evaluate the view-defining query in the CREATE VIEW statement and display the result.

Query:

CREATE VIEW myview1

AS SELECT

ord#, orddate, ord_aug.cust#, custname

FROM ord_aug, customers

WHERE ord_aug.cust# = customers.cust#;

- This query defines a view consisting of ord#, cust#, and custname using a join of ORD_AUG and CUSTOMERS tables.

Query:

CREATE VIEW myview2 (ItemNo, Quantity)

AS SELECT item#, qty

FROM ord_items;

- This query defines a view with columns item# and qty from the ORD_ITEMS table, and renames these columns as ItemNo. and Quantity respectively.

Query:

CREATE VIEW myview3 AS SELECT item#, descr, price FROM items WHERE price <> WITH CHECK OPTION; <-------------------

WITH CHECK OPTION in a CREATE VIEW statement indicates that INSERTs or UPDATEs on the view will be rejected if they violate any integrity constraint implied by the view-defining query.

- This query defines the view as defined. WITH CHECK OPTION ensures that if this view is used for updation, the updated values do not cause the row to fall outside the view.

Query:

DROP VIEW myview1; <---- To drop a view

- this query drops the view MYVIEW1

Creating & Dropping Indexes

Query: CREATE INDEX i_city <-------------------- ON customers (city);

Creates a new index named i_city. The new index file(table) will have the values of city column of Customers table

Query:

CREATE UNIQUE INDEX i_custname <------ ON customers (custname);

Creates an index which allows only unique values for custnames

Query:

CREATE INDEX i_city_custname <--------- ON customers (city, custname);

Creates an index based on two fields : city and custname

Query:

DROP INDEX i_city; <--------------------

Drops index i_city