SQL Workshop Part 1

This is Part 1 of the SQL workshop series I am hosting for people to empower them getting the most out of the data they are storing.

Transcript

1. SQL Workshop 1
2. 1 Introduction 2 Tools 3 Hands-On
3. 1 Introduction Structured Query Language
4. 1 Introduction Relational Database
5. 1 Introduction rows columns table e.g. “retailers” table e.g. “retailer_products”
6. 1 Introduction Sequel is your language to read data from a relational database.
7. 1 Introduction SELECT * FROM retailers
8. 1 Introduction SELECT * FROM retailers
9. aka fancy graphs 2 Tools
10. aka fast forward 30 years 2 Tools
11. You need some query editor to play around. 2 Tools
12. database connection 2 Tools
13. 2 Tools your laptop database THE Internet
14. 2 Tools
15. You need a visualisation interface. 2 Tools https://dashboard.hsnb.co Metabase
16. 2 Tools
17. 3 Hands-On SELECT * FROM retailers Selecting columns from a table
18. 3 Hands-On SELECT id, name, website FROM retailers Selecting columns from a table
19. 3 Hands-On SELECT * FROM retailers
 WHERE created_at >= NOW() - INTERVAL '1 week' Selecting specific rows
20. 3 Hands-On WHERE condition
 AND condition
 OR condition WHERE clause
21. 3 Hands-On name = 'Ferry-Olson'
 website IS NULL
 created_at >= CAST('2017-05-01' AS date)
 
 column operator column or value conditions
22. 3 Hands-On WHERE (A OR B) AND (X OR Y) Parentheses control condition precedence
23. 3 Hands-On SELECT COUNT(*) FROM products Aggregating data from a table
24. 3 Hands-On SELECT AVG(sales_price) / 100 AS average_price_eur
 FROM retailer_products Aggregating data from a table
25. 3 Hands-On …but CHARTS?
26. 3 Hands-On From table to visualisation day count 1 345 2 300 3 399 0 100 200 300 400 day 1 day 2 day 3
27. 3 Hands-On SELECT CAST(created_at AS date) AS day, COUNT(*)
 FROM products
 GROUP BY CAST(created_at AS date) Slicing data aggregates
28. 3 Hands-On SELECT '2017-05-24 12:00', CAST('2017-05-24 12:00' AS date) Playing around with date types
29. 3 Hands-On SELECT '2017-05-24 12:00', DATE '2017-05-24 12:00' Playing around with date types
30. 3 Hands-On SELECT '2017-05-24 12:00', CAST('2017-05-24 12:00' AS timestamptz) Playing around with date types
31. 3 Hands-On SELECT '2017-05-24 12:00', TIMESTAMPTZ '2017-05-24 12:00' Playing around with date types
32. 3 Hands-On SELECT date_trunc('month', DATE '2017-05-24') Truncating date types
33. 3 Hands-On SELECT date_trunc('hour', TIMESTAMP '2017-05-24 13:47') Truncating date types
34. 3 Hands-On SELECT CAST(created_at AS date) AS day, COUNT(*)
 FROM products
 GROUP BY date_trunc('hour', created_at) Slicing aggregates by specific time ranges
35. 3 Hands-On Setting up a Question in Metabase