Ten questions at a time, drawn from 160. Every answer is explained. Nothing is saved and no account is needed.
Which of the following best describes the role of a schema in a relational database?
Practice quiz for SQL. Scores are not saved.
Which of the following best describes the role of a schema in a relational database?
Answer: A logical container that organizes tables and other database objects. A schema acts as a namespace or container to group tables logically, which helps in organization and security. It is not physical hardware (option 0), it is not a query optimizer (option 2), and it is not a reporting tool (option 3).
From lesson: SQL Overview — Databases and Tables
If you want to ensure that a column in a table never contains a missing value, which constraint should you apply?
Answer: NOT NULL. NOT NULL specifically prohibits the inclusion of missing data in a column. While PRIMARY KEY implies NOT NULL, it also enforces uniqueness and indexing, which might not be needed. UNIQUE only ensures no duplicates, and DEFAULT provides a value only if one isn't specified.
From lesson: SQL Overview — Databases and Tables
In a database table, what is the primary purpose of defining a data type for a column?
Answer: To dictate how the database engine interprets and validates the data. Data types define the domain of values (e.g., integers, dates, strings), allowing the engine to store data efficiently and perform valid operations. Option 0 is incorrect because storage size depends on rows, not just column types; option 2 is unrelated to encryption; option 3 is not the primary purpose of data types.
From lesson: SQL Overview — Databases and Tables
When querying a database, why is it considered better practice to explicitly list column names instead of using the asterisk (*) wildcard?
Answer: It prevents errors if the table structure changes and improves code readability. Explicitly listing columns ensures your application logic remains stable if table columns are added or reordered. The wildcard returns all current columns, which can lead to unexpected data in downstream code. The other options are incorrect as they do not impact speed, filtering, or query clauses.
From lesson: SQL Overview — Databases and Tables
Consider a table named 'Employees'. If you need to filter rows based on a specific department, which clause do you use?
Answer: WHERE. The WHERE clause is specifically designed to filter rows based on conditions. SELECT dictates which columns to show, FROM identifies the table source, and GROUP BY is used to aggregate data, not filter individual rows.
From lesson: SQL Overview — Databases and Tables
If you need to retrieve all records from a 'customers' table where the 'age' is over 25, why is 'SELECT * FROM customers WHERE age > 25' preferred over 'SELECT * FROM customers' and filtering manually?
Answer: The database engine handles filtering more efficiently than application code. Option 0 is correct because databases are optimized to filter data at the storage level, reducing network traffic. Option 1 is subjective, option 2 is incorrect, and option 3 is false as SQL does not automatically deduplicate without DISTINCT.
From lesson: SELECT, FROM, WHERE — Core Querying
A query is written as: SELECT name AS full_name FROM users WHERE full_name = 'John'. Why will this query fail?
Answer: Aliases created in the SELECT clause are not yet visible to the WHERE clause. Option 1 is correct because the execution order places FROM and WHERE before SELECT. Options 0, 2, and 3 are factually incorrect regarding SQL syntax rules.
From lesson: SELECT, FROM, WHERE — Core Querying
When querying a column 'status' for rows that do not have a value, why does 'WHERE status != 'active'' miss rows where 'status' is NULL?
Answer: NULL represents an unknown value, so comparing it to a known string results in UNKNOWN. Option 2 is correct because NULL is not a value that can be compared using standard operators. Options 0, 1, and 3 misrepresent how NULLs and operators function in relational databases.
From lesson: SELECT, FROM, WHERE — Core Querying
Which of the following is the most efficient way to select specific columns 'first_name' and 'last_name' from an 'employees' table?
Answer: SELECT first_name, last_name FROM employees. Option 1 is the most concise and efficient practice. Option 0 fetches extra data, option 2 is redundant by specifying the table name unnecessarily, and option 3 uses a redundant keyword.
From lesson: SELECT, FROM, WHERE — Core Querying
In a query 'SELECT * FROM orders WHERE order_date = '2023-01-01'', what does the 'WHERE' clause perform?
Answer: It filters the result set to include only rows satisfying the condition. Option 1 is the definition of WHERE. Option 0 describes ORDER BY, option 2 describes the SELECT clause, and option 3 describes LIMIT/TOP.
From lesson: SELECT, FROM, WHERE — Core Querying
If you want to retrieve rows where 'price' is between 10 and 20, which of the following is logically equivalent to the BETWEEN operator?
Answer: price >= 10 AND price <= 20. BETWEEN is inclusive, meaning it checks for values greater than or equal to the start and less than or equal to the end. The first option excludes the bounds, the third creates an overly broad condition, and the fourth creates a near-universal match.
From lesson: Filtering — AND, OR, NOT, IN, BETWEEN, LIKE
Which query correctly finds names that start with 'J' and contain an 'n' anywhere after the 'J'?
Answer: name LIKE 'J%n%'. The first option uses % to represent any number of characters between J and n, and after n. The second and fourth options enforce only a single character between them, and the third fails if there are characters after the 'n'.
From lesson: Filtering — AND, OR, NOT, IN, BETWEEN, LIKE
Why would the clause 'WHERE status IN ('Open', 'Closed', NULL)' fail to return rows where status is NULL?
Answer: NULL is not equal to any value, so it must be checked with IS NULL. In SQL, NULL represents an unknown value. Comparisons using = or IN are never true for NULL. You must explicitly use 'OR status IS NULL' to catch those records.
From lesson: Filtering — AND, OR, NOT, IN, BETWEEN, LIKE
Given 'WHERE x > 5 OR y < 10 AND z = 1', how does SQL evaluate this due to operator precedence?
Answer: x > 5 OR (y < 10 AND z = 1). SQL processes AND before OR. Therefore, the y and z condition is evaluated first as a single logical unit before being ORed with the x condition.
From lesson: Filtering — AND, OR, NOT, IN, BETWEEN, LIKE
Which condition effectively selects all rows except those where 'category' is 'Electronics'?
Answer: All of the above. All three expressions are valid standard SQL ways to express inequality. They will all correctly exclude 'Electronics' but, importantly, they will also exclude NULL values in the 'category' column.
From lesson: Filtering — AND, OR, NOT, IN, BETWEEN, LIKE
Which of the following best describes the default behavior of ORDER BY in SQL?
Answer: It sorts results in ascending order, treating NULL values as the lowest possible value.. Option 0 is correct because standard SQL defaults to ASC, placing NULLs at the start. Option 1 is wrong because it defaults to ASC, not DESC. Option 2 is wrong because NULLs are included, not ignored. Option 3 is wrong because SQL does not guarantee random order without explicit instructions.
From lesson: Sorting with ORDER BY
When sorting by multiple columns, e.g., 'ORDER BY department, salary DESC', what is the priority?
Answer: It sorts by department, then by salary in descending order within each department.. Option 1 is correct: SQL applies the first criterion fully, then applies subsequent criteria to resolve ties within the first group. Option 0 is wrong because order is defined left-to-right. Option 2 and 3 are incorrect because they ignore the explicit instructions provided in the query syntax.
From lesson: Sorting with ORDER BY
If you have a table of 'ProductIDs' stored as TEXT, how do you ensure they sort numerically (1, 2, 10 instead of 1, 10, 2)?
Answer: ORDER BY CAST(ProductID AS UNSIGNED). Option 2 is correct as casting the string to a numeric type forces mathematical sorting. Option 0 sorts lexicographically. Option 1 sorts by length, which is not the same as numerical order. Option 3 is non-standard syntax that may not be supported by all database engines.
From lesson: Sorting with ORDER BY
Why might a query fail if you try to 'ORDER BY' an alias created in the SELECT clause?
Answer: The ORDER BY clause is logically processed before the SELECT clause in the SQL execution order, so the alias may not be recognized.. Option 2 is correct because, in the logical order of operations, SELECT comes after WHERE but before ORDER BY in some contexts, yet standard SQL scoping varies; referring to the alias is standard, but the logical execution order is why it might fail in specific restricted environments. Options 0, 1, and 3 are conceptually incorrect regarding the order of operations.
From lesson: Sorting with ORDER BY
What is the result of using 'ORDER BY 1 DESC' in a query selecting 'Name, Age FROM Users'?
Answer: The result set will be sorted by Name in descending order.. Option 0 is correct because '1' refers to the first column selected (Name). Option 1 is wrong because 1 refers to the first column, not the second. Option 2 is wrong because ordinal references are standard. Option 3 is wrong because ordinals definitely perform a sort.
From lesson: Sorting with ORDER BY
Which of the following is the most reliable way to implement pagination in SQL?
Answer: Using ORDER BY a unique column, followed by LIMIT and OFFSET. Option 3 is correct because sorting by a unique column ensures the order of rows remains stable between pagination requests. Option 1 is unreliable due to non-deterministic row order. Option 2 is incorrect as OFFSET alone cannot define the number of items returned. Option 4 does not guarantee the sequence of rows retrieved.
From lesson: Limiting Results — LIMIT and OFFSET
If you have 100 rows and execute 'SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20', which rows are returned?
Answer: Rows 21 through 30. Option 2 is correct because OFFSET 20 skips the first 20 rows, and LIMIT 10 fetches the next 10 (rows 21-30). Option 1 is incorrect because it confuses OFFSET with 1-based indexing. Option 3 ignores the OFFSET, and Option 4 misinterprets the syntax.
From lesson: Limiting Results — LIMIT and OFFSET
Why is ORDER BY mandatory when using OFFSET for pagination?
Answer: To guarantee that rows are skipped consistently across multiple queries. Option 3 is correct because relational databases do not have a natural order; without sorting, the 'previous' page and 'next' page might return the same rows. Option 1 is incorrect as sorting usually adds overhead. Option 2 and 4 are false as most engines do not require ORDER BY for syntax, but for logical consistency.
From lesson: Limiting Results — LIMIT and OFFSET
What happens if the LIMIT value is greater than the number of remaining rows after applying the OFFSET?
Answer: The database returns all remaining rows up to the end of the set. Option 2 is correct; LIMIT specifies the maximum number of rows, so if fewer rows exist, the engine returns all available rows. Option 1 and 3 are incorrect as the query remains valid and yields existing data. Option 4 is false as SQL queries are independent of future data state.
From lesson: Limiting Results — LIMIT and OFFSET
If a table has 50 rows and you use 'LIMIT 10 OFFSET 45', what is the result?
Answer: The query returns 5 rows. Option 2 is correct because after skipping 45 rows, only 5 rows remain, so the database returns all of them. Option 1 is incorrect as it assumes the LIMIT overrides availability. Option 3 is false as there is no overflow error. Option 4 is incorrect because there is data left after the offset.
From lesson: Limiting Results — LIMIT and OFFSET
What is the result of the expression '5 + NULL' in SQL?
Answer: NULL. In SQL, any arithmetic operation involving NULL results in NULL because the unknown value makes the entire result unknown. Option 0 and 5 are incorrect because they assume NULL is treated as zero, which it is not in standard math. Error is incorrect as it is a valid SQL expression.
From lesson: NULL Values and IS NULL
If you want to count the number of rows where the 'email' column is not empty, which query is most accurate?
Answer: SELECT COUNT(*) FROM users WHERE email IS NOT NULL. Option 1 counts rows but might be ambiguous; Option 2 explicitly filters NULLs and counts the rows. Option 3 is wrong because comparison with NULL using != is invalid. Option 4 counts every row including those with NULL, failing the requirement to exclude them.
From lesson: NULL Values and IS NULL
Why does a subquery using 'WHERE id NOT IN (SELECT parent_id FROM items)' potentially return zero rows if 'parent_id' contains a NULL?
Answer: The expression evaluates to unknown, which is treated as false. When comparing against a set containing NULL, the NOT IN logic becomes unknown for those rows, and since unknown is not true, the records are excluded. Option 1 and 4 are wrong because SQL treats NULL explicitly. Option 3 is wrong because the syntax is perfectly valid.
From lesson: NULL Values and IS NULL
Which function is the standard way to replace a NULL value with a specific default during a SELECT query?
Answer: COALESCE. COALESCE is the standard ANSI SQL function that takes multiple arguments and returns the first non-NULL value. IFNULL, ISNULL, and NVL are vendor-specific functions (MySQL, SQL Server, and Oracle, respectively) and are not part of the standard SQL specification.
From lesson: NULL Values and IS NULL
What is the logical result of the condition 'NULL IS NULL'?
Answer: TRUE. The 'IS NULL' operator is a specific predicate designed to test for the presence of NULL; it returns TRUE if the value is NULL and FALSE otherwise. Option 1 is correct. Options 2, 3, and 4 are incorrect because they confuse the 'IS' operator with equality comparison logic.
From lesson: NULL Values and IS NULL
If a column 'score' has values [10, NULL, 20], what will SELECT AVG(score) FROM table return?
Answer: 15. AVG() ignores NULL values and calculates the average of [10, 20], which is 15. Option 10 is wrong because it assumes NULL is 0. Option 30 is the sum, not the average. NULL is incorrect because aggregate functions exclude missing data from calculations.
From lesson: Aggregation Functions — COUNT, SUM, AVG, MIN, MAX
Which of the following clauses is used to filter records AFTER an aggregation has been performed?
Answer: HAVING. HAVING is specifically designed to filter groups created by aggregate functions. WHERE filters row-by-row before aggregation, GROUP BY organizes the data, and ORDER BY sorts the final output.
From lesson: Aggregation Functions — COUNT, SUM, AVG, MIN, MAX
You want to find the number of unique cities in a table. Which syntax is correct?
Answer: COUNT(DISTINCT city). COUNT(DISTINCT city) ensures each city name is counted only once. COUNT(city) counts duplicates. The other options are syntactically incorrect or perform the wrong mathematical operation.
From lesson: Aggregation Functions — COUNT, SUM, AVG, MIN, MAX
Given a table of employees, why would 'SELECT department, MAX(salary) FROM employees' fail in many SQL modes?
Answer: The query is missing a GROUP BY clause for the department column.. To select an aggregate alongside a non-aggregated column, the non-aggregated column must appear in the GROUP BY clause. Without it, the engine cannot map the maximum salary to the correct department. The other options are false; MAX works on numbers and can be used without WHERE.
From lesson: Aggregation Functions — COUNT, SUM, AVG, MIN, MAX
What is the result of COUNT(*) on a table containing 10 rows, where 3 rows have a NULL value in the 'price' column?
Answer: 10. COUNT(*) counts all rows regardless of NULL values. Therefore, all 10 rows are counted. 7 would be the result if you used COUNT(price), and 3 is incorrect as it represents only the NULL count.
From lesson: Aggregation Functions — COUNT, SUM, AVG, MIN, MAX
Which of the following scenarios best justifies the use of a HAVING clause instead of a WHERE clause?
Answer: Filtering results to show only departments with a total salary expenditure exceeding $100,000.. Filtering by total salary (an aggregate) requires HAVING because the total is calculated after grouping. The other options are row-level filters that should be handled by WHERE for better performance.
From lesson: GROUP BY and HAVING
Consider a table of sales. Which query will successfully identify products that have been sold more than 50 times in total?
Answer: SELECT product_id FROM sales GROUP BY product_id HAVING COUNT(product_id) > 50. Correct structure is GROUP BY followed by HAVING for the aggregate condition. The first is wrong because WHERE cannot contain aggregates; the third is wrong because HAVING needs a GROUP BY; the fourth has the clauses in the incorrect order.
From lesson: GROUP BY and HAVING
What happens if you run: SELECT category, SUM(price) FROM products GROUP BY category WHERE price > 10?
Answer: It generates a syntax error because WHERE must appear before GROUP BY.. SQL requires the WHERE clause to come before the GROUP BY clause. The others describe logical outcomes that won't happen because the query is syntactically invalid.
From lesson: GROUP BY and HAVING
If you want to group by 'department' and display the average salary, which SELECT statement is valid?
Answer: SELECT department, AVG(salary) FROM employees GROUP BY department. Option 2 is valid because every non-aggregated column in the SELECT is included in the GROUP BY. Option 1 is invalid due to 'name', 3 is invalid due to missing GROUP BY, and 4 groups by the wrong column.
From lesson: GROUP BY and HAVING
When is an alias defined in the SELECT clause accessible in the HAVING clause?
Answer: Never, because HAVING is logically evaluated before the SELECT clause in SQL.. Standard SQL evaluates SELECT after HAVING, so aliases are typically not available. While some engines allow it as an extension, it is not standard, making 'Only when using specific databases' the accurate technical assessment.
From lesson: GROUP BY and HAVING
If you run SELECT DISTINCT first_name, last_name FROM employees;, what determines if a row is considered a duplicate?
Answer: The combination of both first_name and last_name must be unique.. DISTINCT applies to the entire tuple of selected columns. Option 0 and 1 are wrong because the modifier is not restricted to one column. Option 3 is wrong because DISTINCT ignores columns not explicitly listed in the SELECT clause.
From lesson: DISTINCT — Removing Duplicates
What is the result of SELECT DISTINCT department FROM staff; if there are three employees in the 'Sales' department and two in 'HR'?
Answer: Sales, HR. DISTINCT removes all duplicate values from the result set. Option 0 is wrong as it doesn't remove duplicates. Option 2 and 3 are wrong because there is no aggregation occurring here.
From lesson: DISTINCT — Removing Duplicates
How does SQL handle NULL values when the DISTINCT keyword is applied to a column containing them?
Answer: It treats all NULL values as duplicates of each other and returns one NULL.. In SQL, NULLs are treated as duplicates of each other in the context of DISTINCT. Option 0 is wrong because they aren't removed, 1 is wrong because they are grouped, and 3 is wrong because the column is still processed.
From lesson: DISTINCT — Removing Duplicates
Which of the following is functionally equivalent to SELECT DISTINCT status FROM orders;?
Answer: SELECT status FROM orders GROUP BY status;. GROUP BY status creates one row per unique status value, identical to the behavior of DISTINCT. Option 1 doesn't remove duplicates, 2 doesn't filter, and 3 is a filter that doesn't handle duplicates.
From lesson: DISTINCT — Removing Duplicates
You want to know the number of unique cities where customers are located. Which query is correct?
Answer: SELECT COUNT(DISTINCT city) FROM customers;. COUNT(DISTINCT column) is the standard syntax for counting unique occurrences. Option 0 counts all and then tries to apply distinct (invalid), 2 is syntactically incorrect, and 3 applies distinct to a single aggregate result.
From lesson: DISTINCT — Removing Duplicates
If multiple conditions in a CASE statement evaluate to true for a single row, which result is returned?
Answer: The result of the first condition that evaluates to true. SQL stops evaluating once the first true condition is met. The first option is correct because the engine processes conditions top-down. The others are wrong because SQL does not aggregate or error out on subsequent matches; it simply ignores them.
From lesson: CASE WHEN — Conditional Logic
What is the result of a CASE statement that lacks an ELSE clause when none of the WHEN conditions are met?
Answer: NULL. By default, if no conditions are matched and no ELSE is provided, the expression returns NULL. 0 and empty string are incorrect because they are specific values not implicitly assumed, and it is not an error.
From lesson: CASE WHEN — Conditional Logic
Which of the following is the valid structure for a CASE expression?
Answer: CASE WHEN condition THEN result ELSE result END. The first option follows standard SQL syntax. The second is common in other programming languages, the third is missing the ELSE keyword (though optional, the first is more complete), and the fourth uses non-existent syntax.
From lesson: CASE WHEN — Conditional Logic
Why might you get a data type conversion error when using a CASE statement?
Answer: Because the THEN clauses return different, incompatible data types. SQL expects all possible outputs of a single CASE expression to be castable to the same type. If one branch returns a string and another returns an integer, an error occurs. The other options describe syntax errors or logical sequence issues, not type conflicts.
From lesson: CASE WHEN — Conditional Logic
How can you use a CASE statement to effectively categorize numerical values into ranges?
Answer: By evaluating ranges with sequential WHEN conditions and an ELSE clause. Defining sequential ranges (e.g., WHEN val < 10, WHEN val < 20) is the standard way to categorize data. Nesting is unnecessary and inefficient, GROUP BY usually needs the alias, and WHERE clauses don't use CASE statements for categorization in this manner.
From lesson: CASE WHEN — Conditional Logic
What is the result of an INNER JOIN between Table A (5 rows) and Table B (5 rows) if none of the join keys match?
Answer: 0 rows. An INNER JOIN returns only rows where there is a match in both tables. If there are zero matches, the result set is empty (0 rows). It is not a Cartesian product (25) or a sum of rows (10 or 5).
From lesson: INNER JOIN
Which of the following describes the fundamental purpose of an INNER JOIN?
Answer: To retrieve only rows that have corresponding values in both tables. INNER JOIN is strictly an intersection operation. It filters out any rows that do not have a counterpart in the other table. Option 1 describes a Cross Join, Option 3 describes a Left Join, and Option 4 is an unnecessary restriction.
From lesson: INNER JOIN
If you perform an INNER JOIN and the resulting record count is higher than the count of either individual table, what does this imply?
Answer: There is a many-to-many relationship involving duplicate keys. This happens when there are duplicate keys in the joining columns, causing a one-to-many or many-to-many relationship that multiplies the result rows. It is not an error (failed) or a missing condition; it is a mathematical consequence of the data distribution.
From lesson: INNER JOIN
Why is it recommended to use the 'ON' clause instead of the 'WHERE' clause for join conditions?
Answer: The ON clause makes the intent clearer and improves maintainability. Using ON is the modern SQL standard that separates the join logic from the filtering logic. While WHERE can technically function for simple joins, it lacks clarity. Option 1 is false, and Option 3 is an exaggeration.
From lesson: INNER JOIN
When you have two tables, Users and Orders, what will 'SELECT * FROM Users INNER JOIN Orders ON Users.id = Orders.user_id' return?
Answer: Only users who have placed at least one order. Because it is an INNER JOIN, it excludes any User who has no corresponding entry in the Orders table (and vice-versa). Thus, only users with orders appear. Options 1 and 2 describe Outer Joins, and Option 4 describes a full data dump.
From lesson: INNER JOIN
If Table A has 10 rows and Table B has 5 rows, and 3 rows match based on the join key, how many rows will result from a LEFT JOIN (A LEFT JOIN B)?
Answer: 10 rows. A LEFT JOIN returns all rows from the left table (10) regardless of matches. For the 3 matching rows, data from B is joined; for the 7 non-matching rows, columns from B are NULL. The others are wrong because 3 represents an INNER JOIN, 8 is a miscalculation, and 15 is the sum of rows not accounting for the join logic.
From lesson: LEFT, RIGHT, and FULL OUTER JOIN
Why would moving a condition from a WHERE clause to an ON clause change the result of a LEFT JOIN?
Answer: Conditions in WHERE filter rows after the join is performed, while ON filters them during the join process.. In a LEFT JOIN, a WHERE clause filters the entire result set, potentially removing the 'padded' rows with NULLs. An ON clause filters only the right table before the join occurs, preserving the left table rows. The other options are incorrect because performance differences are secondary and the result set differs significantly.
From lesson: LEFT, RIGHT, and FULL OUTER JOIN
What is the primary characteristic of a FULL OUTER JOIN?
Answer: It returns all rows from both tables, filling with NULLs where no match exists.. FULL OUTER JOIN is the combination of LEFT and RIGHT joins, showing all data from both sides. Option 0 describes INNER JOIN, option 1 describes RIGHT JOIN, and option 3 describes CROSS JOIN.
From lesson: LEFT, RIGHT, and FULL OUTER JOIN
Which scenario best justifies the use of a LEFT JOIN over an INNER JOIN?
Answer: When you want to identify records in the left table that have no corresponding record in the right table.. LEFT JOINs are ideal for 'missing' data reports because the NULLs generated on the right side indicate a lack of correspondence. INNER JOINs discard non-matching rows. The other options do not specifically benefit from the OUTER JOIN logic.
From lesson: LEFT, RIGHT, and FULL OUTER JOIN
You perform a query where Table A is LEFT JOINed to Table B. If you want to find rows in Table A that have no match in Table B, how do you modify the query?
Answer: Add WHERE TableB.id IS NULL. When a match is missing in a LEFT JOIN, the right-side columns are filled with NULL. Checking for IS NULL on a right-side column isolates these rows. IS NOT NULL would return only matched rows, and the other options would either cause errors or produce incorrect cross-product data.
From lesson: LEFT, RIGHT, and FULL OUTER JOIN
You need to pair every product from a 'Products' table with every department from a 'Departments' table to create a master schedule. Which approach is most efficient?
Answer: CROSS JOIN. CROSS JOIN is designed specifically to produce the Cartesian product of two sets. INNER JOIN requires a matching predicate, and SELF JOIN is for joining a table to itself. While LEFT JOIN with ON 1=1 technically works, CROSS JOIN is the semantic standard for this operation.
From lesson: CROSS JOIN and SELF JOIN
A table contains 'EmployeeID' and 'ManagerID'. You want to display the employee's name alongside their manager's name. How do you structure the query?
Answer: Perform a SELF JOIN using an INNER or LEFT JOIN. A SELF JOIN is required to look up a manager's name within the same table. A CROSS JOIN would produce incorrect pairings. Joining on primary keys would only match an employee to themselves, not their manager.
From lesson: CROSS JOIN and SELF JOIN
What is the primary difference between a CROSS JOIN and an INNER JOIN?
Answer: INNER JOIN requires a join condition, whereas CROSS JOIN does not. INNER JOIN requires an ON or USING clause to filter results, whereas CROSS JOIN performs a blind multiplication of rows. CROSS JOIN does not require equal row counts, and performance depends entirely on the size of the tables and the efficiency of the join predicate.
From lesson: CROSS JOIN and SELF JOIN
If Table A has 10 rows and Table B has 5 rows, how many rows will result from a CROSS JOIN?
Answer: 50. A CROSS JOIN produces the Cartesian product: 10 multiplied by 5 equals 50. 15 would be the result of a UNION, and 5 is incorrect as it implies a standard join filter.
From lesson: CROSS JOIN and SELF JOIN
In a SELF JOIN, why must you use table aliases like 'A' and 'B'?
Answer: To distinguish between the two instances of the same table. Without aliases, the database engine cannot determine which table instance a column refers to, resulting in an 'ambiguous column' error. Aliases have no impact on execution speed, circular references, or window function availability.
From lesson: CROSS JOIN and SELF JOIN
If you perform an INNER JOIN between a Table A (100 rows) and Table B (50 rows), and 20 rows in A have no match in B, how many rows will the result set contain?
Answer: 80 rows. An INNER JOIN only returns rows where there is a match in both tables. Since 20 rows in Table A have no match, only 80 rows (100 - 20) are returned. Other options fail because they either add rows, guess wrong, or ignore the matching constraint.
From lesson: Joining Multiple Tables
What is the primary difference between a LEFT JOIN and an INNER JOIN regarding the returned result set?
Answer: LEFT JOIN includes all rows from the left table, even if no match exists in the right table.. LEFT JOIN ensures all records from the primary (left) table are kept, filling missing matches with NULL. INNER JOIN excludes non-matching records entirely. Other options incorrectly describe the behavior or claim no difference.
From lesson: Joining Multiple Tables
When joining three tables (A, B, and C), what happens if you use a LEFT JOIN for A to B, and then an INNER JOIN for B to C?
Answer: The entire result set effectively becomes an INNER JOIN, excluding rows that don't match table C.. The INNER JOIN to C filters out any rows from the intermediate set (A-B) that do not have a corresponding record in C. Because the subsequent filter is restrictive, it overrides the inclusive nature of the previous LEFT JOIN. Other options are incorrect as they ignore the restrictive nature of INNER JOIN.
From lesson: Joining Multiple Tables
Why should you use table aliases (e.g., 'FROM orders o') in a query involving multiple joins?
Answer: To improve code readability and explicitly identify which table a column belongs to.. Aliases make the code concise and prevent 'ambiguous column' errors when different tables share identical column names. Aliases do not impact execution speed or bypass security, and they do not 'ignore' duplicate names; they resolve them.
From lesson: Joining Multiple Tables
You need a list of all customers, including those who have never placed an order. Which join approach is correct?
Answer: LEFT JOIN from customers to orders.. A LEFT JOIN keeps every row from the 'customers' table. If no match is found in the 'orders' table, the join columns appear as NULL, which is the desired behavior for a list including those without orders. INNER JOIN excludes these customers, RIGHT JOIN would require the orders table to be the primary, and CROSS JOIN creates an invalid combination of all rows.
From lesson: Joining Multiple Tables
Which type of subquery must return exactly one row and one column to be used in a SELECT list or a WHERE clause with a comparison operator?
Answer: Scalar subquery. A scalar subquery returns a single value (one row, one column). Table subqueries return multiple rows/columns, and row subqueries return one row with multiple columns. Correlated subqueries are a method of execution, not a return type.
From lesson: Subqueries — Scalar, Row, Table
When using a subquery in a FROM clause (a derived table), what is the most important requirement for it to function correctly in most SQL dialects?
Answer: The subquery must be assigned a unique table alias.. Derived tables (table subqueries) require an alias so the outer query can reference the resulting set. Scalar requirements apply to SELECT clauses, and correlation is optional, not mandatory.
From lesson: Subqueries — Scalar, Row, Table
If you need to compare a tuple of two columns (e.g., department_id and salary) against the results of a subquery, which type are you using?
Answer: Row subquery. A row subquery returns a single row consisting of multiple columns, allowing for direct tuple comparison against an outer row. A scalar returns only one value, and a table subquery usually requires operators like IN or EXISTS.
From lesson: Subqueries — Scalar, Row, Table
What happens if a subquery used with the '=' operator returns more than one row?
Answer: The query fails with a runtime error.. The '=' operator expects a single scalar value. If the subquery returns multiple rows, the engine cannot equate a single value to a set, resulting in an error. It does not default to the first/last row.
From lesson: Subqueries — Scalar, Row, Table
Which of the following is true regarding a correlated subquery compared to a non-correlated subquery?
Answer: It references one or more columns from the outer query.. A correlated subquery is dependent on the outer query because it references columns from the outer scope, meaning it cannot be run standalone. Non-correlated subqueries can be run independently.
From lesson: Subqueries — Scalar, Row, Table
What is the primary benefit of using a CTE instead of a subquery in a SELECT statement?
Answer: CTEs improve code readability and allow for self-referencing via recursion. CTEs provide a clear, linear structure for complex logic, and they are the only mechanism for recursive queries. Options 1, 3, and 4 are false as CTEs do not cache on disk, are not required for aggregates, and do not bypass constraints.
From lesson: CTEs — Common Table Expressions (WITH)
How do you define multiple CTEs in a single SQL statement?
Answer: By separating the expressions with a comma after the first WITH. Standard SQL syntax for multiple CTEs uses a single 'WITH' followed by comma-separated definitions. Option 1 is invalid syntax, option 2 breaks the statement scope, and option 4 is not how SQL handles multiple result sets.
From lesson: CTEs — Common Table Expressions (WITH)
Which of the following is required for a recursive CTE to function correctly?
Answer: An anchor member, a recursive member, and UNION ALL. Recursive CTEs must have a base case (anchor), a recursive part that references the CTE itself, and the UNION ALL operator. Other options are irrelevant or syntactically incorrect for standard SQL implementations.
From lesson: CTEs — Common Table Expressions (WITH)
What happens to the data defined in a CTE once the main query completes?
Answer: It is discarded immediately as it exists only for the duration of the query. CTEs are volatile and scope-limited; they vanish as soon as the statement finishes. They are not saved to disk or persistent memory as described in the other choices.
From lesson: CTEs — Common Table Expressions (WITH)
If you have a CTE named 'Sales_CTE', where can you reference it?
Answer: In the FROM clause of the main query or subsequent CTEs. A CTE is scoped to the query it belongs to; it can be referenced in the main query or in later CTE definitions within the same WITH block. It is not global (option 2) and is not restricted to stored procedures (option 3).
From lesson: CTEs — Common Table Expressions (WITH)
A sales manager wants to assign a unique ID to every single order from 1 to N based on the date, regardless of whether multiple orders have the same date. Which function should they use?
Answer: ROW_NUMBER(). ROW_NUMBER() generates a unique, sequential integer for every row, even if there are ties. RANK() and DENSE_RANK() would assign the same number to identical values, failing the requirement for a unique ID per order.
From lesson: Window Functions — ROW_NUMBER, RANK, DENSE_RANK
If your dataset has three products tied for 1st place in sales, how will RANK() and DENSE_RANK() differ in the assignment of the next rank?
Answer: RANK() will give 4th, DENSE_RANK() will give 2nd. RANK() skips numbers (1, 1, 1, 4), so the next is 4th. DENSE_RANK() does not skip (1, 1, 1, 2), so the next is 2nd. The other options misidentify the sequence logic.
From lesson: Window Functions — ROW_NUMBER, RANK, DENSE_RANK
What is the primary reason you must include an ORDER BY clause inside the OVER() function when using these ranking functions?
Answer: To ensure the ranking is deterministic and reproducible. Without ORDER BY, the engine does not know how to rank the rows, making the output non-deterministic. The other options refer to performance or syntax, which are not the primary purpose of the ordering requirement.
From lesson: Window Functions — ROW_NUMBER, RANK, DENSE_RANK
You need to identify the top-performing employee in each department. Which structure is most appropriate?
Answer: PARTITION BY department ORDER BY sales DESC. PARTITION BY department ensures the ranking resets for every department. Ordering by sales DESC brings the best performance to the top. The other options either sort incorrectly or fail to group by department.
From lesson: Window Functions — ROW_NUMBER, RANK, DENSE_RANK
You have a query that calculates a rank for students based on test scores. Why does filtering for 'WHERE rank_col = 1' fail in the same query block?
Answer: Because the order of operations processes WHERE before the window function. SQL processes WHERE clauses before the windowing functions are calculated. Therefore, the engine doesn't recognize the alias created by the window function in the WHERE clause. The other options are incorrect interpretations of SQL lifecycle or function usage.
From lesson: Window Functions — ROW_NUMBER, RANK, DENSE_RANK
You need to compare each employee's salary to their predecessor's salary in the same department. Which clause must be included in your OVER statement to ensure accuracy?
Answer: PARTITION BY department_id ORDER BY hire_date. Partitioning by department isolates the scope, and ordering by hire_date defines what 'predecessor' means. The other options either fail to define the relative order or interfere with the window scope.
From lesson: LAG, LEAD, FIRST_VALUE, LAST_VALUE
You attempt to get the maximum value of a column over the entire partition using LAST_VALUE, but you keep getting the value of the current row. What is the cause?
Answer: The default frame clause limits the window to the current row.. The default window frame is 'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW', which excludes future rows. The other options do not dictate why LAST_VALUE stops at the current row.
From lesson: LAG, LEAD, FIRST_VALUE, LAST_VALUE
If you want to filter for employees whose salary is lower than the person hired immediately before them, where should the LAG function be placed?
Answer: In an outer query that selects from a subquery or CTE where the LAG was calculated.. SQL processes WHERE clauses before window functions. Wrapping in a subquery/CTE allows the engine to calculate the LAG result first, making it available for filtering. The other options cause syntax errors.
From lesson: LAG, LEAD, FIRST_VALUE, LAST_VALUE
What is the primary functional difference between using a self-join to compare adjacent rows versus using LAG/LEAD?
Answer: LAG/LEAD avoids Cartesian product issues and is more readable when maintaining partitioning.. Window functions like LAG/LEAD were specifically designed to avoid the performance and logic complexities of self-joining. Joins don't have built-in partitioning and can easily result in duplicates if keys are not unique.
From lesson: LAG, LEAD, FIRST_VALUE, LAST_VALUE
Consider a query with FIRST_VALUE(salary) OVER (ORDER BY salary). If two employees have the same minimum salary, what will the output look like?
Answer: Both employees will show the same minimum salary.. FIRST_VALUE retrieves the first value in the defined window. If both have the same salary, they are equal in the sort order, so the function returns the value regardless of the duplicate. It does not return NULL or error out.
From lesson: LAG, LEAD, FIRST_VALUE, LAST_VALUE
If you perform a query with 'SUM(sales) OVER(PARTITION BY region)', what is the impact on the number of rows returned?
Answer: The number of rows remains identical to the original table structure.. The correct answer is that the row count remains the same. Unlike GROUP BY, window functions perform calculations without collapsing rows. Option 0 describes GROUP BY behavior, Option 2 describes ROLLUP behavior, and Option 3 is unrelated to the logic of a single aggregation.
From lesson: PARTITION BY with Window Functions
What is the primary difference between a partition and a window frame when using an ORDER BY clause?
Answer: Partition defines the scope of data, while a window frame defines the specific set of rows relative to the current row.. The partition creates the scope (the 'bucket') and the frame defines the sliding window within that bucket. Option 1 is wrong because PARTITION BY is often optional. Option 2 is wrong because frames exist with partitions. Option 3 is incorrect as they serve different purposes.
From lesson: PARTITION BY with Window Functions
You need to find the percentage of a user's total spending compared to their total monthly spending. Why should you use 'PARTITION BY user_id, month'?
Answer: To ensure the denominator for the percentage calculation is isolated to that specific user and month.. By partitioning by both ID and month, the aggregate function treats that specific group as the scope for the calculation. Option 0 is wrong because we are partitioning, not grouping. Option 2 describes DISTINCT. Option 3 describes ORDER BY.
From lesson: PARTITION BY with Window Functions
Why does a query containing 'WHERE rank() OVER(PARTITION BY dept) = 1' fail in SQL?
Answer: Window functions cannot be used in the WHERE clause because they are evaluated after filtering.. SQL executes WHERE clauses before window functions. Therefore, the function cannot be evaluated at that stage. Option 0 is wrong because PARTITION BY works with rank. Option 2 is a true statement about ranking but not the reason for the WHERE clause failure. Option 3 is syntax, which is correct here.
From lesson: PARTITION BY with Window Functions
If you omit the PARTITION BY clause but keep the OVER() clause, what is the default behavior?
Answer: The calculation is performed over the entire dataset as one single partition.. Omitting PARTITION BY treats the entire table as a single group. Option 1 is wrong because it is valid SQL. Option 2 is wrong because the function uses all data, not just the single row. Option 3 is close, but 'aggregate' implies a single value result, whereas window functions return the value per row.
From lesson: PARTITION BY with Window Functions
Which data type is most appropriate for storing a product price that requires exact precision without rounding errors?
Answer: DECIMAL(10,2). DECIMAL ensures fixed-point precision necessary for financial data. FLOAT is approximate and can cause rounding errors; VARCHAR is for text; INTEGER cannot store fractional values.
From lesson: CREATE TABLE and Data Types
When creating a table, what is the primary purpose of defining a column as the PRIMARY KEY?
Answer: To ensure the column contains only unique, non-null values for identifying rows.. A PRIMARY KEY uniquely identifies each record in a table and implicitly enforces NOT NULL. The other options are incorrect because primary keys are not for grouping, don't guarantee physical sort order, and must be unique.
From lesson: CREATE TABLE and Data Types
A user wants to store 'Active' or 'Inactive' status flags for thousands of records. Which data type is the most storage-efficient choice?
Answer: BOOLEAN. BOOLEAN is the most efficient choice as it is designed specifically for binary states. CHAR(8) uses unnecessary space, while VARCHAR and TEXT are overkill for simple binary flags.
From lesson: CREATE TABLE and Data Types
If you need to ensure that a 'UserEmail' column never contains an empty value, which constraint should you include in the CREATE TABLE statement?
Answer: NOT NULL. NOT NULL specifically prevents a record from being inserted with a null value. UNIQUE only prevents duplicate values but allows nulls; DEFAULT provides a value if one is missing; CHECK is for logic validation.
From lesson: CREATE TABLE and Data Types
Why should you avoid using VARCHAR(MAX) for a column intended to store short country codes like 'US' or 'GB'?
Answer: It consumes more memory in the buffer pool and can cause index performance issues.. Variable-length columns with large definitions often force the engine to allocate more overhead, impacting memory and query performance. The other options are false; VARCHAR works fine with short strings and LIKE operators.
From lesson: CREATE TABLE and Data Types
You need to update the email address for a specific user. Which approach best ensures data integrity?
Answer: Use a SELECT query with the unique ID first to confirm the exact row, then execute the UPDATE.. Verifying the target with a SELECT statement is the safest practice. The first and fourth options will overwrite all data, leading to catastrophe. The third is inefficient and risks data loss if the insert fails.
From lesson: INSERT, UPDATE, DELETE
What is the result of executing 'DELETE FROM Employees' without a WHERE clause?
Answer: It deletes all records from the table but keeps the structure.. In standard SQL, a DELETE statement without a WHERE clause acts on all rows in the table. It does not drop the table structure. Option 1 is incorrect because it lacks a filter, and option 4 is incorrect because the statement is syntactically valid.
From lesson: INSERT, UPDATE, DELETE
Which statement correctly adds a new employee named 'John' with ID 101?
Answer: INSERT INTO Employees (Name, ID) VALUES ('John', 101);. Option 3 correctly specifies column names and uses quotes for the string literal. Option 1 assumes column order which is risky. Option 2 fails to quote the string 'John'. Option 4 is an update, not an insertion.
From lesson: INSERT, UPDATE, DELETE
When performing an UPDATE on multiple columns, which syntax is correct?
Answer: UPDATE Table SET Col1 = 'A', Col2 = 'B' WHERE Id = 1;. Standard SQL requires comma separation for multiple columns in a single SET clause. Option 1 follows this. Options 2 and 3 use invalid syntax (AND/semicolon), and Option 4 has the WHERE clause in the wrong sequence.
From lesson: INSERT, UPDATE, DELETE
Why is it important to use a transaction when performing a large DELETE operation?
Answer: It allows you to undo the deletion if you realize you targeted the wrong rows.. Transactions provide atomicity and the ability to rollback. Option 1 is false (transactions often add overhead), Option 3 is false, and Option 4 is logically incorrect as DELETE and UPDATE perform different functions.
From lesson: INSERT, UPDATE, DELETE
Which of the following describes the functional difference between TRUNCATE and DROP TABLE?
Answer: TRUNCATE preserves table structure, while DROP removes the structure entirely.. TRUNCATE removes all rows while keeping the schema intact, whereas DROP removes the definition and the data. The third option is incorrect because both are typically classified as DDL.
From lesson: ALTER TABLE and DROP
When modifying a column using ALTER TABLE, what is the most common reason for a 'null value' error?
Answer: Existing rows contain NULL values, and you are trying to add a NOT NULL constraint without providing a default.. Adding a NOT NULL constraint requires all existing rows to satisfy the condition; without a default, the existing NULLs trigger a constraint violation. The other options refer to metadata conflicts or locking, not row-level data violations.
From lesson: ALTER TABLE and DROP
If you need to rename a column in a SQL table, which approach is considered standard?
Answer: Use ALTER TABLE table_name RENAME COLUMN old_name TO new_name.. The standard SQL syntax for renaming a column is RENAME COLUMN. The other options use invalid syntax or confuse column renaming with data updates.
From lesson: ALTER TABLE and DROP
What happens if you attempt to DROP a table that contains data referenced by a foreign key in another table?
Answer: The database operation fails due to a foreign key constraint violation.. Relational databases enforce integrity; dropping a parent table while children exist violates the constraint. The other options suggest unsafe automated behavior that databases avoid to prevent data corruption.
From lesson: ALTER TABLE and DROP
When altering a column data type from a larger capacity to a smaller one (e.g., VARCHAR(255) to VARCHAR(10)), what is the primary risk?
Answer: Data truncation if existing values exceed the new smaller capacity.. Reducing capacity risks losing data that does not fit in the new constraints. The other options are incorrect as renaming, indexes, and read-only states are not the primary risk of type capacity changes.
From lesson: ALTER TABLE and DROP
What happens if you attempt to insert a duplicate value into a column defined with a UNIQUE constraint that currently contains no NULL values?
Answer: The database rejects the insert and throws an integrity violation error.. A UNIQUE constraint enforces data integrity by preventing identical values in a column. Option 2 is correct because the database engine will block the transaction. Options 0, 1, and 3 are incorrect because SQL constraints are absolute rules that do not permit data modification or warnings to bypass the uniqueness requirement.
From lesson: Constraints — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL
Consider a table 'Orders' with a FOREIGN KEY referencing 'Customers(id)'. What occurs if you try to delete a customer record that still has associated orders?
Answer: The deletion is blocked to prevent orphaned records in the Orders table.. Referential integrity dictates that a child record cannot exist without a valid parent. Option 1 is correct because the database prevents the deletion. Options 0, 2, and 3 are incorrect because cascading is not automatic unless explicitly defined (ON DELETE CASCADE), and the database does not archive or disable constraints on its own.
From lesson: Constraints — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL
Why is it best practice to define a column as NOT NULL even if a UNIQUE constraint exists on it?
Answer: It prevents ambiguity regarding the identity of the records.. A primary goal of database design is ensuring every record is identifiable. Option 2 is correct because NOT NULL forces explicit data entry, avoiding the ambiguity of NULL values. Options 0 and 1 are secondary performance concerns, while option 3 is false as a UNIQUE constraint never automatically becomes a PRIMARY KEY.
From lesson: Constraints — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL
If a table has a composite PRIMARY KEY consisting of (OrderID, ProductID), what does this imply?
Answer: The combination of OrderID and ProductID must be unique for every row.. A composite PRIMARY KEY treats the group of columns as a single identifier. Option 2 is correct because it ensures that the tuple (OrderID, ProductID) is unique. Options 0 and 1 are incorrect because individual columns in a composite key can repeat as long as the pair does not. Option 3 is incorrect as composite keys can be referenced by foreign keys.
From lesson: Constraints — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL
Which scenario best describes the intended use of a FOREIGN KEY constraint?
Answer: To ensure that values in a child table correspond to existing values in a parent table.. The FOREIGN KEY is the primary mechanism for establishing relationships between tables. Option 0 is correct because it links tables through referential integrity. Option 1 relates to naming conventions, option 2 describes arrays (which SQL tables don't use), and option 3 is false because foreign keys facilitate joins rather than replace them.
From lesson: Constraints — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL
A table contains an 'OrderItems' column that stores a comma-separated list of product IDs. Which Normal Form is violated and why?
Answer: 1NF, because values must be atomic.. 1NF requires that all attributes contain atomic (indivisible) values; storing multiple IDs in one cell violates this. The other options are incorrect because those forms deal with dependencies between columns rather than the atomicity of data within a column.
From lesson: Normalization — 1NF, 2NF, 3NF
A table uses (StudentID, CourseID) as a primary key. The attribute 'InstructorOffice' is stored here, but it only depends on 'CourseID'. Which Normal Form is violated?
Answer: 2NF. This violates 2NF because 'InstructorOffice' has a partial dependency on the composite key (it depends on only part of the key). 1NF is satisfied as values are atomic; 3NF is not reached because 2NF is failed; it is not in 3NF because it fails 2NF.
From lesson: Normalization — 1NF, 2NF, 3NF
Consider a table: {OrderID, CustomerID, CustomerZipCode}. If CustomerZipCode is determined by CustomerID, which form does this violate?
Answer: 3NF. This is a transitive dependency (OrderID -> CustomerID -> ZipCode). Since ZipCode is a non-key attribute depending on another non-key attribute (CustomerID), it fails 3NF. It satisfies 1NF (atomic) and 2NF (full key dependency).
From lesson: Normalization — 1NF, 2NF, 3NF
When moving from 2NF to 3NF, what is the primary goal regarding column relationships?
Answer: Eliminating transitive dependencies between non-key attributes.. 3NF specifically addresses the removal of transitive dependencies. The first option is the goal of 1NF; the second is the goal of 2NF; the fourth is the opposite of normalization, which increases redundancy.
From lesson: Normalization — 1NF, 2NF, 3NF
Which of the following scenarios best represents a table that is in 2NF but NOT in 3NF?
Answer: A table where a non-key column depends on another non-key column.. If a non-key column depends on another non-key column, it has a transitive dependency, violating 3NF, but if all non-key columns depend on the full primary key, it remains in 2NF. The other options describe violations of 1NF or 2NF.
From lesson: Normalization — 1NF, 2NF, 3NF
A query performs a full table scan even though an index exists on the 'status' column. Which query is most likely causing this?
Answer: SELECT * FROM orders WHERE status + 1 = 2. Applying math to a column in a WHERE clause makes the index unusable because the database must calculate the value for every row. The other options are SARGable (Search ARGumentable) and allow the engine to perform index seeks.
From lesson: Indexes and Query Performance
Which scenario best justifies the creation of a composite index on (last_name, first_name)?
Answer: Frequent queries filtering by last_name, or both last_name and first_name. Composite indexes work on the leftmost prefix rule. It supports filtering by the first column alone or both, but not the second column alone. Option 1 and 2 violate the order, while option 4 is usually better handled by a single-column index or a primary key.
From lesson: Indexes and Query Performance
How does an excessive number of indexes impact database performance during DML operations?
Answer: It increases the time required to perform INSERT and UPDATE operations. Every index must be updated whenever data changes, which creates overhead. Options 1 is correct; option 2 is false as indexes take space, option 3 is misleading because the optimizer only picks the 'best' index, and option 4 is irrelevant.
From lesson: Indexes and Query Performance
When is a 'Covering Index' most beneficial for query performance?
Answer: When the index contains all columns mentioned in the SELECT, JOIN, and WHERE clauses. A covering index allows the engine to retrieve all requested data directly from the index tree without fetching the actual table row, drastically reducing I/O. Other options are incorrect as they don't describe the benefit of data inclusion.
From lesson: Indexes and Query Performance
Why might an optimizer choose to perform a full table scan instead of using an existing index?
Answer: The query is retrieving a very large percentage of the total table rows. If a query returns a high percentage of rows, the overhead of performing index lookups (B-tree traversal plus heap fetching) is higher than just reading the entire table sequentially. The other choices are generally not primary factors for ignoring an index.
From lesson: Indexes and Query Performance
When querying a standard view, what is the database actually executing?
Answer: The underlying SELECT query defined in the view. Standard views are virtual; option 1 and 3 describe Materialized Views, and option 4 is incorrect because views do not store physical data.
From lesson: Views and Materialized Views
Which scenario best justifies the creation of a Materialized View over a standard view?
Answer: When running complex, resource-heavy aggregations on static historical data. Materialized Views are optimized for read-heavy, complex queries that don't need real-time data, whereas the other options are better served by standard views or direct table access.
From lesson: Views and Materialized Views
Why might a database engine reject an UPDATE statement directed at a view?
Answer: All of the above. Views become 'non-updatable' when the mapping between a row in the view and a row in a base table is ambiguous, which happens in all three listed scenarios.
From lesson: Views and Materialized Views
If you add a new row to a base table, what happens to existing Materialized Views?
Answer: The view remains unchanged until a manual or scheduled refresh is performed. Materialized Views are static snapshots; they are not updated until an explicit refresh command is executed, unlike standard views which are dynamic.
From lesson: Views and Materialized Views
What is the primary security benefit of using a view?
Answer: It allows you to restrict user access to specific rows and columns. Views act as a security layer by exposing only necessary data; they do not provide encryption (option 1), network security (option 3), or protection against code-level injection (option 4).
From lesson: Views and Materialized Views
What is the primary architectural difference between a stored procedure and a user-defined function regarding usage in a query?
Answer: Functions can be called within a SELECT statement, whereas procedures cannot.. Functions are designed for calculations used within queries, so they can appear in the SELECT list; procedures perform actions and cannot be directly invoked as part of a SELECT expression. Option 1 is wrong because functions can return tables. Option 2 is wrong because procedures cannot be used in SELECT. Option 3 is wrong because both are pre-compiled.
From lesson: Stored Procedures and Functions
When should you choose a stored procedure over a function for a specific business requirement?
Answer: When you need to modify multiple tables and manage transactional scope within the routine.. Stored procedures allow for transaction control (COMMIT/ROLLBACK) and multiple DML operations, which are prohibited in functions. Options 0, 2, and 3 describe use cases specifically suited for functions, as they involve querying data rather than modifying it.
From lesson: Stored Procedures and Functions
What happens if a function attempts to modify data in an underlying table?
Answer: The operation is rejected because functions cannot have side effects on the database state.. SQL standards enforce that functions must be deterministic and side-effect-free, meaning they cannot perform INSERT, UPDATE, or DELETE operations. The other options are incorrect because the DBMS prevents the execution entirely rather than allowing it or converting it.
From lesson: Stored Procedures and Functions
Why is it generally recommended to avoid row-by-row processing (cursors) inside stored procedures?
Answer: Set-based operations allow the query optimizer to choose the most efficient execution plan.. Set-based logic allows the optimizer to handle data retrieval efficiently, whereas cursors force serial execution, which ignores the optimization engine. Cursors are not limited to functions (0), do not lock the whole DB (1), and are not primarily constrained by memory (3).
From lesson: Stored Procedures and Functions
If you need a routine to perform a task that requires temporary storage of intermediate results followed by multiple conditional updates, what is the best practice?
Answer: Use a stored procedure to encapsulate the logic and transaction management.. Stored procedures are designed to handle complex business logic, conditional branching, and temporary state management. Functions (0, 3) cannot perform updates, and views (2) are for data presentation, not procedural control flow.
From lesson: Stored Procedures and Functions
If a transaction updates a bank balance but the server loses power before the final commit, what property prevents the partial data from being saved?
Answer: Atomicity. Atomicity ensures that all parts of a transaction succeed or none do. Consistency ensures the database follows rules, Isolation handles concurrency, and Durability guarantees committed data persists. Atomicity is the reason the partial update is rolled back.
From lesson: Transactions and ACID Properties
Which scenario best illustrates the 'Isolation' property?
Answer: Two users modifying the same row, where the database forces one to wait for the other.. Isolation prevents transactions from interfering with each other's intermediate states. Option 1 describes concurrency control. The others relate to Durability, Consistency, and Durability respectively.
From lesson: Transactions and ACID Properties
What is the primary result of setting a transaction isolation level to 'Read Uncommitted'?
Answer: It improves performance by allowing dirty reads where a transaction reads uncommitted changes.. Read Uncommitted prioritizes performance at the cost of correctness by allowing dirty reads. It does not provide total isolation, nor does it guarantee consistency or remove logging requirements.
From lesson: Transactions and ACID Properties
When does the 'Durability' property of a transaction officially start being enforced?
Answer: Immediately upon issuing the COMMIT command.. Durability is only guaranteed once a transaction is committed, ensuring the data is moved to stable storage. It is not enforced during execution, at the end of a block, or by manual administrative flushes.
From lesson: Transactions and ACID Properties
A transaction moves money from Account A to Account B. If the database crashes after the deduction from A but before the addition to B, what prevents the money from disappearing?
Answer: The transaction log is used during recovery to rollback the unfinished state.. The transaction log allows the recovery manager to undo the partial transaction (rollback) to restore the database to a valid state. Consistency defines the target, but the log/Atomicity mechanism performs the recovery.
From lesson: Transactions and ACID Properties
Which of the following best describes the difference between the WHERE and HAVING clauses?
Answer: WHERE filters rows before grouping, while HAVING filters groups after aggregation.. WHERE filters individual rows before any grouping occurs. HAVING acts on the result of a GROUP BY clause to filter aggregate values. Other options incorrectly describe their functional purposes.
From lesson: SQL Interview Questions — Basics
If you want to count every row in a table including those with NULL values in specific columns, which syntax should you use?
Answer: COUNT(*). COUNT(*) counts the total number of rows regardless of NULL values. COUNT(column_name) skips rows where the column is NULL. The other options either require a specific column or perform unnecessary logic.
From lesson: SQL Interview Questions — Basics
What is the result of 'SELECT 10 + NULL' in a standard SQL environment?
Answer: NULL. In SQL, any arithmetic operation performed on a NULL value results in NULL because the missing value prevents a definitive calculation. It does not default to 0 or throw an error.
From lesson: SQL Interview Questions — Basics
When performing a LEFT JOIN, what happens when there is no matching record in the right-hand table?
Answer: The query returns NULL for all columns selected from the right-hand table.. A LEFT JOIN preserves all rows from the left table; if no match is found, the columns from the right table are filled with NULL. It does not exclude the left row nor default to zero.
From lesson: SQL Interview Questions — Basics
Why is the use of 'SELECT *' generally discouraged in production environments?
Answer: It causes the database to return unnecessary data, increasing network traffic and potentially breaking code if the schema changes.. SELECT * fetches every column, which can be inefficient and creates a brittle dependency on the table's exact column order or structure. The other options are false as it is valid, compatible with WHERE, and does not trigger automatic ordering.
From lesson: SQL Interview Questions — Basics
When comparing a NULL value using the standard equals operator in a WHERE clause, what is the result?
Answer: Unknown. In SQL, NULL represents an unknown value. Comparing anything to NULL using '=' results in 'Unknown' (or UNKNOWN), which evaluates to false in a WHERE filter. 'False' and 'Null' are incorrect because they imply a boolean or defined result, while the standard defines this state as Unknown.
From lesson: SQL Interview Questions — Advanced
What is the primary difference between a RANK() and a DENSE_RANK() window function?
Answer: RANK() skips numbers, DENSE_RANK() does not. RANK() leaves gaps in the ranking sequence when ties occur (e.g., 1, 2, 2, 4), whereas DENSE_RANK() continues the sequence without gaps (e.g., 1, 2, 2, 3). Option 1 describes this correctly, while others misidentify the behavior or capabilities.
From lesson: SQL Interview Questions — Advanced
How does a Common Table Expression (CTE) differ from a subquery?
Answer: CTEs can be referenced multiple times within a single statement. The key advantage of a CTE is its ability to be referenced multiple times within the same query, improving readability and modularity. CTEs are not inherently faster, subqueries can be joined, and CTEs are typically not materialized unless specified by the engine.
From lesson: SQL Interview Questions — Advanced
Which of the following describes the execution order of a SQL query involving GROUP BY?
Answer: FROM -> WHERE -> GROUP BY -> SELECT. SQL logically processes the FROM clause first, then filters via WHERE, groups rows with GROUP BY, and finally projects the columns via SELECT. Other options reverse or mix these steps, which is logically impossible for the engine.
From lesson: SQL Interview Questions — Advanced
When using an INNER JOIN, what happens to rows in either table that do not have a match in the other?
Answer: They are excluded from the result set. INNER JOIN only returns rows where there is a match in both participating tables. NULL padding only occurs in OUTER JOINs. No error is thrown, and no flags are applied; the rows simply do not meet the join criteria.
From lesson: SQL Interview Questions — Advanced
Which approach is most efficient for finding the 'top 3' sales per department?
Answer: A window function using DENSE_RANK() partitioned by department. Window functions are purpose-built for ranking within partitions. Self-joins perform poorly on large datasets, UNION ALL is unmaintainable, and correlated subqueries trigger row-by-row processing, which is slow.
From lesson: SQL Coding Challenges — Top-N, Gaps, Deduplication
To identify gaps in a sequence of numbers, which function is the most effective for comparing a row with its successor?
Answer: LEAD(). LEAD() accesses data from the following row, allowing you to compare current and next values. LAG() looks backwards, RANK() ranks rows, and NTILE() buckets data, none of which directly simplify gap analysis as well as LEAD().
From lesson: SQL Coding Challenges — Top-N, Gaps, Deduplication
When deleting duplicate rows based on a specific column, why is a CTE with ROW_NUMBER() preferred over DISTINCT?
Answer: DISTINCT removes the entire row, whereas ROW_NUMBER() allows you to target one record to keep while removing others. DISTINCT filters unique result sets but cannot pinpoint specific duplicates for deletion. ROW_NUMBER() assigns a unique identifier to duplicates, allowing you to isolate one record to keep (e.g., where row_num = 1) and delete the rest.
From lesson: SQL Coding Challenges — Top-N, Gaps, Deduplication
What is the primary risk of using 'ID = ID + 1' to find gaps in a sequence?
Answer: It fails if the sequence has multiple missing numbers in a row. Checking ID+1 only identifies immediate next-neighbor gaps. If two or more consecutive IDs are missing, this logic fails to identify the magnitude of the gap, whereas set-based comparison via LEAD() is more robust.
From lesson: SQL Coding Challenges — Top-N, Gaps, Deduplication
When ranking items with tied values, what is the functional difference between ROW_NUMBER() and DENSE_RANK()?
Answer: ROW_NUMBER() leaves gaps; DENSE_RANK() generates consecutive integers. ROW_NUMBER() provides unique sequential integers even for ties. DENSE_RANK() assigns the same rank to ties but does not skip the next rank, making it 'dense' compared to standard RANK(), which does skip ranks after ties.
From lesson: SQL Coding Challenges — Top-N, Gaps, Deduplication
An application requires complex reporting and joins across five distinct entities. Which data model is most appropriate?
Answer: A relational database. The relational model is designed for set-based operations and joins; the others struggle with multi-entity joins, which would require expensive application-level processing.
From lesson: NoSQL vs SQL — Key Differences
What is the primary trade-off when selecting a strictly relational database versus a non-relational document store?
Answer: ACID compliance vs flexible horizontal scalability. Relational systems prioritize ACID (atomicity, consistency, isolation, durability) and referential integrity, while many NoSQL systems sacrifice immediate consistency for easier horizontal scaling.
From lesson: NoSQL vs SQL — Key Differences
In which scenario would a relational database architecture be considered superior to a document-based NoSQL architecture?
Answer: When transactional integrity across multiple tables is critical. Relational databases provide Foreign Key constraints and transactions that maintain integrity across multiple tables; document stores are poor at cross-document consistency.
From lesson: NoSQL vs SQL — Key Differences
A system requires a single consistent view of financial account balances across the entire cluster at all times. What is the limitation of many distributed NoSQL designs?
Answer: They often use eventual consistency, which may show stale balances. Many distributed non-relational systems prioritize availability over immediate consistency, whereas relational databases are built for strict consistency in financial operations.
From lesson: NoSQL vs SQL — Key Differences
Why might a developer choose to use a JSON-supporting relational database rather than a traditional document-only store?
Answer: To combine structured ACID-compliant data with unstructured dynamic attributes. Relational databases with JSON support allow for structured schemas to handle primary entities while using flexible fields for dynamic attributes, maintaining the best of both worlds.
From lesson: NoSQL vs SQL — Key Differences
