DATA STRUCTURE TASKS

  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 s...

Tables with constraints QN:1  Create a table called students where each student has an id, name, and age. Ensure that the id uniquely identifies each student. Query: CREATE TABLE students ( id SERIAL PRIMARY KEY, name TEXT, age INT ); since, id should uniquely identify each student.. we use PRIMARY KEY QN:2  Create a table employees where name and email cannot be empty, but phone_number can be optional. Query: CREATE TABLE employees ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, phone_number TEXT ); since, name and email should not be empty.. we use NOT NULL.. phone is optional so no constraint QN:3  Create a table users where both username and email must be unique across all records. Query: CREATE TABLE users ( id SERIAL PRIMARY KEY, username TEXT UNIQUE, email TEXT UNIQUE ); since, username and email should not repeat.. we use UNIQUE QN:4  Create a table products where price must always be greater than 0 and stock cannot be negative. Query: CREAT...

Let me explain duplicate transaction (same request multiple times) First, we have accounts table Alice = 1000 Bob = 500 since, now we try same transfer again and again. First time transfer: BEGIN; UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice'; UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob'; COMMIT; After first transfer: Alice = 800 Bob = 700 since, 200 is transferred successfully. Now we run the same transfer again (duplicate request). BEGIN; UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice'; UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob'; COMMIT; After second time: Alice = 600 Bob = 900 since, same operation is applied again.. so money is transferred again. Final observation: since, database does not know it is duplicate request it will execute again and again so duplicate transactions are allowed Problem: since, same request runs multiple times money will be deducted multiple times this ...

Let me explain durability using accounts table First, we have accounts table Alice = 1000 Bob = 500 since, now we perform a normal money transfer. BEGIN; UPDATE accounts SET balance = balance - 300 WHERE name = 'Alice'; UPDATE accounts SET balance = balance + 300 WHERE name = 'Bob'; COMMIT; since, we deduct from Alice and add to Bob.. and commit the transaction. After transaction: Alice = 700 Bob = 800 since, changes are successfully saved. Now we simulate system restart (like closing and reopening database). Reconnect and check data: SELECT * FROM accounts; Result: Alice = 700 Bob = 800 since, even after restart.. data is still there. Final understanding: since, once COMMIT is done.. changes are permanently saved in database this is called durability Now think about failure cases If failure happens before COMMIT: since, transaction is not completed.. changes will not be saved so, data remains old If failure happens after COMMIT: since, commit is already done.. data wil...

Let me explain concurrent transactions using accounts table First, we have accounts table Alice = 1000 Bob = 500 since, now we try to do multiple transactions at same time from same account. Now we open 2 sessions (2 query windows). In Session 1: BEGIN; UPDATE accounts SET balance = balance - 800 WHERE name = 'Alice';  not committing since, we deducted 800 from Alice.. but not committed yet.. so change is not final. Now in Session 2: BEGIN; SELECT * FROM accounts WHERE name = 'Alice'; since, we are checking balance in another session. Result: Alice = 1000 since, Session 2 cannot see uncommitted changes from Session 1.. so it still shows old value. Now in Session 2 we try to deduct again: UPDATE accounts SET balance = balance - 500 WHERE name = 'Alice'; since, system will try to update same row. Result: it will wait (lock) since, Session 1 is already using that row.. so Session 2 cannot update until Session 1 completes. Now go back to Session 1 and do: COMMIT; si...

Let me explain how database maintains valid data using accounts table First, we already have accounts table with a rule: balance >= 0 since, this is a constraint.. database will not allow negative balance. Initial data: Alice = 1000 Bob = 500 Now we try to break the rule by deducting more money than available. UPDATE accounts SET balance = balance - 2000 WHERE name = 'Alice'; since, Alice has only 1000.. after deduction it becomes -1000.. which is not allowed. Result: ERROR will come since, CHECK (balance >= 0) constraint fails.. so database stops the query. Now we try direct update to negative value. UPDATE accounts SET balance = -500 WHERE name = 'Bob'; since, we are directly setting negative value. Result: ERROR will come since, again constraint is violated.. so update will not happen. Now we check data again: SELECT * FROM accounts; Alice = 1000 Bob = 500 since, invalid operations are rejected.. so data remains unchanged. Now we try inside transaction. BEGIN; ...

Let me explain the transaction working using accounts table First, we have an accounts table with balance for each user. Alice = 1000 Bob = 500 since, we are doing money transfer.. we need to deduct from one and add to another. Now we start transaction to transfer 200 from Alice to Bob. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice'; UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob'; COMMIT; since, both debit and credit should happen together.. so we use transaction. After successful transaction: Alice = 800 Bob = 700 since, 200 is deducted from Alice and added to Bob. Now we try with an error in between. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';  error happens here UPDATE accounts SET balance = balance + 2000o WHERE name = 'Bob'; ROLLBACK; since, second query has error.. so transaction fails. After this failed transaction: Alice = 1000 Bob = 500 since, even though money was deducted fi...

Let me explain it with some "Roles and Permissions" qns QN: 1 Create a login role report_user that can only read from film table. ANS: CREATE ROLE report_user LOGIN; GRANT SELECT ON film TO report_user; since, we are creating a new user called report_user.. and giving only SELECT access to film table.. so it can only read data. QN: 2 Try to access customer table and fix permission denied. ANS: GRANT SELECT ON customer TO report_user; since, report_user does not have access to customer table.. so it gives error.. so we give SELECT permission to fix it. QN: 3 Allow report_user to see only some columns in customer table. ANS: REVOKE SELECT ON customer FROM report_user; GRANT SELECT (customer_id, first_name, last_name) ON customer TO report_user; since, we remove full access first.. then give access only to specific columns. QN: 4 Create support_user with limited permissions. ANS: CREATE ROLE support_user LOGIN; GRANT SELECT ON customer TO support_user; GRANT UPDATE (email) ON cu...

Let me explain it with some "DVD Rental DataBase" qns QN: 1 Get all movies with rental rate greater than 3. ANS: SELECT * FROM film WHERE rental_rate > 3; since, it is to select all columns from film table.. and condition is rental_rate greater than 3. QN: 2 Movies with rental rate > 3 and replacement cost < 20. ANS: SELECT * FROM film WHERE rental_rate > 3 AND replacement_cost < 20; since, two conditions are given.. so we use AND to combine both. QN: 3 Movies rated PG or rental rate 0.99. ANS: SELECT * FROM film WHERE rating = 'PG' OR rental_rate = 0.99; since, either one condition can be true.. so we use OR. QN: 4 First 10 movies sorted by rental rate (highest first). ANS: SELECT * FROM film ORDER BY rental_rate DESC LIMIT 10; since, we sort rental_rate in descending.. and take first 10. QN: 5 Skip first 5 and get next 3 (ascending). ANS: SELECT * FROM film ORDER BY rental_rate ASC OFFSET 5 LIMIT 3; since, OFFSET skips first 5 rows.. then LIMIT gives ...

Let me explain it with some "DVD Rental DB" qns QN: 1 Retrieve film titles and their rental rates. ANS: SELECT title AS "Movie Title", rental_rate AS "Rate" FROM film; since, it is to select title and rental_rate from table film.. and we are renaming them as Movie Title and Rate using alias. QN: 2 List customer names and their email addresses. ANS: SELECT first_name AS "First Name", last_name AS "Last Name", email FROM customer; since, we need first name, last name and email from customer table.. and we rename first_name and last_name. QN: 3 Sort films by rental rate descending and title. ANS: SELECT title, rental_rate FROM film ORDER BY rental_rate DESC, title ASC; since, it is to sort rental_rate in descending.. if same means then title will be sorted alphabetically. QN: 4 Actor names sorted by last name then first name. ANS: SELECT first_name, last_name FROM actor ORDER BY last_name, first_name; since, sorting is done first by last_n...

Let me explain it with some "Hackerank" qns QN: 1 Query all columns for a city in  CITY  with the  ID   1661 ANS:  SELECT * from CITY where ID = 1661 ; since, it is to select all columns from table city.. and the condition is for ID = 1661. QN: 2 Query all attributes of every Japanese city in the  CITY  table. The  COUNTRYCODE  for Japan is  JPN . ANS:  SELECT * from CITY where COUNTRYCODE = 'JPN' ; since, it is to select all attributes from the table city.. and the condition is for a COUNTRYCODE =JPN QN:3 Query the  NAME  field for all American cities in the  CITY  table with populations larger than  120000 . The  CountryCode  for America is  USA . ANS:  SELECT NAME from CITY where POPULATION > 120000 and COUNTRYCODE = 'USA' ; since it is to select all the names from the table city , condition is population is more than 120000 and CountryCode =USA QN:4 Query the list ...

  DNS  Step 1: Go to Route 53 Open AWS Console Search -->  Route 53 Click Hosted Zones  Step 2: Create Hosted Zone Click: Create Hosted Zone Fill: Domain Name : yourdomain.com (can be real or dummy) Type :       Public Hosted Zone (for internet access) Click: Create Hosted Zone Step 3: Understand Default Records You’ll see 2 important records:  NS Record (Name Servers) List of AWS DNS servers Used to route domain traffic to AWS SOA Record Contains admin + config info Step 4: Create DNS Record (A Record) Click: Create Record Fill: Record Name : (optional) www Record Type : A Value : <your EC2 public IP> Click: Create Record Step 5: Link Domain If you bought domain from: GoDaddy / Namecheap etc. You must:  Replace their nameservers with Route 53 NS values Example: ns-123.awsdns-45.com ns-678.awsdns-12.net Step 6: Test in Browser http://www.yourdomain.com It will open your...

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