Online Quiz Attempt – Play & Test Your Knowledge

Question 1

1 / 10

Which of the following is the correct syntax to create a stored function that determines if a given string is a palindrome in MySQL?

CREATE FUNCTION isPalindrome(str VARCHAR(255)) RETURNS BOOLEAN DETERMINISTIC BEGIN RETURN (str = REVERSE(str)); END; CREATE PROCEDURE isPalindrome(IN str VARCHAR(255)) RETURNS BOOLEAN DETERMINISTIC BEGIN RETURN (str = REVERSE(str)); END; CREATE FUNCTION isPalindrome(IN str VARCHAR(255)) RETURNS BOOLEAN DETERMINISTIC BEGIN DECLARE reversed_str VARCHAR(255); SET reversed_str = REVERSE(str); RETURN (str = reversed_str); END; CREATE FUNCTION isPalindrome(str VARCHAR(255)) RETURNS BOOLEAN DETERMINISTIC BEGIN DECLARE reversed_str VARCHAR(255); SET reversed_str = REVERSE(str); IF str = reversed_str THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END;

Question 2

2 / 10

Which isolation level in MySQL guarantees that a transaction sees only committed data and prevents non-repeatable reads, phantom reads, and dirty reads, but may still suffer from lost updates?

READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE

Question 3

3 / 10

Consider a table 'employees' with columns 'id' (INT, PRIMARY KEY), 'name' (VARCHAR), and 'salary' (DECIMAL). Which of the following queries is the most efficient way to find the second highest salary?

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1,1; SELECT salary FROM employees ORDER BY salary DESC LIMIT 2; HAVING COUNT(*) = 2;

Question 4

4 / 10

Which MySQL feature allows you to automatically execute a predefined SQL statement after a specific event (e.g., INSERT, UPDATE, DELETE) occurs on a table?

Scheduled Events Stored Procedures Triggers Views

Question 5

5 / 10

What is the primary benefit of using Partitioning in MySQL with respect to query performance, specifically on large tables?

It automatically creates indexes on partition keys. It allows queries to only scan relevant partitions based on the partition key, significantly reducing the amount of data read. It automatically distributes data across multiple physical servers. It compresses the data within each partition, reducing storage space.

Question 6

6 / 10

What is the purpose of the `SQL_CALC_FOUND_ROWS` option in a `SELECT` statement, and how is it used with `FOUND_ROWS()`?

`SQL_CALC_FOUND_ROWS` caches the result set, and `FOUND_ROWS()` retrieves it faster. `SQL_CALC_FOUND_ROWS` is deprecated; `FOUND_ROWS()` calculates the number of rows in the last query. `SQL_CALC_FOUND_ROWS` calculates how many rows the query would have returned without the LIMIT clause, and `FOUND_ROWS()` returns that number. `SQL_CALC_FOUND_ROWS` automatically optimizes the query, and `FOUND_ROWS()` reports the execution time.

Question 7

7 / 10

When using `XA` transactions in MySQL, which of the following statements is used to prepare a transaction for a two-phase commit?

XA START XA BEGIN XA PREPARE XA COMMIT

Question 8

8 / 10

Which of the following is the correct way to declare a cursor in a stored procedure in MySQL that allows updates to the data it retrieves?

DECLARE cursor_name CURSOR FOR SELECT ... FOR UPDATE DECLARE cursor_name CURSOR READ ONLY FOR SELECT ... DECLARE cursor_name CURSOR SCROLL FOR SELECT ... DECLARE cursor_name CURSOR FOR SELECT ... WITH LOCK IN SHARE MODE

Question 9

9 / 10

What is the role of the `innodb_flush_log_at_trx_commit` variable in MySQL's InnoDB storage engine?

It controls how frequently the transaction log is flushed to disk, impacting durability and performance. It determines the maximum size of the transaction log files. It enables or disables automatic transaction log rotation. It specifies the directory where transaction log files are stored.

Question 10

10 / 10

How does the `EXISTS` operator differ from the `IN` operator when used in a subquery regarding performance in MySQL, especially with large datasets?

`EXISTS` always performs better because it uses indexes more effectively. `IN` always performs better because it avoids scanning the entire subquery result set. `EXISTS` stops scanning the subquery's rows once a match is found, while `IN` typically materializes the entire subquery result set before the comparison. There is no significant performance difference between `EXISTS` and `IN`.

Database Master Quiz (SQL Edition)