Test Your PostgreSQL ALTER TABLE Skills with This Quiz

Progress

True / False

The ALTER TABLE command in PostgreSQL can be used to rename a table.

True False

In PostgreSQL, you can use the ALTER TABLE command followed by RENAME TO to change the name of a table.

True / False

Using ALTER TABLE in PostgreSQL, you can add a new column to an existing table.

True False

The ALTER TABLE command can be followed by ADD COLUMN to add a new column to an existing table.

True / False

You cannot drop a column from a table using the ALTER TABLE command in PostgreSQL.

True False

You can drop a column from a table using ALTER TABLE followed by DROP COLUMN.

True / False

In PostgreSQL, the ALTER TABLE command can be used to change the data type of an existing column.

True False

The ALTER TABLE command followed by ALTER COLUMN and SET DATA TYPE can change the data type of a column.

True / False

You can use ALTER TABLE to set a default value for an existing column in PostgreSQL.

True False

ALTER TABLE followed by ALTER COLUMN and SET DEFAULT allows you to set a default value for an existing column.

True / False

The ALTER TABLE command in PostgreSQL can be used to rename a column in a table.

True False

You can rename a column using ALTER TABLE followed by RENAME COLUMN in PostgreSQL.

True / False

In PostgreSQL, ALTER TABLE cannot be used to add constraints to a table.

True False

You can use the ALTER TABLE command to add various constraints (e.g., primary key, foreign key) to a table.

True / False

PostgreSQL allows the use of ALTER TABLE to partition a table.

True False

PostgreSQL supports table partitioning, and you can use ALTER TABLE to add partitions to an existing table.

True / False

Using the ALTER TABLE command to drop a column in PostgreSQL will automatically drop any indexes and constraints associated with that column.

True False

Dropping a column with ALTER TABLE in PostgreSQL also drops any indexes and constraints associated with that column.

Q10

True / False

In PostgreSQL, you can use the ALTER TABLE command to change the owner of a table.

True False

The ALTER TABLE command can change the ownership of a table by using the OWNER TO clause.

Q11

Single Choice

Which of the following SQL statements is used to add a new column named email to an existing users table?

ALTER TABLE users ADD COLUMN email VARCHAR(255) ALTER TABLE users ADD email VARCHAR(255); ALTER users ADD COLUMN email VARCHAR(255);ALTER TABLE ADD COLUMN users email VARCHAR(255);

The correct syntax for adding a new column to an existing table is ALTER TABLE table_name ADD COLUMN column_name datatype;. In this case, ALTER TABLE users ADD COLUMN email VARCHAR(255); is correct.

Q12

Single Choice

How can you change the data type of a column named birthdate in a table called customers from DATE to DATETIME?

ALTER TABLE customers MODIFY birthdate DATETIME; ALTER customers CHANGE birthdate DATETIME; ALTER TABLE customers ALTER COLUMN birthdate DATETIME;ALTER TABLE customers CHANGE birthdate DATETIME;

To modify a column's data type, the ALTER TABLE table_name MODIFY column_name new_datatype; syntax is used. Here, ALTER TABLE customers MODIFY birthdate DATETIME; correctly changes the birthdate column to DATETIME.

Q13

Single Choice

Which command is used to remove a column named phone_number from an existing table named contacts?

ALTER TABLE contacts DROP COLUMN phone_number; ALTER contacts REMOVE COLUMN phone_number; ALTER contacts DROP phone_number;ALTER TABLE contacts REMOVE phone_number;

To delete a column from a table, the syntax is ALTER TABLE table_name DROP COLUMN column_name;. Therefore, ALTER TABLE contacts DROP COLUMN phone_number; is correct.

Q14

Single Choice

You need to rename an existing column username to user_name in the accounts table. Which statement would you use?

ALTER TABLE accounts RENAME COLUMN username TO user_name; ALTER TABLE accounts MODIFY username TO user_name; ALTER TABLE accounts CHANGE username user_name VARCHAR(255);ALTER TABLE accounts UPDATE username TO user_name;

The ALTER TABLE table_name CHANGE old_column_name new_column_name datatype; command is used to rename a column. In this case, ALTER TABLE accounts CHANGE username user_name VARCHAR(255); is the correct syntax.

Q15

Single Choice

How would you modify the salary column of the employees table to not allow NULL values?

ALTER TABLE employees MODIFY salary DECIMAL(10,2) NOT NULL; ALTER TABLE employees CHANGE salary DECIMAL(10,2) NOT NULL; ALTER TABLE employees ALTER salary NOT NULL;ALTER employees MODIFY salary NOT NULL;

To enforce a NOT NULL constraint, the correct syntax is ALTER TABLE table_name MODIFY column_name datatype NOT NULL;. Therefore, ALTER TABLE employees MODIFY salary DECIMAL(10,2) NOT NULL; is correct.

Q16

Single Choice

If you want to drop a primary key from a table named orders, which SQL command would you use?

ALTER TABLE orders DROP PRIMARY KEY; ALTER orders REMOVE PRIMARY KEY; ALTER TABLE orders DELETE PRIMARY KEY;ALTER TABLE orders DROP KEY primary;

The correct syntax to drop a primary key is ALTER TABLE table_name DROP PRIMARY KEY;. Here, ALTER TABLE orders DROP PRIMARY KEY; is the correct statement.

Q17

Single Choice

Consider a table products with a column price. How can you change the price column to allow NULL values and set the default value to 0?

ALTER TABLE products MODIFY price DECIMAL(10,2) NULL DEFAULT 0; ALTER TABLE products MODIFY COLUMN price DECIMAL(10,2) NULL DEFAULT 0; ALTER TABLE products ALTER price DECIMAL(10,2) NULL DEFAULT 0;ALTER TABLE products CHANGE price DECIMAL(10,2) NULL DEFAULT 0;

The correct syntax to allow NULL values and set a default value is ALTER TABLE table_name MODIFY column_name datatype NULL DEFAULT default_value;. Here, ALTER TABLE products MODIFY price DECIMAL(10,2) NULL DEFAULT 0; is correct.

Q18

Single Choice

Which command will you use to change the order of columns in the customers table, moving last_name to appear after first_name?

ALTER TABLE customers MODIFY last_name AFTER first_name; ALTER TABLE customers CHANGE last_name AFTER first_name; ALTER TABLE customers MODIFY last_name POSITION first_name;ALTER TABLE customers ALTER last_name AFTER first_name;

To reorder columns, the correct syntax is ALTER TABLE table_name MODIFY column_name AFTER other_column;. Therefore, ALTER TABLE customers MODIFY last_name AFTER first_name; is the correct answer.

Q19

Single Choice

How do you add a foreign key constraint fk_order_customer to the orders table that references the id column of the customers table?

ALTER TABLE orders ADD CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id); ALTER TABLE orders ADD CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers; ALTER TABLE orders ADD FOREIGN KEY fk_order_customer REFERENCES customers(id)ALTER orders ADD CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

The correct syntax to add a foreign key constraint is ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES other_table(column_name);. Hence, ALTER TABLE orders ADD CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id); is correct.

Q20

Single Choice

You have a table named employees with an existing unique index on the email column. How can you drop the unique index and then rename the email column to email_address in a single operation?

ALTER TABLE employees DROP INDEX email, CHANGE email email_address VARCHAR(255); ALTER TABLE employees CHANGE email email_address VARCHAR(255), DROP INDEX email; ALTER TABLE employees DROP INDEX email, RENAME COLUMN email TO email_address;ALTER employees DROP UNIQUE email, RENAME COLUMN email TO email_address;

In MySQL, you can perform multiple operations in a single ALTER TABLE statement by separating each operation with a comma. The correct syntax to drop the unique index and rename the column is ALTER TABLE employees DROP INDEX email, CHANGE email email_address VARCHAR(255);.

Q21

Multiple Choice

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

SQL Code

ALTER TABLE employees
ADD COLUMN department VARCHAR(50) NOT NULL;

ALTER TABLE employees ADD COLUMN department VARCHAR(50) NOT NULL; ALTER TABLE employees ADD department VARCHAR(50); ALTER employees ADD COLUMN department VARCHAR(50) NOT NULL;ALTER TABLE employees ADD department VARCHAR(50) NOT NULL;

This command adds a new column 'department' to the 'employees' table, specifying that it cannot contain NULL values.

Q22

Multiple Choice

Which SQL command correctly drops a column from a table in PostgreSQL?

SQL Code

ALTER TABLE orders
DROP COLUMN shipping_date;

ALTER TABLE orders DROP COLUMN shipping_date; ALTER TABLE orders REMOVE COLUMN shipping_date; ALTER TABLE orders DELETE COLUMN shipping_date;ALTER orders DROP COLUMN shipping_date;

This command removes the 'shipping_date' column from the 'orders' table, effectively deleting it and all of its data.

Q23

Multiple Choice

Which SQL command is used to rename an existing column in a PostgreSQL table?

SQL Code

ALTER TABLE customers
RENAME COLUMN address TO location;

ALTER TABLE customers RENAME COLUMN address TO location; ALTER TABLE customers CHANGE COLUMN address TO location; ALTER customers RENAME address TO location;ALTER TABLE customers RENAME address AS location;

This command renames the 'address' column to 'location' in the 'customers' table, modifying the column name but keeping its data.

Q24

Multiple Choice

Which SQL command adds a UNIQUE constraint to an existing column in PostgreSQL?

SQL Code

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email); ALTER TABLE users ADD UNIQUE (email); ALTER TABLE users ALTER COLUMN email SET UNIQUE;ALTER users ADD UNIQUE (email);

This command adds a UNIQUE constraint on the 'email' column in the 'users' table, ensuring that all values in this column are unique.

Q25

Multiple Choice

Which SQL command is used to modify a column's data type in an existing PostgreSQL table?

SQL Code

ALTER TABLE orders
ALTER COLUMN total_price TYPE NUMERIC(12, 2);

ALTER TABLE orders ALTER COLUMN total_price TYPE NUMERIC(12, 2); ALTER TABLE orders MODIFY COLUMN total_price TYPE NUMERIC(12, 2); ALTER orders ALTER COLUMN total_price SET TYPE NUMERIC(12, 2);ALTER TABLE orders CHANGE COLUMN total_price TYPE NUMERIC(12, 2);

This command changes the data type of the 'total_price' column in the 'orders' table to NUMERIC with a precision of 12 and a scale of 2.

Q26

Multiple Choice

Which SQL command is used to set a default value for an existing column in a PostgreSQL table?

SQL Code

ALTER TABLE employees
ALTER COLUMN status SET DEFAULT 'active';

ALTER TABLE employees ALTER COLUMN status SET DEFAULT 'active'; ALTER TABLE employees ALTER COLUMN status DEFAULT 'active'; ALTER employees MODIFY COLUMN status SET DEFAULT 'active';ALTER TABLE employees SET DEFAULT 'active' ON status;

This command sets the default value of the 'status' column in the 'employees' table to 'active'.

Q27

Multiple Choice

Which SQL command correctly drops a constraint from a PostgreSQL table?

SQL Code

ALTER TABLE products
DROP CONSTRAINT chk_price;

ALTER TABLE products DROP CONSTRAINT chk_price; ALTER TABLE products REMOVE CONSTRAINT chk_price; ALTER TABLE products DELETE CONSTRAINT chk_price;ALTER products DROP CONSTRAINT chk_price;

This command removes the 'chk_price' constraint from the 'products' table, which might have been used to enforce data rules.

Q28

Multiple Choice

Which SQL command is used to rename an existing table in PostgreSQL?

SQL Code

ALTER TABLE orders
RENAME TO sales_orders;

ALTER TABLE orders RENAME TO sales_orders; ALTER TABLE orders CHANGE NAME TO sales_orders; ALTER TABLE orders MODIFY TO sales_orders;ALTER orders RENAME TO sales_orders;

This command renames the 'orders' table to 'sales_orders', changing the table name but not its structure or data.

Q29

Multiple Choice

Which SQL command adds a CHECK constraint to an existing table in PostgreSQL?

SQL Code

ALTER TABLE accounts
ADD CONSTRAINT chk_balance CHECK (balance >= 0);

ALTER TABLE accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0); ALTER TABLE accounts ADD CHECK (balance >= 0); ALTER accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0);ALTER TABLE accounts CHECK balance >= 0;

This command adds a CHECK constraint to the 'accounts' table to ensure that the 'balance' column is always non-negative.

Q30

Multiple Choice

Which SQL command in PostgreSQL is used to drop a foreign key constraint from a table?

SQL Code

ALTER TABLE orders
DROP CONSTRAINT fk_customer;

ALTER TABLE orders DROP CONSTRAINT fk_customer; ALTER TABLE orders REMOVE CONSTRAINT fk_customer; ALTER orders DROP CONSTRAINT fk_customer;ALTER TABLE orders DELETE CONSTRAINT fk_customer;

This command removes the foreign key constraint 'fk_customer' from the 'orders' table, breaking the relationship between the 'orders' and 'customers' tables.

PostgreSQL Database Quiz Questions