MariaDB Interview Questions and Answers

1 .

MariaDB is a popular, open source, and the community-based project developed by MySQL developers. It is a relational database management technology which provides the same features as MySQL. It is a new replacement for MySQL.

MariaDB turns data into structured wide array of applications, ranging from banking to websites. MariaDB is used because it is fast, scalable, and robust with a reach ecosystem of storage engine, plugin, and many other tools make it versatile for a wide variety of use cases.

2 .

What are the main features of MariaDB?

MariaDB provides the same features of MySQL with some extensions. It is relatively new and advance.

A list of the features of MariaDB :

* MariaDB can run on different operating systems and support a wide variety of programming languages.

* MariaDB is licensed under GPL, LGPL, or BSD.

* MariaDB follows a standard and popular query language.

* MariaDB provides Galera cluster technology.

* MariaDB provides supports for PHP which is the most popular web development language.

* MariaDB includes a wide selection of storage engines, including high-performance storage engines for working with other RDBMS data sources.

* MariaDB also offers many operations and commands unavailable in MySQL and eliminates/replaces features impacting performance negatively.

* MariaDB's speed is one of its prominent features. It is remarkably scalable and can handle tens of thousands of tables and billions of rows of data.

3 .

What is the main objective of MariaDB?

The main purpose of MariaDB is to supply a better, firm and community developed and also that is compatible with MySQL. It is always free DBMS on the basic level.

4 .

Why it is the name as MariaDB?

The company says that MariaDB is a modification of MySQL. MySQL is linked to the Oracle because the trademark is suggested by Oracle. Thus, the company decided to continue this trademark. As MySQL name has come from Monty's first daughter "My", so, for maintaining the continuity MariaDB has come from elder daughter "Maria".

5 .

Can a normal being contribute to MariaDB?

Yes, if any person is willing to participate in contributing the MariaDB they can do. They just have to fill a form that will help the person to gather around his/her team to work over projects. These projects help the MariaDB to be a better future. The projects are like writing or translations of KB essay type.

6 .

Who is the founder of MariaDB?

MariaDB has founded by the Michael “Monty” Widenius, the founder of MYSQL. The main focus of MariaDB foundation to keep the quality of MariaDB project high.

7 .

What do you suggest by MariaDB Galera Cluster?

MariaDB Galera Cluster is also referred to as multi-master cluster of MariaDB. MariaDB Galera Cluster is accessible only on the Linux and also supports either XtraDB or InnoDB storage engines.

MariaDB "Galera Cluster 10.1" is incorporated by default but in MariaDB Galera Cluster 10.0 and MariaDB Galera Cluster 5.5, it has to download individually.

8 .

How to create database in MariaDB?

CREATE DATABASE command is used to create a database in MariaDB, CREATE SCHEMA is a synonym for creating a database.

Syntax :

CREATE DATABASE Database_name;

If the optional OR REPLACE clause is used, it acts as a shortcut for :

DROP DATABASE IF EXISTS db-name;  
CREATE DATABASE db-name;

IF NOT EXISTS :

When IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the specified database is already exist.

For example :

CREATE DATABASE student;

Output :

Query OK, 1 row affected (0.01 sec)

CREATE OR REPLACE DATABASE student;

Output :

Query OK, 2 rows affected (0.00 sec)

CREATE DATABASE IF NOT EXISTS student;

Output :

Query OK, 1 row affected, 1 warning (0.01 sec)

Warning :

Level :Note

Code : 1007
Message : Can't create database 'student' ; database exists

SHOW DATABASE : This command is used to see the database you have created

Syntax :

SHOW DATABASES;

9 .

How to use database in MariaDB?

USE DATABASE command is used to select and use a database in MariaDB. The USE db-name' statement tells MariaDB to use the db_name database as default (current) database for subsequent statements. The database remains the default until the end of the session, or another USE statement is issued:

Syntax :

USE database_name;

Example :

USE student;  
SELECT COUNT (*) FROM mytable;    # selects from student.mytable  
USE faculty;  
SELECT COUNT (*) FROM mytable;    # selects from faculty.mytable  
The DATABASE () and SCHEMA () returns the default database.

10 .

How to delete a database in MariaDB ?

DROP DATABASE command is used to drop a database in MariaDB. Be very careful with this statement! To use a DROP DATABASE, you need to DROP privileges on the database. DROP SCHEMA is a synonym for DROP DATABASE

NOTE : When a database is dropped, user privileges on the database are not automatically

Syntax :

DROP DATABASE Database_name;

IF EXISTS statement :

Use IF EXISTS to prevent an error from occurring for the database that does not exist. A note is generated for each non-existent database when using IF EXISTS statement.

Example :

DROP DATABASE student;

Output :

Query OK, 0 rows affected (0.39 sec)

DROP DATABASE student;

Output :

ERROR (1008): can't drop database; database doesn't exists [\]w: show warning enabled

DROP DATABASE IF EXISTS student;

Output :

Query OK, 0 rows affected, 1 warning (0.00 sec)

Note (code 1008) : can't drop database 'student'; database doesn't exists

11 .

How to create a table in MariaDB's database?

First, you have to create a database in MariaDB follows by selecting the database and then create a table by using the CREATE TABLE statement. You must have the CREATE privilege for a table or on the database to create a table.

Create table statement creates a table name followed by a list of columns, indexes, and constraints. By default, a table is created in the default database

Syntax :

CREATE TABLE table_name (column_name column_type);

For example :

CREATE TABLE Students(    
student_id INT NOT NULL AUTO_INCREMENT,    
student_name VARCHAR(100) NOT NULL,    
student_address VARCHAR(40) NOT NULL,    
admission_date DATE,    
PRIMARY KEY ( student_id ));

Output :

Query OK, 0 rows affected (0.312 sec)

You can verify that whether the table is created by using SHOW TABLES command.

SHOW TABLES;

12 .

What do you understand by 'The Max Storage Capacity of Version 5.5 MariaDB'?

Habitually the internal limits of MariaDB are enough for the size and space limits goals of storage and operating system as its InnoDB/XtraDB can reach the size up to 64 Terabytes. Although the person may have a great number of tables per databases and so many numbers of databases per server it is enough.

13 .

List some tools of Gui/workbench For MariaDB Aria?

There are some important tools of Gui/workbench for MariaDB Aria. These are

* Webyog/SQLyog

* HeidiSQL

* dbForge Studio for MySQL

* MySQL Workbench.

In spite of above all, the company is still trying to add some more support function.

14 .

How many types of procedures are there in MariaDB database?

There are 3 types of procedures in MariaDB database on the basis of references and parameter overwritten. These are

* IN procedure : In this type of procedure, the values of a parameter can be overwritten and referenced by the procedure.

* OUT procedure : In this type of procedure, the values of parameter can be overwritten by the procedure but it can’t be referenced by a procedure.

* IN OUT : In this type of procedure, the values of a parameter can be referenced by the procedure but it can’t be overwritten by a procedure.

15 .

what are the steps to change Innodb_page_size?

It does need to do anything with a new install. There are steps to follow if you want to change the size. These are :

* Firstly, go through your data and export it by moving it or by deleting the database and folders.

* When it is done, fixed innobd_page_size to any of the 4k or 8k

* After that, restart the MariaDB.

* When it is on, there appears a new XtraDB but having smaller page size.

* Now you are free to introduce your data and do what you want to do.

So, in this whole procedure, you don't need to do anything with the new installment.

16 .

Define DDL, DML and DCL commands in MariaDB/any RDBMS ?

Any RDBMS SQL Queries/commands can be divided into three major subgroups.

* DDL (Data Definition Language ) which deals with database schemas and descriptions of how the data should reside in the database, thus the language statements like CREATE TABLE / ALTER TABLE belong to DDL

* DML (Data Manipulation Language ) which deals with data manipulation, and therefore includes most common SQL statements such as SELECT, INSERT, etc.

* DCL (Data Control Language ) which includes commands such as GRANT, and mostly concerns with rights, permissions, privileges and other controls of the database system.

17 .

What Is MariaDB Transaction?

Any RDBMS Transaction is a logical unit of work requested by a user to be applied on the database objects. MariaDB server introduces the transaction concept to allow users to group one or many SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

18 .

How do we get the number of rows affected by any query ?

Before executing any update/delete on the table data, it’s always best practice to check the number of rows that are going to be affected i.e., updated / removed from table with COUNT() operation with the given WHERE condition to cross check whether we’re doing correct update or not

SELECT COUNT(user_id) FROM users WHERE <>;

will only return the number of user_id s going to effect.

19 .

How to insert records in a table in MariaDB database?

INSERT INTO statement is used to insert records in a table in the MariaDB database.

Syntax :

INSERT INTO tablename (field, field2,...) VALUES (value, value2,...);

Or

INSERT INTO     
(column1, column2,... )    
VALUES    
(expression1, expression2, ... ),    
(expression1, expression2, ... ),    
 ...;

Or you can use it also with WHERE condition

INSERT INTO table    
(column1, column2, ... )    
SELECT expression1, expression2, ...    
FROM source_table    
[WHERE conditions];

For example

Specify the column name :

INSERT INTO person (first_name, last_name) VALUES ('Mohd', 'Pervez');

Insert more than 1 row at a time :

INSERT INTO abc VALUES (1,"row 1"), (2, "row 2");

Select from another table :

INSERT INTO abc SELECT * FROM person WHERE status= 'c';

20 .

What is the use of TRUNCATE statement? How is it different from DELETE statement?

TRUNCATE TABLE statement is used to delete a table permanently. It deletes all the records from the table.

Syntax :

TRUNCATE [TABLE] [database_name.]table_name;

Difference between DELETE and TRUNCATE statement :

* DELETE statement is used to remove one or more columns from a table as well as the whole table. On the other hand, the TRUNCATE TABLE statement is used to delete the whole table permanently.

* TRUNCATE TABLE statement is same as DELETE statement without a WHERE clause.

* DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.

* TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and record only the page deallocations in the transaction log. Hence it is faster than delete statement.

Example :

Let's truncate the table "Students".

TRUNCATE TABLE javatpoint.Students;

Output :

Query OK, 0 rows affected (0.031sec).

The TRUNCATE query is executed successfully. You can see that the records of "Student" table have been deleted permanently.

SELECT * FROM Students;

Output :

No record found