Fundamentals

1. What is MySQL?

   • MySQL is a popular open-source relational database management system (RDBMS) known for its speed, reliability, and ease of use. ¹  
     1. www.scribd.com

     www.scribd.com

2. What are the key features of MySQL?

   • Open-source: Free to use and distribute.
   • Cross-platform: Runs on various operating systems.
   • High performance: Known for its speed and efficiency.
   • Scalability: Can be scaled to handle large datasets.
   • Security: Provides strong security features like user authentication and access control.

3. Explain the concept of ACID properties in MySQL.

   • Atomicity: All-or-nothing execution of transactions.
   • Consistency: Ensures data integrity and adheres to database constraints.
   • Isolation: Concurrent transactions do not interfere with each other.
   • Durability: Once a transaction is committed, it will persist even in case of system failures.

4. What are the different data types in MySQL?

   • INT, VARCHAR, TEXT, DATE, DATETIME, DECIMAL, BLOB, etc.

5. What is a primary key in MySQL?

   • A unique identifier for each row in a table.

SQL Basics

6. Write a SQL query to select all columns from a table named 'users'.

   • SELECT * FROM users;

7. Write a SQL query to select specific columns (id, name) from a table named 'customers'.

   • SELECT id, name FROM customers;

8. Write a SQL query to insert a new row into a table.

   • INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');

9. Write a SQL query to update a row in a table.

   • UPDATE customers SET email = 'updated@example.com' WHERE id = 1;

10. Write a SQL query to delete a row from a table.

    • DELETE FROM customers WHERE id = 1;

Joins

11. What are different types of joins in SQL?

    • INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN

12. Explain the difference between INNER JOIN and LEFT JOIN.

    • INNER JOIN: Returns rows where there is a match in both tables.
    • LEFT JOIN: Returns all rows from the left table and matching rows from the right table.

13. Write a SQL query to join two tables (customers and orders).

    • SELECT customers.name, orders.order_date FROM customers INNER JOIN orders ON customers.id = orders.customer_id;

Subqueries

14. What are subqueries in SQL?

    • Nested SELECT statements within another SQL statement.

15. Write a SQL query to find customers who have placed an order.

    • SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);

Aggregation Functions

16. What are some common aggregation functions in SQL?

    • COUNT(), SUM(), AVG(), MIN(), MAX()

17. Write a SQL query to find the average order amount.

    • SELECT AVG(amount) FROM orders;

18. Write a SQL query to count the number of customers in a city.

    • SELECT COUNT(*) FROM customers WHERE city = 'New York';

Indexing

19. What is an index in MySQL?

    • A data structure that improves the speed of data retrieval.

20. What are the different types of indexes in MySQL?

    • Primary key, unique key, index.

21. When should you create an index?

    • When frequently querying on a specific column or combination of columns.

Constraints

22. What are constraints in MySQL?

    • Rules that enforce data integrity.

23. What are some common constraints in MySQL?

    • NOT NULL, UNIQUE, FOREIGN KEY, CHECK

Transactions

24. What is a transaction in MySQL?

    • A logical unit of work that consists of one or more SQL statements.

25. How do you start and commit a transaction in MySQL?

    • BEGIN or START TRANSACTION to start, COMMIT to commit.

Views

26. What is a view in MySQL?
    • A virtual table based on the result of a SQL query.

Stored Procedures

27. What are stored procedures in MySQL?
    • Precompiled SQL statements that can be executed repeatedly.

Triggers

28. What are triggers in MySQL?
    • Special type of stored procedure that automatically executes when an event (INSERT, UPDATE, DELETE) occurs on a table.

Functions

29. What are user-defined functions in MySQL?
    • Functions created by users to perform specific tasks.

Security

30. How do you secure a MySQL database?

    • Strong passwords, user access control, firewalls, regular backups.

31. Explain the concept of SQL injection and how to prevent it.

    • SQL injection is a security vulnerability where malicious SQL code is injected into an application.
    • Prevent it using prepared statements and parameterized queries.

Performance Optimization

32. How can you improve the performance of MySQL queries?

    • Create appropriate indexes, optimize data modeling, use efficient query patterns.

33. What is query caching in MySQL?

    • Stores the results of frequently executed queries to improve performance.

Advanced Topics

34. What is partitioning in MySQL?

    • Dividing a large table into smaller, more manageable parts.

35. What is replication in MySQL?

    • Creating multiple copies of a database on different servers for high availability and load balancing.

36. What is a master-slave replication?

    • A traditional replication topology where changes made to the master are replicated to one or more slave servers.

37. What is a multi-master replication?

    • A replication topology where changes made to any master server are replicated to other master servers.

38. What is a cluster in MySQL?

    • A group of interconnected MySQL servers that work together to provide high availability, scalability, and fault tolerance.

39. What is MySQL's role-based access control (RBAC)?

    • A mechanism to control user access to databases, tables, and operations.

40. What are some common MySQL administration tools?

    • MySQL Workbench, phpMyAdmin.

41. How do you monitor MySQL server performance?

    • Use MySQL's built-in monitoring tools and performance counters.

42. How do you back up and restore a MySQL database?

    • Use the mysqldump and mysql commands.

43. What are some common use cases for MySQL?

    • Web applications, e-commerce, content management systems, data warehousing.

44. What are some of the challenges of using MySQL?

    • Potential performance issues with very large datasets, complex schema design for some applications.

45. What is the difference between MyISAM and InnoDB storage engines?

    • MyISAM: Supports full-text indexing, good for read-heavy workloads.
    • InnoDB: Supports transactions, foreign keys, row-level locking, suitable for most applications.

46. What is the purpose of the EXPLAIN statement in MySQL?

    • Provides information about how MySQL will execute a query, helping to identify potential performance bottlenecks.

47. What is the difference between CHAR and VARCHAR data types?

    • CHAR has a fixed length, while VARCHAR has a variable length.

48. What is the purpose of the WHERE clause in a SQL query?

    • Specifies the conditions that must be met for a row to be included in the result set.

49. What is the purpose of the ORDER BY clause in a SQL query?

    • Sorts the result set based on one or more columns.

50. What is the purpose of the GROUP BY clause in a SQL query?

    • Groups rows that have the same values for specified columns.

I hope these questions are helpful for your MySQL interview preparation!