// EcoFinLearn quiz questions: SQL Foundations
// Source: https://ecofinlearning.com/learn/sql-basics
// (c) Sitraka Forler. Teachers may use these with a class, with a link back: https://ecofinlearning.com/terms#reuse
// Import in Moodle: Question bank > Import > GIFT format.

$CATEGORY: $course$/EcoFinLearn/SQL Foundations/Getting Started

// sb-m0-l1: Your First Query
::sb-m0-l1 Q1::[plain]In the query SELECT * FROM equity_prices LIMIT 5;, what is the job of the word equity_prices?{
	=It names the table the query reads rows from#The FROM clause names the source table. Change it to macro_indicators and you get inflation data instead of prices - same query shape, completely different result.
	~It is the name of one column to display#Columns are chosen by SELECT (here * means all columns). equity_prices comes after FROM, so it is the table, not a column.
	~It limits the result to 5 rows#That is the job of LIMIT 5. equity_prices is the table being read.
	~It is a comment describing the query#Comments in SQL start with -- and are ignored by the engine. equity_prices is a real table name the query depends on.
}

// sb-m0-l1: Your First Query
::sb-m0-l1 Q2::[plain]You run SELECT * FROM equity_prices; with no LIMIT on a table of two million rows. What is the practical risk?{
	=It tries to return all two million rows, which can be slow and hog shared database resources#Without LIMIT the engine returns every matching row. On a large shared database that can be slow and disruptive - which is exactly why analysts add LIMIT when they just want a preview.
	~It raises a syntax error because LIMIT is mandatory#LIMIT is optional. The query is valid SQL - it just returns everything, which is the problem.
	~It returns only the first row automatically#Nothing caps the output unless you add LIMIT. It returns all rows, not one.
	~It returns zero rows for safety#SQL does not silently withhold data. It returns the full result set - potentially millions of rows.
}

// sb-m0-l2: Counting Rows with COUNT(*) and WHERE
::sb-m0-l2 Q1::[plain]You run SELECT COUNT(*) FROM equity_prices; (with no WHERE) and get a number in the thousands. You expected a few hundred for AAPL. What went wrong?{
	=Nothing is broken - with no WHERE, COUNT(*) tallies every row across all five tickers, so a thousands-scale total is exactly right. You just have not filtered to AAPL yet.#COUNT(*) with no filter counts the whole table. The table holds five tickers of similar length, so the grand total is naturally several times the size of any single ticker. Add WHERE ticker \= 'AAPL' to shrink it to the AAPL-only count.
	~The table is corrupted - AAPL should never be more than a few hundred rows.#The table is fine. The big number is the sum across all tickers, not AAPL alone. Nothing is corrupted; the query simply was not filtered.
	~COUNT(*) double-counts each row, so you should use COUNT(1) instead.#COUNT(*) does not double-count, and COUNT(1) returns the identical result. The large number comes from counting all five tickers, not from any counting bug.
	~You need quotes around the table name\: FROM 'equity_prices'.#Table names are identifiers and must NOT take single quotes. Single quotes are for text data. The query ran fine; it simply counted everything because there was no WHERE.
}

// sb-m0-l2: Counting Rows with COUNT(*) and WHERE
::sb-m0-l2 Q2::[plain]In SQL, what is the difference between ticker \= 'AAPL' and ticker \= "AAPL"?{
	=Single quotes make 'AAPL' a text value to compare against; double quotes make "AAPL" an identifier (a column name), so the second form usually errors with "no such column".#The SQL standard reserves single quotes for string literals and double quotes for identifiers like column and table names. ticker \= "AAPL" asks the engine to compare the ticker column to a column called AAPL, which does not exist - hence the error.
	~They are identical - SQL treats single and double quotes the same way.#They are not interchangeable in standard SQL. Single quotes \= text data; double quotes \= identifiers. Some databases are lenient, but relying on that is a habit that will bite you.
	~Double quotes are the correct, modern way to write text; single quotes are deprecated.#Backwards. Single quotes are the standard way to write text literals in SQL and are not deprecated. Double quotes are for identifiers.
	~Single quotes are for numbers and double quotes are for text.#Numbers take no quotes at all (close_price > 170). Text takes single quotes. Double quotes denote identifiers, not text.
}

$CATEGORY: $course$/EcoFinLearn/SQL Foundations/SELECT & Filtering

// sb-m1-l1: SELECT, WHERE and ORDER BY Basics
::sb-m1-l1 Q1::[plain]You run  SELECT trade_date, close_price FROM equity_prices ORDER BY trade_date LIMIT 5;  with NO where clause. What comes back?{
	=The 5 OLDEST rows across ALL tickers, mixed together (AAPL, MSFT, JPM, GS, SPY) because ORDER BY defaults to ascending and there is no ticker filter#No WHERE means every ticker is included; ORDER BY without DESC sorts oldest-first. So you get the earliest dates in the table, one row per ticker per day, all five symbols interleaved - not what a trader checking today's AAPL price wanted.
	~The 5 most recent AAPL rows#Two mistakes\: without WHERE ticker \= 'AAPL' you get all tickers, and without DESC you get the OLDEST rows, not the most recent.
	~An error, because ORDER BY requires a WHERE clause#ORDER BY and WHERE are independent - a query is perfectly valid with either, both, or neither. This query runs fine; it just returns the wrong rows for the task.
	~The 5 rows with the highest close_price#You are sorting by trade_date, not close_price, so price has no effect on the ordering. To sort by price you would write ORDER BY close_price DESC.
}

// sb-m1-l1: SELECT, WHERE and ORDER BY Basics
::sb-m1-l1 Q2::[plain]A colleague's query returns zero rows and no error\:  SELECT * FROM equity_prices WHERE ticker \= AAPL;  (note\: no quotes around AAPL). What is wrong?{
	=AAPL without quotes is read as a COLUMN name, not the text value 'AAPL' - it needs single quotes\: WHERE ticker \= 'AAPL'#Unquoted, SQL treats AAPL as an identifier (a column). Depending on the engine this errors ("no such column") or matches nothing. The fix is single quotes to mark it as a string literal\: WHERE ticker \= 'AAPL'.
	~The table has no AAPL data, so zero rows is correct#equity_prices contains AAPL, MSFT, JPM, GS and SPY. The empty result is caused by the missing quotes, not missing data.
	~WHERE must come after ORDER BY, so the clause order is invalid#WHERE always comes before ORDER BY; this query's clause order is fine. The bug is purely the unquoted string.
	~ticker should be compared with \=\= , not \= , in SQL#SQL uses a single \= for equality comparison (unlike Python or JavaScript, which use \=\=). The single \= is correct here - the real problem is the missing quotes.
}

$CATEGORY: $course$/EcoFinLearn/SQL Foundations/Aggregation

// sb-m2-l1: GROUP BY: Portfolio Summary by Ticker
::sb-m2-l1 Q1::[plain]You run the starter here - SELECT ticker, side, COUNT(*) ... FROM trade_ledger GROUP BY side - and get only 2 rows back, each showing a single ticker. Why did four of the five tickers disappear?{
	=Grouping by side alone makes just two buckets (BUY, SELL); ticker is not in GROUP BY, so SQLite shows one arbitrary ticker per bucket and merges all the rest#The GROUP BY key defines the buckets. With only side as the key there are exactly two buckets, so all five tickers pile into them and the engine keeps just one arbitrary ticker label per row. Adding ticker to the GROUP BY splits them back apart into 10 rows.
	~trade_ledger only contains data for two tickers#trade_ledger has all five tickers (AAPL, MSFT, JPM, GS, SPY) assigned round-robin. The missing tickers are a grouping artefact, not missing data - the corrected query returns all five.
	~ORDER BY total_notional DESC dropped the smaller tickers#ORDER BY only sorts rows, it never removes them. There is no LIMIT here either. The rows collapsed at the GROUP BY stage, before ORDER BY ran.
	~COUNT(*) can only return two rows at a time#COUNT(*) returns one value per group and has no row cap. The number of result rows equals the number of distinct groups - which is set entirely by the GROUP BY columns.
}

// sb-m2-l1: GROUP BY: Portfolio Summary by Ticker
::sb-m2-l1 Q2::[plain]You write\: SELECT ticker, AVG(close_price), trade_date FROM equity_prices GROUP BY ticker. What happens?{
	=Error - trade_date is not in GROUP BY and not wrapped in an aggregate#Every column in SELECT must either be in GROUP BY or wrapped in an aggregate function (AVG, MAX, COUNT, etc.). trade_date here is neither.
	~The query runs and returns one row per ticker with the latest date#Standard SQL engines will raise an error. MySQL in non-strict mode might return an arbitrary trade_date - which is wrong and dangerous.
	~The query returns the average date for each ticker#AVG() on a date column is usually a type error. And trade_date is not wrapped in any function here.
	~The query runs and returns all dates for each ticker#GROUP BY collapses all rows for each ticker into one. You cannot get multiple rows per ticker while aggregating with GROUP BY.
}

// sb-m2-l1: GROUP BY: Portfolio Summary by Ticker
::sb-m2-l1 Q3::[plain]What is the difference between WHERE and HAVING?{
	=WHERE filters rows before aggregation; HAVING filters groups after aggregation#WHERE runs before GROUP BY and cannot reference aggregate functions. HAVING runs after GROUP BY and can filter based on AVG(), COUNT() etc. Example\: HAVING AVG(close) > 100.
	~They are interchangeable - WHERE and HAVING do the same thing#They are not interchangeable. WHERE AVG(close) > 100 will fail because aggregates are not yet computed at the WHERE stage.
	~HAVING is faster than WHERE because it processes fewer rows#Actually WHERE is typically faster because it reduces the number of rows before the GROUP BY computation. HAVING filters after aggregation.
	~WHERE works on columns; HAVING works on rows#Both work on rows, but at different stages. WHERE filters individual rows; HAVING filters aggregated groups.
}

$CATEGORY: $course$/EcoFinLearn/SQL Foundations/JOINs

// sb-m3-l1: Joining Prices to Trades
::sb-m3-l1 Q1::[plain]You join trade_ledger to equity_prices on ticker ONLY, forgetting the date condition. The query runs without error but the row count balloons from 200 trades to over a hundred thousand. What happened?{
	=Each trade matched every price row for that ticker (\~520 trading days), a many-to-many "fan-out"#With only the ticker equated, one AAPL trade pairs with all \~520 AAPL price rows. Multiply across all trades and you get a combinatorial blow-up - the classic incomplete-ON-clause bug.
	~INNER JOIN duplicates every row automatically as a safety mechanism#JOINs never duplicate rows for safety. The extra rows are real matches produced by an under-specified ON clause - there is nothing automatic about it.
	~The database silently switched to a CROSS JOIN because one condition is invalid#A single ticker equality is perfectly valid SQL; nothing switches to CROSS JOIN. The many rows are legitimate ticker matches across every date.
	~equity_prices secretly contains 100,000 rows per ticker#Each ticker has only \~520 daily rows. The six-figure result comes from pairing 200 trades against those \~520 rows each, not from a huge price table.
}

// sb-m3-l1: Joining Prices to Trades
::sb-m3-l1 Q2::[plain]The starter compares t.trade_date to the constant string 'PUT_THE_JOIN_KEY_HERE'. Why does the INNER JOIN return ZERO rows instead of throwing an error?{
	=The comparison is valid SQL but no real trade_date equals that literal, so every match fails and INNER JOIN keeps only matched rows - none#A column \= string comparison is legal; it simply evaluates to false for every row. INNER JOIN discards non-matches, and since nothing matches, the result is empty - no syntax error, just no rows.
	~SQL treats unknown strings as NULL, which crashes the join#An unknown string is just a text literal, not NULL, and it does not crash anything - the comparison is simply always false.
	~The query errors out; the 0-row output is a bug in the sandbox#There is no error. Comparing a date column to a text constant is allowed and yields false, producing an empty (but valid) result set.
	~A LEFT JOIN would have returned rows, but INNER JOIN forbids constants in ON#INNER JOIN allows constants in ON just fine. A LEFT JOIN would indeed keep the left rows (with NULL prices), but that is because LEFT keeps unmatched trades, not because of any rule against constants.
}

// sb-m3-l1: Joining Prices to Trades
::sb-m3-l1 Q3::[plain]A trade was booked on a Saturday (no market close exists for that date). With an INNER JOIN on ticker AND trade_date, what happens to that trade in the slippage report?{
	=It is dropped - no matching price row exists, and INNER JOIN keeps only matched trades#INNER JOIN returns a row only when a match is found on both keys. A Saturday has no equity_prices row, so the trade finds no partner and is excluded - which is correct, since it has no close to measure slippage against.
	~It appears with market_close \= 0 and a huge slippage#INNER JOIN never fabricates a zero close; unmatched rows are dropped entirely, not filled with 0. (A LEFT JOIN would show NULL, not 0.)
	~It matches Friday’s close automatically#A join matches on exact equality of the keys, not "nearest previous date". Getting Friday’s close would require extra logic (e.g. a correlated subquery or ASOF join), not a plain equality join.
	~The whole query errors because one trade has no price#One unmatched trade never errors a join; it is simply omitted. INNER JOIN silently returns only the trades that found a price.
}

$CATEGORY: $course$/EcoFinLearn/SQL Foundations/Common Table Expressions

// sb-m4-l1: CTEs · Structured SQL
::sb-m4-l1 Q1::[plain]You run the starter and get a single row with trading_days \= 2595 (about 5 × 520). What went wrong?{
	=AVG() was applied without GROUP BY ticker, so all five tickers were folded into one bucket and averaged together#With no GROUP BY, the aggregate treats every row from every ticker as one giant group - hence one row and a COUNT(*) near 5 × 520. Adding GROUP BY ticker splits it back into five per-ticker averages.
	~The LAG() window function counted each row five times#LAG just fetches the previous close per ticker; it does not multiply rows. The inflated count comes from the missing GROUP BY collapsing all tickers into a single aggregate bucket.
	~The LIMIT 3 was ignored by the engine#LIMIT 3 was applied - but it can only trim rows that exist. With only 1 row produced upstream, LIMIT 3 has nothing to cut. The bug is upstream, in the missing GROUP BY.
	~trade_date was not sorted, so rows were double-counted#Sort order does not change how many rows an aggregate returns. The count blew up because every ticker landed in one bucket - a GROUP BY problem, not an ordering problem.
}

// sb-m4-l1: CTEs · Structured SQL
::sb-m4-l1 Q2::[plain]In the chain WITH a AS (...), b AS (...) SELECT * FROM b, which statement about the CTEs is true?{
	=b can reference a (SELECT ... FROM a), because a later CTE can read from an earlier one#CTEs are visible downstream\: b may select from a, exactly like a relay leg receiving the baton. The final query can read from either, but a cannot read from b.
	~a and b are fully independent and cannot reference each other#They are not independent - that is the whole point of chaining. b can build on a; only the reverse (a reading b) is forbidden.
	~You must write WITH before both a and b\: WITH a AS (...) WITH b AS (...)#One WITH covers the whole chain; CTEs are separated by commas, not repeated WITH keywords. WITH a AS (...), b AS (...) is the correct form.
	~a can reference b, since SQL resolves all CTEs simultaneously#Non-recursive CTEs flow one direction, top to bottom\: an earlier CTE cannot see a later one. Only b (later) can read a (earlier).
}

// sb-m4-l1: CTEs · Structured SQL
::sb-m4-l1 Q3::[plain]Why does the final query multiply the mean daily log return by 252 rather than 365?{
	=There are about 252 trading days in a US year - markets are closed on weekends and holidays, so only trading days compound#Annualising scales a daily figure up by the number of periods per year. Since returns only accrue on the \~252 days the market is open, 252 - not 365 calendar days - is the correct multiplier.
	~252 is the number of stocks in the S&P index used for benchmarking#The S&P 500 has about 500 constituents; 252 has nothing to do with index membership. It is the count of annual trading days.
	~365 would work equally well; 252 is just a rounding convention#They are not interchangeable - using 365 would overstate annualised return by counting \~113 non-trading days on which nothing compounded.
	~It converts the log return into a simple percentage return#The × 100 converts to a percentage; the × 252 annualises. Multiplying by 252 does not change log returns into simple returns.
}

$CATEGORY: $course$/EcoFinLearn/SQL Foundations/Conditional Logic

// sb-m5-l1: CASE WHEN: Conditional Logic in SQL
::sb-m5-l1 Q1::[plain]A trade has notional \= 60,000. Your CASE is written\: WHEN notional > 20000 THEN 'MEDIUM' WHEN notional > 50000 THEN 'LARGE' ELSE 'SMALL' END. What label does this trade get?{
	=MEDIUM - CASE stops at the first true branch, and 60,000 > 20000 is checked first#Exactly the ordering trap. CASE evaluates top to bottom and takes the first match. 60,000 satisfies > 20000, so it returns MEDIUM and never even tests the > 50000 branch. Put the strictest threshold first to avoid this.
	~LARGE - 60,000 is over 50,000, so the LARGE branch wins#It would be LARGE only if the > 50000 branch came first. As written, the > 20000 branch is tested first and matches, so CASE returns MEDIUM and stops.
	~Both MEDIUM and LARGE - the trade appears in two buckets#A CASE returns exactly one value per row. It cannot assign two labels; it takes only the first branch that is true.
	~SMALL - the ELSE catches it because the conditions conflict#ELSE only fires when no WHEN matches. Here the > 20000 branch matches, so ELSE is never reached.
}

// sb-m5-l1: CASE WHEN: Conditional Logic in SQL
::sb-m5-l1 Q2::[plain]You want the number of trades in each size bucket. After building size_category with CASE WHEN, which clause turns hundreds of rows into one row per bucket?{
	=GROUP BY size_category, alongside COUNT(*) in the SELECT#GROUP BY collapses all rows sharing the same CASE label into one group, and COUNT(*) tallies each group. This CASE-then-GROUP-BY pattern is how every size-tier count in a risk report is produced.
	~ORDER BY size_category, which sorts the labels alphabetically#ORDER BY only sorts rows; it does not combine them. You would still have 200 rows, just alphabetised - not the per-bucket tally you want.
	~WHERE size_category IS NOT NULL, to filter out unlabelled trades#WHERE filters rows but never aggregates them. And with an ELSE branch, no row is NULL anyway - every trade gets a label.
	~DISTINCT size_category, which lists each bucket once#DISTINCT returns the unique labels (LARGE, MEDIUM, SMALL) but cannot attach a COUNT to each. You need GROUP BY to both dedupe and aggregate.
}

$CATEGORY: $course$/EcoFinLearn/SQL Foundations/Subqueries

// sb-m6-l1: Subqueries: Find Above-Average Stocks
::sb-m6-l1 Q1::[plain]Your subquery (SELECT AVG(close_price) FROM equity_prices) returns the whole-market average. Given that this average is a weighted blend of the five per-ticker averages, how many tickers can the final HAVING filter ever return?{
	=Between 1 and 4 - never all 5, because at least the lowest-average ticker sits below the blended market average#A weighted average of five distinct numbers is strictly greater than the smallest and strictly less than the largest. So the cheapest name is always below the benchmark (excluded) and the priciest is always above it (kept) - the result is always a strict subset.
	~Always exactly 3, because that is what the sample data shows#The sandbox data does return 3 today, but that is a fact about this synthetic dataset, not about the query\: run it on other prices and the count can be anything from 1 to 4. The one guarantee is structural\: a strict subset of 1 to 4, never all 5.
	~All 5, since every ticker has a positive average close#That is what the placeholder HAVING > 0 does. Comparing against the market average - not zero - necessarily drops the below-average names.
	~0 - the subquery filters everything out#The highest-average ticker is always above the blended market average, so at least one row always survives. You can never filter everything out with an above-average test.
}

// sb-m6-l1: Subqueries: Find Above-Average Stocks
::sb-m6-l1 Q2::[plain]A colleague rewrites your HAVING clause as WHERE AVG(close_price) > (SELECT AVG(close_price) FROM equity_prices) and the query errors out. Why?{
	=WHERE is evaluated before GROUP BY, so the per-ticker AVG does not exist yet - aggregate filters must go in HAVING#The logical order is FROM → WHERE → GROUP BY → HAVING. WHERE sees individual rows, not group aggregates, so referencing AVG() there is a misuse of an aggregate. HAVING runs after grouping and can see it.
	~Subqueries are not allowed inside WHERE at all#Scalar subqueries are perfectly legal in WHERE - e.g. WHERE close_price > (SELECT AVG(close_price) ...). The error is the aggregate AVG() in WHERE, not the subquery.
	~The subquery returns too many rows for a comparison#SELECT AVG(...) with no GROUP BY returns exactly one value, so it is a valid scalar. The problem is where the aggregate on the outer query is being filtered.
	~AVG() cannot appear more than once in a single statement#AVG() can appear many times - in the SELECT list, the HAVING, and the subquery all at once. The restriction is about WHERE vs HAVING timing, not a count of AVG calls.
}

// sb-m6-l1: Subqueries: Find Above-Average Stocks
::sb-m6-l1 Q3::[plain]What makes the inner SELECT here a "scalar" subquery specifically?{
	=It returns exactly one row and one column - a single value SQL can use in place of a number#AVG(close_price) with no GROUP BY collapses the whole table to one cell. Because it yields a single scalar value, it slots directly into the > comparison as if you had typed the number.
	~It is written inside parentheses#Parentheses are just syntax; a subquery returning many rows (e.g. for IN) is also parenthesised but is not scalar. Scalar is defined by returning one value, not by the brackets.
	~It references a column from the outer query#That describes a correlated subquery. This inner SELECT stands alone and never mentions the outer ticker, so it runs once independently.
	~It uses an aggregate function#Using AVG happens to produce one value here, but a scalar subquery need not aggregate - e.g. (SELECT close_price FROM ... ORDER BY trade_date LIMIT 1) is scalar with no aggregate. What matters is the single-value result.
}