SQL Basics - RRUG 1/6/2016

Overview of SQL and basic syntax.

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.
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.
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