Project Based SQL Course: Code like a SQL Programmer

Learn to write SQL select queries, filter with where, and sort results with order by, using practical examples on user and claim tables.

learn how to obtain unique results with distinct and group by in sql, for single and multi column queries, and use aggregates to count examiner claims while filtering published records.

Explore advanced sql statements: duplicate tables with into, build top 10 inc results using group by and aggregates, and apply where, in, like, and having.

Practice sql with select exercises, filtering attachments entered by L. Nikki and filenames ending with PDA, and count reserve changes per claimant using group by and having.

Write three sql queries to track claim reserve activity, retrieving last reopened dates, examiner assignments, and the latest publish on the reserving tool for each claim.

Master inner join and left join by exploring how to connect tables with common fields in a database, using an insurance dataset and table aliases.

Explore lesser used joins, including the self join, right join, and full join, with notes on situational usefulness, and compare them to inner and left joins.

Master practical join tips, including aliases for inner, left, right, and full joins. Understand how joining on non-unique keys can create duplicates and why full joins can be memory-heavy.

Develop join skills across claim, claimant, and patient tables to fetch patient names. Use left joins to count office users and bucket reserve changes by type via self-joins.

Step 2 builds a join to fetch claim, examiner, and reserve data across five buckets, filtering open claims in Sacramento, San Diego, or San Francisco while excluding reopened reason 3.

Explore subqueries by running queries inside queries, referencing results with aliases, and fetching specific fields from the claim table, such as top 10 claim numbers.

Explore using subqueries in the from clause with an alias to create joinable tables, compute total reserve amounts, and apply the over function for partitioned sums in complex SQL queries.

Explore subqueries in select clause and compare them to from-clause subqueries. Note their unintuitive nature and slower performance, with a quick example of how the same result can be achieved.

Master subqueries with the reserve and payment tables by finding amounts above a specific reserve, above average, the second highest, and comparing first vs last publish medical reserves.

Learn about databases and tables, and how to create and drop databases with simple syntax. Use the object explorer to create a sample database such as example db.

Learn to create tables in sql using data types like integer, float, bit, date time, and varchar, define primary keys, and apply basic constraints in example tables.

Learn how to apply primary keys, foreign keys, and other constraints to create robust tables, including identity, index, and not null rules, for reliable data integrity and performance.

Drop tables and alter table to modify structure; use SQL syntax to add columns, change data types, set not null, and define primary and foreign keys.

Declare and assign variables and table variables in SQL, using set and select with examples like inflation rate and previous user. Understand temporary versus permanent tables and performance notes.

Create a prices table with price_id as primary key and reserve_type_id as foreign key, plus a bill detail table and a PDA document type.

Declare the as-of date and create the published reserving tool table and assigned date table to track claim numbers' most recent publish dates and examiner assignments.

Learn how to insert data into tables using the insert into statement, specifying columns and values, handling defaults and nulls, and using identity primary keys with a couple of examples.

Learn to update and delete data with update statements, set operations, and where clauses, using an office table as examples and practicing safe, targeted changes.

Learn to update from a table by setting a field in one table equal to another's field via a join. See examples with medical reserve cases and claim numbers.

Master temporary tables in SQL by creating, populating, and dropping temp tables to track claim examiners across levels, while comparing performance with table variables and learning when to index.

Practice insert, update, select, and delete across databases; learn to use temporary tables and table variables, handle nulls, and compute top five reserve amounts.

Explore stored procedures: create, execute, and modify procedures to automate queries, reduce network traffic, and improve security. Learn parameters, optional defaults, temp tables, and practical examples.

Learn to build and extend a stored procedure to report negative reserve types, counts of negative reserve changes, and average negative reserve amounts, with optional filters.

Turn the final query into a stored procedure named SFP_get_outstanding_rt_for_reserving_to_publish with optional parameters like days to complete, days overdue, and team.