Upgrade to Pro — share decks privately, control downloads, hide ads and more …

SQL Basics - RRUG 1/6/2016

kickinbahk
January 06, 2016

SQL Basics - RRUG 1/6/2016

Overview of SQL and basic syntax.

kickinbahk

January 06, 2016
Tweet

More Decks by kickinbahk

Other Decks in Technology

Transcript

  1. SQL
    BASICS

    View Slide

  2. SQL
    -
    Structured Query
    Language

    View Slide

  3. A Computer Language used
    for:
    •Storing,
    •Manipulating,
    •Querying

    View Slide

  4. Data stored in
    relational
    databases

    View Slide

  5. RELATIONAL
    DATABASES

    View Slide

  6. A collection of data items organized
    as a set of formally-described tables
    from which data can be accessed or
    reassembled in many different ways
    without having to reorganize the
    database tables.

    View Slide

  7. View Slide

  8. Set of tables
    with data organized
    by predefined
    categories

    View Slide

  9. Each row contains a
    unique instance of data for
    the categories defined by
    the columns.

    View Slide

  10. View Slide

  11. SQL FLAVORS

    View Slide

  12. •Standard
    •PostgreSQL
    •DB2
    •MicrosoftSQL
    •MySQL
    •Oracle
    •Informix

    View Slide

  13. SQL is Standardized
    but different flavors
    are variations

    View Slide

  14. Compare flavors:
    http://troels.arvin.dk/db/rdbms/
    https://en.wikibooks.org/wiki/SQL_Dialects_Reference

    View Slide

  15. SYNTAX

    View Slide

  16. SELECT

    View Slide

  17. Used to select data
    from the tables
    located in a database.

    View Slide

  18. Two keywords: we need
    to SELECT information
    FROM a table.
    SELECT "column_name" FROM "table_name";
    Syntax:

    View Slide

  19. SELECT Store_Name FROM Store_Information;
    Syntax:

    View Slide

  20. Store_Name
    Los Angeles
    San Diego
    Los Angeles
    Boston
    Result:

    View Slide

  21. Select Multiple
    Columns

    View Slide

  22. SELECT Store_Name, Sales FROM Store_Information;
    Syntax:

    View Slide

  23. Store_Name Sales
    Los Angeles 1500
    San Diego 250
    Los Angeles 300
    Boston 700
    Result:

    View Slide

  24. Select All
    Columns
    2 ways…

    View Slide

  25. SELECT Store_Name, Sales, Txn_Date FROM Store_Information;
    Syntax:

    View Slide

  26. SELECT * FROM Store_Information;
    Syntax:

    View Slide

  27. Select Distinct

    View Slide

  28. SELECT - all information
    from a column (or
    columns) on a table

    View Slide

  29. This may cause
    redundancies.

    View Slide

  30. To grab a distinct
    element, all we need to
    do is to add DISTINCT
    after SELECT

    View Slide

  31. SELECT DISTINCT Store_Name FROM Store_Information;
    Syntax:

    View Slide

  32. Store_Name
    Los Angeles
    San Diego
    Boston
    Result:

    View Slide

  33. Where

    View Slide

  34. WHERE - allows
    filtering the result set
    based on certain
    conditions.

    View Slide

  35. SELECT "column_name"
    FROM "table_name"
    WHERE "condition";

    View Slide

  36. SELECT Store_Name
    FROM Store_Information
    WHERE Sales > 1000;
    Syntax:

    View Slide

  37. Store_Name
    Los Angeles
    Result:

    View Slide

  38. Where with Or

    View Slide

  39. SELECT *
    FROM Store_Information
    WHERE Sales > 1000 OR
    Txn_Date = 'Jan-08-1999';
    Syntax:

    View Slide

  40. Store_Name Sales Txn_Date
    Los Angeles 1500 Jan-05-1999
    Los Angeles 300 Jan-08-1999
    Boston 700 Jan-08-1999
    Result:

    View Slide

  41. AND or OR

    View Slide

  42. Allows for
    compound
    conditions

    View Slide

  43. SELECT "column_name"
    FROM "table_name"
    WHERE "simple condition"
    [AND|OR] "simple condition";

    View Slide

  44. We can use
    parenthesis ( ) to
    indicate the order of
    the condition.

    View Slide

  45. SELECT Store_Name
    FROM Store_Information
    WHERE Sales > 1000
    OR (Sales < 500 AND Sales > 275);
    Syntax:

    View Slide

  46. Store_Name
    Los Angeles
    Result:

    View Slide

  47. Inner Join

    View Slide

  48. INNER JOIN - returns
    all rows from multiple
    tables where the join
    condition is met.

    View Slide

  49. SELECT Orders.OrderID,
    Customers.CustomerName, Orders.OrderDate
    FROM Orders
    INNER JOIN Customers
    ON Orders.CustomerID=Customers.CustomerID;
    Syntax:

    View Slide

  50. Result:

    View Slide

  51. Different Joins
    INNER JOIN: Returns all rows when there
    is at least one match in BOTH tables
    LEFT JOIN: Return all rows from the left
    table, and the matched rows from the
    right table

    View Slide

  52. Different Joins
    RIGHT JOIN: Return all rows from
    the right table, and the matched
    rows from the left table
    FULL JOIN: Return all rows when
    there is a match in ONE of the
    tables

    View Slide

  53. WHY KNOW SQL
    IF I KNOW RAILS?

    View Slide

  54. The Rails ORM is an
    attempt at an abstraction

    View Slide

  55. The SQL queries that the
    Rails ORM generates for you
    may need to be fine-tuned.

    View Slide

  56. Resources
    Zed Shaw’s Learn SQL the Hard Way:
    http://sql.learncodethehardway.org/
    book/introduction.html
    Use the Index Luke:
    http://use-the-index-luke.com/sql/table-
    of-contents

    View Slide