AdventureWorks Database: Check for similarity of the values
131. From the following table, write a query in SQL to check the values of MakeFlag and FinishedGoodsFlag columns and return whether they are the same or not. Return NULL if MakeFlag is equal to FinishedGoodsFlag, otherwise return the value of MakeFlag. Return the columns ProductID, MakeFlag, FinishedGoodsFlag, and a column indicating NULL if they are equal. Filter the results for products where ProductID is less than 10.
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 using the NULLIF function to compare MakeFlag and FinishedGoodsFlag
SELECT
-- Selecting the ProductID column
ProductID,
-- Selecting the MakeFlag column
MakeFlag,
-- Selecting the FinishedGoodsFlag column
FinishedGoodsFlag,
-- Applying the NULLIF function to return NULL if MakeFlag is equal to FinishedGoodsFlag
NULLIF(MakeFlag, FinishedGoodsFlag) AS "Null if Equal"
-- Selecting data from the Product table
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 uses the NULLIF function to compare MakeFlag and FinishedGoodsFlag.
- The NULLIF function returns NULL if the two specified expressions are equal; otherwise, it returns the first expression.
- In this case, NULLIF(MakeFlag, FinishedGoodsFlag) will return NULL if MakeFlag is equal to FinishedGoodsFlag.
- The result set includes columns for ProductID, MakeFlag, FinishedGoodsFlag, and the result of the NULLIF function, labeled as "Null if Equal".
- Records are filtered to include only those where ProductID is less than 10.
- This SQL query can be useful for identifying instances where MakeFlag and FinishedGoodsFlag are equal, potentially indicating inconsistencies or redundant data.
Sample Output:
productid|makeflag|finishedgoodsflag|Null if Equal|
---------+--------+-----------------+-------------+
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: Select data from first column that has a nonnull value.
Next: Return any distinct values that are returned by both the queries.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.