AdventureWorks Database: Orders with orderqtys, unitpricediscount, totaldues
SQL Query - AdventureWorks: Exercise-51 with Solution
51. From the following tables write a SQL query to retrieve the orders with orderqtys greater than 5 or unitpricediscount less than 1000, and totaldues greater than 100. Return all the columns from the tables.
Sample table: Sales.SalesOrderHeader
salesorderid|revisionnumber|orderdate |duedate |shipdate |status|onlineorderflag|purchaseordernumber|accountnumber |customerid|salespersonid|territoryid|billtoaddressid|shiptoaddressid|shipmethodid|creditcardid|creditcardapprovalcode|currencyrateid|subtotal |taxamt |freight |totaldue |comment|rowguid |modifieddate |
------------+--------------+-----------------------+-----------------------+-----------------------+------+---------------+-------------------+--------------+----------+-------------+-----------+---------------+---------------+------------+------------+----------------------+--------------+-----------+----------+---------+-----------+-------+------------------------------------+-----------------------+
43659| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO522145787 |10-4020-000676| 29825| 279| 5| 985| 985| 5| 16281|105041Vi84182 | | 20565.6206| 1971.5149| 616.0984| 23153.2339| |79b65321-39ca-4115-9cba-8fe0903e12e6|2011-06-07 00:00:00.000|
43660| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18850127500 |10-4020-000117| 29672| 279| 5| 921| 921| 5| 5618|115213Vi29411 | | 1294.2529| 124.2483| 38.8276| 1457.3288| |738dc42d-d03b-48a1-9822-f95a67ea7389|2011-06-07 00:00:00.000|
43661| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18473189620 |10-4020-000442| 29734| 282| 6| 517| 517| 5| 1346|85274Vi6854 | 4| 32726.4786| 3153.7696| 985.553| 36865.8012| |d91b9131-18a4-4a11-bc3a-90b6f53e9d74|2011-06-07 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4| 28832.5289| 2775.1646| 867.2389| 32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000|
43663| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18009186470 |10-4020-000510| 29565| 276| 4| 1073| 1073| 5| 4322|45303Vi22691 | | 419.4589| 40.2681| 12.5838| 472.3108| |9b1e7a40-6ae0-4ad3-811c-a64951857c4b|2011-06-07 00:00:00.000|
43664| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO16617121983 |10-4020-000397| 29898| 280| 1| 876| 876| 5| 806|95555Vi4081 | | 24432.6088| 2344.9921| 732.81| 27510.4109| |22a8a5da-8c22-42ad-9241-839489b6ef0d|2011-06-07 00:00:00.000|
43665| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO16588191572 |10-4020-000146| 29580| 283| 1| 849| 849| 5| 15232|35568Vi78804 | | 14352.7713| 1375.9427| 429.9821| 16158.6961| |5602c304-853c-43d7-9e79-76e320d476cf|2011-06-07 00:00:00.000|
-- more --
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 all columns from the SalesOrderHeader and SalesOrderDetail tables
SELECT *
-- From the Sales schema's SalesOrderHeader table, aliasing it as 'h'
FROM Sales.SalesOrderHeader AS h
-- Performing an inner join with the SalesOrderDetail table based on SalesOrderID
INNER JOIN Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
-- Filtering the joined result set to include only rows where TotalDue in SalesOrderHeader is greater than 100
-- AND either OrderQty in SalesOrderDetail is greater than 5 OR unitpricediscount in SalesOrderDetail is less than 1000.00
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.unitpricediscount < 1000.00);
Explanation:
- The SQL query retrieves data from the SalesOrderHeader and SalesOrderDetail tables in the Sales schema.
- It selects all columns from both tables.
- The INNER JOIN clause joins rows from the SalesOrderHeader table (aliased as 'h') with matching rows from the SalesOrderDetail table (aliased as 'd') based on the common column SalesOrderID.
- The WHERE clause filters the joined result set to include only rows where:
- TotalDue in the SalesOrderHeader table is greater than 100.
- Either OrderQty in the SalesOrderDetail table is greater than 5 or unitpricediscount in the SalesOrderDetail table is less than 1000.00.
- Parentheses are used to group the OR conditions for clarity and to ensure correct logical evaluation.
Sample Output:
salesorderid|revisionnumber|orderdate |duedate |shipdate |status|onlineorderflag|purchaseordernumber|accountnumber |customerid|salespersonid|territoryid|billtoaddressid|shiptoaddressid|shipmethodid|creditcardid|creditcardapprovalcode|currencyrateid|subtotal |taxamt |freight |totaldue |comment|rowguid |modifieddate |salesorderid|salesorderdetailid|carriertrackingnumber|orderqty|productid|specialofferid|unitprice|unitpricediscount|rowguid |modifieddate |
------------+--------------+-----------------------+-----------------------+-----------------------+------+---------------+-------------------+--------------+----------+-------------+-----------+---------------+---------------+------------+------------+----------------------+--------------+----------+---------+---------+----------+-------+------------------------------------+-----------------------+------------+------------------+---------------------+--------+---------+--------------+---------+-----------------+------------------------------------+-----------------------+
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 30|2E53-4802-85 | 3| 764| 1| 419.4589| 0|2dfd4d76-e020-4b80-a0a5-6463d0c2df73|2011-05-31 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 31|2E53-4802-85 | 5| 770| 1| 419.4589| 0|1f1aa6dc-04a2-48e2-af9f-e46d8f59aa55|2011-05-31 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 32|2E53-4802-85 | 2| 730| 1| 183.9382| 0|5ace6cb5-7341-4d88-9425-56ab7ae512e7|2011-05-31 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 33|2E53-4802-85 | 4| 754| 1| 874.794| 0|dff2a7f2-37f2-4016-bedf-4dbb8c88b631|2011-05-31 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 34|2E53-4802-85 | 3| 725| 1| 183.9382| 0|92897ee9-72a6-49e6-a421-c243b531aff2|2011-05-31 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 35|2E53-4802-85 | 5| 762| 1| 419.4589| 0|1ce18faa-047d-41b1-8cf6-5191c092ab3c|2011-05-31 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 36|2E53-4802-85 | 3| 765| 1| 419.4589| 0|f833cb4a-d6e3-40ac-b383-1c4e7220afef|2011-05-31 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 37|2E53-4802-85 | 2| 768| 1| 419.4589| 0|d8e84dbd-a1ed-41e4-a9a1-0ad95fb9b323|2011-05-31 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 38|2E53-4802-85 | 1| 753| 1| 2146.962| 0|491c561f-6b1e-47d0-95d5-6519654a5f47|2011-05-31 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 39|2E53-4802-85 | 1| 756| 1| 874.794| 0|2ba4cc58-b832-47c0-aef4-d15d029cc083|2011-05-31 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 40|2E53-4802-85 | 3| 763| 1| 419.4589| 0|d5c51a6a-6d12-4ceb-aed4-842e78baf142|2011-05-31 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 41|2E53-4802-85 | 1| 732| 1| 356.898| 0|48420c59-4fbd-4817-a1df-613dce40b18b|2011-05-31 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4|28832.5289|2775.1646| 867.2389|32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43662| 42|2E53-4802-85 | 6| 758| 1| 874.794| 0|2c4f704d-c794-4613-9c8b-258619fa20fa|2011-05-31 00:00:00.000|
...
Contribute your code and comments through Disqus.
Previous: Fetch first twenty rows from the table.
Next: Searches for the word red in the name column.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics