From 0 To 1:SQL And Databases - Heavy Lifting
4.7 (84 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,842 students enrolled
Wishlisted Wishlist

Please confirm that you want to add From 0 To 1:SQL And Databases - Heavy Lifting to your Wishlist.

Add to Wishlist

From 0 To 1:SQL And Databases - Heavy Lifting

Your bodyguard for when data gets too big​, this course is strong but friendly, funny yet deep, animated yet thoughtful.
4.7 (84 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,842 students enrolled
Created by Loony Corn
Last updated 3/2017
English
Current price: $10 Original price: $50 Discount: 80% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 14.5 hours on-demand video
  • 136 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
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
View Curriculum
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
Description

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.

Taught by a Stanford-educated, ex-Googler and an IIT, IIM - educated ex-Flipkart lead analyst. 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, 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.

What's Covered:

SQL In Great Depth

Database Fundamentals and Just Enough Theory

Practical Examples - Queries in MySQL and SQLite, and code in Python


Using discussion forums

Please use the discussion forums on this course to engage with other students and to help each other out. Unfortunately, much as we would like to, it is not possible for us at Loonycorn to respond to individual questions from students:-(

We're super small and self-funded with only 2-3 people developing technical video content. Our mission is to make high-quality courses available at super low prices.

The only way to keep our prices this low is to *NOT offer additional technical support over email or in-person*. The truth is, direct support is hugely expensive and just does not scale.

We understand that this is not ideal and that a lot of students might benefit from this additional support. Hiring resources for additional support would make our offering much more expensive, thus defeating our original purpose.

It is a hard trade-off.

Thank you for your patience and understanding!

Who is the target audience?
  • 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
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 68 Lectures Collapse All 68 Lectures 14:24:12
+
Introduction To Databases
12 Lectures 02:22:17

Life - and business - is becoming more and more data-driven, and data-intensive.

Preview 18:20

As the scale of your data grows, file systems (the most famous of which is - Excel!) struggle to keep up. Databases are carefully engineered to do the heavy lifting

Preview 19:56

MySQL is an open-source RDBMS, the most popular in the world by some measures. Acquired by Oracle, it still has a very powerful free Community Edition
MySQL - Installed and Introduced (Mac OS X)
07:03

Setting up MySQL and the MySQL workbench can be a little daunting - never fear! We'll walk through it. (The Mac OS X version)

Setting up MySQL and the Workbench (Mac OS X)
17:32

Setting up MySQL and the MySQL workbench can be a little daunting - never fear! We'll walk through it. (The windows version)

MySQL Server and Workbench installed (Windows)
06:31

Databases are like all computer systems - garbage in, garbage out. To make sure that what goes in makes sense, we need to model real-world entities and the relationships between them.

Preview 15:14

What's a key? It is a set of defining attributes. Once you have the key, you have captured the essence of an entity, as it were.

Identifying Entities Using Keys
13:41

We dig deeper into the world of entities and relationships.

The Entity Relationship (E-R) Model - Entities And Attributes
05:43

Entities could be modeled even with flat files, but relationships can only be modelled in a database.

Relationships - What Connects Entities
05:51

One-to-one, one-to-many or many-to-many? The nature of the relationships between entities determines how the corresponding data will be represented in a database

Cardinality Of Relationships
09:10

We are almost ready to make the leap from modeling data to setting up a database. But first, let's delve a bit deeper into modelling relationships.

The Entity Relationship (E-R) Model - Relationships
09:50

All of that E-R model stuff we just learnt is really useful! Let's put it to work immediately, by figuring out how we can translate E-R models into database tables.

Mapping E-R Theory to the world of databases
13:26
+
Going From Relations To Database Tables
9 Lectures 02:01:18
Select-From-Where will get you anywhere!
Preview 19:52

Remember that columns in a database have types - these types govern how those columns can be used

Columns Have Data Types
11:13

NULL is a special value - it implies that a value does not exist. Null is not TRUE or FALSE, its just NULL. Blank strings and zeroes are not null either
Quotes and NULLS - Avoid The Gotchas
13:52

Let's keep going - Select-From-Where will get you anywhere:-)
Simple Examples
14:49

More Simple Examples
13:21

LIKE and the % operator can make string matching so much easier
Using the Like Operator
08:09

Helpful operators - Between, In and Not In will simplify your queries (and your life!)
Between, In and Not In
18:12

Remember that you can select any number of columns from any number of tables
A Multi-Column Select
10:27

Dates can be tricky because date handling is so different across database systems. Let's take MySQL as an example, and run through some of the common operations we'd perform with and on dates. Keep in mind that the syntax would be very different for a different DBMS though!

Working with Dates (MySQL As Example)
11:23
+
Creating Tables And Inserting Data Into Them
8 Lectures 02:01:34
The circle of life of data begins with - creating a database, and creating an empty table within it
Preview 14:06

Let's understand how a table can be created. In particular, NULLs, primary keys and auto-increment columns are commonly used, and really handy, so let's make sure we understand them

Column Subtleties: Null, Primary Key and Auto-Increment
14:42

Examples of creating a table and inserting data
An Example - Sales Data
12:36

Examples of creating a table and inserting data

Insert Table - Examples
19:24

Examples of creating a table and inserting data
More Insert Table Examples
09:13

Referential Integrity (aka Foreign Key Constraints) are a really important concept in DBMS.
Referential Integrity Introduced
19:54

Let's get our feet wet - create a database, use that database, create a simple table, and bulk load a file into that table
Creating a Database and Using It
17:25

That first table was a bit simplistic - no constraints. Now let's do a more involved example, and harness the full power of the Bulk Uploader.

An Involved Bulk Loading Example
14:14
+
Back To Retrieving Data - More Advanced Queries Using Group By
7 Lectures 01:46:58

SUM, MAX, MIN, COUNT and AVG are aggregate operators - by definition they operate over a group of rows, rather than a single row

Preview 18:15

We discussed how aggregation operators need a range of queries to function on. What can that range be? It could be the entire table, but even more likely its some group of the rows in a table, defined by the GROUP-BY operator

The Group By Clause
17:19

Let's keep going with the GROUP-BY, and understand how it divvies up the data in a database

More Group By Examples
19:46

We can order the results of a query by one or more columns using the ORDER BY. Remember that relations are technically bags (i.e. multisets) which do not possess order - but this is a convenience taht DBMS make available

Order By
16:15

Having is an operator that filters out groups based on a condition. Its like the WHERE clause but it operates on groups rather than individual rows

Having
19:52

Use LIMIT to return only a specific number of rows from a query. Use this to peek into a large table without retrieving a gazillion rows

Limit
07:12

Count and count distinct are handy to find the number of rows, and the number of unique rows in a query result
Count and Count Distinct
08:19
+
Joins - Don't Let Them Intimidate You! They're All Fuzzy And Nice
5 Lectures 01:18:27

The full power of databases emerges when we link tables - and Joins are the way to accomplish this

Preview 09:54

Cross Joins are conceptually simple, which is great, because they are the underpinning of Inner Joins

Cross Joins aka Cartesian Joins
17:02

Inner Joins are your best friend. Understand them for what they are: cross joins with a filter condition.

Inner Joins
19:52

Outer Joins are really useful if used right. They are a little tricky though - understand how they work, and why you should not be surprised to see NULLs in the result of an outer join.

Left Outer Joins
15:31

Once we've understand Inner and Outer joins, Natural Joins are easy-peasey

Right, Full Outer Joins, Natural Joins, Self Joins
16:08
+
Subqueries: Layering The Cake
6 Lectures 01:06:01
What's a subquery? Its a query inside another. Outer and inner queries used together are very powerful.
Preview 07:06

The Set operations are easily extended to SQL, just remember that a relation is a bag, not a set (what's the difference? bags can contain duplicates, sets can't!)
Union, Union All, Intersect and Except.
19:32

Queries inside queries are really useful to modularize and simplify your queries

Query-In-A-Query
13:53

Inserting rows into a table 1 row at a time is painfully slow - never fear! You can run a query and directly pipe its output into a table
Inserting Via Subqueries
09:42

Let's create a new table, this time using the Inner Join operator to seed it.
Use A Subquery To Populate A Table
06:49

Let's create a table twice - once using Inner Join, and once using Outer Join. Guess what the difference is?
Outer Join And Inner Join: A Little Test
08:59
+
Constraints
5 Lectures 47:01
Any column can hold a NULL value, unless you specify a NOT NULL constraint. If you do, also use a default value if possible
Not NULL constraints
10:07

An Index is a quick way to query specific columns of a database. Indices make lookup very fast, but they slow down updates and deletes, so be sure to really understand them.

Primary keys are always indexed for fast lookup.

Primary Keys
09:43

If for some reason you are unable to assign a primary key, at least have a foreign key
Foreign Key Constraints
13:27

Updates and Deletes have a complicated interplay with the Foreign Key constraint. Understand on-cascade-delete and its cousins
Deletes and Updates With Foreign Key Constraints
12:05

Check constraints are simple, and incredibly useful - and somehow underused. Use them!
Check Constraints
01:39
+
Somewhat More Advanced Concepts
7 Lectures 01:23:37
Always try to create tables with a primary key
Indices
16:47

Stored Procedures are to SQL what functions are to code. Learn how to define and call stored procedures
Stored Procedures
10:26

Triggers are an advanced DBMS feature - conceptually really cool, also complicated!
Triggers
11:38

A transaction is a logical unit of work - the DBMS will ensure that each transaction satisfies 4 ACID properties: Atomicity, Consistency, Isolation and Durablility
Transactions
13:07

The circle of life of data in a database does not stop with queries: Everything must change, including data
Handle with Care: Update and Delete
13:26

Be really careful when you use the Alter and Drop commands. Incredibly powerful, and very simple - sometimes too simple, because that makes them so easy to use!
Handle with Care: Alter and Drop
12:37

Views can be thought of as virtual tables. Temporary tables are exactly what their name would suggest. Use them often!
Views, Temporary Tables, and User Priviliges
05:36
+
Table Design: Theory And Practice
3 Lectures 33:10

Designing good relational schemas starts off by figuring out the real world problem you want to map. Design each of your tables well, consider each column and what constraints it should and should not have. Remember choosing a primary key well is super important!

Preview 11:58

You know what you want to model, how do you figure how many tables you choose to store this information? Here are a few rules of thumb.

More Practical Design Tips
10:44

Normal forms are very inaccessible when you read them in theory, however they are great rules to get well-designed databased. Let's see what they mean in plain English-)

Normal Forms - Friends Once You Know Them
10:28
+
Putting It Together: Database Programming In Python
5 Lectures 55:24
How do programming languages interface with databases? Also, a step by step guide to building your own database of stock price movements over the last 2 years.
Preview 06:46

SQLite is available out of the box with Python, and is a handy and quick way to start working with databases with no setup or installation.
SQLite works right out of the box
06:27

Code along as we build a database of stock movements. We'll download and unzip files with stock movements from the NSE website, insert the data into a database. We'll accept a ticker from a user and generate an excel sheet with a chart of its price movements for the last year.
Build a database of Stock Movements - I
15:01

Build a database of Stock Movements - II
13:48

Code along as we build a database of stock movements. We'll download and unzip files with stock movements from the NSE website, insert the data into a database. We'll accept a ticker from a user and generate an excel sheet with a chart of its price movements for the last year.
Build a database of Stock Movements - III
13:22
1 More Section
About the Instructor
Loony Corn
4.3 Average rating
3,558 Reviews
28,002 Students
65 Courses
A 4-person team;ex-Google; Stanford, IIM Ahmedabad, IIT

Loonycorn is us, Janani Ravi, Vitthal Srinivasan, Swetha Kolalapudi and Navdeep Singh. Between the four of us, we have studied at Stanford, IIM Ahmedabad, the IITs and have spent years (decades, actually) working in tech, in the Bay Area, New York, Singapore and Bangalore.

Janani: 7 years at Google (New York, Singapore); Studied at Stanford; also worked at Flipkart and Microsoft

Vitthal: Also Google (Singapore) and studied at Stanford; Flipkart, Credit Suisse and INSEAD too

Swetha: Early Flipkart employee, IIM Ahmedabad and IIT Madras alum

Navdeep: longtime Flipkart employee too, and IIT Guwahati alum

We think we might have hit upon a neat way of teaching complicated tech courses in a funny, practical, engaging way, which is why we are so excited to be here on Udemy!

We hope you will try our offerings, and think you'll like them :-)