Homework

Question_1.sql

select distinct TVModel, Price as MostRecentPrice
from T_TVPrices T
  inner join  
    (select TVModel as M, MAX(DateUpdated) as D
	   from T_TVPrices
	    group by TVModel) X on T.DateUpdated = X.D and T.TVModel = X.M

Question_1_UAT.feature

Feature: Retrieve Prices for Television
  Should be able to get an unique list of TV models with their most recent price

Scenario: T_TVPrices table has duplicate TVModel records
Given T_TVPrices table has
  | TVModel  | DateUpdated | Price |
  | Sony     | 12-01-2012  | 300   |
  | Sammsung | 12-01-2012  | 200   |
  | Sony     | 12-05-2012  | 100   |
When SQL Code is executed
Then the output should be
  | TVModel  | Price |
  | Sammsung | 200   |
  | Sony     | 100   |
  
Scenario: T_TVPrices table has duplicate TVModel records with the same DateUpdated
Given T_TVPrices table has
  | TVModel  | DateUpdated | Price |
  | Sony     | 12-01-2012  | 300   |
  | Sammsung | 12-01-2012  | 200   |
  | Sony     | 12-05-2012  | 100   |
  | Sony     | 12-05-2012  | 100   |
When SQL Code is executed
Then the output should be
  | TVModel  | Price |
  | Sammsung | 200   |
  | Sony     | 100   |

Scenario: T_TVPrices has duplicate TVModel records on the same date with differenting prices
Given T_TVPrices table has
  | TVModel  | DateUpdated | Price |
  | Sony     | 12-01-2012  | 300   |
  | Sammsung | 12-01-2012  | 200   |
  | Sony     | 12-05-2012  | 100   |
  | Sony     | 12-05-2012  | 200   |
When SQL Code is executed
Then the output should be
  | TVModel  | Price |
  | Sammsung | 200   |
  | Sony     | 100   |
  | Sony     | 200   |

Question_2.sql

with t as (select 1 as x union all select x + 1 from t where x < 100)
  select x as Int, 
		Case
			when (x % 3 = 0) then 'Divisible by 3'
			when (x % 5 = 0) then 'Divisible by 5'
			when (x % 15 = 0) then 'Divisible by both 3 and 5'
			else 'Divisible neither by 3 or 5'
		end
		as 'DivisibleBy'
from t

For question 1, given that there are duplicate records for a model that has the same date and price then a distinct will need to be added to the select statement like so:

select distinct TVModel, Price as MostRecentPrice
from T_TVPrices T
inner join
(select TVModel as M, MAX(DateUpdated) as D
from T_TVPrices
group by TVModel) X on T.DateUpdated = X.D and T.TVModel = X.M