Data Is A Big Deal Why Do We Need A Database? MySQL - Installed and Introduced (Mac OS X) Setting up MySQL and the Workbench (Mac OS X) MySQL Server and Workbench installed (Windows) Entities And Attributes - Things And Stuff Which Describe Them Identifying Entities Using Keys The Entity Relationship (E-R) Model - Entities And Attributes Relationships - What Connects Entities Cardinality Of Relationships The Entity Relationship (E-R) Model - Relationships Mapping E-R Theory to the world of databases Downloads
Introducing The SELECT Statement Columns Have Data Types Quotes and NULLS - Avoid The Gotchas Simple Examples More Simple Examples Using the Like Operator Between, In and Not In A Multi-Column Select Working with Dates (MySQL As Example) Downloads
Creating Database, Use Database and Create Table Column Subtleties: Null, Primary Key and Auto-Increment An Example - Sales Data Insert Table - Examples More Insert Table Examples Referential Integrity Introduced Creating a Database and Using It Creating a Database and Using It Downloads
Aggregation Operators Introduced The Group By Clause More Group By Examples Order By Having Limit Count and Count Distinct Downloads
Introduction To SQL Joins Cross Joins aka Cartesian Joins Inner Joins Left Outer Joins Right, Full Outer Joins, Natural Joins, Self Joins Downloads
Subqueries Introduced Union, Union All, Intersect and Except Query-In-A-Query Inserting Via Subqueries Use A Subquery To Populate A Table Outer Join And Inner Join: A Little Test Downloads
Not NULL constraints Primary Keys Foreign Key Constraints Deletes and Updates With Foreign Key Constraints Check Constraints Downloads
Indices Stored Procedures Triggers Transactions Handle with Care: Update and Delete Handle with Care: Alter and Drop Views, Temporary Tables, and User Privileges Downloads
Practical Tips for Table Designs that you won't regret! More Practical Design Tips Normal Forms - Friends Once You Know Them Downloads
Interfacing with Databases from Python SQLite works right out of the box Build a database of Stock Movements - I Build a database of Stock Movements - II Build a database of Stock Movements - III Downloads
[For Linux/Mac OS Shell Newbies] Path and other Environment Variables
What will I 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, subqueries, aggregates, indices, triggers, stored procedures and other major database concepts
About the course
This Course is taught by Stanford-educated, ex-Googlers. This team has decades of practical experience in quant trading, analytics and e-commerce.
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, it’s 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.
- SQL In Great Depth
- Database Fundamentals and Just Enough Theory
- Practical Examples - Queries in MySQL and SQLite, and code in Python
Who should take the course?
- Yep! Data analysts who would like to really get down and dirty with the data
- Yep! Business folks and executives looking to make their decision making more data-driven and seeking the technical knowledge to do so.
- Yep! Students of Computer Science and Computer Engineering looking to understand database concepts for the first time
- Yep! Software engineers who need to understand and interface with databases from programming languages in their work
Pre-requisites & Requirements
- 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