select * from crime_scene_report where date=20180115 and type='murder' and city='SQL City'Description: 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 id, name, address_number, address_street_name, transcript
from person join interview
on person.id = interview.person_id
where person.id in (
select * from (select id from person where address_street_name = 'Northwestern Dr' order by address_number desc limit 1)
union
select * from (select id from person where name like 'Annabel%'and address_street_name = 'Franklin Ave')
)id name address_number address_street_name transcript
14887 Morty Schapiro 4919 Northwestern Dr 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".
16371 Annabel Miller 103 Franklin Ave 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 membership_id, person.name, membership_status, plate_number from get_fit_now_check_in
join get_fit_now_member
on get_fit_now_check_in.membership_id = get_fit_now_member.id
join person on get_fit_now_member.person_id = person.id
join drivers_license on person.license_id = drivers_license.id
where check_in_date = 20180109
and membership_status = 'gold'
and plate_number like '%H42W%'membership_id name membership_status plate_number
48Z55 Jeremy Bowers gold 0H42W2
select * from interview where person_id = 67318person_id transcript
67318 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 she drives a Tesla Model S. I know that she attended the SQL Symphony Concert 3 times in December 2017.
select person_id, event_name
from facebook_event_checkin
where person_id in (
select person.id
from person
join drivers_license
on person.license_id = drivers_license.id
and car_make = 'Tesla'
and car_model = 'Model S'
and hair_color = 'red'
and height >= 65 and height <= 67
)
and facebook_event_checkin.date >= 20171201
and facebook_event_checkin.date <= 20171231person_id event_name
99716 SQL Symphony Concert
99716 SQL Symphony Concert
99716 SQL Symphony Concert
select person.name, annual_income
from person
join income
on person.ssn = income.ssn
where person.id = 99716name annual_income
Miranda Priestly 310000