mikeblas

-- in flight queries by CPU time
-- author: [email protected]
-- 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,

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

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

"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

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 Basics

• T-SQL Fundamentals by Itzik Ben-Gan
• Joe Celko's SQL for Smarties: Advanced SQL Programming by Joe Celko
• A Visual Introduction to SQL by Chappell and Trimble
• Learn SQL The Hard Way
• The Art of PostgreSQL by Dimitri Fontaine
• Exam Ref 70-762 Developing SQL Databases 1st Edition by Davidson andVarga
• Exam Ref 70-761 Querying Data with Transact-SQL 1st by Itzik Ben-Gan

mikeblas

-- get files for each database (both log and rows)
-- including the sizes of those files 
-- and growth settings

SELECT 
    db.name AS                                   [Database Name], 
    mf.name AS                                   [Logical Name], 
    mf.type_desc AS                              [File Type], 
    mf.physical_name AS                          [Path], 

mikeblas

-- get the row count and space used stats for each index
-- in this database

SELECT
	s.Name as SchemaName, t.Name as TableName, i.name as IndexName,
	p.Rows as RowCounts, SUM(AU.Total_pages) * 8.0/1024 AS TotalSpaceMB,
	SUM(AU.used_pages) * 8.0/1024 AS UsedSpaceMB,
	(SUM(AU.Total_Pages) - SUM(AU.used_pages)) * 8.0/1024 AS UnusedSpaceMB
  FROM sys.tables AS T

mikeblas

-- due to https://www.mssqltips.com/sqlservertip/1786/auto-generate-sql-server-database-attach-and-detach-scripts/
-- modified to use NVARCHAR for names

SET NOCOUNT ON 
DECLARE     @cmd        NVARCHAR(MAX),
            @dbname     NVARCHAR(200),
            @prevdbname NVARCHAR(200)

SELECT @cmd = N'', @dbname = N';', @prevdbname = N''