SQL Basics

• SQL statements are made up of clauses
• Some clauses are required; others are optional
• A clause usually consists of a keyword and an associated value
• For example, the SELECT statements used here include a required FROM clause

• ORDER BY is also a clause of the SELECT statement (optional)
• It sorts the result
• ORDER BY comes last in these SELECT statements, except for row-limiting clauses such as LIMIT or FETCH
• You can also sort by a column that is not included in the SELECT result

SELECT   <column>
FROM     <table>
ORDER BY <column>	

Select the product names from the Products table and sort them (9)

-- Oracle / MySQL / SQL Server
SELECT   prod_name
FROM     Products
ORDER BY prod_name;

SELECT   <column1>,
         <column2>,
         <column3>
FROM     <table>
ORDER BY <column1>,
         <column2>;

Select the product ID, price and name from Products, and sort by price and name

-- Oracle / MySQL / SQL Server
SELECT   prod_id,
         prod_price,
         prod_name
FROM     Products
ORDER BY prod_price,
         prod_name;

• Specify one or more numbers in ORDER BY to sort by the position of a column in the SELECT statement
• You cannot use a position number for a column that is not included in SELECT

SELECT	 <column1>,
         <column2>,
         <column3>
FROM     <table>
ORDER BY 1, 3;

Select the product ID, price and name from Products, and sort by column positions 2 and 3

-- Oracle / MySQL / SQL Server
SELECT	 prod_id,
         prod_price,
         prod_name
FROM     Products
ORDER BY 2,3;

• By default, ORDER BY sorts from A to Z (Ascending)
• To sort from Z to A, add the DESC keyword (descending)

SELECT   <column1>,
         <column2>,
         <column3>
FROM     <table>
ORDER BY <column1> DESC,
         <column2>;

Select the product ID, price and name from Products, and sort by price and name in descending order

-- Oracle / MySQL / SQL Server
SELECT	  prod_id,
          prod_price,
          prod_name
FROM      Products
ORDER BY  prod_price DESC,
          prod_name DESC;

6. Select cust_name from Customers and sort by cust_name (5)

7. Select everything from OrderItems and sort by order_num and item_price (18)

8. Select everything from OrderItems and sort by quantity in descending order (18)

9. Select the vendor names and countries from the Vendors table (6)

10. Select the product ID and quantity from OrderItems, and sort by quantity (18)