MariaDB Interview Questions and Answers

This section covers frequently asked MariaDB interview questions, focusing on database administration, SQL queries, and data manipulation.

What is MariaDB?

MariaDB is a popular, open-source relational database management system (RDBMS). It's a community-developed fork of MySQL, offering similar functionality with additional features and performance enhancements. MariaDB is known for its speed, scalability, and robust ecosystem of tools and plugins.

More information on MariaDB

Key Features of MariaDB

• Cross-platform compatibility.
• Open-source licensing (GPL, LGPL, BSD).
• Standard SQL support.
• Galera cluster technology.
• Support for various programming languages (including PHP).
• A wide range of storage engines.
• Performance enhancements over MySQL.
• Support for GIS and JSON data types (in later versions).

More information on MariaDB features

Creating a Database in MariaDB

Use the CREATE DATABASE (or CREATE SCHEMA) statement:

CREATE DATABASE database_name;

Optional clauses include OR REPLACE (drops the database if it exists) and IF NOT EXISTS (returns a warning if the database already exists).

More information on creating databases

Using a Database

Select the database using the USE statement:

USE database_name;

More information on using databases

Deleting a Database

Use the DROP DATABASE (or DROP SCHEMA) statement to permanently delete a database. Use caution! The IF EXISTS clause prevents errors if the database doesn't exist.

DROP DATABASE database_name;

More information on deleting databases

Creating a Table

After creating a database, select it using USE, then create a table using the CREATE TABLE statement.

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

More information on creating tables

Deleting a Table

The DROP TABLE statement permanently deletes a table. Use caution!

DROP TABLE table_name;

More information on deleting tables

Inserting Records

Use the INSERT INTO statement to add new records. You can specify column names or use the default order.

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

More information on inserting records

Retrieving Records

The SELECT statement retrieves data from a table. You can select specific columns or all columns (using *).

SELECT column1, column2, ... FROM table_name WHERE condition;

More information on retrieving records

Retrieving a Limited Number of Records

Use the LIMIT clause to restrict the number of rows returned by a SELECT statement.

SELECT ... FROM ... LIMIT number_of_rows;

More information on limiting records

Updating Records

The UPDATE statement modifies existing records in a table. A WHERE clause is essential to specify which records to update.

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

More information on updating records

Deleting Records

The DELETE statement removes records from a table. A WHERE clause specifies which records to delete; omitting it deletes all records.

DELETE FROM table_name WHERE condition;

More information on deleting records

TRUNCATE Statement

TRUNCATE TABLE removes all rows from a table. Unlike DELETE, it cannot be rolled back and is generally faster.

TRUNCATE TABLE table_name;

DELETE vs. TRUNCATE

The `TRUNCATE TABLE` Statement

TRUNCATE TABLE removes all rows from a table. Unlike DELETE, it's faster because it deallocates the data pages and doesn't log each row deletion individually. It's a permanent operation; you cannot roll it back.

TRUNCATE TABLE table_name;

Aggregate Functions in MariaDB

Aggregate functions perform calculations on multiple rows and return a single value. MariaDB supports several aggregate functions:

• COUNT(): Counts rows.
• SUM(): Calculates the sum.
• MIN(): Finds the minimum value.
• MAX(): Finds the maximum value.
• AVG(): Calculates the average.
• BIT_AND(), BIT_OR(), BIT_XOR(): Bitwise operations.

COUNT() Function

The COUNT() function returns the number of rows that match the specified criteria. COUNT(*) counts all rows; COUNT(column) counts non-NULL values in the specified column.

SELECT COUNT(*) FROM my_table;

SUM() Function

The SUM() function calculates the sum of values in a numeric column. It returns NULL if there are no rows.

SELECT SUM(column_name) FROM my_table;

MIN() Function

The MIN() function returns the smallest value in a column.

SELECT MIN(column_name) FROM my_table;

MAX() Function

The MAX() function returns the largest value in a column.

SELECT MAX(column_name) FROM my_table;

AVG() Function

The AVG() function computes the average of values in a numeric column.

SELECT AVG(column_name) FROM my_table;

Bitwise Aggregate Functions

BIT_AND(), BIT_OR(), and BIT_XOR() perform bitwise AND, OR, and XOR operations on all values in a column.

Clauses in MariaDB

MariaDB supports standard SQL clauses:

• WHERE: Filters rows based on a condition.
• LIKE: Performs pattern matching using wildcards (%, _).
• ORDER BY: Sorts results in ascending or descending order.
• DISTINCT: Removes duplicate rows from results.
• FROM: Specifies the table(s) to retrieve data from.
• GROUP BY: Groups rows that have the same values in specified columns.
• HAVING: Filters groups (used with GROUP BY).
• LIMIT: Restricts the number of rows returned.

WHERE Clause

The WHERE clause filters rows based on a condition. You can use comparison operators (=, <, >, <=, >=, !=), logical operators (AND, OR, NOT), and the LIKE operator.

SELECT * FROM employees WHERE department = 'Sales';

More information on the WHERE clause

LIKE Clause

The LIKE clause performs pattern matching. The % wildcard matches zero or more characters; the _ wildcard matches a single character.

SELECT * FROM customers WHERE name LIKE 'A%'; -- Names starting with 'A'

More information on the LIKE clause

ORDER BY Clause

The ORDER BY clause sorts the results. Use ASC (ascending) or DESC (descending); ascending is default.

SELECT * FROM products ORDER BY price DESC;

More information on the ORDER BY clause

DISTINCT Clause

The DISTINCT keyword removes duplicate rows from the result set of a SELECT statement.

SELECT DISTINCT city FROM customers;

More information on the DISTINCT clause

FROM Clause

The FROM clause specifies the table(s) from which to retrieve data. It's used with SELECT, INSERT ... SELECT, UPDATE, and DELETE statements.

SELECT * FROM employees;

More information on the FROM clause

COUNT() Aggregate Function

The COUNT() function counts the number of rows (or non-NULL values in a column) that meet the specified criteria.

SUM() Aggregate Function

The SUM() function calculates the sum of values in a numeric column.

MIN() Aggregate Function

The MIN() function returns the minimum value in a column.

MAX() Aggregate Function

The MAX() function returns the maximum value in a column.

AVG() Aggregate Function

The AVG() function calculates the average of values in a numeric column.

MAX() Function

The MAX() function finds the maximum value in a column. It works with numeric and string data types.

SELECT MAX(column_name) FROM table_name;

AVG() Function

The AVG() function calculates the average of the values in a numeric column.

SELECT AVG(column_name) FROM table_name;

SQL Clauses in MariaDB

MariaDB supports various SQL clauses for querying and manipulating data:

• WHERE (filtering)
• LIKE (pattern matching)
• ORDER BY (sorting)
• DISTINCT (removing duplicates)
• FROM (specifying tables)
• GROUP BY (grouping rows)
• HAVING (filtering groups)
• LIMIT (limiting results)
• JOIN (combining data from multiple tables)

WHERE Clause

The WHERE clause filters rows based on a condition.

LIKE Clause

The LIKE clause is used for pattern matching with wildcards (% for any sequence of characters, _ for a single character).

ORDER BY Clause

The ORDER BY clause sorts the result set in ascending (ASC) or descending (DESC) order.

DISTINCT Clause

The DISTINCT clause removes duplicate rows from the result set.

FROM Clause

The FROM clause specifies the table(s) from which to retrieve data.

JOIN Clause

The JOIN clause combines rows from two or more tables based on a related column.

• INNER JOIN: Returns rows only when there is a match in both tables.
• LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.
• RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.

INNER JOIN

An INNER JOIN returns rows only where the join condition is met in both tables.

LEFT JOIN

A LEFT JOIN returns all rows from the left table, even if there's no match in the right table (NULL values for unmatched columns in the right table).

RIGHT JOIN

A RIGHT JOIN returns all rows from the right table, even if there's no match in the left table (NULL values for unmatched columns in the left table).

Functions in MariaDB

MariaDB supports stored functions, which are pre-compiled SQL code that accepts input parameters and returns a value.

CREATE FUNCTION function_name (parameters)
RETURNS datatype
BEGIN
  -- function code
  RETURN value;
END;

Dropping a Function

DROP FUNCTION function_name;

Stored Procedures in MariaDB

Stored procedures are similar to functions but don't necessarily return a value. They are often used to encapsulate complex database operations.

CREATE PROCEDURE procedure_name (parameters)
BEGIN
  -- procedure code
END;

Types of `EVALUATE` Statements in COBOL

The COBOL EVALUATE statement offers flexibility in conditional logic. Several forms exist, allowing for various conditional scenarios within your COBOL programs.

• Simple EVALUATE (evaluates a single expression).
• EVALUATE TRUE (evaluates multiple conditions).
• EVALUATE with THRU (specifies a range of values).
• EVALUATE with multiple WHEN clauses.

JCL Statements

JCL (Job Control Language) uses statements to define and manage batch jobs on IBM mainframes. The main types of statements are:

1. The JOB statement, identifying the job itself.
2. EXEC statements specifying the programs to be executed (one per step).
3. DD (Data Definition) statements describing the input and output datasets.

Creating Stored Procedures in MariaDB

Stored procedures are pre-compiled SQL code blocks that can accept parameters and perform various database operations. They improve performance and code organization.

CREATE PROCEDURE procedure_name (
    IN parameter1 datatype,
    OUT parameter2 datatype,
    INOUT parameter3 datatype
)
BEGIN
    -- Procedure code here
END;

Parameter Types:

• IN: Parameter value is passed to the procedure but cannot be modified by the procedure.
• OUT: Parameter value is set by the procedure and returned to the caller.
• INOUT: Parameter value is passed to the procedure and can be modified by the procedure.

Other Clauses:

• DEFINER: Specifies the user who owns the procedure.
• LANGUAGE SQL: Indicates the procedure uses SQL (mostly used for portability).
• DETERMINISTIC: The procedure always returns the same result for the same inputs.
• NOT DETERMINISTIC: The procedure's output might vary even with identical inputs (due to factors like timestamps or random numbers).
• CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL DATA: Informative clauses describing the procedure's interaction with the database.
• SQL SECURITY: Defines access control (DEFINER or INVOKER).
• COMMENT: Adds comments to describe the procedure.

Dropping Stored Procedures

To remove a stored procedure, use the DROP PROCEDURE statement.

DROP PROCEDURE procedure_name;

The IF EXISTS clause prevents an error if the procedure doesn't exist.