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?

A

CREATE FUNCTION isPalindrome(str VARCHAR(255)) RETURNS BOOLEAN DETERMINISTIC BEGIN RETURN (str = REVERSE(str)); END;

B

CREATE PROCEDURE isPalindrome(IN str VARCHAR(255)) RETURNS BOOLEAN DETERMINISTIC BEGIN RETURN (str = REVERSE(str)); END;

C

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;

D

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?

A

READ UNCOMMITTED

B

READ COMMITTED

C

REPEATABLE READ

D

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?

A

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

B

SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

C

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1,1;

D

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?

A

Scheduled Events

B

Stored Procedures

C

Triggers

D

Views

Question 5

5 / 10

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

A

It automatically creates indexes on partition keys.

B

It allows queries to only scan relevant partitions based on the partition key, significantly reducing the amount of data read.

C

It automatically distributes data across multiple physical servers.

D

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()`?

A

`SQL_CALC_FOUND_ROWS` caches the result set, and `FOUND_ROWS()` retrieves it faster.

B

`SQL_CALC_FOUND_ROWS` is deprecated; `FOUND_ROWS()` calculates the number of rows in the last query.

C

`SQL_CALC_FOUND_ROWS` calculates how many rows the query would have returned without the LIMIT clause, and `FOUND_ROWS()` returns that number.

D

`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?

A

XA START

B

XA BEGIN

C

XA PREPARE

D

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?

A

DECLARE cursor_name CURSOR FOR SELECT ... FOR UPDATE

B

DECLARE cursor_name CURSOR READ ONLY FOR SELECT ...

C

DECLARE cursor_name CURSOR SCROLL FOR SELECT ...

D

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?

A

It controls how frequently the transaction log is flushed to disk, impacting durability and performance.

B

It determines the maximum size of the transaction log files.

C

It enables or disables automatic transaction log rotation.

D

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?

A

`EXISTS` always performs better because it uses indexes more effectively.

B

`IN` always performs better because it avoids scanning the entire subquery result set.

C

`EXISTS` stops scanning the subquery's rows once a match is found, while `IN` typically materializes the entire subquery result set before the comparison.

D

There is no significant performance difference between `EXISTS` and `IN`.

Database Master Quiz (SQL Edition)