Tables 

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

Creating a Table

To create a table, use the CREATE TABLE statement.

CREATE TABLE hotel.person
(pno       FIXED(6),
 name      CHAR(20),
 city      CHAR(20))

You specify the name of the table (person) after the keywords CREATE TABLE. Create the person table in the hotel schema.

In the list of column definitions, you have to enter the name and data type for each column. The list of column definitions is in parentheses.

●     The person table contains columns with the following names: pno, name, city

●     The columns have the following data types: pno FIXED(6) and name and city, each CHAR(20)

 

See also:

SQL Reference Manual, CREATE TABLE Statement (create_table_statement)

SQL Reference Manual, Column Name (column_name)

SQL Reference Manual, Data Type (data_type)

 

Data integrity

The person table is a very simple table. The only input restriction for values in the columns in the person table is that the data types are predefined. In reality, additional entries, restrictions, and objects, such as constraints, primary keys, domain definitions, database procedures and database triggers are usually required to ensure data integrity.

See also:

SQL Reference Manual, Ensuring Data Integrity

 

Changing a Table

You can use the ALTER TABLE statement to change the table definition while the database is in operation.

 

ALTER TABLE hotel.person
  ADD (code FIXED(5), fon FIXED(8))

Columns for the telephone dialing code and number are added to the person table.

See also:

SQL Reference Manual, ADD Definition (add_definition)

 

ALTER TABLE hotel.person
  DROP (code)

The column for the telephone dialing code is deleted from the customer table.

See also:

SQL Reference Manual, DROP Definition (drop_definition)

 

ALTER TABLE hotel.person
  MODIFY fon FIXED(10)

The column definition is changed in the customer table. The column is redefined only if it corresponds to the values already stored.

See also:

SQL Reference Manual, MODIFY Definition (modify_definition)

 

Additional changes can be made to existing tables.

See also:

SQL Reference Manual, ALTER TABLE Statement (alter_table_statement)

 

Renaming a Table

To rename a table, use the RENAME TABLE statement.

 

RENAME TABLE hotel.person TO member

Rename the person table to member

 

See also:

SQL Reference Manual, RENAME TABLE Statement (rename_table_statement)

 

Deleting a Table

To delete a table, use the DROP TABLE statement.

 

DROP TABLE hotel.member

You can use this SQL statement to delete the table definition, the table contents, and all dependent objects (such as view tables and indexes). The table can only be deleted by the table owner.

 

See also:

SQL Reference Manual, DROP TABLE Statement (drop_table_statement)

More examples for Data Definition