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.
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 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!
Life - and business - is becoming more and more data-driven, and data-intensive.
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
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 MySQL workbench can be a little daunting - never fear! We'll walk through it. (The windows version)
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.
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.
We dig deeper into the world of entities and relationships.
Entities could be modeled even with flat files, but relationships can only be modelled in a database.
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
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.
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.
Remember that columns in a database have types - these types govern how those columns can be used
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!
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
Examples of creating a table and inserting data
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.
SUM, MAX, MIN, COUNT and AVG are aggregate operators - by definition they operate over a group of rows, rather than a single row
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
Let's keep going with the GROUP-BY, and understand how it divvies up the data in a database
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
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
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
The full power of databases emerges when we link tables - and Joins are the way to accomplish this
Cross Joins are conceptually simple, which is great, because they are the underpinning of Inner Joins
Inner Joins are your best friend. Understand them for what they are: cross joins with a filter condition.
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.
Once we've understand Inner and Outer joins, Natural Joins are easy-peasey
Queries inside queries are really useful to modularize and simplify your queries
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.
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!
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.
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-)
Loonycorn is us, Janani Ravi and Vitthal Srinivasan. Between us, we have studied at Stanford, been admitted to IIM Ahmedabad and have spent years 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
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 :-)