From Excel to SQL, Master SQL Like a Pro!

Learn to write a basic SQL select using star to retrieve all columns and rows from the employee_dim table in Snowflake, and grasp the select, from, and star keywords.

Learn to select specific columns in SQL, such as employee ssn code and address text, by replacing star with column names and adding a static value column to every row.

Learn to filter rows with a where clause in SQL and Excel, using currency code = 'USD' and department = 'HR' to reveal four and 65 matching rows respectively.

Apply multiple sql filters using and, or, in, between, not in, and not between. Learn to group conditions with parentheses in Snowflake queries on the employee dim.

Learn how to perform aggregation in SQL using sum, max, min, and avg on salary data, and group by department to get insights, with Excel parallels and practical examples.

Learn to obtain unique values in SQL using distinct and group by to extract currency codes from employee dim; both methods yield 158 unique currencies.

Learn how to filter aggregated data using the having clause, moving the condition from where to having after group by to compare minimum salaries across departments.

learn to rename query columns using the as keyword, with examples like min_employee_salary_by_department, and apply meaningful aliases to simplify having clause logic.

explore how to update currency codes from USD to euro using SQL case statements and if statements, with multiple when branches and practical examples.

Master how to limit records in sql using top and limit to view a sample of data quickly and cheaply, including selecting the first ten rows from a table.

Explore handling null values in sql using is null and is not null checks, case statements, and coalesce and zero if null to replace missing data.

Sort data with the order by clause in sql, selecting ascending or descending order and sorting by multiple columns such as employee department code and currency code, including nulls first.

Explore the join concept to retrieve data from multiple tables by matching manager IDs with employee IDs, and see Excel techniques like Xlookup before mastering SQL joins.

Explore inner joins by linking the employee and department tables on department code to return only matching rows, using aliases and on condition syntax.

Explore left outer join by retrieving all rows from the left employee table and matching department names from the department table; when no match, department name returns null.

Learn how right join compares to left outer join, using department code to match rows in a one-to-many setup, returning right table rows and nulls for unmatched left rows.

Explore cross joins, a cartesian join where every row from one table pairs with every row from another, without a joining condition, often harming performance.

Master union all to combine rows from tables using set operators, ensuring matching column counts and data types; combine department codes from employee and department tables into a unified result.

Explore the SQL set operators minus and except to find departments present in the employee table but missing in the department table, with examples and unique value results.

Explore the intersect set operator to return rows common to two tables, removing duplicates to yield unique results. Practice updating your query to use intersect and observe five rows.

Master sql dml basics by learning insert into syntax to add one or multiple rows into department table, verify with select, and understand duplicates and primary key behavior in Snowflake.

insert rows into department manager table from department and employee data using insert into with select, listing department code, department manager employee id, department manager name, remove duplicates with distinct.

Handle not null columns by adding default values, join department with employee to fetch the manager name, and ensure inserts succeed without null results.

Update the department manager table using data from the department manager not null source table with aliases, from, and set clauses, ensuring no duplicates; eight rows updated.

Master deleting specific rows by using a delete from statement with a filter, after validating via a preliminary select for department_code = 'LD' and verifying with a final select.

Delete data by referencing another table using a subquery or the using clause, applying table aliases and optional join conditions for precise removal.

Update or insert rows with a single merge statement, using a department table to update manager IDs or insert new rows via when matched and when not matched.

Explore core data types across databases, including string, number, boolean, date and time, semi-structured, and geospatial types, with examples of char, varchar, binary, and numeric variants.

Learn how to create tables in Snowflake using SQL, define columns with proper data types, enforce not null and primary key constraints, and model simple tables like currency and team.

Master quick table creation using like and clone, leverage zero data copy for clones, and safely manage replacements and existence checks with replace and if not exists.

Alter tables to modify columns: drop the address txt column, add address txt with a data type, update values, and rename the column, understanding that column order doesn't matter.

Learn how to use alter table to rename an empty copy table to MP dept backup, swap tables with EMP dept clone, and verify results with select queries.

Rename an empty copy table to MP dept backup using alter table, then swap with MP data clone to illustrate table-level alterations.

Execute a drop table on the EMP data clone table, then confirm removal by running select star and observing that the table no longer exists.

Create an SQL view that acts as a dynamic window into base tables, reflecting updates without storing data, and learn how to drop the view when needed.

Learn to create a materialized view with sql, enabling automatic refresh and fresh data, with a Snowflake example that aggregates data.

Learn type casting in SQL by converting a numeric employee id to text using to_char or to_varchar and the double colon syntax, with hands-on examples on the MP depth table.

Convert string data to numbers by removing dashes from SSN codes, then cast to integer and sort, using Excel substitute and SQL replace with double colon number in Snowflake.

Convert string data to date format using to_date and try_to_date, handle various separators like dash and dot, and learn to manage invalid dates with null results.

Learn how to create a SQL pivot in Snowflake to sum salaries by department code and currency, using a subquery and the pivot syntax, with Excel pivot comparisons.

Learn how to use the listagg function in sql to aggregate unique department codes from the employee table into a single pipe-delimited string, mirroring excel's textjoin.

Split concatenated values into rows with the split to table function using a pipe delimiter, and generate rows with the generator function.

Explore hierarchical data using a top-down approach in a department hierarchy, employing self-joins on a snowflake table to flatten levels from level one to three.

Master how to show the entire hierarchy in one column for every row using connect by path, start with, and prior to navigate a department table.

Write a recursive sql using with, anchor clause, and recursive clause, union all, to build a department hierarchy with path text and parent department code.

Use a common table expression to find the highest department salaries. Compare cte usage with self-join and subqueries and note benefits in simplifying complex sql and reuse.

Explore using the window function rank with over partition by department code and order by salary descending to identify top earners per department with concise code and qualify.

Explore the row_number function to generate sequential rankings in sql, compare with rank, use a common table expression, and practice with dummy data including department code, employee id, and salary.

Explore how ntile divides a data set into four equal buckets, assigns a group number to each row, and uses order by to create randomized groupings.

Explore dense_rank by ranking customers by transaction amount using a small transaction daily dataset. See how dense_rank yields consecutive ranks for ties, unlike rank, which can skip numbers.

Use lag and lead window functions to compare monthly transaction amounts by customer, partitioned by customer and ordered by month, with nulls converted to zero for accurate month-to-month differences.

Compute the cumulative sum of transaction amounts per customer using window functions, partitioned by customer id and ordered by transaction year month id.

Learn to handle duplicates in SQL by using a common table expression, distinct or group by, and row number over partition by department code to return one row.

Master the concat function to join string columns, such as employee name and ssn code, using a delimiter, the concat underscore function, or the double pipe in snowflake sql.

Learn to measure the length of values in a column with Len in Excel and the length function in SQL, using the address text column.

Extract the middle of a string using Excel's mid and SQL's substr functions, starting at position five for two characters in the employee ssn code.

Use the SQL position function to locate the dash positions in an employee code, like Excel's find function, and build dynamic substrings between dashes using starting positions.

Trim removes blank spaces from strings in SQL, improving searchability; the lecture demonstrates trimming spaces and specific end characters with examples.

Learn to replace a character in a string using the SQL replace function, including replacing with nothing or with two, and compare it to Excel's substitute function.

Discover how to reverse a string in SQL by applying the reverse function to a column, producing the reversed name and understanding practical query usage.

Master SQL pattern matching with the like keyword, using percent and underscore wildcards to find Roy or Rob. Also learn the contains function to search a column for specific strings.

Explore how to create a list data type from a column using array_agg in SQL, display the list with square brackets, and contrast it with list_agg and CTE usage.

Discover how to use the translate function in sql, including the syntax, a comparison with replace, and examples showing translate replaces every occurrence of a character in a string.