AdventureWorks Database: Return all orders with IDs greater than 60000
SQL Query - AdventureWorks: Exercise-167 with Solution
167. From the following tables write a query in SQL to return all orders with IDs greater than 60000.
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 table: Production.Product
productid|name |productnumber|makeflag|finishedgoodsflag|color |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate |sellenddate |discontinueddate|rowguid |modifieddate |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
1|Adjustable Race |AR-5381 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
2|Bearing Ball |BA-8327 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
3|BB Ball Bearing |BE-2349 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
4|Headset Ball Bearings |BE-2908 |false |false | | 800| 600| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
316|Blade |BL-2036 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
317|LL Crankarm |CA-5965 |false |false |Black | 500| 375| 0| 0| | | | | 0| |L | | | |2008-04-30 00:00:00.000| | |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
318|ML Crankarm |CA-6738 |false |false |Black | 500| 375| 0| 0| | | | | 0| |M | | | |2008-04-30 00:00:00.000| | |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
-- more --
Sample Solution:
-- This SQL query selects the SalesOrderID from the salesorderdetail table, and the ProductID and Name from the product table, joining the two tables based on the ProductID column. It filters the results to include only those with SalesOrderID greater than 60000, and orders the results by SalesOrderID.
-- Selecting SalesOrderID from the salesorderdetail table and assigning the alias 'fis' to the table.
SELECT fis.SalesOrderid,
-- Selecting ProductID from the product table and assigning the alias 'dp' to the table.
dp.Productid,
-- Selecting the Name column from the product table.
dp.Name
-- Joining the salesorderdetail table (aliased as 'fis') with the product table (aliased as 'dp') using INNER JOIN and matching rows where the ProductID in both tables are equal.
FROM sales.salesorderdetail AS fis
JOIN production.product AS dp
ON dp.Productid = fis.Productid
-- Filtering the results to include only rows where the SalesOrderID in the salesorderdetail table is greater than 60000.
WHERE fis.SalesOrderid > 60000
-- Ordering the results by SalesOrderID in ascending order.
ORDER BY fis.SalesOrderid;
Explanation:
- The query retrieves the SalesOrderID, ProductID, and Name of each product ordered where the SalesOrderID is greater than 60000.
- An INNER JOIN is used to combine rows from the salesorderdetail table (aliased as 'fis') with matching rows from the product table (aliased as 'dp') based on the ProductID column.
- The WHERE clause filters the results to include only rows where the SalesOrderID in the salesorderdetail table is greater than 60000.
- The ORDER BY clause sorts the results by SalesOrderID in ascending order.
Sample Output:
salesorderid|productid|name |
------------+---------+--------------------------------+
60001| 929|ML Mountain Tire |
60001| 921|Mountain Tire Tube |
60001| 711|Sport-100 Helmet, Blue |
60002| 929|ML Mountain Tire |
60003| 712|AWC Logo Cap |
60003| 869|Women's Mountain Shorts, L |
60004| 870|Water Bottle - 30 oz. |
60005| 870|Water Bottle - 30 oz. |
60005| 712|AWC Logo Cap |
60006| 921|Mountain Tire Tube |
60006| 873|Patch Kit/8 Patches |
60007| 878|Fender Set - Mountain |
60007| 713|Long-Sleeve Logo Jersey, S |
60008| 878|Fender Set - Mountain |
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Fetch the matching rows from two tables.
Next: Return NULL if Territoryid has no orders.
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