English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 46 lectures (2h 22m) | 1.41 GB
Mastering SQL Database Management and SQL Query Optimization for High-Performance Data Engineering
Dive deep into the world of SQL with the Advanced SQL for Data Engineering course. This course is tailored for individuals aiming to sharpen their SQL skills and grasp advanced database design concepts, essential for numerous roles in today’s data-driven industry.
The course is structured into eight comprehensive sections. It begins with setting up the environment and an overview of relational databases to establish the groundwork for more advanced SQL topics. You will be introduced to SQL syntax, as well as Data Definition Language (DDL), Data Manipulation Language (DML), Data Query Language (DQL), and Data Control Language (DCL).
The subsequent sections progress systematically through the manipulation of databases, managing and manipulating DateTime in SQL, handling complex data types, and exploring advanced query techniques. You will master essential SQL statements such as CREATE, ALTER, INSERT, UPDATE, DELETE, MERGE, and DROP.
Then you will dive into the subtleties of DateTime types, timezones, and intervals, and get hands-on experience with ENUMs. Ranges, and nested data. Each topic is designed to equip you with advanced techniques for managing, querying, and manipulating databases.
After that you will explore advanced query techniques with a focus on OVER, various types of JOINS, CASE, CONCAT, and the powerful Recursive Common Table Expressions (CTEs). These concepts will hone your skills in complex data querying, a pivotal aspect of data engineering.
The sixth section deals with optimizing data structures and introduces the concept of data normalization in SQL. You will learn the theory and practical application of data normalization, as well as the STAR schema and Snowflake model, both essential in designing efficient databases.
Section seven delves into performance and control, where you will master stored procedures, User-Defined Functions (UDFs), temporary tables, materialized views, and transactions. These concepts enhance your capabilities to manage and optimize databases for peak performance.
The final section allows you to put all the knowledge you’ve gained into practice with a series of challenging exercises. These practical tasks are designed to cement your understanding of the topics covered and prepare you for real-world scenarios.
By the end of the Advanced SQL for Data Engineering course, you will be proficient in SQL’s advanced features and capable of designing, managing, and optimizing databases effectively.
Whether you’re a budding data engineer or an experienced professional seeking to enhance your skills, this course has you covered!
What you’ll learn
- Execute Database Manipulation
- Manage DateTime in SQL
- Handle Complex Data Types
- Master Advanced Query Techniques
- Optimize Databases
Table of Contents
Setting up the environment and overview of relational databases
1 Introduction to the course
2 Coure GitHub repository
3 Setting up the environment
4 An overview of relational databases
5 DDL, DML, DQL, DCL
6 SQL syntax
Manipulating databases Mastering essential SQL statements
7 Intro SQL statements
8 CREATE
9 ALTER
10 INSERT
11 UPDATE
12 DELETE
13 MERGE
14 DROP
Timing is everything managing and manipulating DateTime in SQL
15 DateTime intro
16 Different DateTime types
17 Timezones
18 Intervals
Complex data types ENUMs, ARRAYs, Ranges, and Nested data in SQL
19 ENUM
20 ARRAYs
21 RANGE
22 Nested data
Advanced query techniques exploring OVER, JOINS, CASE and more in SQL
23 OVER
24 CROSS JOIN
25 LATERAL JOIN
26 CROSS JOIN LATERAL
27 COALESCE
28 CASE
29 CONCAT
30 Recursive CTE
31 Recursive CTE – second part
Optimizing data structures The art and science of data normalization in SQL
32 Data normatlization (13)
33 Data normatlization (23)
34 Data normatlization (33)
35 STAR schema Snowflake
Performance and control Mastering stored procedures, temporary tables
36 Stored procedures and UDFs (13)
37 Stored procedures and UDFs (23)
38 Stored procedures and UDFs (33)
39 Temp table
40 Materialized view
41 Transactions
42 SQL structures
Practical tasks
43 Question 1
44 Question 2
45 Question 3
46 Question 4
Resolve the captcha to access the links!