Advanced SQL for Professional Devlopers

About

These are notes for the Advanced SQL for Professional Devlopers course on by Tyler Clark.

They are designed to be studied along side the course. Please add to them and put in a PR, we'd love to hear your thoughts!

Description

You've got some practice with using SQL to select, update, and join database tables.

But perhaps you've found yourself with a sneaking suspicion that you could be more efficient with your PostgreSQL.

Does your query seem slow?

A lesson on how to profile queries will point you in the direction of a fix.

Are you performing multi-step operations one at a time that are susceptible to errors?

The "all or nothing" transaction is the cure.

These are just a couple of examples of the techniques, tips, and tricks that Tyler Clark has learned in his years of experience working on large scale production applications.

Other topics in this course include working with CSV files, different techniques for aggregating & filtering, and more!

Watch Tyler's course, and continue your journey toward SQL mastery.

What you'll learn:

• Bulk insert and export from csv
• Casting types in SQL
• Define Custom Types (like ENUMs) for data integrity
• Analyze query performance for optimal queries
• Write concise queries with CTE's (Common Table Expressions) and do/declare blocks
• Filter Aggregate data with having
• Guarantee database interactions with Transactions
• Complex pattern matching

Essential Questions

These are the questions that you ask yourself to understand better the content in front of you and connect these ideas to what you have learned in the past.

How does database architecture affect your sql queries?

How much, as a web developer, is this going to be applicable to me?

When should I performance tune my queries?

Prerequisites

This course picks up from where Tyler Clark's SQL Fundamentals course leaves off. It's recommended to view the course before diving into these lessons.

• Familiarity with the CRUD actions in SQL.
• Some knowledge in table relationships, conditional selection, data types, aggregate functions, and subqueries.

Table of Contents

• 01- Bulk Insert And Export Files

• 02- Conflict Do Something

• 03- Casting Types in SQL

• 04- Create a Custom enum Type

• 05- Profiling Queries with Explain Analyze

• 06- Common Table Expressions in SQL

• 07- Conditional returns with case / then

• 08- Create Variables and Blocks with Do / Declare

• 09- Perform Multiple Steps in One with Transactions

• 10- Filter Grouped and Aggregated Data with Having

• 11- Create and Safely Handle Nulls in SQL

• 12- Aggregate Inline filters in SQL

• 13- Pattern Matching and Regular Expressions in SQL

• 14- Work with JSON Data in SQL

Contributors

Ceora Ford 🖋

William Johnson 👀 🚧

Emily Harber 🖋

Zac Jones 👀 🚇

Lauro Silva 🖋

Creeland A. Provinsal 👀 🖋

Appleton 🎨