A Selection Of SQL Tutorials - Issue 77

A roundup of my favorite free SQL tutorials and articles to either get started with or to keep your querying skills strong.

Hello, data people! 

We are halfway through January, and I have already given up on my resolutions. Who cares about healthy eating anyway, as we’ll soon all move into VR and transcend our bodies. 

As promised earlier, today’s newsletter is all about SQL. Many years ago I started making a list of my favorite go-to videos, articles, and lessons which I still use and keep updating to this day. In this issue, I am going to save you hours of research and share my list of free SQL tutorials and practice sites for data acquisition and analysis.

A quick preface: given how quickly tech and analytical states develop, RDBs naturally become more popular and efficient by proxy. As data becomes more accessible, data quality and cleanliness develop into a separate discipline. To increase trust in data, we have to structure it - put constraints and relationships between tables (love the way that sounds). It means SQL knowledge becomes more essential and popular within the analytical domain. You can’t progress without SQL today. 

Some of the sources below I’ve used since 2014 or so. Most of those are (or were) free, but things change, so it is possible some of the classes are not free anymore. 

🛴 Getting started

• Learn Database Design with SQL Server - This will walk you through how to install a database, its terminology, fundamental concepts, usage, etc. Their walkthrough can be applied to other SQL variations besides SQL Server. If you are a MySQL user, here is a similar one - MySQL tutorial.

• A beginners guide to SQL - a little old, but I think this is one of the best introductions I have seen. 

• Understanding SQL Join 

• Mastering SQL Queries

• SQL LEFT JOIN – A Beginner’s Guide - a really good read. The author is a rockstar and I’m such a fan of his writing.  

• Understanding The Self Join

• Using ON Versus WHERE Clauses to Combine and Filter Data in PostgreSQL Joins

• How To Create User Profiles in SQL 

• SQL CASE Conditional Expression Guide

• Snowflake Analytics – Lag

🚲 Become proficient

• Best practices for writing SQL queries

• SQL Best Practices - How to type code cleanly and perfectly organized

• Top 5 SQL Functions

• Bucketing in SQL - grouping, bucketing, and segmenting users, dates, events in SQL, from simple to complex cases. 

• Reasons why SELECT * is bad for SQL performance - this article is more targeted towards DE, but the second part explains some good query plan optimization methods. 

• A Practical Use of the SQL COALESCE Function 

• Snowflake Analytics – Moving Difference

• Joining two tables that are not related by a foreign key relationship 

• 4 Essential SQL Window Functions

• Practical SQL for Data Analysis

• Calculating Customer Lifetime Value: SQL Example

• How to Get the First or Last Value in a Group Using Group By in SQL

• SQL DROP TABLE Statement and Various Use Cases 

• Introduction to Temporary Tables in SQL Server

🚀 Advance and become an expert 

• How to Parse Strings Like a Pro Using SQL SUBSTRING() Function?

• Passing Data table as Parameter to Stored Procedures

• JSON IN POSTGRESQL: HOW TO USE IT RIGHT

• Postgres Full-Text Search

• Advanced SQL - Common Table Expressions

And some of my favorite publications from one and only Haki Benita ⭐: 

• Simple Anomaly Detection Using Plain SQL

• Stop Using datetime.now!

• 12 Common Mistakes and Missed Optimization Opportunities in SQL

• Fastest Way to Load Data Into PostgreSQL Using Python

🔨 Practicing SQL

The best way to practice SQL is in your local database. For this, you can install the PostgreSQL database and use the DataGrip client (free 30-day trial!) with it. This gives you the flexibility to design the schema the way you want and practice SQL from anywhere at any time. 

If you want to go a simpler route, use SQLFiddle or DB-Feddle tools. You can either paste your SQL there and polish the syntax or design your own table schema. 

You can also try Simplql - an in-browser tool that lets you query data files with SQL without using a database. It does everything in the browser. Users can export the results from their queries in CSV or JSON. Easy, fast, intuitive. Love it! 

🎓 SQL Tutorials

If you are new to SQL and are looking for a quick tutorial on basics, check out SQL Zoo. 

The SQL Tutorial for Data Analysis - this is from Mode, so there are many CTAs around, but some descriptions are spot on and the structure is good. They also have another - The Intermediate SQL Tutorial, which is more focused on functions and joins. 

SQLCourse2.com - an online interactive SQL tutorial, from basics to advanced topics. Very similar to W3 school SQL.

TutorialRepublic - SQL Tutorial - I like this tutorial more, as it contains more specific examples, making it easier to understand. 

Leetcode - SQL - a set of SQL problems and challenges by topic, from simple to difficult. A good source to test yourself. 

WindowFunctions and PG Casts - more on an advanced side for SQL users. Postgres specific. 

⁉️ Interview preparation

• 50+ SQL Interview Questions and Answers (Updated for 2021)

• SQL Interview Questions 

---

I continually update this list to keep it fresh and relevant, so please send me your favorite SQL links and sources that you think I should add!  

Thanks for reading, everyone. Until next Wednesday!

Related publications:

• SQL Date-Time Cheatsheet

• SQL: Getting Started with SQLite

• How To Install A Database

• User Activity Analysis In SQL

• Currency Conversion In SQL and Python

• Advanced SQL Functions For Data Analysis

• How To Create Date Series in SQL

• A new SQL function - QUALIFY! And How To Optimize A Query Run Time

• SQL: Data Sampling in RedShift

• Day Of The Week Analysis in SQL

• User Segmentation and Power User Analysis in SQL

• SQL Interview Questions + Preparation Tips

• SQL Joins Don’t Scale?

• Facebook SQL Interview Question