--1. SELECT c.name AS \"Company Name\", csst.current_authorized AS \"Shares Auth

Question

--1.

SELECT
c.name AS "Company Name",
csst.current_authorized AS "Shares Authorized",
csst.total_outstanding AS "total outstanding",
ROUND((csst.total_outstanding/csst.current_authorized*100), 2) AS "% of Auth. Shares Outs."
FROM company c
JOIN Current_stock_stats
ON c.stock_id = csst.stock_id
WHERE csst.current_authorized IS NOT NULL;

I can't get the above query to run and was wondering if there are any mistakes with the syntax that need to be fixed

--2.

SELECT
t.trade_id AS "Trade ID",
c.name AS "Company",
t.shares AS "Max Shares"
FROM trade t
JOIN company c
ON c.stock_id = t.stock_id
WHERE t.shares =
(SELECT MAX(shares)
FROM trade t
WHERE t.stock_ex_id IS NOT NULL)
;

I was told that the inner query restricts to the secondary market (which is what I want), but the outer query doesn't. How can that be fixed?

Explanation / Answer

In first query, You missed that aliasing of the table Current_stock_stats..

SELECT
c.name AS "Company Name",
csst.current_authorized AS "Shares Authorized",
csst.total_outstanding AS "total outstanding",
ROUND((csst.total_outstanding/csst.current_authorized*100), 2) AS "% of Auth. Shares Outs."
FROM company c
JOIN Current_stock_stats csst
ON c.stock_id = csst.stock_id
WHERE csst.current_authorized IS NOT NULL;

Coming to second query..

WHERE = (subquery) should not be used. It should be WHERE IN ( sub query) as the subquery returns more than one row.So the query should be

SELECT
t.trade_id AS "Trade ID",
c.name AS "Company",
t.shares AS "Max Shares"
FROM trade t
JOIN company c
ON c.stock_id = t.stock_id
WHERE t.shares IN
(SELECT MAX(shares)
FROM trade t
WHERE t.stock_ex_id IS NOT NULL);

For further clarifiacation see

http://www.tutorialspoint.com/sql/sql-sub-queries.htm

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

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