Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL Queries > SQL Queries > Flashcards

SQL Queries Flashcards

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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())

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

List the mean, variance, and standard deviation of volumes.

A

SELECT AVG(Volume), VAR(Volume), STDEV(Volume)

FROM stockdata;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

List the middle digits of PhoneNumber

A

SELECT SUBSTRING(PhoneNumber,5,3)

FROM CompanyInformation;

SELECT LEN(Phonenumber)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

List industry in upper case.

A

SELECT UPPER(Industry)

FROM stockdata;

SELECT LOWER(Industry)

FROM stockdata;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Remove leading blanks in Address

A

SELECT LTRIM(Address)

FROM CompanyInformation;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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'
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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'
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

List the industry and the number of different companies in each industry.

A

Select Industry, count(distinct tickersymbol)
from stockdata
group by industry

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

List the industry and the number of different companies in each industry. Put the answer in order of my stocks to least stocks.

A

select industry, count(distinct tickersymbol) as CompaniesPerIndustry
from stockdata
Group by Industry
Order by CompaniesPerIndustry desc

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

List the industry and the number of different companies in each industry for only those industries that have more than three companies

A

select industry, count(distinct tickersymbol) as CountofIndustryCompaniesMorethan3
from stockdata
where (select count(distinct tickersymbol) from stockdata)>3
Group by industry
ORder by CountofIndustryCompaniesMorethan3

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
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 Get2014StocksWithHighAbove100.

A 
create proc Get2014stockswithhighat100
as
select*
from stockdata
where st_high=100
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

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.

A 
create proc Numstockshighgreaterthan130
as
select count(distinct tickersymbol) as Numstockshighabove130
from stockdata
where st_high>130

exec Numstockshighgreaterthan130

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

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.

A 
Create proc GetHighBelow100LowOver90
as
Select PriceID, ST_high, ST_low
From stockdata
where st_high90

exec GetHighBelow100LowOver90

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

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.

A 
create proc GetCloseUnder100LowOver100
as
Select tradedate, st_close, st_low
from stockdata
where st_low=100

exec GetCloseUnder100LowOver100

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

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.

A

create proc Get2013StocksWithHighAbove100
as
Select*
from stockdata
where st_high=100 and year(tradedate)=2013

exec Get2013StocksWithHighAbove100

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
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'
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

List all columns and all rows from the StockData table.

A

Select*

From StockData

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

List the TickerSymbol, Industry, TradeDate and the Volume columns from the StockData table. List all rows.

A

Select tickersymbol, Industry, TradeDate, Volume

From StockData ;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

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.

A

Select tickersymbol, Industry, TradeDate, Volume
From StockData
Where Volume > 30000000 ;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

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.

A

Select tickersymbol, Industry, TradeDate, Volume
From StockData
Where Volume > 30000000
Order By TickerSymbol ;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

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.

A

Select tickersymbol, Industry, TradeDate, Volume
From StockData
Where Volume > 30000000
Order By TickerSymbol, TradeDate ;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

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.

A

Select tickersymbol, Industry, TradeDate, ST_Open, ST_Close
From StockData
Where TradeDate Between ‘2011-1-1’ and ‘2011-12-31’
Order By TradeDate

44
Q

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.

A 
select tickersymbol, industry, tradedate, st_open, st_close
from stockdata
where year(tradedate)=2013
order by industry, tickersymbol,tradedate
45
Q

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.

A

Select tickersymbol, Industry, TradeDate, ST_Open, ST_Close
From StockData
Where Industry = ‘Oil & Gas’
Order By Industry, TickerSymbol,TradeDate ;

46
Q

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.

A

Select tickersymbol, Industry, TradeDate, ST_Open, ST_Close
From StockData
Where Industry not like ‘Oil & Gas’
Order By Industry, TickerSymbol,TradeDate ;

47
Q

List the Price ID of all stocks that have a volume of 0. List the Price IDs in descending order.

A

Select PriceID
From StockData
Where Volume = ‘0’
Order By PriceID DESC

48
Q

Return all attributes for stocks that have been traded in the month of June in 2010. List the trade date in descending order.

A

Select*
From StockData
Where TradeDate > ‘2010-06-01’ and TradeDate

49
Q

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.

A

Select PriceID, ST_Open, ST_Close
From StockData
Where ST_Open ‘120’
Order By ST_Open, ST_Close DESC

50
Q

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.

A 
Select PriceID, ST_High, ST_Low	
	Where ST_high > '130'	
	UNion
Select PriceID, ST_High, ST_low from stockdata
	Where ST_low
51
Q

List the trade dates and low stock prices of all stocks that were traded in 2011. List the trade dates in ascending order.

A

Select TradeDate, ST_Low
From StockData
Where TradeDate between ‘2011-01-01’ and ‘2011-12-31’
Order by TradeDate ASC ;

52
Q

List the sum of all the low stock prices that are above 60 dollars. Call it TotalLowStockPriceOfStocksUnder60.

A

Select Sum (ST_Low) as TotalLowStockPriceOfStocksUnder60
From StockData
Where ST_Low > ‘60’

53
Q

List the earliest trade date of the stock that ended up having a high over 100 dollars. Call it EarliestTradeDateWithHighOver100

A

Select MIN (tradeDate) as EarliestTradeDateWithHighOver100
From StockData
Where ST_High > ‘100’

54
Q

List the most recent trade date of a stock that a low under 5 dollars. Call it MostRecentTradeDateWithLowUnder5

A

Select Max (TradeDate) as MostRecentTradeDateWithLowUnder5
From StockData
Where ST_Low

55
Q

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.

A

Select PriceId, TradeDate, ST_High
From StockData
Where TradeDate > ‘2008-03-10’ and ST_High > ‘125’
Order By ST_High DESC

56
Q

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

A

Select TickerSymbol, ST_High, ST_Low
From StockData
Where tickersymbol = ‘MSFT’ and ST_high ‘50’
Order By ST_high, ST_low ASC

57
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

58
Q

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.

A

Select PriceId, ST_Open
From StockData
Where ST_Open between ‘20’ and ‘21’
Order By PriceId Asc

59
Q

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.

A

Select ST_Close, TradeDate
From StockData
Where ST_Close

60
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

61
Q

List the number of stocks that have an opening price of 70 dollars and were traded in 2008. Call it NumberOfOpeningPricesOf70In2008.

A 
Select count(ST_open) as NumberOfOpeningPricesof70in2008, ST_Open, TradeDate
	From StockData
		Where ST_Open = '70' and YEAR (TradeDate) = '2008'
62
Q

List the number of trade dates of stocks that were traded in the month of August in 2010.

A

Select Count (TradeDate)
From StockData
Where TradeDate between ‘2010-08-1’ and ‘2010-08-31’

63
Q

List the industry and the number of different companies in each industry for only those industries that have more than three companies.

A 
SELECT Industry, Count(Distinct Tickersymbol) AS NumberOFCom
	FROM stockdata
		Group BY Industry
			HAVING COunt(Distinct Tickersymbol) > 3
64
Q

List the ticker symbol and the average closing for a stock that has the largest average closing price.

A

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
Q

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.

A

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
Q

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.

A

Insert into StockData (PriceID, TradeDate, Industry, TickerSymbol)
Values (101928374, 2012-10-03, ‘Tech’, ‘MSFT’)

67
Q

Remove all the rows of stocks that were traded before January 1st, 2001 for Microsoft.

A

DELETE FROM StockData

WHERE TradeDate

68
Q

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.

A

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
Q

List every date that falls on a Friday from the Calendar table regardless of whether there are matching rows from the Stock Data table.

A

Select ActualDate
From Calendar$
where DayofWeek= ‘Friday’

70
Q

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.

A

Select ActualDate,DayofWeek,Daytype, TradeDate, PriceID, TickerSymbol, Industry, ST_Open, ST_close, ST_high, ST_low, Volume
From Calendar$, StockData
Where Daytype= ‘Weekend’

71
Q

Remove all the rows of stocks of Microsoft that were traded during the month of October in the year 2011.

A

Delete from StockData

Where tickersymbol=’MSFT’ and tradedate between ‘2011-10-01’ and ‘2011-10-31’

72
Q

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

A

SELECT top 10 (ST_High - ST_Low) as Differences, TickerSymbol, Industry, TradeDate
From StockData
ORDER BY Differences DESC

73
Q

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.

A

SELECT TickerSymbol, AVG(Volume) AS AverageDailyVolume, DayofWeek
FROM StockData, Calendar$
GROUP BY TickerSymbol, DayofWeek
Order by TickerSymbol, AverageDailyVolume DESC

74
Q

List the ten most common closing prices along with the number of times each occurs.

A

SELECT TOP 10 COUNT(ST_Close) as Occurences, PRICEID, ST_close
FROM StockData
GROUP BY ST_Close, PriceID
ORDER BY ST_CLose DESC

75
Q

List all information from the rows sharing the single most common closing price.

A

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
Q

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.)

A 
SELECT TOP 1000 [PriceID]
      ,[TickerSymbol]
      ,[Industry]
      ,[TradeDate]
      ,[ST_Open]
      ,[ST_High]
      ,[ST_Low]
      ,[ST_Close]
      ,[Volume]
  FROM [Andrus].[dbo].[StockData]
77
Q

Look for missing data by listing any rows in StockData that contain nulls.

A

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
Q

List the percentage of records containing nulls.

A

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
Q

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.

A

Delete from StockData

Where ST_open = ‘0’

80
Q

Set any opening stock prices that are zero to null.

A

Update STockdata
Set ST_Open= Null
Where ST_Open =0;

81
Q

List the largest single-day stock price increase for Ford (between the market opening and closing).

A 
Select Max (ST_Open-ST_Close) as LargestSingleDayStockPriceIncreaseforFord
	From StockData
		Where TickerSymbol = 'F'
82
Q

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.

A 
Select Min (ST_Close) as FordMinClosePriceSept1throughSept11
	From StockData
		Where TickerSymbol= 'F' and TradeDate between '2001-09-01' and '2001-09-11'
83
Q

Now list the minimum closing price of Ford stock in September 2001 after September 11.

A 
Select Min (ST_Close) as FordMinClosePriceSept11throughSep30
	From StockData
		Where TickerSymbol= 'F' and TradeDate between '2001-09-11' and '2001-09-30'
84
Q

List the number of days in the table when a trade occurred (when the trade volume for any stock wasn’t zero).

A

Select count(TradeDate) as #ofDaysWhereTradeOccured
From StockData
Where Volume > ‘0’

85
Q

List the number of trade days in each month of 2001. Sort the list from least to greatest number of trade days.

A 
Select Count(Distinct tradedate), month(tradedate)
	From stockdata
		where year(tradedate)= 2001
			Group By month(tradedate)
				Order by 1
86
Q

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.

A 
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
Q

List the industries in alphabetical order and the number of companies in each industry in the table.

A

Select Industry, count(Distinct TickerSymbol) as NumberofCompanies
From StockData
Group By Industry
Order By Industry ASC

88
Q

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.

A

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
Q

List all of the ticker symbols containing the letter S.

A

Select TickerSymbol
From StockData
Where TickerSymbol =’$S$’

90
Q

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

A

Select AVG (ST_Close),TickerSymbol, TradeDate
From StockData
WHERE TickerSymbol= ‘$S$’ and TradeDate between ‘2012-01-01’ and ‘2012-12-31’

91
Q

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

A

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
Q

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.

A

SELECT *
FROM StockData
WHERE (ST_High-ST_Low) > (ST_Low*.10)

93
Q

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.

A 
SELECT TickerSymbol, Count(TradeDate) AS NumOf10PerProfPerComp
FROM StockData 
WHERE (ST_High-ST_Low) > (ST_Low*.10)
GROUP BY TickerSymbol
ORDER BY NumOf10PerProfPerComp DESC;
94
Q

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.

A

SELECT TickerSymbol, Count(TradeDate) AS NumOf10PerlossPerComp
FROM StockData
WHERE (ST_High-ST_Low)

95
Q

List the five rows with the highest price multiplied by volume. Use the closing price of as the price for the entire day.

A

Select Top 5 (ST_Close*Volume) as ST_CloseMultipliedbyVolume

From STockData

96
Q

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.

A 
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
Q

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.

A

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
Q

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.

A

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
Q

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.

A

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
Q

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.

A

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
Q

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?

A

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
Q

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?

A

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
Q

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?

A

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
Q

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.

A 
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
Q

Write a question and the stored procedure that solves the question.
–Did Ford trade more than 1000 stocks in 2011?

A

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
Q

Write a question and the stored procedure that solves the question using two input parameters.

A

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
Q

Write a stored procedure that is a full INSERT statement.

A

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

SQL Queries (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions