MariaDB Interview Questions

A list of top frequently asked MariaDB interview questions and answers are given below

1) What is MariaDB?

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.

The latest version of MariaDB (version 10.4) also includes GIS and JSON features.

For more information: Click Here

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.

For more information: Click Here

3) 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:

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

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

Output:

Query OK, 1 row affected (0.01 sec) 

Output:

Query OK, 2 rows affected (0.00 sec) 

Output:

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

Warning:

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

Syntax:

For more information: Click Here

4) 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:

Example

For more information: Click Here

5) 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:

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

Output:

Query OK, 0 rows affected (0.39 sec)  

Output:

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

Output:

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

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

For more information: Click Here

6) 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:

For example

Output:

Query OK, 0 rows affected (0.312 sec) 

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

For more information: Click Here

7) How to delete a table in MariaDB’s database?

DROP TABLE command is used to delete a table from a database in MariaDB. It deletes the table permanently and cannot be recovered. You must have DROP privilege for each table. All table data and the table definitions are removed, as well as triggers associated with the table so very careful with this statement!

If any of the tables named in the argument list do not exist, MariaDB returns an error indicating by name which not existing tables it was not unable to drop, but it also drops all of the tables in the list that does exist.

Syntax:

Example

You can verify whether the table is deleted or not.

Output

For more information: Click Here

8) 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:

Or

Or you can use it also with WHERE condition

For example

Specify the column name:

Insert more than 1 row at a time:

Select from another table:

For more information: Click Here

9) How to retrieve records from a table in MongoDB database?

The SELECT statement is used to retrieve records from a table in the MongoDB database. You can choose, single, multiple or all records from a table by using different keywords.

Syntax:

FROM clause indicates the table or tables from which to retrieve rows.

The SELECT statement can be used with UNION statement, ORDER BY clause, LIMIT clause, WHERE clause, GROUP BY clause, HAVING clause, etc.

Example

We have a table “Students”, having some data. So retrieve all records from “Students”.

For more information: Click Here

10) How can you retrieve limited number of records from a table?

LIMIT clause is used with SELECT statement to select a limited number of records from a table. It facilitates you to retrieve records according to your use.

Syntax:

Example

Retrieve records in descending order:

Let’s use SELECT statement with LIMIT clause in “Students” table. The result is displayed in descending order and LIMIT is 4.

For more information: Click Here

11) How can you change or update the already inserted records of a MariaDB table?

The UPDATE statement is used to change, update or modify the existing records of a MariaDB table. It can be used with WHERE, ORDER BY and LIMIT clauses.

Syntax:

For example

We have a table “Test”, having the following data:

Let’s change the ‘title’ “Welcome to MariaDB” where ‘title’ was “Hello”.

For more information: Click Here

12) What is the use of DELETE statement in MariaDB?

The MariaDB DELETE statement is used to delete one or more records from the table in the database. It can be used to delete records from the table as well the whole table if you use it without WHERE condition.

Syntax:

Let’s delete data using one condition.

Example

The query is executed successfully. You can now see that selected data is deleted.

You can see that “Mahesh” is not available in the table.

Similarly, you can delete data using multiple conditions.

For more information: Click Here

13) 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:

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”.

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.

Output:

No record found. 

For more information: Click Here

14) What is an aggregate function? How many types of aggregate functions in MariaDB?

In relational database management system, aggregate functions are the functions where the values of multiple rows are grouped together as input on certain criteria and provide a single value of more significant meaning such as a list, set, etc.

Following is a list of aggregate function in MariaDB:

MariaDB COUNT Function: In MariaDB database, COUNT function is used to return the count of an expression.

Syntax:

The COUNT () Function counts only NOTNULL values.

MariaDB SUM Function: MariaDB SUM function is used to return the summed value of an expression.

Syntax:

MariaDB MIN Function: MariaDB MIN () function is used to retrieve the minimum value of the expression.

Syntax:

MariaDB MAX Function: MariaDB MAX () function is used to retrieve the maximum value of the expression.

Syntax:

MariaDB AVG Function: MariaDB AVG() function is used to retrieve the average value of an expression.

Syntax:

Or

MariaDB BIT_AND Function: Returns the bitwise AND of all bits in exp.

Syntax:

MariaDB BIT_OR: Returns the bitwise OR of all bits in exp.

Syntax:

MariaDB BIT_XOR: Returns the bitwise XOR of all bits in exp.

Syntax:

15) What are the different types of clauses used in MariaDB?

MariaDB supports all clauses used in RDBMS. For example:

MariaDB Where Clause: In MariaDB, WHERE clause is used with SELECT, INSERT, UPDATE and DELETE statement to select or change a specific location where we want to change.

It has appeared after the table name in a statement.

Syntax:

Note: WHERE clause is an optional clause. It can be used with AND, OR, AND & OR, LIKE operators.

MariaDB Like Clause: In MariaDB, LIKE clause is used with SELECT statement to retrieve data when an operation needs an exact match. It can be used with SELECT, INSERT, UPDATE and DELETE statement.

It is used for pattern matching and returns a true or false. The patterns used for comparison accept the following wildcard characters:

“%” wildcard character: It matches numbers of characters (0 or more).

“_” wildcard character: It matches a single character. It matches characters within its set.

Syntax:

MariaDB Order By Clause: In MariaDB database, ORDER BY Clause is used to sort the records in your result set in ascending or descending order.

Syntax:

Note: You can sort the result without using ASC/DESC attributes. By default, the result will be stored in ascending order.

MariaDB DISTINCT Clause: MariaDB DISTINCT Clause is used to remove duplicates from the result when we use it with a SELECT statement.

Syntax:

Note: When you use the only expression in a DISTINCT clause, the query will return the unique values for that expression. When you use multiple expressions in the DISTINCT clause, the query will return unique combinations for the multiple expressions listed.

The DISTINCT clause doesn’t ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.

MariaDB FROM Clause: MariaDB FROM Clause is used to fetch data from a table. It is also used to join the tables which you will study later.

Syntax:

Etc.

16) What is the use of WHERE clause?

The WHERE clause is used to select or change a specific location to fetch the records from a table. It is used with SELECT, INSERT, UPDATE and DELETE statement.

Syntax:

WHERE Clause with Single Condition

Example

We have a table “Students” having some data. Let’s retrieve all records from the “Student” table where student_id is less than 6.

Output:

For more information: Click Here

17) What is the use of LIKE clause in MariaDB?

MariaDB LIKE clause is used with SELECT, INSERT, UPDATE and DELETE statement to retrieve data when an operation needs an exact match.

It is used for pattern matching and returns a true or false. The patterns used for comparison accept the following wildcard characters:

“%” wildcard character: It matches numbers of characters (0 or more).

“_” wildcard character: It matches a single character. It matches characters within its set.

Syntax:

We have a table “Employees”, having the following data.

Let’s use % wildcard with LIKE condition to find all of the names which begins with “L”.

For more information: Click Here

18) What is the use of ORDER BY clause in MariaDB?

MariaDB ORDER BY Clause is used to sort the records in your result set in ascending or descending order.

Note: You can sort the result without using ASC/DESC attribute. By default, the result will be stored in ascending order.

Syntax:

ORDER BY Clause without using ASC/DESC attributes:

“Employees” table, having the following data:

Output:

For more information: Click Here

19) What is the use of MariaDB DISTINCT clause?

MariaDB DISTINCT Clause is used to remove duplicates from the result when it is used with a SELECT statement.

Syntax:

Note: When you use the only expression in the DISTINCT clause, the query will return the unique values for that expression. When you use multiple expressions in the DISTINCT clause, the query will return unique combinations for the multiple expressions listed.

The DISTINCT clause doesn’t ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.

Single Expression:

We have a table name “Students”, having some duplicate entries. A name “Ajeet” is repeated three times.

Let’s use the DISTINCT clause to remove duplicates from the table.

Output:

You can see that “Ajeet” is repeated three times in the original “Students” table but after using DISTINCT clause, it is returned one time and duplicate entries are deleted.

For more information: Click Here

20) Why do we use FROM clause with SELECT statement?

The FROM clause is used with SELECT statement to retrieve data from the table. It is also used to join tables.

Syntax:

Example

Let’s retrieve all employees from the table “Employees”.

Output

As we know that FROM clause used along with the SELECT clause to join the data of two tables too.

Let’s take an example of INNER JOIN: one of the most common types of join which returns all rows from multiple tables where the join condition is satisfied.

We have two tables “Student” and “Employee”.

Use the following syntax to join both tables according to the given parameters:

Output:

For more information: Click Here

21) What is the use of COUNT() aggregate function?

MariaDB COUNT() aggregate function is used to return the count of an expression.

The COUNT () Function counts only NOT NULL values.

COUNT (*) counts the total number of rows in a table.

COUNT () would return 0 if there were no matching rows.

Syntax:

Example

We have a table “Students”, having the following data:

Count “student_id” from “Students” table:

For more information: Click Here

22) What is the use of MariaDB SUM() function?

MariaDB SUM function is used to return the summed value of an expression.

If the table has no any rows, then SUM () returns NULL. The DISTINCT keyword is also used with SUM () to sum only the distinct values of an expression.

Syntax:

Example

Table: EMP

Output:

5000 

For more information: Click Here

23) What is the usage of MIN() function in MariaDB?

MariaDB MIN() function is used to retrieve the minimum value of the expression.

MIN () can take string argument too, in which case it returns the minimum string values.

MIN() returns NULL if there were no matching rows.

Syntax:

Example

We have a table “Student”, having the following data:

Let’s retrieve lowest salary by using MIN () function.

Output:

Let’s take another example:

To check MIN string:

For more information: Click Here

24) What is the usage of MAX() function in MariaDB?

MariaDB MAX() function is used to retrieve the maximum value of the expression.

MAX () can take string argument too, in which case it returns the maximum string values.

MAX () returns NULL if there were no matching rows.

Syntax:

Example

We have a “student” table

To list out the student name with maximum score:

To check maximum string name:

For more information: Click Here

25) What is the usage of AVG() function in MariaDB database?

MariaDB AVG() function is used to retrieve the average value of an expression.

AVG() returns NULL if there were no matching rows.

Syntax:

Or

Example

We have a table “Employee2”, having the following data:

Let’s retrieve the average salary of the employees from the table.

Output

Note: We can Use Average function With formula and ORDER BY clause too.

For more information: Click Here

26) What is JOIN? How many types of JOIN in MariaDB?

JOIN is used to retrieve data from two or more tables. By default, JOIN is also called INNER JOIN. It is used with SELECT statement.

There are mainly two types of joins in MariaDB:

INNER JOIN:

MariaDB INNER JOIN is the most common type of join which returns all rows from multiple tables where the join condition is satisfied.

Syntax:

Example

We have two tables “Students” and “Employee2”.

Student table

Employee2 Table

Execute the following commands:

Output

OUTER JOIN:

Again OUTER JOIN is divided into two types:

LEFT JOIN:

MariaDB LEFT OUTER JOIN is used to return all rows from the left-hand table specified in the ON condition and only those rows from the other table where the joined condition is satisfied.

LEFT OUTER JOIN is also called LEFT JOIN.

Syntax:

Example

Output

RIGHT JOIN:

MariaDB RIGHT OUTER JOIN is used to return all rows from the right-hand table specified in the ON condition and only those rows from the other table where the joined fields are satisfied with the conditions.

MariaDB RIGHT OUTER JOIN is also called RIGHT JOIN.

Syntax:

Example

27) What is MariaDB INNER JOIN?

MariaDB INNER JOIN is the most common type of join which returns all rows from multiple tables where the join condition is satisfied.

Syntax:

Example:

We have two tables’ sites and pages:

Sites table:

Pages table:

Now execute the following commands:

Output:

For more information: Click Here

28) What is LEFT OUTER JOIN in MariaDB?

MariaDB LEFT OUTER JOIN is used to return all rows from the left-hand table specified in the ON condition and only those rows from the other table where the joined condition is satisfied.

LEFT OUTER JOIN is also called LEFT JOIN.

Syntax:

Example

We have two tables’ sites and pages:

Sites table:

Pages table:

Now execute the following commands:

Output:

Site_name Google.com is also included because of LEFT JOIN.

For more information: Click Here

29) What is RIGHT OUTER JOIN in MariaDB?

MariaDB RIGHT OUTER JOIN is used to return all rows from the right-hand table specified in the ON condition and only those rows from the other table where the joined fields are satisfied with the conditions.

MariaDB RIGHT OUTER JOIN is also called RIGHT JOIN.

Syntax:

Example

We have two tables’ sites and pages:

Sites table:

Pages table:

Now execute the following commands:

Output:

Here page_id and page_title contains value because of RIGHT JOIN.

For more information: Click Here

30) What is function in MariaDB? How can you create and drop a function in MariaDB?

MariaDB function is a stored program that is used to pass parameters into them and return a value

We can easily create and drop functions in MariaDB.

# Create Function (MariaDB):

You can create your own function in MariaDB:

Syntax:

Example

Create a function CalcValue in MariaDB database.

DEFINER clause: it is an optional clause. If not specified, the definer is the user that created the function. If you wish to specify a different definer, you must include the DEFINER clause where user_name is the definer for the function.

function_name: It specifies the name to assign to this function in MariaDB.

return_datatype: It specifies the data type of the function’s return value.

LANGUAGE SQL: It is in the syntax for portability but will have no impact on the function.

DETERMINISTIC: It means that the function will always return one result given a set of input parameters.

NOT DETERMINISTIC: It means that the function may return a different result given a set of input parameters. The result may be affected by table data, random numbers or server variables.

CONTAINS SQL: It is the default. It is an informative clause that tells MariaDB that the function contains SQL, but the database does not verify that this is true.

No SQL: An informative clause that is not used and will have no impact on the function.

READS SQL DATA: An informative clause that tells MariaDB that the function will read data using SELECT statements but does not modify any data.

MODIFIES SQL DATA: An informative clause that tells MariaDB that the function will modify SQL data using INSERT, UPDATE, DELETE, or other DDL statements.

declaration_section: The place in the function where you declare local variables.

executable_section: The place in the function where you enter the code for the function.

Output:

MariaDB DROP Function

You can drop your created function very easily from your database.

Syntax:

Parameter Explanation

function_name: It specifies the name of the function that you want to drop.

Example

We have created a function name “CalcValue”. Now drop the function.

Now you can see that function is deleted and not present in the list anymore.

31) What is a procedure or a stored procedure in the database?

Procedures are sort of functions in a database. Procedures are created when you want to perform a task repetitively.

MariaDB procedure is a stored program that is used to pass parameters into it. It does not return a value as a function does.

You can create and drop procedures like functions.

# Create Procedure (MariaDB):

You can create your procedure just like you create a function in MariaDB.

Syntax:

Parameter Explanation

DEFINER clause: Optional.

procedure_name: The name to assign to this procedure in MariaDB.

Parameter: One or more parameters passed into the procedure. When creating a procedure, there are three types of parameters that can be declared:

IN: The parameter can be referenced by the procedure. The value of the parameter cannot be overwritten by the procedure.

OUT: The parameter cannot be referenced by the procedure, but the value of the parameter can be overwritten by the procedure.

IN OUT: The parameter can be referenced by the procedure, and the value of the parameter can be overwritten by the procedure.

LANGUAGE SQL: It is in the syntax for portability but will have no impact on the function.

DETERMINISTIC: It means that the function will always return one result given a set of input parameters.

NOT DETERMINISTIC: It means that the function may return a different result given a set of input parameters. The result may be affected by table data, random numbers or server variables.

CONTAINS SQL: It is the default. It is an informative clause that tells MariaDB that the function contains SQL, but the database does not verify that this is true.

No SQL: It is an informative clause that is not used and will have no impact on the function.

READS SQL DATA: It is an informative clause that tells MariaDB that the function will read data using SELECT statements but does not modify any data.

MODIFIES SQL DATA: It is an informative clause that tells MariaDB that the function will modify SQL data using INSERT, UPDATE, DELETE, or other DDL statements.

declaration_section: The place in the procedure where you declare local variables.

executable_section: The place in the procedure where you enter the code for the procedure.

Example

Create a procedure named “CalcValue” in MariaDB database.

Procedure is created successfully:

You can refer your new procedure as follows

MariaDB DROP Procedure:

You can drop procedure by using the following command:

Syntax:

Parameter Explanation:

procedure_name: It specifies the name of the procedure that you want to drop.

Example

You can see that the procedure is dropped now and it is not available in the list.