Basic MySQL Questions

1. What is MySQL?

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

2. What is a database?

   • A database is a structured collection of data that can be accessed, managed, and updated efficiently.

3. What is SQL?

   • SQL (Structured Query Language) is a language used for managing and manipulating relational databases.

4. What are the different types of SQL commands?

   • DDL (Data Definition Language)
   • DML (Data Manipulation Language)
   • DCL (Data Control Language)
   • TCL (Transaction Control Language)

5. What are some common MySQL data types?

   • INT, VARCHAR, TEXT, DATE, BOOLEAN, FLOAT, DECIMAL, BLOB

6. What is the difference between CHAR and VARCHAR?

   • CHAR is a fixed-length string, while VARCHAR is a variable-length string.

7. How do you create a database in MySQL?

   CREATE DATABASE my_database;
   

8. How do you delete a database?

   DROP DATABASE my_database;
   

9. How do you create a table?

   CREATE TABLE users (
       id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(100),
       email VARCHAR(100) UNIQUE
   );
   

10. How do you delete a table?

    DROP TABLE users;
    

11. How do you insert data into a table?

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

12. How do you retrieve all records from a table?

    SELECT * FROM users;
    

13. How do you update a record in a table?

    UPDATE users SET name = 'Jane Doe' WHERE id = 1;
    

14. How do you delete a record from a table?

    DELETE FROM users WHERE id = 1;
    

15. What is the difference between DELETE and TRUNCATE?

    • DELETE removes specific rows with a condition.
    • TRUNCATE removes all rows from a table but keeps the structure intact.

16. How do you use a WHERE clause?

    SELECT * FROM users WHERE name = 'John Doe';
    

17. How do you sort query results?

    SELECT * FROM users ORDER BY name ASC;
    

18. How do you count the number of rows in a table?

    SELECT COUNT(*) FROM users;
    

19. What is a primary key?

    • A primary key is a unique identifier for each row in a table.

20. What is a foreign key?

    • A foreign key is a column that references the primary key of another table.

Intermediate MySQL Questions

21. What is a JOIN in MySQL?

    • A JOIN is used to retrieve data from multiple tables based on a related column.

22. What are the types of JOINs?

    • INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, SELF JOIN

23. How do you use an INNER JOIN?

    SELECT users.name, orders.order_date
    FROM users
    INNER JOIN orders ON users.id = orders.user_id;
    

24. How do you use a LEFT JOIN?

    SELECT users.name, orders.order_date
    FROM users
    LEFT JOIN orders ON users.id = orders.user_id;
    

25. What is the difference between UNION and UNION ALL?

    • UNION removes duplicate rows, while UNION ALL includes duplicates.

26. What is an index in MySQL?

    • An index is used to speed up searches in a database.

27. How do you create an index?

    CREATE INDEX idx_name ON users(name);
    

28. What is normalization?

    • Normalization is the process of organizing data to reduce redundancy.

29. What is a stored procedure?

    • A stored procedure is a set of SQL statements saved for reuse.

30. How do you create a stored procedure?

    DELIMITER //
    CREATE PROCEDURE GetUsers()
    BEGIN
        SELECT * FROM users;
    END //
    DELIMITER ;
    

31. What is a trigger in MySQL?

    • A trigger is a procedure that executes automatically in response to an event.

32. What is a view?

    • A view is a virtual table based on a SQL query.

33. How do you create a view?

    CREATE VIEW user_view AS SELECT name, email FROM users;
    

34. How do you delete a view?

    DROP VIEW user_view;
    

35. What is a subquery?

    • A subquery is a query inside another query.

36. How do you use a subquery?

    SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);
    

37. What is MySQL replication?

    • Replication allows data from one MySQL server to be copied to another.

38. What is the difference between MyISAM and InnoDB?

    • InnoDB supports transactions, foreign keys, and row-level locking, while MyISAM is faster but lacks these features.

39. What is ACID in databases?

    • ACID stands for Atomicity, Consistency, Isolation, Durability.

40. What is the purpose of AUTO_INCREMENT?

    • It automatically generates unique numbers for a column.

Advanced MySQL Questions

41. What is a transaction in MySQL?

    • A transaction is a sequence of SQL operations executed as a single unit.

42. How do you start and commit a transaction?

    START TRANSACTION;
    UPDATE users SET name = 'New Name' WHERE id = 1;
    COMMIT;
    

43. How do you rollback a transaction?

    ROLLBACK;
    

44. What is a cursor in MySQL?

    • A cursor is used to retrieve row-by-row processing in stored procedures.

45. What is a deadlock in MySQL?

    • A deadlock occurs when two transactions block each other.

46. How do you optimize a MySQL query?

    • Use indexes, limit result sets, and avoid SELECT *.

47. How do you check database size?

    SELECT table_schema "Database", SUM(data_length + index_length) / 1024 / 1024 "Size (MB)" FROM information_schema.tables GROUP BY table_schema;
    

48. What is a composite key?

    • A composite key is a primary key that consists of multiple columns.

49. How do you backup a MySQL database?

    mysqldump -u root -p my_database > backup.sql
    

50. How do you restore a MySQL database?

    mysql -u root -p my_database < backup.sql
    

Advanced MySQL Questions (Continued)

51. What is the difference between HAVING and WHERE?

    • WHERE filters records before aggregation, while HAVING filters after aggregation.

    SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;
    

52. What is the difference between DISTINCT and GROUP BY?

    • DISTINCT removes duplicate values, while GROUP BY groups records and can use aggregate functions.

53. What are aggregate functions in MySQL?

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

54. How do you find the second highest salary?

    
    SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
    

55. How do you get the top N records from a table?

    SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
    

56. What is the default port for MySQL?

    • Port 3306.

57. How do you change a column’s data type?

    ALTER TABLE users MODIFY COLUMN name TEXT;
    

58. How do you add a new column to an existing table?

    ALTER TABLE users ADD COLUMN age INT;
    

59. How do you rename a column?

    ALTER TABLE users CHANGE COLUMN old_name new_name VARCHAR(100);
    

60. How do you remove a column from a table?

    ALTER TABLE users DROP COLUMN age;
    

61. What is the difference between NOW() and CURRENT_TIMESTAMP()?

    • Both return the current date and time, but NOW() allows fractional seconds.

62. What is a CASE statement?

    SELECT name, 
           CASE 
               WHEN age < 18 THEN 'Minor' 
               ELSE 'Adult' 
           END AS age_group
    FROM users;
    

63. How do you concatenate strings in MySQL?

    SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
    

64. How do you extract a substring in MySQL?

    SELECT SUBSTRING('Hello, World!', 1, 5); -- Output: Hello
    

65. How do you convert a string to uppercase in MySQL?

    SELECT UPPER('hello'); -- Output: HELLO
    

66. How do you convert a string to lowercase in MySQL?

    SELECT LOWER('HELLO'); -- Output: hello
    

67. How do you find the length of a string?

    SELECT LENGTH('MySQL'); -- Output: 5
    

68. How do you replace a substring in MySQL?

    SELECT REPLACE('Hello, World!', 'World', 'MySQL'); -- Output: Hello, MySQL!
    

69. What is the COALESCE function?

    • It returns the first non-null value from a list.

    SELECT COALESCE(NULL, NULL, 'Hello', 'World'); -- Output: Hello
    

70. How do you check for NULL values?

    SELECT * FROM users WHERE email IS NULL;
    

71. How do you set a default value for a column?

    ALTER TABLE users ALTER COLUMN status SET DEFAULT 'Active';
    

72. How do you drop an index?

    DROP INDEX idx_name ON users;
    

73. What is the IFNULL function?

    SELECT IFNULL(NULL, 'Default Value'); -- Output: Default Value
    

74. What is the difference between a clustered and non-clustered index?

    • A clustered index stores data physically in the order of the index.
    • A non-clustered index maintains a separate structure for searching.

75. How do you disable foreign key checks?

    SET FOREIGN_KEY_CHECKS = 0;
    

76. How do you enable foreign key checks?

    SET FOREIGN_KEY_CHECKS = 1;
    

77. How do you find duplicate records?

    SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
    

78. How do you remove duplicate records?

    DELETE u1 FROM users u1
    INNER JOIN users u2 
    WHERE u1.id > u2.id AND u1.email = u2.email;
    

79. What is a full-text search in MySQL?

    • A search technique that allows for complex text-based queries.

80. How do you create a full-text index?

    CREATE FULLTEXT INDEX idx_text ON articles(content);
    

81. What is a materialized view?

    • A materialized view is a stored query result.

82. What is MySQL Workbench?

    • A GUI tool for managing MySQL databases.

83. How do you convert a timestamp to a date?

    SELECT DATE(FROM_UNIXTIME(1672531199));
    

84. How do you get the current date?

    SELECT CURDATE();
    

85. How do you get the current time?

    SELECT CURTIME();
    

86. How do you calculate the difference between two dates?

    SELECT DATEDIFF('2025-01-01', '2024-01-01'); -- Output: 365
    

87. How do you add days to a date?

    SELECT DATE_ADD('2024-01-01', INTERVAL 30 DAY);
    

88. What is MySQL slow query log?

    • A log that tracks queries taking longer than a defined threshold.

89. How do you enable slow query log?

    SET GLOBAL slow_query_log = 'ON';
    

90. What is the MySQL Query Cache?

    • A cache mechanism to store query results for faster retrieval.

91. What is the MySQL binary log?

    • A log file that records all changes to the database.

92. How do you check running queries?

    SHOW PROCESSLIST;
    

93. How do you kill a running query?

    KILL query_id;
    

94. How do you change the root password in MySQL?

    ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
    

95. How do you flush privileges?

    FLUSH PRIVILEGES;
    

96. How do you check user privileges?

    SHOW GRANTS FOR 'user'@'localhost';
    

97. How do you list all databases?

    SHOW DATABASES;
    

98. How do you list all tables in a database?

    SHOW TABLES;
    

99. How do you optimize a table?

    OPTIMIZE TABLE users;
    

100. How do you repair a table?
     sql REPAIR TABLE users;