Data definition language

 

Data definition language (DDL) describes the portion of SQL that creates, alters, and deletes database objects. These database objects include schemas, tables, views, sequences, catalogs, indexes, and aliases.

• Creating a schema
  A schema provides a logical grouping of SQL objects. To create a schema, use the CREATE SCHEMA statement.

• Creating a table
  A table can be visualized as a two-dimensional arrangement of data that consists of rows and columns. To create a table, use the CREATE TABLE statement.

• Creating a table using LIKE
  You can create a table that looks like another table. That is, you can create a table that includes all of the column definitions from an existing table.

• Creating a table using AS
  You can create a table from the result of a SELECT statement. To create this type of table, use the CREATE TABLE AS statement.

• Creating and altering a materialized query table
  A materialized query table is a table whose definition is based on the result of a query, and whose data is in the form of precomputed results that are taken from the table or tables on which the materialized query table definition is based.

• Declaring a global temporary table
  You can create a temporary table for use with your current session. To create a temporary table, use the DECLARE GLOBAL TEMPORARY TABLE statement.

• Creating and altering an identity column
  Every time a row is added to a table with an identity column, the identity column value for the new row is generated by the system.

• Using ROWID
  Using ROWID is another way to have the system assign a unique value to a column. ROWID is similar to identity columns. But rather than being an attribute of a numeric column, it is a separate data type.

• Creating and using sequences
  Sequences are similar to identity columns in that they both generate unique values. However, sequences are objects that are independent of any tables. You can use sequences to generate values quickly and easily.

• Creating descriptive labels using the LABEL ON statement
  Sometimes the table name, column name, view name, index name, sequence name, alias name, or SQL package name does not clearly define data that is shown on an interactive display of the table. You can create a more descriptive label for these names by using the LABEL ON statement.

• Describing an SQL object using COMMENT ON
  After you create an SQL object, such as a table or view, you can provide object information for future reference using the COMMENT ON statement.

• Changing a table definition
  You change the definition of a table by adding a column, changing an existing column definition, such as its length or default value, dropping an existing column, adding a constraint, or removing a constraint.

• Creating and using ALIAS names
  When you refer to an existing table or view, or to a physical file that consists of multiple members, you can avoid using file overrides by creating an alias. To create an alias, use the CREATE ALIAS statement.

• Creating and using views
  A view can be used to access data in one or more tables or views. You create a view by using a SELECT statement.

• Creating indexes
  You can use indexes to sort and select data. In addition, indexes help the system retrieve data faster for better query performance.

• Catalogs in database design
  A catalog is automatically created when you create a schema. There is also a system-wide catalog that is always in the QSYS2 library.

• Dropping a database object
  The DROP statement deletes an object. Depending on the action requested, any objects that are directly or indirectly dependent on that object might also be deleted or might prevent the drop from happening.

 

Parent topic:

SQL programming

 

Related concepts

Types of SQL statements

 

Related tasks

Getting started with SQL