MariaDB Interview question and answers | MariaDB Quiz Challenge: SQL, Engines, and Administration

MariaDB Programming Challenge: SQL, Engines, and Administration

Test your knowledge of **MariaDB Fundamentals, Storage Engines, Optimization, and Administration**. **Pass with 80% to proceed to the next difficulty level.**

Level 1: Core Concepts & Basic SQL: 0/10 (0%)

Level 2: Engines, Transactions, & Joins: 0/10 (0%)

Level 3: Optimization & Advanced Features: 0/10 (0%)

Level 1: Core Concepts & Basic SQL

1.1 What category of SQL commands does `CREATE TABLE` belong to?

A. DML (Data Manipulation Language) B. DDL (Data Definition Language) C. DCL (Data Control Language)

**Correct Answer: B. DDL (Data Definition Language)**. DDL commands (`CREATE`, `ALTER`, `DROP`) define the database structure or schema.

1.2 Which data type should be used to store variable-length strings of text up to 65,535 characters?

A. CHAR B. VARCHAR C. TINYTEXT

**Correct Answer: B. VARCHAR**. `VARCHAR` stores variable-length strings efficiently. `CHAR` stores fixed-length strings, padding with spaces if necessary.

1.3 What is the core function of a Primary Key in a table?

A. To speed up all queries. B. To uniquely identify each row in the table and enforce NOT NULL. C. To link one table to another table.

**Correct Answer: B. To uniquely identify each row...**. A Primary Key ensures data integrity by guaranteeing uniqueness and non-nullability for the specified column(s).

1.4 Which SQL command is used to retrieve data from a MariaDB table?

A. GET B. FETCH C. SELECT

**Correct Answer: C. SELECT**. `SELECT` is the fundamental command in SQL for querying and retrieving data.

1.5 In MariaDB, what does the `NULL` value represent?

A. The integer value zero (0). B. Missing, unknown, or inapplicable data. C. An empty string ('').

**Correct Answer: B. Missing, unknown, or inapplicable data**. `NULL` is not equivalent to zero or an empty string; it is a marker for the absence of a value.

1.6 Which SQL clause is used to filter records based on a specified condition?

A. FILTER BY B. WHERE C. HAVING

**Correct Answer: B. WHERE**. The `WHERE` clause filters individual records before any aggregation takes place. `HAVING` filters aggregated groups.

1.7 Which command is used to add, delete, or modify columns in an existing table?

A. MODIFY TABLE B. UPDATE TABLE C. ALTER TABLE

**Correct Answer: C. ALTER TABLE**. This DDL command is used to change the structure of an existing table.

1.8 Which storage engine is the default for new MariaDB tables and supports transactions?

A. Aria B. InnoDB C. MyISAM

**Correct Answer: B. InnoDB**. InnoDB is the standard, reliable, transactional storage engine used by default in both MariaDB and MySQL.

1.9 Which command permanently removes an entire database from the server?

A. DELETE DATABASE B. REMOVE DATABASE C. DROP DATABASE

**Correct Answer: C. DROP DATABASE**. `DROP` is the DDL command used to permanently destroy database objects like tables, indexes, or databases.

1.10 What is the main difference between MariaDB and Oracle/PostgreSQL?

A. MariaDB supports NoSQL features. B. MariaDB is open-source and community-developed. C. MariaDB is the only one that supports the SQL language.

**Correct Answer: B. MariaDB is open-source and community-developed**. MariaDB was created as a fork of MySQL to ensure its continued open-source development, unlike Oracle's MySQL ownership.

**End of Core Concepts & Basic SQL. Click "Next Level" to continue.**

Level 2: Engines, Transactions, & Joins

2.1 What is the primary purpose of a Foreign Key constraint?

A. To speed up the join operation. B. To enforce referential integrity between two tables. C. To limit the number of rows a table can hold.

**Correct Answer: B. To enforce referential integrity...**. A Foreign Key links a column in one table to the Primary Key of another table, ensuring that relationships are valid.

2.2 In the context of database transactions, what does the D in ACID stand for?

A. Distribution B. Durability C. Dependency

**Correct Answer: B. Durability**. ACID stands for **A**tomicity, **C**onsistency, **I**solation, and **D**urability, which are key properties guaranteeing reliable transaction processing.

2.3 Which SQL Join returns all rows from the first table and the matching rows from the second table?

A. INNER JOIN B. LEFT JOIN (or LEFT OUTER JOIN) C. FULL JOIN (or FULL OUTER JOIN)

**Correct Answer: B. LEFT JOIN (or LEFT OUTER JOIN)**. It ensures all records from the left table are present, using NULLs for missing matches in the right table.

2.4 Which clause must be used in a `SELECT` statement to combine rows into groups?

A. ORDER BY B. GROUP BY C. AGGREGATE

**Correct Answer: B. GROUP BY**. The `GROUP BY` clause is used with aggregate functions (e.g., `COUNT`, `SUM`) to group rows that have the same values in specified columns.

2.5 How do you discard all uncommitted changes within an active database transaction?

A. ABORT B. ROLLBACK C. DISCARD

**Correct Answer: B. ROLLBACK**. `ROLLBACK` reverts the database to the state it was in before the current transaction began. `COMMIT` makes the changes permanent.

2.6 What is a View in MariaDB?

A. A temporary table that is physically stored. B. A virtual table based on the result-set of an SQL query. C. A cached version of a query result.

**Correct Answer: B. A virtual table...**. Views are used for security (restricting column access) and simplifying complex queries. They do not store data themselves (unless they are Materialized Views).

2.7 Which clause is used to filter the results of an aggregate function (i.e., filter grouped data)?

A. WHERE B. HAVING C. ORDER BY

**Correct Answer: B. HAVING**. `WHERE` filters rows before grouping; `HAVING` filters groups created by the `GROUP BY` clause.

2.8 The MariaDB storage engine Aria is known for being an enhanced version of which older engine?

A. InnoDB B. MyISAM C. TokuDB

**Correct Answer: B. MyISAM**. Aria is MariaDB's default internal temporary table engine, designed to be crash-safe, overcoming a major limitation of MyISAM.

2.9 When joining two tables, if the join condition is not met, which join returns NULL values for the columns of the right table?

A. INNER JOIN B. LEFT JOIN C. RIGHT JOIN

**Correct Answer: B. LEFT JOIN**. A LEFT JOIN includes all rows from the left table, even if there are no matches in the right table. The right table columns will be NULL in that case.

2.10 What is a Prepared Statement used for?

A. Executing complex DDL commands. B. Executing the same SQL statement repeatedly and preventing SQL injection. C. Defining database security permissions.

**Correct Answer: B. Executing the same SQL statement...**. Prepared statements separate the SQL structure from the data, which increases performance and is the primary defense against SQL injection attacks.

**End of Engines, Transactions, & Joins. Click "Next Level" to continue.**

Level 3: Optimization & Advanced Features

3.1 What is the primary method MariaDB uses to speed up data retrieval operations?

A. Table Normalization B. Denormalization C. Indexing

**Correct Answer: C. Indexing**. Indexes allow the database to locate rows without scanning the entire table, drastically improving query performance, especially for `WHERE` and `JOIN` clauses.

3.2 Which MariaDB feature is designed to offer high-availability and multi-master, synchronous replication?

A. Master-Slave Replication B. Spider Engine C. Galera Cluster

**Correct Answer: C. Galera Cluster**. Galera provides synchronous replication, meaning a transaction is committed on all nodes simultaneously, guaranteeing zero data loss on node failure.

3.3 What is a Clustered Index?

A. An index that uses multiple columns. B. An index that dictates the physical, on-disk order of the data rows. C. An index that is duplicated across all cluster nodes.

**Correct Answer: B. An index that dictates the physical...**. In InnoDB, the Primary Key is the Clustered Index. A table can only have one Clustered Index because the data can only be sorted physically in one way.

3.4 What is the purpose of the SQL command `EXPLAIN` when executed before a `SELECT` statement?

A. To display the query execution plan and optimize query performance. B. To execute the query and return the result set in a verbose format. C. To summarize the aggregated results without fetching the actual data.

**Correct Answer: A. To display the query execution plan...**. `EXPLAIN` is an essential tool for understanding how MariaDB's optimizer plans to execute a query (e.g., which indexes it will use, if it performs a full table scan).

3.5 What is a Stored Procedure?

A. A piece of middleware code run on the client side. B. A precompiled collection of SQL statements stored and executed on the database server. C. A complex view with built-in security features.

**Correct Answer: B. A precompiled collection of SQL statements...**. Stored procedures improve performance, reduce network traffic, and increase security.

3.6 What is the key difference in operation between `TRUNCATE TABLE` and `DELETE FROM`?

A. `DELETE` is faster; `TRUNCATE` is slower but logs individual rows. B. `TRUNCATE` is DDL, resets `AUTO_INCREMENT`, and cannot be rolled back (usually); `DELETE` is DML, slow, and transactional. C. `DELETE` removes the table structure; `TRUNCATE` removes only the rows.

**Correct Answer: B. `TRUNCATE` is DDL, resets `AUTO_INCREMENT`...**. `TRUNCATE` is much faster as it deallocates the data pages rather than deleting row by row.

3.7 What is the main advantage of using a Materialized View over a standard View?

A. It physically stores the query result, speeding up complex reads at the cost of refresh lag. B. It allows the underlying base tables to be modified. C. It automatically updates whenever the underlying data changes.

**Correct Answer: A. It physically stores the query result...**. Unlike a regular View, a Materialized View pre-calculates and stores the data, making query time much faster for complex joins and aggregations.

3.8 What concept allows for partitioning table data based on column values for better management and query optimization?

A. Sharding B. Clustering C. Partitioning

**Correct Answer: C. Partitioning**. Partitioning divides a single logical table into smaller physical pieces (partitions), which can significantly speed up queries that only target specific data ranges.

3.9 In MariaDB, what does a Window Function allow you to do?

A. Perform calculations across a set of table rows that are related to the current row without grouping them. B. Run functions on data retrieved from external API endpoints. C. Restrict the view of data to a specific time window.

**Correct Answer: A. Perform calculations across a set of table rows...**. Window functions (e.g., `ROW_NUMBER()`, `LAG()`, `LEAD()`) provide analytical capabilities over a "window" of rows, often used for ranking or calculating running totals.

3.10 Which variable is used to set the default transaction isolation level for MariaDB sessions?

A. `sql_mode` B. `tx_isolation` (or `transaction_isolation`) C. `wait_timeout`

**Correct Answer: B. `tx_isolation` (or `transaction_isolation`)**. This variable controls how transactions interact with each other. Common levels are `READ COMMITTED` and `REPEATABLE READ`.

**MariaDB Mastered! Click "Finish Quiz" to see your final summary.**

Final Quiz Summary 📋

Related Topics & Tags

SQL DDL DML InnoDB Galera Indexing Optimization ACID Views Interview

Awk command with simple examples

Now, we are going to learn very interesting and Most important in Linux Why late lets know about Awk command,one of the mostly used command in scripting. awk command used to manipulate data | using this AWK command we can generate specified reports as per our requirement. Syntax : awk ‘BEGIN {start_action} {action} END {stop_action}’ filename let me explain about awk command with simple examples Example 1: How to print entire data of the file cat filename | awk '{print}' cat filename | awk '{print$0}' both " awk '{print}' and awk '{print$0}' " used to print entire date Example 2: How to print very first column cat filename| awk '{print$1}' awk '{print$1}' used to print very first column awk '{print$1}' used to print second column Example 3: How to print p attern Matching Technique in Awk. free -m | awk '{/Mem/print}' here, we are matching Mem using awk '{...

Learn Linux in Telugu | Linux complete Free Course in Telugu by 7Hills

Hello, Here we are going to learn complete Linux course freely & Simply way. Why Linux? Linux is an heart the operating system, Based on the Linux Kernel their are different types of Linux flavors available i.e.. RedHat, Centos, Fedora, Mint Debian, Kali, OpenSUSE...etc Features of Linux Open source. Multi-user. Multitasking. Hierarchical file system. Portable. Wide hardware support. No need to worry, we explained each and every topic clearly just click on the below links for watch on Youtube. 1. Linux outline in Telugu 2. Linux introduction In Telugu 3. Linux architecture In Telugu 4. Linux Directory Structure In Telugu 5. Linux Basic Commands In Telugu 6. ...

Hey! Well come back! Here, we are going to learn very useful command rsync. basically rsync is used to sync file system with same structure. It also used to data backup/copy. rsync is free command line utility & In most of the system it available defiantly. We can install rsync utility in debian based system using below command apt-get install rsync We can install rsync utility in redhat based system using below command yum install rsync Below command used to copy files without using option rsync /root/Desktop/original/* /root/Desktop/backup/ Below command used for recursive copy, it means with sub folders. rsync -avhr /root/Desktop/original/* /root/Desktop/backup/ Here -a is used for archive data. -v is used for show output. -h is used for show copied data in MB's,it means human readable format. Below command used for include or exclude rsync -avz --in...

Now we are going to learn very useful & interesting topic "How to reduce/extend LVM size" with real time example.hope this article very helpful for you. For example scenario, I have added some storage space to linux machine,we need to scan hosts to display in linux machine, we can scan all hots/LUN's by using below commands at the time. ------------------------------------------------------ Use below command to scan hosts/LUN's. ls /sys/class/scsi_host/ | while read host ; do echo "- - -" > /sys/class/scsi_host/$host/scan ; done Use below command to verify newly added device/stroage #lsblk Use below command to format /dev/sdb #fdisk /dev/sdb Below commands helpful for complete format. #n #p enter enter #t #8e p #wq Below command used to update partition info to kernal. partprobe /dev/sdb1 try below command to create pv(Physical volume) #pvcreate /dec/sdb1 Try below command to create volume group #vgcreate VG0...