## 1412. Find the Quiet Students in All Exams

Question:

Solution:

``````select
s.student_id,
s.student_name
from Student s
/* exclude student_ids with the highest or lowest scores */
where s.student_id not in (
select distinct
a.student_id
from (
select
e1.exam_id,
e1.student_id,
e1.score,
e2.low_score,
e2.high_score
from Exam e1
left join(
select
exam_id,
min(score) as low_score,
max(score) as high_score
from Exam
group by 1
) e2
on e1.exam_id = e2.exam_id
where (e1.score = low_score
or e1.score = high_score )
) a
)
/* student_ids in all exams */
and s.student_id in (
select distinct
student_id
from Exam
where exam_id is not null )
order by s.student_id``````

## 1421. NPV Queries

Question:

Solution:

``````select
q.id,
q.year,
ifnull(n.npv,0) as npv
from Queries q
left join NPV n
on (q.id = n.id and q.year = n.year )``````

## 1435. Create a Session Bar Chart

Question:

Solution:

``````select
b.bin,
ifnull(s.total,0) as total
from (
select '[0-5>' as bin
union
select '[5-10>' as bin
union
select '[10-15>' as bin
union
select '15 or more' as bin
) b
left join(
select
bin,
count(distinct session_id) as total
from(
select
session_id,
duration,
case
when duration/60 >= 0 and duration/60 < 5 then '[0-5>'
when duration/60 >= 5 and duration/60 < 10 then '[5-10>'
when duration/60 >= 10 and duration/60 < 15 then '[10-15>'
when duration/60 >= 15 then '15 or more'
else null
end as bin
from sessions
) s
group by 1
) s
on b.bin = s.bin``````

## 1440. Evaluate Boolean Expression

Question:

Solution:

``````select
a.left_operand,
a.operator,
a.right_operand,
case
when a.operator = a.result then 'true'
else 'false'
end as value
from(
select
e.left_operand,
x.value as left_value,
e.operator,
e.right_operand,
y.value as right_value,
case
when x.value = y.value then '='
when x.value > y.value then '>'
when x.value < y.value then '<'
end as result
from Expressions e
left join Variables x
on e.left_operand = x.name
left join Variables y
on e.right_operand = y.name
) a``````

## 1445. Apples and Oranges

Question:

Solution:

``````select
a.sale_date,
(a.sold_num - o.sold_num) as diff
from (
select
sale_date,
fruit,
sold_num
from Sales
where fruit = 'apples'
) a
left join (
select
sale_date,
fruit,
sold_num
from Sales
where fruit = 'oranges'
) o
on a.sale_date = o.sale_date
order by 1``````