Protect against orphan data during bulk load

Protect against orphan data during bulk load

Hard SQL Constraints 27 views
Explanation Complexity

Problem Statement

You are loading Orders first, then Customers later. How will you prevent orphan customer_id in Orders? Write a staging-table approach.

Input Format

SQL DDL + DML

Output Format

DDL/DML statements

Example

OrdersStaging, Customers, Orders
Clean final Orders

Constraints

Use staging and validate before final insert

Input / Output Format

Input Format
SQL DDL + DML
Output Format
DDL/DML statements
Constraints
Use staging and validate before final insert

Examples

Input:
OrdersStaging, Customers, Orders
Output:
Clean final Orders

Example Solution (Public)

SQL 
CREATE TABLE OrdersStaging (order_id INT, customer_id INT, order_date DATE, total_amount DECIMAL(10,2)); INSERT INTO Orders(order_id, customer_id, order_date, total_amount) SELECT s.order_id, s.customer_id, s.order_date, s.total_amount FROM OrdersStaging s JOIN Customers c ON c.customer_id = s.customer_id;

Official Solution Code

CREATE TABLE OrdersStaging (order_id INT, customer_id INT, order_date DATE, total_amount DECIMAL(10,2)); INSERT INTO Orders(order_id, customer_id, order_date, total_amount) SELECT s.order_id, s.customer_id, s.order_date, s.total_amount FROM OrdersStaging s JOIN Customers c ON c.customer_id = s.customer_id;
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.