SQL Enchancements for Querying a Single Table

• Order By allows us to control the way the output table is displayed

• Select * 
  From ORDER_ITEM
  Order by Price;
     

• We can list multiple columns to order by.

• Select * 
  From ORDER_ITEM
  Order by Price, OrderNumber;
     

• We can also specify ascending (ASC) or descending (DESC).

• Select * 
  From ORDER_ITEM
  Order by Price Desc, OrderNumber;
     

• The where clause can be more complex than previously presented.
  • and and or

  • Select *
    From ORDER_ITEM
    Where Price < 100 OR Price > 200;
    
    	

  • in and not in allow us to limit the data to membership in, or not in a list

  • Select * 
    From ORDER_ITEM 
    Where Quantity in (1,2);
    	

  • Select * 
    From SKU_DATA 
    Where Buyer not in ("Cindy Lo","Jerry Martin");
    	

  • between lets us select data in a range, endpoints included

  • Slect * 
    From ORDER_ITEM 
    Where price Between 60 and 300;
    	

  • Wildcards and like
  • a % matches anything.

  • Select * 
    From ORDER_ITEM 
    Where price like "1%";
    	

  • Select * 
    From SKU_DATA 
    Where SKU_Description like "%,%";
    

  • the underscore "_" matches any single character.

  • Select * 
    From SKU_DATA 
    Where SKU like "1__1%";
    	

  • These can stack (DOH)

  • Select * 
    From SKU_DATA 
    Where SKU like "1__1%" 
    Order by Buyer ;