mikeblas

• Learn SQL The Hard Way
• Fundamentals of Database Systems 7th edition by Elmasri and Navathe
• The Art of PostgreSQL by Dimitri Fontaine
• Database Design 2nd Edition (online textbook) by Adrienne Watt
• Database System Concepts 7th Edition (Kindle edition) by Silberschatz
• The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling 3rd Edition - the Kimball/Ross Classic/standard reference text on data modeling
• MySQL 8 Query Performance Tuning: A Systematic Method for Improving Execution Speeds - by Krogh
• [Pro SQL Server Relational Database Design and Implementation: Best Practices for Scalability and Performance 6th ed. Edition](

mikeblas

This might kind of seem harsh, but I think it takes into account that there's a lot of knowledge to have in most subjects, and most people take very high levels of knowlege for granted. When I recruit, and when I train people on hiring, I propose a very curious rating on a zero to ten scale:

0. don't know how to spell it
1. heard of it
2. wandering around the bookstore to find a book on it
3. did some tutorials, half a clue
4. used it a bit
5. familiar with most stuff about it
6. did a very substantial project with it

mikeblas

"SQL skills" is a bit of a trigger phrase for me because it's very much overloaded, since SQL work ranges from developing a database (that is, writing the database server itself) through designing a database (using a DBMS someone else wrote to implement a data system), through writing queries and doing DBA work. People tend to lump these things together without carefully understanding what job they're looking for (or what candidate they want to hire) or how to best build a data team or ...

Anyway! Since you asked specifically about querying, I can leave out all the rest:

A beginner:

• Knows the tool. Can setup and use a command-line tool, a GUI tool, knows a couple of each at least. Can diagnose connection problems. Understands how to save, load, manipulate files.

mikeblas

import random


def build_data(tablename, filename, rows):
    with open(filename, 'w') as outfile:
        outfile.write(f"INSERT INTO {tablename} (id, type, category_id) VALUES\n")
        type_list = ['pet', 'plant', 'book', 'snack', 'tool']
        for x in range(rows):
            if x > 0:
                outfile.write(",\n")

mikeblas

It's not so hard to find sample data and data sources to use for interesting side-projects, or just for practicing writing SQL.

In-product sample data

Most DBMSes come with sample databases. You can write lots of interesting queries against them, and usually a tutorial accompanies the database in the documentation.

• Documentation for Microsoft SQL Server's samples ** Microsoft's sample database GitHub, which includes the Contoso database
• For MySQL:
  • there's the Employees sample database
• and the Sakila sample database

mikeblas

-- in flight queries by CPU time
-- author: mike@blaszczak.com
-- Date: 2020-11-27


SELECT s.session_id,
    DB_NAME(r.database_id) as DBName,
	r.status,
	r.blocking_session_id 'Blk by',
	r.wait_type,