— Q1) Find a random mutual fund that has ‘MSFT’ is its top 10 holdings using the LIKE operator.
— Find all 10 top holdings of that firm. Select three random stock symbols from these holdings.
select top10_holdings, fund_symbol,total_net_assets from public.mutualfunds
where top10_holdings ilike ‘%MSFT%’
Copyright By PowCoder代写 加微信 powcoder
order by random()
— To select 3 random stocks, you can use Python for processing or REGEXP_MATCHES. We’ll give points for both methods.
— Q2) Which funds with net assets less than $10M have a PE ratio at least 20% better than the
— median PE ratio of companies that have more than $10M of net assets?
select fund_symbol, total_net_assets,fund_price_earning_ratio from public.mutualfunds
where total_net_assets < 10000000 and fund_price_earning_ratio > 1.2*
(SELECT percentile_cont(0.5)
WITHIN GROUP (ORDER BY fund_price_earning_ratio)
FROM public.mutualfunds
where total_net_assets > 10000000 );
— Q3) Find the newspaper names, titles and publication dates of newspaper articles that contains more than one mutual fund symbol.
with miniTable as (select * from
(select newsid, count(*) as no_of_funds from mfnews
group by newsid) temp
where no_of_funds > 1)
select id, author, src, title from usnewspaper U, miniTable M
where U.id = M.newsid;
— Which of these funds has the lowest PE Ratio?
with t as (
Select fund_price_earning_ratio, mfsymbol from mfnews , (select newsid, count(*) as no_of_funds from mfnews
group by newsid) temp, mutualfunds
where temp. no_of_funds > 1 and mfnews.newsid = temp.newsid and mfnews.mfsymbol = mutualfunds.fund_symbol)
select t.mfsymbol from t where fund_price_earning_ratio = (select min(fund_price_earning_ratio) from t)
— Q4) Find 20 random pairs of mutual fund symbols such that the weekly 52 high of the first is greater than that of the second, but the PE Ratio of the second is greater than that of the first. The answer may be null
select A.fund_symbol as firstFund, B.fund_symbol as secondFund
from public.mutualfunds A, public.mutualfunds B
where A.fund_symbol != B.fund_symbol
and A.week52_high > B.week52_high and A.fund_price_earning_ratio < B.fund_price_earning_ratio
order by random()
--- Write English query for the given SQL query
Fund fund symbol, management name, source, publishing date and title
for newspaper articles that were collected after 11 Jan 2021 and order them according to publishing date and fund symbol.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com