SQL Murder Mystery

Esse desafio é uma maneira divertida de praticar habilidades com bancos de dados relacionais e resolver um mistério ocorrido na cidade de SQL

Site do jogo

Um crime ocorreu em SQL City e o detetive precisa de ajuda. Você perdeu o relatório da cena do crime, mas lembra-se vagamente de que o crime foi um assassinato (murder) que ocorreu em 15 de janeiro de 2018, em SQL City. Comece recuperando o relatório correspondente da cena do crime do banco de dados do departamento de polícia. Segue abaixo o schema das tabelas para solucionar o problema.

Recuperando o relatório da cena do crime

Informações relevantes: assassinato (murder) no dia 15 de janeiro de 2018 que ocorreu em SQL City.

SELECT * FROM crime_scene_report -- Primeiramente seleciono todas as colunas da tabela crime_scene_report

WHERE type = 'murder' -- Agora, filtro os resultados. Apenas casos do tipo 'murder'

AND date = '20180115' -- Além disso, especifico a data do crime. No caso, 15 de janeiro de 2018.

AND city = 'SQL City' -- Por fim, também filtro a busca à cidade 'SQL City'

Primeira Testemunha

Mora na última casa da rua Northwestern Dr.

SELECT -- Seleciono algumas colunas específicas de duas tabelas: person (apelidada de 'p') e, por meio do JOIN, interview (apelidada de 'i')

id,

name,

address_number,

address_street_name,

transcript

FROM person AS p

LEFT JOIN interview AS i ON i.person_id = p.id -- Faço um 'LEFT JOIN' para conectar as tabelas 'person' e 'interview' usando o ID da pessoa

WHERE address_street_name = 'Northwestern Dr' -- Agora, aplico um filtro para mostrar informações da rua 'Northwestern Dr'

ORDER BY address_number DESC -- Ordeno os resultados com base no número do endereço, em ordem decrescente

LIMIT 1 -- Limito o resultado para apenas uma linha, pegando o registro de maior número de endereço

Baseado nas informações da primeira testemunha, faremos alguns filtros dentro de várias tabelas para conseguirmos achar as informações sobre o suspeito. Além disso, também foi realizado um LEFT JOIN com a tabela de entrevista para sabermos o que o suspeito falou.

SELECT -- Aqui, estou selecionando algumas informações específicas sobre membros da academia e suas entrevistas

p.id,

p.name,

gfnm.id AS id_gym,

gfnm.membership_status,

dl.gender,

dl.plate_number,

transcript

FROM get_fit_now_member AS gfnm

LEFT JOIN person AS p ON p.id = gfnm.person_id -- Estou fazendo um 'LEFT JOIN' para conectar as tabelas 'get_fit_now_member' e 'person'

LEFT JOIN drivers_license AS dl ON dl.id = p.license_id -- Também conecto a tabela 'drivers_license' para pegar informações do "DETRAN"

LEFT JOIN interview AS i ON i.person_id = p.id -- E mais um 'LEFT JOIN' com a tabela 'interview' para trazer informações da entrevista

WHERE gfnm.id LIKE '%48Z%' -- Aqui, estou filtrando os resultados. Só quero informações dos membros cujo ID da academia contenha '48Z'

AND gfnm.membership_status = 'gold' -- que têm status 'gold'

AND dl.plate_number LIKE '%H42W%' -- e cuja placa do carro contenha 'H42W'

Conclusão 1

De acordo com as informações da primeira testemunha, o suspeito é o Jeremy Bowers. Inclusive, a própria transcrição do suspeito, é bastante clara de que ele foi contratado por uma mulher com bastante dinheiro. Entretanto, é importante averiguar as informações da segunda testemunha antes de bater o martelo sobre o crime.

Segunda Testemunha

Seu primeiro nome é Annabel e ela mora na rua Franklin Ave.

SELECT -- Agora, é hora de focar nos detalhes pessoais e entrevista da segunda testemunha

p.id,

p.name,

p.address_street_name,

transcript

FROM person AS p

LEFT JOIN interview AS i ON i.person_id = p.id -- Faço um 'LEFT JOIN' com a tabela 'interview' para buscar informações da entrevista

WHERE name LIKE '%Annabel%' -- Filtro os dados das pessoa que tenha 'Annabel' no nome

AND address_street_name = 'Franklin Ave' -- e more na 'Franklin Ave'

Baseado nas informações da segunda testemunha, faremos alguns filtros dentro de várias tabelas para conseguirmos achar as informações citadas anteriormente.

SELECT -- Hora de buscar informações sobre check-ins na academia "Get Fit Now" para descobrir os horários da Annabel Miller

gfnc.check_in_date,

gfnc.check_in_time,

gfnc.check_out_time,

p.name,

gfnm.id AS id_gym,

p.id

FROM get_fit_now_check_in AS gfnc

LEFT JOIN get_fit_now_member AS gfnm ON gfnc.membership_id = gfnm.id -- 'LEFT JOIN' para conectar as tabelas 'get_fit_now_member' e 'person'

LEFT JOIN person AS p ON p.id = gfnm.person_id -- e também para a tabela 'person'

WHERE p.id = '16371' -- Filtro pelo id da Annabel Miller

Baseado nas informações anteriores, faremos alguns filtros dentro de várias tabelas para conseguirmos achar as informações sobre o suspeito. Além disso, também foi realizado um LEFT JOIN com a tabela de entrevista para sabermos o que o suspeito falou.

SELECT - Seleciono as informações detalhadas sobre check-ins na academia "Get Fit Now"

check_in_date,

check_in_time,

check_out_time,

name,

id AS id_gym,

membership_status,

transcript

FROM get_fit_now_check_in AS gfnc

LEFT JOIN get_fit_now_member AS gfnm ON gfnm.id = gfnc.membership_id -- 'LEFT JOIN' para conectar as tabelas 'get_fit_now_member'

LEFT JOIN interview AS i ON i.person_id = gfnm.person_id -- e a tabela 'interview'

WHERE check_in_date = '20180109' -- Definindo as condições de busca. Quero informações do check-in realizado em 9 de janeiro de 2018

AND check_in_time > '1500' AND check_out_time < '1800' -- entre 15:00 e 18:00

AND id_gym IS NOT '90081' -- excluindo registros da Annabel Miller

Conclusão 2

Somente o Jeremy Bowers deixou depoimento. E ele é o mesmo homem informado pela testemunha 1.

Check Your Solution

INSERT INTO solution VALUES (1, 'Jeremy Bowers');

SELECT value FROM solution;

Conclusão final

Baseado nas informações do assassino, faremos alguns filtros dentro de várias tabelas para conseguirmos achar quem foi a mandante.

SELECT p.id, p.name, i.annual_income, f.event_name, f.date FROM drivers_license AS dl -- buscando dados, unindo informações de tabelas diversas

LEFT JOIN person AS p ON p.license_id = dl.id -- Faço um 'LEFT JOIN' para conectar as tabelas 'person'

LEFT JOIN income AS i ON i.ssn = p.ssn -- 'income'

LEFT JOIN facebook_event_checkin AS f ON f.person_id = p.id -- 'facebook_event_checkin'

WHERE dl.gender = 'female' -- Aqui, defino as condições de busca. Quero informações sobre mulheres d

AND dl.height BETWEEN 65 AND 67 -- e altura entre 65 e 67

AND dl.hair_color = 'red' -- cabelo vermelho

AND dl.car_model = 'Model S' -- que dirigem um Model S

AND f.event_name = 'SQL Symphony Concert' -- e que fizeram check-in no evento 'SQL Symphony Concert'

ORDER BY i.annual_income DESC; -- Ordeno os resultados pelo rendimento anual em ordem decrescente

Check Your Solution

INSERT INTO solution VALUES (1, 'Miranda Priestly ');

SELECT value FROM solution;

Page updated

Google Sites

Report abuse