SQL Enhancements for Querying a Single Table

• Order By
  • Give a comma separated list to sort by.
    • Sorts on the first, then second for ties, then third for ties ...

    • SELECT * 
      FROM CODD_ORDER_ITEM
      ORDER BY Quantity;
      
      SELECT * 
      FROM CODD_ORDER_ITEM
      ORDER BY Quantity, Price;
                        

    • You can change the sort order by adding ASC or DESC

    • SELECT * 
      FROM CODD_ORDER_ITEM
      ORDER BY Quantity DESC;
                    

    • You can also use an expression

    • SELECT * 
      FROM CODD_ORDER_ITEM
      ORDER BY Quantity * Price ;
                    

    • You can use an alias

    • SELECT CONCAT(OrderNumber, ", ", SKU) AS A 
      FROM CODD_ORDER_ITEM
      ORDER by A;
      	      

    • Or column numbers

    • SELECT * 
      FROM CODD_ORDER_ITEM
      ORDER BY 3,2 ;
                    

    • But this is deprecated in standard SQL
• Where clause can have complex conditions
  • This is by no means exhaustive
  • Look here
  • AND, OR,

  • SELECT *
    FROM CODD_ORDER_ITEM
    WHERE Quantity > 1
          AND PRICE < 300;
    
    SELECT * 
    FROM CODD_ORDER_ITEM 
    WHERE NOT(quantity <2 OR  quantity > 3);
              

  • IN (expression)

  • SELECT * 
    FROM CODD_ORDER_ITEM 
    WHERE OrderNumber IN (1000,3000);
    	  

  • or NOT IN

  • SELECT * 
    FROM CODD_ORDER_ITEM 
    WHERE OrderNumber NOT IN (1000,3000);
    	  

  • BETWEEN expr and expr

  • SELECT *
    FROM CODD_ORDER_ITEM 
    WHERE ExtendedPrice BETWEEN  99 AND 201;
    	  

  • The between is inclusive of endpoints

  • SELECT * 
    FROM CODD_ORDER_ITEM 
    WHERE OrderNumber BETWEEN  1000 AND 2000;
    
    SELECT * 
    FROM CODD_ORDER_ITEM 
    WHERE OrderNumber BETWEEN  10*100 AND 20*100;
    	  

  • The string comparison functions LIKE, NOT LIKE, STRCMP
  • % is the wild card, one or more character.
  • _ is a single character wild card.

  • SELECT * 
    FROM CODD_SKU_DATA 
    WHERE Department LIKE "C%g";
    
    SELECT * 
    FROM CODD_SKU_DATA 
    WHERE Department LIKE "C_g";
    
    SELECT * 
    FROM CODD_SKU_DATA
    WHERE SKU LIKE '20_000';
    	  

• We can do computations as the item selected.
  • SELECT function
  • SUM, AFVG, MIN, MAX, COUNT

  • SELECT SUM(Quantity) 
    FROM CODD_ORDER_ITEM;
    
    SELECT SUM(Quantity) 
    FROM CODD_ORDER_ITEM 
    WHERE OrderNumber=1000;
    
    SELECT COUNT(Quantity) 
    FROM CODD_ORDER_ITEM;
    	 

  • Aliasing becomes nice here:

  • SELECT COUNT(Quantity) AS "Total Items" 
    FROM CODD_ORDER_ITEM;
    	 

  • And we can do quite a bit of work here

    SELECT SKU, 
           SUM(Quantity) AS Total,
           MIN(Quantity) AS Minimum, 
           MAX(Quantity) AS Maxmimum, 
           AVG(Quantity) AS Average 
    FROM CODD_ORDER_ITEM 
    WHERE SKU=101100;
    	 

• Note that string concatenation is CONCAT( string, string, string)

• SELECT CONCAT(SKU, '-',Quantity) 
  FROM CODD_ORDER_ITEM;