12 Sep 100 MySQL MCQ (Multiple Choice Questions) with Answers

Posted at 10:19h in mcq by

1) Which of the following is an open-source Relational Database Management System (RDBMS)?
  1. MongoDB
  2. MySQL
  3. Cassandra
  4. Redis
Show Answer
Answer: b
Explanation
MySQL is an open-source relational database management system. MongoDB, Cassandra, and Redis are non-relational/NoSQL databases.

2) Which SQL command is used to retrieve data from a MySQL database?

  1. FETCH
  2. GET
  3. SELECT
  4. EXTRACT
Show Answer
Answer: c
Explanation
The SELECT statement is used to query and retrieve rows from one or more tables in MySQL.

3) Which data type is used to store variable-length character strings in MySQL?

  1. CHAR
  2. VARCHAR
  3. TEXT
  4. STRING
Show Answer
Answer: b
Explanation
VARCHAR stores variable-length strings. CHAR is fixed-length, while TEXT is for larger text values.

4) Which clause is used to filter rows based on a specified condition in a SELECT query?

  1. HAVING
  2. WHERE
  3. GROUP BY
  4. ORDER BY
Show Answer
Answer: b
Explanation
WHERE filters individual rows before grouping. HAVING filters grouped results after GROUP BY.

5) What is the default port number for MySQL connections?

  1. 1433
  2. 1521
  3. 3306
  4. 5432
Show Answer
Answer: c
Explanation
MySQL uses TCP port 3306 by default. 1433 is SQL Server, 1521 is Oracle, and 5432 is PostgreSQL.

6) Which SQL keyword is used to remove duplicate rows from a result set?

  1. UNIQUE
  2. DISTINCT
  3. DIFFERENT
  4. FILTER
Show Answer
Answer: b
Explanation
DISTINCT removes duplicate rows from the query result set.

7) Which command is used to add a new record into a MySQL table?

  1. ADD RECORD
  2. INSERT INTO
  3. UPDATE
  4. CREATE ROW
Show Answer
Answer: b
Explanation
INSERT INTO is used to add new rows to a table.

8) Which statement is used to modify existing records in a table?

  1. MODIFY
  2. ALTER
  3. UPDATE
  4. CHANGE
Show Answer
Answer: c
Explanation
UPDATE modifies existing row values. ALTER changes table structure, not row data.

9) Which command is used to delete all rows from a table without logging individual row deletions?

  1. DELETE
  2. REMOVE
  3. TRUNCATE
  4. DROP
Show Answer
Answer: c
Explanation
TRUNCATE removes all rows quickly and does not log individual row deletions like DELETE does.

10) Which command permanently deletes a table structure along with all its data?

  1. TRUNCATE TABLE
  2. DROP TABLE
  3. DELETE TABLE
  4. REMOVE TABLE
Show Answer
Answer: b
Explanation
DROP TABLE removes both the table structure and its data permanently.

11) Which SQL constraint ensures that all values in a column are unique?

  1. CHECK
  2. NOT NULL
  3. UNIQUE
  4. PRIMARY KEY
Show Answer
Answer: c
Explanation
UNIQUE ensures all values in a column are distinct. PRIMARY KEY also enforces uniqueness, but it is a stronger constraint.

12) A PRIMARY KEY constraint automatically implies which combination of constraints?

  1. UNIQUE and CHECK
  2. NOT NULL and DEFAULT
  3. UNIQUE and NOT NULL
  4. FOREIGN KEY and UNIQUE
Show Answer
Answer: c
Explanation
A PRIMARY KEY must be unique and cannot contain NULL values, so it implies UNIQUE and NOT NULL.

13) How many PRIMARY KEY constraints can a single table have?

  1. 0
  2. 1
  3. 2
  4. Unlimited
Show Answer
Answer: b
Explanation
A table can have only one PRIMARY KEY, though that key may consist of multiple columns.

14) Which SQL clause is used to sort the result set in ascending or descending order?

  1. SORT BY
  2. GROUP BY
  3. ORDER BY
  4. ALIGN BY
Show Answer
Answer: c
Explanation
ORDER BY sorts the result set. ASC is the default, and DESC sorts in reverse order.

15) What is the default sorting direction of the ORDER BY clause?

  1. DESC
  2. ASC
  3. RANDOM
  4. NONE
Show Answer
Answer: b
Explanation
ORDER BY defaults to ascending order (ASC) if no direction is specified.

16) Which wildcard character represents zero, one, or multiple characters in a LIKE query?

  1. _
  2. %
  3. *
  4. #
Show Answer
Answer: b
Explanation
The percent sign (%) matches zero, one, or many characters in a LIKE pattern.

17) Which wildcard character represents a single character in a LIKE query?

  1. %
  2. _
  3. ?
  4. $
Show Answer
Answer: b
Explanation
The underscore (_) matches exactly one character in a LIKE pattern.

18) Which aggregate function returns the total number of rows matching a criteria?

  1. SUM()
  2. TOTAL()
  3. COUNT()
  4. NUMBER()
Show Answer
Answer: c
Explanation
COUNT() returns the number of rows that match the query condition.

19) Which operator is used to search for a specified pattern in a column?

  1. IN
  2. LIKE
  3. BETWEEN
  4. EXISTS
Show Answer
Answer: b
Explanation
LIKE is used with wildcards such as % and _ to match string patterns.

20) Which clause is used to group rows that have the same values in specified columns?

  1. ORDER BY
  2. GROUP BY
  3. HAVING
  4. JOIN
Show Answer
Answer: b
Explanation
GROUP BY groups rows with the same values so aggregate functions can be applied to each group.

21) Which clause is used to filter groups created by the GROUP BY clause?

  1. WHERE
  2. HAVING
  3. FILTER
  4. LIKE
Show Answer
Answer: b
Explanation
HAVING filters groups after GROUP BY, while WHERE filters individual rows before grouping.

22) Which function returns the current date and time in MySQL?

  1. CURDATE()
  2. NOW()
  3. GETDATE()
  4. TODAY()
Show Answer
Answer: b
Explanation
NOW() returns the current date and time. CURDATE() returns only the current date.

23) Which JOIN returns all records from the left table and matched records from the right table?

  1. INNER JOIN
  2. RIGHT JOIN
  3. LEFT JOIN
  4. FULL JOIN
Show Answer
Answer: c
Explanation
LEFT JOIN returns all rows from the left table and matching rows from the right table, with NULLs where there is no match.

24) Which JOIN returns records that have matching values in both tables?

  1. LEFT JOIN
  2. RIGHT JOIN
  3. INNER JOIN
  4. CROSS JOIN
Show Answer
Answer: c
Explanation
INNER JOIN returns only rows that have matching values in both joined tables.

25) What type of JOIN produces a Cartesian product of two tables?

  1. INNER JOIN
  2. CROSS JOIN
  3. OUTER JOIN
  4. SELF JOIN
Show Answer
Answer: b
Explanation
CROSS JOIN combines every row from the first table with every row from the second table, producing a Cartesian product.

26) Which MySQL storage engine supports transactions and foreign keys by default?

  1. MyISAM
  2. Memory
  3. InnoDB
  4. CSV
Show Answer
Answer: c
Explanation
InnoDB is the default MySQL storage engine and supports transactions, row-level locking, and foreign keys.

27) Which SQL statement is used to create a new database?

  1. MAKE DATABASE
  2. CREATE DATABASE
  3. NEW DATABASE
  4. BUILD DATABASE
Show Answer
Answer: b
Explanation
CREATE DATABASE is the correct SQL statement for creating a new database.

28) Which statement is used to select a database to work with in MySQL?

  1. USE database_name;
  2. OPEN database_name;
  3. SELECT database_name;
  4. CONNECT database_name;
Show Answer
Answer: a
Explanation
USE database_name; sets the specified database as the current database for subsequent statements.

29) Which statement shows all available databases in MySQL?

  1. DISPLAY DATABASES;
  2. SHOW DATABASES;
  3. LIST DATABASES;
  4. GET DATABASES;
Show Answer
Answer: b
Explanation
SHOW DATABASES; lists all databases available to the current MySQL user.

30) Which statement displays the structure of a table (columns, types, keys)?

  1. SHOW STRUCTURE table_name;
  2. DESCRIBE table_name;
  3. INFO table_name;
  4. STRUCT table_name;
Show Answer
Answer: b
Explanation
DESCRIBE table_name; displays column names, data types, keys, and other table structure details.

31) Which command is used to add a new column to an existing table?

  1. UPDATE TABLE table_name ADD column_name;
  2. ALTER TABLE table_name ADD column_name datatype;
  3. MODIFY TABLE table_name ADD column_name;
  4. INSERT INTO table_name COLUMN column_name;
Show Answer
Answer: b
Explanation
ALTER TABLE … ADD is used to add a new column to an existing table.

32) Which operator is used to test for NULL values in MySQL?

  1. = NULL
  2. IS NULL
  3. EQUALS NULL
  4. == NULL
Show Answer
Answer: b
Explanation
NULL cannot be compared with =; you must use IS NULL or IS NOT NULL.

33) Which SQL function replaces NULL with a specified value?

  1. IFNULL()
  2. ISNULL()
  3. NULLIF()
  4. NVL()
Show Answer
Answer: a
Explanation
IFNULL(expr, value) returns the specified value if expr is NULL; otherwise it returns expr.

34) What does the auto_increment attribute do in MySQL?

  1. Automatically decreases column value
  2. Automatically generates a unique numeric value for new rows
  3. Automatically backs up the table
  4. Encrypts primary keys
Show Answer
Answer: b
Explanation
AUTO_INCREMENT automatically generates a unique numeric value for each new row, commonly used for primary keys.

35) Which keyword is used to limit the number of rows returned by a query in MySQL?

  1. TOP
  2. LIMIT
  3. ROWNUM
  4. FETCH FIRST
Show Answer
Answer: b
Explanation
LIMIT restricts the number of rows returned by a MySQL query.

36) In LIMIT 10, 5, what does 10 represent?

  1. The number of rows to fetch
  2. The starting row offset
  3. The step size
  4. The max ID
Show Answer
Answer: b
Explanation
In LIMIT offset, count, the first number is the offset and the second is the number of rows to return. So 10 is the starting row offset.

37) Which function is used to convert text to uppercase in MySQL?

  1. UPPER()
  2. UCASE()
  3. BOTH a and b
  4. TOUPPER()
Show Answer
Answer: c
Explanation
MySQL supports both UPPER() and UCASE() for converting text to uppercase.

38) Which function returns the length of a string in characters?

  1. LENGTH()
  2. CHAR_LENGTH()
  3. STRLEN()
  4. SIZE()
Show Answer
Answer: b
Explanation
CHAR_LENGTH() returns the number of characters in a string, while LENGTH() returns the number of bytes.

39) Which mathematical function returns the highest value from a column?

  1. HIGHEST()
  2. MAX()
  3. TOP()
  4. GREATEST()
Show Answer
Answer: b
Explanation
MAX() returns the largest value in a column or expression.

40) Which operator is used to filter values within an inclusive range?

  1. IN
  2. BETWEEN
  3. WITHIN
  4. RANGE
Show Answer
Answer: b
Explanation
BETWEEN filters values within an inclusive range, e.g. BETWEEN 10 AND 20.

41) Which operator allows specifying multiple discrete values in a WHERE clause?

  1. IN
  2. MULTI
  3. OR
  4. LIKE
Show Answer
Answer: a
Explanation
IN allows you to specify a list of discrete values, e.g. WHERE city IN (‘Delhi’, ‘Mumbai’).

42) What is the purpose of a Foreign Key in MySQL?

  1. Ensures unique values in the same table
  2. Prevents duplicate records
  3. Enforces referential integrity between two tables
  4. Automatically indexes text columns
Show Answer
Answer: c
Explanation
A foreign key links two tables and enforces referential integrity, ensuring child rows correspond to valid parent rows.

43) Which command grants privileges to MySQL users?

  1. GIVE
  2. ALLOW
  3. GRANT
  4. SET PRIVILEGE
Show Answer
Answer: c
Explanation
GRANT is used to assign privileges to MySQL users.

44) Which command removes privileges from a MySQL user?

  1. REVOKE
  2. REMOVE
  3. DENY
  4. CANCEL
Show Answer
Answer: a
Explanation
REVOKE removes previously granted privileges from a MySQL user.

45) Which privilege allows a user to create new tables and databases?

  1. WRITE
  2. CREATE
  3. BUILD
  4. MAKE
Show Answer
Answer: b
Explanation
The CREATE privilege allows a user to create databases, tables, and other objects.

46) Which command saves all pending transaction changes to the database?

  1. SAVE
  2. COMMIT
  3. ROLLBACK
  4. STORE
Show Answer
Answer: b
Explanation
COMMIT permanently saves all changes made during the current transaction.

47) Which command undoes transaction changes made since the last commit?

  1. UNDO
  2. REVERT
  3. ROLLBACK
  4. CANCEL
Show Answer
Answer: c
Explanation
ROLLBACK reverses uncommitted changes in the current transaction.

48) Which MySQL log records all statements that change database data?

  1. Error Log
  2. Binary Log
  3. Slow Query Log
  4. General Query Log
Show Answer
Answer: b
Explanation
The binary log records data-changing statements and is used for replication and point-in-time recovery.

49) What does DDL stand for in database terminology?

  1. Data Definition Language
  2. Data Description Language
  3. Data Design Language
  4. Dynamic Data Language
Show Answer
Answer: a
Explanation
DDL stands for Data Definition Language, used to define or alter database structures, e.g. CREATE, ALTER, DROP.

50) Which of the following is a DML command?

  1. CREATE
  2. ALTER
  3. INSERT
  4. DROP
Show Answer
Answer: c
Explanation
INSERT is a DML command used to manipulate data. CREATE, ALTER, and DROP are DDL commands.

51) Which of the following is a DDL command?

  1. UPDATE
  2. SELECT
  3. TRUNCATE
  4. DELETE
Show Answer
Answer: c
Explanation
TRUNCATE is classified as DDL because it removes all rows by deallocating data pages rather than deleting rows individually.

52) What is the result of SELECT 5 + NULL; in MySQL?

  1. 5
  2. 0
  3. NULL
  4. Error
Show Answer
Answer: c
Explanation
In MySQL, any arithmetic operation involving NULL returns NULL.

53) Which function removes leading and trailing spaces from a string?

  1. STRIP()
  2. CLEAN()
  3. TRIM()
  4. REMOVE()
Show Answer
Answer: c
Explanation
TRIM() removes leading and trailing spaces from a string.

54) Which index type is created automatically when a PRIMARY KEY is defined?

  1. FULLTEXT
  2. SPATIAL
  3. Clustered Index
  4. Secondary Index
Show Answer
Answer: c
Explanation
In InnoDB, a PRIMARY KEY creates a clustered index that stores the actual row data in key order.

55) Which keyword is used to rename a column or table in a SELECT output?

  1. RENAME
  2. ALIAS
  3. AS
  4. NAME
Show Answer
Answer: c
Explanation
AS is used to assign an alias to a column or expression in the SELECT output.

56) Which function extracts the year part from a DATE or DATETIME expression?

  1. EXTRACT_YEAR()
  2. YEAR()
  3. GET_YEAR()
  4. DATE_YEAR()
Show Answer
Answer: b
Explanation
YEAR(date) returns the year portion of a date or datetime value.

57) How do you write a single-line comment in MySQL?

  1. // comment
  2. — comment
  3. # comment
  4. % comment
Show Answer
Answer: b
Explanation
— starts a single-line comment in MySQL. MySQL also supports # as a single-line comment.

58) What is the purpose of the MySQL EXPLAIN keyword?

  1. Explains database user permissions
  2. Displays the execution plan of a query
  3. Generates query documentation
  4. Repairs corrupted tables
Show Answer
Answer: b
Explanation
EXPLAIN shows how MySQL plans to execute a query, including index usage and join order.

59) Which function is used to concatenate two or more strings together?

  1. COMBINE()
  2. CONCAT()
  3. MERGE()
  4. JOIN_STR()
Show Answer
Answer: b
Explanation
CONCAT() joins two or more strings into a single string.

60) What is a View in MySQL?

  1. A physical table saved on disk
  2. A virtual table based on the result-set of an SQL query
  3. A backup copy of a table
  4. An index file
Show Answer
Answer: b
Explanation
A view is a virtual table defined by a query. It does not store data itself unless materialized.

61) Which command is used to create a view?

  1. MAKE VIEW view_name AS …
  2. CREATE VIEW view_name AS …
  3. BUILD VIEW view_name AS …
  4. NEW VIEW view_name AS …
Show Answer
Answer: b
Explanation
CREATE VIEW view_name AS SELECT … defines a new view in MySQL.

62) What is a Stored Procedure in MySQL?

  1. A table designed for fast data storage
  2. A prepared SQL code that you can save and reuse
  3. A trigger executed on DELETE
  4. An automatic database backup script
Show Answer
Answer: b
Explanation
A stored procedure is a saved, reusable set of SQL statements stored in the database.

63) Which SQL keyword is used to execute a Stored Procedure in MySQL?

  1. EXECUTE
  2. RUN
  3. CALL
  4. START
Show Answer
Answer: c
Explanation
CALL procedure_name(…) is used to execute a stored procedure in MySQL.

64) What is a MySQL Trigger?

  1. A manual backup command
  2. A stored program executed automatically in response to certain events on a table
  3. A primary key modifier
  4. A transaction log file
Show Answer
Answer: b
Explanation
A trigger is a stored program that automatically executes when an INSERT, UPDATE, or DELETE event occurs on a table.

65) Which events can trigger a database trigger in MySQL?

  1. SELECT, INSERT, UPDATE
  2. INSERT, UPDATE, DELETE
  3. CREATE, ALTER, DROP
  4. GRANT, REVOKE, DENY
Show Answer
Answer: b
Explanation
MySQL triggers fire on INSERT, UPDATE, and DELETE events.

66) What does the CHAR data type store?

  1. Fixed-length string
  2. Variable-length string
  3. Large binary data
  4. Numerical integer values
Show Answer
Answer: a
Explanation
CHAR stores fixed-length strings, padding values with spaces to the declared length.

67) What is the storage size of the TINYINT data type in bytes?

  1. 1 byte
  2. 2 bytes
  3. 4 bytes
  4. 8 bytes
Show Answer
Answer: a
Explanation
TINYINT uses 1 byte of storage.

68) What is the maximum range of signed values for a TINYINT column?

  1. 0 to 255
  2. -128 to 127
  3. -32768 to 32767
  4. -2147483648 to 2147483647
Show Answer
Answer: b
Explanation
A signed TINYINT ranges from -128 to 127. Unsigned TINYINT ranges from 0 to 255.

69) Which data type is best suited for storing monetary values with exact precision?

  1. FLOAT
  2. DOUBLE
  3. DECIMAL
  4. INT
Show Answer
Answer: c
Explanation
DECIMAL stores exact numeric values, making it suitable for monetary data where precision matters.

70) What is the default string matching behavior in MySQL standard string queries?

  1. Case-sensitive
  2. Case-insensitive
  3. Binary matching only
  4. ASCII matching only
Show Answer
Answer: b
Explanation
MySQL string comparisons are usually case-insensitive under default collations such as utf8mb4_general_ci.

71) Which operator is used for bitwise AND operations in MySQL?

  1. &&
  2. AND
  3. &
  4. |
Show Answer
Answer: c
Explanation
The single ampersand (&) performs a bitwise AND operation. && and AND are logical operators.

72) Which condition tests if a subquery returns any rows?

  1. IN
  2. EXISTS
  3. ANY
  4. SOME
Show Answer
Answer: b
Explanation
EXISTS returns TRUE if the subquery returns at least one row.

73) Which set operator combines the results of two queries and removes duplicate rows?

  1. UNION ALL
  2. UNION
  3. JOIN
  4. INTERSECT
Show Answer
Answer: b
Explanation
UNION combines result sets and removes duplicate rows. UNION ALL keeps duplicates.

74) What is the difference between UNION and UNION ALL?

  1. UNION retains duplicates; UNION ALL removes duplicates
  2. UNION removes duplicates; UNION ALL retains duplicates
  3. UNION works on text; UNION ALL works on numbers
  4. There is no difference
Show Answer
Answer: b
Explanation
UNION removes duplicate rows, while UNION ALL includes all rows, including duplicates.

75) Which function returns the current logged-in MySQL user and hostname?

  1. USER()
  2. CURRENT_USER()
  3. WHOAMI()
  4. BOTH a and b
Show Answer
Answer: d
Explanation
Both USER() and CURRENT_USER() return the current MySQL user and host information.

76) Which command reloads the grant tables in MySQL?

  1. RELOAD PRIVILEGES;
  2. REFRESH GRANTS;
  3. FLUSH PRIVILEGES;
  4. UPDATE PRIVILEGES;
Show Answer
Answer: c
Explanation
FLUSH PRIVILEGES; reloads the grant tables so privilege changes take effect immediately.

77) Which clause is used to assign default values to a column if no value is provided?

  1. SET
  2. DEFAULT
  3. AUTO
  4. PRESET
Show Answer
Answer: b
Explanation
DEFAULT assigns a value to a column when no value is supplied during insert.

78) What does ACID stand for in transaction processing?

  1. Atomicity, Consistency, Isolation, Durability
  2. Accuracy, Control, Isolation, Data
  3. Availability, Consistency, Integrity, Durability
  4. Access, Control, Indexing, Data
Show Answer
Answer: a
Explanation
ACID stands for Atomicity, Consistency, Isolation, and Durability, the key properties of reliable transactions.

79) Which engine storage type stores all data in RAM for fast access?

  1. InnoDB
  2. MyISAM
  3. Memory (HEAP)
  4. Archive
Show Answer
Answer: c
Explanation
The Memory (HEAP) storage engine stores data in RAM, making access very fast but non-persistent after restart.

80) What command is used to delete a database in MySQL?

  1. REMOVE DATABASE db_name;
  2. DELETE DATABASE db_name;
  3. DROP DATABASE db_name;
  4. ERASE DATABASE db_name;
Show Answer
Answer: c
Explanation
DROP DATABASE db_name; permanently removes the database and all its objects.

81) Which command is used to create an index on a table?

  1. MAKE INDEX
  2. CREATE INDEX
  3. ADD INDEX
  4. BUILD INDEX
Show Answer
Answer: b
Explanation
CREATE INDEX creates an index on one or more columns of a table.

82) What is the main purpose of creating an index on a table?

  1. To compress table size
  2. To speed up data retrieval operations
  3. To encrypt data
  4. To enforce foreign key limits
Show Answer
Answer: b
Explanation
Indexes improve the speed of SELECT queries by allowing faster lookup of rows.

83) Which function calculates the average value of a numeric column?

  1. AVG()
  2. MEAN()
  3. AVERAGE()
  4. SUM_AVG()
Show Answer
Answer: a
Explanation
AVG() returns the average value of a numeric column.

84) Which statement returns all records from a table named employees?

  1. GET ALL FROM employees;
  2. SELECT * FROM employees;
  3. DISPLAY employees;
  4. FETCH ALL employees;
Show Answer
Answer: b
Explanation
SELECT * FROM employees; retrieves all columns and rows from the employees table.

85) What does the ON DELETE CASCADE option mean for foreign keys?

  1. Dependent rows in the child table are automatically deleted when the parent row is deleted
  2. Prevents deletion of rows in the parent table
  3. Sets foreign key values to NULL upon parent deletion
  4. Archives deleted records to a backup table
Show Answer
Answer: a
Explanation
ON DELETE CASCADE automatically deletes child rows when the referenced parent row is deleted.

86) Which function returns the current database name in use?

  1. CURRENT_DB()
  2. DATABASE()
  3. SHOW_DB()
  4. DB_NAME()
Show Answer
Answer: b
Explanation
DATABASE() returns the name of the currently selected database.

87) How do you write a multi-line comment in MySQL?

  1. // comment //
  2. /* comment */
  3. — comment —
  4. # comment #
Show Answer
Answer: b
Explanation
/* comment */ is the standard multi-line comment syntax in MySQL.

88) Which data type is best used for storing binary large objects like images or files?

  1. VARCHAR
  2. TEXT
  3. BLOB
  4. CLOB
Show Answer
Answer: c
Explanation
BLOB (Binary Large Object) is designed to store binary data such as images, audio, or files.

89) What is the default date format in MySQL?

  1. DD-MM-YYYY
  2. MM-DD-YYYY
  3. YYYY-MM-DD
  4. YYYY-DD-MM
Show Answer
Answer: c
Explanation
MySQL stores and displays DATE values in YYYY-MM-DD format.

90) Which function is used to get the number of days between two dates?

  1. DATEDIFF()
  2. DATE_DIFF()
  3. SUBDATE()
  4. DIFFDATE()
Show Answer
Answer: a
Explanation
DATEDIFF(date1, date2) returns the number of days between two dates.

91) What does the CHECK constraint do in MySQL?

  1. Verifies if a user has admin access
  2. Ensures that values in a column satisfy a specific condition
  3. Checks database integrity on startup
  4. Verifies duplicate keys
Show Answer
Answer: b
Explanation
CHECK enforces a condition on column values, e.g. CHECK (age >= 18).

92) Which character set is recommended in modern MySQL for full Unicode support (including emojis)?

  1. latin1
  2. utf8mb3
  3. utf8mb4
  4. ascii
Show Answer
Answer: c
Explanation
utf8mb4 supports full Unicode, including 4-byte characters such as emojis.

93) Which statement is used to remove an index named idx_name from table_name?

  1. DROP INDEX idx_name ON table_name;
  2. DELETE INDEX idx_name FROM table_name;
  3. REMOVE INDEX idx_name;
  4. ALTER TABLE table_name CLEAR INDEX idx_name;
Show Answer
Answer: a
Explanation
DROP INDEX idx_name ON table_name; removes the specified index from the table.

94) Which clause limits query results based on aggregate calculations?

  1. WHERE
  2. ORDER BY
  3. HAVING
  4. GROUP BY
Show Answer
Answer: c
Explanation
HAVING filters grouped results using aggregate conditions, such as HAVING COUNT(*) > 5.

95) What is a subquery in MySQL?

  1. A query stored as a view
  2. A query nested inside another SQL statement
  3. A secondary database connection
  4. A procedure call
Show Answer
Answer: b
Explanation
A subquery is a query written inside another SQL statement, often in WHERE, FROM, or SELECT.

96) Which command shows all active threads/queries running in MySQL?

  1. SHOW PROCESSLIST;
  2. SHOW THREADS;
  3. SHOW QUERIES;
  4. LIST PROCESSES;
Show Answer
Answer: a
Explanation
SHOW PROCESSLIST; displays currently running threads and queries.

97) Which clause is used to set savepoints inside a transaction?

  1. CREATE SAVEPOINT name;
  2. SAVEPOINT name;
  3. SET SAVEPOINT name;
  4. POINT name;
Show Answer
Answer: b
Explanation
SAVEPOINT name; creates a point within a transaction to which you can later roll back.

98) Which MySQL system variable stores the auto-increment step value?

  1. auto_increment_step
  2. auto_increment_increment
  3. auto_increment_offset
  4. increment_val
Show Answer
Answer: b
Explanation
auto_increment_increment controls the step value between successive AUTO_INCREMENT values.

99) Which function returns the auto-generated ID from the last INSERT statement executed by the connection?

  1. LAST_INSERT_ID()
  2. GET_LAST_ID()
  3. AUTO_ID()
  4. NEW_ID()
Show Answer
Answer: a
Explanation
LAST_INSERT_ID() returns the first automatically generated ID value from the most recent INSERT on the current connection.

100) What does the COALESCE() function return?

  1. The first non-NULL value in a list of arguments
  2. The sum of all non-NULL values
  3. A count of NULL entries
  4. The last argument regardless of NULL status
Show Answer
Answer: a
Explanation
COALESCE(value1, value2, …) returns the first non-NULL value from its argument list.
100 SQL MCQ (Multiple Choice Questions) with Answers
100 MongoDB MCQ (Multiple Choice Questions) with Answers
Print page
0 Likes
Studyopedia Editorial Staff
contact@studyopedia.com

We work to create programming tutorials for all.

No Comments

Post A Comment