1/17/2022 2
1/17/2022 3
Copyright By PowCoder代写 加微信 powcoder
select distinct mfsymbol from mfnews
select distinct fund_symbol from mutualfunds;
Variant: MINUS
1/17/2022 4
select distinct mfsymbol from mfnews
select distinct fund_symbol from mutualfunds;
1/17/2022 5
select distinct mfsymbol from mfnews
where src ilike ‘%investor%’ union
select distinct mfsymbol from mfnews
where src ilike ‘%thestreet%’
1/17/2022 6
select distinct mfsymbol from mfnews
where src ilike ‘%investor%’ union all
select distinct mfsymbol from mfnews
where src ilike ‘%thestreet%’
How do we find which symbols occur more than once?
1/17/2022 7
select fund_symbol, management_name from mutualfunds
where starts_with(fund_symbol, ‘AA’) and
fund_symbol NOT IN (‘AAAQX’, ‘AABAX’, ‘AAMOX’) order by fund_symbol
1/17/2022 8
1st Street
Main Street
Genesee Ave.
1st Street
1st Street
1st Street
Main Street
Main Street
Main Street
1/17/2022 9
3 more rows
1/17/2022 10
from mfnews N, mutualfunds M where M.fund_symbol = N.MFSymbol;
Join predicate
select N.MFSymbol, top10_holdings
from mutualfunds M, mfnews N
where M.fund_symbol = N.MFSymbol and
N.src ilike ‘%marketwatch%’
select N.MFSymbol, top10_holdings from mutualfunds M inner join
mfnews N on M.fund_symbol = N.MFSymbol where N.src ilike ‘%marketwatch%’;
1/17/2022 12
select distinct fund_symbol, management_name, publishdate, title from mutualfunds M, mfnews N, usnewspaper U
where M.fund_symbol = N.MFSymbol and
N.newsid = U.id
order by fund_symbol, publishdate desc;
1/17/2022 13
select distinct fund_symbol, mainagement_name, U.src, publishdate, title from mutualfunds M, mfnews N, usnewspaper U
where M.fund_symbol = N.MFSymbol and
N.newsid = U.id and
N.collectiondate >’2021-01-01′::date + interval ’10 days’ order by publishdate, fund_symbol;
Date Function
selection predicate Ñ¡Ôñν´Ê
DiBMSµÄ²éѯ´¦ÀíÆ÷ͨ³£ÔÚÁ¬½Óν´Ê֮ǰÆÀ¹ÀÑ¡Ôñν´Ê
select distinct fund_symbol, newsid
from mutualfunds M left join mfnews N on
M.fund_symbol = N.MFSymbol order by newsid NULLS last;
14733 tuples
1/17/2022 15
select distinct fund_symbol, newsid
from mutualfunds M right join mfnews N on
M.fund_symbol = N.MFSymbol order by newsid NULLS last;
1/18/2022 16
SELECT ¡¦ m.fund_symbol,
inner JOIN usnewspaper u ON n.newsid = u.id;
right outer JOIN mutualfunds m ON m.fund_symbol = n.MFSymbol
SELECT ¡¦ m.fund_symbol,
usnewspaper u, mfnews n
right outer JOIN mutualfunds m ON m.fund_symbol = n.MFSymbol
WHERE n.newsid = u.id;
difference
1/17/2022 17
Fund Symbol Newspaper Title
Fund Manager
14000+ results
1/18/2022 18
1/18/2022 19
select mfsymbol, title, management_name ¨ from mutualfunds m left join
select distinct n.MFSymbol, u.title
from mfnews n, usnewspaper u
where n.newsid = u.id) a on m.fund_symbol = a.MFSymbol
a¡¦ 1/18/2022 20
with miniquery as (select distinct n.MFSymbol, u.title from mfnews n, usnewspaper u
where n.newsid = u.id) ¡¦
select MFsymbol, title, management_name
from mutualfunds m left join miniquery on m.fund_symbol = miniquery.MFSymbol;
1/18/2022 21
À¼ select fund_symbol, fund_long_name, fund_price_earning_ratio from mutualfunds
select max(fund_price_earning_ratio) from mutualfunds
where fund_price_earning_ratio = ( select max(fund_price_earning_ratio) from mutualfunds)
Nesting in the WHERE clause
1/18/2022 22
select fund_symbol, fund_long_name, fund_price_earning_ratio from mutualfunds
where fund_price_earning_ratio = (
select max(fund_price_earning_ratio) from mutualfunds
where total_net_assets >= 500000000)
select fund_symbol, fund_long_name, fund_price_earning_ratio from mutualfunds
where fund_price_earning_ratio = (
select max(fund_price_earning_ratio) from mutualfunds) and
total_net_assets >= 500000000
select fund_symbol, fund_long_name, fund_price_earning_ratio from mutualfunds
where fund_price_earning_ratio = (
select max(fund_price_earning_ratio)
from mutualfunds
where total_net_assets >= 500000000) and
total_net_assets >= 500000000
1/18/2022 23
select fund_symbol, fund_long_name, fund_price_earning_ratio from mutualfunds
where fund_price_earning_ratio > 1.2 *(
select avg(fund_price_earning_ratio) from mutualfunds
fund_family ilike ‘%American Century%’
select fund_symbol, fund_long_name, fund_price_earning_ratio from mutualfunds
where fund_price_earning_ratio > 1.2 *(
select avg(fund_price_earning_ratio)
from mutualfunds
where fund_family ilike ‘%American Century%’ )
1/18/2022 24
ǶÌײéѯ – 3
fund_family, percentile_cont(0.5) within group
(order by fund_price_earning_ratio) as group_median FROM mutualfunds
group by fund_family
order by group_median desc limit 10;
1/18/2022 25
with goodfunds as ( SELECT
fund_family, percentile_cont(0.5) within group
( order by fund_price_earning_ratio ) as group_median
FROM mutualfunds ¡¦ group by fund_family
order by group_median desc
fund_symbol, week52_high
FROM mutualfunds
where fund_family IN ( ¡¦
select fund_family from goodfunds) and week52_high is not null
order by week52_high desc ;
1/18/2022 26
¡¦SELECT fund_symbol, fund_price_earning_ratio/(select avg(fund_pr¶þice_earning_ratio) from mutualfunds) as relative FROM mutualfunds
where fund_family <> ‘Aberdeen’
order by relative desc nulls last
In a good DBMS, the inner subquery will be computed once but used for every result tuple
1/18/2022 27
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com