Oreilly - From 0 To 1:SQL And Databases

Oreilly - From 0 To 1:SQL And Databases - Heavy Lifting

by Loonycorn | Released January 2018 | ISBN: 9781789134285

https://www.oreilly.com/library/view/from-0-to/9781789134285/

Your bodyguard for when data gets too big, this course is strong but friendly, funny yet deep, animated yet thoughtful. About This VideoSQL In Great DepthDatabase Fundamentals and Just Enough TheoryPractical Examples - Queries in MySQL and SQLite, and code in PythonIn DetailYour bodyguard for when data gets too big, this course is strong but friendly, funny yet deep, animated yet thoughtful. Let's parse that. Your bodyguard for when data gets too big: Most business folks (and quite a few engineers) use Excel as a basic tool of decision making and modeling, but when you can't fit the data you'd like into an Excel spreadsheet that you can easily open, its time to move to a database. The course is strong but friendly: This course will help you move to a database without being intimidated by the new environment. Don't let anyone tell you that any dataset is too large or too complicated for you to understand. The course is funny yet deep: It goes really deep into the topics that folks often find hard to understand, such as joins, aggregate operators and interfacing with databases from a programming language. But it never takes itself too seriously. The course is very visual: most of the techniques are explained with the help of animations to help you understand better. This course is practical as well: Queries are explained in excruciating detail, indices are demystified, and potentially career-limiting traps (Drop, Alter) are marked with bright yellow tape markers so you can steer clear. The course is also quirky. The examples are irreverent. Lots of little touches: repetition, zooming out so we remember the big picture, active learning with plenty of quizzes. There's also a peppy soundtrack, and art - all shown by studies to improve cognition and recall. Show and hide more

Chapter 1 : Introduction To Databases
- Data Is A Big Deal 00:18:21
- Why Do We Need A Database? 00:19:57
- MySQL - Installed and Introduced (Mac OS X) 00:07:04
- Setting up MySQL and the Workbench (Mac OS X) 00:17:32
- MySQL Server and Workbench installed (Windows) 00:06:32
- Entities And Attributes - Things And Stuff Which Describe Them 00:15:15
- dentifying Entities Using Keys 00:13:41
- The Entity Relationship (E-R) Model - Entities And Attributes 00:05:43
- Relationships - What Connects Entities 00:05:50
- Cardinality Of Relationships 00:09:11
- The Entity Relationship (E-R) Model - Relationships 00:09:51
- Mapping E-R Theory to the world of databases 00:13:27
Chapter 2 : Going From Relations To Database Tables
- Introducing The SELECT Statement 00:19:53
- Introducing The SELECT Statement 00:11:13
- Quotes and NULLS - Avoid The Gotchas 00:13:53
- Simple Examples 00:14:49
- More Simple Examples 00:13:21
- Using the Like Operator 00:08:10
- Between, In and Not In 00:18:13
- A Multi-Column Select 00:10:27
- Working with Dates (MySQL As Example) 00:11:24
Chapter 3 : Creating Tables And Inserting Data Into Them
- Creating Database, Use Database and Create Table 00:14:07
- Column Subtleties: Null, Primary Key and Auto-Increment 00:14:42
- An Example - Sales Data 00:12:37
- Insert Table - Examples 00:19:25
- More Insert Table Examples 00:09:14
- Referential Integrity Introduced 00:19:54
- Creating a Database and Using It 00:17:25
- An Involved Bulk Loading Example 00:14:14
Chapter 4 : Back To Retrieving Data - More Advanced Queries Using Group By
- Aggregation Operators Introduced 00:18:16
- The Group By Clause 00:17:20
- More Group By Examples 00:19:47
- Order By 00:16:15
- Having 00:19:52
- Limit 00:07:12
- Count and Count Distinct 00:08:20
Chapter 5 : Joins - Don't Let Them Intimidate You! They're All Fuzzy And Nice
- Introduction To SQL Joins 00:09:54
- Cross Joins aka Cartesian Joins 00:17:03
- Inner Joins 00:19:53
- Left Outer Joins 00:15:31
- Right, Full Outer Joins, Natural Joins, Self Joins 00:16:08
Chapter 6 : Subqueries: Layering The Cake
- Subqueries Introduced 00:07:07
- Union, Union All, Intersect and Except. 00:19:32
- Query-In-A-Query 00:13:49
- Inserting Via Subqueries 00:09:43
- Use A Subquery To Populate A Table 00:06:50
- Outer Join And Inner Join: A Little Test 00:08:59
Chapter 7 : Constraints
- Not NULL constraints 00:10:08
- Primary Keys 00:09:43
- Foreign Key Constraints 00:13:27
- Deletes and Updates With Foreign Key Constraints 00:12:05
- Check Constraints 00:01:39
Chapter 8 : Somewhat More Advanced Concepts
- Indices 00:16:47
- Stored Procedures 00:10:27
- Triggers 00:11:38
- Transactions 00:13:07
- Handle with Care: Update and Delete 00:13:27
- Handle with Care: Alter and Drop 00:12:37
- Views, Temporary Tables, and User Priviliges 00:05:36
Chapter 9 : Table Design: Theory And Practice
- Practical Tips for Table Designs that you won't regret! 00:11:58
- More Practical Design Tips 00:10:44
- Normal Forms - Friends Once You Know Them 00:10:28
Chapter 10 : Putting It Together: Database Programming In Python
- Interfacing with Databases from Python 00:06:46
- SQLite works right out of the box 00:06:27
- Build a database of Stock Movements - I 00:15:02
- Build a database of Stock Movements - II 00:13:48
- Build a database of Stock Movements - III 00:13:23
Chapter 11 : Appendix
- [For Linux/Mac OS Shell Newbies] Path and other Environment Variables 00:08:26