Test Your MySQL Structured vs Unstructured Data Knowledge

Progress

True / False

MySQL is typically used to store structured data.

True False

MySQL is a relational database management system (RDBMS) that is designed to manage structured data, which is organized into tables with rows and columns.

True / False

Unstructured data cannot be stored in a MySQL database.

True False

While MySQL is optimized for structured data, unstructured data can still be stored using data types like BLOB (Binary Large Object) or TEXT, though it is less efficient for this purpose.

True / False

JSON data, which is semi-structured, can be stored and queried within MySQL.

True False

MySQL has native support for JSON data types, allowing for efficient storage and querying of JSON documents.

True / False

MySQL's performance is the same for structured and unstructured data.

True False

MySQL is optimized for structured data, and its performance can degrade when handling large amounts of unstructured data, especially if not indexed properly.

True / False

MySQL does not support full-text search capabilities for unstructured data.

True False

MySQL supports full-text search, which allows for efficient searching within unstructured text data stored in TEXT or VARCHAR columns.

True / False

Relational databases like MySQL are generally better for OLTP (Online Transaction Processing) systems which typically involve structured data.

True False

MySQL and other relational databases are optimized for OLTP systems, which handle structured data transactions efficiently.

True / False

MySQL can natively handle both structured and unstructured data equally well without any performance tuning.

True False

Handling unstructured data in MySQL may require performance tuning and optimization techniques, such as proper indexing and partitioning, to maintain efficiency.

True / False

In MySQL, indexing is equally effective for both structured and unstructured data.

True False

Indexing is generally more effective for structured data, where columns and data types are well-defined. Indexing unstructured data can be more complex and less efficient.

True / False

MySQL's InnoDB storage engine supports storing large binary objects (BLOBs) which can be used for unstructured data.

True False

The InnoDB storage engine in MySQL supports BLOB data types, allowing for the storage of large binary objects, which are often unstructured data.

Q10

True / False

In MySQL 8.0, the JSON data type provides a more efficient way to handle unstructured data compared to using BLOB or TEXT data types.

True False

MySQL 8.0 introduced enhancements to the JSON data type, including better performance and indexing capabilities, making it more efficient for handling semi-structured and unstructured data compared to traditional BLOB or TEXT types.

Q11

Single Choice

What does an ERD (Entity-Relationship Diagram) primarily represent in a MySQL database?

The structure of tables and relationships The user interface design The database server settingsThe network architecture

ERD is used to visualize the structure of a database including tables (entities) and relationships.

Q12

Single Choice

In MySQL, which of the following best describes structured data?

Data stored in a table with rows and columns Data stored in a JSON format Data stored in plain text filesData stored in image files

Structured data in MySQL is organized in tables with predefined schema, which includes rows and columns.

Q13

Single Choice

Which tool is commonly used to create an ERD for a MySQL database?

MySQL Workbench Photoshop MS WordBlender

MySQL Workbench is a visual database design tool that includes functionalities for creating ERDs.

Q14

Single Choice

What type of relationship in an ERD is used to denote that one entity can be related to many instances of another entity in MySQL?

One-to-One One-to-Many Many-to-ManySelf-referential

One-to-Many relationship means a single entity instance in one table is related to multiple instances in another table.

Q15

Single Choice

In MySQL, what is an example of unstructured data?

JSON documents stored in a MySQL column Text and binary data in BLOB fields Log files imported into MySQLAll of the above

Unstructured data includes any data that doesn't fit into a structured relational schema, like JSON documents, BLOBs, and log files.

Q16

Single Choice

Which MySQL storage engine is best suited for handling unstructured data?

InnoDB MyISAM MEMORYNDB

InnoDB supports BLOB and TEXT data types, which are often used to store unstructured data.

Q17

Single Choice

How can MySQL handle full-text searches in unstructured data?

Using the FULLTEXT index Using the PRIMARY KEY Using the UNIQUE indexUsing the FOREIGN KEY

MySQL's FULLTEXT index is used for full-text searches in TEXT and VARCHAR columns, useful for searching unstructured text data.

Q18

Single Choice

When modeling an ERD for a MySQL database, how can we represent unstructured data within an entity?

Use BLOB or TEXT data types for attributes Use INTEGER data types for attributes Use DATE data types for attributesUse BOOLEAN data types for attributes

BLOB and TEXT data types are designed to store large amounts of unstructured data.

Q19

Single Choice

Which MySQL function is specifically useful for extracting information from JSON documents stored in a table?

JSON_EXTRACT() DATE_FORMAT() LENGTH()CHAR()

JSON_EXTRACT() is used to extract data from JSON documents stored in MySQL.

Q20

Single Choice

In the context of MySQL, what is a significant challenge when dealing with unstructured data compared to structured data?

Indexing and searching efficiency Data integrity enforcement Table normalizationEstablishing foreign keys

Unstructured data often lacks a predefined schema, making indexing and searching more complex and less efficient than structured data.

Q21

Multiple Choice

Identify the correct SQL statement to store structured data in a relational table.

SQL Code

CREATE TABLE employees (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 department VARCHAR(50),
 salary DECIMAL(10,2)
);

CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, department VARCHAR(50), salary DECIMAL(10,2) ); CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2) ); CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, department TEXT, salary DECIMAL(10,2) );CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name TEXT, department VARCHAR(50), salary FLOAT );

This SQL statement creates a table for storing structured data about employees, including fields like name, department, and salary.

Q22

Multiple Choice

Choose the correct SQL statement to store unstructured data like JSON in a relational table.

SQL Code

CREATE TABLE documents (
 id INT AUTO_INCREMENT PRIMARY KEY,
 document_name VARCHAR(100) NOT NULL,
 content JSON
);

CREATE TABLE documents ( id INT AUTO_INCREMENT PRIMARY KEY, document_name VARCHAR(100) NOT NULL, content JSON ); CREATE TABLE documents ( id INT PRIMARY KEY, document_name VARCHAR(100), content BLOB ); CREATE TABLE documents ( id INT AUTO_INCREMENT PRIMARY KEY, document_name VARCHAR(100) NOT NULL, content TEXT );CREATE TABLE documents ( id INT AUTO_INCREMENT PRIMARY KEY, document_name VARCHAR(100) NOT NULL, content LONGTEXT );

This SQL statement creates a table designed to store unstructured data using the JSON data type, suitable for storing flexible data structures.

Q23

Multiple Choice

Determine the correct SQL statement to query structured data from a relational table.

SQL Code

SELECT name, department, salary
FROM employees
WHERE department = 'Sales';

SELECT name, department, salary FROM employees WHERE department = 'Sales'; SELECT name, department, salary FROM employees WHERE department LIKE 'Sales%'; SELECT * FROM employees WHERE department = 'Sales';SELECT department, name, salary FROM employees WHERE department = 'Sales';

This SQL statement retrieves structured data from the employees table, selecting specific columns and filtering by the department.

Q24

Multiple Choice

Which SQL statement correctly queries unstructured JSON data stored in a relational table?

SQL Code

SELECT document_name, JSON_UNQUOTE(JSON_EXTRACT(content, '$.author')) AS author
FROM documents
WHERE JSON_CONTAINS(content, '"MySQL"', '$.tags');

SELECT document_name, JSON_UNQUOTE(JSON_EXTRACT(content, '$.author')) AS author FROM documents WHERE JSON_CONTAINS(content, '"MySQL"', '$.tags'); SELECT document_name, JSON_UNQUOTE(JSON_EXTRACT(content, '$.author')) AS author FROM documents WHERE JSON_CONTAINS_PATH(content, 'one', '$.tags[MySQL]'); SELECT document_name, JSON_UNQUOTE(JSON_EXTRACT(content, '$.author')) AS author FROM documents WHERE JSON_SEARCH(content, 'one', 'MySQL') IS NOT NULL;SELECT document_name, JSON_UNQUOTE(JSON_EXTRACT(content, '$.author')) AS author FROM documents WHERE content->>'$.tags' = 'MySQL';

This SQL statement uses JSON functions to extract and filter data from JSON content stored in the documents table.

Q25

Multiple Choice

Select the correct SQL statement to modify a structured column to store unstructured data using JSON.

SQL Code

ALTER TABLE customers
MODIFY COLUMN preferences JSON;

ALTER TABLE customers MODIFY COLUMN preferences JSON; ALTER TABLE customers ALTER COLUMN preferences JSON; ALTER TABLE customers CHANGE preferences JSON;ALTER TABLE customers MODIFY preferences JSON;

This SQL statement modifies an existing column in the customers table to use the JSON data type, allowing it to store unstructured data.

Q26

Multiple Choice

Determine the correct SQL statement to combine structured and unstructured data in a single query.

SQL Code

SELECT e.name, e.department, JSON_UNQUOTE(JSON_EXTRACT(d.content, '$.document_type')) AS document_type
FROM employees e
JOIN documents d ON e.id = d.employee_id
WHERE e.department = 'HR';

SELECT e.name, e.department, JSON_UNQUOTE(JSON_EXTRACT(d.content, '$.document_type')) AS document_type FROM employees e JOIN documents d ON e.id = d.employee_id WHERE e.department = 'HR'; SELECT e.name, e.department, d.content->>'$.document_type' AS document_type FROM employees e JOIN documents d ON e.id = d.employee_id WHERE e.department = 'HR'; SELECT e.name, e.department, d.content->>'document_type' AS document_type FROM employees e JOIN documents d ON e.id = d.employee_id WHERE e.department = 'HR';SELECT e.name, e.department, JSON_EXTRACT(d.content, '$.document_type') AS document_type FROM employees e JOIN documents d ON e.id = d.employee_id WHERE e.department = 'HR';

This SQL statement combines structured data from the employees table with unstructured JSON data from the documents table using a JOIN operation.

Q27

Multiple Choice

Which SQL statement correctly creates a table designed to store both structured and unstructured data?

SQL Code

CREATE TABLE mixed_data (
 id INT AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(100) NOT NULL,
 metadata JSON,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE mixed_data ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100) NOT NULL, metadata JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE mixed_data ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100) NOT NULL, metadata TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE mixed_data ( id INT AUTO_INCREMENT PRIMARY KEY, title TEXT NOT NULL, metadata JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );CREATE TABLE mixed_data ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100) NOT NULL, metadata BLOB, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

This SQL statement creates a table that includes both structured data (title, created_at) and unstructured data (metadata stored as JSON).

Q28

Multiple Choice

Identify the correct SQL statement to extract a value from a JSON field and cast it to a specific data type.

SQL Code

SELECT JSON_EXTRACT(content, '$.price') + 0 AS price
FROM products;

SELECT JSON_EXTRACT(content, '$.price') + 0 AS price FROM products; SELECT CAST(JSON_EXTRACT(content, '$.price') AS DECIMAL(10,2)) AS price FROM products; SELECT CONVERT(JSON_EXTRACT(content, '$.price'), DECIMAL(10,2)) AS price FROM products;SELECT JSON_UNQUOTE(JSON_EXTRACT(content, '$.price')) AS price FROM products;

This SQL statement extracts the price value from a JSON field and casts it to a numeric type by adding 0.

Q29

Multiple Choice

Select the correct SQL statement to update a structured field based on the value of an unstructured JSON field.

SQL Code

UPDATE employees e
JOIN documents d ON e.id = d.employee_id
SET e.department = JSON_UNQUOTE(JSON_EXTRACT(d.content, '$.department'))
WHERE e.id = 1;

UPDATE employees e JOIN documents d ON e.id = d.employee_id SET e.department = JSON_UNQUOTE(JSON_EXTRACT(d.content, '$.department')) WHERE e.id = 1; UPDATE employees e JOIN documents d ON e.id = d.employee_id SET e.department = d.content->>'$.department' WHERE e.id = 1; UPDATE employees e JOIN documents d ON e.id = d.employee_id SET e.department = JSON_UNQUOTE(d.content->>'$.department') WHERE e.id = 1;UPDATE employees e JOIN documents d ON e.id = d.employee_id SET e.department = d.content->>'$.department' WHERE e.id = 1;

This SQL statement updates the department field in the employees table based on the value extracted from the unstructured JSON field in the documents table.

Q30

Multiple Choice

Determine the correct SQL statement to insert structured data with an embedded unstructured JSON field.

SQL Code

INSERT INTO user_data (user_id, name, preferences)
VALUES (1, 'John Doe', '{"theme": "dark", "notifications": true}');

INSERT INTO user_data (user_id, name, preferences) VALUES (1, 'John Doe', '{"theme": "dark", "notifications": true}'); INSERT INTO user_data (user_id, name, preferences) VALUES (1, 'John Doe', JSON_OBJECT('theme', 'dark', 'notifications', true)); INSERT INTO user_data (user_id, name, preferences) VALUES (1, 'John Doe', JSON_ARRAY('theme', 'dark', 'notifications', true));INSERT INTO user_data (user_id, name, preferences) VALUES (1, 'John Doe', JSON_QUOTE('{"theme": "dark", "notifications": true}'));

This SQL statement inserts structured data into the user_data table, with the preferences column containing embedded unstructured JSON data.

MySQL Database Quiz Questions