Working with SQL
This course will teach you several key functions necessary to wrangle, filter, and categorize information in a relational database, expand your SQL toolkit, and answer complex questions. Requirements: You need to complete Introduction to SQL course to have basic knowledge before joining this course.
After completing this course, you will have the skills needed to analyze data and provide insights quickly and easily.
- You will learn the robust use of CASE statements, subqueries, and window functions.
- You will learn several concepts in this course such as dealing with missing data, working with dates, and calculating summary statistics using advanced queries.
Requirements: You need to complete Introduction to SQL course to have basic knowledge before joining this course.
SYLLABUS
Chapter content | # | Lesson content |
---|---|---|
Math function | ||
1 | SQL Round function | |
2 | SQL Power function | |
3 | SQL Floor function | |
4 | SQL Abs function | |
5 | SQL Sqrt function | |
6 | SQL Div function | |
7 | SQL Mod function | |
8 | Doing Math with aggregate function (1) | |
9 | Doing Math with aggregate function (2) | |
10 | SQL Math functions review | |
Parsing and Manipulating Text | ||
11 | SQL Length function | |
12 | SQL Left function | |
13 | SQL Substring function | |
14 | SQL Postion function | |
15 | SQL Concat function | |
16 | Case of string | |
17 | SQL Reverse function | |
18 | SQL Replace function | |
19 | SQL Lpad function | |
20 | SQL Trim function (1) | |
21 | SQL Trim function (2) | |
22 | Parsing and Manipulating Text Review (1) | |
23 | Parsing and Manipulating Text Review (2) | |
Working with date/time | ||
24 | Date/time data types review | |
25 | SQL Current Date/Time functions | |
26 | SQL Extract function | |
27 | SQL Age function | |
28 | SQL Interval Data Type (1) | |
29 | SQL Interval Data Type (2) | |
30 | SQL To_date function | |
31 | SQL Date_part function | |
32 | SQL Date_trunc function | |
33 | SQL Date function review (1) | |
34 | SQL Date function review (2) | |
35 | SQL Date function review (3) | |
36 | SQL Date function review (4) | |
Set theory clauses | ||
37 | SQL Union Operator (1) | |
38 | SQL Union Operator (2) | |
39 | SQL Union All Operator (1) | |
40 | SQL Union All Operator (2) | |
41 | SQL Intersect Operator | |
42 | SQL Except Operator | |
43 | Set theory clauses review (1) | |
44 | Set theory clauses review (2) | |
45 | Set theory challenge | |
Conditional expressions | ||
46 | Basic CASE statement | |
47 | CASE statements comparing column values (1) | |
48 | CASE statements comparing column values (2) | |
49 | In CASE of rivalry (1) | |
50 | In CASE of rivalry (2) | |
51 | CASE statement with COUNT | |
52 | CASE statement with SUM | |
53 | CASE statement with AVG | |
54 | CASE statement review (1) | |
55 | CASE statement review (2) | |
56 | CASE statement review (3) | |
57 | CASE statement review (4) | |
More about subqueries | ||
58 | Building on subqueries review (1) | |
59 | Building on subqueries review (2) | |
60 | Building on subqueries review (3) | |
61 | Building on subqueries review (4) | |
62 | Building on subqueries review (5) | |
63 | Correlated subqueries (1) | |
64 | Correlated subqueries (2) | |
65 | Nested subqueries (1) | |
66 | Nested subqueries (2) | |
67 | Nested subqueries (3) | |
68 | Subqueries review (1) | |
69 | Subqueries review (2) | |
Common Table Expressions | ||
70 | CTE syntax | |
71 | Creating CTE(s) (1) | |
72 | Creating CTE(s) (2) | |
73 | Simple CTE | |
74 | Multiple CTEs | |
75 | Clean up with CTEs | |
76 | Recursive CTE (1) | |
77 | Recursive CTE (2) | |
78 | Recursive CTE (3) | |
79 | Recursive CTE (4) | |
80 | Recursive CTE (5) | |
81 | CTE Review (1) | |
82 | CTE Review (2) | |
83 | CTE Review (3) | |
84 | Which technique to use? | |
Windown function | ||
85 | Window functions vs GROUP BY | |
86 | SQL Row_number function (1) | |
87 | SQL Row_number function (2) | |
88 | SQL Row_number function (3) | |
89 | SQL Lag function | |
90 | PARTITION BY (1) | |
91 | PARTITION BY (2) | |
92 | SQL Lead function | |
93 | SQL First_value function | |
94 | SQL Rank function | |
95 | SQL Dense_rank function | |
96 | SQL Ntile function | |
97 | Windown function review (1) | |
98 | Windown function review (2) |