SQL DATA NINJA

1. List the price ID, ticker symbol, trade date, stock closing price, city where the company is located, and the state. Only include the rows where the state is either Texas, California, or Washington. 2. List the price ID , ticker symbol, trade date, the stock closing price , the city where the company is located, and the state. Exclude the rows where the state is either Arkansas, Minnesota, or Idaho. 3. List the price ID , ticker symbol, trade date, the stock closing price , the city where the company is located, and the state. Do so for the dates starting June 8th, 2017 to July 26th, 2020. 4. List the price ID, ticker symbol, trade date, the stock closing price, the day of the week and the day type. Exclude day types that are business and exclude days of the week that are Sunday. 5. List the ticker symbol, trade date, stock high price, stock low price for business days only. Also, only include those rows that have a high price more than five dollars higher than the low price. 6. List the ticker symbol, trade date, stock high price and day of the week. Do so only for January and days of the week that begin with the letter T. 7. List the ticker symbol, trade date, stock opening price and day of the week. Do so for only the 23rd of the month and for days of the week that do not begin with the letter T. 8. List the ticker symbol, trade date, stock opening price, day of the week, city, state and zip code. Do so only for the fifth day of the month and for cities that have tt in them. 9. Please list the first three characters of the TickerSymbol attribute for the CompanyInformation table. 10. What is todays date? 11. How many minutes difference is there between the oldest and youngest members of your group? 12. How much older is Rasputin (1/01/1869) than Tommy Lee, '10/03/1962') in seconds? 13. List every date that falls on a Friday from the Calendar table regardless of whether there are matching rows from the Stock Data table. 14. List the ticker symbol, trade date, closing stock price, the phone number and city where the company is located. Also, list every row in the stock data table to make sure it has an associated row in the company information table. 15. 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. 16. 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. 17. 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. 18. From the stock data table, list the leaps years only once. Perform your test on the trade date column. 19. List the number of trade days in each month of 2020. Also include the average trade volume for each month. Sort the list from least to greatest number of trade days. 20. 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. 21. 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. 22. List each industry and the number of values in the volume column for each industry. Name the new column NumOfVolumes. Include only those industries that have more than 6000 volume values. List your answer such that the industries with the highest number of volumes are listed first and those with the lowest number of volumes are listed last. 23. List the industry and the number of different companies in each industry for only those industries that have more than three companies. 24. 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 2020. 25. From the stock data table, please list the ticker symbol, industry, trade date, opening, closing, high, and low stock prices as well as the volume of all stocks that are from California. 26. List all the columns and rows from StockData, Calendar, and CompanyInformation tables. Join the tables on the appropriate attributes. 27. List the day of week and every date that falls on a weekend from the Calendar table and all matching rows from the stock data table. 28. Please return all combinations of Toys and Colors. Note: The Toys and Colors table are from Assignment 2. If you dont have in your database, you can use the below code to generate the tables. CREATE TABLE Colors( ColorID int NULL, Shade char(15) NULL ) GO CREATE TABLE Toys( ToyID int NULL, ToyName char(15) NULL ) GO INSERT Colors(ColorID, Shade) VALUES (2, 'Blue'); INSERT Colors(ColorID, Shade) VALUES (3, 'Brown'); INSERT Colors(ColorID, Shade) VALUES (4, 'Black'); INSERT Colors(ColorID, Shade) VALUES (5, 'Purple'); INSERT Colors(ColorID, Shade) VALUES (6, 'Green'); INSERT Colors(ColorID, Shade) VALUES (7, 'Yellow'); INSERT Colors(ColorID, Shade) VALUES (8, 'White'); INSERT Colors(ColorID, Shade) VALUES (9, 'Red'); GO INSERT Toys(ToyID, ToyName) VALUES (10, 'Train'); INSERT Toys(ToyID, ToyName) VALUES (11, 'Ball'); INSERT Toys(ToyID, ToyName) VALUES (12, 'YoYo'); INSERT Toys(ToyID, ToyName) VALUES (13, 'Jacks'); INSERT Toys(ToyID, ToyName) VALUES (14, 'Doll'); INSERT Toys(ToyID, ToyName) VALUES (15, 'Wooden Horse'); INSERT Toys(ToyID, ToyName) VALUES (16, 'Truck'); INSERT Toys(ToyID, ToyName) VALUES (17, 'Lincoln Logs'); GO 29. Please return all combinations of Toys and Colors. Include only those toys with and ID larger than 12 and please exclude colors that begin with the letter B. 30. 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. 31. 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. 32. List the number of stocks that have a greater than average high price that were traded on March 9th, 2020. Call it NumberOfStocksAboveAverageHighIn2020. 33. List the ticker symbol and the average closing for a stock that has the largest average closing price. 34. 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. 35. Please list the first day and last day of the current quarter.