Complete Sql For Data Analytics And Business Intelligence

Master SQL from Basics to Advanced with Visualized Concepts, Real-World Case Studies and Interview Qs from Top Companies

What you'll learn

Learn SQL from basics to advanced, focusing on data analytics and business intelligence.

Understand concepts with intuitive visualizations.

Access interview material inspired by real questions from top tech companies.

Work on case studies using e-commerce and retail sales data.

Learn advanced analytical techniques like window functions, data cleaning and Exploratory Data Analysis (EDA)

Learn how to handle complex data types like Date, Time, Array, and JSON data types effectively in SQL queries.

Explore Time Series Analysis and patterns with public datasets in a dedicated module.

Lecture notes for quick revision and interviews.

Strengthen your knowledge with a variety of exercises, quizzes, and challenges.

Get a Data Analyst roadmap and actionable career advice to help you land your dream role.

Requirements

A Mac, PC, or Linux-based Computer.

No prior technical/programming background required.

Description

Unlock the power of SQL with this Complete SQL for Data Analytics and Business Intelligence course! Designed for learners of all levels, this course takes you from SQL fundamentals to advanced analytical techniques. Through engaging visual explanations, real-world case studies, and hands-on practice, you'll master essential skills like SQL Joins, Subqueries, Grouping, Aggregation, handling complex data types, Time Series Analysis, Exploratory Data Analysis (EDA), window functions and more. Prepare for your dream job with interview-focused material inspired by top tech companies and benefit from lecture notes for quick revision. With quizzes, exercises, and a clear Data Analyst roadmap, this course equips you with the tools and confidence to excel in data-driven roles.Key Highlights of the CourseComprehensive SQL Learning: Master SQL from the basics to advanced topics, tailored for data analytics and business intelligence.Visual Learning: Grasp concepts easily with detailed explanations supported by intuitive visualizations.Interview Preparation: Gain access to specially curated material inspired by real interview questions from top tech companies like Tesla, Microsoft, Amazon, Uber, TikTok and more.Real-World Applications: Work on 2 case studies using e-commerce and retail sales datasets to apply your skills to practical scenarios.Advanced Analytical Techniques: Dive deep into advanced SQL concepts, including window functions and other analytical concepts like data cleaning and Exploratory Data Analysis (EDA).Complex Data Types: Learn how to handle Date, Time, Array, and JSON data types effectively in SQL queries.Time Series Analysis: Explore time-based trends and patterns with public datasets in a dedicated module.Comprehensive Study Materials: Includes lecture notes for quick revision and focused interview preparation.Interactive Learning: Strengthen your knowledge with a variety of exercises, quizzes, and challenges.Career Guidance: Get a Data Analyst/Data Scientist roadmap and actionable career advice to help you land your dream role.

Overview

Section 1: Introduction to Data Analytics

Lecture 1 Data Roles and Responsibilities

Section 2: Setting up Local Environment

Lecture 2 Section Introduction

Lecture 3 Database Servers and Clients

Lecture 4 Local Set up - Mac

Lecture 5 Local Set up - Windows

Lecture 6 Getting Familiar with pgAdmin

Lecture 7 Lecture Notes

Section 3: Database Terminologies

Lecture 8 Database and Schema

Lecture 9 Tables

Lecture 10 Multiple databases and schemas

Lecture 11 Creating a Database

Lecture 12 Lecture Notes

Lecture 13 Interview Questions

Section 4: Creating Table and Inserting Data into the Table

Lecture 14 Section Introduction

Lecture 15 Numeric Data Types

Lecture 16 Character Data Types

Lecture 17 Date and Time Data Types

Lecture 18 Boolean Data Type

Lecture 19 Other Important Data Types

Lecture 20 Creating a Table

Lecture 21 Inserting Data into Table

Lecture 22 Lecture Notes

Section 5: Reading Data From Table

Lecture 23 Section Introduction

Lecture 24 The SELECT statement

Lecture 25 Common String Operators and Functions

Lecture 26 Using Comments in SQL

Lecture 27 Lecture Notes

Section 6: Filtering Records

Lecture 28 Section Introduction

Lecture 29 The WHERE Clause

Lecture 30 Comparison operators in WHERE Clause

Lecture 31 Using WHERE with a List of Values

Lecture 32 Using WHERE within a specified range

Lecture 33 Using WHERE with pattern matching

Lecture 34 Compound WHERE Clause

Lecture 35 Lecture Notes

Section 7: Updating and Deleting Records

Lecture 36 Section Introduction

Lecture 37 Updating Records in a Table

Lecture 38 Deleting Records from a Table

Lecture 39 Dropping a Table from Database

Lecture 40 Lecture Notes

Section 8: Table Relationships and Constraints

Lecture 41 Section Introduction

Lecture 42 One-to-Many and Many-to-One Relationships

Lecture 43 One-to-One and Many-to-Many Relationships

Lecture 44 Primary Keys

Lecture 45 Foreign Keys

Lecture 46 Foreign Key Constraint on Delete

Lecture 47 Foreign Key Constraint on Insert

Lecture 48 Other Column Constraints

Lecture 49 Getting Table Schema Information

Lecture 50 Lecture Notes

Section 9: Joining Tables (SQL Joins)

Lecture 51 Section Introduction

Lecture 52 Preparing Dataset

Lecture 53 Why do we need Joins?

Lecture 54 Inner Join

Lecture 55 Left Outer Join

Lecture 56 Right Outer Join

Lecture 57 Full Outer Join

Lecture 58 Exercise 1

Lecture 59 Table and Column Alias

Lecture 60 Exercise 2

Lecture 61 Exercise 3

Lecture 62 Exercise 4

Lecture 63 Join with Filter

Lecture 64 Lecture Notes

Section 10: Grouping and Aggregating Records

Lecture 65 Section Introduction

Lecture 66 Aggregate Functions

Lecture 67 Why do we group records?

Lecture 68 Visualizing GROUP BY Operation

Lecture 69 Combining Grouping and Aggregation

Lecture 70 Exercise: Using Aggregation with Grouping

Lecture 71 Filtering Groups

Lecture 72 Using WHERE and HAVING Together

Lecture 73 Lecture Notes

Section 11: Sorting Records

Lecture 74 Section Introduction

Lecture 75 Sorting Records

Lecture 76 Limiting and Skipping Records

Lecture 77 Lecture Notes

Section 12: Subqueries

Lecture 78 Section Introduction

Lecture 79 What is a Subquery?

Lecture 80 Where is a Subquery Used?

Lecture 81 Subquery in SELECT Clause

Lecture 82 Subquery in FROM Clause

Lecture 83 Subquery in JOIN Clause

Lecture 84 Subquery in WHERE Clause

Lecture 85 Correlated Subquery

Lecture 86 Lecture Notes

Section 13: Case Study 1: Extracting Insights from E-commerce Dataset

Lecture 87 Quick Recap!

Lecture 88 Section Introduction

Lecture 89 Analytics Data Setup

Lecture 90 Exercise 1: Calculate the total revenue

Lecture 91 Solution - Exercise 1

Lecture 92 Exercise 2: Identify the top selling products

Lecture 93 Solution - Exercise 2

Lecture 94 Exercise 3: Calculate the Average Order Value (AOV)

Lecture 95 Solution - Exercise 3

Lecture 96 Exercise 4: Calculate the Customer Lifetime Value (CLV)

Lecture 97 Solution - Exercise 4

Lecture 98 Exercise 5: Identify customers who have not made a purchase in a while

Lecture 99 Solution - Exercise 5

Lecture 100 Exercise 6: Show the most recent review for each product

Lecture 101 Solution - Exercise 6

Lecture 102 Exercise 7: Identify the top rated products

Lecture 103 Solution - Exercise 7

Lecture 104 Exercise 8: Calculate discount based on order value

Lecture 105 Solution - Exercise 8

Section 14: Type Casting

Lecture 106 Section Introduction

Lecture 107 Explicit Type Casting

Lecture 108 Implicit Type Casting

Lecture 109 Lecture Notes

Section 15: Working with Date and Time Data Types

Lecture 110 Section Introduction

Lecture 111 Current Date and Time

Lecture 112 Time Zone Conversions

Lecture 113 Truncating Date and Timestamp

Lecture 114 Example: Truncating Date and Timestamp

Lecture 115 Extracting Parts of Date and Timestamp

Lecture 116 Formatting Date and Timestamp

Lecture 117 Date Math

Lecture 118 Lecture Notes

Section 16: Working with Array Data Type

Lecture 119 Section Introduction

Lecture 120 What is an Array?

Lecture 121 Adding Columns of Array Data Type

Lecture 122 Inserting Data into Array Columns

Lecture 123 Filtering Records Based on Array Values

Lecture 124 Array Indexing

Lecture 125 Array Functions

Lecture 126 Array Aggregation

Lecture 127 Exercise: Array Aggregation

Lecture 128 Flattening of Arrays

Lecture 129 Lecture Notes

Section 17: Working with JSON Data Type

Lecture 130 Section Introduction

Lecture 131 What is JSON?

Lecture 132 Adding Columns of JSON Data Type

Lecture 133 Inserting Data into JSON Columns

Lecture 134 Accessing JSON Fields

Lecture 135 Filtering Records Based on JSON Fields

Lecture 136 Constructing JSON Objects

Lecture 137 JSON Aggregation

Lecture 138 Lecture Notes

Section 18: Window Functions

Lecture 139 Section Introduction

Lecture 140 Data Setup

Lecture 141 The RANK() Window Function

Lecture 142 The DENSE_RANK() Window Function

Lecture 143 The ROW_NUMBER() Window Function

Lecture 144 The LAG() and LEAD() Window Function

Lecture 145 LAG() Function - A Practical Use Case

Lecture 146 Lecture Notes

Section 19: Common Table Expressions (CTEs)

Lecture 147 Section Introduction

Lecture 148 Common Table Expressions (CTEs) In Detail

Lecture 149 Lecture Notes

Section 20: SQL for Cleaning Data for Analysis and Reporting

Lecture 150 Section Introduction

Lecture 151 Data Setup

Lecture 152 Handling Missing Values

Lecture 153 Handling Duplicates

Lecture 154 Handling Outliers

Lecture 155 Standardizing Values in Categorical Columns

Lecture 156 Standardizing Text Data

Lecture 157 Consistent Format for Numeric Data

Lecture 158 Lecture Notes

Section 21: Case Study 2: Time Series Analysis with Retail Sales Dataset

Lecture 159 Section Introduction

Lecture 160 Importing Data From CSV to PostgreSQL Database

Lecture 161 Exploratory Data Analysis (EDA)

Lecture 162 Identifying Sales Trends

Lecture 163 Date Dimension Table and Fact Table

Lecture 164 A Generic and Reusable Date Dimension Table

Lecture 165 Top Business Categories By Sales

Lecture 166 Year-over-Year (YoY) Sales Analysis

Lecture 167 Quick Recap of the Key Concepts

Section 22: Some More Interview Questions From Top Companies

Section 23: Next Steps : Roadmap and Career Guidance

Lecture 168 Data Analyst / Data Scientist Roadmap

Job Seekers: Prepare effectively for Data Analyst interviews.,Non-IT Professionals: Transition into IT as a Data Analyst.,Software Developers: Enhance your skillset with data analytics expertise.,Data Scientists: Apply SQL to real-world analytics and business scenarios.,Junior to Intermediate Data Analysts: Master advanced concepts like Time Series Analysis.