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:

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

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:

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:

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

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

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

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.

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

Drop the table "Students" created within "Javatpoint" database.
DROP TABLE Students;

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:

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

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

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;  

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:

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:

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';   

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:

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];   

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:

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:

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:

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.

SELECT *   
FROM Students  
WHERE student_id < 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:

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

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

SELECT name  
FROM Employees  
WHERE name LIKE '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:

SELECT expressions    
FROM tables    
[WHERE conditions]    
ORDER BY expression [ ASC | DESC ];     

ORDER BY Clause without using ASC/DESC attributes:

"Employees" table, having the following data:

Id	Name	address
1	Lucky	Australia
2	Mayank	Ghaziabad
3	Rahul	Noida
4	Lily	LA

SELECT * FROM Employees  
WHERE name LIKE '%L%' 
ORDER BY id;   

Output:

Id	Name	address
4	Lily	LA
3	Rahul	Noida
2	Mayank	Ghaziabad
1	Lucky	Australia

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:

SELECT DISTINCT expressions    
FROM tables    
[WHERE conditions];

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.

SELECT DISTINCT student_name  
FROM Students  
WHERE student_name = 'Ajeet';   

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:

SELECT Students.student_id, Students.student_name, Employee.salary  
FROM Students   
INNER JOIN Employee
ON Students.student_id = Employee.emp_id;   

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:

SELECT COUNT(aggregate_expression)    
FROM tables    
[WHERE conditions];      

Example

We have a table "Students", having the following data:

Count "student_id" from "Students" table:

SELECT COUNT(student_id)  
FROM Students;   

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:

SELECT SUM(aggregate_expression)    
FROM tables    
[WHERE conditions];   

Example

CREATE TABLE EMP (emp_id, emp_salery);
INSERT INTO EMP VALUES (1,1000),(2,2000),(3,5000);
SELECT *FROM EMP;

Table: EMP

emp_id	emp_salery
1	1000
2	2000
3	5000

Output:

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:

SELECT MIN(aggregate_expression)    
FROM tables    
[WHERE conditions];      

Example

We have a table "Student", having the following data:

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

SELECT MIN(salary) AS "Lowest Salary"  
FROM Student;   

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:

SELECT MAX(aggregate_expression)    
FROM tables    
[WHERE conditions];

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:

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

SELECT expression1, expression2, ... expression_n,  
AVG (aggregate_expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n;   

Example

We have a table "Employee2", having the following data:

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

SELECT AVG(salary) AS "Average Salary"  
FROM Employee2;  

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:

SELECT columns  
FROM table1   
INNER JOIN table2  
ON table1.column = table2.column;  

Example

We have two tables "Students" and "Employee2".

Student table

Employee2 Table

Execute the following commands:

SELECT Students.student_id, Students.student_name, Employee2.salary  
FROM Students   
INNER JOIN Employee2  
ON Students.student_id = Employee2.emp_id; 

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:

SELECT columns  
FROM table1  
LEFT [OUTER] JOIN table2  
ON table1.column = table2.column;   

Example

SELECT Students.student_id, Students.student_name,   
Students.student_address,   
Employee2.salary, Employee2.emp_address  
FROM Students   
LEFT JOIN Employee2  
ON Students.student_id = Employee2.emp_id;  

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:

SELECT columns  
FROM table1  
RIGHT [OUTER] JOIN table2  
ON table1.column = table2.column;   

Example

SELECT Students.student_id, Students.student_name,   
Students.student_address, Employee2.salary,   
Employee2.emp_address  
FROM Students   
RIGHT JOIN Employee2  
ON Students.student_id = Employee2.emp_id;  

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:

SELECT columns    
FROM table1     
INNER JOIN table2    
ON table1.column = table2.column;     

Example:

We have two tables' sites and pages:

Sites table:

site_id	site_name
100	javatpoint.com
200	Facebook.com
300	Yahoo.com
400	Google.com

Pages table:

page_id	site_id	page_title
1	100	MariaDB
2	100	MySQL
3	200	Java interview questions
4	300	Software testing
5	500	Flight booking

Now execute the following commands:

SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title FROM sites INNER JOIN pages ON sites.site_id= page.site_id

Output:

site_id	site_name	page_id	page_title
100	javatpoint	1	MariaDB
100	javatpoint	2	MySQL
200	Facebook.com	3	Java interview questions
300	Yahoo.com	4	Software testing

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:

SELECT columns    
FROM table1    
LEFT [OUTER] JOIN table2    
ON table1.column = table2.column;    

Example

We have two tables' sites and pages:

Sites table:

site_id	site_name
100	javatpoint.com
200	Facebook.com
300	Yahoo.com
400	Google.com

Pages table:

page_id	site_id	page_title
1	100	MariaDB
2	100	MySQL
3	200	Java interview questions
4	300	Software testing
5	500	Flight booking

Now execute the following commands:

SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title FROM sites LEFT JOIN pages ON sites.site_id= pages.site_id

Output:

site_id	site_name	page_id	page_title
100	javatpoint	1	MariaDB
100	javatpoint	2	MySQL
200	Facebook.com	3	Java interview questions
300	Yahoo.com	4	Software testing
400	Google.com	null	null

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 also called RIGHT JOIN.

Syntax:

SELECT columns    
FROM table1    
RIGHT [OUTER] JOIN table2    
ON table1.column = table2.column; 

Example

We have two tables' sites and pages:

Sites table:

site_id	site_name
100	javatpoint.com
200	Facebook.com
300	Yahoo.com
400	Google.com

Pages table:

page_id	site_id	page_title
1	100	MariaDB
2	100	MySQL
3	200	Java interview questions
4	300	Software testing
5	500	Flight booking

Now execute the following commands:

SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title FROM sites RIGHT JOIN pages ON sites.site_id= pages.site_id

Output:

site_id	site_name	page_id	page_title
100	javatpoint	1	MariaDB
100	javatpoint	2	MySQL
200	Facebook.com	3	Java interview questions
300	Yahoo.com	4	Software testing
null	null	5	Flight booking

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:

CREATE   
[ DEFINER = { CURRENT_USER | user_name } ]   
FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]  
RETURNS return_datatype [ LANGUAGE SQL  
| DETERMINISTIC  
| NOT DETERMINISTIC  
| { CONTAINS SQL   
| NO SQL  
| READS SQL DATA  
| MODIFIES SQL DATA }  
| SQL SECURITY { DEFINER | INVOKER }  
| COMMENT 'comment_value'  
BEGIN  
   declaration_section  
   executable_section  
END;   

Example

Create a function CalcValue in MariaDB database.

DELIMITER //  
CREATE FUNCTION CalcValue ( starting_value INT )  
RETURNS INT DETERMINISTIC  
BEGIN  
DECLARE total_value INT;  
   SET total_value = 0;  
   label1: WHILE total_value <= 3000 DO  
     SET total_value = total_value + starting_value;  
   END WHILE label1;  
   RETURN total_value;  
END; //  
DELIMITER ;   

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:

CREATE   
[ DEFINER = { CURRENT_USER | user_name } ]   
PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]  
[ LANGUAGE SQL  
| DETERMINISTIC  
| NOT DETERMINISTIC  
| { CONTAINS SQL   
  | NO SQL  
  | READS SQL DATA  
  | MODIFIES SQL DATA }  
| SQL SECURITY { DEFINER | INVOKER }  
| COMMENT 'comment_value'  
BEGIN  
   declaration_section  
   executable_section  
END;   

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.

DELIMITER //  
CREATE procedure CalcValue ( OUT ending_value INT )  
DETERMINISTIC  
BEGIN  
   DECLARE total_value INT;  
   SET total_value = 50;  
   label1: WHILE total_value <= 3000 DO  
     SET total_value = total_value * 2;  
   END WHILE label1;  
   SET ending_value = total_value;  
END; //  
DELIMITER ;   

Procedure is created successfully: