Chapter 8 material Flashcards
define correlated subquery
executes once for each row in the outer query
define trigger
SQL code that is automatically invoked by the RDBMS upon occurrence of a data manipulation event
List the ticker symbol and the average closing for a stock that has the largest average closing price.
SELECT TOP 1 TickerSymbol, AVG (ST_Close) AS AverageClose
FROM StockData
GROUP BY TickerSymbol
ORDER BY AverageClose DESC;
or
SELECT TickerSymbol, AVG (ST_Close) AS AverageClose
FROM StockData
GROUP BY TickerSymbol
HAVING AVG(ST_Close) >= ANY (SELECT AVG (ST_Close)
FROM StockData
GROUP BY TickerSymbol)
List stocks that were trades both on 7/1/2013 and on 7/1/2014
SELECT DISTINCT TickerSymbol
FROM StockData
WHERE TickerSymbol in
(SELECT tickersymbol
FROM StockData
WHERE TradeDate = ‘2013-7-1’) and TickerSymbol in
(SELECT tickersymbol
FROM StockData
WHERE TradeDate = ‘2014-7-1’)
List stocks that were trades both on 7/1/2013 and on 7/1/2014 using JOIN
SELECT DISTINCT T1.TickerSymbol
FROM
(SELECT tickersymbol
FROM StockData
WHERE TradeDate = ‘2013-7-1’) as T1 JOIN
(SELECT tickersymbol
FROM StockData
WHERE TradeDate = ‘2014-7-1’) as T2 ON T1.tickersymbol = T2.tickersymbol;
List the ticker symbol and the percentage of each ticker symbol’s total volume to the total volume of all stocks.
SELECT tickersymbol, SUM(Volume)*100/ (SELECT SUM(Volume) from StockData)
FROM stockdata
GROUP BY TickerSymbol;
-List tickersymbol, trade date, and all trade volumes that are greater than the average trade volume of the particular firm
SELECT tickersymbol, tradedate, volume, (SELECT AVG(volume)
FROM StockData S2
WHERE S1.Tickersymbol = S2.TickerSymbol)
FROM stockdata S1
WHERE volume > (SELECT AVG(volume)
FROM StockData S2
WHERE S1.Tickersymbol = S2.TickerSymbol);
–List all information from the rows sharing the single least common closing price for the particular firm
SELECT *
FROM stockdata S1
WHERE st_Close = (SELECT TOP 1 s2.ST_Close
FROM StockData S2
WHERE S1.Tickersymbol = S2.TickerSymbol Group by st_close ORDER BY count(ST_close) DESC)
–List companies whose close stock price is greater than double the open price at least once (derived
SELECT tickersymbol
FROM StockData
WHERE St_Close > St_Open;
–List companies whose close stock price is greater than double the open price at least once using the EXISTS operator
SELECT tickersymbol
FROM StockData
WHERE EXISTS (SELECT tickersymbol, st_open
FROM StockData
WHERE St_Close > St_Open);
–List the TickerSymbol, Company name and Industry for all stocks were traded on Tuesday (Derived table)
SELECT s.TickerSymbol, c.CompanyName, s.Industry
FROM StockData s JOIN CompanyInformation c ON s.TickerSymbol=c.TickerSymbol JOIN Calendar d ON s.TradeDate = d.ActualDate
WHERE DayOfWeek = ‘Tuesday’;
–List the TickerSymbol, Company name and Industry for all stocks were traded on both a Tuesday and a Wednesday. (CTE common table expression)
SELECT StockTue.Tickersymbol, CompanyName, Industry
FROM
(SELECT s.TickerSymbol, DayOfWeek
FROM StockData s JOIN Calendar d ON s.TradeDate = d.ActualDate
WHERE DayOfWeek = ‘Tuesday’) AS StockTue
JOIN
(SELECT s.TickerSymbol, DayOfWeek
FROM StockData s JOIN Calendar d ON s.TradeDate = d.ActualDate
WHERE DayOfWeek = ‘Wednesday’) AS StockWed
ON StockTue.TickerSymbol=StockWed.TickerSymbol
JOIN
Companyinformation AS Cominfo
ON Stockwed.Tickersymbol = Cominfo.Tickersymbol;
–List the TickerSymbol, Company name and Industry for all stocks were traded on both a Tuesday and a Wednesday. (VIEW)
CREATE VIEW StockTue AS (
SELECT s.TickerSymbol, DayOfWeek
FROM StockData s JOIN Calendar d ON s.TradeDate = d.ActualDate
WHERE DayOfWeek = ‘Tuesday’);
CREATE VIEW StockWed AS
(SELECT s.TickerSymbol, DayOfWeek
FROM StockData s JOIN Calendar d ON s.TradeDate = d.ActualDate
WHERE DayOfWeek = ‘Wednesday’);
SELECT StockTue.Tickersymbol, CompanyName, Industry
FROM StockTue, StockWed, Companyinformation AS c
WHERE StockTue.TickerSymbol=StockWed.TickerSymbol AND Stockwed.Tickersymbol = C.Tickersymbol;
IF
(SELECT COUNT(PriceID)
FROM Stockdata
WHERE TickerSymbol = ‘aapl’) > 10000
PRINT ‘There are more than 10,000 Apple stocks traded.’
ELSE PRINT ‘There are 10,000 or less than 10,000 Apple stocks traded.’;
If the number of price id’s from the stockdata table with the apple ticker symbol are greater than 10000 then return the statement ‘there are more than 10,000 apple stocks traded’ if less than 10,000 then return ‘there are 10,000 or less than 10,000 apple stocks traded’
what does the pivot operator do?
change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.
