A student has used the following query to retrieve data from the Employee table (it has 8 records) and the Department table (it has 4 records). The result of his query is 32 records in the output table. What is wrong with his query?

SELECT Employee.First_Name, Employee.Last_Name, Department.Depart_Name
FROM Employee, Department

Question

A student has used the following query to retrieve data from the Employee table (it has 8 records) and the Department table (it has 4 records). The result of his query is 32 records in the output table. What is wrong with his query?

SELECT Employee.First_Name, Employee.Last_Name, Department.Depart_Name
FROM Employee, Department

BlackTom AI · Accepted Answer

The scenario describes a student querying two tables, Employee (8 records) and Department (4 records), and getting 32 rows in the result. This multiplication of 8 by 4 indicates a Cartesian product, meaning every row from Employee is paired with every row from Department.

Option 1: The student has to use WHERE to connect PK and FK in the relationship.
This is the correct diagnostic. Without a join condition linking Employee and Department on their related keys (for example, a foreign key in Employee referencing Department), SQL performs a cross join, producing 8 × 4 = 32 rows. Adding a proper join condition in the WHERE clause (or using an explicit JOIN ... ON ... syntax) would pair each employee with only their corresponding department, reducing the result to the expected number of related records.

Option 2: The student has to use HAVING to connect PK and FK in the relationship.
HAVING is used to filter results of an aggregate query after GROUP BY, not to establish join relationships between tables. This option misuses HAVING; it would not fix the root cause of the Cartesian product.

Option 3: The student has to query from one table only.
The task involves data from both Employee and Department, so querying a single table would miss needed information. The issue is not about needing fewer tables, but about linking them correctly.

Option 4: The student has to ORDER BY the results in ascending order.
ORDER BY controls the order of the output but does not affect the number of rows returned or how tables are joined. It would not resolve the 32-row Cartesian result.

In summary, the excessive row count arises because there is no join condition between Employee and Department, causing a Cartesian product. Adding a proper join predicate connects related records and yields the correct, typically fewer, result set.

类似问题

What was the world's first widely adopted biodiversity policy?

The aim of the Paris Agreement is to ensure that the earth’s preindustrial temperature is not exceeded by more than º Celsius.

The IPCC's mission is to Blank ______.

Why have most international agreements for environmental protection been based on the honor system?

As was exhibited with the Montreal Protocol and the encouragement of reduced CFC production, trade can be an effective tool to compel compliance with international treaties.

The Stockholm Declaration and UNEP were the result of the Blank ______ first conference on the Human Environment in 1972.

In addition to the direct mission of the Convention on International Trade in Endangered Species (CITES), why was CITES so important?

President Trump hiring a corporate leader from the oil and gas industry to head the Department of Energy is an example of regulatory ; one intention behind this was to change environmental policy.

更多留学生实用工具

智能学习助手

风格化写作助手

论文查重助手

文献引用助手

课堂转译助手

课堂笔记助手

Quiz搜索助手

学校历年真题

智能刷题助手

智能匹配练习

希望你的学习变得更简单