WGU C170 Data Management –
Applications: The Complete -Question
Objective Assessment Study Guide
with Answers & Rationales
1. What is structured data?
Answer: Data stored in a traditional system such as a relational
database or spreadsheet, organized into tables with rows and
columns.
Rationale: This structure allows for efficient querying and
management using a DBMS, unlike unstructured data (e.g., text files,
images).
2. What is a transaction?
Answer: A group of queries that must either all be completed
successfully or are rejected as a whole.
Rationale: This embodies the "All or Nothing" principle (Atomicity),
ensuring data integrity even during system failures.
3. What is CRUD?
Answer: The four common database operations: Create, Read,
Update, Delete.
Rationale: These are the fundamental operations for persisting and
retrieving data in any database system.
,4. What is a unique value in a database context?
Answer: A value that occurs only once in a given column.
Rationale: This is crucial for primary keys, ensuring each row can be
uniquely identified.
5. What is a relationship in a database?
Answer: A connection between two pieces of data in different tables
in the same database.
Rationale: Relationships are the foundation of relational databases,
allowing data to be linked and combined through foreign keys.
6. What are four benefits of utilizing databases?
Answer: 1) Provide structure to the data; 2) Allow enforcement of
rules; 3) Protect data from unauthorized access; 4) Ensure changes
only get made if all associated changes are made successfully.
Rationale: These benefits ensure data is reliable, secure, and
consistent through ACID properties and constraints.
7. A salesperson is authorized to sell many products and a product can
be sold by many salespersons. Which kind of binary relationship does
this scenario describe?
Answer: Many-to-many.
Rationale: This requires a junction table (associative entity) to
implement because a single salesperson is linked to many products,
and vice versa.
8. What is the purpose of using a SELECT statement?
Answer: To retrieve data from the database.
, Rationale: It is the primary SQL command for querying and returning
results from one or more tables.
9. Which SQL statement retrieves all columns from the Owners table?
Answer: SELECT * FROM Owners;
Rationale: The asterisk (*) is a wildcard that represents all columns in
the table.
10. What happens in an UPDATE statement if the WHERE clause is
omitted?
Answer: Every row in the table is updated.
Rationale: This is a common and dangerous mistake that leads to
widespread, unintended data changes.
11. Which action deletes all rows from a table?
Answer: Omitting the WHERE clause from a DELETE statement.
Rationale: DELETE FROM table_name; removes all data from the
table but does not destroy the table's structure.
12. Which SQL statement tallies the number of different cities in which
record companies have been founded?
Answer: SELECT COUNT(DISTINCT city) FROM recordcompany;
Rationale: COUNT(DISTINCT column_name) provides the number of
unique, non-null values in that column.
13. Which task does ORDER BY perform by default?
Answer: Sorting rows in ascending order.
Rationale: To sort in descending order, you must explicitly use
the DESC keyword.
, 14. A database administrator needs to compile a list of movies
released each year. Which SQL command will accomplish this?
Answer: SELECT YEAR, TITLE FROM MOVIE GROUP BY YEAR;
Rationale: GROUP BY groups the movies by year, and you can list the
titles within each group.
15. Which two columns in a CREATE TABLE member statement are
created as something other than variable-length strings?
Answer: member_id (INT) and expiration (DATE).
Rationale: member_id is a numeric data type, and expiration is a
date data type, while columns like last_name are variable-length
strings (VARCHAR).
16. How does a row subquery differ from a table subquery?
Answer: A row subquery returns a single row of one or more values. A
table subquery returns a result set that resembles a table with multiple
rows and columns.
Rationale: Row subqueries are used in comparisons, while table
subqueries are used in the FROM clause.
17. What is the name of the special internal database where the query
optimizer finds information?
Answer: Relational Catalog (or System Catalog).
Rationale: It stores metadata about database objects, which the query
optimizer uses to generate efficient execution plans.
18. Two attributes in two related tables have the exact same domain of
values. The attribute is a primary key in one table. Which kind of key is
the attribute in the other table?
Applications: The Complete -Question
Objective Assessment Study Guide
with Answers & Rationales
1. What is structured data?
Answer: Data stored in a traditional system such as a relational
database or spreadsheet, organized into tables with rows and
columns.
Rationale: This structure allows for efficient querying and
management using a DBMS, unlike unstructured data (e.g., text files,
images).
2. What is a transaction?
Answer: A group of queries that must either all be completed
successfully or are rejected as a whole.
Rationale: This embodies the "All or Nothing" principle (Atomicity),
ensuring data integrity even during system failures.
3. What is CRUD?
Answer: The four common database operations: Create, Read,
Update, Delete.
Rationale: These are the fundamental operations for persisting and
retrieving data in any database system.
,4. What is a unique value in a database context?
Answer: A value that occurs only once in a given column.
Rationale: This is crucial for primary keys, ensuring each row can be
uniquely identified.
5. What is a relationship in a database?
Answer: A connection between two pieces of data in different tables
in the same database.
Rationale: Relationships are the foundation of relational databases,
allowing data to be linked and combined through foreign keys.
6. What are four benefits of utilizing databases?
Answer: 1) Provide structure to the data; 2) Allow enforcement of
rules; 3) Protect data from unauthorized access; 4) Ensure changes
only get made if all associated changes are made successfully.
Rationale: These benefits ensure data is reliable, secure, and
consistent through ACID properties and constraints.
7. A salesperson is authorized to sell many products and a product can
be sold by many salespersons. Which kind of binary relationship does
this scenario describe?
Answer: Many-to-many.
Rationale: This requires a junction table (associative entity) to
implement because a single salesperson is linked to many products,
and vice versa.
8. What is the purpose of using a SELECT statement?
Answer: To retrieve data from the database.
, Rationale: It is the primary SQL command for querying and returning
results from one or more tables.
9. Which SQL statement retrieves all columns from the Owners table?
Answer: SELECT * FROM Owners;
Rationale: The asterisk (*) is a wildcard that represents all columns in
the table.
10. What happens in an UPDATE statement if the WHERE clause is
omitted?
Answer: Every row in the table is updated.
Rationale: This is a common and dangerous mistake that leads to
widespread, unintended data changes.
11. Which action deletes all rows from a table?
Answer: Omitting the WHERE clause from a DELETE statement.
Rationale: DELETE FROM table_name; removes all data from the
table but does not destroy the table's structure.
12. Which SQL statement tallies the number of different cities in which
record companies have been founded?
Answer: SELECT COUNT(DISTINCT city) FROM recordcompany;
Rationale: COUNT(DISTINCT column_name) provides the number of
unique, non-null values in that column.
13. Which task does ORDER BY perform by default?
Answer: Sorting rows in ascending order.
Rationale: To sort in descending order, you must explicitly use
the DESC keyword.
, 14. A database administrator needs to compile a list of movies
released each year. Which SQL command will accomplish this?
Answer: SELECT YEAR, TITLE FROM MOVIE GROUP BY YEAR;
Rationale: GROUP BY groups the movies by year, and you can list the
titles within each group.
15. Which two columns in a CREATE TABLE member statement are
created as something other than variable-length strings?
Answer: member_id (INT) and expiration (DATE).
Rationale: member_id is a numeric data type, and expiration is a
date data type, while columns like last_name are variable-length
strings (VARCHAR).
16. How does a row subquery differ from a table subquery?
Answer: A row subquery returns a single row of one or more values. A
table subquery returns a result set that resembles a table with multiple
rows and columns.
Rationale: Row subqueries are used in comparisons, while table
subqueries are used in the FROM clause.
17. What is the name of the special internal database where the query
optimizer finds information?
Answer: Relational Catalog (or System Catalog).
Rationale: It stores metadata about database objects, which the query
optimizer uses to generate efficient execution plans.
18. Two attributes in two related tables have the exact same domain of
values. The attribute is a primary key in one table. Which kind of key is
the attribute in the other table?
