hist_qry4.sql

Calculates the value of the S&P 500 and Russell 2000 index for a specified day using unadjusted prices and the index composition of the two indexes on the specified day.

Output

The rows returned by this query are:

INDEX_NAME      AVERAGE_CLOSE_PRICE
Russell 2000       49.47026052
S&P500            54.44644

SQL Statements

-- Calculate the value of the S&P500 and Russell 2000 index
-- for a specified day using unadjusted prices and the index composition
-- of the 2 indexes (see appendix for spec) on the specified day.

commit
;

Select ii.INDEX_NAME, AVG(sh.CLOSE_PRICE) as AVERAGE_CLOSE_PRICE
FROM MARKET_INDEX AS ii 
inner join INDEX_CMPSTN AS ic
	on ii.MARKET_INDEX_ID = ic.MARKET_INDEX_ID 
inner join STOCK_HISTORY AS sh
on ic.INSTRUMENT_ID = sh.INSTRUMENT_ID
and sh.TRADE_DATE = '2005-03-03'
WHERE ii.INDEX_NAME in ('S&P 500','Russell 2000') 
GROUP BY
ii.INDEX_NAME
;