Maria DB Interview Questions & Answers

Basic Questions:

1. What is MariaDB?

   • MariaDB is an open-source relational database management system (RDBMS) and a fork of MySQL.

2. How is MariaDB different from MySQL?

   • MariaDB is a community-developed fork of MySQL, created by the original developers of MySQL. It aims to be fully compatible with MySQL while adding new features.

3. Explain the key features of MariaDB.

   • ACID compliant, supports multiple storage engines, has a pluggable storage engine architecture, and is fully compatible with MySQL.

4. How do you install MariaDB?

   • Installation methods may vary, but on Linux, you can use commands like:

   sudo apt-get install mariadb-server
   

5. What is the default port number for MariaDB?

   • The default port number for MariaDB is 3306.

SQL Queries:

6. What is the syntax to create a table in MariaDB?

   CREATE TABLE table_name (
       column1 datatype,
       column2 datatype,
       ...
   );
   

7. How do you insert data into a table?

   INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
   

8. Explain the SELECT statement in MariaDB.

   • The SELECT statement is used to query the database and retrieve data.

9. How can you update data in a table?

   UPDATE table_name SET column1 = value1 WHERE condition;
   

10. What is the purpose of the WHERE clause in SQL?

    • The WHERE clause filters records based on a specified condition.

Constraints and Indexing:

11. What is a primary key?

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

12. Explain the UNIQUE constraint.

    • The UNIQUE constraint ensures that all values in a column are distinct.

13. What is an index in MariaDB?

    • An index is a data structure that improves the speed of data retrieval operations on a database.

14. How do you create an index on a table column?

    CREATE INDEX index_name ON table_name (column1, column2, ...);
    

Joins and Subqueries:

15. What is a JOIN in SQL?

    • JOIN is used to combine rows from two or more tables based on a related column.

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

    • INNER JOIN returns only matching rows, while LEFT JOIN returns all rows from the left table and the matching rows from the right table.

17. What is a subquery?

    • A subquery is a query nested inside another query.

18. Give an example of a correlated subquery.

    • A correlated subquery refers to a column in the outer query, such as:

    SELECT column1 FROM table1 WHERE column2 > (SELECT AVG(column2) FROM table1);
    

Transactions and ACID Properties:

19. What does ACID stand for?

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

20. Explain the concept of a transaction in MariaDB.

    • A transaction is a sequence of one or more SQL statements executed as a single unit of work.

21. How can you ensure the ACID properties in MariaDB?

    • MariaDB automatically ensures ACID properties for each transaction.

Views and Triggers:

22. What is a view in MariaDB?

    • A view is a virtual table based on the result of a SELECT query.

23. How do you create a view?

    CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
    

24. Explain triggers in MariaDB.

    • A trigger is a set of instructions that are automatically executed or fired in response to certain events.

25. Give an example of using a trigger.

    CREATE TRIGGER trigger_name
    AFTER INSERT ON table_name
    FOR EACH ROW
    BEGIN
        -- Trigger logic here
    END;
    

Backup and Restore:

26. How can you backup a MariaDB database?

    • Use the mysqldump command, like:

    mysqldump -u username -p dbname > backup.sql
    

27. Explain the process of restoring a MariaDB database from a backup.

    • Use the mysql command, like:

    mysql -u username -p dbname < backup.sql
    

Data Types:

28. Name some common data types in MariaDB.

    • INTEGER, DECIMAL, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME.

29. Explain the INTEGER data type.

    • INTEGER is a whole number data type in MariaDB.

30. What is the purpose of the BLOB data type?

    • BLOB (Binary Large Object) is used to store binary data.

Security:

31. How can you secure a MariaDB database?

    • Use strong passwords, limit user privileges, and regularly update MariaDB.

32. Can MariaDB databases be encrypted?

    • Yes, MariaDB supports database encryption using features like TDE (Transparent Data Encryption).

Advanced Queries:

33. Explain the GROUP BY clause.

    • GROUP BY is used to group rows that have the same values in specified columns into summary rows.

34. What is the HAVING clause?

    • HAVING is used to filter the results of a GROUP BY clause.

35. How can you perform a case-insensitive search in MariaDB?

    • Use the COLLATE NOCASE option in the WHERE clause, like:

    SELECT column1 FROM table1 WHERE column2 COLLATE NOCASE = 'value';
    

Performance Optimization:

36. How can you improve the performance of a query in MariaDB?

    • Use indexes, optimize queries, and analyze query execution plans.

37. Explain the EXPLAIN command in MariaDB.

    • The EXPLAIN command is used to analyze the query plan generated by the query optimizer.

Date and Time:

38. How does MariaDB handle date and time?

    • MariaDB uses DATE, TIME, DATETIME, and TIMESTAMP data types to handle date and time values.

39. How can you perform date and time operations in MariaDB?

    • Use built-in date and time functions like DATE_ADD, DATE_SUB, and NOW.

Error Handling:

40. What is the purpose of the DECLARE...HANDLER statement in MariaDB?
    • The DECLARE...HANDLER statement is used for error handling in stored procedures.

Foreign Keys:

41. Does MariaDB support foreign keys?
    • Yes, MariaDB supports foreign keys and enforces referential integrity.

42

. How do you create a foreign key in MariaDB? - Use the FOREIGN KEY constraint in the CREATE TABLE statement.

Integration with Programming Languages:

43. Which programming languages can be used with MariaDB?

    • MariaDB has connectors for many programming languages, including PHP, Python, Java, and more.

44. How can you interact with MariaDB using Python?

    • Use libraries like mysql-connector-python or pymysql to connect, execute queries, and fetch results.

Optimization Techniques:

45. Explain the OPTIMIZE TABLE command in MariaDB.

    • The OPTIMIZE TABLE command is used to reclaim unused space and defragment the table.

46. What is the purpose of the ANALYZE TABLE command in MariaDB?

    • The ANALYZE TABLE command gathers statistics about the distribution of keys in each index.

Galera Cluster (if applicable):

47. What is the Galera Cluster?
    • The Galera Cluster is a synchronous multi-master cluster for MariaDB, allowing for active-active replication.

Integration with Web Development:

48. How can you use MariaDB with web development?
    • MariaDB is commonly used with web development frameworks and libraries, such as Django (Python) and Express (Node.js).

Miscellaneous:

49. Explain the difference between AUTO_INCREMENT and the IDENTITY column.

    • In MariaDB, AUTO_INCREMENT is used to generate a unique integer for each new row. IDENTITY is not used in MariaDB.

50. How can you check the version of MariaDB?

    • Use the following SQL query:

    SELECT VERSION();