Pro Yearly is on sale from $80 to $50! »

SQL Basics - RRUG 1/6/2016

55e2e1f28b890e26c101ed44c5f1d3af?s=47 kickinbahk
January 06, 2016

SQL Basics - RRUG 1/6/2016

Overview of SQL and basic syntax.

55e2e1f28b890e26c101ed44c5f1d3af?s=128

kickinbahk

January 06, 2016
Tweet

Transcript

  1. SQL BASICS

  2. SQL - Structured Query Language

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

  4. Data stored in relational databases

  5. RELATIONAL DATABASES

  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.
  7. None
  8. Set of tables with data organized by predefined categories

  9. Each row contains a unique instance of data for the

    categories defined by the columns.
  10. None
  11. SQL FLAVORS

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

  13. SQL is Standardized but different flavors are variations

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

  15. SYNTAX

  16. SELECT

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

    database.
  18. Two keywords: we need to SELECT information FROM a table.

    SELECT "column_name" FROM "table_name"; Syntax:
  19. SELECT Store_Name FROM Store_Information; Syntax:

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

  21. Select Multiple Columns

  22. SELECT Store_Name, Sales FROM Store_Information; Syntax:

  23. Store_Name Sales Los Angeles 1500 San Diego 250 Los Angeles

    300 Boston 700 Result:
  24. Select All Columns 2 ways…

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

  26. SELECT * FROM Store_Information; Syntax:

  27. Select Distinct

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

    a table
  29. This may cause redundancies.

  30. To grab a distinct element, all we need to do

    is to add DISTINCT after SELECT
  31. SELECT DISTINCT Store_Name FROM Store_Information; Syntax:

  32. Store_Name Los Angeles San Diego Boston Result:

  33. Where

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

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

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

  37. Store_Name Los Angeles Result:

  38. Where with Or

  39. SELECT * FROM Store_Information WHERE Sales > 1000 OR Txn_Date

    = 'Jan-08-1999'; Syntax:
  40. Store_Name Sales Txn_Date Los Angeles 1500 Jan-05-1999 Los Angeles 300

    Jan-08-1999 Boston 700 Jan-08-1999 Result:
  41. AND or OR

  42. Allows for compound conditions

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

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

    of the condition.
  45. SELECT Store_Name FROM Store_Information WHERE Sales > 1000 OR (Sales

    < 500 AND Sales > 275); Syntax:
  46. Store_Name Los Angeles Result:

  47. Inner Join

  48. INNER JOIN - returns all rows from multiple tables where

    the join condition is met.
  49. SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON

    Orders.CustomerID=Customers.CustomerID; Syntax:
  50. Result:

  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
  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
  53. WHY KNOW SQL IF I KNOW RAILS?

  54. The Rails ORM is an attempt at an abstraction

  55. The SQL queries that the Rails ORM generates for you

    may need to be fine-tuned.
  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