SQL For Data Analysis Practice Exam

Question 1 SQL For Data Analysis

What is a non-repeatable read?

LIMIT sets the maximum rows returned; OFFSET specifies how many rows to skip before starting to return rows ORDER BY column_name DESC Reading the same row twice within a transaction and getting different values because another transaction updated and committed in between Simple CASE (CASE x WHEN val THEN result ...) and searched CASE (CASE WHEN condition THEN result ...)

Question 2 SQL For Data Analysis

What is the difference between DELETE and TRUNCATE?

ROWS counts physical rows; RANGE counts by value range, treating rows with equal ORDER BY values as a single peer group Reading the same row twice within a transaction and getting different values because another transaction updated and committed in between The condition used to match rows between the two joined tables DELETE removes rows one at a time, can use WHERE, fires triggers, and is fully logged; TRUNCATE drops all rows quickly, cannot use WHERE, cannot be rolled back in some engines, and resets identity counters

Question 3 SQL For Data Analysis

What is the difference between WHERE and HAVING?

The percent sign (%) = NULL is never true (and never false) because NULL is not comparable; you must use IS NULL to test for NULL A CTE that references itself in its own definition, used to traverse hierarchies, generate series, or walk graphs WHERE filters individual rows before GROUP BY; HAVING filters aggregated groups after GROUP BY

Question 4 SQL For Data Analysis

What is the difference between = ANY and = ALL?

Divides the result set into partitions to which the window function is applied independently Undoes all changes made within the current transaction, returning the database to its prior state A clustered index determines the physical order of data in the table (one per table); a non-clustered index is a separate structure with pointers to the data rows (many per table) = ANY is true if the value matches at least one item; = ALL is true only if it matches every item

Question 5 SQL For Data Analysis

What wildcard matches a single character in LIKE?

The underscore (_) A key made up of two or more columns that together uniquely identify a row A subquery that references columns from the outer query, evaluated once per outer row A view is a stored query re-executed each time it is referenced; a materialized view stores the result physically and must be refreshed

Question 6 SQL For Data Analysis

Can a CTE refer to another CTE defined earlier in the same WITH clause?

COALESCE is ANSI standard and accepts many arguments; ISNULL is SQL Server specific and takes exactly two arguments Yes — CTEs in the same WITH clause can reference earlier CTEs in that clause A join that automatically joins tables on all columns with the same name Removes rows from a table

Question 7 SQL For Data Analysis

What is the result of SELECT 1 + '2' in MySQL?

DELETE removes rows one at a time, can use WHERE, fires triggers, and is fully logged; TRUNCATE drops all rows quickly, cannot use WHERE, cannot be rolled back in some engines, and resets identity counters A join where a table is joined with itself, typically using aliases, to compare rows within the same table 3 (MySQL performs implicit type conversion from string to number) WHERE filters individual rows before GROUP BY; HAVING filters aggregated groups after GROUP BY

Question 8 SQL For Data Analysis

What does NULLIF(a, b) return?

Yes — ORDER BY is evaluated after SELECT, so SELECT-level aliases are allowed COUNT(*) counts all rows including NULLs; COUNT(column) counts only non-NULL values in that column NULL if a = b, otherwise returns a SELECT column AS alias_name or SELECT column alias_name

Question 9 SQL For Data Analysis

What does GROUP BY do?

Returns all rows from the right table and matched rows from the left; rows without a match on the left get NULLs = ANY is true if the value matches at least one item; = ALL is true only if it matches every item Collapses rows that share values in the listed columns into single rows, typically for use with aggregate functions ORDER BY column_name DESC

Question 10 SQL For Data Analysis

What is a self join?

RANK returns ordinal rank with gaps; PERCENT_RANK returns the relative rank as a number between 0 and 1 A join where a table is joined with itself, typically using aliases, to compare rows within the same table UNION combines result sets and removes duplicates; UNION ALL combines them and keeps all rows including duplicates A stored, precomputed result set of a query that is refreshed on a schedule or on demand, trading freshness for read speed

Question 11 SQL For Data Analysis

What is a derived table?

Returns all rows from both tables, with NULLs in places where there is no match on either side Simple CASE (CASE x WHEN val THEN result ...) and searched CASE (CASE WHEN condition THEN result ...) Skips a row that would violate a uniqueness constraint instead of erroring (PostgreSQL syntax; MySQL equivalent is INSERT IGNORE) A subquery used in the FROM clause as if it were a table, typically with an alias

Question 12 SQL For Data Analysis

What does the INSERT ... ON CONFLICT DO NOTHING do?

Returns values conditionally, similar to if/else, evaluated top to bottom with the first match returned ON defines an arbitrary join condition with any expression; USING (col) joins on identically named columns and produces one output column for that key To query and retrieve data from one or more tables Skips a row that would violate a uniqueness constraint instead of erroring (PostgreSQL syntax; MySQL equivalent is INSERT IGNORE)

Question 13 SQL For Data Analysis

What is the difference between JOIN ON and JOIN USING?

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE Returns only unique values, removing duplicate rows from the result set ON defines an arbitrary join condition with any expression; USING (col) joins on identically named columns and produces one output column for that key HAVING

Question 14 SQL For Data Analysis

What does the SUM function ignore by default?

A sliding window of three rows: the current row plus the one before and after it Converts a value from one data type to another, e.g. CAST(x AS INT) NULL values A unit of work consisting of one or more SQL statements that either all succeed (commit) or all fail (rollback)

Question 15 SQL For Data Analysis

What is a full-text search in SQL?

CAST is ANSI standard portable syntax; CONVERT is SQL Server specific and supports a style argument for date/format conversions A subquery that references columns from the outer query, evaluated once per outer row A search over text columns using word-based matching, often via MATCH ... AGAINST (MySQL), tsvector/tsquery (PostgreSQL), or FULLTEXT indexes COALESCE is ANSI standard and accepts many arguments; ISNULL is SQL Server specific and takes exactly two arguments

Question 16 SQL For Data Analysis

What does LIMIT 10 OFFSET 20 return?

The 10 rows starting at position 21 (rows 21-30) When you need an existence check (EXISTS), a scalar value, or a result that is logically independent of the joined columns; JOINs are usually clearer for combining columns FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT/OFFSET Simple CASE (CASE x WHEN val THEN result ...) and searched CASE (CASE WHEN condition THEN result ...)

Question 17 SQL For Data Analysis

What does LAG(column, 1) do?

Returns the value of column from the previous row in the partition, or NULL if none OVER ([PARTITION BY cols] [ORDER BY cols] [frame_clause]) COUNT counts all rows (or non-NULL values); COUNT DISTINCT counts only unique non-NULL values A full table scan where every row is read — used when no suitable index exists or when the table is small

Question 18 SQL For Data Analysis

What is the difference between COUNT and COUNT DISTINCT?

COUNT counts all rows (or non-NULL values); COUNT DISTINCT counts only unique non-NULL values IN compares a value against a list or subquery result; EXISTS checks only for the presence of rows and is usually faster for correlated existence checks SELECT * FROM table_name AS t or SELECT * FROM table_name t INNER JOIN returns only rows with matches in both tables; LEFT JOIN returns all rows from the left table plus matched rows from the right (NULLs where no match)

Question 19 SQL For Data Analysis

What does PARTITION BY do inside an OVER clause?

Divides the result set into partitions to which the window function is applied independently COALESCE is ANSI standard and accepts many arguments; ISNULL is SQL Server specific and takes exactly two arguments A function that performs a calculation across a set of rows related to the current row without collapsing them into a single output row A subquery that references columns from the outer query, evaluated once per outer row

Question 20 SQL For Data Analysis

How do you match a literal percent sign with LIKE?

Escape it: WHERE col LIKE '%\%%' ESCAPE '\' Aliases defined in SELECT are not available in WHERE or GROUP BY because those clauses are evaluated before SELECT RANK leaves gaps after ties (1,2,2,4); DENSE_RANK does not leave gaps (1,2,2,3) The sequence of steps the database engine uses to execute a SQL query, including join order, index usage, and aggregation strategies

Question 21 SQL For Data Analysis

How do you alias a table in a query?

The percent sign (%) SELECT * FROM table_name AS t or SELECT * FROM table_name t Yes — BETWEEN x AND y includes both x and y Both enforce uniqueness, but a primary key is implicitly NOT NULL and there can be only one per table; a unique key allows NULLs (one per column in many engines) and you can have multiple

Question 22 SQL For Data Analysis

What is the difference between a correlated and a non-correlated subquery?

WHERE filters individual rows before GROUP BY; HAVING filters aggregated groups after GROUP BY LIKE is case-sensitive (in most engines); ILIKE is case-insensitive (PostgreSQL) A non-correlated subquery is independent of the outer query and runs once; a correlated subquery depends on the outer query and runs for each outer row Collapses rows that share values in the listed columns into single rows, typically for use with aggregate functions

Question 23 SQL For Data Analysis

What does the ON clause specify in a JOIN?

Returns the first non-NULL argument from a list of expressions The condition used to match rows between the two joined tables RANK leaves gaps after ties (1,2,2,4); DENSE_RANK does not leave gaps (1,2,2,3) Yes — CTEs in the same WITH clause can reference earlier CTEs in that clause

Question 24 SQL For Data Analysis

What is the syntax of the OVER clause?

Reading the same row twice within a transaction and getting different values because another transaction updated and committed in between A CTE that references itself in its own definition, used to traverse hierarchies, generate series, or walk graphs OVER ([PARTITION BY cols] [ORDER BY cols] [frame_clause]) A key made up of two or more columns that together uniquely identify a row

Question 25 SQL For Data Analysis

What does the frame clause ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING define?

A sliding window of three rows: the current row plus the one before and after it UNPIVOT — converting columns into rows Adding an interval like INTERVAL '7 days' is portable; adding a raw number of days depends on the engine and date arithmetic semantics WHERE

Question 26 SQL For Data Analysis

What is the difference between RANK() and PERCENT_RANK()?

A query nested inside another query (in SELECT, FROM, WHERE, or HAVING) EXPLAIN or EXPLAIN ANALYZE (the latter actually runs the query and reports actual timings and row counts) RANK returns ordinal rank with gaps; PERCENT_RANK returns the relative rank as a number between 0 and 1 A subquery used in the FROM clause as if it were a table, typically with an alias

Question 27 SQL For Data Analysis

What is UNION and how does it differ from UNION ALL?

UNION combines result sets and removes duplicates; UNION ALL combines them and keeps all rows including duplicates SELECT * FROM table_name AS t or SELECT * FROM table_name t Escape it: WHERE col LIKE '%\%%' ESCAPE '\' To query and retrieve data from one or more tables

Question 28 SQL For Data Analysis

What is the difference between COALESCE and ISNULL?

Divides the result set into partitions to which the window function is applied independently COALESCE is ANSI standard and accepts many arguments; ISNULL is SQL Server specific and takes exactly two arguments READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE Index seek locates rows directly using the B-tree (efficient for selective predicates); index scan walks the entire index range (still full, but smaller than the table)

Question 29 SQL For Data Analysis

What is a primary key?

Reading data written by another transaction that has not yet been committed Reading the same row twice within a transaction and getting different values because another transaction updated and committed in between Simple CASE (CASE x WHEN val THEN result ...) and searched CASE (CASE WHEN condition THEN result ...) A column or set of columns that uniquely identifies each row in a table; cannot be NULL and enforces uniqueness

Question 30 SQL For Data Analysis

What wildcard matches any sequence of characters in LIKE?

Tests whether the expression equals any value in the given list INSERT, UPDATE, and DELETE become slower because the index must be maintained in addition to the table A view is a stored query re-executed each time it is referenced; a materialized view stores the result physically and must be refreshed The percent sign (%)

Question 31 SQL For Data Analysis

What is the syntax for a CTE?

Assigns a unique sequential integer to each row within its partition, 1-based, with no ties WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name Escape it: WHERE col LIKE '%\%%' ESCAPE '\' Yes — BETWEEN x AND y includes both x and y

Question 32 SQL For Data Analysis

What is the difference between DATE and TIMESTAMP?

3 (MySQL performs implicit type conversion from string to number) DATE stores year/month/day only; TIMESTAMP additionally stores hour/minute/second (and often fractional seconds and time zone) Removes rows from a table A key made up of two or more columns that together uniquely identify a row

Question 33 SQL For Data Analysis

What is the difference between LIKE and ILIKE?

OVER ([PARTITION BY cols] [ORDER BY cols] [frame_clause]) Modifies existing rows in a table A situation where two or more transactions each hold a lock the other needs, blocking each other indefinitely until the database detects and aborts one LIKE is case-sensitive (in most engines); ILIKE is case-insensitive (PostgreSQL)

Question 34 SQL For Data Analysis

What does ROLLBACK do?

Pattern matching on string columns using wildcards All queries must have the same number of columns in the same order with compatible data types Undoes all changes made within the current transaction, returning the database to its prior state UNION returns rows from either query; INTERSECT returns only rows that appear in both queries

Question 35 SQL For Data Analysis

What is a scalar subquery?

The first non-NULL argument, or NULL if all are NULL Divides the result set into partitions to which the window function is applied independently A subquery that returns exactly one row with one column, usable wherever a single value is expected COUNT(*) counts all rows including NULLs; COUNT(column) counts only non-NULL values in that column

Question 36 SQL For Data Analysis

What is a sequential scan?

The sequence of steps the database engine uses to execute a SQL query, including join order, index usage, and aggregation strategies A full table scan where every row is read — used when no suitable index exists or when the table is small All queries must have the same number of columns in the same order with compatible data types OVER ([PARTITION BY cols] [ORDER BY cols] [frame_clause])

Question 37 SQL For Data Analysis

What does COMMIT do?

Reading the same row twice within a transaction and getting different values because another transaction updated and committed in between SELECT column AS alias_name or SELECT column alias_name A subquery that references columns from the outer query, evaluated once per outer row Permanently applies all changes made within the current transaction

Question 38 SQL For Data Analysis

What are the four SQL transaction isolation levels?

A join that automatically joins tables on all columns with the same name Reading data written by another transaction that has not yet been committed The underscore (_) READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

Question 39 SQL For Data Analysis

What is the difference between = ANY and IN?

ON defines an arbitrary join condition with any expression; USING (col) joins on identically named columns and produces one output column for that key They are functionally equivalent: x = ANY(a, b, c) is the same as x IN (a, b, c) DELETE removes rows one at a time, can use WHERE, fires triggers, and is fully logged; TRUNCATE drops all rows quickly, cannot use WHERE, cannot be rolled back in some engines, and resets identity counters INSERT, UPDATE, and DELETE become slower because the index must be maintained in addition to the table

Question 40 SQL For Data Analysis

What is a CROSS JOIN?

Divides the result set into partitions to which the window function is applied independently SELECT column AS alias_name or SELECT column alias_name Returns the Cartesian product of two tables — every row of table A combined with every row of table B Yes — ORDER BY is evaluated after SELECT, so SELECT-level aliases are allowed

Question 41 SQL For Data Analysis

What does IN (val1, val2, ...) do?

Tests whether the expression equals any value in the given list RANK returns ordinal rank with gaps; PERCENT_RANK returns the relative rank as a number between 0 and 1 NULL if a = b, otherwise returns a UNPIVOT — converting columns into rows

Question 42 SQL For Data Analysis

What is a CTE in SQL?

COUNT counts all rows (or non-NULL values); COUNT DISTINCT counts only unique non-NULL values Returns all rows from the right table and matched rows from the left; rows without a match on the left get NULLs A Common Table Expression: a named temporary result set defined with WITH that can be referenced within a single query UNION combines result sets and removes duplicates; UNION ALL combines them and keeps all rows including duplicates

Question 43 SQL For Data Analysis

How does a view differ from a materialized view?

= NULL is never true (and never false) because NULL is not comparable; you must use IS NULL to test for NULL A view is a stored query re-executed each time it is referenced; a materialized view stores the result physically and must be refreshed COUNT(*) counts all rows including NULLs; COUNT(column) counts only non-NULL values in that column It applies to the single implicit group of all rows, allowing HAVING to filter on aggregates in the same way as with explicit groups

Question 44 SQL For Data Analysis

What is the LIKE operator used for?

Use a window function with SUM() OVER (ORDER BY ...) with an appropriate frame A Common Table Expression: a named temporary result set defined with WITH that can be referenced within a single query Tests whether a value lies within an inclusive range of values Pattern matching on string columns using wildcards

Question 45 SQL For Data Analysis

What is the difference between a clustered and a non-clustered index?

A clustered index determines the physical order of data in the table (one per table); a non-clustered index is a separate structure with pointers to the data rows (many per table) A function that performs a calculation across a set of rows related to the current row without collapsing them into a single output row ORDER BY column_name DESC Returns the value of column from the next row in the partition, or NULL if none

Question 46 SQL For Data Analysis

What is the CAST function used for?

= NULL is never true (and never false) because NULL is not comparable; you must use IS NULL to test for NULL The percent sign (%) Converts a value from one data type to another, e.g. CAST(x AS INT) A data structure (typically a B-tree) that speeds up lookups at the cost of additional storage and slower writes

Question 47 SQL For Data Analysis

What does DATE_TRUNC do?

The underscore (_) SELECT * FROM table_name AS t or SELECT * FROM table_name t Truncates a date/time value to a specified precision (e.g. 'month', 'day', 'hour') Lets you compute multiple groupings in a single query, equivalent to the UNION of several GROUP BY queries

Question 48 SQL For Data Analysis

What is a phantom read?

Divides the rows in each partition into 4 buckets as evenly as possible and assigns the bucket number to each row Returns only unique values, removing duplicate rows from the result set Running the same range query twice in a transaction and getting a different set of rows because another transaction inserted or deleted matching rows in between Returns rows from the first query that are not present in the second query

Question 49 SQL For Data Analysis

What does EXISTS return?

A CTE that references itself in its own definition, used to traverse hierarchies, generate series, or walk graphs TRUE if the subquery returns at least one row, FALSE otherwise; never returns NULL Tests whether the expression equals any value in the given list Yes — CTEs in the same WITH clause can reference earlier CTEs in that clause

Question 50 SQL For Data Analysis

What keyword marks the recursive part of a recursive CTE?

A subquery that references columns from the outer query, evaluated once per outer row The first non-NULL argument, or NULL if all are NULL UNION ALL (typically) joining the anchor member to the recursive member When you need an existence check (EXISTS), a scalar value, or a result that is logically independent of the joined columns; JOINs are usually clearer for combining columns