Test Your Oracle ALTER TABLE Knowledge

Progress

True / False

The ALTER TABLE statement in Oracle is used to modify the structure of an existing table.

True False

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table, as well as to add or drop constraints.

True / False

In Oracle, you can use the ALTER TABLE statement to rename a column.

True False

The ALTER TABLE statement can be used to rename a column using the RENAME COLUMN clause.

True / False

The ALTER TABLE statement in Oracle cannot be used to add a new column to an existing table.

True False

The ALTER TABLE statement can be used to add new columns to an existing table using the ADD clause.

True / False

In Oracle, you can use the ALTER TABLE statement to change the data type of an existing column.

True False

The ALTER TABLE statement allows changing the data type of an existing column using the MODIFY clause.

True / False

The ALTER TABLE statement in Oracle can be used to add a primary key constraint to an existing table.

True False

The ALTER TABLE statement can add a primary key constraint to an existing table using the ADD CONSTRAINT clause.

True / False

In Oracle, the ALTER TABLE statement can be used to drop a column from a table.

True False

The ALTER TABLE statement can drop an existing column from a table using the DROP COLUMN clause.

True / False

In Oracle, the ALTER TABLE statement can be used to move a table to a different tablespace.

True False

The ALTER TABLE statement can move a table to a different tablespace using the MOVE clause.

True / False

In Oracle, the ALTER TABLE statement cannot be used to rename an existing table.

True False

The ALTER TABLE statement can rename an existing table using the RENAME TO clause.

True / False

The ALTER TABLE statement in Oracle can be used to add a virtual column to an existing table.

True False

The ALTER TABLE statement can add a virtual column, which is computed from an expression, to an existing table using the ADD clause.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes topics on using the ALTER TABLE statement for advanced table modifications and optimizations.

True False

The OCP certification exam covers advanced topics, including the use of the ALTER TABLE statement for complex table modifications and performance optimizations, reflecting the candidate's in-depth knowledge of Oracle Database management.

Q11

Single Choice

Which Oracle SQL command is used to add a new column to an existing table?

ADD COLUMN ALTER TABLE MODIFY TABLEUPDATE TABLE

The ALTER TABLE command is used in Oracle to add, modify, or drop columns in an existing table.

Q12

Single Choice

What does the following Oracle SQL command do?

SQL Code

ALTER TABLE employees ADD email VARCHAR2(100);

Adds a new row to the employees table. Adds a new column named email to the employees table. Deletes the email column from the employees table.Modifies the data type of the email column in the employees table.

The ALTER TABLE statement adds a new column email with the data type VARCHAR2(100) to the employees table.

Q13

Single Choice

How can you remove a column from an existing table in Oracle?

DROP COLUMN DELETE COLUMN ALTER TABLE ... DROP COLUMNREMOVE COLUMN

The correct syntax to remove a column from a table in Oracle is ALTER TABLE table_name DROP COLUMN column_name.

Q14

Single Choice

What will happen if you execute the following Oracle SQL command?

SQL Code

ALTER TABLE employees MODIFY salary NUMBER(10,2);

Changes the data type of the salary column to NUMBER(10,2). Adds a new column salary with data type NUMBER(10,2). Deletes the salary column from the employees table.Returns an error because the MODIFY clause is not valid.

The ALTER TABLE ... MODIFY command changes the data type of an existing column in the table. In this case, it changes the salary column to NUMBER(10,2).

Q15

Single Choice

Which Oracle SQL statement is used to rename a column in an existing table?

SQL Code

ALTER TABLE employees RENAME COLUMN old_name TO new_name;

Alters the data type of the column. Renames a column from old_name to new_name. Adds a new column named new_name.Returns an error because columns cannot be renamed.

The ALTER TABLE ... RENAME COLUMN statement is used to rename an existing column in an Oracle table.

Q16

Single Choice

What does the following Oracle SQL command do?

SQL Code

ALTER TABLE orders ADD CONSTRAINT order_date_nn CHECK (order_date IS NOT NULL);

Adds a new column named order_date_nn. Adds a NOT NULL constraint to the order_date column. Adds a CHECK constraint to ensure order_date is not NULL.Modifies the order_date column to accept NULL values.

The ALTER TABLE ... ADD CONSTRAINT ... CHECK statement adds a CHECK constraint to ensure that the order_date column does not accept NULL values.

Q17

Single Choice

Which Oracle SQL command would you use to move an existing table to a different tablespace?

SQL Code

ALTER TABLE employees MOVE TABLESPACE new_tablespace;

Moves the table to a new schema. Moves the table to a new tablespace named new_tablespace. Renames the table to new_tablespace.Creates a copy of the table in the new_tablespace.

The ALTER TABLE ... MOVE TABLESPACE command moves the table's storage to a specified tablespace.

Q18

Single Choice

What does the following Oracle SQL command accomplish?

SQL Code

ALTER TABLE employees MODIFY CONSTRAINT emp_dept_fk DEFERRABLE INITIALLY IMMEDIATE;

Modifies the emp_dept_fk foreign key constraint to be deferrable and initially immediate. Drops the emp_dept_fk constraint from the employees table. Adds a new foreign key constraint named emp_dept_fk.Converts the emp_dept_fk constraint to a unique constraint.

The ALTER TABLE ... MODIFY CONSTRAINT statement modifies the existing emp_dept_fk foreign key constraint to be deferrable and enforced immediately upon execution.

Q19

Single Choice

How would you compress a table to save storage space using an ALTER TABLE statement in Oracle?

SQL Code

ALTER TABLE sales COMPRESS FOR OLTP;

Compresses the sales table data for efficient storage in OLTP environments. Increases the size of the sales table. Converts the sales table to a read-only table.Moves the sales table to a different tablespace.

The ALTER TABLE ... COMPRESS FOR OLTP statement enables compression for the table, optimizing storage and potentially improving performance in OLTP (Online Transaction Processing) environments.

Q20

Single Choice

Which of the following is true regarding the use of the ALTER TABLE statement in Oracle for partitioned tables?

You cannot add or modify partitions using ALTER TABLE. You can add, drop, and modify partitions of a partitioned table using ALTER TABLE. ALTER TABLE cannot be used with partitioned tables.Partitions can only be modified using a special partition management tool.

The ALTER TABLE statement in Oracle allows you to manage partitions, including adding, dropping, and modifying them in a partitioned table.

Q21

Multiple Choice

Identify the SQL scripts that correctly add a new column to an existing table using the ALTER TABLE statement in Oracle.

ALTER TABLE employees ADD email VARCHAR2(100); ALTER TABLE employees ADD (email VARCHAR2(100) NULL); ALTER TABLE employees ADD COLUMN email VARCHAR2(100);ALTER TABLE employees ADD email VARCHAR2(100) DEFAULT NULL;

The ALTER TABLE statement is used to modify an existing table structure. The correct scripts will demonstrate how to add a new column that allows NULL values.

Q22

Multiple Choice

Select the SQL scripts that correctly modify a column's data type using the ALTER TABLE statement in Oracle.

ALTER TABLE customers MODIFY phone_number VARCHAR2(15); ALTER TABLE customers ALTER phone_number VARCHAR2(15); ALTER TABLE customers MODIFY (phone_number VARCHAR2(15));ALTER TABLE customers CHANGE phone_number VARCHAR2(15);

Altering a column's data type or length is a common use of the ALTER TABLE statement. The correct scripts will demonstrate how to modify the column's length.

Q23

Multiple Choice

Determine the SQL scripts that correctly drop a column from an existing table using the ALTER TABLE statement in Oracle.

ALTER TABLE employees DROP COLUMN middle_name; ALTER TABLE employees DROP middle_name; ALTER TABLE employees REMOVE COLUMN middle_name;ALTER TABLE employees DELETE COLUMN middle_name;

Dropping a column from a table is done using the ALTER TABLE DROP COLUMN statement. The correct scripts will demonstrate how to remove a column from an existing table.

Q24

Multiple Choice

Identify the SQL scripts that correctly add a constraint to an existing table using the ALTER TABLE statement in Oracle.

ALTER TABLE employees ADD CONSTRAINT unq_email UNIQUE (email); ALTER TABLE employees ADD UNIQUE (email); ALTER TABLE employees ADD CONSTRAINT UNIQUE (email);ALTER TABLE employees MODIFY email UNIQUE;

Adding a constraint to an existing table is done using the ALTER TABLE ADD CONSTRAINT statement. The correct scripts will demonstrate how to add a UNIQUE constraint.

Q25

Multiple Choice

Select the SQL scripts that correctly rename a column using the ALTER TABLE statement in Oracle.

ALTER TABLE departments RENAME COLUMN dept_id TO department_id; ALTER TABLE departments MODIFY dept_id TO department_id; ALTER TABLE departments CHANGE dept_id TO department_id;ALTER TABLE departments RENAME dept_id TO department_id;

Renaming a column is done using the ALTER TABLE RENAME COLUMN statement. The correct scripts will demonstrate how to rename a column in an existing table.

Q26

Multiple Choice

Identify the SQL scripts that correctly add multiple columns to an existing table using the ALTER TABLE statement in Oracle.

ALTER TABLE employees ADD (date_of_birth DATE, gender VARCHAR2(10)); ALTER TABLE employees ADD date_of_birth DATE, ADD gender VARCHAR2(10); ALTER TABLE employees ADD (date_of_birth DATE); ALTER TABLE employees ADD (gender VARCHAR2(10));ALTER TABLE employees ADD COLUMNS (date_of_birth DATE, gender VARCHAR2(10));

Adding multiple columns to an existing table can be done in a single ALTER TABLE statement. The correct scripts will demonstrate how to add multiple columns at once.

Q27

Multiple Choice

Determine the SQL scripts that correctly enable and disable a constraint using the ALTER TABLE statement in Oracle.

ALTER TABLE employees DISABLE CONSTRAINT fk_dept_id; ALTER TABLE employees ENABLE CONSTRAINT fk_dept_id; ALTER TABLE employees DISABLE fk_dept_id; ALTER TABLE employees ENABLE fk_dept_id; ALTER TABLE employees SET CONSTRAINT fk_dept_id DISABLE; ALTER TABLE employees SET CONSTRAINT fk_dept_id ENABLE;ALTER TABLE employees DROP CONSTRAINT fk_dept_id; ALTER TABLE employees ADD CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

Enabling and disabling constraints is important for certain maintenance tasks. The correct scripts will demonstrate how to disable and then re-enable a constraint using the ALTER TABLE statement.

Q28

Multiple Choice

Select the SQL scripts that correctly change a column's default value using the ALTER TABLE statement in Oracle.

ALTER TABLE orders MODIFY status DEFAULT 'Pending'; ALTER TABLE orders ALTER status SET DEFAULT 'Pending'; ALTER TABLE orders SET DEFAULT 'Pending' FOR status;ALTER TABLE orders MODIFY (status DEFAULT 'Pending');

Changing a column's default value is done using the ALTER TABLE MODIFY statement. The correct scripts will demonstrate how to set a new default value for an existing column.

Q29

Multiple Choice

Identify the SQL scripts that correctly move a table to a new tablespace using the ALTER TABLE statement in Oracle.

ALTER TABLE sales MOVE TABLESPACE data_ts; ALTER TABLE sales MOVE data_ts; ALTER TABLE sales MOVE TO data_ts;ALTER TABLE sales MOVE TABLESPACE data_ts COMPACT;

Moving a table to a different tablespace can be done using the ALTER TABLE MOVE statement. The correct scripts will demonstrate how to move a table to a specified tablespace.

Q30

Multiple Choice

Select the SQL scripts that correctly drop a constraint using the ALTER TABLE statement in Oracle.

ALTER TABLE employees DROP CONSTRAINT unq_email; ALTER TABLE employees REMOVE CONSTRAINT unq_email; ALTER TABLE employees DROP UNIQUE unq_email;ALTER TABLE employees DELETE CONSTRAINT unq_email;

Dropping a constraint from an existing table is done using the ALTER TABLE DROP CONSTRAINT statement. The correct scripts will demonstrate how to remove a specific constraint.

Oracle Database Quiz Questions