MySQL Quiz - MCQ - Multiple Choice Questions

Q. Which of the following is the correct SQL statement to create a new table in MySQL?

A. CREATE NEW TABLE
B. CREATE TABLE
C. NEW TABLE
D. TABLE CREATE

Q. What is the purpose of the PRIMARY KEY constraint in MySQL?

A. To ensure data integrity by uniquely identifying each record in a table
B. To speed up data retrieval by indexing the table
C. To allow sorting of data based on the key value
D. All of the above

Q. How do you insert data into a table in MySQL?

A. INSERT DATA
B. INSERT INTO TABLE
C. INSERT INTO <table_name> VALUES (<values>);
D. None of the above

Q. What is the purpose of the WHERE clause in a SELECT statement?

A. To specify the columns to retrieve data from
B. To specify the table to retrieve data from
C. To filter the rows based on a specified condition
D. To sort the retrieved data

Q. What is the difference between DELETE and TRUNCATE in MySQL?

A. DELETE removes rows one by one, TRUNCATE removes all rows at once
B. DELETE can be rolled back, TRUNCATE cannot be rolled back
C. DELETE is used to remove specific rows, TRUNCATE is used to remove all rows
D. All of the above

Q. How do you update data in a table using MySQL?

A. UPDATE TABLE
B. UPDATE <table_name> SET <column1> = <value1>, <column2> = <value2>...
C. CHANGE TABLE
D. ALTER TABLE

Q. What is a JOIN in MySQL and what are the different types of JOINs?

A. A JOIN combines rows from two or more tables based on a related column. Types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.
B. A JOIN is used to add new columns to a table. Types: ADD JOIN, APPEND JOIN, EXTEND JOIN.
C. A JOIN is used to merge two tables into one. Types: MERGE JOIN, COMBINE JOIN, UNION JOIN.
D. A JOIN is used to remove duplicate rows from a table. Types: DISTINCT JOIN, UNIQUE JOIN, DEDUPE JOIN.

Q. What is a subquery in MySQL and when is it useful?

A. A subquery is a query within another query and is useful for retrieving data that depends on the results of another query.
B. A subquery is a query that runs in the background and is used to optimize performance.
C. A subquery is a query that is executed first and then used as a condition in the main query.
D. A subquery is a query that is run on a different database and returns data to the main query.

Q. How do you create an index in MySQL?

A. CREATE INDEX
B. INDEX CREATE
C. NEW INDEX
D. TABLE INDEX

Q. What is a view in MySQL and how do you create one?

A. A view is a virtual table based on the result-set of an SQL statement. To create a view, use the CREATE VIEW statement.
B. A view is a copy of a table that can be used for backup purposes. To create a view, use the COPY TABLE statement.
C. A view is a temporary table that exists only for the duration of a session. To create a view, use the TEMPORARY TABLE statement.
D. A view is a read-only version of a table. To create a view, use the READ ONLY statement.

Q. How do you handle date and time data in MySQL?

A. MySQL provides the DATE and TIME data types to store date and time values.
B. MySQL automatically converts all date and time values to timestamps.
C. MySQL stores date and time values as strings and requires custom functions for manipulation.
D. MySQL does not support date and time data types, and they must be handled programmatically.

Q. What is normalization in the context of database design?

A. Normalization is the process of organizing data to minimize redundancy and improve data integrity.
B. Normalization is a technique used to optimize query performance by reducing the number of joins.
C. Normalization is the process of converting data into a standardized format for easier analysis.
D. Normalization is a method for encrypting data to protect sensitive information.

Q. What is the difference between a CHAR and a VARCHAR data type in MySQL?

A. CHAR has a fixed length, VARCHAR has a variable length.
B. CHAR is used for storing characters, VARCHAR is used for storing numbers.
C. CHAR is case-sensitive, VARCHAR is not case-sensitive.
D. CHAR is used for fixed-length strings, VARCHAR is used for variable-length numbers.

Q. How do you handle NULL values in MySQL?

A. NULL values are automatically handled by MySQL and do not require special treatment.
B. NULL values can be used in calculations and comparisons just like any other value.
C. NULL values must be explicitly handled using the IS NULL or IS NOT NULL conditions.
D. NULL values are treated as zero in calculations and false in comparisons.

Which of the following SQL queries will return the names of employees who earn a salary greater than $50000?

SELECT first_name, last_name
FROM employees
WHERE salary > 50000;

SELECT first_name, last_name
FROM employees
WHERE salary >= 50000;

SELECT first_name, last_name
FROM employees
WHERE salary BETWEEN 50000 AND 100000;

SELECT first_name, last_name
FROM employees
WHERE salary > 50000 AND salary < 100000;

What will be the output of the following SQL query if the "orders" table has 100 rows?

SELECT COUNT(*) FROM orders;

COUNT(*)

It will return an error.

The query will not execute.

Which of the following SQL queries will update the salary of all employees by increasing it by $500?

UPDATE employees
SET salary = salary + 500;

UPDATE employees
SET salary = salary * 1.05;

ALTER TABLE employees
MODIFY salary = salary + 500;

CHANGE TABLE employees
SET salary = salary + 500;

What will be the output of the following SQL query if the "employees" table has 5 rows?

SELECT * FROM employees LIMIT 3;

3 rows will be returned.

5 rows will be returned.

It will return an error.

Only the first 3 rows will be updated.

Which of the following SQL queries will insert a new row into the "customers" table with the given values?

INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', 'john@example.com');

INSERT INTO customers
SET first_name = 'John', last_name = 'Doe', email = 'john@example.com';

INSERT INTO customers (first_name, email)
VALUES ('John', 'john@example.com');

INSERT INTO customers
VALUES ('John', 'Doe', 'john@example.com');

What will be the output of the following SQL query if the "products" table has 20 rows?

SELECT * FROM products LIMIT 10 OFFSET 5;

10 rows starting from row 6 will be returned.

5 rows will be returned.

15 rows will be returned.

It will return an error.

Which of the following SQL queries will delete all rows from the "orders" table where the order date is before '2023-01-01'?

DELETE FROM orders
WHERE order_date < '2023-01-01';

REMOVE FROM orders
WHERE order_date < '2023-01-01';

DELETE orders
WHERE order_date < '2023-01-01';

REMOVE orders
WHERE order_date < '2023-01-01';

What will be the output of the following SQL query if the "employees" table has 10 rows and the "departments" table has 5 rows?

SELECT COUNT(*) FROM employees, departments;

It will return an error.

Which of the following SQL queries will retrieve the names of employees who work in the "Sales" department?

SELECT first_name, last_name
FROM employees
WHERE department = 'Sales';

SELECT first_name, last_name
FROM employees
WHERE department IN ('Sales');

SELECT first_name, last_name
FROM employees
WHERE department LIKE '%Sales%';

SELECT first_name, last_name
FROM employees
WHERE department = ANY ('Sales');

What will be the output of the following SQL query if the "customers" table has 10 rows and the "orders" table has 5 rows?

SELECT c.customer_name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

It will return the names of all 10 customers along with their respective order counts.

It will return the names of only those customers who have placed orders, along with their order counts.

It will return an error.

It will return the names of all 10 customers with a count of 5 orders for each.

Search Tutorials