Kurzrepetition zu SQL-Befehlen

Aufgabe: Aufwärmen

Führen Sie zum Aufwärmen die beiden LearningSnacks durch:

https://www.learningsnacks.de/share/14028
https://www.learningsnacks.de/share/369848/

SQL Murder Mystery

Aufgabe

Lösen Sie das Rätsel durch den Einsatz Ihrer SQL-Skills!

https://mystery.knightlab.com/

Lösung

SELECT description FROM crime_scene_report
WHERE type="murder" AND date="20180115"
AND city="SQL City"

Security footage shows that there were 2 witnesses. The first witness lives at the last house on «Northwestern Dr». The second witness, named Annabel, lives somewhere on «Franklin Ave».

SELECT * FROM person
WHERE address_street_name = "Northwestern Dr"
ORDER BY address_number DESC LIMIT 1

14887 Morty Schapiro 118009 4919 Northwestern Dr 111564949

SELECT * FROM person
WHERE address_street_name = "Franklin Ave"
AND name LIKE "Annabel%"

16371 Annabel Miller 490173 103 Franklin Ave 318771143

SELECT transcript FROM interview WHERE person_id = 14887 OR person_id = 16371

I heard a gunshot and then saw a man run out. He had a «Get Fit Now Gym» bag. The membership number on the bag started with «48Z». Only gold members have those bags. The man got into a car with a plate that included «H42W».

I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.

SELECT person_id FROM get_fit_now_member WHERE id LIKE "48Z%" AND membership_status = "gold"

28819, 67318

SELECT id FROM drivers_license WHERE plate_number LIKE "%H42W%"

183779, 423327, 664760

SELECT id FROM person WHERE license_id IN (183779, 423327, 664760)

51739, 67318, 78193

SELECT name FROM person WHERE id = 67318

Jeremy Bowers