US Funds dataset from Yahoo Finance
1/11/2022 2
Currently we have 14050 rows and 298 columns
Copyright By PowCoder代写 加微信 powcoder
1/11/2022 3
1/11/2022 5
1/11/2022 6
Attributes you want “returned” from the query Tables that you want to use
Predicates to be satisfied by every tuple in the result
select fund_symbol, fund_category, fund_family, initial_investment from mutualfunds
1/11/2022 8
1/11/2022 9
1/11/2022 10
1/11/2022 11
Think twice if
we have a 些
1/11/2022 12
select fund_symbol, fund_long_name, fund_return_3months, fund_return_1year, fund_return_3years
from mutualfunds
where (investment_type = ‘Blend’ or investment_type = ‘Value’) and fund_return_1year > 0.2 and fund_return_3years > 0.2;
select fund_symbol, fund_long_name, fund_return_3months, fund_return_1year, fund_return_3years from mutualfunds
where investment_type = ‘Growth’ and fund_return_1year > 0.5 and fund_return_3years > 0.5;
The investment seeks to achieve long-term capital appreciation. Under normal circumstances, the fund invests primarily in equity securities of U.S. large market capitalization companies. The fund’s investments in equity securities may include common stock, preferred stock and convertible securities. It may also invest in foreign equity securities through American Depositary Receipts (“ADRs”). The fund invests in companies the Adviser believes to be of high quality and believes to be undervalued relative to their expected long-term free cash flows. It is non-diversified.
iLikecase insensitive Like
The investment seeks long-term capital growth. To pursue its goal, the fund normally invests at least 80% of its net assets, plus any borrowings for investment purposes, in the stocks of companies included in the Standard & Poors (S&P)© Midcap 400 Index or the © Index. The fund’s portfolio managers select stocks through a “bottom-up,” structured approach that seeks to identify undervalued securities using a quantitative screening process.
select fund_symbol, investment_strategy, top10_holdings from mutualfunds
where investment_strategy ilike ‘%bio%pharma%’ or
investment_strategy ilike ‘%health%science%’
The investment seeks long-term capital appreciation. Under normal circumstances, the fund invests at least 80% of its net assets, plus any borrowings for investment purposes, in equity securities of mid-cap companies. In addition, under normal market conditions, it invests at least 25% of its total assets in companies in the following group of industries: Health Care Equipment & Supplies, Health Care Technology, Biotechnology, Life Sciences Tools & Services, and/or Software, as defined by third party sources. The fund may have 25% or more of its total assets invested in any one of these industries. It is non-diversified.
The investment seeks long-term capital growth by investing in a worldwide and diversified portfolio of health sciences companies. The fund normally invests at least 80% of its net assets (plus any borrowings for investment purposes) in securities of companies principally engaged in the discovery, development, production or distribution of products related to scientific advances in health care, including biotechnology, pharmaceuticals, diagnostics, managed health care and medical equipment and supplies. It has a policy of investing at least 25% of its assets in investments in the medical research and health care industry.
select fund_symbol, investment_strategy, top10_holdings from mutualfunds
where top10_holdings like ‘____ %LLY% ____ %’ OR top10_holdings like ‘LLY % ____ % ____ %’
LIKE Operator
Description
WHERE EName LIKE ‘a%’
Finds any values that start with “a”
WHERE EName LIKE ‘%a’
Finds any values that end with “a”
WHERE EName LIKE ‘%or%’
Finds any values that have “or” in any position
WHERE EName LIKE ‘_r%’
Finds any values that have “r” in the second position
WHERE EName LIKE ‘a__%’
Finds any values that start with “a” and are at least 3 characters in length
WHERE EName LIKE ‘a%o’
Finds any values that start with “a” and ends with “o”
select fund_symbol, top10_holdings
from mutualfunds
where not(top10_holdings like ‘%AAPL%’ or top10_holdings like ‘%MSFT%’) and
fund_family = ‘American Beacon’
not (A or B) not(A) and not(B)
1/11/2022 22
select fund_symbol, fund_category, investment_strategy from mutualfunds
where fund_return_2020/category_return_2020 > 1 and
fund_return_2019/category_return_2019 > 1 and
not (fund_category ilike ‘%growth%’ and (investment_strategy like ‘%capital%appreciation%’ or
investment_strategy like ‘%total%return%’)
1/11/2022 23
select management_name, fund_family, fund_symbol from mutualfunds
order by management_name
1/12/2022 24
select count(*), avg(fund_price_earning_ratio), stddev(fund_price_earning_ratio), max(fund_price_earning_ratio), min(fund_price_earning_ratio)
from mutualfunds
where fund_category = ‘Real Estate’ ;
select count(*), avg(fund_price_earning_ratio), stddev(fund_price_earning_ratio), max(fund_price_earning_ratio), min(fund_price_earning_ratio)
from mutualfunds
where fund_category = ‘Real Estate’ and top10_holdings like ‘%MSFT%’;
1/11/2022 25
select fund_category, count(*), avg(fund_price_earning_ratio), stddev(fund_price_earning_ratio), max(fund_price_earning_ratio), min(fund_price_earning_ratio)
from mutualfunds
where top10_holdings like ‘%MSFT%’ and
fund_category is not null and
fund_price_earning_ratio is not null group by fund_category;
1/11/2022 26
select fund_category, count(*), avg(fund_price_earning_ratio), stddev(fund_price_earning_ratio), max(fund_price_earning_ratio), min(fund_price_earning_ratio)
from mutualfunds
where top10_holdings like ‘%MSFT%’ and
fund_category is not null and
fund_price_earning_ratio is not null group by fund_category
having count(*) > 5;
1/11/2022 27
select ‘There are ‘|| count(*) || ‘ funds in the category “‘|| fund_category || ‘” whose PE Ratio stats are:’ || ‘ mean: ‘||
Extended projection
round(avg(fund_price_earning_ratio), 3) || ‘, and std. dev: ‘ || from mutualfunds
where top10_holdings like ‘%MSFT%’ and
fund_category is not null and
fund_price_earning_ratio is not null group by fund_category
having count(*) > 5;
round(stddev(fund_price_earning_ratio), 3) as report
1/11/2022 28
1/11/2022 29
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com