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
