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 capstone project based on insurance claims analysis.

Concepts

0. Introduction

01 - SQL for data engineering
02 - What you should know
03 - Using Codespaces

1. SQL Introduction

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

2. SQL Basics

08 - Introduction to databases and tables
09 - SQL syntax and basic queries
10 - Selecting and filtering data
11 - Sorting data with ORDER BY
12 - Combining conditions with AND and OR
13 - Challenge - Combine filter and ORDER BY
14 - Solution - Combine filter and ORDER BY

3. Data Manipulation

15 - Introduction to data manipulation
16 - Inserting data into tables
17 - Updating existing records
18 - Deleting records from tables
19 - Challenge - Delete records
20 - Solution - Delete records

4. Advanced Data Querying

21 - Introduction to joins
22 - Inner join explained
23 - Left and right joins explained
24 - Full outer joins
25 - Using the UNION operator
26 - Using the CASE statement for conditional logic
27 - Using GROUP BY to aggregate data
28 - Common aggregation functions - SUM, AVG, COUNT, etc.
29 - Filtering GROUP BY results with the HAVING clause
30 - Challenge - Joins
31 - Solution - Joins

5. SQL Functions

32 - Introduction to SQL functions
33 - Using mathematical functions
34 - Working with date functions
35 - Working with time functions
36 - String functions - Concatenation and trimming
37 - String functions - Changing case and extracting substrings
38 - Challenge - Play with strings
39 - Solution - Play with strings

6. SQL Analytical Functions

40 - Introduction to analytical functions
41 - Using the RANK() function
42 - Using the DENSE RANK() function
43 - Exploring the ROW NUMBER() function
44 - Understanding the LAG() and LEAD() functions
45 - Running totals with SUM() OVER()
46 - Calculating moving averages
47 - Challenge - Find out the sum
48 - Solution - Find out the sum

7. Views and Stored Procedures

49 - Introduction to views
50 - Creating and using views
51 - Introduction to stored procedures
52 - Creating and executing stored procedures
53 - Challenge - Select via view
54 - Solution - Select via view

8. Indexes and Performance

55 - Introduction to indexes
56 - Creating and managing indexes
57 - Understanding query performance
58 - Basic performance tuning techniques
59 - Challenge - Indexes
60 - Solution - Indexes

9. Database Security and User Management

61 - Introduction to database security
62 - Understanding users and roles in SQL
63 - Creating users and assigning roles
64 - Granting and revoking permissions
65 - Using roles for permission management
66 - Best practices for managing roles and permissions
67 - Challenge - Create a role
68 - Solution - Create a role

10. Debugging - Common Mistakes and SQL Errors

69 - Understanding SQL syntax errors
70 - Logical errors in SQL
71 - Debugging NULL values
72 - Practical example - Debugging real SQL issues

11. ETL Overview

73 - What is ETL and its importance
74 - ETL process flow
75 - Common ETL tools

12. Real-World Project

76 - Final project - Insurance claims analysis
77 - Final project solution - Insurance claims analysis

Conclusion

78 - Next steps