From 0 To 1:SQL And Databases – Heavy Lifting

From 0 To 1:SQL And Databases – Heavy Lifting

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 14h 24m | 4.53 GB

Your bodyguard for when data gets too big, this course is strong but friendly, funny yet deep, animated yet thoughtful.

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

No prerequisites are needed for the SQL commands and DBMS fundamentals. Basic knowledge of programming in Python would be helpful if you want to run the source code in the course-ending project. This course will cover generic (non-system-specific) SQL, but will also conduct exercises using 2 different database technologies: MySQL and SQLite. Installation and use of both these will be explained in-depth

What You Will Learn

  • Explore large datasets and uncover insights – going far beyond the Excel, deep into the data
  • Model and create a database for day-to-day use
  • Interface with databases from a programming language such as Python
  • Have the comfort and confidence needed to load data and use both GUI and a command line interface for database operations
  • Fully understand and leverage joins, sub queries, aggregates, indices, triggers, stored procedures and other major database concepts
Table of Contents

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