SECTION 1: MYSQL INSTALLATION AND DATABASE MANAGEMENT
1. How do you check the installed MySQL version?
Answer:
SELECT VERSION();From the Linux terminal:
mysql --version2. How do you connect to MySQL from the terminal?
Answer:
mysql -u root -p3. How do you connect to a MySQL server on a particular host and port?
Answer:
mysql -h 192.168.1.10 -P 3306 -u root -p4. How do you display all databases?
Answer:
SHOW DATABASES;5. How do you create a database?
Answer:
CREATE DATABASE government_db;6. How do you create a database using UTF-8 character encoding?
Answer:
CREATE DATABASE government_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;The collation shown is suitable for MySQL 8.0; choose a supported collation for the installed version.
7. How do you select a database?
Answer:
USE government_db;8. How do you display the currently selected database?
Answer:
SELECT DATABASE();9. How do you delete a database?
Answer:
DROP DATABASE government_db;This permanently removes the database and its objects. Verify the target and backup before execution.
10. How do you create a database if it does not already exist?
Answer:
CREATE DATABASE IF NOT EXISTS government_db;11. How do you rename a database in MySQL?
Answer: MySQL does not provide a general RENAME DATABASE statement. A migration is normally performed by creating the destination database, moving or exporting the objects and data, validating them, and then retiring the old database.
12. How do you display the tables in a database?
Answer:
SHOW TABLES;13. How do you display the structure of a table?
Answer:
DESCRIBE employees;14. How do you display the CREATE TABLE statement?
Answer:
SHOW CREATE TABLE employees;15. How do you display the current MySQL server time?
Answer:
SELECT NOW();SECTION 2: TABLE CREATION AND CONSTRAINTS
16. How do you create an employees table?
Answer:
CREATE TABLE employees ( employee_id INT AUTO_INCREMENT PRIMARY KEY, employee_number VARCHAR(30) NOT NULL UNIQUE, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, department VARCHAR(100), salary DECIMAL(12,2) NOT NULL DEFAULT 0, hire_date DATE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB;17. How do you create a departments table?
Answer:
CREATE TABLE departments ( department_id INT AUTO_INCREMENT PRIMARY KEY, department_name VARCHAR(100) NOT NULL UNIQUE, location VARCHAR(100) ) ENGINE=InnoDB;18. How do you create a foreign key between employees and departments?
Answer:
CREATE TABLE employees ( employee_id INT AUTO_INCREMENT PRIMARY KEY, employee_number VARCHAR(30) NOT NULL UNIQUE, first_name VARCHAR(80) NOT NULL, department_id INT, CONSTRAINT fk_employee_department FOREIGN KEY (department_id) REFERENCES departments(department_id) ) ENGINE=InnoDB;19. How do you create a table with a composite primary key?
Answer:
CREATE TABLE employee_courses ( employee_id INT NOT NULL, course_id INT NOT NULL, enrollment_date DATE, PRIMARY KEY (employee_id, course_id) ) ENGINE=InnoDB;20. How do you create a table with a CHECK constraint?
Answer:
CREATE TABLE payroll ( payroll_id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, salary DECIMAL(12,2) NOT NULL, CONSTRAINT chk_salary CHECK (salary >= 0) ) ENGINE=InnoDB;21. How do you add a column to an existing table?
Answer:
ALTER TABLE employees ADD COLUMN email VARCHAR(150);22. How do you modify a column's data type?
Answer:
ALTER TABLE employees MODIFY COLUMN first_name VARCHAR(120) NOT NULL;Check existing data and dependent applications before changing a production column.
23. How do you rename a column?
Answer:
ALTER TABLE employees RENAME COLUMN first_name TO given_name;24. How do you drop a column?
Answer:
ALTER TABLE employees DROP COLUMN email;25. How do you add a unique constraint?
Answer:
ALTER TABLE employees ADD CONSTRAINT uq_employee_email UNIQUE (email);SECTION 3: MYSQL INSERT, SELECT AND FILTERING
26. How do you insert one employee record?
Answer:
INSERT INTO employees ( employee_number, first_name, last_name, department, salary, hire_date ) VALUES ( 'EMP001', 'Asha', 'Mwakalinga', 'ICT', 1500000, '2026-01-10' );27. How do you insert multiple employee records?
Answer:
INSERT INTO employees ( employee_number, first_name, last_name, department, salary ) VALUES ('EMP002', 'John', 'Mushi', 'Finance', 1800000), ('EMP003', 'Neema', 'Joseph', 'ICT', 2000000), ('EMP004', 'Peter', 'Massawe', 'HR', 1700000);28. How do you retrieve all employees?
Answer:
SELECT * FROM employees;29. How do you retrieve selected employee columns?
Answer:
SELECT employee_number, first_name, last_name, salary FROM employees;30. How do you retrieve employees from the ICT department?
Answer:
SELECT * FROM employees WHERE department = 'ICT';31. How do you retrieve employees earning more than 1,500,000?
Answer:
SELECT * FROM employees WHERE salary > 1500000;32. How do you retrieve employees earning between 1,000,000 and 2,000,000?
Answer:
SELECT * FROM employees WHERE salary BETWEEN 1000000 AND 2000000;33. How do you retrieve employees from ICT or Finance?
Answer:
SELECT * FROM employees WHERE department IN ('ICT', 'Finance');34. How do you retrieve employees whose names start with A?
Answer:
SELECT * FROM employees WHERE first_name LIKE 'A%';35. How do you retrieve employees whose names contain the letters "sha"?
Answer:
SELECT * FROM employees WHERE first_name LIKE '%sha%';36. How do you retrieve employees whose email is NULL?
Answer:
SELECT * FROM employees WHERE email IS NULL;37. How do you retrieve employees whose email is not NULL?
Answer:
SELECT * FROM employees WHERE email IS NOT NULL;38. How do you sort employees by salary from highest to lowest?
Answer:
SELECT * FROM employees ORDER BY salary DESC;39. How do you retrieve the five highest-paid employees?
Answer:
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;40. How do you retrieve employees hired during 2026?
Answer:
SELECT * FROM employees WHERE hire_date >= '2026-01-01' AND hire_date < '2027-01-01';SECTION 4: MYSQL UPDATE, DELETE AND AGGREGATION
41. How do you update an employee's salary?
Answer:
UPDATE employees SET salary = 2000000 WHERE employee_number = 'EMP001';42. How do you increase all ICT employees' salaries by 10%?
Answer:
UPDATE employees SET salary = salary * 1.10 WHERE department = 'ICT';Verify the affected records and obtain approval before executing a bulk salary update.
43. How do you delete one employee?
Answer:
DELETE FROM employees WHERE employee_number = 'EMP004';44. How do you count all employees?
Answer:
SELECT COUNT(*) AS total_employees FROM employees;45. How do you calculate the total salary expenditure?
Answer:
SELECT SUM(salary) AS total_salary FROM employees;46. How do you calculate average salary?
Answer:
SELECT AVG(salary) AS average_salary FROM employees;47. How do you find the highest and lowest salaries?
Answer:
SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary FROM employees;48. How do you count employees in each department?
Answer:
SELECT department, COUNT(*) AS total_employees FROM employees GROUP BY department;49. How do you retrieve departments with more than five employees?
Answer:
SELECT department, COUNT(*) AS total_employees FROM employees GROUP BY department HAVING COUNT(*) > 5;50. How do you remove all records from a table while keeping its structure?
Answer:
TRUNCATE TABLE employees;TRUNCATE removes all records and resets the AUTO_INCREMENT counter in normal MySQL usage. It is a DDL operation and causes an implicit commit.
SECTION 5: JOINS AND SUBQUERIES
51. How do you retrieve employees with their department names using INNER JOIN?
Answer:
SELECT e.employee_number, e.first_name, e.last_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;52. How do you retrieve all employees, including those without a department?
Answer:
SELECT e.employee_number, e.first_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;53. How do you retrieve departments and their employees using RIGHT JOIN?
Answer:
SELECT d.department_name, e.first_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;54. How do you find employees who are not assigned to any department?
Answer:
SELECT e.* FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.department_id IS NULL;55. How do you retrieve employees and their managers using SELF JOIN?
Answer:
Assume the employees table contains manager_id:
56. How do you retrieve employees earning more than the average salary?
Answer:
SELECT * FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees );57. How do you find the second-highest salary?
Answer:
SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < ( SELECT MAX(salary) FROM employees );58. How do you retrieve employees earning the maximum salary?
Answer:
SELECT * FROM employees WHERE salary = ( SELECT MAX(salary) FROM employees );59. How do you find employees working in the same department as Asha?
Answer:
SELECT * FROM employees WHERE department_id IN ( SELECT department_id FROM employees WHERE first_name = 'Asha' );60. How do you retrieve departments that have at least one employee?
Answer:
SELECT * FROM departments d WHERE EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id );SECTION 6: MYSQL INDEXES AND QUERY OPTIMIZATION
61. How do you create an index on employee_number?
Answer:
CREATE INDEX idx_employee_number ON employees(employee_number);62. How do you create a unique index on email?
Answer:
CREATE UNIQUE INDEX uq_employee_email ON employees(email);63. How do you create a composite index?
Answer:
CREATE INDEX idx_department_salary ON employees(department_id, salary);64. How do you display indexes on a table?
Answer:
SHOW INDEX FROM employees;65. How do you drop an index?
Answer:
DROP INDEX idx_department_salary ON employees;66. How do you inspect a query execution plan?
Answer:
EXPLAIN SELECT * FROM employees WHERE department_id = 1;67. How do you analyze actual query execution?
Answer:
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 1;This executes the SELECT statement and reports actual execution statistics.
68. How do you update table statistics?
Answer:
ANALYZE TABLE employees;69. How do you optimize an InnoDB table?
Answer:
OPTIMIZE TABLE employees;Use this only when justified by the table's storage and maintenance requirements.
70. How do you identify the size of a table?
Answer:
SELECT table_name, ROUND( (data_length + index_length) / 1024 / 1024, 2 ) AS size_mb FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'employees';SECTION 7: MYSQL TRANSACTIONS AND LOCKING
71. How do you start a transaction?
Answer:
START TRANSACTION;72. How do you commit a transaction?
Answer:
COMMIT;73. How do you roll back a transaction?
Answer:
ROLLBACK;74. How do you create a savepoint?
Answer:
START TRANSACTION; UPDATE employees SET salary = salary + 100000 WHERE employee_id = 1; SAVEPOINT salary_update; UPDATE employees SET salary = salary + 200000 WHERE employee_id = 2; ROLLBACK TO SAVEPOINT salary_update; COMMIT;75. How do you demonstrate a bank transfer using a transaction?
Answer:
Assume a bank_accounts table with account_id and balance:
In a real implementation, check the affected-row count of the debit and credit operations. If either fails, roll back the transaction. Use suitable locking and validation to prevent invalid transfers.
76. How do you lock selected rows for update?
Answer:
START TRANSACTION; SELECT * FROM bank_accounts WHERE account_id = 1 FOR UPDATE;The lock is held until the transaction commits or rolls back.
77. How do you check the transaction isolation level?
Answer:
SELECT @@transaction_isolation;78. How do you set the session isolation level to READ COMMITTED?
Answer:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;79. How do you check InnoDB status?
Answer:
SHOW ENGINE INNODB STATUS;80. How do you check current running processes?
Answer:
SHOW FULL PROCESSLIST;SECTION 8: STORED PROCEDURES AND FUNCTIONS
81. How do you create a simple stored procedure?
Answer:
DELIMITER // CREATE PROCEDURE get_employees() BEGIN SELECT * FROM employees; END // DELIMITER ;82. How do you execute a stored procedure?
Answer:
CALL get_employees();83. How do you create a procedure that accepts an employee ID?
Answer:
DELIMITER // CREATE PROCEDURE get_employee_by_id( IN p_employee_id INT ) BEGIN SELECT * FROM employees WHERE employee_id = p_employee_id; END // DELIMITER ;84. How do you call the procedure with an employee ID?
Answer:
CALL get_employee_by_id(1);85. How do you create a stored procedure with an OUT parameter?
Answer:
DELIMITER // CREATE PROCEDURE count_employees( OUT p_total INT ) BEGIN SELECT COUNT(*) INTO p_total FROM employees; END // DELIMITER ;86. How do you retrieve the OUT parameter value?
Answer:
CALL count_employees(@total); SELECT @total AS total_employees;87. How do you create a stored function that calculates annual salary?
Answer:
DELIMITER // CREATE FUNCTION annual_salary( p_monthly_salary DECIMAL(12,2) ) RETURNS DECIMAL(14,2) DETERMINISTIC NO SQL BEGIN RETURN p_monthly_salary * 12; END // DELIMITER ;88. How do you call the annual salary function?
Answer:
SELECT annual_salary(1500000);89. How do you create a function that checks whether a salary is valid?
Answer:
DELIMITER // CREATE FUNCTION is_valid_salary( p_salary DECIMAL(12,2) ) RETURNS BOOLEAN DETERMINISTIC NO SQL BEGIN RETURN p_salary >= 0; END // DELIMITER ;90. How do you display stored procedures and functions?
Answer:
SHOW PROCEDURE STATUS WHERE Db = DATABASE(); SHOW FUNCTION STATUS WHERE Db = DATABASE();SECTION 9: TRIGGERS AND EVENTS
91. How do you create an audit table?
Answer:
CREATE TABLE employee_audit ( audit_id BIGINT AUTO_INCREMENT PRIMARY KEY, employee_id INT, action_type VARCHAR(20), changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB;92. How do you create an AFTER INSERT trigger?
Answer:
DELIMITER // CREATE TRIGGER after_employee_insert AFTER INSERT ON employees FOR EACH ROW BEGIN INSERT INTO employee_audit ( employee_id, action_type ) VALUES ( NEW.employee_id, 'INSERT' ); END // DELIMITER ;93. How do you create a BEFORE UPDATE trigger?
Answer:
DELIMITER // CREATE TRIGGER before_employee_update BEFORE UPDATE ON employees FOR EACH ROW BEGIN IF NEW.salary < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative'; END IF; END // DELIMITER ;94. How do you create an AFTER DELETE trigger?
Answer:
DELIMITER // CREATE TRIGGER after_employee_delete AFTER DELETE ON employees FOR EACH ROW BEGIN INSERT INTO employee_audit ( employee_id, action_type ) VALUES ( OLD.employee_id, 'DELETE' ); END // DELIMITER ;95. How do you list triggers?
Answer:
SHOW TRIGGERS;96. How do you delete a trigger?
Answer:
DROP TRIGGER IF EXISTS after_employee_insert;97. How do you enable the MySQL Event Scheduler?
Answer:
SET GLOBAL event_scheduler = ON;The setting may also be configured persistently in the server configuration file.
98. How do you create an event that runs daily?
Answer:
CREATE EVENT daily_employee_check ON SCHEDULE EVERY 1 DAY DO INSERT INTO system_logs(message, created_at) VALUES ('Daily employee check', NOW());This assumes system_logs has the specified columns.
99. How do you list scheduled events?
Answer:
SHOW EVENTS;100. How do you delete a scheduled event?
Answer:
DROP EVENT IF EXISTS daily_employee_check;SECTION 10: MYSQL VIEWS AND ADVANCED SQL
101. How do you create a view of ICT employees?
Answer:
CREATE VIEW ict_employees AS SELECT employee_id, employee_number, first_name, last_name, salary FROM employees WHERE department = 'ICT';102. How do you retrieve records from a view?
Answer:
SELECT * FROM ict_employees;103. How do you update a view?
Answer:
UPDATE ict_employees SET salary = 2200000 WHERE employee_id = 1;This works only if the view is updatable and the operation satisfies its underlying table constraints.
104. How do you replace an existing view?
Answer:
CREATE OR REPLACE VIEW ict_employees AS SELECT employee_id, employee_number, first_name, last_name, salary FROM employees WHERE department = 'ICT';105. How do you delete a view?
Answer:
DROP VIEW IF EXISTS ict_employees;106. How do you create a temporary table?
Answer:
CREATE TEMPORARY TABLE temporary_employees ( employee_id INT, employee_name VARCHAR(100), salary DECIMAL(12,2) );107. How do you insert data into a temporary table?
Answer:
INSERT INTO temporary_employees VALUES (1, 'Asha Mwakalinga', 1500000);108. How do you create a recursive CTE to generate numbers from 1 to 10?
Answer:
WITH RECURSIVE numbers AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < 10 ) SELECT * FROM numbers;109. How do you use ROW_NUMBER() to number employees?
Answer:
SELECT employee_id, first_name, salary, ROW_NUMBER() OVER ( ORDER BY salary DESC ) AS row_num FROM employees;110. How do you rank employees by salary?
Answer:
SELECT employee_id, first_name, salary, RANK() OVER ( ORDER BY salary DESC ) AS salary_rank FROM employees;SECTION 11: MYSQL JSON OPERATIONS
111. How do you create a table with a JSON column?
Answer:
CREATE TABLE employee_profiles ( profile_id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT, profile_data JSON );112. How do you insert JSON data?
Answer:
INSERT INTO employee_profiles ( employee_id, profile_data ) VALUES ( 1, '{"skills":["SQL","MySQL"],"city":"Dodoma"}' );113. How do you retrieve a JSON property?
Answer:
SELECT profile_data->'$.city' AS city FROM employee_profiles;114. How do you retrieve a JSON property as text?
Answer:
SELECT profile_data->>'$.city' AS city FROM employee_profiles;115. How do you check whether a JSON document contains a particular value?
Answer:
SELECT * FROM employee_profiles WHERE JSON_CONTAINS( profile_data, '"SQL"', '$.skills' );116. How do you update a JSON property?
Answer:
UPDATE employee_profiles SET profile_data = JSON_SET( profile_data, '$.city', 'Arusha' ) WHERE employee_id = 1;117. How do you remove a JSON property?
Answer:
UPDATE employee_profiles SET profile_data = JSON_REMOVE( profile_data, '$.city' ) WHERE employee_id = 1;118. How do you add a value to a JSON array?
Answer:
UPDATE employee_profiles SET profile_data = JSON_ARRAY_APPEND( profile_data, '$.skills', 'Oracle' ) WHERE employee_id = 1;119. How do you count elements in a JSON array?
Answer:
SELECT JSON_LENGTH( profile_data, '$.skills' ) AS skill_count FROM employee_profiles;120. How do you create an index on a JSON property?
Answer:
Use a generated column:
SECTION 12: MYSQL USERS AND PRIVILEGES
121. How do you create a MySQL user?
Answer:
CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'Use-A-Strong-Unique-Password';Use a secure password manager rather than placing production passwords in scripts.
122. How do you create a user who can connect from another host?
Answer:
CREATE USER 'app_user'@'192.168.1.20' IDENTIFIED BY 'Use-A-Strong-Unique-Password';123. How do you grant SELECT permission?
Answer:
GRANT SELECT ON government_db.* TO 'report_user'@'localhost';124. How do you grant INSERT, UPDATE and DELETE permissions?
Answer:
GRANT INSERT, UPDATE, DELETE ON government_db.* TO 'app_user'@'localhost';125. How do you grant all privileges on one database?
Answer:
GRANT ALL PRIVILEGES ON government_db.* TO 'app_user'@'localhost';Avoid granting unnecessary privileges to application accounts.
126. How do you display a user's privileges?
Answer:
SHOW GRANTS FOR 'report_user'@'localhost';127. How do you revoke UPDATE permission?
Answer:
REVOKE UPDATE ON government_db.* FROM 'app_user'@'localhost';128. How do you change a MySQL user's password?
Answer:
ALTER USER 'report_user'@'localhost' IDENTIFIED BY 'New-Strong-Unique-Password';129. How do you remove a MySQL user?
Answer:
DROP USER 'report_user'@'localhost';130. How do you create a MySQL role?
Answer:
CREATE ROLE 'database_reader';SECTION 13: MYSQL BACKUP AND RESTORATION
131. How do you back up one MySQL database using mysqldump?
Answer:
mysqldump -u root -p government_db > government_db.sql132. How do you back up all MySQL databases?
Answer:
mysqldump -u root -p --all-databases > all_databases.sql133. How do you back up only the database structure?
Answer:
mysqldump -u root -p --no-data government_db > schema.sql134. How do you back up only the data?
Answer:
mysqldump -u root -p --no-create-info government_db > data.sql135. How do you restore a database backup?
Answer:
First create the target database:
Then restore:
mysql -u root -p government_restore < government_db.sql136. How do you back up a single table?
Answer:
mysqldump -u root -p government_db employees > employees.sql137. How do you restore a single table backup?
Answer:
mysql -u root -p government_db < employees.sql138. How do you create a compressed backup?
Answer:
mysqldump -u root -p government_db | gzip > government_db.sql.gz139. How do you restore a compressed backup?
Answer:
gunzip -c government_db.sql.gz | mysql -u root -p government_db140. How do you check the binary logging status?
Answer:
SHOW VARIABLES LIKE 'log_bin';SECTION 14: MYSQL REPLICATION AND MONITORING
141. How do you check replication status on a replica?
Answer:
SHOW REPLICA STATUS\GThis is the modern terminology in MySQL 8.0. Older versions use SHOW SLAVE STATUS.
142. How do you check the source's binary log files?
Answer:
SHOW BINARY LOGS;143. How do you display the current binary log position?
Answer:
SHOW BINARY LOG STATUS;On older versions, use SHOW MASTER STATUS.
144. How do you inspect MySQL server variables?
Answer:
SHOW VARIABLES;145. How do you inspect MySQL server status counters?
Answer:
SHOW GLOBAL STATUS;146. How do you check the maximum connection limit?
Answer:
SHOW VARIABLES LIKE 'max_connections';147. How do you check current connection usage?
Answer:
SHOW GLOBAL STATUS LIKE 'Threads_connected';148. How do you check database size?
Answer:
SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables GROUP BY table_schema;149. How do you identify the largest tables?
Answer:
SELECT table_schema, table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables WHERE table_schema = 'government_db' ORDER BY data_length + index_length DESC LIMIT 10;150. How do you inspect the MySQL error log location?
Answer:
SHOW VARIABLES LIKE 'log_error';SECTION 15: MYSQL LINUX ADMINISTRATION
151. How do you check whether MySQL is running on Linux?
Answer:
sudo systemctl status mysqlSome Linux distributions use the service name mysqld.
152. How do you start MySQL?
Answer:
sudo systemctl start mysql153. How do you stop MySQL?
Answer:
sudo systemctl stop mysql154. How do you restart MySQL?
Answer:
sudo systemctl restart mysql155. How do you enable MySQL to start automatically after reboot?
Answer:
sudo systemctl enable mysql156. How do you inspect the MySQL service logs?
Answer:
sudo journalctl -u mysql -n 100157. How do you check whether port 3306 is listening?
Answer:
sudo ss -lntp | grep 3306158. How do you check the MySQL process?
Answer:
ps aux | grep mysqld159. How do you check disk space on a Linux database server?
Answer:
df -h160. How do you check MySQL data-directory configuration?
Answer:
SHOW VARIABLES LIKE 'datadir';SECTION 16: ADVANCED MYSQL PRACTICAL OPERATIONS
161. How do you create a partitioned table by year?
Answer:
CREATE TABLE yearly_logs ( log_id BIGINT NOT NULL, log_date DATE NOT NULL, message TEXT, PRIMARY KEY (log_id, log_date) ) PARTITION BY RANGE (YEAR(log_date)) ( PARTITION p2025 VALUES LESS THAN (2026), PARTITION p2026 VALUES LESS THAN (2027), PARTITION pmax VALUES LESS THAN MAXVALUE );162. How do you add a new partition?
Answer:
For a RANGE partitioned table, split the MAXVALUE partition:
163. How do you create a table with a generated column?
Answer:
CREATE TABLE products ( product_id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(100), unit_price DECIMAL(12,2), quantity INT, total_value DECIMAL(14,2) GENERATED ALWAYS AS (unit_price * quantity) STORED );164. How do you create a full-text index?
Answer:
CREATE TABLE articles ( article_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200), content TEXT, FULLTEXT INDEX ft_article_content (title, content) ) ENGINE=InnoDB;165. How do you search using a full-text index?
Answer:
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('database administration' IN NATURAL LANGUAGE MODE);166. How do you create a spatial table?
Answer:
CREATE TABLE office_locations ( office_id INT AUTO_INCREMENT PRIMARY KEY, office_name VARCHAR(100), location POINT NOT NULL SRID 4326, SPATIAL INDEX idx_location (location) ) ENGINE=InnoDB;167. How do you insert geographic coordinates?
Answer:
INSERT INTO office_locations ( office_name, location ) VALUES ( 'Dodoma Office', ST_GeomFromText('POINT(35.7516 -6.1630)', 4326) );168. How do you retrieve geographic coordinates?
Answer:
SELECT office_name, ST_Longitude(location) AS longitude, ST_Latitude(location) AS latitude FROM office_locations;169. How do you create a temporary table from an existing table?
Answer:
CREATE TEMPORARY TABLE ict_employees AS SELECT * FROM employees WHERE department = 'ICT';170. How do you copy a table structure without copying its records?
Answer:
CREATE TABLE employees_backup LIKE employees;SECTION 17: MYSQL DATA IMPORT AND EXPORT
171. How do you export a query result to a CSV file?
Answer:
SELECT employee_number, first_name, salary FROM employees INTO OUTFILE '/var/lib/mysql-files/employees.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';The MySQL server must have permission to write to the destination, and secure_file_priv may restrict the directory.
172. How do you import CSV data using LOAD DATA INFILE?
Answer:
LOAD DATA INFILE '/var/lib/mysql-files/employees.csv' INTO TABLE employees FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (employee_number, first_name, salary);173. How do you import a CSV file from the client machine?
Answer:
LOAD DATA LOCAL INFILE 'employees.csv' INTO TABLE employees FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (employee_number, first_name, salary);LOCAL INFILE must be enabled and allowed by the client and server configuration.
174. How do you check the secure_file_priv setting?
Answer:
SHOW VARIABLES LIKE 'secure_file_priv';175. How do you check database character sets?
Answer:
SHOW VARIABLES LIKE 'character_set%';SECTION 18: MYSQL TROUBLESHOOTING PRACTICAL SCENARIOS
176. How do you identify duplicate email addresses?
Answer:
SELECT email, COUNT(*) AS total FROM employees WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1;177. How do you find records without a matching department?
Answer:
SELECT e.* FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE e.department_id IS NOT NULL AND d.department_id IS NULL;178. How do you identify employees with missing names?
Answer:
SELECT * FROM employees WHERE first_name IS NULL OR TRIM(first_name) = '';179. How do you identify tables that use MyISAM?
Answer:
SELECT table_name, engine FROM information_schema.tables WHERE table_schema = DATABASE() AND engine = 'MyISAM';180. How do you convert a MyISAM table to InnoDB?
Answer:
ALTER TABLE employees ENGINE = InnoDB;Review the table structure and application behavior before converting production tables.
SECTION 19: MYSQL SECURITY AND RECOVERY SCENARIOS
181. How do you check whether binary logging is enabled?
Answer:
SHOW VARIABLES LIKE 'log_bin';182. How do you check the current GTID execution state?
Answer:
SELECT @@GLOBAL.gtid_executed;183. How do you check whether a user requires SSL?
Answer:
SHOW CREATE USER 'app_user'@'localhost';184. How do you require encrypted connections for a user?
Answer:
ALTER USER 'app_user'@'localhost' REQUIRE SSL;The client must connect using TLS for authentication and access to succeed.
185. How do you check whether a user account is locked?
Answer:
SELECT user, host, account_locked FROM mysql.user;Access to this system table requires appropriate privileges.
186. How do you lock a MySQL user account?
Answer:
ALTER USER 'app_user'@'localhost' ACCOUNT LOCK;187. How do you unlock a MySQL user account?
Answer:
ALTER USER 'app_user'@'localhost' ACCOUNT UNLOCK;188. How do you identify the current MySQL user?
Answer:
SELECT USER(), CURRENT_USER();USER() reports the supplied connection identity; CURRENT_USER() reports the account MySQL uses for privilege checking.
189. How do you check the SQL mode?
Answer:
SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;190. How do you check the MySQL time zone?
Answer:
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone, @@system_time_zone;SECTION 20: COMPLETE DATABASE ADMINISTRATION TASKS
191. How do you create a database backup using MySQL Workbench?
Answer: Open MySQL Workbench, connect to the server, select Server, Data Export, choose the database and objects, select the export method and destination, and start the export. Verify the resulting backup file.
192. How do you restore a database using MySQL Workbench?
Answer: Connect to the server, select Server, Data Import, choose the dump file or folder, select the destination schema, configure the import options and start the import. Validate the restored tables and records.
193. How do you identify the top ten largest tables in the server?
Answer:
SELECT table_schema, table_name, ROUND( (data_length + index_length) / 1024 / 1024, 2 ) AS size_mb FROM information_schema.tables WHERE table_schema NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'sys' ) ORDER BY data_length + index_length DESC LIMIT 10;194. How do you identify tables without primary keys?
Answer:
SELECT t.table_schema, t.table_name FROM information_schema.tables t LEFT JOIN information_schema.table_constraints c ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND c.constraint_type = 'PRIMARY KEY' WHERE t.table_schema = DATABASE() AND t.table_type = 'BASE TABLE' AND c.constraint_name IS NULL;195. How do you identify unused or rarely used indexes?
Answer:
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = DATABASE();Review the observation period and workload before removing any index. Some indexes may be used infrequently but remain important for critical operations.
196. How do you identify foreign-key relationships in a database?
Answer:
SELECT table_name, column_name, constraint_name, referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE table_schema = DATABASE() AND referenced_table_name IS NOT NULL;197. How do you check the MySQL server uptime?
Answer:
SHOW GLOBAL STATUS LIKE 'Uptime';198. How do you inspect the InnoDB deadlock information?
Answer:
SHOW ENGINE INNODB STATUS;Inspect the LATEST DETECTED DEADLOCK section.
199. How do you perform a basic MySQL database health assessment?
Answer: I would inspect server uptime, error logs, connection usage, disk capacity, InnoDB status, slow queries, replication status, table sizes, indexes and backup completion. I would also run application-level tests.
200. How would you demonstrate complete MySQL database administration in a practical interview?
Answer: I would create a database, design tables with keys and constraints, insert and retrieve records, demonstrate joins and subqueries, create indexes, write stored procedures and triggers, manage users and privileges, execute transactions, back up and restore the database, inspect server status and troubleshoot a sample performance problem.
QUESTIONS 1–25
1. Install MySQL on Ubuntu
Answer: sudo apt update
sudo apt install mysql-server -y
sudo systemctl enable --now mysql
2. Install MySQL on Windows
Answer: Run the official MySQL Installer; select Server and Workbench; configure port, service and secure administrator credentials; test connection.
3. Check MySQL version
Answer: SELECT VERSION();
-- CLI: mysql --version
4. Start MySQL service
Answer: sudo systemctl start mysql
5. Stop MySQL service
Answer: sudo systemctl stop mysql
6. Restart MySQL service
Answer: sudo systemctl restart mysql
7. Check MySQL service status
Answer: sudo systemctl status mysql
8. Connect locally
Answer: mysql -u root -p
9. Connect remotely
Answer: mysql -h 192.168.1.100 -P 3306 -u admin -p
10. Exit the MySQL client
Answer: EXIT;
11. List databases
Answer: SHOW DATABASES;
12. Select a database
Answer: USE company_db;
13. List tables
Answer: SHOW TABLES;
14. Describe a table
Answer: DESCRIBE employees;
15. Show table DDL
Answer: SHOW CREATE TABLE employees;
16. Show current user
Answer: SELECT USER(), CURRENT_USER();
17. Check server port
Answer: SHOW VARIABLES LIKE 'port';
18. Check maximum connections
Answer: SHOW VARIABLES LIKE 'max_connections';
19. Check connected threads
Answer: SHOW STATUS LIKE 'Threads_connected';
20. Inspect key server variables
Answer: SHOW VARIABLES LIKE 'datadir';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'wait_timeout';
21. Create database
Answer: CREATE DATABASE company_db;
22. Create utf8mb4 database
Answer: CREATE DATABASE company_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
23. Select company database
Answer: USE company_db;
24. Create departments table
Answer: CREATE TABLE departments(department_id INT AUTO_INCREMENT PRIMARY KEY,department_name VARCHAR(100) NOT NULL UNIQUE,description TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
25. Create employees table
Answer: CREATE TABLE employees(employee_id INT AUTO_INCREMENT PRIMARY KEY,full_name VARCHAR(150) NOT NULL,email VARCHAR(150) UNIQUE,gender ENUM('Male','Female') NOT NULL,salary DECIMAL(12,2),department_id INT,employment_date DATE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(department_id) REFERENCES departments(department_id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;
QUESTIONS 26–50
26. Create composite primary key
Answer: CREATE TABLE student_courses(student_id INT NOT NULL,course_id INT NOT NULL,enrollment_date DATE,PRIMARY KEY(student_id,course_id)) ENGINE=InnoDB;
27. Add column
Answer: ALTER TABLE employees ADD phone_number VARCHAR(20);
28. Modify column type
Answer: ALTER TABLE employees MODIFY phone_number VARCHAR(30);
29. Rename column
Answer: ALTER TABLE employees RENAME COLUMN full_name TO employee_name;
30. Drop column
Answer: ALTER TABLE employees DROP COLUMN phone_number;
31. Add unique constraint
Answer: ALTER TABLE employees ADD CONSTRAINT uq_email UNIQUE(email);
32. Add foreign key
Answer: ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY(department_id) REFERENCES departments(department_id);
33. Create index
Answer: CREATE INDEX idx_employee_name ON employees(full_name);
34. Create composite index
Answer: CREATE INDEX idx_dept_salary ON employees(department_id,salary);
35. Drop index
Answer: DROP INDEX idx_employee_name ON employees;
36. Rename table
Answer: RENAME TABLE employees TO staff;
37. Create temporary table
Answer: CREATE TEMPORARY TABLE temp_staff(id INT,name VARCHAR(100));
38. Create table from query
Answer: CREATE TABLE high_salary AS SELECT employee_id,full_name,salary FROM employees WHERE salary>2000000;
39. Copy table structure
Answer: CREATE TABLE employees_backup LIKE employees;
40. Drop database safely
Answer: Verify database name and backup first, then: DROP DATABASE company_db;
41. Insert department
Answer: INSERT INTO departments(department_name,description) VALUES('ICT','Information technology');
42. Insert multiple departments
Answer: INSERT INTO departments(department_name) VALUES('HR'),('Finance'),('Engineering');
43. Insert employee
Answer: INSERT INTO employees(full_name,email,gender,salary,department_id) VALUES('John Joseph','john@example.com','Male',1500000,1);
44. Select all employees
Answer: SELECT * FROM employees;
45. Select chosen columns
Answer: SELECT employee_id,full_name,email,salary FROM employees;
46. Find employee by ID
Answer: SELECT * FROM employees WHERE employee_id=5;
47. Filter salary
Answer: SELECT * FROM employees WHERE salary>1000000;
48. Filter salary range
Answer: SELECT * FROM employees WHERE salary BETWEEN 1000000 AND 3000000;
49. Filter by department
Answer: SELECT * FROM employees WHERE department_id=2;
50. Search name prefix
Answer: SELECT * FROM employees WHERE full_name LIKE 'J%';
QUESTIONS 51–7551. Search name substring
Answer: SELECT * FROM employees WHERE full_name LIKE '%son%';
52. Find NULL email
Answer: SELECT * FROM employees WHERE email IS NULL;
53. Find non-NULL email
Answer: SELECT * FROM employees WHERE email IS NOT NULL;
54. Top five salaries
Answer: SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
55. Update salary
Answer: UPDATE employees SET salary=2500000 WHERE employee_id=5;
56. Delete a row safely
Answer: SELECT * FROM employees WHERE employee_id=5;
START TRANSACTION;
DELETE FROM employees WHERE employee_id=5;
-- Verify then COMMIT; otherwise ROLLBACK.
57. Inner join employees and departments
Answer: SELECT e.employee_id,e.full_name,d.department_name FROM employees e JOIN departments d ON d.department_id=e.department_id;
58. Left join employees and departments
Answer: SELECT e.full_name,d.department_name FROM employees e LEFT JOIN departments d ON d.department_id=e.department_id;
59. List all departments
Answer: SELECT d.department_name,e.full_name FROM departments d LEFT JOIN employees e ON e.department_id=d.department_id;
60. Find employees with no department
Answer: SELECT e.* FROM employees e LEFT JOIN departments d ON d.department_id=e.department_id WHERE d.department_id IS NULL;
61. Find empty departments
Answer: SELECT d.* FROM departments d LEFT JOIN employees e ON e.department_id=d.department_id WHERE e.employee_id IS NULL;
62. Join three tables
Answer: SELECT s.full_name,c.course_name FROM students s JOIN student_courses sc ON sc.student_id=s.student_id JOIN courses c ON c.course_id=sc.course_id;
63. Self join employee manager
Answer: SELECT e.full_name employee,m.full_name manager FROM employees e LEFT JOIN employees m ON m.employee_id=e.manager_id;
64. Cross join
Answer: SELECT d.department_name,e.full_name FROM departments d CROSS JOIN employees e;
65. Union results
Answer: SELECT email FROM employees UNION SELECT email FROM customers;
66. Union all results
Answer: SELECT email FROM employees UNION ALL SELECT email FROM customers;
67. Find salary above average
Answer: SELECT * FROM employees WHERE salary>(SELECT AVG(salary) FROM employees);
68. Find highest salary by department
Answer: SELECT e.* FROM employees e WHERE salary=(SELECT MAX(e2.salary) FROM employees e2 WHERE e2.department_id=e.department_id);
69. Departments with more than five employees
Answer: SELECT department_id,COUNT(*) total FROM employees GROUP BY department_id HAVING COUNT(*)>5;
70. Find duplicate emails
Answer: SELECT email,COUNT(*) n FROM employees WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*)>1;
71. Find salary above department average
Answer: SELECT e.* FROM employees e WHERE salary>(SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id=e.department_id);
72. Count employees
Answer: SELECT COUNT(*) FROM employees;
73. Sum salaries
Answer: SELECT SUM(salary) FROM employees;
74. Average salaries
Answer: SELECT AVG(salary) FROM employees;
75. Minimum and maximum salary
Answer: SELECT MIN(salary),MAX(salary) FROM employees;
QUESTIONS 76–10076. Count by department
Answer: SELECT department_id,COUNT(*) FROM employees GROUP BY department_id;
77. Average by department
Answer: SELECT department_id,AVG(salary) FROM employees GROUP BY department_id;
78. Second-highest distinct salary
Answer: SELECT MAX(salary) FROM employees WHERE salary=5000000 THEN 'High' WHEN salary>=2000000 THEN 'Medium' ELSE 'Low' END band FROM employees;
81. Replace NULL values
Answer: SELECT COALESCE(email,'No Email') FROM employees;
82. Create CTE
Answer: WITH high_pay AS (SELECT * FROM employees WHERE salary>2000000) SELECT * FROM high_pay;
83. Recursive employee hierarchy
Answer: WITH RECURSIVE h AS (SELECT employee_id,full_name,manager_id,1 lvl FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id,e.full_name,e.manager_id,h.lvl+1 FROM employees e JOIN h ON e.manager_id=h.employee_id) SELECT * FROM h;
84. Rank employees
Answer: SELECT full_name,salary,ROW_NUMBER() OVER(ORDER BY salary DESC) rn FROM employees;
85. Running total
Answer: SELECT employee_id,salary,SUM(salary) OVER(ORDER BY employee_id ROWS UNBOUNDED PRECEDING) running_total FROM employees;
86. Create view
Answer: CREATE VIEW employee_directory AS SELECT employee_id,full_name,email FROM employees;
87. Query view
Answer: SELECT * FROM employee_directory;
88. Replace view
Answer: CREATE OR REPLACE VIEW employee_directory AS SELECT employee_id,full_name,email,salary FROM employees;
89. Drop view
Answer: DROP VIEW employee_directory;
90. Create stored procedure
Answer: DELIMITER //
CREATE PROCEDURE get_employees()
BEGIN SELECT * FROM employees; END //
DELIMITER ;
CALL get_employees();
91. Procedure with IN parameter
Answer: DELIMITER //
CREATE PROCEDURE employee_by_id(IN p_id INT)
BEGIN SELECT * FROM employees WHERE employee_id=p_id; END //
DELIMITER ;
CALL employee_by_id(5);
92. Procedure to insert employee
Answer: DELIMITER //
CREATE PROCEDURE add_emp(IN p_name VARCHAR(150),IN p_email VARCHAR(150))
BEGIN INSERT INTO employees(full_name,email,gender) VALUES(p_name,p_email,'Male'); END //
DELIMITER ;
93. Procedure with OUT parameter
Answer: DELIMITER //
CREATE PROCEDURE employee_count(OUT p_total INT)
BEGIN SELECT COUNT(*) INTO p_total FROM employees; END //
DELIMITER ;
CALL employee_count(@n); SELECT @n;
94. Create function
Answer: DELIMITER //
CREATE FUNCTION annual_pay(p_monthly DECIMAL(12,2)) RETURNS DECIMAL(14,2) DETERMINISTIC NO SQL
BEGIN RETURN p_monthly*12; END //
DELIMITER ;
95. Create audit trigger
Answer: CREATE TABLE employee_audit(id INT AUTO_INCREMENT PRIMARY KEY,employee_id INT,action_name VARCHAR(20),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
DELIMITER //
CREATE TRIGGER emp_insert_audit AFTER INSERT ON employees FOR EACH ROW BEGIN INSERT INTO employee_audit(employee_id,action_name) VALUES(NEW.employee_id,'INSERT'); END //
DELIMITER ;
96. Prevent negative salary
Answer: DELIMITER //
CREATE TRIGGER check_salary BEFORE INSERT ON employees FOR EACH ROW BEGIN IF NEW.salary=0);
118. Create local user
Answer: CREATE USER 'ict_admin'@'localhost' IDENTIFIED BY 'Use-A-Strong-Unique-Password';
119. Create restricted remote user
Answer: CREATE USER 'remote_admin'@'192.168.1.50' IDENTIFIED BY 'Use-A-Strong-Unique-Password';
120. Grant schema privileges
Answer: GRANT SELECT,INSERT,UPDATE ON company_db.* TO 'ict_admin'@'localhost';
121. Grant read-only
Answer: CREATE USER 'auditor'@'localhost' IDENTIFIED BY 'Use-A-Strong-Unique-Password'; GRANT SELECT ON company_db.* TO 'auditor'@'localhost';
122. Grant insert/update
Answer: GRANT INSERT,UPDATE ON company_db.* TO 'data_entry'@'localhost';
123. Revoke write access
Answer: REVOKE INSERT,UPDATE ON company_db.* FROM 'data_entry'@'localhost';
124. Show grants
Answer: SHOW GRANTS FOR 'auditor'@'localhost';
125. Change password
Answer: ALTER USER 'ict_admin'@'localhost' IDENTIFIED BY 'New-Strong-Unique-Password';
QUESTIONS 126–150126. Lock user
Answer: ALTER USER 'ict_admin'@'localhost' ACCOUNT LOCK;
127. Unlock user
Answer: ALTER USER 'ict_admin'@'localhost' ACCOUNT UNLOCK;
128. Drop user
Answer: DROP USER 'ict_admin'@'localhost';
129. Create role
Answer: CREATE ROLE 'db_readonly';
130. Grant role privileges
Answer: GRANT SELECT ON company_db.* TO 'db_readonly';
131. Assign default role
Answer: GRANT 'db_readonly' TO 'auditor'@'localhost'; SET DEFAULT ROLE 'db_readonly' TO 'auditor'@'localhost';
132. Review root grants
Answer: SHOW GRANTS FOR 'root'@'localhost';
133. Use PDO prepared statement
Answer: $stmt=$pdo->prepare('SELECT * FROM employees WHERE email=:email');
$stmt->execute([':email'=>$email]);
$row=$stmt->fetch(PDO::FETCH_ASSOC);
134. Back up one database
Answer: mysqldump -u root -p company_db > company_db.sql
135. Back up all databases
Answer: mysqldump -u root -p --all-databases > all_databases.sql
136. Include routines, triggers and events
Answer: mysqldump -u root -p --routines --triggers --events company_db > full.sql
137. Consistent InnoDB logical backup
Answer: mysqldump -u root -p --single-transaction --quick company_db > company_db.sql
138. Restore a dump
Answer: mysql -u root -p company_db < company_db.sql
139. Restore all databases
Answer: mysql -u root -p < all_databases.sql
140. Back up one table
Answer: mysqldump -u root -p company_db employees > employees.sql
141. Restore one table
Answer: mysql -u root -p company_db < employees.sql
142. Export CSV
Answer: SELECT employee_id,full_name,email FROM employees INTO OUTFILE '/var/lib/mysql-files/employees.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
143. Import CSV
Answer: LOAD DATA INFILE '/var/lib/mysql-files/employees.csv' INTO TABLE employees FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
144. Check binary logging
Answer: SHOW VARIABLES LIKE 'log_bin';
145. List binary logs
Answer: SHOW BINARY LOGS;
146. Inspect source log position
Answer: SHOW MASTER STATUS; -- legacy syntax; use version-supported source status command.
147. Read binary log
Answer: mysqlbinlog binlog.000001
148. Point-in-time recovery
Answer: Restore the consistent full backup, then replay required binary logs to the target time on an isolated recovery host; validate coordinates and transactions.
149. Enable slow query log
Answer: SET GLOBAL slow_query_log=ON; SET GLOBAL long_query_time=2;
150. Find slow log path
Answer: SHOW VARIABLES LIKE 'slow_query_log_file';
QUESTIONS 151–175151. Explain query
Answer: EXPLAIN SELECT * FROM employees WHERE department_id=2;
152. Analyze query execution
Answer: EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id=2;
153. Review unused indexes
Answer: SELECT * FROM sys.schema_unused_indexes; Review over representative workload before removal.
154. List indexes
Answer: SHOW INDEX FROM employees;
155. Refresh statistics
Answer: ANALYZE TABLE employees;
156. Inspect InnoDB
Answer: SHOW ENGINE INNODB STATUS;
157. Inspect active sessions
Answer: SHOW FULL PROCESSLIST;
158. Find long-running sessions
Answer: SELECT ID,USER,HOST,DB,COMMAND,TIME,STATE,INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND'Sleep' ORDER BY TIME DESC;
159. Inspect memory variables
Answer: SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW VARIABLES LIKE 'max_connections';
160. Inspect buffer pool stats
Answer: SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;
161. Inspect connection counters
Answer: SHOW GLOBAL STATUS LIKE 'Connections'; SHOW GLOBAL STATUS LIKE 'Threads_connected'; SHOW GLOBAL STATUS LIKE 'Threads_running';
162. Inspect table sizes
Answer: SELECT TABLE_NAME,ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) size_mb FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='company_db';
163. Inspect schema sizes
Answer: SELECT TABLE_SCHEMA,ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) size_mb FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA;
164. Index email lookup
Answer: CREATE INDEX idx_email ON employees(email); EXPLAIN SELECT * FROM employees WHERE email='x@example.com';
165. Optimize department/salary filter
Answer: CREATE INDEX idx_dept_salary ON employees(department_id,salary); EXPLAIN SELECT * FROM employees WHERE department_id=2 AND salary>2000000;
166. Use keyset pagination
Answer: SELECT * FROM employees WHERE employee_id>1000 ORDER BY employee_id LIMIT 20;
167. Monitor MySQL
Answer: Collect Performance Schema/SYS and OS metrics: CPU, RAM, disk, connections, latency, locks, replication and backup status.
168. Verify replication prerequisites
Answer: SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'server_id';
169. Configure source server
Answer: In my.cnf: [mysqld] server-id=1; log_bin=mysql-bin; binlog_format=ROW
170. Create replication account
Answer: CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'Strong-Unique-Secret'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
171. Configure replica source
Answer: Use version-appropriate CHANGE REPLICATION SOURCE TO with actual host, account, log file/position or GTID auto-position from a consistent snapshot.
172. Start replica
Answer: START REPLICA; -- older versions: START SLAVE;
173. Check replica status
Answer: SHOW REPLICA STATUS\G -- older versions: SHOW SLAVE STATUS\G
174. Inspect replication health
Answer: Review I/O and SQL thread state, lag, last errors and GTID sets where applicable.
175. Stop replica
Answer: STOP REPLICA; -- older versions: STOP SLAVE;
QUESTIONS 176–200176. Troubleshoot replication
Answer: Inspect status/logs, network, credentials, source log retention, disk and data divergence; fix root cause and verify catch-up.
177. Configure GTID conceptually
Answer: Enable compatible GTID settings on all members, provision a consistent snapshot and configure auto-position according to the installed version.
178. Inspect InnoDB Cluster
Answer: Use MySQL Shell cluster status and verify Router routing and metadata.
179. Create Workbench connection
Answer: Create a connection profile with hostname, port, username and authentication; test then save.
180. Create schema in Workbench
Answer: Execute CREATE DATABASE company_db; and refresh Schemas.
181. Export in Workbench
Answer: Server > Data Export; choose schema/tables, options and destination; run and verify.
182. Import in Workbench
Answer: Server > Data Import; select dump source and target schema; import and validate.
183. Explain in Workbench
Answer: Use Explain/Visual Explain and inspect access type, indexes, joins and estimates.
184. Manage Workbench users
Answer: Server Administration > Users and Privileges; review accounts and grants.
185. Monitor Workbench server
Answer: Review dashboards, status variables, connections and logs.
186. Create Workbench ERD
Answer: Create model/EER diagram, define tables, keys and relationships, then forward engineer after review.
187. Compare schemas
Answer: Compare DDL and INFORMATION_SCHEMA metadata; review differences before applying migrations.
188. Respond to slow production database
Answer: Assess impact; inspect OS resources, sessions, slow logs, plans, locks and I/O; fix measured bottleneck and monitor.
189. Respond to accidental deletion
Answer: Preserve logs, restore backup to separate instance, replay logs to pre-delete point and reconcile verified records.
190. Respond to startup failure
Answer: Inspect service/error log, disk, permissions, configuration, port and InnoDB recovery messages; follow supported recovery.
191. Respond to remote connection failure
Answer: Check service, listen interface, routing, firewall, account host, credentials, TLS and connection limits.
192. Respond to full disk
Answer: Find growth and large files, review logs/backups, add capacity or archive by policy; never manually delete active data files.
193. Respond to stopped replication
Answer: Inspect I/O/SQL errors, network, credentials, source logs, disk and divergence; correct and verify catch-up.
194. Correct an employee record safely
Answer: START TRANSACTION; UPDATE employees SET email='correct@example.com' WHERE employee_id=10; SELECT * FROM employees WHERE employee_id=10; COMMIT only after verification; otherwise ROLLBACK.
195. Respond to too many connections
Answer: Inspect session leaks, pooling, long transactions and resource limits; fix causes before changing max_connections.
196. Design an institutional backup plan
Answer: Set schedules, log backups, encryption, isolated offsite copies, retention, access controls, alerts, restore drills, RPO and RTO.
197. Plan first month as DBA
Answer: Inventory databases and criticality; review security, backup/restore, replication, performance, architecture, documentation and maintenance.
198. Create a unique email index
Answer: CREATE UNIQUE INDEX uq_employee_email ON employees(email);
199. Find duplicate salaries
Answer: SELECT salary,COUNT(*) n FROM employees GROUP BY salary HAVING COUNT(*)>1;
200. Find employees hired this year
Answer: SELECT * FROM employees WHERE YEAR(employment_date)=YEAR(CURDATE());
