Test Your MySQL RDBMS Knowledge with Our SQL Quiz

Progress

True / False

MySQL is an example of a Relational Database Management System (RDBMS).

True False

MySQL is a popular open-source RDBMS used for managing relational databases.

True / False

In MySQL, data is stored in tables that can relate to each other using foreign keys.

True False

An RDBMS like MySQL uses tables and supports relationships through primary keys and foreign keys.

True / False

MySQL does not support the SQL language for database management.

True False

MySQL fully supports SQL (Structured Query Language) for database management and operations.

True / False

In MySQL, normalization is a process used to reduce data redundancy

True False

Normalization in MySQL (and other RDBMS) is used to organize data to reduce redundancy and improve data integrity.

True / False

MySQL does not provide ACID (Atomicity, Consistency, Isolation, Durability) properties.

True False

MySQL supports ACID properties, particularly when using storage engines like InnoDB, to ensure reliable transactions.

True / False

In MySQL, a schema is a collection of database objects, including tables, views, and procedures.

True False

A schema in MySQL (and other RDBMS) is indeed a logical collection of database objects.

True / False

In MySQL, indexing a column improves the speed of data retrieval operations

True False

Indexes in MySQL enhance the performance of data retrieval operations by allowing faster access to rows.

True / False

MySQL uses the concept of foreign key constraints to enforce referential integrity.

True False

Foreign keys in MySQL ensure that the relationships between tables remain consistent and enforce referential integrity.

True / False

MySQL can handle only a limited number of transactions at a time.

True False

MySQL, especially with the InnoDB storage engine, is designed to handle a large number of concurrent transactions efficiently.

Q10

True / False

MySQL supports the use of triggers to automatically perform actions in response to certain events in the database.

True False

Triggers in MySQL are a powerful feature that allows automatic execution of SQL code in response to specified events, such as INSERT, UPDATE, or DELETE.

Q11

Single Choice

What does RDBMS stand for?

Relational Database Management System Rational Database Management System Remote Database Management SystemRedundant Database Management System

RDBMS stands for Relational Database Management System, which organizes data into tables related by common fields.

Q12

Single Choice

Which of the following is a key feature of an RDBMS like MySQL?

Hierarchical data storage Object-oriented data storage Table-based data storageGraph-based data storage

RDBMS stores data in tables, allowing for relational queries and operations.

Q13

Single Choice

In MySQL, which SQL keyword is used to create a new table in an RDBMS?

NEW TABLE ADD TABLE CREATE TABLEMAKE TABLE

The CREATE TABLE statement is used to create a new table in MySQL.

Q14

Single Choice

Which SQL command is used to retrieve data from a MySQL RDBMS?

FETCH GET SELECTRETRIEVE

The SELECT statement is used to query and retrieve data from tables in MySQL.

Q15

Single Choice

In MySQL RDBMS, which constraint ensures that a column must contain a unique value?

NOT NULL UNIQUE CHECKFOREIGN KEY

The UNIQUE constraint ensures that all values in a column are different from each other.

Q16

Single Choice

What is the purpose of a primary key in a MySQL RDBMS?

To allow null values in a column To ensure each row has a unique identifier To define a relationship between tablesTo allow duplicate values in a column

A primary key uniquely identifies each record in a table and does not allow duplicate values.

Q17

Single Choice

In a MySQL RDBMS, what is a foreign key used for?

To store large amounts of text To enforce referential integrity between tables To speed up queriesTo encrypt data

A foreign key is a column that creates a relationship between two tables, enforcing referential integrity.

Q18

Single Choice

Which SQL statement is used to modify existing data in a MySQL RDBMS table?

ALTER UPDATE MODIFYCHANGE

The UPDATE statement is used to modify existing records in a table.

Q19

Single Choice

What is a transaction in the context of a MySQL RDBMS?

A single operation that changes data A sequence of operations performed as a single logical unit of work A process to back up the databaseA method to compress data

A transaction is a series of SQL operations that are executed as a single unit, ensuring data integrity.

Q20

Single Choice

In a MySQL RDBMS, which property of a transaction ensures that once a transaction is committed, it will remain so, even in the event of a system failure?

Atomicity Consistency IsolationDurability

Durability ensures that once a transaction has been committed, it remains permanent, even in the event of a system crash.

Q21

Multiple Choice

Which of the following SQL statements correctly creates a table named 'students' with 'id' as the primary key in an RDBMS?

SQL Code

CREATE TABLE students (
 id INT AUTO_INCREMENT,
 name VARCHAR(100),
 age INT,
 PRIMARY KEY (id)
);

CREATE TABLE students ( id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE students ( id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY ); CREATE TABLE students ( id INT, name VARCHAR(100), PRIMARY KEY (id) );CREATE TABLE students ( id INT AUTO_INCREMENT, name VARCHAR(100), age INT, PRIMARY KEY (id) );

In an RDBMS like MySQL, the primary key uniquely identifies each record in the table. The correct SQL statement should include 'PRIMARY KEY (id)' after defining the column.

Q22

Multiple Choice

Identify the correct SQL code that inserts a record into a table named 'employees' ensuring referential integrity.

SQL Code

INSERT INTO employees (id, name, department_id)
VALUES (1, 'John Doe', 3);
INSERT INTO departments (id, name)
VALUES (3, 'Sales');

INSERT INTO employees (id, name, department_id) VALUES (2, 'Jane Doe', 5); INSERT INTO employees (id, name, department_id) VALUES (3, 'John Smith', 1); INSERT INTO employees (id, name, department_id) VALUES (1, 'John Doe', 3); INSERT INTO departments (id, name) VALUES (3, 'Sales');INSERT INTO employees (id, name, department_id) VALUES (4, 'Alice Johnson', NULL);

In an RDBMS, referential integrity is maintained by ensuring that any foreign key value matches a primary key in the related table. The code that ensures this is correct.

Q23

Multiple Choice

Choose the SQL code snippet that correctly creates a foreign key constraint between two tables in an RDBMS.

SQL Code

CREATE TABLE orders (
 order_id INT AUTO_INCREMENT,
 customer_id INT,
 PRIMARY KEY (order_id),
 FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE orders ( order_id INT, customer_id INT, PRIMARY KEY (order_id) ); CREATE TABLE orders ( order_id INT AUTO_INCREMENT, customer_id INT, PRIMARY KEY (order_id) ); CREATE TABLE orders ( order_id INT AUTO_INCREMENT, customer_id INT, PRIMARY KEY (order_id), FOREIGN KEY (customer_id) REFERENCES customers(id) );CREATE TABLE orders ( order_id INT, customer_id INT, FOREIGN KEY (order_id) REFERENCES customers(id) );

In an RDBMS, a foreign key constraint links two tables by establishing a relationship between columns, usually the primary key of one table and a foreign key in another.

Q24

Multiple Choice

Which of the following SQL statements would correctly normalize data by separating it into related tables?

SQL Code

CREATE TABLE students (
 id INT AUTO_INCREMENT,
 name VARCHAR(100),
 address VARCHAR(255),
 PRIMARY KEY (id)
);
CREATE TABLE addresses (
 address_id INT AUTO_INCREMENT,
 address VARCHAR(255),
 PRIMARY KEY (address_id)
);

CREATE TABLE students ( id INT AUTO_INCREMENT, name VARCHAR(100), address_id INT, PRIMARY KEY (id) ); CREATE TABLE addresses ( address_id INT AUTO_INCREMENT, address VARCHAR(255), PRIMARY KEY (address_id) ); INSERT INTO students (id, name, address_id) VALUES (1, 'John Doe', 1);All of the above.

Normalization in RDBMS involves organizing data to reduce redundancy. Splitting the data into related tables is key, such as separating addresses into a distinct table.

Q25

Multiple Choice

Select the SQL code that effectively ensures data integrity using constraints in an RDBMS.

SQL Code

CREATE TABLE employees (
 id INT AUTO_INCREMENT,
 name VARCHAR(100),
 department_id INT,
 PRIMARY KEY (id),
 FOREIGN KEY (department_id) REFERENCES departments(id)
);
CREATE TABLE departments (
 id INT AUTO_INCREMENT,
 name VARCHAR(100),
 PRIMARY KEY (id)
);

CREATE TABLE employees ( id INT, name VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE departments ( id INT, name VARCHAR(100) ); CREATE TABLE employees ( id INT AUTO_INCREMENT, name VARCHAR(100), department_id INT, PRIMARY KEY (id), FOREIGN KEY (department_id) REFERENCES departments(id) );CREATE TABLE departments ( id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (id) );

Data integrity in RDBMS is maintained through constraints such as primary and foreign keys that enforce rules on the data.

Q26

Multiple Choice

Which SQL code ensures the atomicity of transactions in an RDBMS by rolling back on failure?

SQL Code

START TRANSACTION;
INSERT INTO employees (id, name, department_id)
VALUES (5, 'Tom Holland', 2);
INSERT INTO salaries (employee_id, salary)
VALUES (5, 70000);
COMMIT;

START TRANSACTION; INSERT INTO employees (id, name, department_id) VALUES (5, 'Tom Holland', 2); COMMIT; START TRANSACTION; INSERT INTO salaries (employee_id, salary) VALUES (5, 70000); COMMIT; START TRANSACTION; INSERT INTO employees (id, name, department_id) VALUES (5, 'Tom Holland', 2); INSERT INTO salaries (employee_id, salary) VALUES (5, 70000); ROLLBACK;START TRANSACTION; INSERT INTO employees (id, name, department_id) VALUES (5, 'Tom Holland', 2); INSERT INTO salaries (employee_id, salary) VALUES (5, 70000); COMMIT;

Atomicity in RDBMS ensures that all operations within a transaction are completed successfully, or none are, ensuring the database remains consistent.

Q27

Multiple Choice

Identify the SQL code that demonstrates the use of indexes to improve query performance in an RDBMS.

SQL Code

CREATE INDEX idx_employee_name
ON employees (name);
SELECT * FROM employees
WHERE name = 'John Doe';

CREATE INDEX idx_employee_name ON employees (name); SELECT * FROM employees WHERE name = 'John Doe'; SELECT * FROM employees WHERE name = 'John Doe'; CREATE INDEX idx_employee_id ON employees (id); SELECT * FROM employees WHERE id = 1;SELECT * FROM employees WHERE id = 1;

Indexes in RDBMS speed up data retrieval operations by creating an internal data structure that allows faster access to the data.

Q28

Multiple Choice

Choose the SQL code snippet that correctly implements data redundancy reduction using normalization.

SQL Code

CREATE TABLE students (
 id INT AUTO_INCREMENT,
 name VARCHAR(100),
 class_id INT,
 PRIMARY KEY (id)
);
CREATE TABLE classes (
 id INT AUTO_INCREMENT,
 class_name VARCHAR(100),
 PRIMARY KEY (id)
);

CREATE TABLE students ( id INT AUTO_INCREMENT, name VARCHAR(100), class_id INT, PRIMARY KEY (id) ); CREATE TABLE classes ( id INT AUTO_INCREMENT, class_name VARCHAR(100), PRIMARY KEY (id) ); INSERT INTO students (id, name, class_id) VALUES (1, 'John Doe', 1);All of the above.

Normalization in RDBMS reduces data redundancy by organizing the data into related tables with relationships defined by foreign keys.

Q29

Multiple Choice

What SQL code would you use to enforce unique constraints on a table to prevent duplicate data entries?

SQL Code

CREATE TABLE students (
 id INT AUTO_INCREMENT,
 name VARCHAR(100),
 email VARCHAR(100),
 UNIQUE (email),
 PRIMARY KEY (id)
);

CREATE TABLE students ( id INT AUTO_INCREMENT, name VARCHAR(100), email VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE students ( id INT AUTO_INCREMENT, name VARCHAR(100), email VARCHAR(100), UNIQUE (email), PRIMARY KEY (id) ); CREATE TABLE students ( id INT, name VARCHAR(100), email VARCHAR(100), PRIMARY KEY (id) );CREATE TABLE students ( id INT, name VARCHAR(100), email VARCHAR(100), UNIQUE (email) );

In RDBMS, a UNIQUE constraint ensures that all values in a column are distinct, preventing duplicate data entries.

Q30

Multiple Choice

Which SQL code correctly sets up a relational database structure for an e-commerce application?

SQL Code

CREATE TABLE products (
 id INT AUTO_INCREMENT,
 name VARCHAR(100),
 price DECIMAL(10, 2),
 PRIMARY KEY (id)
);
CREATE TABLE orders (
 id INT AUTO_INCREMENT,
 product_id INT,
 quantity INT,
 PRIMARY KEY (id),
 FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE products ( id INT, name VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE orders ( id INT, product_id INT, PRIMARY KEY (id) ); CREATE TABLE products ( id INT AUTO_INCREMENT, name VARCHAR(100), price DECIMAL(10, 2), PRIMARY KEY (id) );CREATE TABLE orders ( id INT AUTO_INCREMENT, product_id INT, quantity INT, PRIMARY KEY (id), FOREIGN KEY (product_id) REFERENCES products(id) );

A relational database in MySQL is organized into tables related by keys. The primary key identifies each record, while foreign keys establish relationships between tables.

MySQL Database Quiz Questions