Given the following DDL statement: CREATE TABLE sales (item CHAR(20)); If a DBA
wishes to increase the size of the ITEM column, which SQL statement needs to be used?
ALTER TABLE sales ADD COLUMN item CHAR(40);
ALTER TABLE sales ALTER COLUMN item CHAR(40);
ALTER TABLE sales MODIFY COLUMN item CHAR(40);
ALTER TABLE sales ALTER COLUMN item SET DATA TYPE CHAR(40);
One Comment on “which SQL statement needs to be used?”
Alters the definition of a column. Only the specified attributes will be altered; others will remain unchanged. Columns of a typed table cannot be altered (SQLSTATE 428DH). The table must not be defined as a history table (SQLSTATE 428FR).
Specifies the name of the column that is to be altered. The column-name must identify an existing column of the table (SQLSTATE 42703). The name must not be qualified. The name must not identify a column that is otherwise being added, altered, or dropped in the same ALTER TABLE statement (SQLSTATE 42711).
SET DATA TYPE altered-data-type
Specifies the new data type of the column. The new data type must be castable from the existing data type of the column (SQLSTATE 42837) except when one of the data types is a distinct type, in which case the source data type of the distinct type is used in determining if the data types are castable. A LOB column cannot be altered to a different data type (SQLSTATE 42837). A non-LOB column cannot be altered to a LOB data type (SQLSTATE 42837).
Altering a string data type that results in the truncation of non-blank characters from existing data is not allowed (SQLSTATE 42837).