SQL Essentials - Thinking in SQL from Beginners to Pro

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

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.

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.

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

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.

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.

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 with lead and lag to perform forward and backward (pivoting) analysis, identifying gaps and sequences in data.

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