Need help editing SQL queries. I just need the queries to run in SQLDeveloper wi

Question

Need help editing SQL queries. I just need the queries to run in SQLDeveloper without error messages. Without the ERD I'm not sure how helpful the questions listed below the queries will be.

--1.
SELECT
t.trade_id,
t.stock_id,
SUM(t.price_total*con.exchange_rate)
FROM trade t
JOIN stock_exchange se
ON t.stock_ex_id = se.stock_ex_id
JOIN currency cu
ON se.currency_id = cu.currency_id
JOIN conversion con
ON cu.currency_id = con.from_currency_id
WHERE t.stock_ex_id IS NOT NULL
;

--2.
SELECT
MAX(COUNT(t.shares)) AS "Max Count of Company Stock",
c.name AS "Company Name"
FROM trade t
INNER JOIN company c
ON t.stock_id = c.stock_id
GROUP BY c.name;

--3.
WITH HighestTradeVolume
(SELECT
COUNT(t.shares) AS "Total Trades"
sl.stock_symbol AS "Stock SYmbol",
se.symbol AS "Stock Exchange Symbol",
se.name AS "Stock Exchange"
FROM stock_exchange se
JOIN trade t
ON se.stock_ex_id = t.stock_ex_id
JOIN stock_listing sl
ON t.stock_id = sl.stock_id AND sl.stock_ex_id = t.stock_ex_id
GROUP by se.name, se.symbol, sl.stock_symbol
ORDER BY se.name, se.symbol, sl.stock_symbol)
HAVING SUM(t.shares) = (SELECT MAX(SUM(t.shares))
FROM trade t);

--4.
SELECT
sp.price AS "Current Price",
c.stock_id AS "Stock ID",
c.name AS "Company Name",
t.shares AS "Trade Volume",
MAX(t.transaction_time) AS "Recent Trade",
SUM(t.transaction_time/t1.transaction_time) AS "% Change in Price"
FROM stock_price sp
JOIN company c
ON sp.stock_id = c.stock_id
INNER JOIN trade t
ON c.stock_id = t.stock_id
WHERE stock_ex_id = 3
AND t1.transaction_time < t.transaction_time
ORDER BY t.shares, sp.price, c.stock_id, c.name desc;
  

1. Display the trade id, the stock id and the total price (in US dollars) for the secondary market trade with the highest total price. Convert all prices to US dollars.

2. Display the name of the company and trade volume for the company whose stock has the largest total volume of shareholder trades worldwide. [Example calculation: A company declares 20000 shares, and issues 10000 on the new issue market (primary market), and 1000 shares is sold to a stockholder on the secondary market. Later that stockholder sells 500 shares to another stockholder (or back to the company itself). The number of shareholder trades is 2 and the total volume of shareholder trades is 1500.]

3. For each stock exchange, display the symbol of the stock with the highest total trade volume. Show the stock exchange name, stock symbol and total trade volume. Sort the output by the name of the stock exchange name and stock symbol.

4. List all companies on the New York Stock Exchange. Display the company name, shareholder trade volume, the current price and the percentage change for the last price change, and sort the output in descending order of shareholder trade volume.

Explanation / Answer

--1.
SELECT
t.trade_id,
t.stock_id,
SUM(t.price_total*con.exchange_rate),
MAX(t.price_total*con.exchange_rate) OVER (PARTITION BY t.trade_id,
t.stock_id) -- To get the highest total price within trade and stock.
FROM trade t
JOIN stock_exchange se
ON t.stock_ex_id = se.stock_ex_id
JOIN currency cu
ON se.currency_id = cu.currency_id
JOIN conversion con
ON cu.currency_id = con.from_currency_id
WHERE t.stock_ex_id IS NOT NULL
AND t.trade_type='Secondary market' --- Filter for secondary market.
;

--2.
SELECT
MAX(COUNT(t.shares)) AS "Max Count of Company Stock",
c.name AS "Company Name"
FROM trade t
INNER JOIN company c
ON t.stock_id = c.stock_id
GROUP BY c.name;

--3.
SELECT DISTINCT
sl.stock_symbol AS "Stock SYmbol",
se.symbol AS "Stock Exchange Symbol",
se.name AS "Stock Exchange"
COUNT(t.shares) OVER (PARTITION BY sl.stock_symbol,se.symbol,se.name) AS "Total Trades"
MAX(t.trade_amt) OVER (PARTITION BY sl.stock_symbol,se.symbol,se.name) AS "Total Trade"
FROM stock_exchange se
JOIN trade t
ON se.stock_ex_id = t.stock_ex_id
JOIN stock_listing sl
ON t.stock_id = sl.stock_id AND sl.stock_ex_id = t.stock_ex_id
ORDER BY se.name, se.symbol, sl.stock_symbol;

--4.
SELECT
sp.price AS "Current Price",
c.stock_id AS "Stock ID",
c.name AS "Company Name",
t.shares AS "Trade Volume",
(sp.prev_price-sp.price)/sp.prev_price*100 AS "% Change in Price"
FROM stock_price sp
JOIN company c
ON sp.stock_id = c.stock_id
INNER JOIN trade t
ON c.stock_id = t.stock_id
WHERE stock_ex_id = 3
AND t1.transaction_time < t.transaction_time
ORDER BY t.shares, sp.price, c.stock_id, c.name desc;

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.