DS0-001 Database Structures And SQL Practice Test 2 — 15 Questions

Question 1

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 2

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 3

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 4

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 5

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

Question 6

Which scenario best justifies the use of a relational database over a NoSQL database?

Accepted Answer

Financial transactions. Relational databases provide strong ACID compliance, which is essential for financial transactions where data integrity is paramount.

Answer

Big data analytics

Answer

Rapid prototyping

Answer

Social media feeds

Question 7

A developer needs to calculate the running total of sales. Which SQL feature is most appropriate?

Accepted Answer

Window Function. Window functions, specifically SUM() OVER(), allow calculations across a set of rows related to the current row without collapsing the result into a single group.

Answer

GROUP BY

Answer

Trigger

Answer

Recursive CTE

Question 8

What is the primary purpose of a 'Normalization' process in database design?

Accepted Answer

Reduce redundancy. Normalization minimizes redundancy and dependency by organizing fields and table relationships, which reduces data anomalies.

Answer

Convert SQL to NoSQL

Answer

Increase query speed

Answer

Enable horizontal scaling

Question 9

A query is slow due to excessive scanning of a large table. The execution plan shows a 'Table Scan'. What is the most likely missing element?

Accepted Answer

An index. A Table Scan occurs when the database must read every row; an index would allow it to find data more efficiently.

Answer

A view

Answer

A stored procedure

Answer

A trigger

Question 10

Which SQL statement is used to remove a table entirely from the database?

Accepted Answer

DROP TABLE. The DROP TABLE statement removes the definition and all data within the table permanently.

Answer

DELETE TABLE

Answer

REMOVE TABLE

Answer

TRUNCATE TABLE

Question 11

Which SQL clause is used to sort the result of a query by one or more columns?

Accepted Answer

ORDER BY. The ORDER BY clause is used to sort the result set in ascending or descending order.

Answer

SORT BY

Answer

ARRANGE BY

Answer

GROUP BY

Question 12

Which THREE of the following are considered ACID properties in database transactions?

Accepted Answer

Atomicity. ACID stands for Atomicity, Consistency, Isolation, and Durability.

Answer

Scalability

Answer

Availability

Question 13

Which TWO of the following are common types of NoSQL databases?

Accepted Answer

Key-Value. Document and Key-Value stores are two primary categories of NoSQL databases.

Answer

Relational

Answer

Hierarchical

Answer

Network

Question 14

A database administrator wants to check the current connection count. Which system catalog or dynamic management view would typically provide this information?

Accepted Answer

Dynamic Management Views. Most RDBMS provide dynamic management views (like sys.dm_exec_sessions in SQL Server) to monitor server health and connections.

Answer

Information Schema

Answer

System Tables

Answer

Audit Logs

Question 15

To implement a secure 'Multi-Tenant' architecture, which design approach is most effective for data separation?

Accepted Answer

Separate schemas. Using a separate schema or database per tenant provides a hard boundary that prevents cross-tenant data access.

Answer

Shared columns

Answer

Indexing

Answer

Stored procedures