-- ============================================================================= -- XXXXXXXXXXCIS 506 Homework 5 Template -- -- , -- -- --...

-- =============================================================================
-- CIS 506 Homework 5 Template
--
-- ,
--
--
-- =============================================================================
-- This is submitted as individual work according the Academic Integrity Policy
-- and the W.P. Carey Honor Code to the best of my ability." If it is not true,
-- state why it is not true.
-- =============================================================================
use bbb;
-- ----------------------------------------------------------------------------
-- 1 Generate a report that shows two rows,
-- the beer with highest calories and beer with the lowest calories
-- (Assume there is no possibility of a tie for the lowest or highest calories)
-- (_) rows
-- ----------------------------------------------------------------------------
select beer, cal from beer where cal= ( select max(cal) from beer)
union all
select beer, cal from beer where cal= ( select min(cal) from beer);

-- ----------------------------------------------------------------------------
-- 2 The developers of the BBB app have been requested by the local health
-- organization to measure the health hazards of drinking three of ones favorite
-- beers per day for a week. Generate a report that shows the total weekly
-- calories consumed by each person if they drank three of their favorite beers
-- per day for a week. Of course this means that if someone likes four beers,
-- the report will show the total of the 4x3=12 beers per day, times 7 days to
-- obtain the weekly total. Report should show the name and the weekly total of
-- calories consumed in a week for that person
-- (_) rows
-- ----------------------------------------------------------------------------
select ML.Name, SUM(ML.totalcal)*7 AS WeeklyConsumedCalories
from (
Select pr.name, count(li.beer)*3 * br.cal as totalcal
from person pr
inner join likes li on li.name = pr.name
inner join beer br on li.beer=br.beer
group by pr.name,li.beer
) ML
group by ML.name;

--...