w3resource

AdventureWorks Database: Check for similarity of the values

SQL Query - AdventureWorks: Exercise-131 with Solution

131. From the following table write a query in SQL to check the values of MakeFlag and FinishedGoodsFlag columns and return whether they are same or not. Return ProductID, MakeFlag, FinishedGoodsFlag, and the column that are null or not null.

Sample table: production.Product


Click to view Full table

Sample Solution:

SELECT ProductID, MakeFlag, FinishedGoodsFlag,   
   NULLIF(MakeFlag,FinishedGoodsFlag) AS "Null if Equal"
FROM Production.Product  
WHERE ProductID < 10;

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.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook