MySQL mcq questions and answers | MySQL MCQs Page-2 section-7

Section 1 Section 2 Section 3 Section 4 Section 5 Section 6 Section 7 Section 8 Section 9 Section 10 Section 11 Section 12

11.
For InnoDB tables in mysqldump an online backup that takes no locks on tables can be performed by . . . . . . . .

A. -multiple-transaction

B. -single-transaction

C. -double-transaction

D. -no-transaction

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

This question is about taking a backup of your MySQL database without disrupting the normal operations. We want to ensure that the backup process doesn't interfere with users accessing or modifying data.

InnoDB is a storage engine used in MySQL. It handles transactions, which are essentially a series of operations that are treated as a single unit.

mysqldump is a command-line utility used for creating backups of your MySQL database. It generates a script that can be used to recreate the database later.

The options provided are arguments that can be used with mysqldump:
    -single-transaction: This option is commonly used for creating backups. It ensures that all the data changes in a single transaction are captured consistently. It takes a short-term lock during the backup process, but it's usually a minimal disruption.
    -multiple-transaction: This option is designed for taking backups of large databases. It breaks the backup process into multiple transactions, allowing users to continue accessing the database while the backup is in progress. However, it's not considered a "no-lock" backup as it still takes locks on the table during individual transactions.
    -double-transaction: This option is not a standard option for mysqldump. It's not related to backup procedures.
    -no-transaction: This option is not typically used for taking backups. It avoids using transactions during the backup, potentially leading to inconsistencies in the data if other processes are modifying the database concurrently.

The answer to the question is: None of the above options will provide a truly "no-lock" backup. While -multiple-transaction might seem like a suitable option, it doesn't guarantee a completely lock-free process.

To achieve a truly lock-free backup with InnoDB tables, you would need to use techniques like:
    Logical replication: This involves setting up a replication system where changes to the primary database are replicated to a secondary instance. You can then take a backup of the secondary instance without impacting the primary database.
    Percona XtraBackup: This tool is specifically designed for taking online backups of InnoDB tables. It works at the file system level, minimizing locks on tables.

The question might be designed to test your understanding of the different backup methods and their limitations.

12.

abc in the following MySQL statement is . . . . . . . .

CREATE VIEW xyz (abc) AS SELECT a FROM t;

A. row name

B. column name

C. view

D. database

Answer & Solution Discuss in Board Save for Later

13.
Which statement terminates the execution of a function?

A. BEGIN...END

B. RETURN

C. ITERATE

D. LOOP

Answer & Solution Discuss in Board Save for Later

14.

How many storage engines among the following are transaction-safe?

InnoDB, Falcon, MyISAM, MEMORY

A. 1

B. 2

C. 3

D. 4

Answer & Solution Discuss in Board Save for Later

15.
What is the default format for "Time" data type?

A. HHH:MI:SS

B. SS:MI:HHH

C. MI:SS:HHH

D. None of the mentioned

Answer & Solution Discuss in Board Save for Later

16.

Consider a database name "db_name" whose attributes are intern_id (primary key), subject, subject_value.
Intern_id = {1, 2, 3, 4, 5, 6}
Subject = {sql, oop, sql, oop, c, c++}
Subject_value = {0, 0, 1, 1, 2, 2, 3, 3}
If these are one to one relation then what will be the output of the following MySQL statement?

SELECT intern_id
FROM db_name
WHERE subject IN (SELECT subject FROM db_name WHERE subject_value = 3);

A. {5, 6}

B. {1, 2, 3}

C. {3, 4}

D. None of the mentioned

Answer & Solution Discuss in Board Save for Later

17.
Which clause is mandatory with clause "SELECT" in Mysql?

A. FROM

B. WHERE

C. Both FROM and WHERE

D. None of the mentioned

Answer & Solution Discuss in Board Save for Later

18.
What represents an 'attribute' in a relational database?

A. Table

B. Row

C. Column

D. Object

Answer & Solution Discuss in Board Save for Later

19.
What is the maximum non zero values for DOUBLE?

A. ±1.7976931348623157E+307

B. ±1.7976931348623157E+308

C. ±1.7976931348623157E+306

D. ±1.7976931348623157E+305

Answer & Solution Discuss in Board Save for Later

20.

What will be the output of the following MySQL statement?

SELECT *
FROM employee
WHERE start_date>=’2007-01-01’ AND
        Start_date<=’2005-01-01’

A. All employees between 2007 and 2005

B. All employees from 2007 to 2005

C. Empty set

D. None of the mentioned

Answer & Solution Discuss in Board Save for Later

1 2 3 4 5 6 7 8 9 10

11.
For InnoDB tables in mysqldump an online backup that takes no locks on tables can be performed by . . . . . . . .

Answer & Solution

12.
abc in the following MySQL statement is . . . . . . . .

`CREATE VIEW xyz (abc) AS SELECT a FROM t;`

Answer & Solution

13.
Which statement terminates the execution of a function?

Answer & Solution

14.
How many storage engines among the following are transaction-safe?

`InnoDB, Falcon, MyISAM, MEMORY`

Answer & Solution

15.
What is the default format for "Time" data type?

Answer & Solution

Answer & Solution

17.
Which clause is mandatory with clause "SELECT" in Mysql?

Answer & Solution

18.
What represents an 'attribute' in a relational database?

Answer & Solution

19.
What is the maximum non zero values for DOUBLE?

Answer & Solution

20.
What will be the output of the following MySQL statement?

`SELECT * FROM employee WHERE start_date>=’2007-01-01’ AND Start_date<=’2005-01-01’`

Answer & Solution

Read More Section(MySQL Miscellaneous)

MySQL mcq questions and answers | MySQL MCQs

11. For InnoDB tables in mysqldump an online backup that takes no locks on tables can be performed by . . . . . . . .

Answer & Solution

12. abc in the following MySQL statement is . . . . . . . . CREATE VIEW xyz (abc) AS SELECT a FROM t;

Answer & Solution

13. Which statement terminates the execution of a function?

Answer & Solution

14. How many storage engines among the following are transaction-safe? InnoDB, Falcon, MyISAM, MEMORY

Answer & Solution

15. What is the default format for "Time" data type?

Answer & Solution

Answer & Solution

17. Which clause is mandatory with clause "SELECT" in Mysql?

Answer & Solution

18. What represents an 'attribute' in a relational database?

Answer & Solution

19. What is the maximum non zero values for DOUBLE?

Answer & Solution

20. What will be the output of the following MySQL statement? SELECT * FROM employee WHERE start_date>=’2007-01-01’ AND Start_date<=’2005-01-01’

Answer & Solution

Read More Section(MySQL Miscellaneous)

11.
For InnoDB tables in mysqldump an online backup that takes no locks on tables can be performed by . . . . . . . .

12.
abc in the following MySQL statement is . . . . . . . .

`CREATE VIEW xyz (abc) AS SELECT a FROM t;`

13.
Which statement terminates the execution of a function?

14.
How many storage engines among the following are transaction-safe?

`InnoDB, Falcon, MyISAM, MEMORY`

15.
What is the default format for "Time" data type?

17.
Which clause is mandatory with clause "SELECT" in Mysql?

18.
What represents an 'attribute' in a relational database?

19.
What is the maximum non zero values for DOUBLE?

20.
What will be the output of the following MySQL statement?

`SELECT * FROM employee WHERE start_date>=’2007-01-01’ AND Start_date<=’2005-01-01’`