SQL Commands

Card Set Information

SQL Commands
2014-01-11 10:00:32
sql commands

SQL Commands
Show Answers:

    used to SELECT data FROM tables in a database; will return multiples

    SELECT column_name FROM table_name;
    used to SELECT data FROM tables in a database without multiples

    SELECT DISTINCT column_name FROM table_name;
  3. WHERE clause
    filters the results based on certain conditions; can include a single comparison clause (simple condition) or multiple comparisons combined with AND or OR operators (compound conditions)

    SELECT column_name FROM table_name WHERE condition;
  4. { }+
    the expression inside the brackets will occur one or more times
  5. AND OR
    connects simple conditions to create compound conditions; [AND|OR] means either AND or OR can be used

    SELECT column_name FROM table_name WHERE simple condition {[AND|OR] simple condition}+;
  6. IN (WHERE)
    limits selection criteria to one or more discrete values; values in the parenthesis can be one or more, each separated by a comma

    SELECT column_name FROM table_name WHERE column_name IN ('value1', 'value2'...);
    selects a range of values; includes the values used in the results

    • SELECT colum_name
    • FROM table_name
    • WHERE column_name
    • BETWEEN 'value1' AND 'value2';

    To exclude values from the result use:

    • SELECT column_name
    • FROM table_name
    • WHERE (column-name > 'value1')
    • AND (column_name < 'value2');
    used to exclude a range of values

    • SELECT column_name
    • FROM table_name
    • WHERE column_name
    • NOT BETWEEN 'value1' and 'value2';
  9. LIKE
    allows you to search based on a pattern rather than specifying exactly what is desired (as in IN) or spell out a range (as in BETWEEN)               

    • SELECT column_name
    • FROM table_name WHERE column_name
    • LIKE {PATTERN};{PATTERN}: often consists of wildcards
  10. ORDER BY
    orders the information extracted from a table

    • SELECT column_name
    • FROM table_name
    • [WHERE condition] ORDER BY column name [ASC/DESC]           

    • There WHERE statement is optional, however if it exists, it comes before the ORDER BY               

    • ASC – Ascending                             
    • DESC – Descending               

    • You can order multiple columns by separating first, second… columns with comma               
    • This will order by first column first and, in the event of a tie, order by the second
  11. AVG()
    Calculates the average of a column

    • SELECT AVG(column_name)
    • FROM table_name;
  12. COUNT()
    Counts the number of rows in a table               

    SELECT COUNT(column_name)                FROM table_name;
  13. MAX()
    Finds the maximum value in a column               

    • SELECT MAX(column_name)               
    • FROM table_name;
  14. MIN()
    Finds the minimum value in a column               

    • SELECT MIN(column_name)               
    • FROM table_name;
  15. SUM()
    Finds the total of all values in a column               

    • SELECT SUM(column_name)                
    • FROM table_name;
  16. GROUP BY
    allows sums to be grouped based on table conditions               

    • SELECTcolumn_name1, SUM(column_name2)               
    • FROM table_name               
    • GROUP BY column_name1;
  17. HAVING
    the WHERE clause of aggregate functions; may or may not incorporate GROUP BY               

    • SELECT column_name1, SUM(column_name2)               
    • FROM table_name               
    • GROUP BY column_name1         
    • HAVING (arithmetic function condition);
  18. Table Alias
    renames tables; placed directly after table name in FROM clause; frequently used when performing joins               

    • SELECT column_name                
    • FROM table_alias.table_name table_alias;
  19. Column Alias
    Helps organize output by renaming the column selected; used to rename columns with functions, ie SUM(column_name) to show as column_name               

    • SELECT column_alias.column_name1 column_alias               
    • FROM table_name;
  20. AS
    Used to assign an alias to a column or table.

    • SELECT table_alias.column_name AS column_alias
    • FROM table_name AS table_alias;
    Combining infromation from multiple tables. (See "JOIN and ON" card for alternate)

    • SELECT A1.Region_Name REGION, SUM(A2.Sales) SALES
    • FROM Geography A1, Store_Information A2
    • WHERE A1.Store_Name = A2.Store_Name
    • GROUP BY A1.Region_Name;
  22. JOIN and ON
    Combines information from multiple tables. (See "JOINING" card for alternate)

    • SELECT A1.column_name1 ALIAS1, SUM(A2.column_name2) ALIAS2
    • FROM table_name1 A1, table_name2 A2
    • JOIN table_name2 A2
    • ON A1.column_name3 = A2.column_name3
    • GROUP BY A1.column_name1;
    JOIN that returns rows where there is at least one match on both tables.

    For example: We want to find out sales by store and we only want to see stores with sales listed in the report...

    • SELECT A1.Store_Name STORE, SUM(A2.Sales) SALES
    • FROM Geography A1
    • INNER JOIN Store_Information A2
    • ON A1.Store_Name = A2.Store_Name
    • GROUP BY A1.Store_Name;