1) You have a table customers with a column email that currently allows NULL values. Modify the table so that future entries must always have an email.
Answer:
ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;
2) In the users table, ensure that the username column is unique across all records using an ALTER statement.
Answer:
ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);
3) In the products table, enforce that price must always be greater than 0 using an ALTER command.
Answer:
ALTER TABLE products
ADD CONSTRAINT price_check CHECK (price > 0);
4) Modify the orders table so that the status column defaults to 'pending' if no value is provided during insertion.
Answer:
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';
5) Alter the employees table by adding a new column salary such that
It cannot be NULL
It must always be greater than 10,000
Answer:
add column:
ALTER TABLE employees
ADD COLUMN salary INT;applying conditions:
ALTER TABLE employees
ALTER COLUMN salary SET NOT NULL;
ALTER TABLE employees
ADD CONSTRAINT salary_check CHECK (salary > 10000);
6) Modify the foreign key constraint between employees and departments so that when a department is deleted, all related employees are automatically removed.
Answer:
ALTER TABLE employees
ADD CONSTRAINT employees_department_id_fkey
FOREIGN KEY (department_id)
REFERENCES departments(id)
ON DELETE CASCADE;
7) In the accounts table, remove an existing CHECK constraint that enforces balance >= 0.
Answer:
ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;
8) In the payments table, ensure that the combination of user_id and transaction_id is unique using an ALTER TABLE statement.
Answer:
ALTER TABLE payments
ADD CONSTRAINT unique_user_transaction
UNIQUE (user_id, transaction_id);
Top comments (0)