SQL Queries Flashcards
(107 cards)
1
Q
Create a stored procedure for all stocks in 2014 with a stock high of 100. Execute the stored procedure afterwards. Call the stored procedure 2014StocksGreater100.
A
CREATE PROC SP_2014StocksGreater100
AS
SELECT *
FROM StockData
WHERE ST_High > 100 AND YEAR(TradeDate) = 2014;
EXEC SP_2014StocksGreater100
2
Q
Create a stored procedure that lists all attributes for TickerSymbol that is returned by the user.
A
CREATE PROCEDURE ChooseStock1 @Tick CHAR(10)
AS
SELECT *
FROM StockData
WHERE TickerSymbol = @Tick;
EXEC ChooseStock1 ‘aapl’
3
Q
Create a stored procedure that lists the industry and the number of different companies in each industry. The industry should be returned by the user.
A
CREATE PROC indcompany @industry nchar(6)
AS
SELECT Industry, COUNT(DISTINCT TickerSymbol)
FROM stockdata
WHERE Industry = @industry
GROUP BY Industry;
EXEC indcompany ‘tech’
4
Q
Create a stored procedure that allows to enter a new record into StockData
A
CREATE PROC EnterStock @PID CHAR(10), @SO FLOAT, @SC FLOAT
AS
INSERT INTO StockData (PriceID, ST_Open, ST_Close)
VALUES (@PID, @SO, @SC);
EXEC EnterStock ‘1321321’, 1.21, 1.01
5
Q
List todays’ date, yesterday’s date , the current year, the number of days and months that have elapsed since 9/11.
A
SELECT GETDATE(), DATEADD(dd, -1, GETDATE()), DATEADD(dd, 1, GETDATE()), YEAR(GETDATE()), DATEDIFF(dd,’9/11/2001’, GETDATE()), YEAR(GETDATE()), DATEDIFF(mm,’9/11/2001’, GETDATE())
6
Q
List the mean, variance, and standard deviation of volumes.
A
SELECT AVG(Volume), VAR(Volume), STDEV(Volume)
FROM stockdata;
7
Q
List area codes of PhoneNumber
A
SELECT SUBSTRING(PhoneNumber,1,3)
FROM CompanyInformation;
SELECT LEFT(PhoneNumber,3)
FROM CompanyInformation;
SELECT RIGHT(PhoneNumber,3)
FROM CompanyInformation;
8
Q
List the middle digits of PhoneNumber
A
SELECT SUBSTRING(PhoneNumber,5,3)
FROM CompanyInformation;
SELECT LEN(Phonenumber)
9
Q
List industry in upper case.
A
SELECT UPPER(Industry)
FROM stockdata;
SELECT LOWER(Industry)
FROM stockdata;
10
Q
Remove leading blanks in Address
A
SELECT LTRIM(Address)
FROM CompanyInformation;
11
Q
-List the number of stocks that have a greater than average high price and were traded on September 9th, 2011. Call expensive stocks.
A
SELECT COUNT(ST_High)AS ExpensiveStocks
FROM StockData
WHERE ST_High > (SELECT AVG (ST_High) FROM StockData) AND TradeDate = ‘09/09/2011’;
12
Q
List all information from the rows sharing the single most common closing price.
A
SELECT * from StockData where ST_Close = (SELECT Top 1 ST_close FROM StockData GROUP BY ST_Close ORDER BY COUNT(ST_Close) DESC);
13
Q
-List calendar dates on which stock trading was recorded.
A
SELECT ActualDate
FROM Calendar
WHERE ActualDate NOT IN (SELECT TradeDate FROM Stockdata);
SELECT *
FROM Calendar;
14
Q
List the ticker symbol and the average closing for a stock that has the largest average closing price.
A
SELECT TOP 1 TickerSymbol, AVG (ST_Close) AS AverageClose
FROM StockData
GROUP BY TickerSymbol
ORDER BY AverageClose DESC;
15
Q
Write a query that lists the ticker symbol and the number of trade dates for those stocks that have more than 2000 trade dates.
A
Select tickersymbol, count(distinct tradedate)
from stockdata
where (select count(distinct tradedate)from stockdata)>2000
group by tickersymbol
16
Q
Write a query that lists the TickerSymbol and average volume for those stocks that have an average greater than three million shares.
A
select tickersymbol, avg (volume)
from stockdata
where (select avg(volume) from stockdata)>3000000
Group by tickersymbol
17
Q
Write a query that lists the TickerSymbol and average volume for those stocks that have a total shares traded greater than ten million shares.
A
select tickersymbol, avg(volume) as averagevolume , sum(volume) as sumofvolume
from stockdata
where (select sum(volume) from stockdata)>10000000
group by tickersymbol
18
Q
List the TickerSymbol, Industry, the row number, and the stock close price for the tech industry. Arrange the answer to be in order of the highest closing price to the lowest closing price.
A
Select tickersymbol, industry ,ROW_NUMBER() OVER(ORDER BY tickersymbol) AS ‘row number’, ST_close
from stockdata
where industry=’TECH’
Order by ST_close desc
19
Q
Create a stored procedure that lists all attributes for TickerSymbol that is returned by the user.
A
create proc tickersymbolattributes @tick nchar(10) as SELECT * FROM StockData WHERE TickerSymbol = @Tick; exec tickersymbolattributes 'hon'
20
Q
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.
A
select tradedate, st_open
from stockdata
where st_open > (select avg(st_open)from stockdata)
order by tradedate desc, st_open asc
21
Q
List the number of stocks that have an opening price of 70 dollars and were traded in 2013. Call it NumberOfOpeningPricesOf70In2013
A
Select count(tickersymbol) as NumberofOpeningPricesof70in2013
from stockdata
where ST_open=70 and year(tradedate)=2013
Order by count(tickersymbol)
22
Q
List the number of stocks that have a greater than average high price and were traded on September 9th, 2013. Call it NumberOfStocksAboveAverageHighIn2013.
A
select count(volume) as StocksGreaterthanAverageHighPriceSept92013 from stockdata where st_high>(select avg(st_high) from stockdata) and tradedate='09/09/2013'
23
Q
List the highest high price of all the stocks that have been traded in 2013 and have a low price less than 100 dollars.
A
Select max(ST_high) as HighPriceStocksTradedin2014
from stockdata
where ST_low
24
Q
List the number of trade dates of stocks that were traded in the month of August in 2014.
A
Select count(distinct tradedate) from stockdata where month(tradedate)=08 and year(tradedate)= 2014
25
List the industry and the number of different companies in each industry.
Select Industry, count(distinct tickersymbol)
from stockdata
group by industry
26
List the industry and the number of different companies in each industry. Put the answer in order of my stocks to least stocks.
select industry, count(distinct tickersymbol) as CompaniesPerIndustry
from stockdata
Group by Industry
Order by CompaniesPerIndustry desc
27
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 CountofIndustryCompaniesMorethan3
from stockdata
where (select count(distinct tickersymbol) from stockdata)>3
Group by industry
ORder by CountofIndustryCompaniesMorethan3
28
Create a stored procedure for all stocks in 2014 with a stock high of 100. Execute the stored procedure afterwards. Call the stored procedure Get2014StocksWithHighAbove100.
```
create proc Get2014stockswithhighat100
as
select*
from stockdata
where st_high=100
```
29
Create a stored procedure to find the number of stocks that have a high stock price over 130 dollars. Execute the stored procedure afterwards. Call the stored procedure NumStocksHighAbove130.
```
create proc Numstockshighgreaterthan130
as
select count(distinct tickersymbol) as Numstockshighabove130
from stockdata
where st_high>130
```
exec Numstockshighgreaterthan130
30
Create a stored procedure to display the price ID, high stock price, and low stock price of all stocks that have a high stock price below 100 dollars and a low stock price greater than 90 dollars. Execute the stored procedure afterwards. Call the stored procedure GetHighBelow100LowOver90.
```
Create proc GetHighBelow100LowOver90
as
Select PriceID, ST_high, ST_low
From stockdata
where st_high90
```
exec GetHighBelow100LowOver90
31
Create a stored procedure to display the trade date, closing stock price, and low stock price of all stocks that have a low stock price less or equal to 100 dollars and a closing price greater than or equal to 100 dollars. Execute the stored procedure afterwards. Call the stored procedure GetCloseUnder100LowOver100.
```
create proc GetCloseUnder100LowOver100
as
Select tradedate, st_close, st_low
from stockdata
where st_low=100
```
exec GetCloseUnder100LowOver100
32
Create a stored procedure for all stocks in 2013 with a stock high of 100. Execute the stored procedure afterwards. Call the stored procedure Get2013StocksWithHighAbove100.
create proc Get2013StocksWithHighAbove100
as
Select*
from stockdata
where st_high=100 and year(tradedate)=2013
exec Get2013StocksWithHighAbove100
33
Write a query that lists the ticker symbol and the number of trade dates for those stocks that have more than 2000 trade dates.
Select tickersymbol, count(distinct tradedate)
from stockdata
where (select count(distinct tradedate)from stockdata)>2000
group by tickersymbol
34
Write a query that lists the TickerSymbol and average volume for those stocks that have an average greater than three million shares.
select tickersymbol, avg (volume)
from stockdata
where (select avg(volume) from stockdata)>3000000
Group by tickersymbol
35
Write a query that lists the TickerSymbol and average volume for those stocks that have a total shares traded greater than ten million shares.
select tickersymbol, avg(volume) as averagevolume , sum(volume) as sumofvolume
from stockdata
where (select sum(volume) from stockdata)>10000000
group by tickersymbol
36
List the TickerSymbol, Industry, the row number, and the stock close price for the tech industry. Arrange the answer to be in order of the highest closing price to the lowest closing price.
Select tickersymbol, industry ,ROW_NUMBER() OVER(ORDER BY tickersymbol) AS 'row number', ST_close
from stockdata
where industry='TECH'
Order by ST_close desc
37
Create a stored procedure that lists all attributes for TickerSymbol that is returned by the user.
```
create proc tickersymbolattributes @tick nchar(10)
as
SELECT *
FROM StockData
WHERE TickerSymbol = @Tick;
exec tickersymbolattributes 'hon'
```
38
List all columns and all rows from the StockData table.
Select*
| From StockData
39
List the TickerSymbol, Industry, TradeDate and the Volume columns from the StockData table. List all rows.
Select tickersymbol, Industry, TradeDate, Volume
| From StockData ;
40
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 ;
41
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. Arrange the answer in TickerSymbol order. This means for example that British Petroleum should appear before Sunoco.
Select tickersymbol, Industry, TradeDate, Volume
From StockData
Where Volume > 30000000
Order By TickerSymbol ;
42
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. 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 ;
43
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 '2011-1-1' and '2011-12-31'
Order By TradeDate
44
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.
```
select tickersymbol, industry, tradedate, st_open, st_close
from stockdata
where year(tradedate)=2013
order by industry, tickersymbol,tradedate
```
45
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 = 'Oil & Gas'
Order By Industry, TickerSymbol,TradeDate ;
46
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 not like 'Oil & Gas'
Order By Industry, TickerSymbol,TradeDate ;
47
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
48
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 > '2010-06-01' and TradeDate
49
List the price ID, opening price, and closing price of all stocks who have an opening price smaller than 120 dollars and a closing price greater than 120 dollars. List the opening and closing prices in ascending order.
Select PriceID, ST_Open, ST_Close
From StockData
Where ST_Open '120'
Order By ST_Open, ST_Close DESC
50
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
Where ST_high > '130'
UNion
Select PriceID, ST_High, ST_low from stockdata
Where ST_low
```
51
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 TradeDate between '2011-01-01' and '2011-12-31'
Order by TradeDate ASC ;
52
List the sum of all the low stock prices that are above 60 dollars. Call it TotalLowStockPriceOfStocksUnder60.
Select Sum (ST_Low) as TotalLowStockPriceOfStocksUnder60
From StockData
Where ST_Low > '60'
53
List the earliest trade date of the stock that ended up having a high over 100 dollars. Call it EarliestTradeDateWithHighOver100
Select MIN (tradeDate) as EarliestTradeDateWithHighOver100
From StockData
Where ST_High > '100'
54
List the most recent trade date of a stock that a low under 5 dollars. Call it MostRecentTradeDateWithLowUnder5
Select Max (TradeDate) as MostRecentTradeDateWithLowUnder5
From StockData
Where ST_Low
55
List the Price ID and TradeDate and stock high of all stocks that were traded before October 3rd, 2008 and had a stock high over 125 dollars. List the stock highs in descending order.
Select PriceId, TradeDate, ST_High
From StockData
Where TradeDate > '2008-03-10' and ST_High > '125'
Order By ST_High DESC
56
Return all attributes for stocks that have the MSFT ticker symbol and have a high stock price below 20 dollars or a low stock price above 50 dollars. List the high and low stock prices in ascending order
Select TickerSymbol, ST_High, ST_Low
From StockData
Where tickersymbol = 'MSFT' and ST_high '50'
Order By ST_high, ST_low ASC
57
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 ASC
58
List the Price ID's in ascending order and the open prices of all the stocks that have an open price between 20 and 21 dollars.
Select PriceId, ST_Open
From StockData
Where ST_Open between '20' and '21'
Order By PriceId Asc
59
List the closing prices in descending order of all the closing stock prices that were below 10 dollars and were traded before January 1st, 2007.
Select ST_Close, TradeDate
From StockData
Where ST_Close
60
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
61
List the number of stocks that have an opening price of 70 dollars and were traded in 2008. Call it NumberOfOpeningPricesOf70In2008.
```
Select count(ST_open) as NumberOfOpeningPricesof70in2008, ST_Open, TradeDate
From StockData
Where ST_Open = '70' and YEAR (TradeDate) = '2008'
```
62
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 '2010-08-1' and '2010-08-31'
63
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 NumberOFCom
FROM stockdata
Group BY Industry
HAVING COunt(Distinct Tickersymbol) > 3
```
64
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 avg(ST_Close) from stockdata
Group by tickersymbol ;
65
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 13th, 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 (PriceID, TickerSymbol, TradeDate, ST_Open, ST_High, ST_Low, ST_Close, Volume)
Values (123456,'MSFT', 2012-08-13, 28.76, 29.33, 28.72, 29, 23320365)
66
It's October 3rd, 2012 and you need to add information to the Stock Data 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 (PriceID, TradeDate, Industry, TickerSymbol)
Values (101928374, 2012-10-03, 'Tech', 'MSFT')
67
Remove all the rows of stocks that were traded before January 1st, 2001 for Microsoft.
DELETE FROM StockData
| WHERE TradeDate
68
Make a change for the Microsoft row for July 13th 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.50, ST_high=30.75, ST_low=26.75, ST_Close= 29.80, Volume= 3320365
WHERE TradeDate= '2012-07-13'and TickerSymbol= 'MSFT'
69
List every date that falls on a Friday from the Calendar table regardless of whether there are matching rows from the Stock Data table.
Select ActualDate
From Calendar$
where DayofWeek= 'Friday'
70
List the day of week and every date that falls on a weekend from the Calendar table and all rows from the Stock Data table.
Select ActualDate,DayofWeek,Daytype, TradeDate, PriceID, TickerSymbol, Industry, ST_Open, ST_close, ST_high, ST_low, Volume
From Calendar$, StockData
Where Daytype= 'Weekend'
71
Remove all the rows of stocks of Microsoft that were traded during the month of October in the year 2011.
Delete from StockData
| Where tickersymbol='MSFT' and tradedate between '2011-10-01' and '2011-10-31'
72
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 (ST_High - ST_Low) as Differences, TickerSymbol, Industry, TradeDate
From StockData
ORDER BY Differences DESC
73
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 TickerSymbol, AVG(Volume) AS AverageDailyVolume, DayofWeek
FROM StockData, Calendar$
GROUP BY TickerSymbol, DayofWeek
Order by TickerSymbol, AverageDailyVolume DESC
74
List the ten most common closing prices along with the number of times each occurs.
SELECT TOP 10 COUNT(ST_Close) as Occurences, PRICEID, ST_close
FROM StockData
GROUP BY ST_Close, PriceID
ORDER BY ST_CLose DESC
75
List all information from the rows sharing the single most common closing price.
SELECT TOP 1 Count (ST_Close) as Occurences, PRICEID, ST_close, ST_Open, ST_High, ST_Low, TickerSymbol, TradeDate, Industry
FROM StockData
GROUP BY ST_Close, PriceID, ST_close, ST_Open, ST_High, ST_Low, TickerSymbol, TradeDate, Industry
ORDER BY ST_CLose DESC
76
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.)
```
SELECT TOP 1000 [PriceID]
,[TickerSymbol]
,[Industry]
,[TradeDate]
,[ST_Open]
,[ST_High]
,[ST_Low]
,[ST_Close]
,[Volume]
FROM [Andrus].[dbo].[StockData]
```
77
Look for missing data by listing any rows in StockData that contain nulls.
SELECT *
FROM Stockdata
WHERE PriceID is null or 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
78
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_Low is null or ST_Close is null or Volume IS null)/Cast (count (*) as decimal (21,13))*100
From Stockdata ;
79
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'
80
Set any opening stock prices that are zero to null.
Update STockdata
Set ST_Open= Null
Where ST_Open =0;
81
List the largest single-day stock price increase for Ford (between the market opening and closing).
```
Select Max (ST_Open-ST_Close) as LargestSingleDayStockPriceIncreaseforFord
From StockData
Where TickerSymbol = 'F'
```
82
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 Min (ST_Close) as FordMinClosePriceSept1throughSept11
From StockData
Where TickerSymbol= 'F' and TradeDate between '2001-09-01' and '2001-09-11'
```
83
Now list the minimum closing price of Ford stock in September 2001 after September 11.
```
Select Min (ST_Close) as FordMinClosePriceSept11throughSep30
From StockData
Where TickerSymbol= 'F' and TradeDate between '2001-09-11' and '2001-09-30'
```
84
List the number of days in the table when a trade occurred (when the trade volume for any stock wasn’t zero).
Select count(TradeDate) as #ofDaysWhereTradeOccured
From StockData
Where Volume > '0'
85
List the number of trade days in each month of 2001. Sort the list from least to greatest number of trade days.
```
Select Count(Distinct tradedate), month(tradedate)
From stockdata
where year(tradedate)= 2001
Group By month(tradedate)
Order by 1
```
86
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 count (distinct tradedate) as NoOFTradeDAys, DATENAME (MM,TradeDAte) as TradeMonths, AVG (Volume) AVGVolPerMonth
From stockdata
Where YEAR(TradeDAte) = 2001
Group By DATENAME (MM,TradeDate)
ORder By NoOFTradeDAys
```
87
List the industries in alphabetical order and the number of companies in each industry in the table.
Select Industry, count(Distinct TickerSymbol) as NumberofCompanies
From StockData
Group By Industry
Order By Industry ASC
88
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 NumberofCompanies, count(distinct TradeDate) as UniqueDates, min(TradeDate) as MinTradeDate, max(TradeDate) as MaxTradeDate
From StockData
Group By Industry
Order By Industry ASC
89
List all of the ticker symbols containing the letter S.
Select TickerSymbol
From StockData
Where TickerSymbol ='$S$'
90
List the all of the ticker symbols containing the letter S and their average closing price in 2012.
Select AVG (ST_Close),TickerSymbol, TradeDate
From StockData
WHERE TickerSymbol= '$S$' and TradeDate between '2012-01-01' and '2012-12-31'
91
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
SELECT COUNT(A.TradeDate)
FROM
(SELECT DISTINCT TradeDate, TickerSymbol , ST_High, ST_Low
FROM StockData
WHERE (ST_High-ST_Low) > (ST_Low*.10) ) A
92
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) > (ST_Low*.10)
93
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(TradeDate) AS NumOf10PerProfPerComp
FROM StockData
WHERE (ST_High-ST_Low) > (ST_Low*.10)
GROUP BY TickerSymbol
ORDER BY NumOf10PerProfPerComp DESC;
```
94
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(TradeDate) AS NumOf10PerlossPerComp
FROM StockData
WHERE (ST_High-ST_Low)
95
List the five rows with the highest price multiplied by volume. Use the closing price of as the price for the entire day.
Select Top 5 (ST_Close*Volume) as ST_CloseMultipliedbyVolume
| From STockData
96
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 A.TickerSymbol, SUM((B.AvgPrice*A.Volume)) AS ReqCol
FROM StockData A, (select AVG(ST_close) AvgPrice ,TickerSymbol
FROM StockData
WHERE YEAR(TradeDate) = 2011
Group By TickerSymbol) B
where A.TickerSymbol = B.TickerSymbol
AND YEAR(TradeDate) = 2011
GROUP BY A.TickerSymbol
ORDER BY ReqCol DESC
```
97
List the ticker symbol, year, and the average price multiplied by volume for each year for Apple stock. Use the closing price of as the price for the entire day.
SELECT TickerSymbol, DATENAME(YY, 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) ASC;
98
List the Year, TickerSymbol, the previous year closing price, the current year closing price, and the annual rate of return of stocks traded since 2000. Sort the output in ascending by year.
select year(tradedate) as year, tickersymbol, Lag(ST_Close, 1) over(order by year(tradedate)) as ST_ClosePrevYear, ST_Close as ST_CloseCurrentYear,
(((ST_Close) - Lag(ST_Close, 1) over(order by year(tradedate)))/Lag(ST_Close, 1) over(order by year(tradedate)))*100 as [Rate of Return]
from StockData
where year(tradedate) >= '2000'
order by year asc
99
List the year, month, ticker symbol, previous month closing, current month closing, and monthly return percent. We are only interested in stocks traded since 2000.
select year(Tradedate) as Year, month(tradedate) as Month, tickersymbol, Lag(ST_Close, 1) over(order by Month(tradedate)) as ST_ClosePrevYear, ST_Close as ST_CloseCurrentYear,
(((ST_Close) - Lag(ST_Close, 1) over(order by Month(tradedate)))/Lag(ST_Close, 1) over(order by Month(tradedate)))*100 as [Rate of Return]
from StockData
where year(tradedate) >= '2000'
order by Year asc
100
List the year, week, ticker symbol, previous week closing, current week closing, weekly return percent and the closing price for the week. We are only interested in stocks traded since 2000.
select year(Tradedate) as Year, datepart(wk,tradedate) as week, tickersymbol, Lag(ST_Close, 1) over(order by datepart(wk,tradedate)) as ST_ClosePrevYear, ST_Close as ST_CloseCurrentYear,
(((ST_Close) - Lag(ST_Close, 1) over(order by datepart(wk,tradedate)))/Lag(ST_Close, 1) over(order by datepart(wk,tradedate)))*100 as [Rate of Return]
from StockData
where year(tradedate) >= '2000'
order by Year asc
101
Write a question and a query that uses derived tables approach.
--What is the closing price and rate of return for 07/26/2010 and 07/26/2011 for Apple and Ford?
select t1.ST_Close as [Closing Price 07/26/2010], t2.ST_Close as [Closing Price 07/26/2011], t1.TickerSymbol, ((t2.ST_Close-t1.ST_Close)/(t1.ST_Close))*100 as [Rate of Return]
from
(select * from StockData where TradeDate = '07/26/2010') t1 join
(select * from StockData where TradeDate = '07/26/2011') t2 on
t1.TickerSymbol = t2.tickersymbol
where t1.TickerSymbol = 'F' or t2.TickerSymbol = 'AAPL'
102
Write a question and a query that uses derived tables approach
--What is the closing price and rate of return for 07/26/2010 and 07/26/2011 for Apple and Ford?
With
t1 as
(select * from StockData where TradeDate = '07/26/2010'),
t2 as
(select * from StockData where TradeDate = '07/26/2011')
select t1.ST_Close as [Closing Price 07/26/2010], t2.ST_Close as [Closing Price 07/26/2011], t1.TickerSymbol, ((t2.ST_Close-t1.ST_Close)/(t1.ST_Close))*100 as [Rate of Return]
from t1, t2
where t1.TickerSymbol = t2.tickersymbol and (t1.TickerSymbol = 'F' or t1.TickerSymbol = 'AAPL')
103
Write the same query with the view and query approach.
| --What is the closing price and rate of return for 07/26/2010 and 07/26/2011 for Apple and Ford?
Create view t1 as
(select * from StockData where TradeDate = '07/26/2010')
create view t2 as
(select * from StockData where TradeDate = '07/26/2011')
select t1.ST_Close as [Closing Price 07/26/2010], t2.ST_Close as [Closing Price 07/26/2011], t1.TickerSymbol, ((t2.ST_Close-t1.ST_Close)/(t1.ST_Close))*100 as [Rate of Return]
from t1, t2
where t1.TickerSymbol = t2.tickersymbol and (t1.TickerSymbol = 'F' or t1.TickerSymbol = 'AAPL')
104
Write a question or a problem and solve it using a trigger.
| --How can we need to find out who is an idiot, who is average and who is a genius based on grades in each class.
```
Create trigger LevelOfIntelligence on Gradebook
For Update
as
update GradeBook
set IntelligenceLevel = 'Idiot'
Where grade = '90';
```
update GradeBook
set Grade = '1'
where name = 'Inchan' and Class = 'IntroSQL'
select *
from gradebook
105
Write a question and the stored procedure that solves the question.
--Did Ford trade more than 1000 stocks in 2011?
Create proc FordStocksVolume1000in2011 as
if
(Select sum(volume)
from StockData
Where TickerSymbol = 'F' and year(tradedate) = '2011') >1000
print 'There is more than 1,000 Ford stocks traded in 2011.'
Else Print 'There is not more than 1,000 Ford stocks traded in 2011.';
106
Write a question and the stored procedure that solves the question using two input parameters.
create proc TicknDate @tickersymbol varchar(10), @month int
as
Select*
from stockdata
where tickersymbol=@tickersymbol and month (tradedate)=@month
select *
from StockData
where TickerSymbol = 'AAPL' AND TradeDate = '09/15/1989'
107
Write a stored procedure that is a full INSERT statement.
create proc InsertStatement @ProductID int, @Product_Description varchar(50), @Product_Finish varchar(50), @Standard_Price int, @ProductLineID int, @QOH int, @Reorder_Point int, @Reorder varchar
as
begin
insert into Product_T
(Product_ID, Product_Description, Product_Finish, Standard_Price, Product_Line_ID, QOH, Reorder_Point, Reorder)
values
(@ProductID, @Product_Description,@Product_Finish, @Standard_Price, @ProductLineID, @QOH, @Reorder_Point, @Reorder)
end
exec InsertStatement 9,Chair, Cherry, 129, 17, 5, 4, Y
