SQL Exercise: Display orders after sorting orders by order number
All Orders Sorted by Order Number
From the following table, write a SQL query to find all the orders. Sort the result-set in ascending order by ord_no. Return all fields.
Sample table: orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample Solution:
-- This query selects all columns from the 'orders' table and orders the result set by the 'ord_no' column.
SELECT *
-- Specifies the table from which to retrieve the data (in this case, 'orders').
FROM orders
-- Orders the result set by the 'ord_no' column in ascending order (default order).
ORDER BY ord_no;
Output of the Query:
ord_no purch_amt ord_date customer_id salesman_id 70001 150.50 2012-10-05 3005 5002 70002 65.26 2012-10-05 3002 5001 70003 2480.40 2012-10-10 3009 5003 70004 110.50 2012-08-17 3009 5003 70005 2400.60 2012-07-27 3007 5001 70007 948.50 2012-09-10 3005 5002 70008 5760.00 2012-09-10 3002 5001 70009 270.65 2012-09-10 3001 5005 70010 1983.43 2012-10-10 3004 5006 70011 75.29 2012-08-17 3003 5007 70012 250.45 2012-06-27 3008 5002 70013 3045.60 2012-04-25 3002 5001
Code Explanation:
The said SQL query retrieves all columns from the 'orders' table and returns the result sorted in ascending order based on the "ord_no" column. The "ORDER BY ord_no" clause sorts the rows in the result set based on the values in the ord_no column.
Relational Algebra Expression:
Relational Algebra Tree:
Explanation:
Visual presentation:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
For more Practice: Solve these Related Problems:
- Write a SQL query to select all orders and sort the result-set in ascending order by customer_id, then by ord_no.
- Write a SQL query to retrieve all order details sorted by ord_date in ascending order and secondarily by ord_no.
- Write a SQL query to list all orders sorted by ord_no and filter out orders where purch_amt is NULL.
- Write a SQL query to select all order fields sorted by ord_no ascending, including only orders with even ord_no values.
- Write a SQL query to display all orders and order them by the length of the string representation of ord_no in ascending order.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Number of orders booked for each day.
Next SQL Exercise: Arrange the orders according to the order date.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.