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

Introduction

• SQL for data engineering
• What you should know
• Using Codespaces

1. SQL Introduction

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

2. 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

3. Data Manipulation

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

4. 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

5. 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

6. 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

7. 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

8. Indexes and Performance

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

9. 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

10. Debugging: Common Mistakes and SQL Errors

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

11. ETL Overview

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

12. Real-World Project

• Capstone project: Insurance claims analysis
• Capstone project solution: Insurance claims analysis

Conclusion

• Next steps