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

Introduction
1 SQL for data engineering
2 What you should know
3 Using Codespaces

SQL Introduction
4 What is SQL and why learn it
5 Role of SQL in data engineering
6 Setting up the SQL environment Install PostgreSQL
7 Walkthrough PostgreSQL

SQL Basics
8 Introduction to databases and tables
9 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

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

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

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

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

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

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

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

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

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

Real-World Project
76 Capstone project Insurance claims analysis
77 Capstone project solution Insurance claims analysis

Conclusion
78 Next steps