MySQL Quiz - MCQ - Multiple Choice Questions with Answers

MySQL is an open-source relational database system, which means it uses a structured method to organize data into tables that can be related to each other.

Dive into the world of MySQL with this beginner's quiz! Test your knowledge of the fundamental concepts and check your understanding of this popular relational database system. Each question is accompanied by an answer and a brief explanation, so you can learn as you go!

1. Which SQL command is used to retrieve data from a database?

A. INSERT

B. DELETE

C. SELECT

D. UPDATE

Answer:

C. SELECT

Explanation:

The SELECT command is used to retrieve data from one or more tables in a database.

2. Which SQL statement is used to remove a table from the database?

A. DELETE TABLE

B. DROP TABLE

C. REMOVE TABLE

D. CLEAR TABLE

Answer:

B. DROP TABLE

Explanation:

The DROP TABLE statement is used to delete an existing table from the database.

3. Which keyword is used to sort the result set in ascending order?

A. SORT

B. ORDER

C. ORDER BY

D. SORT BY

Answer:

C. ORDER BY

Explanation:

The ORDER BY keyword is used in SQL to sort the result set in ascending or descending order.

4. How can you fetch unique values from a column?

A. UNIQUE

B. DISTINCT

C. SINGLE

D. UNO

Answer:

B. DISTINCT

Explanation:

The DISTINCT keyword is used to fetch unique values from a column in a table.

5. Which of the following SQL commands will delete all records from a table named customers but retain the table structure?

A. DROP TABLE customers;

B. TRUNCATE TABLE customers;

C. DELETE TABLE customers;

D. REMOVE ALL FROM customers;

Answer:

B. TRUNCATE TABLE customers;

Explanation:

The TRUNCATE TABLE command removes all records from a table but retains its structure for future use.

6. In which SQL command would you use the WHERE clause?

A. INSERT

B. UPDATE

C. CREATE

D. ALTER

Answer:

B. UPDATE

Explanation:

The WHERE clause is commonly used in UPDATE (and SELECT and DELETE) to filter records based on a condition.

7. What is the default sort order of the ORDER BY clause?

A. DESC

B. ASC

C. RANDOM

D. NONE

Answer:

B. ASC

Explanation:

By default, the ORDER BY clause sorts the records in ascending order.

8. Which SQL command is used to add a new column to an existing table?

A. ADD COLUMN

B. NEW COLUMN

C. INSERT COLUMN

D. ALTER TABLE

Answer:

D. ALTER TABLE

Explanation:

The ALTER TABLE command is used to add, delete, or modify columns in an existing table.

9. Which data type is used to store text values in MySQL?

A. CHAR

B. INT

C. FLOAT

D. BOOL

Answer:

A. CHAR

Explanation:

In MySQL, the CHAR and VARCHAR data types are used to store text values.

10. Which keyword is used to join two tables in SQL?

A. CONNECT

B. LINK

C. JOIN

D. COMBINE

Answer:

C. JOIN

Explanation:

The JOIN keyword is used in SQL to combine rows from two or more tables based on a related column.

11. Which MySQL function returns the current date and time?

A. NOW()

B. DATE()

C. TODAY()

D. TIMESTAMP()

Answer:

A. NOW()

Explanation:

The NOW() function returns the current date and time.

12. What does SQL stand for?

A. Structured Quality Language

B. Simple Query Logic

C. Structured Query Language

D. System Query Language

Answer:

C. Structured Query Language

Explanation:

SQL stands for Structured Query Language, used to communicate with databases.

13. Which command is used to create a new database in MySQL?

A. CREATE NEW DATABASE

B. ADD DATABASE

C. NEW DATABASE

D. CREATE DATABASE

Answer:

D. CREATE DATABASE

Explanation:

The CREATE DATABASE command is used to create a new database.

14. Which operator is used to compare if two values are not equal?

A. !=

B. <>

C. ><

D. Both A and B

Answer:

D. Both A and B

Explanation:

In SQL, both != and <> operators are used to indicate not equal.

15. Which MySQL data type is used to store large blocks of text?

A. TEXT

B. CHAR

C. BIGCHAR

D. LARGETEXT

Answer:

A. TEXT

Explanation:

The TEXT data type in MySQL is used to store large blocks of text.

16. Which SQL clause is used to filter the results returned by a query?

A. SORT BY

B. FILTER BY

C. WHERE

D. HAVING

Answer:

C. WHERE

Explanation:

The WHERE clause is used to filter query results based on specified conditions.

17. Which command is used to back up a MySQL database?

A. BACKUP DATABASE

B. SAVE DATABASE

C. mysqldump

D. mysqlsave

Answer:

C. mysqldump

Explanation:

mysqldump is a command-line utility to take backups of MySQL databases.

18. Which SQL statement is used to insert a new record in a table?

A. ADD RECORD

B. INSERT NEW

C. INSERT INTO

D. ADD INTO

Answer:

C. INSERT INTO

Explanation:

The INSERT INTO statement is used to add a new record to a table.

19. What type of JOIN returns only rows when there is at least one match in both tables?

A. LEFT JOIN

B. RIGHT JOIN

C. INNER JOIN

D. FULL JOIN

Answer:

C. INNER JOIN

Explanation:

An INNER JOIN fetches rows when there is at least one match in both tables.

20. Which MySQL function can be used to return the length of a string?

A. LENGTH()

B. SIZE()

C. STRLEN()

D. COUNT()

Answer:

A. LENGTH()

Explanation:

The LENGTH() function is used to obtain the length of a string in MySQL.

21. Which keyword can be used to specify a unique constraint for a table column?

A. UNIQUE

B. DISTINCT

C. SINGLE

D. RARE

Answer:

A. UNIQUE

Explanation:

The UNIQUE keyword ensures that all values in a column are distinct.

22. In MySQL, which command is used to return the version of the MySQL server?

A. SELECT VERSION();

B. GET VERSION();

C. SELECT MYSQLVERSION();

D. SELECT SERVER();

Answer:

A. SELECT VERSION();

Explanation:

SELECT VERSION(); returns the version of the MySQL server.

23. Which SQL statement is used to update data in a database?

A. REFRESH

B. MODIFY

C. REPLACE

D. UPDATE

Answer:

D. UPDATE

Explanation:

The UPDATE statement is used to modify existing records in a table.

24. Which of the following is not a valid SQL data type?

A. FLOAT

B. CHAR

C. SINGLE

D. DECIMAL

Answer:

C. SINGLE

Explanation:

SINGLE is not a valid SQL data type.

25. Which clause is used with the GROUP BY clause to filter grouped results in SQL?

A. WHERE

B. ORDER BY

C. HAVING

D. LIKE

Answer:

C. HAVING

Explanation:

The HAVING clause is used to filter results after data has been grouped with the GROUP BY clause.

26. Which of the following is used to make a column's value automatically increase for each new record?

A. AUTOINCREMENT

B. AUTOADD

C. INCREMENTAL

D. ADDAUTO

Answer:

A. AUTOINCREMENT

Explanation:

The AUTOINCREMENT attribute automatically increases the value of the column for each new record.

27. How do you select all columns from a table named "students"?

A. SELECT * INTO students;

B. SELECT students.*;

C. SELECT ALL FROM students;

D. SELECT * FROM students;

Answer:

D. SELECT * FROM students;

Explanation:

The SELECT * FROM statement retrieves all columns from the specified table.

28. What would be the result of the following SQL query: SELECT UPPER('mysql');?

A. mysql

B. MYSQL

C. UPPER

D. None of the above

Answer:

B. MYSQL

Explanation:

The UPPER() function converts all characters of a string to uppercase.

29. Which SQL function is used to round a number?

A. ROUND

B. RND

C. CIRCLE

D. LOOP

Answer:

A. ROUND

Explanation:

The ROUND() function is used to round a number to the nearest whole number or to a specified number of decimals.

30. Which of the following is not an SQL aggregate function?

A. COUNT()

B. MAX()

C. CONCAT()

D. SUM()

Answer:

C. CONCAT()

Explanation:

CONCAT() is a string function used to concatenate two or more strings, whereas the others are aggregate functions.

31. Which command is used to remove a specific row from a table?

A. REMOVE

B. DROP

C. DELETE

D. DESTROY

Answer:

C. DELETE

Explanation:

The DELETE statement is used to remove specific rows from a table based on a condition.

32. Which keyword defines a primary key constraint on a table in MySQL?

A. PRIMARY

B. MAIN

C. UNIQUE

D. PRIMARY KEY

Answer:

D. PRIMARY KEY

Explanation:

The PRIMARY KEY constraint uniquely identifies each record in a table.

33. Which keyword would you use to rename a table in MySQL?

A. RENAME

B. RENAME TABLE

C. ALTER

D. CHANGE TABLE

Answer:

B. RENAME TABLE

Explanation:

The RENAME TABLE statement is used to rename an existing table.

34. Which SQL statement is used to access data from multiple tables based on a relationship between certain columns?

A. COMBINE

B. UNION

C. JOIN

D. MERGE

Answer:

C. JOIN

Explanation:

The JOIN statement is used to combine rows from two or more tables based on a related column.

35. What is the maximum length of a table name in MySQL?

A. 64 characters

B. 128 characters

C. 256 characters

D. 512 characters

Answer:

A. 64 characters

Explanation:

In MySQL, the maximum length for a table name is 64 characters.

36. Which MySQL function returns the smallest integer value not less than a number?

A. CEILING()

B. FLOOR()

C. ROUNDUP()

D. TOP()

Answer:

A. CEILING()

Explanation:

The CEILING() function returns the smallest integer value that is not less than the provided number.

37. Which SQL keyword is used to retrieve a maximum of 5 records from a table?

A. FETCH

B. TOP

C. LIMIT

D. GET

Answer:

C. LIMIT

Explanation:

The LIMIT keyword is used in SQL to specify the maximum number of records to return.

38. What is the SQL query for retrieving all records from a table named 'products' where the price is greater than 50?

A. SELECT * FROM products WHERE price > 50;

B. FETCH * FROM products WHEN price > 50;

C. GET * FROM products HAVING price > 50;

D. RECEIVE * FROM products WHERE price > 50;

Answer:

A. SELECT * FROM products WHERE price > 50;

Explanation:

The SELECT statement combined with the WHERE clause retrieves specific records based on a condition.

39. Which SQL clause is used to specify a condition while fetching data from a table?

A. DECISION

B. CHECK

C. WHERE

D. CONDITION

Answer:

C. WHERE

Explanation:

The WHERE clause is used to filter records and fetch only those that satisfy a specific condition.

40. What is the primary purpose of creating an index on a MySQL column?

A. To consume more disk space.

B. To slow down SELECT query performance.

C. To improve the speed of retrieval operations.

D. To enhance the look of the database.

Answer:

C. To improve the speed of retrieval operations.

Explanation:

Indexes are primarily used to enhance the speed of database retrieval operations.

41. Which of the following tools can be used to analyze query performance in MySQL?

A. EXPLAIN

B. DEBUG

C. TRACE

D. LISTEN

Answer:

A. EXPLAIN

Explanation:

The EXPLAIN keyword provides insights into how MySQL will execute a query, helping to identify potential bottlenecks.

42. Which storage engine in MySQL supports transactions?

A. MEMORY

B. MERGE

C. MyISAM

D. InnoDB

Answer:

D. InnoDB

Explanation:

InnoDB is a storage engine in MySQL that supports transactions, row-level locking, and foreign keys.