MySQL Assignment – 4

Basic Questions

  1. Create a table orders with fields: order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2), order_date DATE.
  2. Insert 5 sample orders in one SQL statement.
  3. Lock the orders table for write operations.
  4. Unlock the orders table.
  5. Create a stored procedure get_all_customers() to return all rows from customers.
  6. Create a stored procedure get_customer_by_id(IN cust_id INT) that returns customer details.
  7. Write a stored procedure increase_salary(IN emp_id INT, IN bonus DECIMAL(10,2)) that updates salary.
  8. Declare a local variable inside a stored procedure and set its value.
  9. Create a function get_fullname(fname VARCHAR(50), lname VARCHAR(50)) that concatenates names.
  10. Create a function square_num(num INT) that returns the square.
  11. Create a trigger before_student_update that sets last_modified = NOW() before updating students.
  12. Drop a trigger before_student_update.
  13. Create a new user report_user with password report123.
  14. Grant SELECT on all tables of salesdb to report_user.
  15. Revoke INSERT privilege on salesdb.orders from report_user.
  16. Insert 3 rows into products inside a transaction and commit.
  17. Insert a row into customers inside a transaction and rollback.
  18. Create a savepoint sp1 after inserting a row in payments, then rollback to sp1.
  19. Create an index on employees.salary.
  20. Drop the index on employees.salary.

Intermediate Questions

  1. Simulate two transactions locking the same row in orders to show lock contention.
  2. Write a stored procedure bulk_insert_orders(IN cust_id INT) that inserts 10 dummy orders for the same customer using a loop.
  3. Write a stored procedure add_order(IN cust_id INT, IN amt DECIMAL(10,2)) that inserts into orders.
  4. Alter procedure add_order to also accept order_date.
  5. Drop the procedure add_order.
  6. Create a function total_order_amount(cust INT) returning total spent by a customer.
  7. Create a function calculate_age(dob DATE) returning exact age in years.
  8. Create a trigger prevent_delete_employee that stops deletion from employees.
  9. Create a trigger log_insert_order that copies inserted rows into orders_audit.
  10. List all triggers in the current database.
  11. Create a role readonly_role and grant it SELECT on all tables.
  12. Grant readonly_role to report_user.
  13. Drop role readonly_role.
  14. Create a covering index on customers(city, name, email).
  15. Run a transaction inserting into orders, updating customers, and rollback if any error occurs.
  16. Create a transaction inserting into payments and commit if all succeed.
  17. Write SQL to show query execution plan for SELECT * FROM orders WHERE amount > 5000.
  18. Create an index on orders(order_date) and run a query using it.
  19. Export table orders using mysqldump.
  20. Import table orders into another database using mysql CLI.

Advanced Questions

  1. Simulate a deadlock with two sessions updating different rows in reverse order.
  2. Write a stored procedure transfer_amount(IN from_id INT, IN to_id INT, IN amt DECIMAL(10,2)) with error handling using DECLARE HANDLER.
  3. Write a procedure safe_transaction() that inserts into two tables and rolls back on error.
  4. Create a function nth_highest_salary(n INT) using a subquery with LIMIT and OFFSET.
  5. Create a function validate_password(pwd VARCHAR(100)) returning 1 if length > 8 and contains @, else 0.
  6. Create a trigger before_insert_salary that prevents salary < 10000.
  7. Create a trigger after_update_salary that logs old vs new salary into salary_history.
  8. Create roles admin_role, manager_role, clerk_role with different privileges and assign to users.
  9. Optimize a query joining employees, departments, and projects using proper indexes.
  10. Write a shell script that runs mysqldump every day and saves file with timestamp in filename.