MySQL Certification Practice Test

1. Which statement creates a new database in MySQL?

CREATE DATABASE dbname;

NEW DATABASE dbname;

MAKE DATABASE dbname;

INIT DATABASE dbname;

2. What does the PRIMARY KEY constraint do?

Allows duplicate values

Uniquely identifies each row and prevents NULL

Sorts data automatically

Encrypts sensitive data

3. Which JOIN returns rows with matches in both tables?

LEFT JOIN

INNER JOIN

FULL OUTER JOIN

CROSS JOIN

4. What is the purpose of an INDEX in MySQL?

Store backup copies of data

Speed up query execution and WHERE clauses

Encrypt table contents

Automatically delete old records

5. Which statement removes all rows from a table without logging?

DELETE FROM table;

TRUNCATE TABLE table;

DROP TABLE table;

REMOVE TABLE table;

6. What does ACID stand for in database transactions?

Atomicity, Consistency, Isolation, Durability

Accuracy, Concurrency, Integration, Data

Application, Coherence, Index, Distribution

Aggregation, Calculation, Integrity, Design

7. Which clause filters grouped data in SQL?

WHERE

HAVING

FILTER

GROUP

8. What is a FOREIGN KEY?

A key that encrypts data between servers

A column referencing PRIMARY KEY in another table

A temporary key for backup operations

A key used only for read-only access

9. Which statement displays the structure of a table?

SHOW TABLE table_name;

DESCRIBE table_name;

STRUCTURE table_name;

DISPLAY table_name;

10. What does normalization do in database design?

Increases query speed automatically

Reduces data redundancy and improves integrity

Encrypts all table data

Deletes unused records

11. Which command grants permissions to a user?

PERMIT USER

ALLOW USER

GRANT

AUTHORIZE

12. What is the default storage engine in modern MySQL?

MyISAM

InnoDB

Memory

13. Which function returns the number of rows affected by a query?

COUNT()

Mysql_num_rows()

ROW_COUNT()

AFFECTED_ROWS()

14. What does the DISTINCT keyword do?

Removes duplicate rows from results

Sorts results in ascending order

Limits output to first 10 rows

Groups results by a column

15. Which backup method locks the database during backup?

Logical backup with mysqldump

Cold backup (offline backup)

Binary log backup

Incremental backup