SQLNoir.sql

-- https://github.com/hristo2612/SQLNoir

-- Case #001: The Vanishing Briefcase
-- Set in the gritty 1980s, a valuable briefcase has disappeared from the Blue Note Lounge.
select * from crime_scene where location = 'Blue Note Lounge'
-- id	date	type	description
-- 76	19851120	theft	A briefcase containing sensitive documents vanished. A witness reported a man in a trench coat with a scar on his left cheek fleeing the scene.
select * from suspects where attire = 'trench coat' AND scar = 'left cheek'
-- id	name
-- 3	Frankie Lombardi
-- 183	Vincent Malone
select suspect_id, transcript, name from interviews join suspects on interviews.suspect_id = suspects.id where attire = 'trench coat' AND scar = 'left cheek' aND transcript IS NOt NULL
-- suspect_id	transcript	name
-- 183	I wasn’t going to steal it, but I did.	Vincent Malone

-- Case #002: The Stolen Sound
-- In the neon glow of 1980s Los Angeles, the West Hollywood Records store was rocked by a daring theft.
-- A prized vinyl record, worth over $10,000, vanished during a busy evening, leaving the store owner desperate for answers.
-- Vaguely recalling the details, you know the incident occurred on July 15, 1983, at this famous store.
-- Your task is to track down the thief and bring them to justice.
select id, description from crime_scene where location = 'West Hollywood Records'
-- id	description
-- 65	A prized vinyl record was stolen from the store during a busy evening.
select clue from witnesses join crime_scene on witnesses.crime_scene_id = crime_scene.id where location = 'West Hollywood Records'
-- clue
-- I saw a man wearing a red bandana rushing out of the store.
-- The main thing I remember is that he had a distinctive gold watch on his wrist.
select id, name from suspects where bandana_color = 'red' AND accessory = 'gold watch'
-- id	name
-- 35	Tony Ramirez
-- 44	Mickey Rivera
-- 97	Rico Delgado
select suspect_id, name, transcript from interviews join suspects on interviews.suspect_id = suspects.id where bandana_color = 'red' AND accessory = 'gold watch' AND transcript IS NOT NULL
-- suspect_id	name	transcript
-- 35	Tony Ramirez	I wasn't anywhere near West Hollywood Records that night.
-- 44	Mickey Rivera	I was busy with my music career; I have nothing to do with this theft.
-- 97	Rico Delgado	I couldn't help it. I snapped and took the record.


-- Case #003: The Miami Marina Murder
-- A body was found floating near the docks of Coral Bay Marina in the early hours of August 14, 1986.
-- Your job detective is to find the murderer and bring them to justice.
-- This case might require the use of JOINs, wildcard searches, and logical deduction.
-- Get to work, detective.
select description from crime_scene where location = 'Coral Bay Marina' and date = 19860814
-- The body of an unidentified man was found near the docks.
-- Two people were seen nearby: one who lives on 300ish "Ocean Drive"
-- and another whose first name ends with "ul" and his last name ends with "ez".
select * from person where address like '%Ocean Drive'
-- 101	Carlos Mendez	Los Ojos	Fisherman	369 Ocean Drive
select * from person where name like '%ul%' and name like 'ez%'
-- 102	Raul Gutierrez	The Cobra	Nightclub Owner	45 Sunset Ave
select * from interviews where interviews.person_id IN (101, 102)
-- id	person_id	transcript
-- 101	101	I saw someone check into a hotel on August 13. The guy looked nervous.
-- 103	102	I heard someone checked into a hotel with "Sunset" in the name.
select * from hotel_checkins where check_in_date = 19860813 and hotel_name like '%Sunset%'
select surveillance_records.person_id, surveillance_records.suspicious_activity from hotel_checkins join surveillance_records on hotel_checkins.id = surveillance_records.hotel_checkin_id where check_in_date = 19860813 and hotel_name like '%Sunset%' and suspicious_activity IS NOT NULL
-- 8	Left suddenly at 3 AM
select confession from confessions   where confessions.person_id = 8
-- Alright! I did it. I was paid to make sure he never left the marina alive.
select name from person where id = 8
-- Thomas Brown

-- Case #004: The Midnight Masquerade Murder
-- On October 31, 1987, at a Coconut Grove mansion masked ball,
-- Leonard Pierce was found dead in the garden.
select * from person where name = 'Leonard Pierce' -- 4	Leonard Pierce	Business Magnate	101 Elite Ave
select id, description from crime_scene where date = 19871031 and location like '%Coconut Grove%'
-- id	description
-- 75	During a masked ball, a body was found in the garden. Witnesses mentioned a hotel booking and suspicious phone activity.
select witness_id, clue from witness_statements where crime_scene_id = 75
-- witness_id	clue
-- 37	I overheard a booking at The Grand Regency.
-- 42	I noticed someone at the front desk discussing Room 707 for a reservation made yesterday.
select person_id, note from hotel_checkins join surveillance_records on hotel_checkins.id = surveillance_records.hotel_checkin_id where hotel_name = 'The Grand Regency' and room_number = 707 and check_in_date = 19871030
-- 11	Subject was overheard yelling on a phone: "Did you kill him?"
select caller_id, recipient_id, call_date, call_time, note from phone_records where caller_id = 11 or recipient_id = 11
-- caller_id	recipient_id	call_date	call_time	note
-- 11	58	19871030	23:30	Why did you kill him, bro? You should have left the carpenter do it himself!
select * from catering_orders where person_id IN (11, 58, 133) -- 58	19871030	Screwdriver	1
select * from final_interviews where person_id = 11 or person_id = 58 -- I didn’t kill Leo per se. I was just a middleman.
select caller_id, recipient_id, call_date, call_time, note from phone_records where caller_id = 58 or recipient_id = 58
-- 133	58	19871030	22:15	I will do it. Only if you give me that nice Lambo of yours.
select catering_orders.* from vehicle_registry join catering_orders on vehicle_registry.person_id = catering_orders.person_id where car_make = 'Lamborghini'
select * from final_interviews where person_id = 133 -- I was attending a wedding that day. I couldnt have killed anyone.
select caller_id, recipient_id, call_date, call_time, note from phone_records where caller_id = 133 or recipient_id = 133
select person_id, persion.name from vehicle_registry join person on vehicle_registry.person_id = person.id where car_make = 'Lamborghini' and person.occupation = 'Carpenter' -- 97 Marco Santos
select * from final_interviews where person_id = 97 -- I ordered the hit. It was me. You caught me.