MySQL mcq questions and answers | MySQL MCQs

This question asks you to choose the best data type for a column that will store large numbers, up to 2 million. Let's break down the options:
Option A: SMALLINT - Stores small integers, typically from -32,768 to 32,767. Not big enough for 2 million.
Option B: TINYINT - Stores even smaller integers, typically from -128 to 127. Definitely not big enough.
Option C: MEDIUMINT - Stores integers up to 8,388,607. Still not large enough for 2 million.
Option D: BIGINT - Stores large integers, typically from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. This is the largest integer type in MySQL and perfect for numbers up to 2 million.
So the answer is D: BIGINT
It's the most suitable data type for handling such large numbers.

This MySQL command is used to filter data based on specific conditions.
Let's break down the command:

WHERE TITLE=’teller’ OR start_date=’2007-01-01’

This command will select rows where either:

1. TITLE=’teller’ : The employee's job title is "teller."
OR
2. start_date=’2007-01-01’ : The employee's start date is January 1, 2007.

In simpler terms, it will fetch data about employees who are either tellers or started working on January 1, 2007.
Therefore, the correct answer is Option D: All of the mentioned.

This command will return data for employees who meet any of the following criteria:
* They are a teller.
* They started on January 1, 2007.
* They are a teller and started on January 1, 2007.

This question tests your understanding of how the ORDER BY clause works in MySQL.
Let's break it down:
* The SQL statement: * `SELECT emp_id`: This part selects the 'emp_id' column from the table. * `FROM person`: This specifies the table called 'person' to get the data from. * `ORDER BY emp_id`: This is the key! It instructs MySQL to arrange the selected 'emp_id' values in ascending order.
* The `emp_id` set: {1, 2, 2, 3, 3, 1}.
* The output: The SQL statement will sort this set of 'emp_id' values in ascending order, resulting in: * {1, 1, 2, 2, 3, 3}
Therefore, the correct answer is Option A.

This question is about how MySQL handles the LAST_INSERT_ID() function, which is used to get the ID of the last row inserted into a table.

The Question:
Does the LAST_INSERT_ID() function only remember the ID from the last insert done within the same connection to the database server?

Understanding the Options:
* Option A: True - This means that if you close the connection to the MySQL server and then open a new connection, the LAST_INSERT_ID() function will not remember any previous inserted IDs. It will only work for the current connection. * Option B: False - This means that the LAST_INSERT_ID() function will remember the last inserted ID across different connections, even if you close and re-open the connection.

The Answer:
The correct answer is Option A: True. The LAST_INSERT_ID() function is tied to the current connection. It only remembers the last AUTO_INCREMENT value generated within that connection. When you close the connection, it forgets that value.
Example:
Imagine you insert a new row into a table using one connection. Then you close that connection and open a new one. If you use LAST_INSERT_ID() in this new connection, it won't return the ID of the row you inserted earlier because it's a different connection.

This question asks whether the MySQL server is difficult to set up and configure.
Here's why the answer is False:
MySQL is known for being quite user-friendly. While it has many advanced settings, the core configuration is straightforward. There are plenty of resources and guides available to help beginners get started.
The MySQL server is actually quite flexible and allows for customization based on your specific needs.

This question is about how the AUTO_INCREMENT feature in MySQL works best with different data types. Let's break it down:

AUTO_INCREMENT automatically assigns a unique, increasing number to each new row added to a table. This is super useful for creating primary keys that uniquely identify each entry.

Now, look at the options:
* FLOAT and DOUBLE are used for storing numbers with decimal points. They are not ideal for AUTO_INCREMENT because you need whole, increasing numbers.
* CHARACTER is for storing text, not numbers.
* INT is the best fit for AUTO_INCREMENT. It's a whole number data type, perfect for creating a sequence of unique IDs.

Therefore, the correct answer is Option B: INT.

This question is about different types of logs in MySQL. Think of logs as a record of what happens in your database.

Metadata refers to information about data, like the structure of your tables. So, the metadata log tracks changes to this structure.

Let's look at the options:

Option A: error log - This log stores errors that occur in MySQL. It's not related to metadata changes.

Option B: ddl log - DDL stands for Data Definition Language. It's used to create, modify, or delete tables. So, the DDL log would track changes to your database structure, which is exactly what the metadata log does!

Option C: binary log - This log records all changes made to the database. It includes DDL operations but also records data changes (inserts, updates, deletes).

Option D: relay log - This log is used in replication. It's a temporary log that's used to pass changes from the master server to the slave server.

Therefore, the best answer is Option B: ddl log. The metadata log is essentially the same as the DDL log - it records changes to the database structure.

This question is about how MySQL handles the auto-increment counter in MyISAM tables when you use the `TRUNCATE TABLE` command.

The `TRUNCATE TABLE` command is used to delete all rows from a table.

In MyISAM tables, the auto-increment counter is a special value that helps MySQL assign unique IDs to new rows. When you add a new row, the counter is increased by 1, and the new row gets the current value of the counter.

The question asks what happens to the counter when you use `TRUNCATE TABLE`.

The answer is Option A: 0

When you truncate a table, MySQL resets the auto-increment counter to 0. This ensures that when you start adding new rows again, they will receive unique IDs starting from 1.

This question is asking about which part of MySQL actually does the work of handling your data. Think of it like this:
* You are the MySQL client. You write commands in SQL to tell MySQL what to do. * MySQL server is like the brain of the system. It takes your commands, figures out how to do them, and then does them with your data.
So the answer is Option B: MySQL server.
Here's why the other options are incorrect:
* Option A: MySQL client is the tool you use to interact with the server, it doesn't actually handle data. * Option C: SQL is the language you use to communicate with the server. * Option D: Server daemon program is a more technical term for the background process that keeps the MySQL server running.

This question is about how to run a bunch of commands in MySQL all at once.
Imagine you have a list of commands you want to run, like creating a table or adding some data.

You can't just type them all in one by one, right? It would take forever!

So, MySQL lets you use a special file to store all those commands. Then, you can tell MySQL to run all those commands from that file.

Out of the options given, the correct answer is Option B: dofile.

Here's how it works:
1. Write your MySQL commands in a text file. 2. Save the file with the extension .sql. 3. In MySQL, use the command `source filename.sql` to run all the commands in that file.
Let me know if you want to know more about `dofile`!