FC0-U61 Practice Test 24 — 15 Questions

Question 1

A database administrator needs to remove all rows from the 'Log' table but keep the table structure for future use. The administrator also wants the operation to be irreversible (cannot be rolled back). Which SQL statement should be used?

Accepted Answer

TRUNCATE TABLE Log;. TRUNCATE TABLE Log; is correct because it removes all rows from the table while preserving the table structure (columns, constraints, indexes) for future use. Unlike DELETE, TRUNCATE is a DDL operation that does not generate individual row deletion logs, making it irreversible and cannot be rolled back in most database systems.

Answer

DELETE FROM Log;

Answer

ALTER TABLE Log REMOVE ROWS;

Answer

DROP TABLE Log;

Question 2

Which TWO of the following are examples of database management systems (DBMS)?

Accepted Answer

Microsoft SQL Server. Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It uses Structured Query Language (SQL) to store, retrieve, and manage data in a structured format, making it a core example of a DBMS.

Answer

Python

Answer

HTML

Answer

Linux

Question 3

Which TWO of the following are valid SQL functions used to aggregate data?

Accepted Answer

COUNT. COUNT and SUM are both aggregate functions in SQL. COUNT returns the number of rows in a result set or the number of non-NULL values in a column, while SUM calculates the total sum of a numeric column. These functions operate on a set of rows and return a single summary value, which is the defining characteristic of aggregate functions in SQL.

Answer

SORT

Answer

LENGTH

Answer

APPEND

Question 4

A table has columns: EmployeeID (PK), DepartmentID, DepartmentName, Salary. Which normal form violation exists?

Accepted Answer

Third Normal Form. The table violates Third Normal Form (3NF) because DepartmentName depends on DepartmentID, which is not a candidate key. In 3NF, every non-key column must depend solely on the primary key (EmployeeID), not on another non-key column. This transitive dependency (EmployeeID → DepartmentID → DepartmentName) requires splitting the table into Employees and Departments to achieve 3NF.

Answer

No violation

Answer

Second Normal Form

Answer

First Normal Form

Question 5

Refer to the exhibit. The database administrator sees this error in the log. Which action should the administrator take to resolve the deadlock?

Accepted Answer

The database will automatically roll back one of the transactions; no immediate action is needed.. When a deadlock occurs in a database, the database management system (DBMS) automatically detects the circular dependency and selects one of the transactions as a victim, rolling it back to break the deadlock. This is a built-in mechanism in most relational databases, such as SQL Server, Oracle, or MySQL, and does not require manual intervention. Therefore, no immediate action is needed from the administrator, as the database will resolve the deadlock itself.

Answer

Increase the transaction isolation level to Serializable to prevent deadlocks.

Answer

Remove all indexes from the involved tables to reduce lock contention.

Answer

Manually kill one of the processes to break the deadlock.

Question 6

A company needs to store an organizational chart showing reporting relationships. Which database model is most appropriate?

Accepted Answer

Hierarchical. A hierarchical database model is most appropriate for storing an organizational chart because it naturally represents parent-child relationships, such as manager-subordinate reporting lines, in a tree-like structure. This model allows each record to have a single parent and multiple children, directly mirroring the one-to-many relationships in a reporting hierarchy.

Answer

Relational

Answer

Document

Answer

Graph

Question 7

A web application constructs SQL queries by concatenating user input directly. What is the primary security risk?

Accepted Answer

SQL injection. When a web application constructs SQL queries by concatenating user input directly, an attacker can inject malicious SQL code into the query. This allows the attacker to manipulate the database, such as by bypassing authentication, retrieving unauthorized data, or executing arbitrary commands. This is the classic definition of SQL injection, which exploits the lack of input sanitization or parameterized queries.

Answer

Denial of service

Answer

Cross-site scripting

Answer

Buffer overflow

Question 8

A database designer wants to enforce that a 'CustomerID' value in an 'Orders' table must exist in the 'Customers' table. Which TWO methods can achieve this?

Accepted Answer

Use a trigger to check existence. Option C is correct because a trigger can be programmed to check, before an INSERT or UPDATE on the Orders table, whether the new CustomerID exists in the Customers table. If the value is not found, the trigger can roll back the transaction, enforcing referential integrity without using a formal foreign key constraint. This is a valid method in database systems that support procedural logic within triggers.

Answer

Create a unique constraint on Orders.CustomerID

Answer

Create an index on Orders.CustomerID

Answer

Create a primary key on Customers.CustomerID

Question 9

A query uses a LEFT JOIN between Customers and Orders. The result set includes customers with no orders. What does the Orders columns show for those customers?

Accepted Answer

NULL. In a LEFT JOIN, all rows from the left table (Customers) are retained, and for rows with no matching rows in the right table (Orders), the columns from the right table are filled with NULL. This is the standard SQL behavior defined by the SQL standard (ISO/IEC 9075). Therefore, for customers with no orders, the Orders columns show NULL, not zero, an empty string, or any other default value.

Answer

The same as the customer ID

Answer

Zero

Answer

Empty string

Question 10

A database must be restored to the exact state at 11:00 AM. The last full backup was at 10:00 PM previous day, and transaction log backups were taken every hour. What is the minimum to restore?

Accepted Answer

Full backup plus all transaction log backups from after full backup. To restore a database to a specific point in time (11:00 AM) when only full and transaction log backups exist, you must restore the most recent full backup (from 10:00 PM previous day) and then apply all subsequent transaction log backups in sequence. Transaction log backups contain all committed transactions up to the time of the backup, so applying them in order after the full backup brings the database to the exact state at the time of the last log backup (which would be 11:00 AM if the hourly log backup was taken at that time). Option A is correct because it includes the full backup and all transaction log backups taken after it, which is the minimum required for point-in-time recovery.

Answer

Transaction log backup only

Answer

Full backup only

Answer

Full backup plus differential backup

Question 11

A user wants to retrieve all records from a table named 'Products' where the price is greater than 100. Which TWO SQL statements will work?

Accepted Answer

SELECT * FROM Products WHERE Price > 100;. Option B is correct because it uses the standard SQL SELECT statement with a WHERE clause to filter records where the Price column is greater than 100, retrieving all columns with the asterisk (*). This is the fundamental and correct syntax for filtering rows in SQL.

Answer

SELECT * FROM Products WHERE Price > 100 GROUP BY Category;

Answer

SELECT ALL FROM Products WHERE Price > 100;

Answer

SELECT * FROM Products HAVING Price > 100;

Question 12

A banking transaction transfers money from savings to checking. The system updates the savings balance, then the power fails before the checking balance is updated. Which ACID property is violated?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In this scenario, the transfer operation consists of two steps: deducting from savings and crediting to checking. Because the power failure occurs after the savings balance is updated but before the checking balance is updated, the transaction is left partially completed, violating atomicity. The system must either complete both steps or roll back the first step to maintain atomicity.

Answer

Isolation

Answer

Durability

Answer

Consistency

Question 13

A database administrator adds an index on the 'Email' column of a 'Users' table. What is a likely consequence?

Accepted Answer

Slower update operations on the table. Adding an index on the 'Email' column improves the speed of SELECT queries that filter or sort by that column, but it introduces overhead on write operations. Every time a row is inserted, updated, or deleted, the database must also update the index structure (e.g., a B-tree). This additional work makes UPDATE operations slower, which is why option C is correct.

Answer

Reduced storage space

Answer

Improved query performance for all queries

Answer

Faster inserts on the table

Question 14

A company is deciding between using open-source software and proprietary software for a new project. Which of the following is a primary advantage of open-source software?

Accepted Answer

The ability to modify the source code to fit specific needs.. Open-source software provides access to its source code under a license (e.g., GPL, Apache 2.0) that permits users to study, modify, and redistribute it. This ability to customize the code to meet specific business or technical requirements is a primary advantage, as proprietary software typically restricts such modifications through end-user license agreements (EULAs).

Answer

No cost for any type of use or distribution.

Answer

Guaranteed professional technical support.

Answer

Vendor lock-in with regular updates.

Question 15

Refer to the exhibit. A user attempts to insert a row into Orders with a CustomerID that does not exist in the Customers table. What is the expected outcome?

Accepted Answer

The INSERT statement fails with a constraint violation. The INSERT statement fails with a constraint violation because the CustomerID column in the Orders table is defined as a foreign key referencing the CustomerID primary key in the Customers table. When a user attempts to insert a row with a CustomerID that does not exist in the Customers table, the relational database management system (RDBMS) enforces referential integrity and rejects the operation, raising a foreign key constraint violation error.

Answer

The row is inserted but with a warning

Answer

The row is inserted successfully

Answer

The Customers table automatically creates a new customer