day1_solutions.tex

% From https://www.sharelatex.com/learn/Typing_exams_in_LaTeX
% 
% To produce pdf run:
%   $ pdflatex paper.tex 
%

\documentclass[10pt]{exam}

\usepackage{listings}
\lstset{basicstyle=\footnotesize\ttfamily,breaklines=true}
 
% These do what you would expect:
\printanswers
%\noprintanswers

\begin{document}
 
\title{SQL Workshop Day 1}
\author{UC Davis DSI}
\date{2018}

\maketitle

\section{Selecting and Sorting}

\begin{questions}

\question How many tables are in the database?

\begin{solution}
    7. Look under `Database Structure' tab.
\end{solution}


\question Select all columns in the \texttt{company\_info} table.

\begin{solution}
\begin{lstlisting}
SELECT *
FROM company_info
;
\end{lstlisting}
\end{solution}


\question Which table has the most rows? The fewest rows?

\begin{solution}

    Count rows using:

\begin{lstlisting}
SELECT COUNT(*)
FROM daily_share_prices  -- replace with name of each table
;
\end{lstlisting}
\end{solution}


\question Write a query so that the columns in \texttt{financial\_ratios} table
appear in alphabetical order.

\begin{solution}
\begin{lstlisting}
SELECT beta
    , dividend_yield
    , market_cap_in_millions
    , price_earnings_ratio
    , price_to_book_ratio
    , return_on_equity
    , share_price
    , ticker
FROM financial_ratios
;
\end{lstlisting}
\end{solution}


    \question Which company has the least expensive stock in the
    \texttt{financial\_ratios} table?

\begin{solution}
\begin{lstlisting}
SELECT ticker, share_price
FROM financial_ratios
ORDER BY share_price
LIMIT 1
;
\end{lstlisting}
\end{solution}

    \question Which company has the most expensive stock in the
    \texttt{financial\_ratios} table?

\begin{solution}
\begin{lstlisting}
SELECT ticker, share_price
FROM financial_ratios
ORDER BY share_price DESC
LIMIT 1
;
\end{lstlisting}
\end{solution}


\question How many different stocks do we have daily share prices for?

\begin{solution}
\begin{lstlisting}
SELECT COUNT(DISTINCT ticker)
FROM daily_share_prices
;
\end{lstlisting}
\end{solution}


\question How many different days do we have daily share prices for?

\begin{solution}
\begin{lstlisting}
SELECT COUNT(DISTINCT date)
FROM daily_share_prices
;
\end{lstlisting}
\end{solution}


\question Which 5 states have the highest population in 2017? lowest?

\begin{solution}
\begin{lstlisting}
SELECT *
FROM state_populations
ORDER BY popnEstimate2017  -- DESC for lowest
LIMIT 5
;
\end{lstlisting}
\end{solution}


\question How many different 4 digit industry sector codes are possible?

\begin{solution}
\begin{lstlisting}
SELECT COUNT(DISTINCT sic)
FROM sic
;
\end{lstlisting}
\end{solution}


\question How many different industry sectors appear in the company
information?

\begin{solution}
\begin{lstlisting}
SELECT COUNT(DISTINCT sic_code)
FROM company_info
;
\end{lstlisting}
\end{solution}


\question What are all the different prices that each company closed at?

\begin{solution}
\begin{lstlisting}
SELECT DISTINCT ticker, close
FROM daily_share_prices
;
\end{lstlisting}
\end{solution}


\question Which states (in this database) have companies in them?

\begin{solution}
\begin{lstlisting}
SELECT DISTINCT state
FROM company_locations
\end{lstlisting}
\end{solution}

\question How many companies are in each of the different tables? What can
you conclude?

\begin{solution}
\begin{lstlisting}
SELECT COUNT(DISTINCT ticker)
FROM company_info  -- Similar for others
;
\end{lstlisting}
company\_info: 450, company\_locations: 500, company\_name: 505,
daily\_share\_prices: 505. Different numbers mean that not all companies
are in all of the tables.
\end{solution}

\end{questions}


% Part 2 ------------------------------------------------------------
\newpage
\section{Filtering and Calculations}
\begin{questions}


\question Which company is located in Oregon?

\begin{solution}
\begin{lstlisting}
SELECT *
FROM company_locations
WHERE state = 'Oregon'
;
\end{lstlisting}
\end{solution}


\question Which companies have a price-earnings ratio below 7.0?

\begin{solution}
\begin{lstlisting}
SELECT ticker, share_price
FROM financial_ratios
WHERE price_earnings_ratio < 7
;
\end{lstlisting}
\end{solution}


\question Which companies have an asset turnover ($\textrm{revenue} /
\textrm{assets}$) greater than 5.0?

\begin{solution}
\begin{lstlisting}
SELECT ticker, (revenue / asset) AS asset_turnover
FROM company_info
WHERE asset_turnover > 5.0
;
\end{lstlisting}
\end{solution}


\question Which states had a 2010 population of at least 10,000,000?

\begin{solution}
\begin{lstlisting}
SELECT *
FROM state_populations
WHERE popn2010 >= 10000000
;
\end{lstlisting}
\end{solution}


\question How many days did Apple's (AAPL) share price close above 150?

\begin{solution}
\begin{lstlisting}
SELECT COUNT(*)
FROM daily_share_prices
WHERE ticker == 'AAPL'
AND close > 150
;
\end{lstlisting}
\end{solution}


\question Which New York companies are not located in New York city?

\begin{solution}
\begin{lstlisting}
SELECT *
FROM company_locations
WHERE state = 'New York'
AND city != 'New York'
;
\end{lstlisting}
\end{solution}


\question Which California companies are located in Irvine, San Jose, or
Thousand Oaks?

\begin{solution}
\begin{lstlisting}
SELECT *
FROM company_locations
WHERE state = 'California'
AND city IN ('Irvine', 'San Jose', 'Thousand Oaks')
;
\end{lstlisting}
\end{solution}


\question Which companies have assets of more than 1 trillion dollars or are in the
software industry?

\begin{solution}
\begin{lstlisting}
SELECT *
FROM company_info
WHERE asset > 1e12
OR industry = 'Software'
;
\end{lstlisting}
\end{solution}


\question Which division has SIC codes that mention the word ``tree''?

\begin{solution}
\begin{lstlisting}
SELECT division, description
FROM sic
WHERE description LIKE '%tree %' -- The space is important!
;
\end{lstlisting}
\end{solution}


\question Which companies have web pages that do not have ``.com'' in the address?

\begin{solution}
\begin{lstlisting}
SELECT company_name, web_page
FROM company_info
WHERE web_page NOT LIKE '%.com%'
;
\end{lstlisting}
\end{solution}


\question Which companies are classified as construction (SIC codes 1500 --
1799)?

\begin{solution}
\begin{lstlisting}
SELECT *
FROM company_info
WHERE sic_code BETWEEN 1500 AND 1799
;
\end{lstlisting}
\end{solution}


\question Which companies are in Silicon Valley, which is approximately bounded
by the box (-122.996, 38.092) to (-121.479, 37.039)?

\begin{solution}
\begin{lstlisting}
SELECT *
FROM company_locations
WHERE xcoord BETWEEN -122.996 AND -121.479
AND ycoord BETWEEN 37.039 AND 38.092
;
\end{lstlisting}
\end{solution}


\question Are any rows in the daily share prices table missing the opening or
closing price?

\begin{solution}
\begin{lstlisting}
SELECT *
FROM daily_share_prices
WHERE open IS NULL
OR close IS NULL
;
\end{lstlisting}
\end{solution}


\question Are any states missing population estimates?

\begin{solution}
\begin{lstlisting}
SELECT *
FROM state_populations
WHERE popn2010 IS NULL
OR popnestimate2017 IS NULL
;
\end{lstlisting}
\end{solution}


\question How many California cities have a company whose address is on a
``street'' or ``road''?

\begin{solution}
\begin{lstlisting}
SELECT DISTINCT city
FROM company_locations
WHERE state = 'California'
AND (
-- Be careful: some streets might not end with '.'
	street LIKE '%st.'
	OR street LIKE '%rd.'
)
;
\end{lstlisting}
\end{solution}

\end{questions}
\end{document}