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

Question 1

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

Accepted Answer

Financial transactions. Financial transactions require strict ACID guarantees — atomicity, consistency, isolation, and durability — which relational databases provide natively through transactions and constraints. NoSQL databases typically relax these guarantees in favor of scalability and availability, making them unsuitable for scenarios where data integrity is paramount.

Answer

Big data analytics

Answer

Rapid prototyping

Answer

Social media feeds

Question 2

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

Accepted Answer

Window Function. A window function computes an aggregate or ranking value across a set of rows related to the current row without collapsing them, which is exactly what a running total requires. Using SUM(sales) OVER (ORDER BY date) produces a cumulative sum that grows row by row while preserving every detail row. This is the canonical use case for window functions such as SUM, AVG, RANK, and ROW_NUMBER with an OVER clause.

Answer

GROUP BY

Answer

Trigger

Answer

Recursive CTE

Question 3

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

Accepted Answer

Reduce redundancy. Normalization is the process of organizing database tables to minimize redundancy and dependency by dividing large tables into smaller, related ones and defining relationships. Its primary purpose is to reduce data duplication and prevent update, insert, and delete anomalies.

Answer

Convert SQL to NoSQL

Answer

Increase query speed

Answer

Enable horizontal scaling

Question 4

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 in an execution plan means the database engine is reading every row of the table to satisfy the query, which is expensive on large tables. An index provides a sorted access path that lets the optimizer seek directly to matching rows instead of scanning the whole table, dramatically reducing I/O. Adding an appropriate index on the columns used in WHERE, JOIN, or ORDER BY clauses is the standard fix.

Answer

A view

Answer

A stored procedure

Answer

A trigger

Question 5

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

Accepted Answer

DROP TABLE. The SQL statement used to remove a table entirely from the database is DROP TABLE. This command deletes the table definition, all data, and associated indexes, triggers, and permissions. It is a Data Definition Language (DDL) operation and cannot be rolled back in most databases once committed.

Answer

DELETE TABLE

Answer

REMOVE TABLE

Answer

TRUNCATE TABLE

Question 6

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 in SQL is the standard, ANSI-defined mechanism for sorting the result set of a SELECT statement by one or more columns, in ascending (ASC, the default) or descending (DESC) order. It is applied after the result set is produced and can reference column names, aliases, or positional ordinals. No other clause in standard SQL performs result-set sorting.

Answer

SORT BY

Answer

ARRANGE BY

Answer

GROUP BY

Question 7

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 8

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. Dynamic Management Views (DMVs) in SQL Server, such as sys.dm_exec_connections and sys.dm_exec_sessions, expose real-time server state including current connection counts, session details, and resource usage. They are the standard mechanism for monitoring live connection activity because they query the running instance's internal state rather than static metadata. This makes DMVs the correct choice for checking current connection count.

Answer

Information Schema

Answer

System Tables

Answer

Audit Logs

Question 9

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

Accepted Answer

Separate schemas. Separate schemas provide strong logical isolation by giving each tenant its own namespace of objects, enabling independent permission management and reducing cross-tenant data exposure. This approach balances security and manageability better than sharing columns, which risks accidental data leakage between tenants.

Answer

Shared columns

Answer

Indexing

Answer

Stored procedures

Question 10

Which THREE of the following are standard SQL aggregate functions?

Accepted Answer

AVG. AVG (option A) is a standard SQL aggregate function that returns the average value of a numeric column, making it correct. SUM (option B) is also a standard aggregate function that totals the values in a numeric column, so it is correct. COUNT (option D) is a standard aggregate function that returns the number of rows or non-NULL values, so it is correct. SELECT (option C) is a data retrieval statement, not an aggregate function, and DELETE (option E) is a data manipulation statement used to remove rows, so neither belongs among aggregate functions.

Answer

SELECT

Answer

DELETE

Question 11

Which TWO of the following are DDL (Data Definition Language) commands?

Accepted Answer

DROP. DROP (A) is a DDL command because it removes entire database objects such as tables, views, or indexes from the schema, altering the database structure. CREATE (C) is also DDL since it defines new database objects like tables, indexes, or schemas, changing the structure of the database. INSERT (B) is a DML command used to add rows of data into existing tables, not to change schema. SELECT (D) is a DML/DQL command that retrieves data and does not modify database structure. UPDATE (E) is DML because it modifies existing row values within a table rather than defining or altering schema objects.

Answer

INSERT

Answer

SELECT

Answer

UPDATE

Question 12

Which THREE of the following are benefits of database indexing?

Accepted Answer

Enforcement of uniqueness. Option A (Enforcement of uniqueness) is correct because a UNIQUE index (or a PRIMARY KEY constraint, which is backed by a unique index) forces the database engine to reject any duplicate values in the indexed column(s), guaranteeing data integrity. Option B (Faster data retrieval) is correct because an index provides a sorted, balanced-tree (B-tree) or hash structure that lets the query optimizer locate rows with index seeks instead of scanning the entire table, dramatically reducing I/O for SELECT and WHERE predicates. Option C (Optimization of JOIN operations) is correct because indexes on join columns allow the optimizer to perform efficient nested-loop, hash, or merge joins by probing the indexed key rather than scanning full tables. Option D (Faster write operations) is not a benefit: every INSERT, UPDATE, or DELETE must also maintain the index entries, adding overhead and slowing writes. Option E (Reduced storage requirements) is not a benefit: indexes consume additional disk space and memory, increasing rather than reducing storage needs.

Answer

Faster write operations

Answer

Reduced storage requirements

Question 13

Which TWO of the following are potential issues when denormalizing a database?

Accepted Answer

Data anomalies. Denormalization intentionally duplicates data across tables to reduce joins, and this redundancy creates data anomalies (Option A): if a duplicated value is updated in one place but not another, the database can end up with inconsistent or conflicting records, violating update/insert/delete consistency. Denormalization also increases storage usage (Option E) because the same data is stored in multiple tables or columns, and additional indexes or summary columns may be added, consuming more disk space than a normalized design. The unmarked options do not belong because simplified data entry (B) and improved schema flexibility (C) are generally benefits rather than issues, and slower join performance (D) is incorrect since denormalization typically reduces the number of joins and can improve read performance.

Answer

Simplified data entry

Answer

Improved schema flexibility

Answer

Slower join performance

Question 14

Which THREE of the following are common SQL constraints?

Accepted Answer

PRIMARY KEY. PRIMARY KEY (C) is a common SQL constraint that uniquely identifies each row in a table and implicitly enforces NOT NULL and uniqueness. UNIQUE (D) is a common SQL constraint that ensures all values in a column or set of columns are distinct, though it typically permits NULL values. CHECK (E) is a common SQL constraint that restricts the values a column can accept based on a Boolean expression, such as CHECK (age >= 18). GROUP BY (A) is a SELECT clause used for aggregating rows into groups, not a constraint, and JOIN (B) is a query operation that combines rows from two or more tables, also not a constraint.

Answer

GROUP BY

Answer

JOIN

Question 15

Which TWO of the following scenarios are best suited for using a 'View'?

Accepted Answer

Simplifying complex joins. Views are best for simplifying complex queries and implementing row/column level security.

Answer

Storing large binary objects

Answer

Improving write speed

Answer

Generating unique sequences