Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Row Validation in Views

Explore how to build SQL views that validate rows by applying business rules with CASE expressions. Learn to classify data dynamically within a view, enabling consistent validation logic without rewriting queries. This lesson covers creating views, implementing conditional checks, and alternative methods for row validation in SQL.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
CREATE VIEW Product_Validation_View AS
SELECT ProductID, ProductName, Stock, MonthlySales,
       CASE
          WHEN Stock < 30 AND MonthlySales < 5 THEN 'Critical'
          WHEN Stock < 30 THEN 'Low Stock'
          WHEN MonthlySales < 5 THEN 'Low Sales'
          ELSE 'Normal'
       END AS ValidationStatus
FROM   Products;
SELECT * FROM Product_Validation_View ORDER BY ProductID;

Explanation

The explanation of the solution code is given below:

  • Line 1: The CREATE VIEW statement defines a new view named Product_Validation_View.

  • Line 2: The query selects the ProductID, ProductName, Stock, and MonthlySales columns from the Products table to make them visible in the view output.

  • Lines 3–7: A CASE expression evaluates each product according to the business rules.

    • Lines 3–4: These apply the Critical condition when both Stock and MonthlySales fall below the defined thresholds.

    • Line 5: This applies the Low Stock label.

    • Line 6: This applies the Low Sales label.

    • Line 7: This assigns Normal for all products that do not meet the above rules.

  • Line 8: This closes the  ...