SQL Stock Market Analysis:
The following questions are based on a Stock Data set that have been downloaded from google finance. The dataset dates from as early as 1950(not sure) till 2016. These queries are designed to question the dataset in a way that most of the SQL operations to modify or analyze data are used.

1.       List all columns and all rows from the StockData table.
SELECT *
FROM StockData;

2.       List the TickerSymbol, Industry, TradeDate and the Volume columns from the StockData table.  List all rows.
SELECT TickerSymbol, Industry, TradeDate, Volume
FROM StockData;

3.       List the TickerSymbol, Industry, TradeDate and the Volume columns from the StockData table.  List only the rows that have a volume of more thirty million shares traded.
SELECT TickerSymbol, Industry, TradeDate, Volume
FROM StockData
WHERE Volume > 30000000;

4.       List the TickerSymbol, Industry, TradeDate and the Volume columns from the StockData table.  List only the rows that have a volume of more than 30 million shares traded.  Arrange the answer in TickerSymbol order.  This means for example that British Petroleum should appear before Starbucks.
SELECT TickerSymbol, Industry, TradeDate, Volume
FROM StockData
WHERE Volume > 30000000
ORDER BY TickerSymbol;

5.       List the TickerSymbol, Industry, TradeDate and the Volume columns from the StockData table.  List only the rows that have a volume of more than 30 million shares traded.  Arrange the answer in TickerSymbol order and then in TradeDate order.  This means that for a given stock, trading days should appear in chronological order.

SELECT TickerSymbol, Industry, TradeDate, Volume
FROM StockData
WHERE Volume > 30000000
ORDER BY TickerSymbol, TradeDate;

6.       List the TickerSymbol, Industry, TradeDate, the opening stock price and the closing stock price.  List only those trading days that occurred in the year 2011.  Arrange the answer in order of the trade dates which means that for a given stock, trading days should appear in chronological order.

SELECT TickerSymbol, Industry, TradeDate, ST_Open, ST_Close
FROM StockData
WHERE TradeDate BETWEEN '1/1/2011' AND '12/31/2011'
ORDER BY TradeDate;

7.       List the TickerSymbol, Industry, TradeDate, the opening stock price and the closing stock price.  List only those trading days that occurred in the year 2011.  Arrange the answer in order of the industry, the ticker symbol, and then by the trade dates.  Please use the "greater than" and "less than" operators for this answer.
SELECT TickerSymbol, Industry, TradeDate, ST_Open, ST_Close
FROM StockData
WHERE TradeDate >= '1/1/2011' AND TradeDate =< '12/31/2012'
ORDER BY Industry, TickerSymbol, TradeDate;


8.       List the TickerSymbol, Industry, TradeDate, the opening stock price and the closing stock price.  List only those trading days that occurred in the year 2011.  Arrange the answer in order of the industry, the ticker symbol, and then by the trade dates.  Please use "between" for this answer.

SELECT TickerSymbol, Industry, TradeDate, ST_Open, ST_Close
FROM StockData
WHERE TradeDate BETWEEN '1/1/2011' AND '12/31/2011'
ORDER BY Industry, TickerSymbol, TradeDate;

9.       List the TickerSymbol, Industry, TradeDate, the opening stock price and the closing stock price.  List only stocks that include the word "oil" in the industry description.  Arrange the answer in order of the industry, the ticker symbol, and then by the trade dates.
SELECT tickersymbol, Industry, TradeDate, St_Open, ST_Close
FROM StockData
WHERE Industry LIKE '%oil%'
ORDER BY Industry, TickerSymbol, TradeDate;

10.    List the TickerSymbol, Industry, TradeDate, the opening stock price and the closing stock price.  List only stocks that do not include the word "oil" in the industry description.  Arrange the answer in order of the industry, the ticker symbol, and then by the trade dates.
SELECT TickerSymbol, Industry, TradeDate, ST_Open, ST_Close
FROM StockData
WHERE Industry LIKE '%oil%'
ORDER BY Industry, TickerSymbol, TradeDate;

11.    List the TickerSymbol, TradeDate and the closing stock price.  List only Microsoft tuples that occurred between January 1, 2000 and June 1, 2011.  Arrange the answer in order of the ticker symbol, trade dates and then the closing price.
SELECT TickerSymbol, TradeDate, ST_Close
FROM StockData
WHERE TradeDate BETWEEN '1/1/2000' AND '1/6/2011' AND TickerSymbol = 'MSFT'
ORDER BY TickerSymbol, TradeDate, ST_Close;

12.    List the TickerSymbol, TradeDate and the closing stock price.  List only Microsoft tuples that occurred in on dates other than the range January 1, 2000 to June 1, 2011.  Arrange the answer in order of the ticker symbol, trade dates and then the closing price.
SELECT TickerSymbol, TradeDate, ST_Close
FROM StockData
WHERE TradeDate NOT BETWEEN '1/1/2000' AND '1/6/2011' AND TickerSymbol = 'MSFT'
ORDER BY TickerSymbol, TradeDate, ST_Close;

13.    List the TickerSymbol, Industry,TradeDate, the opening price and the closing stock price.  List only the ‘WFM’ and ‘XOM’ ticker symbols.  Arrange the answer in order of the ticker symbol, Industry and the trade dates.
SELECT TickerSymbol, Industry,TradeDate, ST_Open, ST_Close
FROM StockData
WHERE TickerSymbol IN ('WFM','XOM')
ORDER BY TickerSymbol, Industry, TradeDate;
14.    List the Price ID of all stocks that have a volume of 0.  List the Price IDs in descending order.
SELECT PriceID
FROM StockData
WHERE Volume = 0
ORDER BY PriceID DESC;
15.    Return all attributes for stocks that have a closing price within $24 and $25.  List the closing prices in descending order.
SELECT *
FROM StockData
WHERE ST_Close BETWEEN 24 AND 25
ORDER BY ST_Close DESC;
16.    Return all attributes for stocks that have been traded in the month of June in 2010.  List the trade date in descending order.
SELECT *
FROM StockData
WHERE TradeDate BETWEEN '6/1/2010' AND '6/30/2010'
ORDER BY TradeDate DESC;
17.    List the price ID, opening price, and closing price of all stocks who have an opening price smaller than $120 and a closing price greater than $120.  List the opening and closing prices in ascending order.
SELECT PriceID, ST_Open, ST_Close
FROM StockData
WHERE ST_Open < 120 AND ST_Close > 120
ORDER BY ST_Open, ST_Close ASC;

18.    List the price ID, stock high and stock low of all stocks that have a stock high above $130. Also list the same information for all stocks that have a stock low below $5.  List the high and low stocks by ascending order.
SELECT PriceID, ST_High, ST_Low
FROM StockData
WHERE ST_Low < 5 OR ST_High > 120
ORDER BY  ST_High, ST_Low ASC;
19.    List the trade dates and low stock prices of all stocks that were traded in 2011.  List the trade dates in ascending order.
SELECT TradeDate, ST_Low
FROM StockData
WHERE YEAR(TradeDate) = 2011
ORDER BY TradeDate ASC;
20.    List the sum of all the low stock prices that are above $60.  Call it TotalLowStockPriceOfStocksUnder60.
SELECT SUM(ST_Low) AS TotalLowStockPriceofStocksUnder60
FROM StockData
WHERE ST_Low > 60;
21.    List the highest Price ID with a low stock price under $80.
SELECT MAX(PriceID)
FROM StockData
WHERE ST_Low < 80;
22.    List the earliest trade date of the stock that ended up having a high over $100.  Call it EarliestTradeDateWithHighOver100.
SELECT MAX(TradeDate) as EarliestTradeDateWithHighOver100
FROM StockData
WHERE ST_High > 100;
23.    List the most recent trade date of a stock that has a low under $5.  Call it MostRecentTradeDateWithLowUnder5.
SELECT MAX(TradeDate) as MostRecentTradeDateWithLowUnder5
FROM StockData
WHERE ST_Low < 5;
24.    List the PriceID and TradeDate and stock high of all stocks that were traded before October 3, 2008 and had a stock high over $125. List the stock highs in descending order.
SELECT PriceID, TradeDate, ST_High
FROM StockData
WHERE TradeDate < '10/1/2008' AND ST_High > 125
ORDER BY ST_High;
25.    List the trade dates, stock highs and stock lows of all stocks that either, had a stock high over $130 and were traded before December 31, 2008, or stocks that were traded after December 31, 2010 and had a stock low below $5.  List the stock highs and lows in descending order.
SELECT TradeDate, ST_High, ST_Low
FROM StockData
WHERE (TradeDate < '12/31/2008' AND ST_High > 130) OR (TradeDate > '12/31/2010' AND ST_Low < 5)
ORDER BY ST_High DESC, ST_Low DESC;
26.    List the Price IDs and stock highs over $130 rounded to the nearest penny.
SELECT PriceID, ST_High, ROUND(ST_High,2)
FROM StockData
WHERE ST_High > 130;
27.    Return all attributes for stocks that have the MSFT ticker symbol and have a high stock price below $20 or a low stock price above $50.  List the high and low stock prices in ascending order.
SELECT *
FROM StockData
WHERE TickerSymbol = 'MSFT' AND ( ST_High < 20 OR ST_Low > 50)
ORDER BY ST_High, ST_Low;
28.    List the PriceID's in descending order of all the stocks that close with a price greater than $90 and less than $100.
SELECT PriceID
FROM StockData
WHERE ( ST_Close > 90 AND ST_Close < 100)
ORDER BY PriceID DESC;
29.    List the trade dates in descending order and open price in ascending order of all the stocks that open with a price greater than the average open price.
SELECT TradeDate, ST_Open
FROM StockData
WHERE ST_Open > (SELECT AVG(ST_Open) FROM StockData)
ORDER BY TradeDate DESC, ST_Open;
30.    List the volumes in descending order of all the stocks that have an opening price greater than or equal to $100 but with a closing price not equal to $120.
SELECT Volume
FROM StockData
WHERE ST_Open >= 100 AND ST_Close <> 120
ORDER BY TradeDate DESC;
31.    List the PriceID's in ascending order and the open prices of all the stocks that have an open price between $20 and $21.
SELECT PriceID
FROM StockData
WHERE ST_Open BETWEEN 20 AND 21
ORDER BY PriceID;
32.    List the closing prices in descending order of all the closing stock prices that were below $10 and were traded before January 1, 2007.
SELECT ST_Close
FROM StockData
WHERE ST_Close < 10 AND TradeDate = '1/1/2007'
ORDER BY ST_Close DESC;
33.    List the trade dates in descending order and open price in ascending order of all the stocks that open with a price greater than the average open price.
SELECT TradeDate, ST_Open
FROM StockData
WHERE ST_Open > (SELECT AVG(ST_Open) FROM StockData)
ORDER BY TradeDate DESC, ST_Open;
34.    List the number of stocks that have an opening price of $70 and were traded in 2008.  Call it NumberOfOpeningPricesOf70In2008.
SELECT COUNT(ST_Open) as NumberOfOpeningPricesOf70In2008
FROM StockData
WHERE ST_Open > 70 AND YEAR(TradeDate) = 2008;
35.    List the number of stocks that have a greater than average high price and were traded on September 9, 2011.  Call it NumberOfStocksAboveAverageHighIn2011.
SELECT COUNT(PriceID) as NumberOfStocksAboveAverageHighIn2011
FROM StockData
WHERE ST_High > (SELECT AVG(ST_High) FROM StockData) AND TradeDate = '9/9/2011';
36.    List the highest high price of all the stocks that have been traded in 2011 and have a low price less than $100.
SELECT MAX(ST_High)
FROM StockData
WHERE YEAR(TradeDate) = 2011 AND ST_Low < 100;
37.    List the number of trade dates of stocks that were traded in the month of August in 2010.
SELECT COUNT(TradeDate)
FROM StockData
WHERE TradeDate BETWEEN '8/1/2010' AND '8/31/2010';
38.    List the industry and the number of different companies in each industry.
SELECT Industry, COUNT(*) as Different_Companies
FROM Companyinformation
GROUP BY Industry;

39.    List the industry and the number of different companies in each industry. Put the answer in order of most stocks to least stocks.
SELECT Industry, COUNT(distinct TickerSymbol) as Different_Companies, Count(PriceID) as Stocks
FROM  StockData
GROUP BY Industry
ORDER BY Stocks DESC;

40.    List the industry and the number of different companies in each industry for only those industries that have more than three companies.
SELECT Industry, COUNT(distinct TickerSymbol) as Different_Companies
FROM  StockData
GROUP BY Industry
HAVING COUNT(distinct TickerSymbol) > 3

41.    List the ticker symbol and the average closing for a stock that has the largest average closing price.
SELECT TickerSymbol, AVG(ST_Close) AS LargestAvgClose
FROM StockData
GROUP BY TickerSymbol
HAVING AVG (ST_Close) >= ALL
(SELECT AV(ST_CLOSE)
FROM StockData
GROUP BY TickerSymbol


42.    List the price ID and the high stock price of the two stocks with the most expensive high prices that are still less than $100.
SELECT TOP 2 PriceID, ST_HIGH
FROM StockData
WHERE ST_High < 100
ORDER BY ST_High DESC;

43.     Add a new row to the Stock Data table.  This stock should have the Microsoft ticker symbol (MSFT) in the Tech Industry.  The stock was traded on July 13, 2012.  It had an opening price of $28.76, a high price of $29.33, a low price of $28.72, a closing price of $29, and a volume of 23,320,365.
INSERT INTO StockData VALUES ('MSFT','Tech','7/13/2012',28.76,29.33,28.72,29,23320365);

44.    It's October 3, 2012 and you need to add information to the StockData table for Microsoft on that date.  You have to wait until the end of the day before you can put any of the prices because you don't know the closing price yet.  For now just add Microsoft's trade date, Industry, and ticker symbol to the table.
INSERT INTO StockData (TickerSymbol, Industry, TradeDate) VALUES ('MSFT','Tech','10/3/2012');
45.    Remove all the rows of stocks that were traded before January 1, 2001 for Microsoft.
DELETE FROM StockData
WHERE TradeDate < '1/1/2001' AND TickerSymbol = 'MSFT';

46.    Make a change for the Microsoft row for July 13, 2012.  Change the opening stock price to $27.50, the high price to $30.75, the low price to $26.75, and the closing stock price to $29.80, and the volume to 3,320,365.
UPDATE StockData
SET
 ST_Open = 27.5, ST_High = 30.75, ST_Low = 26.75, ST_Close = 29.8, Volume = 3320365
WHERE TradeDate = '7/13/2012' AND TickerSymbol = 'MSFT';

47.    From the stock data table please list the ticker symbol, date, opening and closing stock prices, and day type of all stocks that were traded on a Holiday in 2011.
SELECT s.Ticker_Symbol, s.Trade_Date, s.ST_Open, s.ST_Close, c.DayType
FROM StockData AS s, Calendar AS c
WHERE YEAR(Trade_Date)=2011
AND DayType='Holiday';

48.    List every date that falls on a Friday from the Calendar table regardless of whether there are matching rows from the StockData table.
SELECT ActualDate
FROM Calendar
WHERE DayOfWeek='Friday';

49.    List the day of week and every date that falls on a weekend from the Calendar table and all rows from the StockData table.

SELECT c.ActualDate, DayOfWeek, sd.*
FROM Calendar AS c FULL JOIN StockData AS sd
ON (sd.TradeDate=c.ActualDate)
WHERE DayType='Weekend';

50.    Remove all the rows of stocks of Microsoft that were traded during the month of October in the year 2011.
DELETE FROM StockData
WHERE Ticker_Symbol='MSFT'
AND YEAR(Trade_Date)=2011
AND MONTH(Trade_Date)=10;

51.    List the ticker symbol and the percentage of each ticker symbol's total number of trading days to the total number of days for all stocks. (Cast will change the data type from a thing to a model. Cast works with all data bases, convert is only SQL. Cast just changes the appearance)
SELECT TickerSymbol, CAST(COUNT(*) AS DECIMAL (21,13))/ (SELECT CAST(COUNT(*) AS DECIMAL (21,13))
FROM StockData) AS PercentOfTotal
FROM StockData
GROUP BY TickerSymbol
Order BY PercentOfTotal DESC;
SELECT CAST (GETDATE() AS INT);

52.    List the ticker symbol, the industry (only for the tech industry), the row number and the closing stock price such that the highest prices are listed first and the lowest prices are listed last.
SELECT TickerSymbol, Industry, ROW_NUMBER() OVER(ORDER BY ST_Close DESC) AS 'Row Number', ST_Close
FROM StockData
WHERE Industry = 'Tech';

53.    List the ten largest differences (from greatest to least) between a daily high and low stock price along with the accompanying TickerSymbol, Industry, and TradeDate.
SELECT TOP 10 TickerSymbol, Industry, TradeDate, ST_High - ST_Low AS DIFF
FROM StockData
ORDER BY DIFF DESC;

54.    List each ticker symbol and the average daily trade volume for that stock. Order the list from highest to least daily trade volume.
SELECT Ticker_Symbol, AVG(Volume) AS avgVolume
FROM StockData a, Calendar b
WHERE a.Trade_Date = b.ActualDate
GROUP BY Ticker_Symbol, Trade_Date
ORDER BY avgVolume DESC;

55.    List each ticker symbol and the average daily trade volume for that stock on Fridays. Order the list from highest to least daily trade volume.
SELECT Ticker_Symbol, AVG(Volume) AS AvgVol
FROM StockData a, Calendar b
WHERE a.Trade_Date = b.ActualDate
AND b.DayOfWeek = 'Friday'
GROUP BY Ticker_Symbol, Trade_Date
ORDER BY AvgVol DESC;

56.    List each ticker symbol, day of the week, and the average daily trade volume for that stock on each day of the week. Order the list by ticker symbol and then by highest to least daily trade volume.
SELECT a.Ticker_Symbol, b.DayOfWeek, AVG(Volume) AS avgVolume
FROM StockData a, Calendar b
WHERE A.Trade_Date = B.ActualDate
GROUP BY Ticker_Symbol, DayOfWeek, Trade_Date
ORDER BY Ticker_Symbol, avgVolume DESC;

57.    List the ten most common closing prices along with the number of times each occurs.
a.       SELECT TOP 10
SELECT TOP 10 ST_Close, COUNT(ST_Close)
FROM StockData
GROUP BY ST_Close;

58.    List all information from the rows sharing the single most common closing price.
SELECT * FROM StockData
WHERE ST_Close =
(SELECT TOP 1 ST_Close
FROM StockData
GROUP BY ST_Close
ORDER BY COUNT(*) DESC);

59.    Cleaning, Exploring, and Validating Data
60.    When you are given a new data set, or when you load one into a database, it can be extremely helpful to verify that the data make sense before running any analytical queries. If you had just imported StockData, the following steps would be useful to explore the data.

61.    List 1000 rows of the table to see what the data look like. (On a production server, you would not select the entire table, but a limited number of rows. Otherwise you risk slowing down the server, particularly if the table is very large.)

62.    Note:  this can also be done by right-clicking on the table name in SSMS and selecting “Select Top 1000 Rows”

SELECT TOP 1000 *
FROM StockData


63.    Look for missing data by listing any rows in StockData that contain nulls.
SELECT *
FROM StockData
WHERE ST_Open = NULL;

64.    List the percentage of records containing nulls.
SELECT (SELECT COUNT(*)
FROM StockData
WHERE TickerSymbol IS NULL OR Industry IS NULL OR
TradeDate IS NULL OR ST_Open IS NULL OR
ST_High IS NULL OR ST_Low IS NULL OR
ST_Close IS NULL OR Volume IS NULL)
/ CAST(COUNT(*) AS DECIMAL(21,13)) * 100 --* 100 makes it a percentage
FROM StockData;

65.    List all rows where the high stock price for the day is not at least as high as the low for that day. There should not be any rows where this is the case.
SELECT *
FROM StockData
WHERE ST_High < ST_Low;

66.    List all rows where any of the stock prices are zero.
SELECT *
FROM StockData
WHERE (ST_Open = 0 ) OR (ST_High = 0 ) OR (ST_Low = 0 ) OR (ST_Close = 0 );


67.    Stock prices should never be zero. These data are incorrect. The problem with leaving inaccurate data is that it will throw off other analyses (averages, etc.). If you found this data, you would have some options:

68.    Delete all rows with impossible (zero) values. Try to find the correct values and correct the records. Set the offending values to null, meaning you don’t know the correct value.

DELETE
FROM StockData
WHERE (ST_Open = 0 ) OR (ST_High = 0 ) OR (ST_Low = 0 ) OR (ST_Close = 0 );

69.    Set any opening stock prices that are zero to null.
UPDATE StockData
SET ST_Open = NULL
WHERE ST_Open = 0;

70.    List the largest single-day stock price increase for Ford (between the market opening and closing).
SELECT MAX(ST_Close - ST_Open)
FROM StockData
WHERE TickerSymbol = 'F';

71.    List the trade date and the amount the stock price increased for the day on which the price of Ford stock had the largest price increase.
b.       SELECT TradeDate, (ST_CLOSE – ST_Open) * 100
SELECT TOP 1 TradeDate, ST_Close - ST_Open as PriceInc
FROM StockData
WHERE TickerSymbol = 'F'
ORDER BY ST_Close - ST_Open DESC;


72.    List the largest single-day stock price percentage increase for Ford (between the market opening and closing).
SELECT TradeDate, (PriceInc/ST_Open) * 100 AS PercentagePriceIncreased
 ST_Open,ST_Close
FROM(SELECT TOP 1 TradeDate, (ST_Close - ST_Open) as PriceInc, ST_Open,ST_Close
FROM StockData
WHERE TickerSymbol = 'F'
ORDER BY ST_Close - ST_Open DESC)T;

73.    List the trade date and the percentage of the stock price increase for the day on which Ford stock had the largest percentage gain.
SELECT TradeDate, (ST_Close - ST_Open) AS AmountPriceIncreased
FROM StockData
WHERE TickerSymbol = 'F' AND (ST_Close - ST_Open) =
(SELECT MAX(ST_Close - ST_Open) FROM StockData WHERE TickerSymbol = 'F');

74.    List the lowest opening price for Ford stock in 2008.
SELECT MIN(ST_Open)
FROM StockData
WHERE TickerSymbol = 'F' AND TradeDate >= '1/1/2008' AND TradeDate < '1/1/2009';

75.    List the highest closing price for Ford in 2011.
SELECT MAX(ST_Open)
FROM StockData
WHERE TickerSymbol = 'F' AND TradeDate >= '1/1/2011' AND TradeDate < '1/1/2012';

76.    List the amount you would have made if you had purchased 1000 shares of Ford stock at its lowest opening price in 2008 and then sold it at its highest closing price in
c.        SELECT ((SELECT MAX(ST_CLOSE)
d.       FROM StockData
e.       WHERE TickerSymbol = ‘F’ AND TradeDate >= ‘20110101’
f.         AND TradeDate < ‘20120101’) * 1000)
  SELECT ((SELECT MAX(ST_Close) 
FROM StockData 
WHERE TickerSymbol = 'F' AND TradeDate >= '20110101' AND TradeDate < 

'20120101') * 1000)
-
((SELECT MIN(ST_Open) 
FROM StockData 
WHERE TickerSymbol = 'F' AND TradeDate >= '20080101' AND TradeDate < 

'20090101') * 1000)

77.    List the percentage increase that the above amount represents relative to the original investment.
SELECT ((((SELECT MAX(ST_Close)
              FROM StockData
              WHERE TickerSymbol = 'F' AND TradeDate >= '20110101'
                     AND TradeDate < '20120101') * 1000)
       -
       ((SELECT MIN(ST_Open)
              FROM StockData
              WHERE TickerSymbol = 'F' AND TradeDate >= '20080101'
                     AND TradeDate < '20090101') * 1000)) * 100)
       /
       ((SELECT MIN(ST_Open)
       FROM StockData
       WHERE TickerSymbol = 'F' AND TradeDate >= '20080101'
              AND TradeDate < '20090101') * 1000);

78.    List the months of the year and their corresponding average closing prices of Ford stock. List the months with the lowest average closing prices first. (ON EXAM HE WILL LOOK FOR COUNT DISTINCT WHEN HE SAYS UNIQUE VALUES) Example: Count Distinct David Olsen. The more columns you add, the more likely you will be unique. Distinct does not
g.       SELECT DATENAME(month, TradeDate) AS MonthOfYear, AVG (ST_Close) As AverageClosingPrice
h.       From StockData
i.         WHERE TickerSymbol = ‘F’
j.         GROUP BY DATENAME(month, TradeDate)
k.        ORDER BY AVG (ST_Close);
SELECT DATENAME(month, TradeDate) AS MonthOfYear, AVG(ST_Close) AS AverageClosingPrice
FROM StockData
WHERE TickerSymbol = 'F'
GROUP BY DATENAME(month, TradeDate)
ORDER BY AVG(ST_Close);

79.    Suppose we have a theory that stocks dropped in value after September 11, 2001. List the minimum closing price of Ford stock in September 2001 before September 11.
SELECT TickerSymbol, MIN(ST_Close) AS LowestClosingPrice
FROM StockData
WHERE TradeDate >= '9/1/2001' AND TradeDate < '9/11/2001' AND TickerSymbol = 'F'
GROUP BY TickerSymbol;

80.    Now list the minimum closing price of Ford stock in September 2001 after September 11.
SELECT TickerSymbol, MIN(ST_Close) AS LowestClosingPrice
FROM StockData
WHERE TradeDate > '09/11/2001' AND TradeDate <= '09/30/2001' ANDTickerSymbol = 'F'
GROUP BY TickerSymbol;

81.    List the number of days in the table when a trade occurred (when the trade volume for any stock wasn’t zero).
SELECT COUNT(DISTINCT(TradeDate)) AS DaysWithTrades
FROM StockData
WHERE Volume <> 0;

82.    List the number of trade days in each month of 2001. Sort the list from least to greatest number of trade days.
SELECT DATENAME(MONTH, TradeDate) AS MonthOfYear, COUNT(DISTINCT(TradeDate)) AS TradeDays
FROM StockData
WHERE Volume > 0 AND TradeDate > '12/31/2000' AND TradeDate < '1/1/2002'
GROUP BY DATENAME(MONTH, TradeDate)
ORDER BY COUNT(DISTINCT(TradeDate));

83.    List the number of trade days in each month of 2001. Also include the average trade volume for each month. Sort the list from least to greatest number of trade days.
SELECT DATENAME(month, TradeDate) AS MonthOfYear, COUNT(DISTINCT(TradeDate)) AS TradeDays, AVG(Volume) AS AvgVol
FROM StockData
WHERE Volume > 0 AND TradeDate > '12/31/2000' AND TradeDate < '1/1/2002'
GROUP BY DATENAME(month, TradeDate)
ORDER BY COUNT(DISTINCT(TradeDate));

84.    List the industries in alphabetical order and the number of companies in each industry in the table.
SELECT Industry, COUNT(DISTINCT TickerSymbol) AS Companies
FROM StockData
GROUP BY Industry
ORDER BY Industry;

85.    List the industries in alphabetical order and the number of companies in each industry in the table. Also include the range of dates and the number of unique dates for each industry.
SELECT Industry, COUNT(DISTINCT TickerSymbol) AS Companies, DATEDIFF(day, MIN(TradeDate), MAX(TradeDate)) AS TradeDateRange, COUNT(DISTINCT(TradeDate)) AS DistinctTradeDates
FROM StockData
GROUP BY Industry
ORDER BY Industry;

86.    List the industries in alphabetical order and the number of companies in each industry in the table. Also include the ratio of unique dates to the range of dates for each industry.
SELECT Industry, COUNT(DISTINCT TickerSymbol) AS Companies, COUNT(DISTINCT(TradeDate)) * 1.0 / DATEDIFF(day, MIN(TradeDate), MAX(TradeDate)) AS TradeDateRatio
FROM StockData
GROUP BY Industry
ORDER BY Industry;


87.    List the five rows with the highest ratio of daily high stock price to daily low stock price.
SELECT TOP 5 *
FROM StockData
ORDER BY (ST_High / ST_Low) DESC;

88.    List the number of shares (including fractions of shares) you could have purchased with $2000 at the low price on the day when the high to low price ratio was highest.
SELECT TOP 1 (2000 / ST_Low) AS SharesPurchased
FROM StockData
ORDER BY (ST_High / ST_Low) DESC;

89.    List the total selling price of the shares above if you sold them at the daily high price on the same day.
SELECT TOP 1 (2000 / ST_Low ) * ST_High AS SharesPurchased
FROM StockData
ORDER BY (ST_High / ST_Low) DESC;


90.    On October 9, 2008, you think the stock market is heading down so you panic and sell all of your stocks. If you had owned 200 shares of each stock in your table, list the amount you would have sold them for at the closing price on that date.
SELECT (200 * SUM(ST_Close)) AS OneShare
FROM StockData
WHERE TradeDate = '10/9/2008';

91.    On October 9, 2012, you think the stock market is healthy and you decide to purchase your 200 shares of each stock back. How much will this cost?
SELECT (200 * SUM(ST_Close)) AS OneShare
FROM StockData
WHERE TradeDate = '10/9/2012'


92.    List the amount you made (or lost) by “wisely” selling when the market is struggling and buying when the market is “healthy.”


93.    List all of the ticker symbols containing the letter S.
SELECT DISTINCT TickerSymbol
FROM StockData
WHERE TickerSymbol LIKE '%S%'

94.    List the all of the ticker symbols containing the letter S and their average closing price in 2012.

SELECT TickerSymbol, AVG(ST_Close) AS AvgClose
FROM StockData
WHERE TickerSymbol LIKE '%S%' AND TradeDate > '12/31/2011' AND TradeDate < '1/1/2013'
GROUP BY TickerSymbol;

95.    List the number of times you could have made at least a 10% profit by purchasing stocks at its lowest price for the day and selling the stock at its highest price for the day.
SELECT COUNT(*) AS NumberOfRows
FROM StockData
WHERE ST_High >= (ST_Low * 1.1);

96.    List the records on which you could have made at least a 10% profit by purchasing stocks at its lowest price for the day and selling the stock at its highest price for the day.
SELECT * FROM StockData
WHERE ST_High >= (ST_Low * 1.1)

97.    List the relevant ticker symbols and the number of times you could have made at least a 10% profit on that stock by purchasing the stock at its lowest price for the day and selling the stock at its highest price for the day. List the ticker symbol for which this occurs most frequently first.
SELECT TickerSymbol, COUNT(*) AS NumberOfTimes
FROM StockData
WHERE ST_High >= (ST_Low * 1.1)
GROUP BY TickerSymbol
ORDER BY COUNT(*) DESC;

98.    List the relevant ticker symbols and the number of times you could have had at least a 10% loss on that stock by purchasing the stock at its highest price for the day and selling the stock at its lowest price for the day. List the ticker symbol for which this occurs most frequently first.

SELECT TickerSymbol, COUNT(*) AS NumberOfTimes
FROM StockData
WHERE (ST_High - ST_Low) / ST_High > .1
GROUP BY TickerSymbol
ORDER BY COUNT(*) DESC;

99.    List the five rows with the highest price multiplied by volume. Use their closing price as the price for the entire day.
SELECT TOP 5 *
FROM StockData
ORDER BY (ST_Close * Volume) DESC;


100.List the ticker symbols and the average price multiplied by volume for each ticker symbol in 2011. Use the closing price of as the price for the entire day. List the ticker symbol with the highest average price times volume first.
SELECT TickerSymbol, AVG(ST_Close * Volume) AS AvgPriceVolume
FROM StockData
WHERE TradeDate > '12/31/2010' AND TradeDate < '1/1/2012'
GROUP BY TickerSymbol
ORDER BY AVG(ST_Close * Volume) DESC;

101.List the ticker symbol, year, and the average price multiplied by volume for each year for Apple stock. Use the closing price as the price for the entire day.
SELECT TickerSymbol, DATENAME(year, TradeDate) AS [Year], AVG(ST_Close * Volume) AS AvgPriceVolume
FROM StockData
WHERE TickerSymbol = 'AAPL'
GROUP BY DATENAME(year, TradeDate), TickerSymbol
ORDER BY DATENAME(year, TradeDate) ;



NOTES:
Union compatible must have the same domain/*same structure
Know Cartesian product
Add attributes, times the tuples – 5 columns, 70 rows.
T or F – Cross join is a Cartesian product (TRUE) No common columns are required.

Relational divide

Comments

Post a Comment

Popular posts from this blog

Comparative analysis of algorithms: Developing CPU scheduling algorithms and understanding their impact in practice can be difficult and time  consuming due to the need to modify and test operating system kernel code and measure the resulting performance on  a consistent workload of real applications. In a single-processor system, only one process can run at a time; any others  must wait until the CPU is free and can be rescheduled. The objective of multiprogramming is to have some process  running at all times, to maximize CPU utilization .  Scheduling is a fundamental operating-system function. Almost all computer resources are scheduled before use.  The CPU is, of course, one of the primary computer resources. Thus, its scheduling is central to operating-system  design. CPU scheduling determines which processes run when there are multiple run-able processes. CPU scheduling  is important because it can have a big effect on resource utilizati...
Read more

Gaming in Pygame

Game Development in python using Pygame is fun. Its a library that constitutes almost all the functions that can help us create a decent game. This is the code for a game called catalicious that I created using Pygame library. Its not a very elegant game in my opinion but a fine attempt on gaming. This have given me a really strong understanding of how to create simple concept games. I was looking into KIVY library before starting this game in pygame. I found Pygame easier to implement. Although the next attempt will be in kivy, I am not sure what to comment exactly as I dont know how well KIVY library can perform as far as gaming is concerned. In my opinion KIVY is more sophisticated library as compared to Pygame in terms of more built in functions. Please let me know in comments what do you think about it. This is the code for the game in which cat dodges obstacles and scores points. Make sure you have a cat image and background image in your folder(With exact dimensions and name o...
Read more