MariaDB Interview Questions

MariaDB Interview

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.

https://techzone360.shop/mariadb-interview-questions/

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:

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
Code
Message
Note
1007
Can’t create database ‘student’ ; database exists
SHOW DATABASE: This command is used to see the database you have created

Syntax:

SHOW DATABASES;
For more information: Click Here

4) 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
For more information: Click Here

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

CREATE TABLE table_name (column_name column_type);
For example

1. CREATE TABLE Students(
2. student_id INT NOT NULL AUTO_INCREMENT,
3. student_name VARCHAR(100) NOT NULL,
4. student_address VARCHAR(40) NOT NULL,
5. admission_date DATE,
6. 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;
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:

DROP TABLE table_name ;
Example

Drop the table “Students” created within “Javatpoint” database.
DROP TABLE Students;
Mariadb Drop table 1
You can verify whether the table is deleted or not.

SHOW TABLES; //command
Output

Mariadb Drop table 2
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:

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

1. INSERT INTO
2. (column1, column2,… )
3. VALUES
4. (expression1, expression2, … ),
5. (expression1, expression2, … ),
6. …;
   Or you can use it also with WHERE condition
7. INSERT INTO table
8. (column1, column2, … )
9. SELECT expression1, expression2, …
10. FROM source_table
11. [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’;
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:

SELECT expressions
FROM tables
[WHERE conditions];
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.

SELECT [ ALL | DISTINCT ]
expressions
FROM tables
[WHERE conditions]
[GROUP BY expressions]
[HAVING condition]
[ORDER BY expression [ ASC | DESC ]];
Example

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

SELECT * FROM Students;
Mariadb Select data 1
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:

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
LIMIT row_count;
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.

SELECT student_id, student_name, student_address
FROM Students
WHERE student_id <= 7
ORDER BY student_id DESC
LIMIT 4;
Mariadb Select limit 1
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:

UPDATE table_name SET field=new_value, field2=new_value2,…
[WHERE …]
For example

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

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

Mariadb Select limit 1
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:

DELETE FROM table
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT number_rows];
Let’s delete data using one condition.

Example

DELETE FROM Students
WHERE student_name = ‘Mahesh’;
Mariadb Delete data 1
The query is executed successfully. You can now see that selected data is deleted.

SELECT * FROM Students;
Mariadb Delete data 2
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:

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

SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions];
The COUNT () Function counts only NOTNULL values.

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

Syntax:

SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];
MariaDB MIN Function: MariaDB MIN () function is used to retrieve the minimum value of the expression.

Syntax:

SELECT MIN(aggregate_expression)
FROM tables
[WHERE conditions];
MariaDB MAX Function: MariaDB MAX () function is used to retrieve the maximum value of the expression.

Syntax:

SELECT MAX(aggregate_expression)
FROM tables
[WHERE conditions];
MariaDB AVG Function: MariaDB AVG() function is used to retrieve the average value of an expression.

Syntax:

SELECT AVG(aggregate_expression)
FROM tables
[WHERE conditions];
Or

SELECT expression1, expression2, … expression_n,
AVG(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n;
MariaDB BIT_AND Function: Returns the bitwise AND of all bits in exp.

Syntax:

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

Syntax:

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

Syntax:

BIT_XOR (exp)
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:

[COMMAND] field,field2,… FROM table_name,table_name2,… WHERE [CONDITION]
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:

SELECT field, field2,…. FROM table_name, table_name2,…
WHERE field LIKE condition
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:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];
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:

SELECT DISTINCT expressions
FROM tables
[WHERE conditions];.
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:

SELECT columns_names FROM table_name;
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:

[COMMAND] field,field2,… FROM table_name,table_name2,… WHERE [CONDITION]
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.

SELECT *
FROM Students
WHERE student_id < 6;
Output:

Mariadb Where clause 1

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:

SELECT field, field2,… FROM table_name, table_name2,…
WHERE field LIKE condition