FC0-U71 Data and Database Fundamentals Practice Questions

Explanation: INNER JOIN returns only rows where the join condition (Students.StudentID = Enrollments.StudentID) is satisfied, which lists each student's name only if they have at least one enrollment. Option A (RIGHT JOIN) would include all enrollments, even if a StudentID does not match a student, resulting in NULL names for orphaned enrollments. Option C uses an implicit cross join (comma) with a WHERE clause; though it produces the same result set, it is not considered a proper JOIN statement and is less clear, so it does not meet the requirement of 'joining tables'. Option D (LEFT JOIN) would include students with no enrollments, showing NULL for CourseID, which does not match the requirement of listing only enrolled courses.

Explanation: DELETE FROM table_name removes all rows from a table while preserving the table structure, including its columns, constraints, indexes, and relationships. It is a DML (Data Manipulation Language) operation that can be filtered with a WHERE clause and can be rolled back within a transaction. This is exactly what the question asks for.

Explanation: Normalization is the process of organizing data in a relational database to minimize redundancy and dependency by dividing large tables into smaller, related tables and defining relationships between them. Option B, 'Removing duplicate data from a table,' captures the essence of normalization: it eliminates duplicate data by ensuring each piece of information is stored in only one place. While normalization often involves creating additional tables, the primary goal is to reduce redundancy and improve data integrity, not to improve performance (which can sometimes degrade due to joins).

Explanation: Option A (DESCRIBE Customers) is correct because DESCRIBE (or its synonym EXPLAIN) is the standard SQL command that returns the column definitions of a table—column names, data types, nullability, keys, and defaults—which is exactly metadata about the database structure. Option D (SHOW TABLES) is correct because it is the MySQL/SQL-standard administrative statement that lists the tables present in the current database, revealing the schema's structure at the table level. Option E (SELECT * FROM information_schema.tables WHERE table_schema = 'store') is correct because information_schema is the ANSI SQL standard metadata catalog, and querying its tables view filtered by table_schema returns structural information (table names, types, engines) about the store schema. Option B (SELECT * FROM Customers) is not a structural query—it retrieves the actual row data stored in the Customers table, i.e., data manipulation rather than metadata inspection. Option C (INSERT INTO Customers VALUES (...)) is a data manipulation statement that adds rows to the table and does not query or reveal the database structure at all.

Explanation: Normalization is a formal design process whose primary goals include eliminating update anomalies (C): by decomposing repeating groups into separate relations, insert, update, and delete anomalies caused by partial and transitive dependencies are removed. It also aims to reduce data redundancy (D), since storing the same fact in multiple rows or columns wastes space and creates inconsistency risks; each fact should be stored in exactly one place. Additionally, normalization enforces data integrity (E) by ensuring that dependencies are represented as keys and constraints, so the database structure itself prevents invalid or inconsistent states. Option A is not a goal because normalization typically increases the number of tables and complexity rather than simplifying the schema, and option B is not a goal because normalization often requires more joins, which can reduce rather than improve query performance.