SQL And Databases - Heavy Lifting : From 0 To 1

About Course

• Prerequisites: 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.

  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: 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 (and people will try, most likely)

  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.

Topics covered in this course

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

What you get from this course?

• • 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, subqueries, aggregates, indices, triggers, stored procedures and other major database concepts
  • SQL In Great Depth
  • Database Fundamentals and Just Enough Theory
  • Practical Examples - Queries in MySQL and SQLite, and code in Python

Who should buy this course?

• • Data analysts who would like to really get down and dirty with the data
  • Business folks and executives looking to make their decision making more data-driven, and seeking the technical knowledge to do so.
  • Students of Computer Science and Computer Engineering looking to understand database concepts for the first time
  • Software engineers who need to understand and interface with databases from programming languages in their work

  What are the requirements?

  • 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