Use subqueries to connect two table into one

Question

Tables: CRIMES, SUSPECTS

What was the maximum sentence for the crime committed and what was it? Use a subquery.

table Crimes :

enter image description hereenter image description hereenter image description here

so on...

table Suspects:

enter image description hereenter image description hereenter image description here

so on....

I found the maximum length of words

select max(length(crime_type)) from crimes;

joined with another table

select *

from crimes natural join suspects

where crime_type in (select max(length(crime_type)) from crimes);

or

select crime_type (select max(length(crime_type)) from crimes)

from crimes natural join suspects;

but oracle SQL didn't accept an answer, there appeared error.

So how can I find the longest sentence and show the crime type with name?

Answer 1

I think you got it wrong. "Sentence length" means "number of days someone spent in a prison"; it is not a measure of number of letters in a crime description.

Therefore, consider something like this: join tables on a common column, use dense_rank analytic function to sort rows by sentence length (difference between end and start date; that's the number of days in between) and - finally - fetch row(s) that ranked as the highest:

select crime_type,  
       name,
       sentence_length
from (select c.crime_type, 
         s.name, 
         c.end_date - c.start_date as sentence_length,
         dense_rank() over (order by c.end_date - c.start_date desc) rnk
      from crimes c join suspects s on s.suspect_id = c.suspect_id
     )
where rnk = 1;