MySQL MCQs
Q.1 Which SQL statement is used to read data from a database?
A. SELECT
B. INSERT
C. UPDATE
D. DELETE
Q.2 Identify the error in this SQL command:
CREATE TABLE Products (ProductID INT, Price DECIMAL(5));
A. DECIMAL definition is incomplete
B. Price should be an INTEGER
C. ProductID should be a TEXT
D. No error
Q.3 What is incorrect in the following SQL statement?
CREATE TABLE Users (ID INT, Name CHAR(20), Email VARHCAR(100));
A. Spelling mistake in data type for Email
B. ID should be VARCHAR
C. Name should be TEXT
D. Syntax is correct
Q.4 What does the following SQL command achieve?
CREATE TABLE Orders (OrderID INT, OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP);
A. Creates a table with two columns without defaults
B. Creates a table and sets a default timestamp for OrderDate
C. Creates a table and makes OrderDate a primary key
D. None of the above
Q.5 Consider the following SQL statement:
ALTER TABLE Employees ADD COLUMN Birthdate DATE;
What does this statement do?
A. Adds a new row called Birthdate
B. Changes the data type of Birthdate
C. Adds a new column Birthdate to store dates
D. Deletes the Birthdate column
Q.6 Which SQL statement correctly creates a table with a column for storing binary data?
A. CREATE TABLE Files (Data CHAR(64));
B. CREATE TABLE Files (Data BINARY(64));
C. CREATE TABLE Files (Data TEXT);
D. CREATE TABLE Files (Data BLOB);
Q.7 What is the primary use of the ENUM data type in MySQL?
A. To store arrays
B. To limit input to a list of possible values
C. To create a queue structure
D. To log changes
Q.8 In MySQL, which data type is most appropriate for storing monetary values?
A. DECIMAL
B. FLOAT
C. DOUBLE
D. INTEGER
Q.9 What is the main difference between CHAR and VARCHAR data types in MySQL?
A. CHAR stores binary data, VARCHAR does not
B. CHAR has unlimited length, VARCHAR is limited
C. CHAR is variable-length, VARCHAR is fixed-length
D. CHAR is fixed-length, VARCHAR is variable-length
Q.10 Which data type would be best for storing an email address?
A. CHAR(50)
B. VARCHAR(100)
C. TEXT
D. BLOB
Q.11 What is the purpose of the VARCHAR data type in MySQL?
A. To store fixed-length strings
B. To store variable-length strings
C. To store large text objects
D. To store integers
Q.12 Which data type in MySQL is used to store boolean values?
A. TINYINT
B. SMALLINT
C. VARCHAR
D. BOOLEAN
Q.13 Identify the error in this SQL statement:
INSERT INTO Order (ID, Product) VALUES (101, ‘Laptop’);
A. Table name should be Orders
B.Syntax is correct
C. Missing semicolon at the end
D. The Product column does not exist
Q.14 What does the following SQL command achieve?
UPDATE Products SET Price = Price * 1.1 WHERE Category = ‘Electronics’;
A. Increases the price of all products by 10%
B. Decreases the price of all products by 10%
C. Increases the price of electronics products by 10%
D. None of the above
Q.15 Consider the following SQL command:
DELETE FROM Customers WHERE CustomerID = 3;
What does it do?
A. Deletes the customer record with CustomerID 3
B. Deletes all records from Customers
C. Updates CustomerID to 3
D. None of the above
Q.16 What is the result of the following SQL query?
SELECT 15 + 25;
A. 40
B. ’15 + 25′
C. 15
D. 25
Q.17 What is the default sort order of the ORDER BY statement in SQL?
A. Ascending
B. Descending
C. Random
D. Fixed
Q.18 Which SQL statement is used to change data in an existing row?
A. INSERT
B. UPDATE
C. ALTER
D. CREATE
Q.19 What does the GROUP BY statement do in a SQL query?
A. Groups data based on one or more columns
B. Sorts the output
C. Deletes groups of data
D. Modifies data in a group
Q.20 What type of SQL statement is used to add new data into a database?
A. CREATE
B. SELECT
C. INSERT
D. UPDATE
Q.21 What does the DISTINCT keyword do in a SQL query?
A. Removes duplicates from results
B. Creates a distinct file
C. Orders results
D. Counts rows
Q.22 SQL keywords are case sensitive.
A. All keywords are sensitive
B. Some are sensitive
C. None are sensitive
D. Depends on version
Q.23 Which SQL clause is used to filter the records returned from a SQL query?
A. FROM
B. WHERE
C. SELECT
D. ORDER BY
Q.24 Identify the issue in the following SQL statement:
SELECT FROM users WHERE username=’admin’;
A. SELECT clause is incomplete
B. WHERE clause is incorrect
C. Syntax is correct
D. Missing semicolon
Q.25 What does the following MySQL command do?
CREATE DATABASE SampleDB;
A. Creates a new table named SampleDB
B. Creates a new schema named SampleDB
C. Creates a new database named SampleDB
D. Deletes the database SampleDB
Q.26 In MySQL, what does the AUTO_INCREMENT attribute automatically add to a column?
A. Random numbers
B. A timestamp
C. Unique identifiers incrementally
D. Fixed numbers
Q.27 MySQL belongs to which category of database management systems?
A. Hierarchical DBMS
B. Network DBMS
C. Relational DBMS
D. Object-oriented DBMS
Q.28 Which command is used to access a MySQL database via command line?
A. mysql -u user -p
B. ssh database
C. connect to mysql
D. open mysql
Q.29 Which SQL statement is used to remove data from a MySQL database?
A. DELETE
B. REMOVE
C. CLEAR
D. ERASE
Q.30 What does MySQL primarily function as?
A. A web server
B. A database management system
C. A programming language
D. A browser
Q.31 What is wrong with this SQL command:
CREATE INDEX idx_product_id ON Products (ProductID) WHERE ProductID IS NULL;
A. MySQL does not support filtered indexes
B. Index name is incorrect
C. Syntax error in WHERE clause
D. All are correct
Q.32 Identify the error in this SQL statement:
CREATE INDEX ON Orders (OrderDate);
A. Missing index name
B. Syntax is correct
C. Should be a UNIQUE index
D. No column specified
Q.33 What is the effect of adding an index to a table on the performance of INSERT statements?
A. Slows down INSERTs
B. Speeds up INSERTs
C. No effect on INSERTs
D. INSERTs become unpredictable
Q.34 How does the query optimizer use indexes in MySQL?
A. To decide the best order to join tables
B. To reduce the use of disk space
C. To increase transaction speed
D. To automatically update table statistics
Q.35 What does the following SQL command do?
CREATE INDEX idx_name ON Customers (Name);
A. Creates a unique constraint on Name
B. Deletes an index on Name
C. Creates an index on the Name column
D. Alters the Name column
Q.36 When is a composite index useful in MySQL?
A. When querying multiple columns frequently
B. Only with the PRIMARY KEY
C. For single-column searches
D. Never
Q.37 Which MySQL engine supports FULLTEXT indexing?
A. MyISAM
B. InnoDB
C. Both
D. Neither
Q.38 What type of index would be most effective for a column that stores unique values in MySQL?
A. Primary index
B. Secondary index
C. Clustered index
D. Non-clustered index
Q.39 What is the primary purpose of an index in a database?
A. To increase database size
B. To enhance data security
C. To speed up data retrieval
D. To transform data
Q.40 What is incorrect in the following SQL command?
SELECT FROM Employees, Departments WHERE Employees.DepartmentID = Department.ID;
A. SELECT clause is incomplete
B. WHERE clause is incorrect
C. Syntax is correct
D. Using WHERE instead of ON
Q.41 Identify the error in this SQL statement:
SELECT * FROM Orders INNER JOIN Customers ON Orders.CustID = Customers.ID WHERE Customers.Status = ‘Active’;
A. There is no error
B. Misuse of JOIN
C. Syntax error in the WHERE clause
D. Column name mismatch in the ON clause
Q.42 What does this SQL query achieve?
SELECT EmployeeID, MAX(Salary) FROM Employees GROUP BY DepartmentID HAVING COUNT(EmployeeID) > 1;
A. Retrieves the highest salary in each department with more than one employee
B. Lists all employees’ salaries
C. Deletes entries with max salaries
D. None of the above
Q.43 Consider this SQL statement:
SELECT Name, Product FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.OrderID IS NULL; What does this query return?
A. Names and products of customers who have not placed any orders
B. All customer and order pairs
C. All customers and their orders
D. Invalid query
Q.44 What does the following SQL statement accomplish?
SELECT * FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
A. Retrieves all employees and their department data
B. Updates employee records
C. Deletes departments
D. Creates new departments
Q.45 Which type of subquery is executed once for each row processed by the parent query?
A. Correlated
B. Non-correlated
C. Repeated
D. Static
Q.46 In SQL, a subquery can be placed in which of the following clauses?
A. WHERE
B. FROM
C. SELECT
D. All of the above
Q.47 Which SQL keyword is used to create a subquery?
A. SUB
B. WITH
C. SELECT
D. IN
Q.48 What does a FULL OUTER JOIN do?
A. Combines all rows from both tables where matches exist, filling with NULLs where there is no match
B. Only combines matching rows
C. Only returns non-matching rows of both tables
D. None of the above
Q.49 Which type of JOIN returns only the rows that have a match in both joined tables?
A. LEFT JOIN
B. RIGHT JOIN
C. INNER JOIN
D. FULL JOIN
Q.50 What is the main purpose of using a JOIN in SQL?
A. To delete records from a table
B. To merge rows from two or more tables
C. To update records in a table
D. To create a new table
Q.51 What is wrong with this SQL command:
DELETE FROM Products WHERE;
A. Incomplete WHERE clause
B. Syntax is correct
C. Unnecessary semicolon
D. Extra space in statement
Q.52 Identify the mistake in this SQL statement:
UPDATE User SET Status = ‘Active’ WHER ID = 5;
A. Typo in WHERE clause
B. Incorrect column name
C. Both
D. None of the above
Q.53 How does the SQL statement INSERT INTO Orders (ProductID, Quantity) VALUES (101, 1) ON DUPLICATE KEY UPDATE Quantity = Quantity + 1; function?
A. Adds a new order or increases quantity if exists
B. Creates multiple entries for same product
C. Deletes previous entries for product
D. None of the above
Q.54 What does the following SQL statement do?
UPDATE Users SET Age = Age + 1 WHERE ID = 1;
A. Increments age of all users
B. Sets all users’ age to 1
C. Increments age of the user with ID 1
D. Deletes user with ID 1
Q.55 Consider the following SQL command:
INSERT INTO Customers (Name, Age) VALUES (‘Alice’, 30);
What does this command do?
A. Inserts a new row into Customers
B. Updates a row in Customers
C. Deletes a row from Customers
D. None of the above
Q.56 What is the result of the following SQL query?
SELECT COUNT(*) FROM Users;
A. The number of rows in Users
B. The list of users
C. The first user in the table
D. Total data size of Users
Q.57 What does the SQL clause ‘ON DUPLICATE KEY UPDATE’ do in an INSERT statement?
A. Inserts a new row as a duplicate
B. Updates the row if it already exists
C. Deletes the duplicate row
D. None of the above
Q.58 Which clause should be used with the DELETE statement to specify which rows to remove?
A. WHERE
B. HAVING
C. GROUP BY
D. ORDER BY
Q.59 What is the purpose of the DELETE statement in SQL?
A. To drop a table
B. To remove specific rows from a table
C. To modify rows
D. To create a new table
Q.60 What does the UPDATE statement do in SQL?
A. Deletes records
B. Modifies existing records
C. Inserts new records
D. Creates a table
Q.61 What’s wrong with this SQL command?
CREATE VIEW CustomerContacts AS SELECT CustomerID, ContactName, ContactEmail FROM Customers WHERE ContactEmail LIKE ‘%@%’;
A. The WHERE clause is invalid for a view
B. The LIKE operator is used incorrectly
C. There is no error
D. The view includes too many columns
Q.62 Identify the error in this view creation:
CREATE VIEW TotalOrders AS SELECT COUNT(*) FROM Orders;
A. Missing alias for COUNT(*)
B. Syntax is correct
C. Should use SUM instead of COUNT
D. View cannot contain aggregate functions
Q.63 How does the INFORMATION_SCHEMA.VIEWS table help users?
A. It shows the SQL statements for all views in the database
B. It displays metadata about each view in the database
C. It changes the definitions of views
D. It creates new views
Q.64 What is the purpose of the SQL command SHOW FULL TABLES WHERE Table_type = ‘VIEW’;?
A. To list all tables in the database
B. To display only the views in the database
C. To modify the type of tables to views
D. To delete views from the database
Q.65 What does this command do?
CREATE OR REPLACE VIEW CustomerInfo AS SELECT Name, Email FROM Customers WHERE Active = 1;
A. Replaces an existing view or creates a new one displaying certain customer info
B. Deletes the old CustomerInfo view and creates a new one
C. Alters the Customers table structure
D. None of the above
Q.66 What does the following SQL command do?
CREATE VIEW ActiveUsers AS SELECT * FROM Users WHERE Status = ‘Active’;
A. Creates a new table ActiveUsers with data from Users
B. Deletes inactive users from Users
C. Creates a view showing only active users from Users
D. Updates the status of all users in Users
Q.67 How do you restrict access to specific rows of a table through a view?
A. Using the WITH RESTRICT clause
B. By setting permissions on the base table
C. Using a WHERE clause in the view definition
D. Views cannot restrict row access
Q.68 Which statement is true about updatable views in MySQL?
A. All views are updatable
B. Only views created with the WITH CHECK OPTION are updatable
C. Views based on multiple tables cannot be updated
D. Views cannot be updated
Q.69 What is a view in MySQL?
A. A physical table in the database
B. A saved SQL query that can be treated as a table
C. A user interface for databases
D. A tool for database design
Q.70 What’s wrong with this trigger?
CREATE TRIGGER ValidateCredit BEFORE INSERT ON Orders FOR EACH ROW BEGIN IF NEW.Credit > 10000 THEN SET NEW.Credit = 10000; END IF; END;
A. It modifies data during a BEFORE trigger which is not allowed
B. It should use the AFTER keyword
C. The IF condition is incorrectly formulated
D. Syntax is correct
Q.71 Identify the mistake in this trigger definition:
CREATE TRIGGER AuditInsert AFTER INSERT INTO AuditRecords FOR EACH ROW BEGIN INSERT INTO AuditLog VALUES (NEW.RecordID, NOW()); END;
A. Syntax error in the INSERT statement
B. Incorrect trigger event specification
C. Missing END keyword
D. All are correct
Q.72 How does this trigger function?
CREATE TRIGGER SyncAudit BEFORE UPDATE ON Accounts FOR EACH ROW BEGIN UPDATE Audit SET Balance = NEW.Balance WHERE AccountID = OLD.AccountID; END;
A. Synchronizes balances in the Audit table when Accounts are updated
B. Creates a new account
C. Deletes old audit records
D. None of the above
Q.73 What action is performed by this SQL statement?
CREATE TRIGGER UpdateLog AFTER UPDATE ON Documents FOR EACH ROW BEGIN INSERT INTO ChangeLog (DocID, ChangedOn) VALUES (OLD.DocID, NOW()); END;
A. Logs changes to documents
B. Deletes old document records
C. Creates a backup of documents
D. None of the above
Q.74 What does the following SQL trigger do?
CREATE TRIGGER CheckAge BEFORE INSERT ON Employees FOR EACH ROW BEGIN IF NEW.Age < 18 THEN SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Employee too young’; END IF; END;
A. Prevents insertion of employees under 18
B. Updates age of new employees
C. Deletes records of young employees
D. Calculates average age of employees
Q.75 What is the limitation of a trigger in MySQL related to transaction control statements?
A. Triggers cannot execute SELECT statements
B. Triggers cannot include transaction control statements
C. Triggers can rollback transactions only
D. Triggers can commit transactions only
Q.76 Which of the following is NOT a valid event for a trigger in MySQL?
A. BEFORE UPDATE
B. AFTER DELETE
C. ON SELECT
D. AFTER INSERT
Q.77 When does a BEFORE INSERT trigger execute?
A. After the data is inserted into the table
B. Before the data is inserted into the table
C. During the data insertion
D. After the transaction commits
Q.78 What is a trigger in MySQL?
A. A command to start a transaction
B. A predefined action executed in response to an event
C. A scheduled database backup
D. A user privilege setting
Q.79 What is wrong with this SQL command?
CREATE PROCEDURE ResetLog() DELETE FROM LogEntries;
A. Incorrect procedure syntax
B. DELETE statement should include a WHERE clause
C. Should be a function
D. Syntax is correct
Q.80 Identify the error in this stored procedure creation:
CREATE PROCEDURE UpdateUser() BEGIN UPDATE Users SET age = age + 1; END;
A. Missing parameters for user identification
B. Syntax is correct
C. Unnecessary semicolon at END
D. Should use a function instead
Q.81 What is the impact of declaring a variable with the same name as a column in a stored procedure?
A. It leads to an error due to name conflict
B. It hides the column for the duration of the procedure
C. It automatically updates the column
D. It has no effect
Q.82 How do you execute a stored procedure named ‘CalculateDiscount’ that takes two parameters in MySQL?
A. EXECUTE CalculateDiscount;
B. CALL CalculateDiscount(100, 20);
C. RUN CalculateDiscount(100, 20);
D. LAUNCH CalculateDiscount(100, 20);
Q.83 What does this SQL command do:
CREATE FUNCTION GetCustomerLevel(p_credit DOUBLE) RETURNS VARCHAR(20) RETURN CASE WHEN p_credit > 50000 THEN ‘Platinum’ WHEN p_credit > 20000 THEN ‘Gold’ ELSE ‘Silver’ END;?
A. Creates a function that returns a customer’s level based on credit
B. Deletes a function
C. Modifies an existing function
D. None of the above
Q.84 What differentiates a deterministic function from a non-deterministic function in MySQL?
A. Deterministic functions return the same result any time they are called with a specific set of input values
B. Deterministic functions perform better
C. Non-deterministic functions cannot be indexed
D. Non-deterministic functions are faster
Q.85 How do stored procedures contribute to database security?
A. By restricting direct access to data
B. By encrypting data automatically
C. By logging user actions
D. By validating user inputs
Q.86 What is the purpose of the OUT parameter in a stored procedure?
A. To send data into a procedure
B. To return data from the procedure to the caller
C. To declare variable types
D. To specify optional parameters
Q.87 Which of the following is NOT a characteristic of functions in MySQL?
A. Can return only one value
B. Can be used in SQL expressions
C. Can perform updates on tables
D. Can be called from within SQL
Q.88 In MySQL, how can you pass a parameter to a stored procedure?
A. By using the IN keyword
B. By declaring it outside the procedure
C. By using the OUT keyword
D. By using the DECLARE keyword
Q.89 What is the primary advantage of using stored procedures?
A. Increased security
B. Reduced network traffic and higher performance
C. Automatic data backup
D. Simplified syntax
Q.90 What is a stored procedure in MySQL?
A. A collection of SQL queries executed as a single query
B. A user-defined function that stores data
C. A method to backup databases
D. A protocol for MySQL communication
Q.91 What is load balancing in the context of MySQL?
A. Distributing user requests across multiple servers
B. Splitting databases across servers
C. Replicating databases across multiple locations
D. Synchronizing data across servers
Q.92 What’s wrong with using the following command for a backup?
mysqldump -u root –all-databases –quick > backup.sql
A. The –quick option is not appropriate for all databases
B. No error
C. The command should not use redirection
D. The command lacks password authentication
Q.93 Identify the error in this backup command:
mysqldump -u root -p –all-databases > backup.sql
A. Incorrect redirection for output
B. Syntax error in options
C. No error
D. Incorrect user credentials
Q.94 What is the impact of enabling the –single-transaction option during a mysqldump of an InnoDB database?
A. Creates a new transaction for each table
B. Locks the entire database during the dump
C. Creates a consistent snapshot without locking tables
D. Disables logging during the dump
Q.95 How do you restore a MySQL database from a dump file created by mysqldump?
A. mysql -u user -p database < dump.sql
B. mysqldump -u user -p database < dump.sql
C. mysqladmin -u user -p database < dump.sql
D. mysqlrestore -u user -p database < dump.sql
Q.96 What does the following command do?
mysqladmin -u root -p flush-logs
A. Deletes all logs
B. Creates a new log file by closing and reopening the log files
C. Restarts the MySQL server
D. Changes log file permissions
Q.97 What is the main advantage of using physical backups over logical backups in MySQL?
A. Faster restoration times
B. More secure
C. Smaller backup files
D. Easier to configure
Q.98 Which statement is true about point-in-time recovery in MySQL?
A. It requires binary logging to be enabled
B. It can only restore data deleted by mistake
C. It’s only possible with mysqldump
D. It is automatically enabled in MySQL
Q.99 What type of backup involves copying only the data changed since the last full backup?
A. Incremental backup
B. Full backup
C. Mirror backup
D. Snapshot backup
Q.100 What is the purpose of the mysqldump utility?
A. To compress MySQL databases
B. To replicate databases
C. To back up MySQL databases
D. To optimize MySQL databases
Q.101 What’s wrong with using the following SQL statement for password management?
SET PASSWORD FOR ‘user’@’localhost’ = PASSWORD(‘open_sesame’);
A. Depreciated method for setting passwords
B. Syntax error
C. No error
D. Incorrect hostname usage
Q.102 Identify the error in this user creation SQL command:
CREATE USER ‘admin’@’localhost’ IDENTIFIED BY password;
A. Syntax is correct
B. Missing quotes around ‘password’
C. Wrong host specified
D. Password is too simple
Q.103 How does enabling the require_secure_transport option in MySQL enhance security?
A. Forces connections to use SSH
B. Forces connections to use SSL/TLS
C. Disables all database connections
D. Increases connection timeout
Q.104 What does this SQL command achieve?
REVOKE ALL PRIVILEGES ON database.* FROM ‘user’@’localhost’;
A. Grants all privileges to a user
B. Removes all privileges from a user for a database
C. Changes the user’s password
D. None of the above
Q.105 What is the primary benefit of implementing database auditing in MySQL?
A. To increase data retrieval speed
B. To monitor and log database activity
C. To reduce database size
D. To automatically fix database errors
Q.106 How can you restrict user access to specific columns in a MySQL table?
A. By setting column-level privileges
B. By deleting unwanted columns
C. By using table-level firewalls
D. By encrypting specific columns
Q.107 What does the MySQL SECURITY command do when applied to a view?
A. Changes the view’s charset
B. Sets who can access the view
C. Deletes the view after a certain period
D. Modifies the view’s collation setting
Q.108 What is the purpose of using SSL in MySQL connections?
A. To speed up the connection
B. To compress data before transmission
C. To encrypt data during transmission
D. To log connection activities
Q.109 What’s wrong with this transaction sequence?
START TRANSACTION; DELETE FROM Orders; COMMIT;
A. Missing WHERE clause in DELETE statement, potentially dangerous
B. No error
C. COMMIT should be ROLLBACK
D. DELETE statement syntax error
Q.110 Identify the error in this SQL transaction sequence:
BEGIN; UPDATE Account SET Balance = Balance – 100 WHERE ID = 1; COMMIT;
A. Incorrect use of BEGIN for starting a transaction
B. Syntax error in UPDATE statement
C. COMMIT does not save the changes
D. No error
Q.111 How do transactions affect performance in a database system?
A. They slow down database operations due to locking mechanisms
B. They generally speed up database operations
C. They have no impact on performance
D. They improve data integrity without affecting performance
Q.112 Consider this sequence of commands:
START TRANSACTION; INSERT INTO Orders (Product, Quantity) VALUES (‘Shoes’, 10); ROLLBACK;
What is the outcome?
A. The order is permanently added to the database
B. The order is not added to the database
C. The order data is corrupted
D. A new transaction is started
Q.113 What is the effect of this SQL command?
SET AUTOCOMMIT = 0;
A. Turns off automatic execution of statements
B. Turns on automatic saving of the transaction
C. Disables the transaction log
D. Turns off automatic committing of transactions
Q.114 How does a transaction ensure data integrity?
A. By locking the database
B. By allowing multiple users to modify data simultaneously
C. By ensuring all parts of the transaction succeed or fail together
D. By prioritizing transactions
Q.115 In which case would you use the SAVEPOINT statement in a transaction?
A. To save the transaction to disk
B. To mark a specific point within a transaction to which you later might rollback
C. To split the transaction
D. To end the transaction
Q.116 What is the role of the ROLLBACK statement in a transaction?
A. To duplicate the transaction
B. To save the transaction
C. To undo the transaction if an error occurs
D. To start over the database connection
Q.117 What does the COMMIT statement do?
A. Reverts the current transaction
B. Starts a new transaction
C. Saves the changes made by the current transaction
D. Checks the integrity of the transaction
Q.118 Which statement is used to start a transaction in MySQL?
A. BEGIN TRANSACTION
B. START TRANSACTION
C. INITIATE TRANSACTION
D. BEGIN
Q.119 What is a transaction in database management?
A. A group of SQL queries executed individually
B. A group of SQL queries executed as a single unit of work
C. A single SQL query
D. A schema in the database
Q.120 Spot the error in this SQL statement:
CREATE VIEW SalesSummary AS SELECT ProductID, SUM(Quantity) FROM Sales GROUP BY ProductID HAVING SUM(Quantity) > 100;
A. Missing alias for SUM(Quantity)
B. Syntax is correct
C. HAVING clause is unnecessary
D. Grouping by ProductID is not allowed
Q.121 What common issue might occur when connecting MySQL Workbench to a MySQL server?
A. Incorrect password or username
B. Syntax errors in queries
C. Corrupted installation files
D. Network connection timeout
Q.122 What does the ‘Performance Dashboard’ in MySQL Workbench display?
A. Current server status and system health
B. Historical data analysis
C. User session statistics
D. Detailed error logs
Q.123 In MySQL Workbench, how is a new schema created?
A. By executing CREATE SCHEMA mySchema; in the SQL editor
B. By selecting File > New Schema
C. By using the Schema Wizard
D. By right-clicking on the Schemas panel and selecting ‘Create Schema’
Q.124 What feature does MySQL Workbench provide for server administration?
A. Server startup and shutdown controls
B. Automatic patching
C. Cloud service integration
D. Real-time traffic monitoring
Q.125 How does MySQL Workbench assist in optimizing SQL queries?
A. By generating query syntax
B. By visually displaying query execution plans
C. By executing queries faster
D. By rewriting queries automatically
Q.126 What is MySQL Workbench primarily used for?
A. Database design and modeling
B. Query optimization
C. User management
D. Database replication
Q.127 Identify the issue in this JDBC URL:
jdbc:mysql://localhost:3306;databaseName=mydb;user=root;password=root;
A. Incorrect delimiter used
B. Port number is wrong
C. Password should not be included
D. User should be specified with a URL parameter
Q.128 What could be the issue if a Python script using SQLAlchemy cannot connect to a MySQL database?
A. Incorrect database URL format
B. Outdated Python version
C. Firewall blocking the connection
D. All are correct
Q.129 What is the purpose of using ORM (Object-Relational Mapping) tools like Sequelize with MySQL?
A. To allow SQL commands to be written in JavaScript
B. To convert MySQL queries to NoSQL
C. To map database schemas to application models
D. To encrypt database communication
Q.130 How do you execute a transaction in a Node.js application using the mysql library?
A. Using connection.beginTransaction();
B. Using mysql.startTransaction();
C. By automatically starting on any connection.query()
D. Using transaction.commit();
Q.131 What does this PHP line of code do?
$stmt = $pdo->prepare(‘SELECT * FROM users WHERE email = ?’);
A. Prepares a SQL statement for execution with a placeholder for email
B. Executes a query to find all users
C. Connects to the ‘users’ database
D. Logs a user in based on email
Q.132 In the context of connecting to MySQL from a Node.js application, what is an advantage of using the mysql2 library over mysql?
A. Improved performance and prepared statements support
B. Only supports older MySQL versions
C. Includes automatic data encryption
D. Is easier to configure
Q.133 When integrating MySQL with PHP, what does the PDO extension provide that the older mysql extension does not?
A. Asynchronous query execution
B. Native JSON support
C. Support for multiple databases
D. Stored procedure debugging
Q.134 How does JDBC handle MySQL transactions in Java applications?
A. By using auto-commit mode by default
B. By disabling transactions
C. By manual locking mechanisms
D. By using only prepared statements
Q.135 What is a common method to connect a Python application to a MySQL database?
A. Using the PyMySQL library
B. Using the direct TCP/IP connection
C. Using SSH tunneling
D. Using XML configurations
Q.136 What’s problematic about setting up replication with the following configuration:
binlog_format=MIXED; in a cluster that supports only row-based logging?
A. Mixed format can cause errors with some queries
B. The configuration is fine
C. Row-based logging is not supported in clusters
D. Mixed format is less secure than row-based
Q.137 Identify the flaw in this replication setup command:
SET GLOBAL gtid_slave_pos=’3E11-2343-23EE-2445′; START SLAVE;
A. GTID format is incorrect
B. There’s no error; the command is correct
C. GTID should be set at the session level, not globally
D. It uses an outdated command structure
Q.138 In MySQL Cluster, what role does the ndb_mgmd daemon play?
A. Manages memory allocation for the cluster
B. Acts as the primary data node
C. Provides connection pooling services
D. Manages cluster configuration and node management
Q.139 How do you configure a MySQL server to act as a slave in a replication topology?
A. Use the SLAVE START command
B. Modify the my.cnf file to include master server details
C. Use the SERVER SLAVE command
D. Execute REPLICATE START on the database
Q.140 What does the following command do in the context of MySQL replication?
CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=107;
A. Changes the replication master to a new server
B. Updates where the slave should start reading the master’s binary log
C. Stops replication
D. Resets the master’s binary log
Q.141 Which type of replication is preferred for high fault tolerance in MySQL Cluster?
A. Asynchronous replication
B. Synchronous replication
C. Semi-synchronous replication
D. Delayed replication
Q.142 How does MySQL Cluster (NDB) handle data partitioning?
A. Automatically partitions data across all data nodes
B. Manually partitions data based on user settings
C. Does not support data partitioning
D. Only partitions data on request
Q.143 What is the difference between asynchronous and synchronous replication in MySQL?
A. Asynchronous waits for an event; synchronous does not
B. Asynchronous is faster; synchronous is slower
C. Asynchronous does not guarantee data consistency; synchronous does
D. Asynchronous uses more resources; synchronous uses less
Q.144 What is a potential risk when using the LOAD DATA INFILE statement in a replication setup?
A. It can lock the table on the master
B. It might not replicate to the slave under certain configurations
C. It increases the load on the network
D. It is not supported in clustered environments
Q.145 Identify the issue with this replication setup command:
SET GLOBAL sql_slave_skip_counter = 2; START SLAVE;
A. The slave counter should not be set globally
B. This command should be followed by a server restart
C. It skips error events in replication, which can lead to data inconsistency
D. All are correct
Q.146 How does the max_connections setting affect a MySQL server?
A. Limits the number of open transactions
B. Limits the number of database users
C. Sets the maximum number of allowable connections
D. Restricts query execution time
Q.147 What does the following MySQL command achieve?
CHANGE MASTER TO MASTER_HOST=’master2′, MASTER_LOG_FILE=’log-bin.000012′, MASTER_LOG_POS=123;
A. Initiates a new master server
B. Changes replication settings to a new master log
C. Restarts the MySQL server
D. Updates the server configuration
Q.148 In MySQL, what does sharding help with?
A. Data recovery
B. Data archiving
C. Data scalability
D. Data encryption
Q.149 What is the primary benefit of using Galera Cluster over standard MySQL replication?
A. Faster data replication
B. Automatic node provisioning
C. Synchronous replication and automatic conflict resolution
D. Manual switching during failures
Q.150 How does MySQL Cluster achieve high availability?
A. By using shared storage
B. By using disk mirroring
C. By synchronously replicating data across nodes in real-time
D. By periodically backing up data
