SQL Essentials - Thinking in SQL from Beginners to Pro

Define database and structured data, explain DBMS and SQL, and introduce ANSI SQL standards and the five major database categories, guiding the use of big data tools like Spark.

Define databases and tables, store data, query for business questions and reports, and manipulate records with sql in a browser-based environment.

Learn to set up a databricks free edition account for sql hands-on learning, including sign-up, email verification, login, and configuring dark mode and editor theme.

Log into a Databricks free account, import resources, configure a dev catalog and scholar nest database, upload sql data, and test the environment by running the SQL Essentials notebook.

Learn how SQL is classified into five areas: DDL, DML, SQL statements, auxiliary statements, and SQL functions, and how to apply a requirement-based approach to mastering them.

Define a micro project to design a club database and migrate Excel data into SQL, capturing members, facilities, bookings, and joining details for efficient operations.

Design your data model by turning Excel sheets into three tables—members, facilities, and bookings—with clear primary and foreign keys. Visualize one-to-many relationships to support scalable sql queries.

Implement the data model in the database using SQL, learn DDL to create databases and tables, insert and manipulate data from Excel, and select data for verification.

Learn to work with databases and tables using SQL commands in a Databricks environment. Create, describe, show, and drop databases, including if not exists and cascade options.

Learn to work with tables by performing five operations—create, show tables, describe table, alter table, and drop table—using database naming and dot notation to manage facilities in mydb.

Master inserting, updating, and deleting data in SQL tables, using insert into with values and with select, including multi-row inserts and basic table creation.

Learn to load data by inserting from a source table into a target table, filter with where, transform names with concat, and perform updates and deletes safely.

Learn the basics of the select statement for querying data from tables, including from, where, order by, and limit/offset, plus aliases and reading JSON files in spark.

Implement a micro project solution by creating a club database with facilities, members, and bookings tables, loading CSV data, and verifying the results.

Master the select expression in sql essentials by learning to select all or specific columns, rename with aliases, reorder results, and remove duplicates with distinct.

Learn to use mathematical expressions in select clauses, compute monthly and annual maintenance, 25% depreciation of initial investment for first-year cost, and reuse results with a with-clause common table expression.

Learn to implement conditional and logical expressions in the select clause with operators like =, >, <, and like. See true/false flags for high maintenance and large facility, with-clause usage.

Learn to use conditional expressions in the select clause with case when, then, and end to compute a maintenance type from annual maintenance and handle multiple conditions.

Learn how to filter records with the where clause in SQL, using comparison operators, between, in, like, and is null, and combine conditions with and, or, not.

Learn to filter with case when then expressions and reuse calculations via common table expressions (ctes) to handle indoor facilities, facility type, and total cost.

Explore subquery filters in the where clause, returning single or multiple values. Use single value operators like = and list operators like in with bookings and members examples.

Learn how to filter facilities with a correlated query using exists, linking inner and outer queries on facility_id, to require related bookings and member costs.

Learn to join data sets across tables by using foreign keys to combine facilities, members, and bookings; explore inner and outer joins, subqueries, correlated queries, and denormalized tables.

Master inner joins by linking two or more tables with a common key, using aliases and on criteria, to produce precise facility and booking reports.

Explore outer joins in SQL, including left, right, and full outer joins, with step-by-step examples using bookings and members to demonstrate preserving records with or without matches.

Explore natural joins, cross joins, self joins, semi joins, and anti joins in SQL using Spark; learn when to use each type, their syntax, and practical examples.

Learn to use SQL functions across math, string, and date/time categories with Spark and PostgreSQL, including rounding, concatenation, and date formatting to build facility, member, and bookings reports.

Explore the seven essential mathematical functions in SQL essentials, learn to test them quickly using sample data and practical examples of abs, floor, ceil, round, pow, and least.

Master nine essential string functions in SQL, including concat, concat ws, contains and in str, lower and upper, left, right, substr, length, trim, replace, and regex based case expressions.

Explore commonly used SQL date time functions, current date and timestamp, and date arithmetic; convert strings to date or timestamp, format dates, and add or subtract days and months.

Master simple aggregation in SQL by using aggregate functions such as count, min, max, and average, with practical club data examples that illustrate counting non-null values and distinct recommendations.

Learn to use grouping aggregation in sql to compute total booking amounts by members for 2022, using group by, sum, and top-five results with order by and limit.

Master grouping aggregates with practical SQL examples: compute total bookings by member and facility for 2022, identify top five spenders, and generate monthly revenue and area–facility earnings reports.

Learn to filter grouped data with the having clause after group by. See why where cannot reference aggregated totals, and practice using aliases for top five members by year 2022.

Explore multi-level aggregation in SQL, including rollup, grouping sets, and cube, to compute final totals by group. Use monthly revenue reports to illustrate rollup and grand totals with nulls last.

Explore grouping sets, rollup, and cube to generate multi-level aggregations and grand totals for revenue from and facility name, including all combinations and null rows.

Explore windowing aggregates in SQL essentials by learning partition by, order by, and over syntax to calculate running totals, top-end analyses, and forward or backward comparisons.

Learn to compute running totals and three-day moving averages with windowing aggregates, partitioned by the booked by column and ordered by booking date, using July 2022 revenue data.

Explore windowing aggregates and top-n analysis using rank, dense_rank, and row_number to produce top and bottom dates by partitioning and ordering by revenue.

Explore windowing aggregates with lead and lag to perform forward and backward (pivoting) analysis, identifying gaps and sequences in data.

Learn how SQL set operations vertically combine two same-structured datasets using union, union all, intersect, and minus (except), including duplicates handling and when to apply each.

Learn to pivot data sets with SQL to create two-dimensional row and column summaries, using sum or average and a sales table example.

Explore unpivoting pivot results to convert columns to rows, restoring a normal table for analysis; learn the simple unpivot syntax and practical examples with quarters and half-year pivots.

Learn how SQL views store and reuse complex queries as virtual tables. Create or replace view to save query logic and enable consistent reports without rewriting the query.

Learn about auxiliary statements in SQL that provide table and column statistics, caching, time zone settings, and object details using show and describe commands.

Explore three data models: club, corp, and rental, and master sql with 50 extra practice questions, do it yourself notebooks, and stepwise cleanup, setup, and data loading workflows.

Celebrate your accomplishment after a long, difficult journey; you finished the course with a solid foundation, and your learning starts from here to expand knowledge and experience.