interview-questions
  1. mysql-interview-questions

MySQL Interview Questions & Answers

Basic MySQL Concepts:

  1. Q: What is MySQL?

    • A: MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for managing and manipulating data.

  2. Q: Explain the difference between MyISAM and InnoDB storage engines in MySQL.

    • A: MyISAM is non-transactional and suitable for read-heavy operations, while InnoDB is transactional, supports foreign keys, and is suitable for applications with write-intensive operations.

  3. Q: How do you create a database in MySQL?

    • A: CREATE DATABASE database_name;

  4. Q: Write a query to retrieve all columns from a table named "employees" in MySQL.

    • A: SELECT * FROM employees;

  5. Q: What is the purpose of the MySQL SHOW TABLES statement?

    • A: It is used to display a list of tables in a specific database.

MySQL Queries:

  1. Q: How can you find the number of rows in a table in MySQL?

    • A: SELECT COUNT(*) FROM table_name;

  2. Q: Write a query to find the second-highest salary from an "employees" table in MySQL.

    • A: 
      SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

  3. Q: Explain the purpose of the MySQL LIMIT clause.

    • A: LIMIT is used to restrict the number of rows returned by a query.

  4. Q: How do you perform a join operation in MySQL?

    • A: Use JOIN clauses to combine rows from two or more tables based on a related column.

  5. Q: What is a subquery in MySQL?

    • A: A subquery is a query nested inside another query, usually enclosed in parentheses.

Indexing and Optimization:

  1. Q: What is an index in MySQL, and how does it improve query performance?

    • A: An index is a data structure that improves the speed of data retrieval operations on a database table. It speeds up query performance by allowing the database engine to locate and retrieve data more quickly.

  2. Q: How do you optimize a MySQL query?

    • A: Optimization techniques include using indexes, optimizing queries using EXPLAIN, and avoiding unnecessary data retrieval.

  3. Q: Explain the concept of query caching in MySQL.

    • A: Query caching involves storing the result set of a query in the cache so that if the same query is executed again, the result can be quickly retrieved from the cache instead of re-executing the query.

  4. Q: How can you create an index on a table column in MySQL?

    • A: CREATE INDEX index_name ON table_name (column_name);

  5. Q: What is the purpose of the MySQL EXPLAIN statement?

    • A: EXPLAIN provides information about how MySQL executes a SELECT statement, helping to analyze and optimize queries.

Joins and Relationships:

  1. Q: Explain the difference between INNER JOIN and LEFT JOIN in MySQL.

    • A: INNER JOIN returns rows with matching values in both tables, while LEFT JOIN returns all rows from the left table and matching rows from the right table.

  2. Q: How do you perform a self-join in MySQL?

    • A: Use an alias to reference the same table within the query. Example: SELECT e1.name, e2.name FROM employees e1, employees e2 WHERE e1.manager_id = e2.employee_id;

  3. Q: What is the purpose of the MySQL UNION operator?

    • A: UNION is used to combine the results of two or more SELECT statements into a single result set.

  4. Q: Explain the concept of a foreign key in MySQL.

    • A: A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a link between the two tables.

  5. Q: How can you enforce referential integrity in MySQL using foreign keys?

    • A: By defining foreign key constraints using the FOREIGN KEY keyword.

MySQL Administration:

  1. Q: How do you backup and restore a MySQL database?

    • A: Use mysqldump to create a backup and mysql to restore it.

  2. Q: What is the purpose of the MySQL GRANT statement?

    • A: GRANT is used to grant privileges to MySQL user accounts.

  3. Q: How can you change the root password in MySQL?

    • A: Use the SET PASSWORD statement or the mysqladmin utility.

  4. Q: Explain the purpose of the MySQL mysqld daemon.

    • A: mysqld is the MySQL server daemon responsible for managing database connections and handling SQL queries.

  5. Q: How do you monitor MySQL performance?

    • A: Use tools like MySQL Enterprise Monitor, MySQL Workbench, and Performance Schema.

Advanced MySQL Concepts:

  1. Q: What is the purpose of the MySQL TRIGGER statement?

    • A: A trigger is a set of instructions that are automatically executed in response to certain events on a particular table.

  2. Q: Explain the concept of the MySQL VIEW.

    • A: A view is a virtual table based on the result of a SELECT statement. It does not store the data itself but provides a way to represent data stored in tables.

  3. Q: What is the purpose of the MySQL ENUM data type?

    • A: ENUM is a data type that defines a set of permissible values for a column.

  4. Q: How can you implement full-text search in MySQL?

    • A: Use the FULLTEXT index and the MATCH AGAINST operator.

  5. Q: What is the purpose of the MySQL JSON data type?

    • A: The JSON data type stores JSON-formatted data and allows for more efficient manipulation of JSON documents.

MySQL Security and Permissions:

  1. Q: How do you grant SELECT permission on a table to a user in MySQL?

    • A: GRANT SELECT ON table_name TO user_name;

  2. Q: What is SQL injection, and how can it be prevented in MySQL?

    • A: SQL injection is a code injection technique. Prevention involves using parameterized queries and prepared statements.

  3. Q: How can you encrypt sensitive data in a MySQL database?

    • A: Use functions like AES_ENCRYPT and AES_DECRYPT for encryption and decryption.

  4. Q: Explain the concept of role-based access control in MySQL.

    • A: Role-based access control involves assigning permissions to roles and then assigning roles to users. It simplifies the management of permissions.

  5. Q: How do you revoke permissions in MySQL?

    • A: `REVOKE permission_name ON table_name FROM

user_name;`

Database Design and Modeling:

  1. Q: What is the purpose of normalization in database design?

    • A: Normalization minimizes redundancy and dependency by organizing data into separate tables based on their relationships.

  2. Q: Explain the concept of denormalization.

    • A: Denormalization involves combining tables to reduce the number of joins and improve query performance. It sacrifices some level of normalization for performance gains.

  3. Q: What is an ER diagram, and how is it used in database design?

    • A: An Entity-Relationship (ER) diagram is a visual representation of the relationships between entities in a database. It helps in designing and understanding the structure of a database.

  4. Q: How do you create an index on a table column in MySQL?

    • A: CREATE INDEX index_name ON table_name (column_name);

  5. Q: Explain the concept of ACID properties in database transactions.

    • A: ACID stands for Atomicity, Consistency, Isolation, and Durability. It ensures that database transactions are reliable even in the event of system failures.

Advanced Querying:

  1. Q: Write a query to find the nth highest salary from an "employees" table in MySQL.

    • A: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT n-1,1;

  2. Q: How do you use the CASE statement in MySQL?

    • A: The CASE statement is used for conditional logic in MySQL. Example:
      SELECT column_name,
       CASE
         WHEN condition1 THEN 'Value1'
         WHEN condition2 THEN 'Value2'
         ELSE 'DefaultValue'
       END
FROM table_name;

  3. Q: What is the purpose of the OFFSET and FETCH clauses in MySQL?

    • A: OFFSET and FETCH are used for pagination. OFFSET specifies the number of rows to skip, and FETCH specifies the number of rows to return.

  4. Q: How can you perform a cross join in MySQL?

    • A: Use the CROSS JOIN keyword. Example: SELECT * FROM table1 CROSS JOIN table2;

  5. Q: Write a query to find the employees who have not been assigned any project in MySQL.

    • A: SELECT * FROM employees WHERE employee_id NOT IN (SELECT employee_id FROM projects);

Troubleshooting and Maintenance:

  1. Q: How do you troubleshoot a slow-performing query in MySQL?

    • A: Use tools like the MySQL Query Profiler, EXPLAIN statement, and review the slow query log.

  2. Q: What is the purpose of the MySQL error log?

    • A: The error log records information about errors, warnings, and notices encountered by the MySQL server.

  3. Q: How can you identify and resolve performance bottlenecks in MySQL?

    • A: Use tools like the MySQL Performance Schema, analyze query execution plans, and optimize indexes.

  4. Q: How can you optimize the storage engine for a specific table in MySQL?

    • A: Choose an appropriate storage engine based on the characteristics of the table (e.g., InnoDB for transactional tables, MyISAM for read-heavy tables).

  5. Q: What is the purpose of the MySQL OPTIMIZE TABLE statement?

    • A: OPTIMIZE TABLE is used to reclaim unused space and defragment tables, improving overall performance.