Master SQL for Data Analysis

1. Introduction

This brief introductory video outlines the course content that you will learn from this course and the importance of learning SQL.

1. Welcome to Level 1!

In this video, the author emphasizes the need to learn SQL and what you will learn from the first half (level 1) of this course.

2. Our Learning Objectives

This video looks at the learning objectives from this course and the general training roadmap.

3. DB Installation

This video demonstrates how to install PostgreSQL, the most advanced RDBMS that we have currently.

1. Overview - Database, DBMS, SQL

In this video, the author discusses the key terms used when talking about SQL and relational databases.

2. Tables, Columns, Rows

In this lesson, we will explore tables in a database, and the two-dimensional rows and columns of a table.

3. Primary and Foreign Keys

In this video, we will look at a primary key used to identify a row in a table and a foreign key used to navigate from one table to another.

4. Relational Model, ER Diagram

Here, we will explore the three main building blocks of a relational database: entity, attribute, and relationship.

5. Schema, Metadata, Data Dictionary

Let's learn how to create multiple relational database instances and define the needed table for each entity type.

6. Null Values

In this video, you will learn what a null value is, how it affects the functioning of a table, and how to be careful when analyzing null values.

7. Indexes

Let's learn more about indexes, which will help optimize queries. You will learn how to create an index and structure data using indexes.

8. Partitions

In this lesson, we will explore partitions, a popular strategy to handle growing amounts of data, and break tables into smaller parts called partitions.

1. Overview

This video outlines creating entire databases and building schemas and tables in detail.

2. Creating - Database and Schema

This lesson will teach us how to create a database and schema using the PostgreSQL software.

3. Creating Tables - Introduction

Let's explore how to organize data better by building tables in a database.

4. Creating Tables - Constraints

Here, we will look at the different constraints that can be added to update or limit data stored in a specific column in a table.

5. Inserting Data

In this lesson, we will look at inserting data into tables and learn to use commands to insert single and multiple rows of data.

6. Update and Delete

Here, you will learn to update a table's fields and delete erroneous or redundant information.

1. Overview

This video provides a brief overview of the contents explored in this section about retrieving data with queries (SQL).

2. Query Data (SELECT)

Let's learn to query data using the SELECT option that determines the columns to include in the result set.

3. Filtering Conditions (WHERE) - Part 1

In this lesson, you will learn to use filtering conditions to filter data based on conditions. Here, we will explore the WHERE condition.

4. Filtering Conditions (WHERE) - Part 2

Here, you will learn to combine and filter data based on multiple conditions in the same query using operators.

5. Alias for Tables and Columns

Let's learn how to create labels for columns generated by expressions or built-in functions to make column names more visible.

6. Searching Patterns (Wildcards)

In this video, you will learn about wildcards or search patterns that allow us to search a portion of the information from a string-type column.

7. Distinct Values (DISTINCT)

Let's explore the way to find distinct values among multiple rows of data as part of a preliminary preparation process.

8. Sorting Rows (ORDER BY)

In this lesson, you will learn about the options for sorting the output result of a query in some specific order to save time and keep data better organized.

9. Grouping Rows (GROUP BY and HAVING)

Let's learn how to group rows based on values in specific columns and then apply different actions on the groups, such as applying filters using GROUP BY and HAVING.

10. Data-Type Conversions (CAST)

Here, we will understand predefined datatypes and how to convert one data type to another.

11. Database Dictionary

We will explore the data-storing area called the dictionary, which stores constraints, filters, functions, lists, and so on.

1. Let's Recap!

The author briefly recapitulates the topics learned in this level during this training program, level 1.

1. Introduction

In this video, the author briefly outlines data analysis and then explains, in brief, the advanced concepts to be covered in the second half (level 2) of this course.

1. Welcome to Level 2!

This is a brief introductory video to the level-2 segment and briefly outlines the concepts we will learn in this part of the course. Here, we will understand the learning objectives, and acquire the datasets required for the level 2 segment of the course learning.

2. Our Learning Objectives

In this video, the author discusses the more complex SQL learning objectives of the level 2 segment and what advanced SQL concepts we will be covering.

3. Loading the Books Datasets

This video discusses the PostgreSQL database management system and loading the required datasets for training purposes.

4. Loading the eCommerce Datasets

In this video, we will look at the different store datasets of eCommerce that we will use in the eCommerce training project.

1. Overview

This video provides an overview of the combination of data from multiple datasets from multiple tables using operators.

2. Union, Intersect, Except

In this video, you will learn more about the UNION, INTERSECT, JOIN, and EXCEPT operators while combining datasets and tables.

3. Cross-Join

In this lesson, you will learn about combining datasets and tables with very little or no common factors among them.

4. Inner-Join

In this lecture, we will look at the most common types of joins, called the inner-join type, for various use cases.

5. Outer-Join

In this lesson, we will look at a situation where there are tables with unmatched rows from one table to another.

1. Overview

In this video, we will talk about a powerful tool, the subquery, which extracts much more value and valuable insights by combining multiple tables and querying with subqueries.

2. What Is Subquery?

This video explains in detail what a subquery is all about, the types of subqueries, and how to create a subquery to extract more value from data.

3. Conditional Logic - Single Row

Here, we will look at the first type of subquery used to filter data with conditional logic, the results of which can be a single row or multiple rows of subqueries, depending on how we build it.

4. Conditional Logic - Multiple Rows

The second type of subquery used to filter data based on conditional logic is the subquery that may return multiple rows of data.

5. As Inline Views (FROM)

After learning how to use a subquery to filter rows, we will now look at using forms in the form section, such as regular tables, which are useful in simplifying complex queries.

1. Overview

This video briefly outlines conditional logic with a case, how data gets transformed with this logic, and classification and binning.

2. Simple CASE - Data Transformation

In this video, we will look at the first option to create a use case, which is the simple case used to compare an input expression using the = operator.

3. Searched CASE - Classification and Binning

Here, we will explore the second type of case statement, the searched case expression, which is more flexible than the previous simple case structure.

1. Overview

This video briefly outlines the window functions, a powerful tool used to work on the most complex structures in SQL and data analysis in complex projects.

2. Creating a Window Function

This is an illustrative video on creating a simple window function using the OVER and PARTITION BY functions.

3. Sequential Numbers

Let's look at another typical use case of a window function: to create a sequential integer number inside a group of rows while deciding how this group will be ordered.

4. Ranking

After learning how to divide the table into partitions, order rows inside a partition based on logic, and then add a sequential number to each row; you will learn to rank rows that share two or more same values.

1. Overview

This video provides a quick overview of simplifying our queries to obtain more value out of the data.

2. Virtual Tables (Views)

In this lesson, we will look at creating virtual tables to look at tables in a different way than their existing database structures.

3. Common Table Expressions (CTEs)

In this video, you will learn more about the common table expression, which allows us to reference data within a SELECT statement.

1. Let's Recap

In this final video, the author succinctly recapitulates the course content with explanations of key terminologies and topics covered thus far.