Combined MySQL Questions (Theoretical + Practical)

1. What is MySQL, and how is it different from other RDBMS like PostgreSQL?

2. Explain the difference between CHAR and VARCHAR data types in MySQL.

3. How does the WHERE clause work in a SELECT statement?

4. What is the difference between INNER JOIN and OUTER JOIN?

5. Explain the use of the GROUP BY clause in MySQL.

6. How does the HAVING clause differ from the WHERE clause?

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

8. Explain how indexing works in MySQL.

9. Describe the role of primary keys and foreign keys in MySQL.

10. How does MySQL handle NULL values in queries?

11. What are the different types of joins in MySQL?

12. Explain the use of the LIMIT clause in MySQL.

13. What is the difference between COUNT(*) and COUNT(column)?

14. How does the ORDER BY clause function in MySQL?

15. What is a MySQL transaction, and how do you use COMMIT and ROLLBACK?

16. Explain the purpose of AUTO_INCREMENT in MySQL.

17. How do you use the DATEDIFF function in MySQL?

18. What is the purpose of the TIMESTAMPDIFF function?

19. Describe how to use the YEAR() function in MySQL.

20. What is a subquery in MySQL, and where is it commonly used?

21. How do you enforce data integrity in MySQL?

22. Explain the difference between LEFT JOIN and RIGHT JOIN.

23. What is a CROSS JOIN in MySQL?

24. How does MySQL handle case sensitivity in queries?

26. What is the use of the DISTINCT keyword in MySQL?

27. How can you update multiple rows in MySQL?

28. Explain how to delete duplicate rows from a MySQL table.

29. What are the differences between NOW(), CURDATE(), and CURRENT_TIMESTAMP()?

30. How does the SUM() function work in MySQL?

31. Explain the use of AVG() in MySQL.

32. What is the difference between MIN() and MAX() in MySQL?

33. Describe how to use the IN and NOT IN operators in MySQL.

34. How do you use LIKE and NOT LIKE in MySQL?

35. What is the use of the CONCAT() function in MySQL?

36. Explain how to use the CASE statement in MySQL.

37. What are stored procedures in MySQL?

38. How do you create and use triggers in MySQL?

39. What is a MySQL view, and how is it created?

40. Explain the difference between CHAR and TEXT data types.

41. What is the difference between BLOB and TEXT in MySQL?

42. How do you handle errors in MySQL?

43. Describe the ALTER TABLE command and its uses.

44. What is the use of the DESCRIBE command?

45. How can you import and export data in MySQL?

46. What are MySQL user-defined functions?

47. How do you optimize a MySQL query?

48. Explain the difference between MyISAM and InnoDB storage engines.

49. How does MySQL handle concurrent connections?

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

51. How do you back up a MySQL database?

53. What is denormalization, and why is it used?

54. How do foreign key constraints work in MySQL?

55. How do you use IF statements in MySQL queries?

56. Explain how to handle transactions in MySQL.

57. What are the differences between static and dynamic SQL?

58. How does MySQL perform query optimization?

59. What is the purpose of the ANALYZE TABLE command?

60. How do you handle stored routines in MySQL?

61. Explain the use of MATCH and AGAINST operators for full-text search.

62. What is the difference between REPLACE and INSERT in MySQL?

63. How does the TRUNCATE command differ from DELETE?

64. What are ENUM and SET data types in MySQL?

65. What is the role of the FOREIGN_KEY_CHECKS variable?

66. How does MySQL handle default values for columns?

67. Explain how to retrieve metadata in MySQL.

68. What are MySQL privileges, and how do you grant them?

69. Describe how to reset a MySQL root password.

70. What are the key differences between FLOAT and DOUBLE in MySQL?

71. Retrieve all records from the employee table.

72. Select only the name and salary columns from the employee table.

73. Retrieve the first 5 rows from the employee table.

74. Retrieve the next 5 rows after the first 10 rows from the employee table.

75. Retrieve all employees with a salary greater than 50,000.

76. Retrieve employees whose department is 'Sales'.

77. Find the total salary paid in each department.

78. Count the number of employees in each department.

80. List employees ordered by salary in descending order.

81. Retrieve departments where the total salary exceeds 100,000.

82. List departments with more than 5 employees.

83. Calculate the average salary of employees in the employee table.

84. Find the maximum salary in the employee table.

85. Calculate the number of days each employee has worked since their hire date.

86. Calculate the number of months between the hire date and today's date.

87. Retrieve all employee names and their department names using INNER JOIN.

88. Retrieve all employees and their department names using LEFT JOIN.

89. Find all departments and the employees in them, including departments with no employees.

90. Retrieve all employees and their department names, even if they are not assigned a department.

91. Generate all possible pairs of employees from the employee table using CROSS JOIN.

92. Combine results from employee and dept tables using UNION.

93. Retrieve unique employee and manager names using UNION.

94. Retrieve all employees whose department ID is in (1, 2, 3).

95. Find employees not assigned to any of the departments listed in a specified set.

96. Retrieve the names of employees whose salary is above the average salary using a subquery.

97. Find the department with the maximum number of employees using a subquery.

98. Insert a new employee into the employee table.

99. Update the salary of all employees in the Sales department by 10%.

100. Delete all employees from the employee table whose salary is below 30,000.

101. Use SUM() to find the total payroll cost.

102. Use COUNT() to determine the number of employees hired in the current year.