MySQL Cheat Sheet: 100 Questions

1. Basic SQL Queries

1.1. What is the purpose of the SELECT statement?

● Answer: The SELECT statement is used to query the database and retrieve data
from one or more tables.

Example:
SELECT * FROM employees;

1.2. How do you retrieve all columns from a table named users?

● Answer: Use the SELECT * statement to retrieve all columns from the table.

Example:
SELECT * FROM users;

1.3. How can you select only specific columns from a table?

● Answer: Specify the column names in the SELECT statement.

Example:
SELECT first_name, last_name FROM employees;

1.4. How do you filter records using the WHERE clause?

● Answer: Use the WHERE clause to specify conditions for filtering records.

Example:
SELECT * FROM employees WHERE department = 'Sales';

1.5. What is the purpose of the ORDER BY clause?

 ● Answer: The ORDER BY clause is used to sort the result set by one or more
columns.

Example:
SELECT * FROM employees ORDER BY salary DESC;

2. Aggregate Functions
2.1. How do you find the total number of rows in a table?

● Answer: Use the COUNT() function.

Example:
SELECT COUNT(*) FROM employees;

2.2. How can you find the average value of a numeric column?

● Answer: Use the AVG() function.

Example:
SELECT AVG(salary) FROM employees;

2.3. How do you find the maximum value in a column?

● Answer: Use the MAX() function.

Example:
SELECT MAX(salary) FROM employees;

2.4. How can you find the minimum value in a column?

● Answer: Use the MIN() function.

Example:
SELECT MIN(salary) FROM employees;

●
2.5. How do you calculate the sum of a numeric column?

● Answer: Use the SUM() function.

Example:
SELECT SUM(salary) FROM employees;

3. Joins
3.1. What is an INNER JOIN?

● Answer: An INNER JOIN returns rows that have matching values in both tables.

Example:
SELECT employees.first_name, departments.department_name

FROM employees

INNER JOIN departments ON employees.department_id =

departments.department_id;

3.2. How does a LEFT JOIN differ from an INNER JOIN?

● Answer: A LEFT JOIN returns all rows from the left table and the matched rows
from the right table. Non-matching rows from the right table will be NULL.

Example:
SELECT employees.first_name, departments.department_name

FROM employees

LEFT JOIN departments ON employees.department_id =

departments.department_id;

3.3. What is a RIGHT JOIN?

● Answer: A RIGHT JOIN returns all rows from the right table and the matched rows
from the left table. Non-matching rows from the left table will be NULL.
 Example:
SELECT employees.first_name, departments.department_name

FROM employees

RIGHT JOIN departments ON employees.department_id =

departments.department_id;

3.4. How do you perform a FULL JOIN in MySQL?

● Answer: MySQL does not support FULL JOIN directly. Use a combination of LEFT
JOIN and RIGHT JOIN.

Example:
SELECT employees.first_name, departments.department_name

FROM employees

LEFT JOIN departments ON employees.department_id =

departments.department_id

UNION

SELECT employees.first_name, departments.department_name

FROM employees

RIGHT JOIN departments ON employees.department_id =

departments.department_id;

3.5. What is a CROSS JOIN?

● Answer: A CROSS JOIN returns the Cartesian product of both tables, meaning
every combination of rows.

Example:
SELECT employees.first_name, departments.department_name

FROM employees

CROSS JOIN departments;

●
4. Subqueries
4.1. What is a subquery?

● Answer: A subquery is a query nested inside another query.

Example:
SELECT first_name

FROM employees

WHERE department_id = (SELECT department_id FROM departments WHERE

department_name = 'Sales');

4.2. How do you use a subquery in the WHERE clause?

● Answer: Place the subquery inside the WHERE clause to filter results based on the
result of the subquery.

Example:
SELECT first_name

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

4.3. What is a correlated subquery?

● Answer: A correlated subquery references columns from the outer query and is
executed for each row of the outer query.

Example:
SELECT e1.first_name

FROM employees e1

WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE

e1.department_id = e2.department_id);

4.4. How do you use a subquery in the FROM clause?

 ● Answer: A subquery in the FROM clause is used as a derived table or temporary
table.

Example:
SELECT avg_salaries.department_id, AVG(salary)

FROM (SELECT department_id, salary FROM employees) AS avg_salaries

GROUP BY avg_salaries.department_id;

4.5. How can you use a subquery in the SELECT clause?

● Answer: A subquery in the SELECT clause returns a value that can be used as a
column in the result set.

Example:
SELECT first_name,

(SELECT AVG(salary) FROM employees) AS avg_salary

FROM employees;

5. Data Modification
5.1. How do you insert a new record into a table?

● Answer: Use the INSERT INTO statement.

Example:
INSERT INTO employees (first_name, last_name, department_id, salary)

VALUES ('John', 'Doe', 1, 50000);

5.2. How can you update existing records in a table?

● Answer: Use the UPDATE statement with a WHERE clause to specify which records
to update.

Example:
UPDATE employees
 SET salary = 55000

WHERE first_name = 'John' AND last_name = 'Doe';

5.3. How do you delete records from a table?

● Answer: Use the DELETE FROM statement with a WHERE clause to specify which
records to delete.

Example:
DELETE FROM employees

WHERE first_name = 'John' AND last_name = 'Doe';

5.4. How can you delete all records from a table without removing the table itself?

● Answer: Use the TRUNCATE TABLE statement.

Example:
TRUNCATE TABLE employees;

5.5. What is the difference between DELETE and TRUNCATE?

● Answer: DELETE removes rows one at a time and can be rolled back, while
TRUNCATE removes all rows at once and cannot be rolled back.

Example:
-- DELETE

DELETE FROM employees;

-- TRUNCATE

TRUNCATE TABLE employees;

6. Indexing
6.1. What is an index in MySQL?

● Answer: An index is a database object that improves the speed of data retrieval
operations.

Example:
CREATE INDEX idx_salary ON employees(salary);

6.2. How do you create an index on a table?

● Answer: Use the CREATE INDEX statement.

Example:
CREATE INDEX idx_department ON employees(department_id);

6.3. What is the difference between a unique index and a non-unique index?

● Answer: A unique index ensures that all values in the indexed column are
unique, while a non-unique index does not enforce uniqueness.

Example:
-- Unique Index

CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Non-Unique Index

CREATE INDEX idx_non_unique_name ON employees(name);

6.4. How do you drop an index?

● Answer: Use the DROP INDEX statement.

Example:
DROP INDEX idx_salary ON employees;

●
6.5. What is a composite index?

● Answer: A composite index is an index on multiple columns.

Example:
CREATE INDEX idx_name_dept ON employees(first_name, department_id);

7. Transactions
7.1. What is a transaction in MySQL?

● Answer: A transaction is a sequence of one or more SQL operations executed as

a single unit of work.

Example:
START TRANSACTION;

INSERT INTO accounts (balance) VALUES (1000);

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

COMMIT;

7.2. How do you start a transaction?

● Answer: Use the START TRANSACTION statement.

Example:
START TRANSACTION;

7.3. How do you commit a transaction?

● Answer: Use the COMMIT statement to save all changes made during the
transaction.

Example:
COMMIT;

●
7.4. How do you roll back a transaction?

● Answer: Use the ROLLBACK statement to undo changes made during the
transaction.

Example:
ROLLBACK;

7.5. What is the difference between COMMIT and ROLLBACK?

● Answer: COMMIT saves changes made during the transaction, while ROLLBACK
undoes them.

Example:
-- Commit

COMMIT;

-- Rollback

ROLLBACK;

8. User Management
8.1. How do you create a new user in MySQL?

● Answer: Use the CREATE USER statement.

Example:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

8.2. How do you grant privileges to a user?

● Answer: Use the GRANT statement to assign privileges.

Example:
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';
 ●

8.3. How do you revoke privileges from a user?

● Answer: Use the REVOKE statement to remove privileges.

Example:
REVOKE ALL PRIVILEGES ON database_name.* FROM 'newuser'@'localhost';

8.4. How do you drop a user in MySQL?

● Answer: Use the DROP USER statement.

Example:
DROP USER 'newuser'@'localhost';

8.5. How do you change a user’s password?

● Answer: Use the ALTER USER statement.

Example:
ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword';

9. Data Types
9.1. What are the different types of data in MySQL?

● Answer: MySQL supports several data types, including numeric, date/time, and
string types.

Example:
-- Numeric Types

INT, FLOAT, DOUBLE

-- Date/Time Types

DATE, DATETIME, TIMESTAMP

 -- String Types

CHAR, VARCHAR, TEXT

9.2. What is the difference between CHAR and VARCHAR?

● Answer: CHAR is a fixed-length string, while VARCHAR is a variable-length string.

Example:
-- CHAR

CREATE TABLE test_char (

col CHAR(10)

);

-- VARCHAR

CREATE TABLE test_varchar (

col VARCHAR(10)

);

9.3. How do you store a date in MySQL?

● Answer: Use the DATE or DATETIME data type.

Example:
CREATE TABLE events (

event_date DATE

);

9.4. What is the purpose of the TEXT data type?

● Answer: The TEXT data type is used to store large amounts of text.
 Example:
CREATE TABLE articles (

content TEXT

);

9.5. How do you define a column that auto-increments?

● Answer: Use the AUTO_INCREMENT attribute for an integer column.

Example:
CREATE TABLE users (

user_id INT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50)

);

10. Performance Optimization

10.1. What is a query optimizer?

● Answer: The query optimizer analyzes SQL queries and determines the most
efficient way to execute them.

Example:
EXPLAIN SELECT * FROM employees WHERE salary > 50000;

10.2. How do you analyze a query execution plan?

● Answer: Use the EXPLAIN statement to get insights into how a query will be
executed.

Example:
EXPLAIN SELECT * FROM employees WHERE department_id = 1;

●
10.3. How can you improve query performance with indexes?

● Answer: Create indexes on columns frequently used in WHERE, JOIN, and ORDER BY
clauses.

Example:
CREATE INDEX idx_dept ON employees(department_id);

10.4. What is query caching?

● Answer: Query caching stores the result of a query to speed up subsequent

executions of the same query.
● Example: Query caching is automatically managed by MySQL.

10.5. How do you optimize a large table?

● Answer: Regularly use the OPTIMIZE TABLE statement to defragment and improve
table performance.

Example:
OPTIMIZE TABLE employees;

11. Transactions and Concurrency

11.1. What are isolation levels in MySQL transactions?

● Answer: Isolation levels define the extent to which the operations in one
transaction are isolated from those in other transactions. Levels include READ
UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

Example:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

11.2. How do you handle deadlocks in MySQL?

● Answer: Use transaction retry mechanisms and ensure that transactions access
resources in the same order to avoid deadlocks.
● Example: Detect and handle deadlocks programmatically in application code.
11.3. What is a savepoint in MySQL?

● Answer: A savepoint allows you to set a point within a transaction to which you
can roll back.

Example:
SAVEPOINT my_savepoint;

-- Perform operations

ROLLBACK TO SAVEPOINT my_savepoint;

11.4. How do you lock tables in MySQL?

● Answer: Use the LOCK TABLES statement to lock tables for reading or writing.

Example:
LOCK TABLES employees WRITE;

-- Perform operations

UNLOCK TABLES;

11.5. What is an implicit lock?

● Answer: Implicit locks are automatically managed by MySQL and occur when
rows or tables are accessed in transactions.
● Example: Implicit locks are managed by the MySQL engine during transaction
execution.

12. Data Integrity and Constraints

12.1. What is a primary key in MySQL?

● Answer: A primary key is a unique identifier for each record in a table.

Example:
CREATE TABLE employees (

employee_id INT PRIMARY KEY,

name VARCHAR(50)
 );

12.2. How do you create a foreign key constraint?

● Answer: Use the FOREIGN KEY constraint to establish a relationship between

columns in different tables.

Example:
CREATE TABLE departments (

department_id INT PRIMARY KEY,

department_name VARCHAR(50)

);

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

department_id INT,

FOREIGN KEY (department_id) REFERENCES departments(department_id)

);

12.3. What is a unique constraint?

● Answer: A unique constraint ensures that all values in a column or a group of

columns are distinct.

Example:
CREATE TABLE users (

user_id INT PRIMARY KEY,

email VARCHAR(100) UNIQUE

);

●
12.4. How do you define a default value for a column?

● Answer: Use the DEFAULT keyword to set a default value for a column.

Example:
CREATE TABLE products (

product_id INT PRIMARY KEY,

stock_quantity INT DEFAULT 0

);

**12.5. What is a

check constraint?**

● Answer: A check constraint enforces a condition on the values in a column.

Example:
CREATE TABLE employees (

employee_id INT PRIMARY KEY,

salary DECIMAL(10, 2) CHECK (salary > 0)

);

13. Stored Procedures and Functions

13.1. What is a stored procedure in MySQL?

● Answer: A stored procedure is a saved set of SQL statements that can be

executed as a unit.

Example:
CREATE PROCEDURE GetEmployeeById(IN emp_id INT)

BEGIN

SELECT * FROM employees WHERE employee_id = emp_id;

END;
 ●

13.2. How do you call a stored procedure?

● Answer: Use the CALL statement to execute a stored procedure.

Example:
CALL GetEmployeeById(1);

13.3. What is a user-defined function (UDF) in MySQL?

● Answer: A user-defined function is a custom function created by the user to

perform specific tasks and return a value.

Example:
CREATE FUNCTION CalculateBonus(salary DECIMAL(10,2))

RETURNS DECIMAL(10,2)

BEGIN

RETURN salary * 0.10;

END;

13.4. How do you create a function in MySQL?

● Answer: Use the CREATE FUNCTION statement.

Example:
CREATE FUNCTION GetEmployeeCount()

RETURNS INT

BEGIN

DECLARE emp_count INT;

SELECT COUNT(*) INTO emp_count FROM employees;

RETURN emp_count;

END;
 ●

13.5. What is the difference between a stored procedure and a function?

● Answer: A stored procedure performs an action and may not return a value, while
a function returns a value and can be used in SQL expressions.

Example:
-- Stored Procedure

CREATE PROCEDURE GetAllEmployees()

BEGIN

SELECT * FROM employees;

END;

-- Function

CREATE FUNCTION GetTotalEmployees()

RETURNS INT

BEGIN

RETURN (SELECT COUNT(*) FROM employees);

END;

14. Data Backup and Restoration

14.1. How do you create a backup of a MySQL database?

● Answer: Use the mysqldump command to create a backup.

Example:
mysqldump -u username -p database_name > backup.sql

14.2. How do you restore a MySQL database from a backup?

 ● Answer: Use the mysql command to restore a database from a backup.

Example:
mysql -u username -p database_name < backup.sql

14.3. What is the purpose of the mysqldump command?

● Answer: mysqldump creates a logical backup of a MySQL database by generating

a SQL script that can be used to recreate the database.

Example:
mysqldump -u username -p database_name > backup.sql

14.4. How can you create a backup of a single table?

● Answer: Use mysqldump with the table name.

Example:
mysqldump -u username -p database_name table_name > table_backup.sql

14.5. How do you automate database backups?

● Answer: Schedule mysqldump commands using cron jobs or scheduled tasks.

Example (cron job):

0 2 * * * mysqldump -u username -p database_name > /path/to/backup_$(date
+\%F).sql

15. Advanced Features

15.1. What is partitioning in MySQL?

● Answer: Partitioning divides a table into smaller, more manageable pieces while
maintaining a single logical view.

Example:
CREATE TABLE orders (
 order_id INT,

order_date DATE

PARTITION BY RANGE (YEAR(order_date)) (

PARTITION p0 VALUES LESS THAN (2000),

PARTITION p1 VALUES LESS THAN (2010),

PARTITION p2 VALUES LESS THAN (2020),

PARTITION p3 VALUES LESS THAN (MAXVALUE)

);

15.2. How do you use views in MySQL?

● Answer: Views are virtual tables created by a query and can simplify complex
queries.

Example:
CREATE VIEW employee_view AS

SELECT first_name, last_name, department_name

FROM employees

JOIN departments ON employees.department_id = departments.department_id;

15.3. What is a materialized view?

● Answer: A materialized view is a view that stores the result set physically,
allowing faster query performance. MySQL does not support materialized views
directly; use tables with periodic refreshes.
● Example: Simulate using tables and scheduled jobs.

15.4. How do you implement full-text search in MySQL?

● Answer: Use FULLTEXT indexes to enable full-text search capabilities.

 Example:
CREATE TABLE articles (

id INT PRIMARY KEY,

content TEXT,

FULLTEXT(content)

);

15.5. What is the purpose of SHOW TABLE STATUS?

● Answer: SHOW TABLE STATUS provides information about the status and structure
of tables, such as size and number of rows.
● Example: ```sql SHOW TABLE STATUS LIKE employees; Certainly! Here are
advanced MySQL questions, continuing from where we left off:

16. Advanced Query Optimization

16.1. How do you analyze and optimize slow queries in MySQL?

● Answer: Use the slow_query_log to identify slow queries and the EXPLAIN
statement to analyze their execution plans.

Example:
SET GLOBAL slow_query_log = 'ON';

SET GLOBAL slow_query_log_file = 'slow-query.log';

16.2. What is a covering index and how does it improve query performance?

● Answer: A covering index includes all columns needed by a query, allowing the
query to be resolved using the index alone.

Example:
CREATE INDEX idx_name_age ON employees(name, age);

16.3. How do you use ANALYZE TABLE for optimization?

 ● Answer: ANALYZE TABLE updates statistics about table key distributions, helping
the optimizer make better decisions.

Example:
ANALYZE TABLE employees;

16.4. What are the implications of using JOIN vs. SUBQUERY for performance?

● Answer: Joins are often more efficient than subqueries because they use indexes
and optimize query plans better.

Example:
-- Join

SELECT e.name, d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id;

-- Subquery

SELECT name, (SELECT department_name FROM departments WHERE department_id

= e.department_id) AS department_name

FROM employees e;

16.5. How can you improve the performance of GROUP BY queries?

● Answer: Index columns used in GROUP BY and ensure statistics are up-to-date to
improve performance.

Example:
CREATE INDEX idx_department ON employees(department_id);

17. High Availability and Replication

17.1. What is MySQL replication and how does it work?
 ● Answer: MySQL replication involves copying data from a master database to one
or more slave databases to ensure data redundancy and load balancing.

Example:
-- On master

CHANGE MASTER TO MASTER_HOST='slave_host',

MASTER_USER='replication_user', MASTER_PASSWORD='password';

START SLAVE;

17.2. How do you set up a master-slave replication in MySQL?

● Answer: Configure the master and slave servers with appropriate replication
settings and start the replication process.

Example:
-- On master

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'slave_host'

IDENTIFIED BY 'password';

-- On slave

CHANGE MASTER TO MASTER_HOST='master_host',

MASTER_USER='replication_user', MASTER_PASSWORD='password';

START SLAVE;

17.3. What is a replication lag, and how can you monitor it?

● Answer: Replication lag is the delay between the master and slave databases.
Use SHOW SLAVE STATUS to monitor it.

Example:
SHOW SLAVE STATUS \G;

17.4. How do you implement MySQL Cluster for high availability?

 ● Answer: MySQL Cluster uses data nodes, management nodes, and SQL nodes
to provide high availability and fault tolerance.
● Example: Installation and configuration are done through MySQL Cluster
management tools and configuration files.

17.5. What is the difference between statement-based and row-based replication?

● Answer: Statement-based replication logs the SQL statements executed, while

row-based replication logs the actual changes to the data.

Example:
-- Statement-based

binlog_format = STATEMENT;

-- Row-based

binlog_format = ROW;

18. Security and Encryption

18.1. How do you encrypt data in MySQL?

● Answer: Use MySQL's built-in encryption functions or the ENCRYPT and

AES_ENCRYPT functions to encrypt data.

Example:
-- Encryption

SELECT AES_ENCRYPT('password', 'encryption_key');

-- Decryption

SELECT AES_DECRYPT(encrypted_column, 'encryption_key') FROM table;

18.2. How do you use SSL/TLS to secure MySQL connections?

 ● Answer: Configure MySQL to use SSL/TLS by setting the
--require_secure_transport option and providing SSL certificates.

Example:
-- Server-side

[mysqld]

ssl-ca=/path/to/ca-cert.pem

ssl-cert=/path/to/server-cert.pem

ssl-key=/path/to/server-key.pem

-- Client-side

[client]

ssl-ca=/path/to/ca-cert.pem

ssl-cert=/path/to/client-cert.pem

ssl-key=/path/to/client-key.pem

18.3. What is MySQL's approach to user authentication and how can it be improved?

● Answer: MySQL uses the mysql.user table for authentication. Improve security
by using strong passwords and enabling caching_sha2_password authentication.

Example:
CREATE USER 'user'@'host' IDENTIFIED WITH caching_sha2_password BY
'password';

18.4. How can you restrict user privileges in MySQL?

● Answer: Use the GRANT and REVOKE statements to control user access to
databases and tables.

Example:
GRANT SELECT, INSERT ON database_name.* TO 'user'@'host';

REVOKE ALL PRIVILEGES ON database_name.* FROM 'user'@'host';

 ●

18.5. How do you audit MySQL activity for security purposes?

● Answer: Use MySQL Enterprise Audit plugin or third-party tools to log and
monitor user activity.

Example:
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

19. Data Warehousing and ETL

19.1. What is ETL and how is it implemented in MySQL?

● Answer: ETL (Extract, Transform, Load) involves extracting data from various
sources, transforming it, and loading it into a data warehouse. Implement ETL
using MySQL’s LOAD DATA INFILE, INSERT INTO ... SELECT, and other data
import/export methods.

Example:
LOAD DATA INFILE '/path/to/file.csv' INTO TABLE table_name

FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

19.2. How do you perform data transformation in MySQL?

● Answer: Use SQL functions and expressions to transform data during insertion or
querying.

Example:
INSERT INTO new_table (name, age)

SELECT UPPER(name), age + 1 FROM old_table;

19.3. What is the purpose of data partitioning in a data warehouse?

● Answer: Data partitioning improves query performance and manageability by

dividing large tables into smaller, more manageable pieces.
 Example:
CREATE TABLE sales (

sale_id INT,

sale_date DATE

PARTITION BY RANGE (YEAR(sale_date)) (

PARTITION p0 VALUES LESS THAN (2000),

PARTITION p1 VALUES LESS THAN (2010),

PARTITION p2 VALUES LESS THAN (2020)

);

19.4. How do you handle large data imports efficiently?

● Answer: Use bulk data loading commands like LOAD DATA INFILE and optimize
import settings.

Example:
LOAD DATA INFILE '/path/to/large_file.csv' INTO TABLE table_name

FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

19.5. How do you manage schema changes in a data warehouse?

● Answer: Use schema migration tools and version control to manage and apply
schema changes systematically.
● Example: Tools like Liquibase or Flyway can be used for schema migration and
version control.

20. Backup and Recovery

20.1. How do you perform point-in-time recovery in MySQL?

● Answer: Use binary logs to restore the database to a specific point in time.
 Example:
mysqlbinlog --stop-datetime="2024-07-01 12:00:00" /path/to/binlog >
recovery.sql

mysql -u username -p database_name < recovery.sql

20.2. How do you backup and restore MySQL databases using snapshots?

● Answer: Use filesystem-level snapshots or MySQL’s built-in backup tools to

create and restore backups.

Example:
# Create snapshot

xtrabackup --backup --stream=tar | gzip > backup.tar.gz

# Restore snapshot

gunzip < backup.tar.gz | xtrabackup --prepare --apply-log

20.3. How can you use MySQL Enterprise Backup for hot backups?

● Answer: MySQL Enterprise Backup allows hot backups with no downtime by

taking consistent snapshots of data.
● Example: ```bash mysql

backup --backup-dir=/backup --backup-image=mybackup.img --backup