MySQL mcq questions and answers | MySQL MCQs

This question asks about how to find views in MySQL that have become unusable because of changes to the tables they are based on.

Imagine you have a view called "CustomerOrders" that shows information from the "Customers" and "Orders" tables. If you make changes to either of those tables, the "CustomerOrders" view might stop working correctly.

Here's how to understand the answer choices:
* Option A: CREATE TABLE - This is used to create a new table, not to check the validity of views.
* Option B: VERIFY TABLE - This is used to check the consistency of a table's data, but it doesn't specifically tell you about invalidated views.
* Option C: DETAILS TABLE - This is not a valid command in MySQL.
* Option D: CHECK TABLE - This is the correct answer! CHECK TABLE is used to check the consistency of a table or view. It will tell you if a view is invalidated because of changes to the underlying tables.

So, the best way to check for invalidated views is to use CHECK TABLE.

This question is about how MySQL handles NULL values in a WHERE clause.

The code you see:

SELECT *
FROM person
WHERE emp_id = NULL;

Is trying to find rows in the table 'person' where the 'emp_id' column is equal to NULL.

However, in SQL, comparing a value to NULL using '=' will always result in false.

This is because NULL represents an unknown or missing value.

So, the correct answer is:
Option C: No output

The query won't return any rows because it's looking for rows where 'emp_id' is exactly equal to NULL, which will never be true.

To find rows where 'emp_id' is NULL, you need to use the operator 'IS NULL'.

For example:

SELECT *
FROM person
WHERE emp_id IS NULL;

This code will correctly find rows where the 'emp_id' column is NULL.

This question is about character sets and collations in MySQL. Let's break it down:
* Character set: A character set defines a set of characters (like letters, numbers, and symbols) that a database can store.
* Collation: A collation defines how those characters are sorted and compared. Think of it as a specific language setting for your data.
For example, the character set "utf8mb4" can handle a wide range of characters from different languages. However, it can have several collations like "utf8mb4_general_ci" (case-insensitive) or "utf8mb4_unicode_ci" (case-insensitive and uses Unicode rules).
So, the answer is Option D: more than 1. A character set can have multiple collations to handle different sorting and comparison needs.

This question is asking about how to link the math library in a C program. The math library is a collection of pre-written functions for doing mathematical operations like calculating square roots, trigonometric functions (like sine, cosine), logarithms, and more.

To use these functions in your C program, you need to tell the compiler where to find them. This is done by adding a special flag to the compilation command.

The correct answer is Option A: -lm. This flag tells the compiler to link the math library (which is usually named "libm.so" or "libm.a").

The other options are incorrect because they don't refer to the math library:
* Option B: -ln is used to link the network library.
* Option C: -lp is used to link the standard C library (libc).
* Option D: -lq is not a standard linker flag in most systems.

Example:
If your C program is named "myprogram.c" , you would compile it with the math library using the following command:
``` gcc myprogram.c -lm -o myprogram ```
This command compiles the "myprogram.c" file, links the math library, and creates an executable file named "myprogram".

This question is about selecting data from a table named "employee" using a specific condition. Let's break it down:
Understanding the Code
* SELECT emp_id, fname, lname FROM employee: This part tells MySQL to retrieve the "emp_id", "fname" (first name), and "lname" (last name) columns from the "employee" table.
* WHERE LEFT (fname, 1) = 'F': This is the condition that filters the results. It uses the LEFT() function to extract the first character (1) from the "fname" column. The condition then checks if this extracted character is equal to 'F'.
Explanation of Options
* Option A: Only those employees are selected whose first name started with 'F': This is correct. The code specifically selects employees where the first letter of their first name is 'F'.
* Option B: Only those employees are selected whose first name started with other than 'F': This is incorrect. The code only selects employees with 'F' as the first letter of their first name.
* Option C: All of the mentioned: This is incorrect because Option B is not correct.
* Option D: None of the mentioned: This is incorrect because Option A is correct.
Therefore, the correct answer is Option A.

This question is about how MySQL handles storage for different table types.

Let's break down the options:

* MyISAM: MyISAM tables are stored in separate files. Each MyISAM table has its own set of files. So they are not bound by the operating system (OS) file size limit.
* InnoDB: InnoDB tables store data in a shared tablespace. This tablespace is essentially a single file that holds data for multiple InnoDB tables. So they are bound by the OS file size limit.
* TRANSACTION: The transaction log for InnoDB is also stored in a shared tablespace, which is also a single file. So they are bound by the OS file size limit.

Therefore, the answer is Option C: 2. Two tablespaces, InnoDB and TRANSACTION, are bound by the OS file size limit.

This question asks you about the command used to view the databases in your MySQL server.

Let's break down the options:
* Option A: mysqld is the actual MySQL server, not a command to list databases.
* Option B: mysqlshow is the correct command to display a list of databases. It's specifically designed for this purpose.
* Option C: mysqllist doesn't exist as a standard MySQL command.
* Option D: mysqldb also doesn't exist as a standard MySQL command.

Therefore, the answer is Option B: mysqlshow.

This question is about what happens when you create a new table in MySQL and don't specifically tell the database what to put in a column. Think of it like creating a new spreadsheet - if you don't enter anything, what will show up?

The answer is Option A: NULL. This means the column will be empty.

Here's why the other options are incorrect:
* Option B: 0 - While 0 is a common value, MySQL won't automatically fill columns with 0 unless you tell it to.
* Option C: 1 - Same as above, 1 is not a default value.
* Option D: -1 - Again, this is not a default value.

Remember, NULL is a special value in databases that means "unknown" or "empty".

This question is about Data Manipulation Language (DML) in MySQL. DML is used to modify the data in your database. Let's break down the options:
Option A: UPDATE
The UPDATE statement is used to change existing data within a table. It's like editing information in a spreadsheet.
Option B: ALTER
The ALTER statement is used to modify the structure of a table. This is like changing the columns or rows in a spreadsheet. It's not about changing the data itself.
Option C: MODIFY
The MODIFY statement is similar to ALTER. It's used to change the structure of a table, but it's often used to specifically change the definition of an existing column.
Option D: All of the mentioned
Since UPDATE deals with changing data, and ALTER and MODIFY are about changing table structure, not data, Option D is incorrect.
The Correct Answer: Option A: UPDATE
Remember, DML is about managing the data itself, not the table's layout.