AdventureWorks Database: Find unit price, ID number, and modulus of division
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 --
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.