w3resource

AdventureWorks Database: Find unit price, ID number, and modulus of division

SQL Query - AdventureWorks: Exercise-141 with Solution

141. From the following table write a query in SQL to return the ID number, unit price, and the modulus (remainder) of dividing product prices. Convert the modulo to an integer value.

Sample table: Sales.SalesOrderDetail
salesorderid|salesorderdetailid|carriertrackingnumber|orderqty|productid|specialofferid|unitprice|unitpricediscount|rowguid                             |modifieddate           |
------------+------------------+---------------------+--------+---------+--------------+---------+-----------------+------------------------------------+-----------------------+
       43659|                 1|4911-403C-98         |       1|      776|             1| 2024.994|                0|b207c96d-d9e6-402b-8470-2cc176c42283|2011-05-31 00:00:00.000|
       43659|                 2|4911-403C-98         |       3|      777|             1| 2024.994|                0|7abb600d-1e77-41be-9fe5-b9142cfc08fa|2011-05-31 00:00:00.000|
       43659|                 3|4911-403C-98         |       1|      778|             1| 2024.994|                0|475cf8c6-49f6-486e-b0ad-afc6a50cdd2f|2011-05-31 00:00:00.000|
       43659|                 4|4911-403C-98         |       1|      771|             1| 2039.994|                0|04c4de91-5815-45d6-8670-f462719fbce3|2011-05-31 00:00:00.000|
       43659|                 5|4911-403C-98         |       1|      772|             1| 2039.994|                0|5a74c7d2-e641-438e-a7ac-37bf23280301|2011-05-31 00:00:00.000|
       43659|                 6|4911-403C-98         |       2|      773|             1| 2039.994|                0|ce472532-a4c0-45ba-816e-eefd3fd848b3|2011-05-31 00:00:00.000|
       43659|                 7|4911-403C-98         |       1|      774|             1| 2039.994|                0|80667840-f962-4ee3-96e0-aeca108e0d4f|2011-05-31 00:00:00.000|
       43659|                 8|4911-403C-98         |       3|      714|             1|  28.8404|                0|e9d54907-e7b7-4969-80d9-76ba69f8a836|2011-05-31 00:00:00.000|
       43659|                 9|4911-403C-98         |       1|      716|             1|  28.8404|                0|aa542630-bdcd-4ce5-89a0-c1bf82747725|2011-05-31 00:00:00.000|
       43659|                10|4911-403C-98         |       6|      709|             1|      5.7|                0|ac769034-3c2f-495c-a5a7-3b71cdb25d4e|2011-05-31 00:00:00.000|
       43659|                11|4911-403C-98         |       2|      712|             1|   5.1865|                0|06a66921-6b9f-4199-a912-ddafd383472b|2011-05-31 00:00:00.000|
       43659|                12|4911-403C-98         |       4|      711|             1|  20.1865|                0|0e371ee3-253e-4bb0-b813-83cf4224f972|2011-05-31 00:00:00.000|
	   -- more --

Click to view Full table

Sample Solution:

SELECT ProductID, UnitPrice, OrderQty,  
   CAST(UnitPrice AS INT) % OrderQty AS Modulo  
FROM Sales.SalesOrderDetail;

Sample Output:

productid|unitprice|orderqty|modulo|
---------+---------+--------+------+
      776| 2024.994|       1|     0|
      777| 2024.994|       3|     0|
      778| 2024.994|       1|     0|
      771| 2039.994|       1|     0|
      772| 2039.994|       1|     0|
      773| 2039.994|       2|     0|
      774| 2039.994|       1|     0|
      714|  28.8404|       3|     2|
      716|  28.8404|       1|     0|
      709|      5.7|       6|     0|
      712|   5.1865|       2|     1|
      711|  20.1865|       4|     0|
      762| 419.4589|       1|     0|
      758|  874.794|       1|     0|
      745|   809.76|       1|     0|
      743| 714.7043|       1|     0|
      747| 714.7043|       2|     1|
      712|   5.1865|       4|     1|
      715|  28.8404|       4|     1|
...

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Calculate sales targets per month for salespeople.
Next: Find marketing Assistants with more than 41 vacation hours.


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.