DS0-001 Database Structures And SQL — 20 Questions

Question 1

A database administrator needs to store unstructured data that scales horizontally across multiple servers while maintaining high availability. Which database structure should be selected?

Accepted Answer

NoSQL (Document Store). NoSQL databases, such as document stores, are designed for horizontal scaling and handling unstructured data, unlike rigid relational schemas.

Answer

Relational (RDBMS)

Answer

Network

Answer

Hierarchical

Question 2

A user wants to add a new column to an existing table named 'Employees'. Which SQL statement should be executed?

Accepted Answer

ALTER TABLE. The ALTER TABLE command is used to modify the structure of an existing table, such as adding a new column.

Answer

MODIFY TABLE

Answer

UPDATE TABLE

Answer

CREATE COLUMN

Question 3

To improve read performance on a table frequently queried by a specific column, which database object should be created?

Accepted Answer

Index. An index allows the database engine to find specific rows without scanning the entire table, significantly improving read performance.

Answer

View

Answer

Stored Procedure

Answer

Trigger

Question 4

A database administrator is investigating performance degradation during heavy concurrent write operations. Which isolation level provides the highest level of data consistency but the lowest concurrency?

Accepted Answer

Serializable. Serializable isolation ensures that concurrent transactions result in a state that could have been achieved if transactions were executed serially, preventing all phenomena but reducing concurrency.

Answer

Read Committed

Answer

Repeatable Read

Answer

Read Uncommitted

Question 5

A developer needs to ensure that a 'Customer_ID' field in a 'Orders' table always references a valid ID in the 'Customers' table. Which constraint should be applied?

Accepted Answer

FOREIGN KEY. A foreign key constraint ensures referential integrity between two tables by requiring that the value in the child table exists in the parent table.

Answer

PRIMARY KEY

Answer

UNIQUE

Answer

CHECK

Question 6

Which SQL command is used to permanently remove all rows from a table while keeping the table definition intact?

Accepted Answer

TRUNCATE. TRUNCATE is a DDL operation that removes all records from a table and is generally faster than DELETE because it does not log individual row deletions.

Answer

DELETE

Answer

DROP

Answer

REMOVE

Question 7

Which type of join should be used to return all records from the 'Left' table and only matching records from the 'Right' table?

Accepted Answer

LEFT JOIN. A LEFT JOIN returns all rows from the left table, and matching rows from the right table; if no match exists, NULL values are returned for right-side columns.

Answer

INNER JOIN

Answer

RIGHT JOIN

Answer

FULL JOIN

Question 8

An analyst is writing an SQL query to retrieve total sales per region, but the result must only show regions with more than 100 transactions. Which clause is required?

Accepted Answer

HAVING. The HAVING clause is used to filter groups created by the GROUP BY clause, whereas WHERE filters individual rows.

Answer

ORDER BY

Answer

LIMIT

Answer

WHERE

Question 9

Which SQL keyword is used to eliminate duplicate values from a query result set?

Accepted Answer

DISTINCT. The DISTINCT keyword is used in a SELECT statement to return only unique values.

Answer

UNIQUE

Answer

GROUP BY

Answer

FILTER

Question 10

Which DML statement is used to update existing records in a database table?

Accepted Answer

UPDATE. The UPDATE statement modifies the data of existing rows in a table.

Answer

ALTER

Answer

MODIFY

Answer

INSERT

Question 11

A database developer needs to combine the results of two different SELECT queries into a single result set. Which operator should be used?

Accepted Answer

UNION. The UNION operator is specifically designed to combine the result sets of two or more SELECT statements into one.

Answer

JOIN

Answer

CONCAT

Answer

MERGE

Question 12

An application requires an automatic timestamp for every row inserted into an 'Audit' table. Which feature should the developer implement?

Accepted Answer

Trigger. A trigger is a database object that automatically executes code when a specific event (like INSERT) occurs on a table.

Answer

Index

Answer

Constraint

Answer

View

Question 13

When migrating data between two different relational databases, which DDL-related issue is most likely to cause failure if ignored?

Accepted Answer

Incompatible data types. Data types can vary significantly between RDBMS vendors, leading to truncation or conversion errors if not mapped correctly during migration.

Answer

Table naming conventions

Answer

Storage engine choice

Answer

Primary key naming

Question 14

To prevent unauthorized access to specific columns in a 'Salary' table, which object should be created for the reporting team?

Accepted Answer

View. A view can be used to expose only specific columns to a user or group, effectively hiding sensitive data like salaries.

Answer

Index

Answer

Sequence

Answer

Stored Procedure

Question 15

A business user needs to calculate the average age of customers, but some age fields are NULL. Which function should be used to treat NULL as 0?

Accepted Answer

COALESCE. The COALESCE function returns the first non-null expression in the list, allowing NULLs to be replaced with 0.

Answer

NULLIF

Answer

IFNULL

Answer

ISNULL

Question 16

A transaction involves multiple updates that must either all succeed or all fail. Which command ensures this behavior?

Accepted Answer

COMMIT. COMMIT finalizes the transaction, while ROLLBACK reverts it; together they ensure atomicity.

Answer

GRANT

Answer

SAVEPOINT

Answer

BEGIN

Question 17

Which command is used to restrict a user's access to a specific table?

Accepted Answer

REVOKE. REVOKE is the DCL command used to remove permissions previously granted to a user.

Answer

DENY

Answer

DROP

Answer

REMOVE

Question 18

Which NoSQL structure is best suited for scenarios where data relationships are complex and frequently traversed?

Accepted Answer

Graph. Graph databases are specifically designed for data that is highly interconnected, such as social networks.

Answer

Key-Value

Answer

Column-family

Answer

Document

Question 19

Which operator is used to search for a pattern in a string column?

Accepted Answer

LIKE. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Answer

IN

Answer

CONTAINS

Answer

MATCH

Question 20

An application is experiencing deadlocks. Which strategy would effectively reduce the frequency of deadlocks?

Accepted Answer

Uniform access order. Accessing tables in the same order across different transactions is a primary method to prevent circular wait conditions that cause deadlocks.

Answer

Enabling indexing

Answer

Using read-only databases

Answer

Increasing table size