SQL Basics - RRUG 1/6/2016

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