SQL hw2a - Subqueries

Question 1

Using the daily stock data from 2010, return a list of the unique trading days in 2010.

Answer 1

SELECT DISTINCT retdate
FROM stocks.s2010
;

Question 2

Return the symbol, date, and the dollar volume traded for the highest dollar volume traded stocks in 2010 on the NYSE.

Answer 2

SELECT symb, retdate, (cls*vol) as dollarVolume
FROM stocks.s2010
WHERE exch = 'NYSE'
ORDER BY dollarVolume DESC
LIMIT 1
;

Question 3

Using the 2010 data, return the stock (symbol only) with the largest volume on Jan 11th that also appears on Dec 1st.

Answer 3

SELECT symb
FROM stocks.s2010
WHERE symb IN
    (SELECT DISTINCT symb 
    FROM stocks.s2010
    WHERE retdate = '2010-12-01')
  AND retdate = '2010-01-11'
ORDER BY vol DESC
LIMIT 1
;

Question 4

Using the 2010 data, return the stock symbol and a column called “HFlag” which is equal to 1 if the high - low is greater than 1 and zero otherwise. Only return those companies who stock symbol begins or ends with “A”.

Answer 4

SELECT symb, 
  CASE 
    WHEN (high - low) > 1 THEN 1
    ELSE 0
  END AS HFlag
FROM stocks.s2010
WHERE symb LIKE 'A%'
;

Question 5

Write a query which returns (a) the date, (b) closing price, (c) a flag (‘gt30’) which is equal to 1 when the closing price is greater than $30.00 and 0 otherwise for ‘AAPL’ in 2010.

Answer 5

SELECT retdate, cls, 
  CASE 
    WHEN cls > 30 THEN 1
    ELSE 0
  END AS gt30
FROM stocks.s2010
WHERE symb = 'AAPL'
;

Question 6

Return the list of symbols that exist in 2011, but not 2010.

Answer 6

SELECT DISTINCT symb
FROM stocks.s2011
WHERE symb NOT IN 
  (SELECT DISTINCT symb
  FROM stocks.s2010)
;

– has to be DISTINCT because it is comparing two groups! It can’t do multiple permutations of comparisons at once…it needs unique symb values.

Question 7

Using the fnd data, return company name, year, and the column called “Hflag” which is equal to 1 if the company has a net income larger than $1 Billion dollars and 0 otherwise. Only include those companies whose name begins with “B”

Answer 7

SELECT conm, fyear,
  CASE
    WHEN netinc > 1000  THEN 1
    ELSE 0
  END AS HFlag
FROM stocks.fnd
WHERE conm LIKE 'B%'
;

– note that netinc is in units of millions

Question 8

Using the fnd data, which ticker symbols have a net income to employee ratio greater than $1000 in fiscal year 2010 and also have a net income between 20 and 30 million dollars in 2011?

Answer 8

SELECT tic
FROM stocks.fnd
WHERE emp > 0
  AND (netinc/emp)*1000 > 1000
  AND fyear = 2010
  AND tic in 
    (SELECT tic
    FROM stocks.fnd
    WHERE fyear = 2011
        AND (netinc BETWEEN 20 AND 30))
;

Question 9

Using the fnd data, which companies (company name) in fiscal year 2010 had a profit margin greater than 20%, turnover more than 2 and more than 10,000 employees?

Answer 9

SELECT conm
FROM stocks.fnd
WHERE fyear = 2010
  AND rev > 0
  AND invt > 0
  AND ((netinc/rev)*100) > 20
  AND (rev/invt) > 2
  AND emp > 10
;

Question 10

The lowest five symbols by volume from January 11th, 2010 that have a volume between 1 million and 10 million on December 1st, 2011. In other words, of those stocks which had between 1 and 10 million shares traded on December 1st, 2011, which five have the lowest volume traded on January 11th, 2010.

Answer 10

SELECT symb
FROM stocks.s2010
WHERE retdate = '2010-11-01'
  AND symb IN
    (SELECT DISTINCT symb 
    FROM stocks.s2011
    WHERE retdate = '2011-12-01'
      AND vol > 1000000
      AND vol < 10000000)
ORDER BY vol ASC
LIMIT 5
;

Question 11

Of the stocks (symbols) that existed in 2011, but not in 2010, which had the highest closing price in 2011?

Answer 11

SELECT symb
FROM stocks.s2011
WHERE symb NOT IN 
  (SELECT DISTINCT symb
  FROM stocks.s2010)
  AND cls IS NOT NULL
ORDER BY cls DESC
LIMIT 1
;

Question 12

Which symbols were in the top 500 of dollar volume on the 2nd, 3rd, and 4th days of February 2011?
(The stock needs to be in the top 500 for all days)

Answer 12

SELECT symb
FROM stocks.s2011
WHERE symb IN
    (SELECT symb 
    FROM stocks.s2011
    WHERE retdate = '2011-02-02'
      AND vol IS NOT NULL
      AND CLS IS NOT NULL
    ORDER BY (vol*cls) DESC
    LIMIT 500)
  AND symb IN 
     (SELECT symb 
     FROM stocks.s2011
     WHERE retdate = '2011-02-03'
       AND vol IS NOT NULL
       AND CLS IS NOT NULL
     ORDER BY (vol*cls) DESC
     LIMIT 500)
  AND symb IN 
     (SELECT symb 
     FROM stocks.s2011
     WHERE retdate = '2011-02-04'
       AND vol IS NOT NULL
       AND CLS IS NOT NULL
     ORDER BY (vol*cls) DESC
     LIMIT 500)
;

Question 13

Of the symbols that had volume between 100,000 and 1,000,000 on the 2nd and 3rd of February 2011, which had volume greater than 5,000,000 on the 4th of February?

Answer 13

SELECT symb
FROM stocks.s2011
WHERE retdate = '2011-02-04'
  AND vol > 5000000
  AND symb IN
    (SELECT symb 
    FROM stocks.s2011
    WHERE retdate = '2011-02-02'
      AND vol BETWEEN 10^5 and 10^6)
  AND symb IN
    (SELECT symb 
    FROM stocks.s2011
    WHERE retdate = '2011-02-03'
      AND vol BETWEEN 10^5 and 10^6)

Question 14

Write a query to generate the following dataset:

• company name, ticker symbol, revenue for all the companies whose name begin with “A” or “a”
• a column, revflag which is 1 if the revenue is greater than $25,000,000 and 0 otherwise

Answer 14

SELECT conm, tic, rev,
  CASE 
    WHEN (rev > 25) THEN 1
    ELSE 0
  END AS revflag
FROM stocks.fnd
WHERE conm iLIKE 'a%;
;

– notice iLIKE for case-insensitivity

Question 15

Write a query to generate the following dataset:

• company name, ticker symbol, revenue, inventory, and employee information from fiscal year 2010
• A column called turnflag which is 1 for companies with turnover greater than 2, 0 otherwise
• For a company to be included it must have revenue, inventory, and employee all greater than zero for both 2010 and 2011

Answer 15

SELECT conm, tic, rev, invt, emp,
  CASE 
    WHEN ((inv > 0) AND (rev/inv > 2)) THEN 1
    ELSE 0
  END AS turnflag
FROM stocks.fnd
WHERE fyear = 2010
    AND rev > 0
    AND invt > 0
    AND emp > 0
    AND conm IN
            ( SELECT DISTINCT conm
                FROM stocks.fnd
                WHERE fyear = 2011
                    AND rev > 0
                    AND invt > 0
                    AND emp > 0)
;