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

Last update on December 20 2024 11:56:58 (UTC/GMT +8 hours)

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.

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:

-- Selecting ProductID, UnitPrice, OrderQty, and calculating the modulo of the integer value of UnitPrice divided by OrderQty
SELECT 
    -- Selecting the ProductID column from the SalesOrderDetail table
    ProductID, 
    -- Selecting the UnitPrice column from the SalesOrderDetail table
    UnitPrice, 
    -- Selecting the OrderQty column from the SalesOrderDetail table
    OrderQty,  
    -- Casting UnitPrice as an integer and calculating the modulo (%) with OrderQty, labeling it as Modulo
    CAST(UnitPrice AS INT) % OrderQty AS Modulo  
-- Selecting data from the SalesOrderDetail table
FROM 
    Sales.SalesOrderDetail;

Explanation:

• This SQL code selects ProductID, UnitPrice, OrderQty columns from the SalesOrderDetail table and calculates the modulo of the integer value of UnitPrice divided by OrderQty.
• The SELECT statement specifies the columns to be included in the result set.
• The CAST function is used to convert the UnitPrice column to an integer value before calculating the modulo with OrderQty.
• The modulo operation (%) returns the remainder when UnitPrice (casted to integer) is divided by OrderQty.
• The result set will contain columns for ProductID, UnitPrice, OrderQty, and the calculated modulo labeled as Modulo.

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.