SQL hw1a - Basic Queries

Question 1

All columns relating to AAPL

Answer 1

SELECT *
FROM stocks.s2010
where symb = ‘AAPL’
;

Question 2

All columns from the table and a column with the
difference between the open and close (open-close)
for AAPL on the 7th of January

Answer 2

SELECT *, (opn-cls) as oc_difference
FROM stocks.s2010
WHERE symb = 'AAPL' 
  AND retdate = '2010-01-07'
;

Question 3

Write a query which returns the stock symbol, the date,
the open and close price for the top five differences
(open-close) in 2010 for only those stocks on the New
York Stock Exchange

Answer 3

SELECT symb, retdate, opn, cls
FROM stocks.s2010
WHERE exch = 'NYSE'
ORDER BY (opn-cls) DESC
LIMIT 5
;

Question 4

The days when AAPL has a volume more than 20 million and where the high is $3 or more greater than the low.

Answer 4

SELECT retdate
FROM stocks.s2010
WHERE symb = 'AAPL'
  AND vol >= 20000000
  AND high-low >= 3
;

Question 5

Write a query which returns 3 columns: the return date,

SYMB and volume, but only for stocks that have a volume larger than 200 million

Answer 5

SELECT retdate, symb, vol
FROM stocks.s2010
WHERE vol > 200000000
;

Question 6

Write a query which returns all information about
Google (GOOG), Netflix (NFLX), Amazon (AMZN), and
Microsoft (MSFT) in 2010.

Answer 6

SELECT *
FROM stocks.s2010
WHERE symb = 'GOOG'
  OR symb = 'NFLX'
  OR symb = 'AMZN'
  OR symb = 'MSFT'
;

Question 7

Write a query which returns the date and symbol of
the largest “one-day gainer”, that is the stock which
has the highest close-open on the NYSE

Answer 7

SELECT retdate, symb
FROM stocks.s2010
WHERE exch = 'NYSE'
ORDER BY (cls-opn) DESC
LIMIT 1
;

Question 8

Write a query which returns the date and symbol of the
largest “one-day percentage gainer”, that is the stock
which has the highest (close-open)/open on the NYSE

Answer 8

SELECT retdate, symb
FROM stocks.s2010
WHERE exch = 'NYSE'
ORDER BY (cls-opn)/opn DESC
LIMIT 1
;

Question 9

Consider stocks on the NYSE which had a volume of more than 1 million. Which stocks (symb and date) had their open price the same as their low and their closing price the same as their high?

Answer 9

SELECT symb, date
FROM stocks.s2010
WHERE exch = 'NYSE'
  AND vol = 1000000
  AND opn = low
  AND cls=high
;

Question 10

Consider stocks on the NYSE which had a volume of more than 1 million. Which stocks (symbol and date) had their closing price the same as their low and their opening price the same as their high?

Answer 10

SELECT symb, retdate
FROM stocks.s2010
WHERE exch = 'NYSE'
  AND vol = 1000000
  AND cls = low
  AND opn = high
;

Question 11

Consider stocks on the NYSE which had a volume of more than 1 milion. Of those days which stock had either (a) open = low and close = high, or (b) open = high and close = low, which symbol and date has the largest volume traded?

Answer 11

SELECT symb, retdate
FROM stocks.s2010
WHERE exch = 'NYSE'
  AND vol > 1000000
  AND (((opn = low) AND (cls = high)) 
    OR ((opn = high) AND (cls = low)))
ORDER BY vol DESC
LIMIT 1
;

Question 12

Which company (ticker symbol) had the highest net income over all the years that are in the FND tables?

Answer 12

SELECT tic
FROM stocks.fnd
ORDER BY netinc DESC NULLS LAST
LIMIT 1
;

Question 13

Which company (ticker symbol) had the highest net income in fiscal year 2011? (using the FND table)

Answer 13

SELECT tic
FROM stocks.fnd
WHERE fyear = 2011
ORDER BY netinc DESC NULLS LAST
LIMIT 1
;

Question 14

Which company (ticker symbol) had the lowest, non-zero, net income over all the years? (use the FND table)

Answer 14

SELECT tic
FROM stocks.fnd
WHERE netinc > 0
  AND netinc IS NOT NULL
ORDER BY netinc ASC
LIMIT 1
;

Question 15

Which company (ticker symbol) which had a net-income per employee over $1000, had the largest number of employees (over all the years)? Keep units in mind! (use the FND table)

Answer 15

SELECT tic
FROM stocks.fnd
WHERE (netinc*1000/emp > 1000)
  AND emp <> 0
  AND emp IS NOT NULL
ORDER BY emp DESC
LIMIT 1
;