Who Am I?

MCSE: Data Management & Analytics
MCSA: SQL 2016 Database Administration
MCSA: SQL 2016 Database Development
Adobe Certified Professional: Adobe ColdFusion
Adobe ColdFusion Specialist
(Plus CFMX certs from Macromedia & Adobe)
CompTIA Security+
Currently hold a U.S. DoD Security Clearance

Shawn Oden

codefumonkey@gmail.com | @CodeFuMonkey | codefumonkey.com

What Will We Learn?

Basic Parts of a Query

AND & OR in WHERE

IN in WHERE

LIKE & Wildcards

ORDER BY

INNER / OUTER JOIN

Subqueries

Table & Column Aliases

GROUP BY

Aggregates

HAVING

CAST / CONVERT

UNION & UNION ALL

Creating & Using CTEs

Creating & Using Views

Using Stored Procedures

Disclaimer

This is just the basics.

These aren't the only ways,

and probably not even the best ways.

I'm just demonstrating these common concepts.

If I say something that doesn't make sense, please question me. We all learn better that way.

Special Thanks

This idea originated from

THE SQL MURDER MYSTERY

created by

Joon Park and Cathy He

from

Northwestern University's Knight Lab

This project is being released under Creative Commons CC BY-SA 4.0.

Before We Get Started

We Need:

Download / Install SSMS (Management Studio) or Azure Data Studio
https://aka.ms/ssmsfullsetup or https://bit.ly/downloadADS

Pull the Database Container from Docker Hub
docker pull codefumonkey/sqlsleuth:itb2023
docker run -p 10433:1433 --name sqlsleuth -d codefumonkey/sqlsleuth:itb2023
docker start sqlsleuth

Grab the Gist of the SQL scripts from GitHub
Main Script & Pop Quiz Solution: https://bit.ly/sqlsleuthitb2023
The full repo includes the SQL scripts needed to setup the database.

Run the Query Script in SSMS or the Notebooks in Azure Data Studio
User = sleuth
Password = Pa$$w0rd

Part 1: The Very Basics

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

Plus the other bits to help us do the job.

Parts of a Query

https://learn.microsoft.com/en-us/sql/sql-server

Part 1: The Very Basics

Parts of a Query

https://learn.microsoft.com/en-us/sql/sql-server

SELECT = Used to retrieve columns from a table.

FROM = Used to specify the table name.

WHERE = Used to filter results.

HAVING = Specifies the filter for an aggregation.

ORDER BY = Used to sort result rows.

GROUP BY = Groups rows in a query to allow for aggregation.

Part 1: The Very Basics

LEXICAL

Order of Evaluation

SELECT col1

FROM table

WHERE colX = 42

GROUP BY col1

HAVING col1 > 3

ORDER BY col1

How We Write It

LOGICAL

FROM table

WHERE colX = 42

GROUP BY col1

HAVING col1 > 3

SELECT col1

ORDER BY col1

How SQL Sees It

https://www.eversql.com/sql-order-of-operations-sql-query-order-of-execution/
https://blog.jooq.org/a-beginners-guide-to-the-true-order-of-sql-operations/

Part 1: The Very Basics

LEXICAL

Order of Evaluation (a bit more)

SELECT TOP n DISTINCT col1, aggregate(col2)

WINDOW

FROM table (...JOIN table2)

WHERE colX = 42

GROUP BY col1

HAVING col1 > 3

UNION [ALL] / INTERSECT / EXCEPT

ORDER BY col1

LIMIT/FETCH ...

How We Write It

LOGICAL

FROM table (...JOIN table2)

WHERE colX = 42

GROUP BY col1

aggregate(col2)

HAVING col1 > 3

window functions

SELECT col1

DISTINCT

UNION [ALL] / INTERSECT / EXCEPT

ORDER BY col1

LIMIT / OFFSET / FETCH / TOP

How SQL Sees It

https://www.eversql.com/sql-order-of-operations-sql-query-order-of-execution/
https://blog.jooq.org/a-beginners-guide-to-the-true-order-of-sql-operations/

Part 1: The Very Basics

Order of Evaluation

ACTUAL

MAGIC

How SQL Does It

https://www.eversql.com/sql-order-of-operations-sql-query-order-of-execution/
https://blog.jooq.org/a-beginners-guide-to-the-true-order-of-sql-operations/

Part 2: The Challenge

Solve a Crime With Queries

The Data County Police Department Needs Your Help!

Part 2: The Challenge

Welcome to the Investigation Unit of the Data County Police Department.

You've been brought in to assist the DCPD with some of their database issues. Unfortunately, the previous intern made a bit of a mess, and things aren't working like they should.

To add to the trouble, Detective Fife, the new rookie assigned to one of our cold cases, accidentally shredded the case folder and has asked for your help in rebuilding the information and solving the crime.

He remembers that it was a murder that took place in Cloud City

and occurred sometime on January 10th of either 2016 or 2018.

All of the clues to this mystery are buried in this huge database, and you need to use some SQL skills to navigate through this vast network of information.

NOTE: Thanks to the intern, some of the case data isn't organized like it should be, and we aren't able to rely on proper linking of two of the tables: interview should link to crime_scene_report, but it doesn't, making it more difficult to find the appropriate witness interviews for a crime scene report. Fortunately, DCPD detectives are pretty efficient and are able to interview all witnesses within 3 days of a crime. And the people of Cloud City are pretty honest people, even the criminals. They aren't going to lie to the police, so it's safe to assume their statements are factual.

Part 2: The Challenge

What does our database look like?

Part 2: The Challenge

Find our Crime Scene Report

What we know:

It happened in Cloud City
It was a murder
It happened on Jan 10 of 2016 or 2018

Part 2: The Challenge

Find our Crime Scene Report

SELECT *
FROM crime_scene_report

Invalid object name 'crime_scene_report'

Don't forget to tell your IDE what database to use.

USE sql_sleuth

Commands completed successfully.

Part 2: The Challenge

Find our Crime Scene Report

SELECT *
FROM crime_scene_report

id  date        Crime Type  description                                                  city
1   2019-09-19    identity theft    Chinese National Pleads Guilty to ...                Funkytown
2   2017-04-30    drugs    Former Angels Communications Director Eric Kay ...            LA
3   2018-12-10    fraud    Cybercriminal Charged with Unauthorized Computer ...          Funkytown
4   2018-01-01    bribery  Two New Yorkers Plead Guilty in Scheme to Bribe ...           New York
5   2018-11-30    fraud    Tuscaloosa Man Sentenced to Seven Years on COVID19 ...        Tuscaloosa
6   2019-10-13    theft    Husband and Wife Are Sentenced to Prison for ...              Funkytown
.....
190 2018-02-18    fraud    Watertown Woman Charged with Wire Fraud                       Watertown
191 2019-06-04    murder   Twenty Two Year Old Man Pleads Guilty to Attempted ...        Funkytown
192 2019-04-20    drugs    Georgia Man Going to Prison for His Role in Cocaine ...       Atlanta
193 2022-01-01    missing person    An intern for the Data County Police Department      Cloud City
                           didn't show up for work and has been missing for the last
                           month. We suspect foul play, likely involving either
                           someone who works for the PD or someone who would benefit
                           from the intern's disappearance, like the new contractor
                           coming in to replace the intern.

Part 2: The Challenge

Find our Crime Scene Report

SELECT *
FROM crime_scene_report
WHERE ????????

Part 2: The Challenge

Find our Crime Scene Report

What we know:

It happened in Cloud City
It was a murder
It happened on Jan 10 of 2016 or 2018

Text

Part 2: The Challenge

Find our Crime Scene Report

SELECT *
FROM crime_scene_report
WHERE city = 'Cloud City'

id date Crime Type description city
11 2016-01-15 kidnapping Security cameras show 3 witnesses. Cloud City
The first witness lives at the fourth house on "Briggs Blvd".
The second witness has blond hair and was wearing a "Wyld Stallyns" t-shirt.
The third witness is named William and lives somewhere on "Martha Way"
20 2017-07-01 speeding I wrote a ticket to a woman for speeding. Cloud City
79 2018-01-10 murder Security footage shows that there were 2 witnesses. Cloud City
The first witness lives at the last house on "Northwestern Dr".
The second witness, named "Frances", lives somewhere on "Franklin Ave" and has 3 roommates.
126 2018-01-15 robbery A Man Dressed as SpiderMan is on a Robbery Spree Cloud City
134 2018-02-15 murder Someone killed the guard with an arrow to the knee! Cloud City
193 2022-01-01 missing person An intern for the Cloud County Police Department didn't show up for work and has been Cloud City
missing for the last month. We suspect foul play, likely involving either someone who works
for the PD or someone who would benefit from the intern's disappearance, like the new
contractor coming in to replace the intern.

Part 2: The Challenge

Find our Crime Scene Report

What we know:

It happened in Cloud City
It was a murder
It happened on Jan 10 of 2016 or 2018

Text

Part 2: The Challenge

Find our Crime Scene Report

SELECT *
FROM crime_scene_report
WHERE Crime Type = 'murder'

An expression of non-boolean type specified in a context where a condition is expected, near 'Type'

Part 2: The Challenge

Find our Crime Scene Report

SELECT *
FROM crime_scene_report
WHERE [Crime Type] = 'murder'

id date Crime Type description city
28 1963-11-22 murder Someone shot the President. There was a witness watching the parade from the grassy knoll. Dallas
72 2019-08-24 murder Man Suspected in Philadelphia Murder Arrested in Atlanta Atlanta
79 2018-01-10 murder Security footage shows that there were 2 witnesses. ... Cloud City
99 2019-11-06 murder Jury Convicts Longtime Hoover Criminal Gang Members... Funkytown
130 2018-01-15 murder Someone put a gun against a man's head and pulled the ... Reno
134 2018-02-15 murder Someone killed the guard! He took an arrow to the knee! Cloud City
165 2017-06-24 murder Detroit Street Gang Member Pleads Guilty to Murdering... Detroit
173 2018-07-10 murder Former U.S. Soldier Admits to Murder of Fort ... Fort Steward
183 2019-06-29 murder Shiprock Woman Charged with Murder in Indian Country Shiprock
185 2018-01-14 murder Someone shot Deputy Johnson and then Sheriff Brown. Kings Town
191 2019-06-04 murder TwentyTwoYearOld Man Pleads Guilty to Attempted ... Funkytown

Part 2: The Challenge

Find our Crime Scene Report

SELECT *
FROM crime_scene_report
WHERE city = 'Cloud City'
    AND [Crime Type] = 'murder'

id  date        Crime Type  description                                               city
79  2018-01-10  murder      Security footage shows that there were 2 witnesses.       Cloud City  
                            The first witness lives at the last house on "Northwestern Dr".
                            The second witness, named "Frances", lives somewhere on 
                            "Franklin Ave" and has 3 roommates.
134 2018-02-15  murder      Witnesses saw a man wearing a green hood shot the guard   Cloud City                                      in the knee with an arrow.

Part 2: The Challenge

Find our Crime Scene Report

What we know:

It happened in Cloud City
It was a murder
It happened on Jan 10 of 2016 or 2018

.

Part 2: The Challenge

Sidebar

ISO 8601 Date Format

2016-01-10

instead of

January 10, 2016

https://www.iso.org/iso-8601-date-and-time-format.html

1/10/2016 vs 10/1/2016

Depending on where you're from, they're both January.

Part 2: The Challenge

Find our Crime Scene Report

SELECT *
FROM crime_scene_report
WHERE date = '2016-01-10' OR date = '2018-01-10'

id date Crime Type description city
11 2016-01-10 kidnapping Security cameras show 3 witnesses. Cloud City
The first witness lives at the fourth house on "Briggs Blvd".
The second witness has blond hair and was wearing a "Wyld Stallyns" t-shirt.
The third witness is named William and lives somewhere on "Martha Way"
79 2018-01-10 murder Security footage shows that there were 2 witnesses. Cloud City
The first witness lives at the last house on "Northwestern Dr".
The second witness, named "Frances", lives somewhere on "Franklin Ave" and has 3 roommates.
126 2018-01-10 robbery A Man Dressed as SpiderMan is on a Robbery Spree Cloud City
130 2018-01-10 murder Someone put a gun against a man's head and pulled the ... Reno
135 2018-01-10 theft Over a billion dollars in BitCoin (so about 40 BTC..600..120..10..I give up!) was Charlottesburg
embezzled from MyBTCWallet Trading Company. We think an employee did it.
163 2018-01-10 missing animals Several livestock escaped from a farm on James William Road. Elliottsville

Part 2: The Challenge

Find our Crime Scene Report

SELECT *
FROM crime_scene_report
WHERE city = 'Cloud City'
    AND [Crime Type] = 'murder'
    AND date = '2016-01-10' OR date = '2018-01-10'

id  date        Crime Type  description                                             city
79  2018-01-10  murder      Security footage shows that there were 2 witnesses. ... Cloud City  
126 2018-01-10  robbery     A Man Dressed as SpiderMan is on a Robbery Spree.       Cloud City
130 2018-01-10  murder      Someone put a gun against a man's head and pulled ...   Reno
135 2018-01-10  theft       Over a billion dollars in BitCoin (so about 40 ...     Charlottesburg
163 2018-01-10  missing animals  Several livestock escaped from a farm ...          Elliottsville

Part 2: The Challenge

Find our Crime Scene Report

SELECT *
FROM crime_scene_report
WHERE city = 'Cloud City'
    AND [Crime Type] = 'murder'
	AND date = '2016-01-10'

SELECT *
FROM crime_scene_report
WHERE date = '2018-01-10'

Part 2: The Challenge

Find our Crime Scene Report

SELECT *
FROM crime_scene_report
WHERE city = 'Cloud City'
    AND [Crime Type] = 'murder'
    AND ( date = '2016-01-10' OR date = '2018-01-10' )

id  date        Crime Type  description                                               city
79  2018-01-10  murder      Security footage shows that there were 2 witnesses.       Cloud City  
                            The first witness lives at the last house on "Northwestern Dr".
                            The second witness, named "Frances", lives somewhere on 
                            "Franklin Ave" and has 3 roommates.

SELECT *
FROM crime_scene_report
WHERE city = 'Cloud City'
    AND [Crime Type] = 'murder'
    AND date IN ('2016-01-10', '2018-01-10')

Part 2: The Challenge

Find our Crime Scene Report

SELECT id, description
FROM crime_scene_report
WHERE city = 'Cloud City'
    AND [Crime Type] = 'murder'
    AND date IN ('2016-01-10''2018-01-10')

id description                                                                                
79 Security footage shows that there were 2 witnesses.                                        
   The first witness lives at the last house on "Northwestern Dr".
   The second witness, named "Frances", lives somewhere on "Franklin Ave" and has 3 roommates.

Part 2: The Challenge

Find our Witnesses

SELECT *
FROM interview
WHERE crime_scene_report_id = 79

The crime_scene_report_id from our crime scene is 79.

Part 2: The Challenge

Find our Witnesses

SELECT *
FROM interview
INNER JOIN crime_scene_report ON id = crime_scene_report_id

Part 2: The Challenge

Find our Witnesses

SELECT *
FROM interview
INNER JOIN crime_scene_report ON id = crime_scene_report_id
WHERE city = 'Cloud City'
    AND [Crime Type] = 'murder'
    AND date IN ('2016-01-05', '2018-01-05')

Ambiguous column name 'id'.

SELECT *
FROM interview
INNER JOIN crime_scene_report ON id = crime_scene_report_id
    AND city = 'Cloud City'
    AND [Crime Type] = 'murder'
    AND date IN ('2016-01-05', '2018-01-05')

Part 2: The Challenge

Find our Witnesses

SELECT *
FROM interview
INNER JOIN crime_scene_report ON crime_scene_report.id = 
  interview.crime_scene_report_id
    AND crime_scene_report.city = 'Cloud City'
    AND crime_scene_report.[Crime Type] = 'murder'
    AND crime_scene_report.date IN ('2016-01-05', '2018-01-05')

SELECT i.id AS interview_id, i.person_id AS interview_person_id, 
    i.transcript AS interview_transcript, i.interview_date, 
    i.crime_scene_report_id
FROM interview i
INNER JOIN crime_scene_report csr ON csr.id = i.crime_scene_report_id
    AND csr.city = 'Cloud City'
    AND csr.[Crime Type] = 'murder'
    AND csr.date IN ('2016-01-05', '2018-01-05')

Conversion failed when converting the varchar value 'QMdJrEwZLMLY6Ij' to data type int.

Part 2: The Challenge

Find our Witnesses

SELECT *
FROM interview i
INNER JOIN crime_scene_report csr ON CAST(csr.id
  AS varchar(10)) = i.crime_scene_report_id
    AND csr.city = 'Cloud City'
    AND csr.[Crime Type] = 'murder'
    AND csr.date IN ('2016-01-05', '2018-01-05')

id date Crime Type description city id person_id transcript interview_date crime_scene_report_id

Part 2: The Challenge

Find our Witnesses

SELECT *
FROM interview i
LEFT OUTER JOIN crime_scene_report csr ON CAST(csr.id AS
   varchar(10)) = i.crime_scene_report_id
WHERE csr.city = 'Cloud City'
    AND csr.[Crime Type] = 'murder'
    AND csr.date IN ('2016-01-05', '2018-01-05')

id date        Crime Type  description         city          id     person_id transcript interview_date crime_scene_report_id
...
79 2018-01-15  murder      Security footage... Cloud City    NULL    NULL     NULL        NULL           NULL
...

Part 2: The Challenge

Find our Witnesses

SELECT *
FROM interview i
RIGHT OUTER JOIN crime_scene_report csr ON CAST(csr.id AS
   varchar(10)) = i.crime_scene_report_id
WHERE csr.city = 'Cloud City'
    AND csr.[Crime Type] = 'murder'
    AND csr.date IN ('2016-01-05', '2018-01-05')

id    person_id transcript interview_date crime_scene_report_id  id  date        Crime Type  description         city  
...
NULL  NULL      NULL        NULL          NULL                   79  2018-01-15  murder      Security footage... Cloud City
...

Part 2: The Challenge

Find our Witnesses

SELECT * FROM interview

id   person_id	 transcript	                      interview_date crime_scene_report_id
1    19948	 I think the guy who did it goes to ...	2018-01-12	NULL
2    94837	 A big tree fell in the storm.		2019-08-29	dpkNeqnLjXGfhve
3    78551	 Al went to the animal shelter and ...	2019-11-27	mCqwiHuJD3R5Xjc
...
65   69725	 You ever make to-do lists and put ...	2018-12-08	PwK9PHXm8ivdNvS
66   42837	 You should consider the positive ...	2018-08-04	HiSAQtkCmBAUJVU
67   16371	 I did it. I'm ready to face the music.	2017-07-02	kfQTL2UKz819OoV

Part 2: The Challenge

Find our Witnesses

SELECT csr.description
FROM crime_scene_report csr
WHERE csr.city = 'Cloud City'
    AND csr.[Crime Type] = 'murder'
	AND csr.date IN ('2016-01-05', '2018-01-05')

Security footage shows that there were 2 witnesses.
The first witness lives at the last house on "Northwestern Dr".
The second witness, named "Frances", lives somewhere on "Franklin Ave" and has 3 roommates.

Part 2: The Challenge

What we know:

Witness 1:
- Lives at the last house on Northwestern Drive

Witness 2:
- Named Frances
- Lives somewhere on Franklin Ave
  - Has 3 roommates

Find our Witnesses

Part 2: The Challenge

Find our First Witness

Part 2: The Challenge

Find our First Witness

SELECT *
FROM person p
WHERE p.address_street_name = 'Northwestern Dr'

id    person_name     address_number  address_street_name         person_ssn
10000 Frances Andrews 1               Northwestern Dr             101-01-0101
89906 Kinsey Erickson 309             Northwestern Dr             635-28-7661
10001 William Preston 1               Northwestern Dr             111-11-1111
10002 Theodore Logan  3               Northwestern Dr             222-22-2222
14887 Richard Hammill 4919            Northwestern Dr             111-56-4949
22239 Dusty Sigafus   1125            Northwestern Dr             724-38-6723
28360 Rashad Cascone  3212            Northwestern Dr             838-90-7424
53890 Sophie Tiberio  3755            Northwestern Dr             442-83-0147
96178 Galina Khuu     2183            Northwestern Dr             398-51-8185

We know that the first witness lives at the last house on Northwestern Dr.

Part 2: The Challenge

Find our First Witness

SELECT *
FROM person p
WHERE p.address_street_name = 'Northwestern Dr'
ORDER BY address_number

id    person_name     address_number  address_street_name         person_ssn
10000 Frances Andrews 1               Northwestern Dr             101-01-0101
10001 William Preston 1               Northwestern Dr             111-11-1111
10002 Theodore Logan  3               Northwestern Dr             222-22-2222
89906 Kinsey Erickson 309             Northwestern Dr             635-28-7661 
22239 Dusty Sigafus   1125            Northwestern Dr             724-38-6723 
96178 Galina Khuu     2183            Northwestern Dr             398-51-8185
28360 Rashad Cascone  3212            Northwestern Dr             838-90-7424
53890 Sophie Tiberio  3755            Northwestern Dr             442-83-0147
14887 Richard Hammill 4919            Northwestern Dr             111-56-4949

We know that the first witness lives at the last house on Northwestern Dr.

Part 2: The Challenge

Find our First Witness

SELECT *
FROM person p
WHERE p.address_street_name = 'Northwestern Dr'
ORDER BY address_number DESC

id    person_name     address_number  address_street_name         person_ssn
14887 Richard Hammill 4919            Northwestern Dr             111-56-4949
53890 Sophie Tiberio  3755            Northwestern Dr             442-83-0147
28360 Rashad Cascone  3212            Northwestern Dr             838-90-7424
96178 Galina Khuu     2183            Northwestern Dr             398-51-8185
22239 Dusty Sigafus   1125            Northwestern Dr             724-38-6723
89906 Kinsey Erickson  309            Northwestern Dr             635-28-7661
10002 Theodore Logan     3            Northwestern Dr             222-22-2222
10000 Frances Andrews    1            Northwestern Dr             101-01-0101
10001 William Preston    1            Northwestern Dr             111-11-1111

We know that the first witness lives at the last house on Northwestern Dr.

Part 2: The Challenge

Find our First Witness

SELECT TOP 1 *
FROM person p
WHERE p.address_street_name = 'Northwestern Dr'
ORDER BY address_number DESC

id    person_name     address_number  address_street_name         person_ssn
14887 Richard Hammill 4919            Northwestern Dr             111-56-4949

We know that the first witness lives at the last house on Northwestern Dr.

Part 2: The Challenge

Sidebar

Aggregation = a cluster of things

Subquery = a query inside of another query

SELECT max(address_number)
FROM person p
WHERE p.address_street_name = 'Northwestern Dr'

SELECT *
FROM person p
WHERE p.address_street_name = 'Northwestern Dr'
    AND p.address_number = (
        SELECT max(address_number) 
        FROM person
        WHERE address_street_name = 'Northwestern Dr'
    )

SELECT max(address_number)
FROM person p
WHERE p.address_street_name = 'Northwestern Dr'
GROUP BY p.address_number

SELECT max(address_number)
FROM person p
WHERE p.address_street_name = 'Northwestern Dr'

Part 2: The Challenge

Sidebar

Correlated Subquery = subquery relates to the outer query

SELECT *
FROM person p1
WHERE p1.address_street_name = 'Northwestern Dr'
    AND p1.address_number = (
        SELECT max(p2.address_number) 
        FROM person p2
        WHERE p2.address_street_name =
		    p1.address_street_name
    )

Subquery requires outer query to run.

https://www.mssqltips.com/sqlservertip/6037/sql-server-uncorrelated-and-correlated-subquery/

Part 2: The Challenge

Sidebar

Un-Correlated Subquery = subquery doesn't relate to the outer query

SELECT *
FROM person p1
WHERE p1.address_street_name = 'Northwestern Dr'
    AND p1.address_number = (
        SELECT max(p2.address_number) 
        FROM person p2
        WHERE p2.address_street_name = 'Northwestern Dr' 
    )

Subquery can be run by itself.

https://www.mssqltips.com/sqlservertip/6037/sql-server-uncorrelated-and-correlated-subquery/

Part 2: The Challenge

Sidebar

A subquery can also be run in the SELECT.

SELECT p1.*,
    (
        SELECT COUNT(*)
        FROM person p2
        WHERE p2.address_street_name = p1.address_street_name
            AND p2.address_number = p1.address_number
    ) AS residentCount
FROM person p1

id    person_name      address_number  address_street_name  ...  residentCount
83010 Dorena OFeeny    8182            Northwestern Ln      ...       1
95678 Eachelle Sherlaw  103            Franklin Ave         ...       4
37680 Webster Kahan      55            Nova Ave             ...       1
10001 William Preston     1            Northwestern Dr      ...       2
........

Part 2: The Challenge

Sidebar

A subquery can also be run in the FROM.

SELECT *
FROM (
    SELECT id, person_name, address_number, address_street_name
    FROM person p
    WHERE p.address_street_name = 'Northwestern Dr'
) s1

id    person_name     address_number  address_street_name
10000 Frances Andrews 1               Northwestern Dr    
89906 Kinsey Erickson 309             Northwestern Dr    
10001 William Preston 1               Northwestern Dr    
10002 Theodore Logan  3               Northwestern Dr    
14887 Richard Hammill 4919            Northwestern Dr    
22239 Dusty Sigafus   1125            Northwestern Dr    
28360 Rashad Cascone  3212            Northwestern Dr    
53890 Sophie Tiberio  3755            Northwestern Dr    
96178 Galina Khuu     2183            Northwestern Dr

Part 2: The Challenge

Use Window Functions!

We know that the first witness lives at the last house on Northwestern Dr.

SELECT p.id, p.person_name, p.address_number
      , ROW_NUMBER() OVER (ORDER BY address_number DESC) AS rn
FROM person p
WHERE p.address_street_name = 'Northwestern Dr'

id    person_name        address_number rn    
14887    Richard Hammill 4919            1
53890    Sophie Tiberio  3755            2
28360    Rashad Cascone  3212            3
96178    Galina Khuu     2183            4
22239    Dusty Sigafus   1125            5
89906    Kinsey Erickson 309             6
10002    Theodore Logan  1               7
10000    Frances Andrews 1               8
10001    William Preston 1               9

SELECT s1.id, s1.person_name
FROM (
    SELECT p.id, p.person_name
      , ROW_NUMBER() OVER (ORDER BY address_number DESC) AS rn
    FROM person p
    WHERE p.address_street_name = 'Northwestern Dr'
) s1
WHERE rn = 1

id    person_name     
14887 Richard Hammill

Part 2: The Challenge

Find our First Witness

id    person_name     address_number  address_street_name         person_ssn
14887 Richard Hammill 4919            Northwestern Dr             111-56-4949

We know that the first witness lives at the last house on Northwestern Dr.

SELECT *
FROM person p1
WHERE p1.address_street_name = 'Northwestern Dr'
    AND p1.address_number = (
        SELECT max(p2.address_number) 
        FROM person p2
        WHERE p2.address_street_name = 'Northwestern Dr' 
    )

Part 2: The Challenge

Find our First Witness

Our first witness is

Richard Hammill (ID 14887)

Part 2: The Challenge

Find our Second Witness

SELECT *
FROM person p
WHERE p.person_name = 'Frances'

id    person_name     address_number  address_street_name         person_ssn

We know that the second witness is named Frances and lives somewhere on Franklin Ave with 3 roommates.

Part 2: The Challenge

Find our Second Witness

SELECT TOP 3 *
FROM person p

id    person_name         address_number  address_street_name  person_ssn
20578 Frances Cronk        115            Franklin Ave         629-16-3792
47097 Vladamir Charrisson 3892            Ronald Regan Pl      510-66-0478
99234 Ezechiel Frawley    4157            Corry Pass           343-92-1292

What does the data look like?

This is bad database design.

Part 2: The Challenge

Find our Second Witness

SELECT *
FROM person p
WHERE p.person_name LIKE 'Frances'

id    person_name     address_number  address_street_name         person_ssn

We know that the second witness is named Frances ...

Part 2: The Challenge

Sidebar

Wildcards for LIKE

% Any number of characters, including zero characters
_ Exactly one character of any type
[ ] Exactly one character within this list of characters
^ Inverse of the following selection

Multiple wildcards can be used.

Part 2: The Challenge

Find our Second Witness

We know that the second witness is named Frances ...

SELECT *
FROM person p
WHERE p.person_name LIKE 'Frances%'

id    person_name        address_number  address_street_name  person_ssn
20578 Frances Cronk      115             Franklin Ave         629-16-3792
10000 Frances Andrews    1               Northwestern Dr      101-01-0101
16371 Frances Fisher     103             Franklin Ave         318-77-1143
94769 Francesca Moar     1714            Evergreen Pt         634-73-5584
78354 Frances Siona      978             Whitewater Dr        800-27-8294
78799 Frances Droneburg  1944            W Natalie  Dr        478-79-3500
86541 Francesca Zwilling 9992            Franklin Ave         332-96-1158

Part 2: The Challenge

Find our Second Witness

We know that the second witness is named Frances ...

SELECT *
FROM person p
WHERE p.person_name LIKE 'Frances %'

id    person_name        address_number  address_street_name  person_ssn
20578 Frances Cronk      115             Franklin Ave         629-16-3792
10000 Frances Andrews    1               Northwestern Dr      101-01-0101
16371 Frances Fisher     103             Franklin Ave         318-77-1143
78354 Frances Siona      978             Whitewater Dr        800-27-8294
78799 Frances Droneburg  1944            W Natalie  Dr        478-79-3500

SELECT *
FROM person p
WHERE p.person_name LIKE 'Frances[ ]%'

SELECT *
FROM person p
WHERE p.person_name LIKE 'Frances %'

Part 2: The Challenge

Find our Second Witness

We know that the second witness is named Frances
and lives somewhere on Franklin Ave ...

SELECT p.id, p.person_name
FROM person p
WHERE p.person_name LIKE 'Frances %'
    AND p.address_street_name = 'Franklin Ave'

id    person_name    
20578 Frances Cronk  
16371 Frances Fisher

Part 2: The Challenge

Find our Second Witness

We know that the second witness is named Frances
and lives somewhere on Franklin Ave
with 3 roommates.

SELECT *, count(*) AS residentCount
FROM person p
WHERE p.address_street_name = 'Franklin Ave'
GROUP BY p.address_number

Column 'person.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Part 2: The Challenge

Find our Second Witness

We know that the second witness is named Frances
and lives somewhere on Franklin Ave
with 3 roommates.

SELECT address_number, count(*) AS residentCount
FROM person p
WHERE p.address_street_name = 'Franklin Ave'
GROUP BY p.address_number

address_number    residentCount
103               4
115               1
1062              4
2032              1
2556              1
3015              1
9992              1

Part 2: The Challenge

Find our Second Witness

We know that the second witness is named Frances
and lives somewhere on Franklin Ave
with 3 roommates.

SELECT address_number, count(*) AS residentCount
FROM person p
WHERE p.address_street_name = 'Franklin Ave'
GROUP BY p.address_number
HAVING residentCount = 4

Invalid column name 'residentCount'.

Part 1: The Very Basics

LEXICAL

Order of Evaluation (a bit more)

SELECT TOP n DISTINCT col1, aggregate(col2)

WINDOW

FROM table (...JOIN table2)

WHERE colX = 42

GROUP BY col1

HAVING col1 > 3

UNION [ALL] / INTERSECT / EXCEPT

ORDER BY col1

LIMIT/FETCH ...

How We Write It

LOGICAL

FROM table (...JOIN table2)

WHERE colX = 42

GROUP BY col1

aggregate(col2)

HAVING col1 > 3

WINDOW

SELECT col1

DISTINCT

UNION [ALL] / INTERSECT / EXCEPT

ORDER BY col1

LIMIT / FETCH / TOP

How SQL Sees It

https://www.eversql.com/sql-order-of-operations-sql-query-order-of-execution/
https://blog.jooq.org/a-beginners-guide-to-the-true-order-of-sql-operations/

Part 2: The Challenge

Find our Second Witness

SELECT address_number
FROM person p
WHERE p.address_street_name = 'Franklin Ave'
GROUP BY p.address_number
HAVING count(*) = 4 /* 1 witness + 3 roommates */

address_number
103
1062

We know that the second witness is named Frances
and lives somewhere on Franklin Ave
with 3 roommates.

Part 2: The Challenge

Find our Second Witness

We know that the second witness is named Frances
and lives somewhere on Franklin Ave
with 3 roommates.

SELECT p1.id, p1.person_name
FROM person p1
WHERE p1.person_name LIKE 'Frances %'
    AND p1.address_street_name = 'Franklin Ave'
    AND p1.address_number = (
        SELECT p2.address_number
        FROM person p2
        WHERE p2.address_street_name = p1.address_street_name
        GROUP BY p.address_number
        HAVING count(*) = 4 /* 1 witness + 3 roommates */
    }

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Part 2: The Challenge

Find our Second Witness

We know that the second witness is named Frances
and lives somewhere on Franklin Ave
with 3 roommates.

SELECT p1.id, p1.person_name
FROM person p1
WHERE p1.person_name LIKE 'Frances %'
    AND p1.address_street_name = 'Franklin Ave'
    AND p1.address_number IN (
        SELECT p2.address_number
        FROM person p2
        WHERE p2.address_street_name = p1.address_street_name
        GROUP BY p.address_number
        HAVING count(*) = 4 /* 1 witness + 3 roommates */
    )

id       person_name
16371    Frances Fisher

Part 2: The Challenge

Find our Second Witness

We know that the second witness is named Frances
and lives somewhere on Franklin Ave
with 3 roommates.

SELECT s1.id, s1.person_name
FROM (
    SELECT p1.id, p1.person
        , COUNT(*) OVER (PARTITION BY p1.address_number) AS residentCount
    FROM person p1
    WHERE p1.address_street_name = 'Franklin Ave'
) s1
WHERE s1.person_name LIKE 'Frances %'
    AND residentCount = 4

id       person_name
16371    Frances Fisher

Part 2: The Challenge

Find our Second Witness

Our second witness is

Frances Fisher (ID 16371)

Part 2: The Challenge

Combine our Witnesses

UNION = Used to combine the results of two queries. Eliminates duplicate rows.

UNION ALL = Just like a regular UNION, but includes all duplicate rows.

All queries in the UNION must have the same number of columns and the datatypes of those columns must match. The column names in the results are inherited from the first query in the UNION.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql

Part 2: The Challenge

Sidebar (UNIONS)

SELECT p1.id, p1.person_name, person_ssn
FROM person p1
WHERE p1.id = 14887

UNION ALL

SELECT p2.person_name, p2.id, p2.person_ssn
FROM person p2
WHERE p2.id = 16371

Queries must have the same number of columns and, while column names don't have to match, they do have to be a compatible datatype. Final names are inherited from the first query.

Conversion failed when converting the varchar value 'Richard Hammill' to data type int.

Part 2: The Challenge

Sidebar (UNIONS)

SELECT p1.id AS thisID, p1.person_name AS thatName
    , person_ssn AS someSSN
FROM person p1
WHERE p1.id = 14887

UNION ALL

SELECT p2.id, p2.person_name, p2.person_ssn
FROM person p2
WHERE p2.id = 16371

Queries must have the same number of columns and, while column names don't have to match, they do have to be a compatible datatype. Final names are inherited from the first query.

thisID    thatName        someSSN
14887     Richard Hammill 111-56-4949
16371     Frances Fisher  318-77-1143

Part 2: The Challenge

Sidebar (UNIONS)

SELECT p1.id, p1.person_name, person_ssn
FROM person p1
WHERE p1.id = 14887

UNION ALL

SELECT p2.id, p2.person_ssn, p2.person_name
FROM person p2
WHERE p2.id = 16371

Queries must have the same number of columns and, while column names don't have to match, they do have to be a compatible datatype. Final names are inherited from the first query.

id        person_name     person_ssn
14887     Richard Hammill 111-56-4949
16371     318-77-1143     Frances Fisher

WATCH OUT! Compatible datatypes won't throw an error

Part 2: The Challenge

Combine our Witnesses

SELECT TOP 1 p1.id, p1.person_name
FROM person p1
WHERE p1.address_street_name = 'Franklin Ave'
ORDER BY p1.address_number DESC

UNION ALL

SELECT p1.id, p1.person_name
FROM person p1
WHERE p1.person_name LIKE 'Frances %'
    AND p1.address_street_name = 'Franklin Ave'
    AND p1.address_number IN (
        SELECT p2.address_number
        FROM person p2
        WHERE p2.address_street_name = 'Franklin Ave'
        GROUP BY p.address_number
        HAVING count(*) = 4 /* 1 witness + 3 roommates */
    )

Incorrect syntax near the keyword 'UNION'.

Part 2: The Challenge

Combine our Witnesses

SELECT p1.id, p1.person_name
FROM person p1
WHERE p1.person_name LIKE 'Frances %'
    AND p1.address_street_name = 'Franklin Ave'
    AND p1.address_number IN (
        SELECT p2.address_number
        FROM person p2
        WHERE p2.address_street_name = 'Franklin Ave'
        GROUP BY p.address_number
        HAVING count(*) = 4 /* 1 witness + 3 roommates */
    )

UNION ALL

SELECT TOP 1 p1.id, p1.person_name
FROM person p1
WHERE p1.address_street_name = 'Franklin Ave'
ORDER BY p1.address_number DESC

id       person_name
16371    Frances Fisher
10000    Frances Andrews

Part 1: The Very Basics

LEXICAL

Order of Evaluation (a bit more)

SELECT TOP n DISTINCT col1, aggregate(col2)

WINDOW

FROM table (...JOIN table2)

WHERE colX = 42

GROUP BY col1

HAVING col1 > 3

UNION [ALL] / INTERSECT / EXCEPT

ORDER BY col1

LIMIT/FETCH ...

How We Write It

LOGICAL

FROM table (...JOIN table2)

WHERE colX = 42

GROUP BY col1

aggregate(col2)

HAVING col1 > 3

WINDOW

SELECT col1

DISTINCT

UNION [ALL] / INTERSECT / EXCEPT

ORDER BY col1

LIMIT / FETCH / TOP

How SQL Sees It

https://www.eversql.com/sql-order-of-operations-sql-query-order-of-execution/
https://blog.jooq.org/a-beginners-guide-to-the-true-order-of-sql-operations/

Part 2: The Challenge

Combine our Witnesses

DERIVED TABLE = It is essentially a subquery within the FROM clause that is used to generate a result set that can't be processed within the normal logical processing of a query. It can be used like a virtual table.

https://www.mssqltips.com/sqlservertip/6038/sql-server-derived-table-example/

Part 2: The Challenge

Combine our Witnesses

Use a derived table to get Richard's record.

SELECT s1.id, s1.person_name
FROM (
    /* SELECT RICHARD */
    SELECT TOP 1 p1.id, p1.person_name
    FROM person p1
    WHERE p1.address_street_name = 'Franklin Ave'
    ORDER BY p1.address_number DESC
) s1

id       person_name
14887    Richard Hammill

Part 2: The Challenge

Combine our Witnesses

SELECT s1.id, s1.person_name
FROM (
    SELECT TOP 1 p1.id, p1.person_name
    FROM person p1
    WHERE p1.address_street_name = 'Franklin Ave'
    ORDER BY p1.address_number DESC
) s1
UNION ALL
SELECT p1.id, p1.person_name
FROM person p1
WHERE p1.person_name LIKE 'Frances %'
    AND p1.address_street_name = 'Franklin Ave'
    AND p1.address_number IN (
        SELECT p2.address_number
        FROM person p2
        WHERE p2.address_street_name = 'Franklin Ave'
        GROUP BY p.address_number
        HAVING count(*) = 4 /* 1 witness + 3 roommates */
    )

id       person_name
14887    Richard Hammill
16371    Frances Fisher

Part 2: The Challenge

Combine our Witnesses

SELECT p1.id, p1.person_name
FROM person p1
WHERE p1.address_street_name = 'Northwestern Dr'
    AND p1.address_number  = (
        SELECT max(p2.address_number)
        FROM person p2
        WHERE p2.address_street_name = 'Northwestern Dr'
    )
UNION ALL
SELECT p3.id, p3.person_name
FROM person p3
WHERE p3.person_name LIKE 'Frances %'
    AND p3.address_street_name = 'Franklin Ave'
    AND p3.address_number IN (
        SELECT p4.address_number
        FROM person p4
        WHERE p4.address_street_name = 'Franklin Ave'
        GROUP BY p4.address_number
        HAVING count(*) = 4 /* 1 witness + 3 roommates */
    )

id       person_name
14887    Richard Hammill
16371    Frances Fisher

Part 2: The Challenge

Find our Witness Interviews

Part 2: The Challenge

Find our Witness Interiews

SELECT *
FROM interview i
INNER JOIN person p ON i.person_id = p.id

id  person_id transcript                          interview_date .... id    person_name ...
33  11173     I was standing on the grassy knoll  1963-11-24          11173 Ken Whitted 
              to watch the parade. I clearly saw 
              the shooter. It was REDACTED REDACTED. 
30  13064     I walked into the store just as     2018-01-16          13064 Dion Montague
              the robber was snatching money out  
              of the drawer. ...    
40  14265     It was all in self defense. But I   2018-01-15          14265 Marley Roberts
              didn't shoot that Deputy. 
66  16371     I did it. I'm ready to face ...     2017-07-02          16371 Frances Fisher 
32  19825     I was in the library with my        2018-01-18          19825 Willian Menjiva
              friend Col. Mustard. A woman dressed 
              like a maid ran in and beat him with 
              a candlestick.
.....................

Let's look at our Interviews.

Part 2: The Challenge

Find our Witness Interiews

SELECT p.person_id, p.person_name, i.interview_date,
    i.transcript
FROM interview i
INNER JOIN person p ON i.person_id = p.id
WHERE i.person_id IN (14887,16371)

person_id person_name     interview_date transcript                          
14887     Richard Hammill 2018-01-15     I heard a gunshot and then saw a man run out. He
                                         had a "Globogym" bag. The membership number on 
                                         the bag started with "48Z". The man got into a 
                                         car with a plate that included "H42W". He 
                                         dropped a wallet that was empty except for 3 
                                         receipts to "MOVIE NIGHT: Twilight".
16371     Frances Fisher  2018-01-16     I saw the murder happen, and I recognized the
                                         killer from my gym. He was working out near 
                                         me in the Gold Members area yesterday.
16371     Frances Fisher  2017-07-02     I did it. I'm ready to face the music.

Part 2: The Challenge

Find our Witness Interiews

SELECT p.person_id, p.person_name, i.interview_date, i.transcript
FROM interview i
INNER JOIN ( 
    SELECT p1.id, p1.person_name
    FROM person p1
    WHERE p1.address_street_name = 'Northwestern Dr'
        AND p1.address_number = (
            SELECT max(p2.address_number)
            FROM person p2
            WHERE p2.address_street_name = 'Northwestern Dr'
        )
    UNION ALL
    SELECT p3.id, p3.person_name
    FROM person p3
    WHERE p3.person_name LIKE 'Frances %'
        AND p3.address_street_name = 'Franklin Ave'
        AND p3.address_number IN (
            SELECT p4.address_number
            FROM person p4
            WHERE p4.address_street_name = 'Franklin Ave'
            GROUP BY p4.address_number
            HAVING count(*) = 4
        )
) s1 ON i.person_id = s1.id

Did Frances confess?

person_id person_name     interview_date  transcript                          
14887     Richard Hammill  2018-01-12     I heard a gunshot and then saw a man run out. He
                                          had a "Globogym" bag. The membership number on 
                                          the bag started with "48Z". The man got into a 
                                          car with a plate that included "H42W". He 
                                          dropped a wallet that was empty except for 3 
                                          receipts to "MOVIE NIGHT: Twilight".
16371     Frances Fisher   2018-01-10     I saw the murder happen, and I recognized the
                                          killer from my gym. He was working out near 
                                          me in the Gold Members area yesterday.
16371     Frances Fisher   2017-07-02     I did it. I'm ready to face the music.

Wait. That's not the right date.

Part 2: The Challenge

Find our Witness Interiews

SELECT p.person_id, p.person_name, i.interview_date, i.transcript
FROM interview i
INNER JOIN ( 
    SELECT p1.id, p1.person_name
    FROM person p1
    WHERE p1.address_street_name = 'Northwestern Dr'
        AND p1.address_number = (
            SELECT max(p2.address_number)
            FROM person p2
            WHERE p2.address_street_name = 'Northwestern Dr'
        )
UNION ALL
SELECT p3.id, p3.person_name
FROM person p3
WHERE p3.person_name LIKE 'Frances %'
    AND p3.address_street_name = 'Franklin Ave'
    AND p3.address_number IN (
        SELECT p4.address_number
        FROM person p4
        WHERE p4.address_street_name = 'Franklin Ave'
        GROUP BY p4.address_number
        HAVING count(*) = 4
    )
) s1 ON i.person_id = s1.id
WHERE i.interview_date >= '2018-01-10'
    AND i.interview_date <= DATEADD(day,3,'2018-01-10')

person_id person_name     interview_date  transcript                          
14887     Richard Hammill  2018-01-12     I heard a gunshot and then saw a man run out. He
                                          had a "Globogym" bag. The membership number on 
                                          the bag started with "48Z". The man got into a 
                                          car with a plate that included "H42W". He 
                                          dropped a wallet that was empty except for 3 
                                          receipts to "MOVIE NIGHT: Twilight".
16371      Frances Fisher  2018-01-10     I saw the murder happen, and I recognized the
                                          killer from my gym. He was working out near 
                                          me in the Gold Members area yesterday.

Part 2: The Challenge

THE GAME IS AFOOT!

Find our Suspect

THE GAME IS AFOOT!

Part 2: The Challenge

Find our Suspect

What we know:

Richard heard a gunshot, and Frances saw the murder happen.
Richard says the suspect is a man
Frances recognized the killer from her gym when she was there "yesterday".
- Frances says the suspect is a Gold Member
Richard says the killer had a Globogym bag with a membership number that starts with "48Z".
Richard saw the killer get into a car with a license plate that included "H42W".

Part 2: The Challenge

Find our Suspect

Frances' Statement:

She recognized the killer from her gym when she was there "yesterday".
She says the suspect is a Gold Member

Part 2: The Challenge

Find our Suspect

SELECT gm.membership_id, gci.check_in_dt, gci.check_out_dt
FROM globogym_member gm
LEFT OUTER JOIN globogym_check_in gci ON gm.id = gci.membership_id 
    AND gci.check_in_dt >= '2018-01-09 00:00:00.000'
    AND gci.check_in_dt <= '2018-01-09 23:59:59.999'
WHERE gm.person_id = 16371

When was Frances at the gym?

Part 2: The Challenge

Find our Suspect

SELECT *
FROM table t
WHERE myDate >= '2018-01-09'
    AND myDate < '2018-01-10'

A Common Pattern in Date Filtering

SELECT *
FROM table t
WHERE myDate >= '2018-01-09 00:00:00.000'
    AND myDate <= '2018-01-09 23:59:59.999'

SELECT *
FROM table t
WHERE myDate BETWEEN '2018-01-09 00:00:00.000'
    AND '2018-01-09 23:59:59.999'

Part 2: The Challenge

Find our Suspect

SELECT gm.membership_id, gci.check_in_dt, gci.check_out_dt
FROM globogym_member gm
LEFT OUTER JOIN globogym_check_in gci ON gm.id = gci.membership_id 
    AND gci.check_in_dt >= '2018-01-09 00:00:00.000'
    AND gci.check_in_dt <= '2018-01-09 23:59:59.999'
WHERE gm.person_id = 16371

When was Frances at the gym?

membership_id   check_in_dt                 check_out_dt
90081           2018-01-09 16:00:16.160     2018-01-09 17:15:15.170
90081           2018-01-09 04:00:00.000     2018-01-09 05:30:00.000

Part 2: The Challenge

Find our Suspect

SELECT gci.membership_id, gci.check_in_dt, gci.check_out_dt
FROM globogym_check_in gci
INNER JOIN (
    SELECT gm.membership_id, gci2.check_in_dt, gci2.check_out_dt
    FROM globogym_member gm
    INNER JOIN globogym_check_in gci2 ON gm.id = gci2.membership_id 
        AND gci2.check_in_dt >= '2018-01-09 00:00:00.000'
        AND gci2.check_in_dt <= '2018-01-16 23:59:59.999'
    WHERE gm.person_id = 16371 /* Frances */
) ac ON
        gci.check|in_dt <= ac.check_out_dt /* Before Frances */
        AND
        gci.check_out_dt >= ac.check_in_dt /* After Frances */
WHERE gci.membership_id <> ac.membership_id /* Eliminate Frances */

Who was at the gym with Frances?

Part 2: The Challenge

Find our Suspect

SELECT *
FROM table t1
INNER JOIN ( /* Our search dates */
    SELECT t2.id, t2.startDate, t2.endDate
    FROM table t2 
    WHERE t2.myDate >= [START DATE]
        AND t2.myDate < [END DATE]
) s1 ON 
    t1.startDate <= s1.endDate
    AND 
    t1.endDate >= s1.startDate
WHERE s1.id <> t1.id /* Eliminate search record */

A Common Pattern to Find Date Overlaps

Part 2: The Challenge

Find our Suspect

SELECT gci.membership_id, gci.check_in_dt, gci.check_out_dt
FROM globogym_check_in gci
INNER JOIN (
    SELECT gm.membership_id, gci2.check_in_dt, gci2.check_out_dt
    FROM globogym_member gm
    INNER JOIN globogym_check_in gci2 ON gm.id = gci2.membership_id 
        AND gci2.check_in_dt >= '2018-01-09 00:00:00.000'
        AND gci2.check_in_dt <= '2018-01-16 23:59:59.999'
    WHERE gm.person_id = 16371 /* Frances */
) ac ON
        gci.check|in_dt <= ac.check_out_dt /* Before Frances */
        AND
        gci.check_out_dt >= ac.check_in_dt /* After Frances */
WHERE gci.membership_id <> ac.membership_id /* Eliminate Frances */

Who was at the gym with Frances?

membership_id   check_in_dt             check_out_dt
48ZQQ           2018-01-09 16:40:44.567 2018-01-09 17:00:23.437
48Z55           2018-01-09 15:30:52.000 2018-01-09 17:00:47.273
66AAEC          2018-01-09 15:55:30.907 2018-01-09 17:38:29.910
48Z38           2018-01-09 15:30:36.820 2018-01-09 17:00:15.000
EA3FTM          2018-01-09 16:12:47.857 2018-01-09 18:37:15.953
48ZA2           2018-01-09 16:10:10.997 2018-01-09 17:00:17.307
GKZV7           2018-01-09 15:20:05.830 2018-01-09 17:45:54.457
1YHL3           2018-01-09 15:30:12.347 2018-01-09 16:45:33.633
3BRSC           2018-01-09 15:45:00.003 2018-01-09 17:00:21.110
A5N3S           2018-01-09 15:00:51.020 2018-01-09 17:00:35.553
QH7N6Y          2018-01-09 13:56:00.997 2018-01-09 16:08:45.863
A90TX           2018-01-09 16:13:53.837 2018-01-09 17:50:03.987
RIAP3O          2018-01-14 14:23:13.957 2018-01-14 15:20:38.883
EA3FTM          2018-01-14 14:44:52.840 2018-01-14 16:36:00.997
RA9XC6          2018-01-14 13:28:08.973 2018-01-14 14:22:31.903
KNG99D          2018-01-14 13:45:07.977 2018-01-14 14:20:35.893
SWW7D           2018-01-14 14:02:13.957 2018-01-14 16:02:26.920
A90TX           2018-01-11 11:32:06.980 2018-01-11 13:26:48.853
RMZKKA          2018-01-11 12:20:17.947 2018-01-11 13:42:28.913
6FDOTC          2018-01-11 12:27:55.833 2018-01-11 13:39:32.900

Part 2: The Challenge

Find our Suspect

SELECT p.person_name, gci.membership_id, gci.check_in_dt, gci.check_out_dt
FROM globogym_check_in gci
INNER JOIN ( /* Frances' Checkin */
    SELECT gm.membership_id, gci2.check_in_dt, gci2.check_out_dt
    FROM globogym_member gm
    INNER JOIN globogym_check_in gci2 ON gm.id = gci2.membership_id 
        AND gci2.check_in_dt >= '2018-01-09 00:00:00.000'
        AND gci2.check_in_dt <= '2018-01-16 23:59:59.999'
    WHERE gm.person_id = 16371
) ac ON 
        gci.check_in_dt <= ac.check_out_dt /* Before Frances */
        AND
        gci.check_out_dt >= ac.check_in_dt /* After Frances */
INNER JOIN globogym_member gm ON gci.membership_it = gm.id
    AND gm.membership_status = 'Gold'
INNER JOIN person p ON gm.person_id = p.id
WHERE gci.membership_id <> ac.membership_id /* Eliminate Frances */

Who was at the gym with Frances?

person_name     membership_id    check_in_dt    check_out_dt
Taylor Skyes    3BRSC    2018-01-09 15:45:00    2018-01-09 17:00:21
Roslyn Gonzaga  48ZA2    2018-01-09 16:10:10    2018-01-09 17:00:17
Mary Cameron    A5N3S    2018-01-09 15:00:51    2018-01-09 17:00:35
Jude Fairbairn  GKZV7    2018-01-09 15:20:05    2018-01-09 17:45:54
Greg Alwardt    1YHL3    2018-01-09 15:30:12    2018-01-09 16:45:33
Ramiro Matthias SWW7D    2018-01-14 14:02:13    2018-01-14 16:02:26
Jack Torrence   48Z55    2018-01-09 15:30:52    2018-01-09 17:00:47
Maggie Tyrer    KNG99D   2018-01-14 13:45:07    2018-01-14 14:20:35

Part 2: The Challenge

Find our Suspect

Common Table Expression (CTE) = It is a temporary named result set created during the execution of a query, that can be used to repeat functionality during a query. They can also be used recursively to create powerful analytics. It is only available to the query in which it is defined. They are defined with the WITH clause. Multiple CTEs can be used in a single query.

https://learnsql.com/blog/what-is-common-table-expression/

Part 2: The Challenge

Find our Suspect

; WITH aci AS (
    /* Frances' Checkin */
    SELECT gm.membership_id, gci2.check_in_dt, gci2.check_out_dt
    FROM globogym_member gm
    INNER JOIN globogym_check_in gci2 ON gm.id = gci2.membership_id 
        AND gci2.check_in_dt >= '2018-01-09 00:00:00.000'
        AND gci2.check_in_dt <= '2018-01-16 23:59:59.999'
    WHERE gm.person_id = 16371
) 
SELECT p.person_name, gci.membership_id, gci.check_in_dt, gci.check_out_dt
FROM globogym_check_in gci1
INNER JOIN aci ON
    gci2.check_in_dt <= aci.check_out_dt /* Before Frances */
    AND
    gci2.check_out_dt >= aci.check_in_dt /* After Frances */
INNER JOIN globogym_member gm ON gci.membership_it = gm.id
    ANDgm.membership_status = 'Gold'
INNER JOIN person p ON gm.person_id = p.id
WHERE gci.membership_id <> aci.membership_id /* Eliminate Frances */

Who was at the gym with Frances? CTE Version

person_name     membership_id    check_in_dt            check_out_dt
Taylor Skyes    3BRSC            2018-01-09 15:45:00    2018-01-09 17:00:21
Roslyn Gonzaga  48ZA2            2018-01-09 16:10:10    2018-01-09 17:00:17
Mary Cameron    A5N3S            2018-01-09 15:00:51    2018-01-09 17:00:35
Jude Fairbairn  GKZV7            2018-01-09 15:20:05    2018-01-09 17:45:54
Greg Alwardt    1YHL3            2018-01-09 15:30:12    2018-01-09 16:45:33
Ramiro Matthias SWW7D            2018-01-14 14:02:13    2018-01-14 16:02:26
Jack Torrence   48Z55            2018-01-09 15:30:52    2018-01-09 17:00:47
Maggie Tyrer    KNG99D           2018-01-14 13:45:07    2018-01-14 14:20:35

Part 2: The Challenge

Find our Suspect

VIEW = It is a virtual table created from a query definition that is stored in the database, and can be used just like a regular table. They can be used to simplify basic queries by combining multiple JOINs into a simple SELECT operation.

https://learn.microsoft.com/en-us/sql/relational-databases/views/views

Part 2: The Challenge

Find our Suspect

CREATE OR ALTER VIEW vw_Frances_Suspects /* SQL Server 2016 SP1+ Syntax */
AS
    SELECT p.id AS person_id, p.person_name, gci.membership_id
	FROM globogym_check_in gci
	INNER JOIN ( /* Frances' Checkin */
        SELECT gm.membership_id, gci2.check_in_dt, gci2.check_out_dt
        FROM globogym_member gm
        INNER JOIN globogym_check_in gci2 ON gm.id = gci2.membership_id 
            AND gci2.check_in_dt >= '2018-01-09 00:00:00.000'
            AND gci2.check_in_dt <= '2018-01-16 23:59:59.999'
        WHERE gm.person_id = 16371
    ) ac ON 
            gci.check_in_dt <= ac.check_out_dt /* Before Frances */
            AND
            gci.check_out_dt >= ac.check_in_dt /* After Frances */
    INNER JOIN globogym_member gm ON gci.membership_it = gm.id
        AND gm.membership_status = 'Gold'
    INNER JOIN person p ON gm.person_id = p.id
    WHERE gci.membership_id <> ac.membership_id /* Eliminate Frances */

Create a View

Part 2: The Challenge

Find our Suspect

SELECT * FROM vw_Frances_Suspects

Use a View

person_id person_name     membership_id
19948     Taylor Skyes    3BRSC
20716     Roslyn Gonzaga  48ZA2
24541     Mary Cameron    A5N3S
30221     Jude Fairbairn  GKZV7
32045     Greg Alwardt    1YHL3
49568     Ramiro Matthias SWW7D
67318     Jack Torrence   48Z55
60700     Maggie Tyrer    KNG99D

Part 2: The Challenge

Find our Suspect

Richard's Statement:

He says the suspect is a man.
He says the killer had a Globogym bag with a membership number that starts with "48Z".
He saw the killer get into a car with a license plate that included "H42W".

Part 2: The Challenge

Find our Suspect

SELECT *
FROM globogym_member gm
WHERE gm.id LIKE '48Z%'

Check Gym Member Numbers

id    person_id member_name membership_start_date membership_status
48ZA2 20716     RG          2017-08-09            Gold
48Z7A 28819     JG          2016-03-05            Gold
48Z54 37213     FF          2016-01-01            Silver
48Z38 49550     TB          2017-02-03            Silver
48Z55 67318     JT          2016-01-01            Gold
48ZQQ 78354     FS          2016-02-05            Silver

Part 2: The Challenge

Find our Suspect

SELECT gm.id, p.person_name, gm.person_id, gm.membership_status, dl.gender
FROM globogym_member gm
INNER JOIN person p ON gm.person_id = p.id
INNER JOIN drivers_license dl ON gm.person_id = dl.person_id
    AND dl.gender = 'Male'
WHERE gm.id LIKE '48Z%'

Check Gym Member Number and Gender

id    person_name   person_id membership_status gender
48Z7A Joe Germuska  28819     Gold              Male
48Z54 Fritz Filteau 37213     Silver            Male
48Z38 Tomas Baisley 49550     Silver            Male
48Z55 Jack Torrence 67318     Gold              Male

Part 2: The Challenge

Find our Suspect

SELECT *
FROM vehicle v
WHERE v.plate_number LIKE '%H42W%'

Check Vehicle License Plates

plate_number owner_id    car_make        car_model
HH42WI       7330363628  Lexus           GS
00H42W       1097173289  Mercedes-Benz   E-Class
QH42W6       9810450248  BMW             X6
4H42WR       6945664760  Nissan          Altima
0H42W2       3497423327  Chevrolet       Spark LS
H42WFB       9032148870  Land Rover      Discovery Series II
6ZH42W       6708799716  Acura           RDX
H42W0X       6997183779  Toyota          Prius

Part 2: The Challenge

Find our Suspect

SELECT gm.id, p.person_name, gm.person_id, gm.membership_status, dl.gender
FROM globogym_member gm
INNER JOIN person p ON gm.person_id = p.id
INNER JOIN drivers_license dl ON gm.person_id = dl.person_id
    AND dl.gender = 'Male'
INNER JOIN vehicle v ON dl.license_id = v.owner_id
    AND v.plate_number LIKE '%H42W%'
WHERE gm.id LIKE '48Z%'

Check Gym Member Number, Gender & Vehicle

id    person_name   person_id membership_status gender
48Z7A Joe Germuska  28819     Gold              Male
48Z54 Fritz Filteau 37213     Silver            Male
48Z55 Jack Torrence 67318     Gold              Male

Part 2: The Challenge

Find our Suspect

CREATE OR ALTER VIEW vw_Richard_Suspects /* SQL Server 2016 SP1+ Syntax */
AS
    SELECT gm.id, p.person_name, gm.person_id, gm.membership_status, dl.gender
    FROM globogym_member gm
    INNER JOIN person p ON gm.person_id = p.id
    INNER JOIN drivers_license dl ON gm.person_id = dl.person_id
        AND dl.gender = 'Male'
    INNER JOIN vehicle v ON dl.license_id = v.owner_id
        AND v.plate_number LIKE '%H42W%'
    WHERE gm.id LIKE '48Z%'

Create a View

Part 2: The Challenge

Find our Suspect

SELECT * FROM vw_RichardSuspects

Use a View

id    person_name   person_id membership_status gender
48Z7A Joe Germuska  28819     Gold              Male
48Z54 Fritz Filteau 37213     Silver            Male
48Z55 Jack Torrence 67318     Gold              Male

Part 2: The Challenge

Find our Suspect

SELECT * FROM vw_Frances_Suspects

INTERSECT

SELECT * FROM vw_Richard_Suspects

Combine both Views

Part 2: The Challenge

Find our Suspect

INTERSECT = Similar to a UNION. Returns distinct rows between both queries being combined.

EXCEPT = Similar to a UNION, but only returns distinct rows from the left side of the input if they don't appear on the right.

These follow the same rules as a UNION. All queries must have the same number of columns and the datatypes of those columns must match. The column names in the results are inherited from the first query.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql

Part 2: The Challenge

Find our Suspect

SELECT * FROM vw_Frances_Suspects

INTERSECT

SELECT * FROM vw_Richard_Suspects

Combine both Views

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Part 2: The Challenge

Find our Suspect

SELECT person_id, person_name FROM vw_Frances_Suspects

INTERSECT

SELECT person_id, person_name FROM vw_Richard_Suspects

Combine both Views

person_id  person_name
67318      Jack Torrence

GOTCHA!

Part 2: The Challenge

Verify our Suspect

/* With the sproc, declare the return variable */
DECLARE @results varchar(max) ;
/* Execute the sproc, passing in our parameters and setting up the output */
EXEC usp_check_solution 79, 'Jack Torrence', @results OUTPUT
/* Select the results variable to see its value */
SELECT @results

Time to suspend disbelief...

Congrats, you found the murderer! Check their interview transcript to see what they've got to say.

Part 2: The Challenge

Find our Suspect

Our Murderer is

Jack Torrence (ID 67318)

SELECT transcript
FROM interview
WHERE person_id = 67318

What does the Suspect have to say for themself?

I was hired by a woman with a lot of money. I don't know her name but I know she's around 5'5" (65") or 5'7" (67"). She has red hair and green or brown eyes. She drives a Tesla Model S. I saw her at Into The Box in April 2017 and met with her at the DBA Support Group 3 times in December 2017.

The plot thickens...

Part 3: Pop Quiz

Find the Mastermind

What we know:

The Mastermind is a Female.
The Mastermind has a lot of money.
The Mastermind is between 5'5" (65") & 5'7" (67").
The Mastermind has red hair.
The Mastermind has green or brown eyes.
The Mastermind drives a Tesla Model S.
The Mastermind attended the DBA Support Group 3 times in December 2017 & Into The Box in April 2017.