ALTER TABLE

 ALTER TABLE constraints

QN: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.

Query:

ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;


since, email should not be empty for future records.. we use SET NOT NULL

QN:2 

In the users table, ensure that the username column is unique across all records using an ALTER statement.

Query:

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);


since, username should not repeat.. we add UNIQUE constraint

QN:3 

In the products table, enforce that price must always be greater than 0 using an ALTER command.

Query:

ALTER TABLE products
ADD CONSTRAINT check_price CHECK (price > 0);


since, price should always be greater than 0.. we use CHECK constraint

QN:4

 Modify the orders table so that the status column defaults to 'pending' if no value is provided during insertion.

Query:

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';


since, if no value is given.. status should automatically become pending

QN: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

Query:

ALTER TABLE employees
ADD COLUMN salary INT NOT NULL CHECK (salary > 10000);


since, salary should not be empty and must be above 10000.. we use NOT NULL and CHECK

QN:6 

Modify the foreign key constraint between employees and departments so that when a department is deleted, all related employees are automatically removed.

Query:

ALTER TABLE employees
DROP CONSTRAINT employees_department_id_fkey;

ALTER TABLE employees
ADD CONSTRAINT employees_department_id_fkey
FOREIGN KEY (department_id) REFERENCES departments(id)
ON DELETE CASCADE;


since, existing foreign key cannot be modified directly.. we drop and recreate with CASCADE

QN:7 

In the accounts table, remove an existing CHECK constraint that enforces balance >= 0.

Query:

ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;


since, we want to remove condition on balance.. we drop CHECK constraint

QN:8

 In the payments table, ensure that the combination of user_id and transaction_id is unique using an ALTER TABLE statement.

Query:

ALTER TABLE payments
ADD CONSTRAINT unique_payment UNIQUE (user_id, transaction_id);


since, same user should not have duplicate transaction_id.. we use combination UNIQUE

Comments

Post a Comment

Popular posts from this blog

THE DELIVERY MAN

  The Delivery Man – Python List Operations  Introduction Lists in Python are used to store multiple items in a single variable. They are flexible, allowing us to add, remove, update, and manipulate elements easily. In this task, different list operations are performed to simulate a delivery man's workflow. 1. Creating a list and accessing an item items = ["Notebook", "Pencil", "Eraser", "Ruler", "Marker"] print(items[2]) Approach: Indexing Why: Lists use zero-based indexing, so the third item is accessed using index 2. 2. Adding a new item items.append("Glue Stick") print(items) Approach: append() method Why: It adds an item at the end of the list, which is ideal for adding new deliveries. 3. Inserting an item items.insert(2, "Highlighter") print(items) Approach: insert() method Why: Used when we need to add an item at a specific position instead of the end. 4. Removing an item items.remove("Ruler...
Read more

EC2 LAUNCHING

  EC2 Step 1: Launch EC2 Instance Go to: AWS Console --> EC2 --> Launch Instance Configure: Name : my-web-server AMI : Amazon Linux 2 (easy for beginners) Instance Type : t2.micro (free tier) Step 2: Key Pair Create or select a key pair. Step 3: Network Settings  Allow these: SSH (port 22) --> for login  HTTP (port 80) --> for web access Step 4: Launch Instance Click Launch Instance Step 5: Connect to Instance Use: EC2 --> Connect --> EC2 Instance Connect (easiest) OR via terminal: with this command. for this you have to download the pem files while selecting the key pair.  ssh -i your-key.pem ec2-user@<public-ip> Step 6: Install Web Server For Amazon Linux: sudo yum update -y sudo yum install httpd -y Step 7: Start Web Server sudo systemctl start httpd sudo systemctl enable httpd Step 8: Add a Web Page echo "Hello from EC2" | sudo tee /var/www/html/index.html Step 9: Get Public IP Go to EC2 d...
Read more

SORT A LINKED LIST USING MERGE SORT

  Sort a Linked List using Merge Sort Introduction Sorting a linked list efficiently is a common problem in data structures. Unlike arrays, linked lists do not allow direct access to elements, so algorithms like quicksort are not ideal. Merge Sort is the best choice for linked lists because: It does not require random access It works efficiently with node-based structures Problem Statement Given the head of a linked list: Sort the list in ascending order Return the sorted linked list Example Input: 10 -> 30 -> 20 -> 40 -> 60 -> 50 Output: 10 -> 20 -> 30 -> 40 -> 50 -> 60 Solution Code class Solution: def mergeSort(self, head): if not head or not head.next: return head mid = self.getMiddle(head) next_to_mid = mid.next mid.next = None left = self.mergeSort(head) right = self.mergeSort(next_to_mid) return self.merge(left, right) def getMiddle(self,...
Read more