AdventureWorks Database: Evaluate whether the values in two columns are the same
129. From the following table write a query in SQL to evaluate whether the values in the MakeFlag and FinishedGoodsFlag columns are the same for products with ProductID less than 10. If the values in MakeFlag and FinishedGoodsFlag are the same, return NULL; otherwise, return the value of MakeFlag. Return the columns ProductID, MakeFlag, FinishedGoodsFlag, and the evaluated result.
Sample table: production.Product
productid|name |productnumber|makeflag|finishedgoodsflag|color |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate |sellenddate |discontinueddate|rowguid |modifieddate |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
1|Adjustable Race |AR-5381 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
2|Bearing Ball |BA-8327 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
3|BB Ball Bearing |BE-2349 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
4|Headset Ball Bearings |BE-2908 |false |false | | 800| 600| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
316|Blade |BL-2036 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
317|LL Crankarm |CA-5965 |false |false |Black | 500| 375| 0| 0| | | | | 0| |L | | | |2008-04-30 00:00:00.000| | |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
318|ML Crankarm |CA-6738 |false |false |Black | 500| 375| 0| 0| | | | | 0| |M | | | |2008-04-30 00:00:00.000| | |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
-- more --
Sample Solution:
-- Selecting specific columns from the Product table and applying a CASE statement to compare MakeFlag and FinishedGoodsFlag
SELECT
-- Selecting the ProductID column
ProductID,
-- Selecting the MakeFlag column
MakeFlag,
-- Selecting the FinishedGoodsFlag column
FinishedGoodsFlag,
-- Applying a CASE statement to handle conditions based on MakeFlag and FinishedGoodsFlag
CASE
-- If MakeFlag is equal to FinishedGoodsFlag, return NULL
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
-- If MakeFlag is not equal to FinishedGoodsFlag, return MakeFlag
ELSE MakeFlag
END
FROM
Production.Product
-- Filtering records where ProductID is less than 10
WHERE
ProductID < 10;
Explanation:
- This SQL code selects specific columns from the Product table and applies a CASE statement to handle conditions based on MakeFlag and FinishedGoodsFlag.
- The CASE statement compares the values of MakeFlag and FinishedGoodsFlag for each row:
- If MakeFlag is equal to FinishedGoodsFlag, NULL is returned.
- If MakeFlag is not equal to FinishedGoodsFlag, MakeFlag itself is returned.
- The result set includes columns for ProductID, MakeFlag, and FinishedGoodsFlag, as well as the result of the CASE statement.
- Records are filtered to include only those where ProductID is less than 10.
- This SQL query can be used to identify products where MakeFlag and FinishedGoodsFlag are different, indicating potential inconsistencies in data or manufacturing status.
Sample Output:
productid|makeflag|finishedgoodsflag|makeflag|
---------+--------+-----------------+--------+
1|false |false | |
2|false |false | |
3|true |false |true |
4|false |false | |
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Change the display of product line categories with comment.
Next: Select data from first column that has a nonnull value.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.