As the DBA, your manager called a meeting and asked why there are so many orders

Question

As the DBA, your manager called a meeting and asked why there are so many orders for customers that don't exist in the customer table. Upon inspection, you observe that the customer table is joined in a one-to-many relationship with orders.

Write a query that would show which customers were missing for existing orders. Use a join or a subquery.

Propose a solution to your manager for how to correct the problem. There are several alternatives. Present at least one per post, and explain in detail how this data anomaly could happen. Describe your short- and long-term proposal to correct it.

Explanation / Answer

Query to find the Missing Customers:

SELECT DISTINCT O.CUSTOMER_ID FROM ORDERS O WHERE O.CUSTOMER_ID NOT IN

(SELECT C.CUSTOMER_ID FROM CUSTOMER C)

This issue happend because there are no FOREIGN KEYconstraint on ORDERS Table's CUSTOMER_ID. ALTER the ORDERS Table, CUSTOMER_ID column by Adding a Foreignkey constraint with CUSTOMER Table's CUSTOMER_ID. So whenever a new entry is added to the ORDERS table, CUSTOMER_ID should be present in CUSTOMER Table

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.