w3resource

AdventureWorks Database: Returns TRUE even if NULL is specified in the subquery

SQL Query - AdventureWorks: Exercise-145 with Solution

145. From the following table write a query in SQL to return TRUE even if NULL is specified in the subquery. Return DepartmentID, Name and sort the result set in ascending order.

Sample table: HumanResources.Department
departmentid|name                      |groupname                           |modifieddate           |
------------+--------------------------+------------------------------------+-----------------------+
           1|Engineering               |Research and Development            |2008-04-30 00:00:00.000|
           2|Tool Design               |Research and Development            |2008-04-30 00:00:00.000|
           3|Sales                     |Sales and Marketing                 |2008-04-30 00:00:00.000|
           4|Marketing                 |Sales and Marketing                 |2008-04-30 00:00:00.000|
           5|Purchasing                |Inventory Management                |2008-04-30 00:00:00.000|
           6|Research and Development  |Research and Development            |2008-04-30 00:00:00.000|
           7|Production                |Manufacturing                       |2008-04-30 00:00:00.000|
           8|Production Control        |Manufacturing                       |2008-04-30 00:00:00.000|
           9|Human Resources           |Executive General and Administration|2008-04-30 00:00:00.000|
          10|Finance                   |Executive General and Administration|2008-04-30 00:00:00.000|
          11|Information Services      |Executive General and Administration|2008-04-30 00:00:00.000|
          12|Document Control          |Quality Assurance                   |2008-04-30 00:00:00.000|
          13|Quality Assurance         |Quality Assurance                   |2008-04-30 00:00:00.000|
          14|Facilities and Maintenance|Executive General and Administration|2008-04-30 00:00:00.000|
          15|Shipping and Receiving    |Inventory Management                |2008-04-30 00:00:00.000|
          16|Executive                 |Executive General and Administration|2008-04-30 00:00:00.000|

Click to view Full table

Sample Solution:

SELECT DepartmentID, Name   
FROM HumanResources.Department   
WHERE EXISTS (SELECT NULL)  
ORDER BY Name ASC ;

Sample Output:

departmentid|name                      |
------------+--------------------------+
          12|Document Control          |
           1|Engineering               |
          16|Executive                 |
          14|Facilities and Maintenance|
          10|Finance                   |
           9|Human Resources           |
          11|Information Services      |
           4|Marketing                 |
           7|Production                |
           8|Production Control        |
           5|Purchasing                |
          13|Quality Assurance         |
           6|Research and Development  |
           3|Sales                     |
          15|Shipping and Receiving    |
           2|Tool Design               |

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Retrieve rows whose datetime values are between '20111212' and '20120105'.
Next: Get employees with Johnson last names.


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.