Structured Query Language (SQL)
A query goes to a bar. He walks up to two tables and asks, “May I join you?”
Learn SQL: Here’s Your Beginner’s Roadmap
Have you asked yourself recently, “How do I get started learning SQL?” If you have, this is the place for you. Below are bite-sized lessons on the basic elements of SQL, as I learn them. Questions, comments requests? Let me know.
1: Starting SQL from the absolute beginning
The start of my shared journey into SQL. This is my very first serious look into SQL and database management. Read now.
2: Getting started with Postgre SQL and PGAdmin
Downloading, installing, and configuring pgAdmin, one of the popular SQL database management programs. Get started.
3: Creating, restoring & deleting SQL databases
The fundamental actions and building blocks of working with SQL databases. Delete your first table?
4: Restoring databases with schema only in PGAdmin
One of the key common SQL database management tasks: restoring databases. Introducing more finesse and flexibility with database management.
5: The SELECT clause: SQL statement fundamentals
We’re finally writing SQL queries and statements! Our first foray into SQL clauses and statements, the “Hello World” of SQL. Select your first step!
6: Down with duplicates! The SELECT DISTINCT SQL query
Data is messy, and often duplicated. The SELECT DISTINCT statement is an easy way to get back only the unique values. Learn more.
7: Segmentation baby steps: Using SELECT WHERE
Segmentation rocks! SELECT WHERE is the first step in a journey of slicing through massive sluggishness of aggregate data sets to get phenomenal game-changing business insights.
8: Using the COUNT function for Quick Info
Scrolling to the bottom of a table to get a count rows, in any situation, is for the birds. COUNT is indeed self-explanatory. It’s also useful.
9: The LIMIT statement: Brevity is everything
Sometimes you need a quick validation of your work, without crashing the entire server. Maybe you only need the top [X] data points for a given question. The LIMIT statement is your friend here.
10: Using the ORDER BY clause to have it your way
By default, PostgreSQL returns rows in the order they were inserted into the table. It’s your data, use it when you need it! Start using ORDER BY.
11: Get particular. Specify ranges with BETWEEN
The next small step in the quest for segmentation is the BETWEEN statement. Say you need a list of lapsed customers from 2 weeks to 1 month ago. You don’t call Ghostbusters here, query with BETWEEN.
12: Efficiency! Learning the IN statement
Yes, make ALL the things efficient! The IN statement allows us to make very granular queries against a database. In doing so, we can skip the headaches of multiple value = x OR statement lists, or working with a BETWEEN statement.
13: Use the LIKE statement for pattern matching
Large and complex data sets are a reality of data science. The LIKE statement employs pattern matching when you can’t remember what values you need to look up, or, when you just have plain messy or incomplete data.
14: Beginner Postgre SQL skills challenge!
Pop quiz! May the force be with you, the odds ever in your favor, and live long whilst prospering. Test your knowledge.
15: GROUP BY SQL statement
Incrementing and innovating: emulating Excel’s pivot table functionality, at scale. Meet GROUP BY, our latest SQL Statement.
16: GROUP SQL skills challenge
After a 4 month hiatus from articles, why not hop back in with a quiz on the GROUP BY SQL statement?
17: Combined intermediate Postgre SQL skills challenge
I know, we just did a skills test! But, it’s necessary to ensure we’re on the right path for SQL mastery. Get started.
18: AS PostgreSQL statement
We’re approaching some kick-butt SQL knowledge in the JOINS function. However, we first need to cover a foundational element to SQL database manipulation: the AS statement.
19: Inner JOINS with Postgre SQL
The first of several articles on JOINS – a key ingredient on the road to advanced PostgreSQL success. We’ll break down concepts, syntax and code samples to better harness the power of PostgreSQL. Start working with inner JOINS.