SQL Murder Mystery

solution.sql

select * from crime_scene_report
where date = '20180115'
and city = 'SQL City'
and type = 'murder';

select id, name
from person
where address_street_name = 'Northwestern Dr'
group by address_street_name
having max(address_number);

select id
from person
where name like 'Annabel %'
and address_street_name = 'Franklin Ave'

select person.name, interview.transcript
from interview
join person on person.id = interview.person_id
where person_id in (14887, 16371);
              
select p.name
from person p
join get_fit_now_member m on p.id = m.person_id
join get_fit_now_check_in ci on m.id = ci.membership_id
join drivers_license dl on p.license_id = dl.id
where m.id like '48Z%'
and dl.plate_number like '%H42W%'
and ci.check_in_date = '20180109';

select person.name, interview.transcript
from interview
join person on person.id = interview.person_id
where person.name = 'Jeremy Bowers';

select p.name
from person p
join drivers_license dl on p.license_id = dl.id
join facebook_event_checkin ci on p.id = ci.person_id
where dl.hair_color = 'red'
and dl.car_make = 'Tesla'
and dl.car_model = 'Model S'
and ci.event_name = 'SQL Symphony Concert'
and ci.date like '201712%'
group by p.name
having count(1) == 3;