Complete Guide to SQL for Data Engineering: from Beginner to Advanced

Course details

Are you looking to get up and running with SQL as a data engineer, but not sure where to begin? In this course, instructor Deepak Goyal provides a comprehensive overview of SQL specifically targeted at data engineers, from basic queries and analytic tasks to advanced functions and data management.Find out why SQL is one of the most frequently and widely used programming languages for data engineers. Explore fundamental skills in data manipulation, queries, functions, views and stored procedures, indexes and performance, database security, debugging, ETL, and more. The course concludes with a real-world final project based on insurance claims analysis.

Learning objectives
Analyze the daily responsibilities and key roles of a data engineer.
Explain the importance of SQL in data cleaning and analytics for data engineers.
Describe the ETL (Extract, Transform, Load) process and its significance in data engineering.
Identify different types of databases and their uses in data engineering tasks.
Demonstrate the installation and setup of PostgreSQL for SQL practice.
Write SQL queries to retrieve and manipulate data from databases.
Implement JOIN operations to combine data from multiple tables.
Utilize indexing to optimize SQL query performance.
Handle SQL syntax and logical errors effectively.
Secure databases by managing users, roles, and permissions.

Concepts

Introduction

SQL for data engineering
What you should know
Using Codespaces

SQL Introduction

What is SQL and why learn it
Role of SQL in data engineering
Setting up the SQL environment - Install PostgreSQL
Walkthrough - PostgreSQL

SQL Basics

Introduction to databases and tables
SQL syntax and basic queries
Selecting and filtering data
Sorting data with ORDER BY
Combining conditions with AND and OR
Challenge - Combine filter and ORDER BY
Solution - Combine filter and ORDER BY

Data Manipulation

Introduction to data manipulation
Inserting data into tables
Updating existing records
Deleting records from tables
Challenge - Delete records
Solution - Delete records

Advanced Data Querying

Introduction to joins
Inner join explained
Left and right joins explained
Full outer joins
Using the UNION operator
Using the CASE statement for conditional logic
Using GROUP BY to aggregate data
Common aggregation functions - SUM, AVG, COUNT, etc.
Filtering GROUP BY results with the HAVING clause
Challenge - Joins
Solution - Joins

SQL Functions

Introduction to SQL functions
Using mathematical functions
Working with date functions
Working with time functions
String functions - Concatenation and trimming
String functions - Changing case and extracting substrings
Challenge - Play with strings
Solution - Play with strings

SQL Analytical Functions

Introduction to analytical functions
Using the RANK() function
Using the DENSE RANK() function
Exploring the ROW NUMBER() function
Understanding the LAG() and LEAD() functions
Running totals with SUM() OVER()
Calculating moving averages
Challenge - Find out the sum
Solution - Find out the sum

Views and Stored Procedures

Introduction to views
Creating and using views
Introduction to stored procedures
Creating and executing stored procedures
Challenge - Select via view
Solution - Select via view

Indexes and Performance

Introduction to indexes
Creating and managing indexes
Understanding query performance
Basic performance tuning techniques
Challenge - Indexes
Solution - Indexes

Database Security and User Management

Introduction to database security
Understanding users and roles in SQL
Creating users and assigning roles
Granting and revoking permissions
Using roles for permission management
Best practices for managing roles and permissions
Challenge - Create a role
Solution - Create a role

Debugging - Common Mistakes and SQL Errors

Understanding SQL syntax errors
Logical errors in SQL
Debugging NULL values
Practical example - Debugging real SQL issues

ETL Overview

What is ETL and its importance
ETL process flow
Common ETL tools

Real-World Project

Final project - Insurance claims analysis
Final project solution - Insurance claims analysis

Conclusion

Next steps