Question 1

SQL pattern matching: Choose the accurate statement. “In SQL-92, the wildcard asterisk (*) represents any sequence of characters.”

Accepted Answer

Introduction / Context: Pattern matching in standard SQL uses the LIKE operator with specific wildcard symbols. Confusing these symbols (especially across tools and shells) leads to wrong results and puzzling bugs. This question checks whether you know the standard wildcard for “any sequence of characters.” Given Data / Assumptions: ANSI SQL-92 semantics for LIKE are assumed. We are not discussing filesystem globbing or vendor-specific regex functions. Simple character and pattern comparisons with LIKE are in scope. Concept / Approach: In SQL-92, LIKE uses the percent sign % to match any sequence of zero or more characters and the underscore _ to match exactly one character. The asterisk * is not the standard wildcard in LIKE. Some tools (for example, legacy Access modes or GUI filters) may accept * as a convenience, but this is not the ANSI standard. Conflating shell globbing (where * matches any sequence) with SQL LIKE is a frequent misconception. Step-by-Step Solution: Recall the rules: % = any sequence; _ = single character.Construct an example: WHERE name LIKE 'Jo%' matches John, Jose, Jo.Test with : WHERE name LIKE 'Jo' will not behave as intended under ANSI SQL (it may error or treat * literally).Therefore, the statement that * is the SQL-92 wildcard for sequences is false. Verification / Alternative check: Vendor documentation (PostgreSQL, SQL Server, MySQL in SQL mode) confirms % and _ are the LIKE wildcards; * is used in SELECT * to mean “all columns,” not for pattern matching. Why Other Options Are Wrong: “Correct” contradicts the standard. “Correct only in MS Access” is out of scope for SQL-92 and still not the standard. “Applies only to REGEXP” mixes regex syntax with LIKE semantics. “Depends on collation” affects comparison rules, not wildcard characters. Common Pitfalls: Using filesystem instincts in SQL; forgetting to escape % or _ when matching them literally (via ESCAPE clause). Final Answer: Incorrect

Question 2

Indexing fundamentals: Assess this claim. “Indexes can usually be created for both primary keys and secondary (non-primary) keys.”

Accepted Answer

Introduction / Context: Indexes speed up data retrieval by creating auxiliary structures that map key values to table rows. Most relational database management systems (RDBMS) support indexes on primary keys as well as on secondary (non-primary, non-unique) columns. This question checks your understanding of what columns may be indexed. Given Data / Assumptions: Typical relational systems (PostgreSQL, SQL Server, Oracle, MySQL/InnoDB) are assumed. Primary keys are unique identifiers and are typically indexed automatically. Secondary columns may also be indexed to improve query performance. Concept / Approach: Primary keys get index support automatically (unique B-tree, hash, or other structures depending on the engine). Secondary keys—such as foreign keys, frequently searched attributes, or sort/group columns—can also have indexes created to reduce scan costs. While details like clustered vs. nonclustered, bitmap vs. B-tree, and partial indexes vary by vendor, the general principle that both primary and secondary keys may be indexed is universally true. Step-by-Step Solution: Recognize that PRIMARY KEY implies a unique index or equivalent constraint support.Determine workload columns (JOIN predicates, WHERE filters, ORDER BY/GROUP BY keys).Create appropriate secondary indexes to support those access paths.Validate with EXPLAIN/EXPLAIN ANALYZE to confirm index usage. Verification / Alternative check: Query plans typically show index seeks/scans on both primary and secondary indexes when predicates match indexed columns. Why Other Options Are Wrong: Limiting to clustered or unique-only scenarios is inaccurate; non-unique secondary indexes are common. Partitioning is orthogonal; indexes exist with or without partitioning. Common Pitfalls: Over-indexing, redundant indexes, or ignoring write overhead; failing to include leading columns that match query predicates; misunderstanding composite index ordering. Final Answer: Correct

Question 3

Aggregate results: Clarify the definition. “Scalar aggregates are multiple values returned from an SQL query that includes an aggregate function.”

Accepted Answer

Introduction / Context: Aggregates compress sets of rows into summarized results. Understanding the difference between scalar aggregates and grouped aggregates is essential for reading query plans and designing reports. This item misstates the definition of a scalar aggregate. Given Data / Assumptions: A scalar aggregate query uses aggregate functions without a GROUP BY clause. Grouped aggregates use GROUP BY to produce one result per group. Common aggregates: COUNT(), SUM(), AVG(), MIN(), MAX(). Concept / Approach: A scalar aggregate returns exactly one row with one or more aggregate expressions summarizing the entire input set (for example, SELECT COUNT() FROM T;). In contrast, a grouped aggregate returns one result per group (for example, SELECT dept, COUNT() FROM T GROUP BY dept;). Therefore, calling a scalar aggregate “multiple values” is incorrect—scalar aggregates yield a single result set row (though that row can contain multiple aggregate columns). Step-by-Step Solution: Write a scalar aggregate: SELECT COUNT() FROM Sales; → one row, one column.Add more aggregates: SELECT COUNT(), SUM(amount) FROM Sales; → still one row, multiple columns, but a single scalar aggregate result set.Introduce grouping: SELECT region, SUM(amount) FROM Sales GROUP BY region; → multiple rows, one per region (not scalar).Conclude the statement is incorrect. Verification / Alternative check: Running the examples shows the row counts: scalar = 1 row; grouped = many rows. Why Other Options Are Wrong: “Correct” reverses the definition. Linking correctness to GROUP BY, windowing, or isolation is irrelevant to the scalar vs. grouped distinction. Common Pitfalls: Confusing “one row per query” with “one column”—a scalar aggregate can include multiple aggregate columns but still returns a single row. Final Answer: Incorrect

Question 4

SQL command categories: Decide whether the statement is accurate. “Data Manipulation Language (DML) commands are used to define a database, including creating, altering, and dropping tables and establishing constraints.”

Accepted Answer

Introduction / Context: SQL groups statements into categories with different purposes. Confusing these categories can lead to permission errors and incorrect assumptions about transactional behavior. This question examines whether DML is responsible for defining database structures. Given Data / Assumptions: DML includes SELECT, INSERT, UPDATE, DELETE, and MERGE (data access/modification). DDL (Data Definition Language) includes CREATE, ALTER, DROP, and constraint definitions. We assume SQL-92 or later with typical vendor extensions. Concept / Approach: DML manipulates the contents of existing tables and views—reading and changing rows. DDL defines or changes schema objects—creating tables, indexes, views, constraints, and altering or dropping them. Therefore, the statement that DML is used to “define” a database is incorrect: defining is DDL’s job. Some databases allow DDL within transactions (with varying behaviors), but that does not change the category definitions. Step-by-Step Solution: Classify CREATE TABLE, ALTER TABLE, DROP TABLE → DDL.Classify SELECT, INSERT, UPDATE, DELETE → DML.Map “establishing constraints” (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) → DDL.Conclude the claim is false. Verification / Alternative check: Check role privileges: DDL usually requires higher privileges than DML; DBAs often separate these responsibilities. Why Other Options Are Wrong: “Correct” mislabels categories. References to NoSQL, stored procedures, or autocommit do not change SQL taxonomy. Common Pitfalls: Assuming GRANT/REVOKE for DDL equals DML privileges; expecting DDL to be fully transactional in engines that auto-commit DDL. Final Answer: Incorrect

Question 5

Using DISTINCT and ALL: Assess the limitation. “DISTINCT and its counterpart ALL can be used more than once in a single SELECT statement.”

Accepted Answer

Introduction / Context: DISTINCT removes duplicate rows from a SELECT result, while ALL (the default) retains all rows. Understanding where and how often DISTINCT/ALL can appear prevents syntax errors and unintended results. This question tests whether you can apply DISTINCT multiple times in one SELECT clause. Given Data / Assumptions: ANSI SQL behavior is assumed. DISTINCT or ALL applies to the entire SELECT list at the query block level. We are not discussing set operators like UNION, INTERSECT, EXCEPT which have their own DISTINCT/ALL options. Concept / Approach: Within a single SELECT query block, DISTINCT (or ALL) can be specified at most once and it applies to the whole projection list taken together. You cannot use DISTINCT per column or repeat it multiple times. You may, however, use DISTINCT in separate query blocks (for example, subqueries) or specify DISTINCT/ALL for set operators such as UNION DISTINCT or UNION ALL independently of the inner SELECT’s DISTINCT. Step-by-Step Solution: Consider SELECT DISTINCT col1, col2 FROM T; → duplicates across (col1, col2) pairs are removed once.Trying SELECT DISTINCT DISTINCT col1 FROM T; → invalid syntax.Trying column-level DISTINCT (for example, DISTINCT col1, DISTINCT col2) → not supported in ANSI SQL.Set operators: (SELECT ...) UNION ALL (SELECT DISTINCT ...) → each block may choose its own DISTINCT/ALL once. Verification / Alternative check: Vendor references show DISTINCT as a single keyword per query block; repeating it triggers a parse error. Why Other Options Are Wrong: “Correct” conflicts with the grammar. “Correct only with UNION” is misleading: UNION itself can be ALL or DISTINCT, but not multiple times per block. “Per column” support does not exist in standard SQL. Optimizer behavior does not change syntax rules. Common Pitfalls: Misusing DISTINCT to mask joins that duplicate rows; placing DISTINCT where GROUP BY is more appropriate. Final Answer: Incorrect

Question 6

Purpose of SELECT DISTINCT: Choose the correct interpretation. “SELECT DISTINCT is used when a user wishes to see duplicate rows in a query result.”

Accepted Answer

Introduction / Context: SELECT DISTINCT is commonly used to eliminate duplicate rows in result sets. Misunderstanding this leads to the opposite of the desired output. This question checks whether you know the exact role of DISTINCT. Given Data / Assumptions: Standard SQL behavior is assumed. Duplicates are determined across the entire SELECT list. NULLs compare as equal for DISTINCT evaluation in most SQL engines for the purpose of deduplication. Concept / Approach: SELECT DISTINCT applies a set operation over the projected columns, removing duplicate rows and returning only unique combinations. If the goal is to see duplicates, one would use GROUP BY with HAVING COUNT() > 1 or window functions (for example, COUNT() OVER(PARTITION BY ...)) to identify duplicates, not DISTINCT. Therefore, the statement claiming DISTINCT shows duplicates is the opposite of reality. Step-by-Step Solution: To remove duplicates: SELECT DISTINCT col1, col2 FROM T.To find duplicates: SELECT col1, COUNT() FROM T GROUP BY col1 HAVING COUNT() > 1.To mark duplicates without filtering: SELECT col1, COUNT(*) OVER(PARTITION BY col1) AS dup_cnt FROM T.Conclusion: DISTINCT removes, not reveals, duplicates. Verification / Alternative check: Run sample data with repeated values; DISTINCT returns unique rows, while GROUP BY/HAVING reveals duplicates. Why Other Options Are Wrong: “Correct” is the inverse of the truth. GROUP BY or ORDER BY does not change the fundamental meaning of DISTINCT. NULL semantics affect grouping/uniqueness but do not invert DISTINCT’s purpose. Common Pitfalls: Using DISTINCT to “fix” unintended duplicates created by joins instead of correcting the join logic; assuming DISTINCT is per-column. Final Answer: Incorrect

Question 7

COUNT with a non-grouped column: Analyze the statement. “The SQL: SELECT Name, COUNT() FROM NAME_TABLE; counts the number of name rows and displays this total in a table with a single row and a single column.”

Accepted Answer

Introduction / Context: Mixing aggregated and non-aggregated columns in a SELECT list requires a GROUP BY clause in standard SQL. Without GROUP BY, only aggregate expressions are allowed (or all selected columns must be functionally dependent according to vendor-specific extensions). This question examines what happens when Name appears alongside COUNT() without GROUP BY. Given Data / Assumptions: ANSI SQL-92 behavior is assumed. NAME_TABLE contains a column Name and multiple rows. No vendor-specific “only_full_group_by” relaxations are assumed. Concept / Approach: The query SELECT Name, COUNT() FROM NAME_TABLE; is invalid in standard SQL because Name is neither aggregated nor grouped. Most engines will raise a syntax or semantic error (for example, “column must appear in the GROUP BY clause or be used in an aggregate function”). Furthermore, even if a vendor allowed it with non-standard behavior, the result would not be “a single row and a single column”—the SELECT list clearly has two columns. To correctly count all rows, use SELECT COUNT() FROM NAME_TABLE; To count per name, use SELECT Name, COUNT() FROM NAME_TABLE GROUP BY Name; Step-by-Step Solution: Goal: total rows → SELECT COUNT() FROM NAME_TABLE; → one row, one column.Goal: count per Name → SELECT Name, COUNT(*) FROM NAME_TABLE GROUP BY Name; → multiple rows, two columns.Therefore the original statement is incorrect on both validity and shape of the result set.Fix with appropriate GROUP BY or remove the non-aggregated column. Verification / Alternative check: Test in PostgreSQL or SQL Server—engine rejects the non-grouped, non-aggregated column alongside an aggregate. Why Other Options Are Wrong: “Correct” misstates the rules and the output shape. Uniqueness of Name does not remove the need for GROUP BY in standard SQL. “Autogrouping” is not part of the standard and is rarely supported. Common Pitfalls: Forgetting GROUP BY when mixing aggregate and non-aggregate columns; misreading result shapes. Final Answer: Incorrect

Question 8

Removing duplicate rows: Evaluate the necessity. “The qualifier DISTINCT must be used in an SQL statement when we want to eliminate duplicate rows.”

Accepted Answer

Introduction / Context: SQL provides multiple mechanisms to eliminate duplicates depending on the goal—either to return unique values in a query result or to physically deduplicate stored data. This question asks if DISTINCT is strictly required whenever we want to eliminate duplicates. Given Data / Assumptions: We are talking about result sets, not necessarily physical table de-duplication. GROUP BY and DISTINCT both can produce unique sets in a projection. Window functions can help identify duplicates without removing them automatically. Concept / Approach: DISTINCT is a straightforward way to remove duplicate rows in the output projection. However, GROUP BY can also collapse rows to a unique set by grouping on the same columns and optionally including aggregate expressions. For physical de-duplication in a table, constraints (for example, UNIQUE) or staging logic may be used. Therefore, the statement that DISTINCT “must” be used is too strong—DISTINCT is sufficient but not necessary. Step-by-Step Solution: To get unique rows: SELECT DISTINCT col1, col2 FROM T;Alternative: SELECT col1, col2 FROM T GROUP BY col1, col2;To find duplicates: SELECT col1, COUNT() FROM T GROUP BY col1 HAVING COUNT() > 1;To prevent duplicates in storage: ALTER TABLE T ADD CONSTRAINT u UNIQUE(col1, col2); Verification / Alternative check: Run both DISTINCT and GROUP BY versions; results match for pure deduplication without aggregations. Why Other Options Are Wrong: “Correct” ignores the valid GROUP BY alternative. ORDER BY, views, or indexes do not make DISTINCT mandatory. Common Pitfalls: Using DISTINCT to hide bad joins; forgetting that GROUP BY also enforces uniqueness in the output; assuming indexes automatically remove duplicates in results (they do not). Final Answer: Incorrect

Question 9

Subqueries vs. joins: Judge the generality of this claim. “There is an equivalent join expression that can be substituted for all subquery expressions.”

Accepted Answer

Introduction / Context: SQL supports both subqueries (nested SELECT statements) and joins to combine and filter data. In many cases, the optimizer can transform one into the other. However, believing that every subquery has a join-equivalent is an overgeneralization. This question probes that nuance. Given Data / Assumptions: Consider both correlated and non-correlated subqueries. Consider predicates involving EXISTS, NOT EXISTS, IN, NOT IN, and scalar subqueries. Standard SQL null-handling applies. Concept / Approach: Many non-correlated IN/EXISTS subqueries can be rewritten as joins (for example, semi-joins) to achieve equivalent results. However, NOT IN and NOT EXISTS with NULL-sensitive semantics, as well as certain correlated scalar subqueries or complex conditional logic, may not map cleanly to straightforward inner/outer joins without changing semantics—especially in the presence of NULLs or when duplicates matter. Even when a theoretical relational algebra equivalence exists (for example, anti-join vs. NOT EXISTS), rewriting requires care to preserve behavior and performance, and some constructs are naturally clearer as subqueries. Step-by-Step Solution: Identify subquery type: scalar, IN/EXISTS, correlated vs. non-correlated.Check for NULL-sensitive logic: NOT IN behaves differently if the subquery can return NULL.Attempt rewrite: EXISTS → semi-join; NOT EXISTS → anti-join; scalar subquery → join plus aggregation with care.Conclude: not all subqueries have a simple, semantics-preserving join equivalent in every context. Verification / Alternative check: Test tricky cases with NULLs and duplicates; results can diverge between a naive join rewrite and the original subquery behavior. Why Other Options Are Wrong: “Correct” is too absolute. “Only for non-correlated” is closer but still not universal; edge cases exist. Null handling and optimizer choices do not guarantee join equivalence. Common Pitfalls: Rewriting NOT IN to a left join filter without handling NULLs; changing cardinalities by accidental duplication during joins; losing performance despite semantic equivalence. Final Answer: Incorrect

Question 10

In SQL database practice, is there a standard ADD command used to insert one row or to add multiple rows from a query result, or is row insertion performed by another statement (such as INSERT and INSERT ... SELECT)?

Accepted Answer

Introduction / Context: SQL provides well-defined statements for data manipulation. This item tests whether the statement “ADD command inserts one row or many rows from a query” reflects standard SQL terminology and behavior across common database systems. Given Data / Assumptions: The claim is that an ADD command inserts rows. We consider mainstream SQL implementations (PostgreSQL, MySQL, SQL Server, Oracle). We focus on row insertion versus schema changes. Concept / Approach: The SQL statement for inserting data is INSERT. It supports inserting a single row (INSERT INTO ... VALUES (...)) or multiple rows either via multiple VALUES clauses or by selecting from another result set (INSERT INTO ... SELECT ...). The keyword ADD is not a DML verb for rows; rather, ADD typically appears in ALTER TABLE to add a column, constraint, or index, depending on the DBMS dialect. Step-by-Step Solution: Identify the required action: insert data rows.Recall standard SQL: INSERT is the correct statement for row insertion.Check multi-row support: INSERT ... SELECT and multi-VALUES provide bulk insertion.Map ADD correctly: used in ALTER TABLE contexts, not for inserting rows. Verification / Alternative check: Try example patterns: INSERT INTO t(col1,col2) VALUES (1, 'a');INSERT INTO t(col1,col2) SELECT colA,colB FROM u; Both are valid; there is no “ADD INTO” for rows in standard SQL. Why Other Options Are Wrong: “Correct” would endorse a non-standard statement. “Vendor-specific” is misleading; major DBMSs do not use ADD to insert rows. “Valid only inside stored procedures” is irrelevant; INSERT works both inside and outside procedures. “Applies only to adding columns” is a partial truth about ADD, but the prompt is about inserting rows. Common Pitfalls: Confusing DML (INSERT) with DDL ALTER TABLE ... ADD. Remember that adding rows uses INSERT; adding columns or constraints may use ADD. Final Answer: Incorrect

Question 11

Result sets in SQL: is the output of every SELECT query a tabular relation (a table-like result set) that can be further queried?

Accepted Answer

Introduction / Context: The relational model views query outputs as relations (tables). SQL adopts this notion with “result sets.” This question checks whether you understand that regardless of complexity—joins, aggregates, subqueries—the SELECT statement returns a table-like structure. Given Data / Assumptions: We consider standard SELECT queries. We include queries with joins, expressions, aggregates, and subqueries. We exclude procedural constructs that print messages or raise errors. Concept / Approach: Every SELECT produces a rectangular result: zero or more rows and one or more columns, each column having a defined type. Even SELECT without FROM (in some DBMSs) returns a single-row, single-column table. ORDER BY can change presentation order, but the underlying structure remains a table. This property enables composability: nesting a SELECT inside another or materializing it as a view. Step-by-Step Solution: Recognize SELECT’s semantics: it projects columns and filters/joins rows.Understand that the output is a set (or multiset) of rows with named columns.Note that the result can be aliased in a subquery or view and queried again.UI presentation (grid, CSV) does not change the logical tabular nature. Verification / Alternative check: Wrap any SELECT as a derived table: SELECT * FROM (SELECT 1 AS n) AS q; The inner SELECT is a table expression, proving tabular output. Why Other Options Are Wrong: Restricting correctness to single-table queries is incorrect—joins still return a table. Client tools may format results, but SQL semantics define them as tables. ORDER BY is not required to obtain a table; it only orders rows. Common Pitfalls: Confusing scalar functions with queries; while functions can return a value, a SELECT’s output is a rowset even if it contains one row and one column. Final Answer: Correct

Question 12

Sorting query results: can SQL ORDER BY sort by multiple columns (primary key, tie-breakers), or is it limited to only one column?

Accepted Answer

Introduction / Context: Sorting is fundamental in reporting and pagination. This question evaluates whether ORDER BY can handle multiple sort keys (e.g., last_name, first_name) or if it is limited to a single column. Given Data / Assumptions: Standard SQL ORDER BY syntax is available. Columns may be named or referenced by ordinal or expression. We can specify ASC/DESC independently per key. Concept / Approach: SQL ORDER BY supports a comma-separated list of expressions, each with its own direction. For instance, ... ORDER BY last_name ASC, first_name ASC; sorts primarily by last_name and uses first_name to break ties. This generalizes to any number of keys, including expressions (e.g., ORDER BY COALESCE(priority, 999), created_at DESC). Step-by-Step Solution: Identify the need for stable tie-breaking across rows with equal primary sort values.Use multiple ORDER BY items separated by commas.Assign directions (ASC/DESC) per item as required.Test with sample data to verify expected hierarchy of ordering. Verification / Alternative check: Run an example and compare: ORDER BY department, salary DESC clearly sorts first by department, then within each department by salary descending. Why Other Options Are Wrong: “Correct” conflicts with the multi-key capability of ORDER BY. Limiting to numeric columns is false; text, dates, and expressions are allowed. ORDER BY does not depend on indexes to function, though indexes can affect performance. SORTING does not require GROUP BY. Common Pitfalls: Relying on implicit sort order or assuming rows are returned in insertion order. Always specify ORDER BY explicitly for deterministic presentation. Final Answer: Incorrect

Question 13

Understanding GROUP BY: does the SQL GROUP BY clause group together rows that share the same value(s) for the specified column(s) so that aggregations apply per group?

Accepted Answer

Introduction / Context: GROUP BY is essential for reporting and analytics—summing sales per region, counting orders per customer, and so on. This item asks whether GROUP BY collects rows sharing equal values into groups for aggregation. Given Data / Assumptions: Aggregates like SUM, COUNT, AVG, MIN, MAX operate per group. Non-aggregated selected columns must appear in GROUP BY (standard SQL). Multiple columns or expressions may define a group key. Concept / Approach: GROUP BY partitions the result set by the specified key(s). Each partition receives aggregate calculations. For example: SELECT region, SUM(amount) FROM sales GROUP BY region; computes totals per region. With multiple keys: GROUP BY region, year creates groups for each unique combination of region and year. Step-by-Step Solution: Select the grouping attributes needed to answer the business question.Place those attributes in GROUP BY.Apply aggregates to measure per-group metrics.Optionally filter groups using HAVING (after grouping). Verification / Alternative check: Compare row counts before and after grouping; unique combinations of GROUP BY keys should equal the number of groups returned. Why Other Options Are Wrong: “Incorrect” contradicts the definition. Limiting to COUNT is wrong—any aggregate works. GROUP BY supports multiple keys and does not require ORDER BY to function (though ORDER BY can sort the grouped output). Common Pitfalls: Selecting non-aggregated columns not listed in GROUP BY (violates standard SQL), and confusing HAVING (post-group filter) with WHERE (pre-group filter). Final Answer: Correct

Question 14

Can ORDER BY be used together with SELECT to control the presentation order of the rows returned by a query?

Accepted Answer

Introduction / Context: SQL does not guarantee row order unless ORDER BY is specified. This question verifies that ORDER BY is the standard mechanism to sort SELECT output in ascending or descending order by one or more expressions. Given Data / Assumptions: We are executing a SELECT statement. We want a deterministic presentation order. We can specify ASC or DESC per sort key. Concept / Approach: ORDER BY appends to SELECT to sort rows by column(s) or expressions. Without it, the DBMS may return rows in any order that satisfies the query plan. ORDER BY is independent of indexes, GROUP BY, or data types; it is a logical directive that the optimizer will implement, possibly via sorting or index scans. Step-by-Step Solution: Write the SELECT to produce the desired columns and filters.Add ORDER BY with one or more sort expressions.Choose ASC or DESC per expression.Test to confirm stable ordering across runs. Verification / Alternative check: Compare results with and without ORDER BY; only the ORDER BY version ensures deterministic order, which is critical for pagination and consistent reporting. Why Other Options Are Wrong: “Incorrect” ignores basic SQL behavior. Indexes may improve performance but are not required. GROUP BY is not a prerequisite. ORDER BY works for any sortable data type, not just numbers. Common Pitfalls: Assuming “natural” order equals insertion order—this is unreliable. Always use ORDER BY for predictable presentation. Final Answer: Correct

Question 15

Indexes and storage/maintenance: does each index consume extra disk space and incur overhead during INSERT/UPDATE/DELETE operations when the indexed value changes?

Accepted Answer

Introduction / Context: Indexes accelerate read queries but are not free. This item tests whether you know that indexes require extra storage and must be maintained whenever indexed columns change, affecting write performance. Given Data / Assumptions: An index is a separate data structure (e.g., B-tree, hash). Write operations may touch both the base table and relevant indexes. Storage and performance costs matter in design. Concept / Approach: Creating an index materializes additional metadata to speed lookups. Each index consumes disk (and memory when cached). When rows are inserted, updated, or deleted, index entries must be created, modified, or removed, adding CPU and I/O work. Clustered and nonclustered indexes differ in structure, but both have maintenance overhead. Thus, indexing strategy balances read performance against write costs and storage. Step-by-Step Solution: Estimate query patterns to justify indexes.Consider cardinality and selectivity for index usefulness.Account for write amplification when indexing frequently updated columns.Monitor fragmentation and rebuild/reorganize as needed. Verification / Alternative check: Measure transaction latency before and after index creation on a hot column; write latency typically increases due to index maintenance. Why Other Options Are Wrong: Limiting overhead to clustered or primary indexes ignores maintenance on all index types. Storage medium (SSD vs. HDD) affects speed, not the logical requirement to maintain indexes. “Incorrect” denies the fundamental trade-off of indexing. Common Pitfalls: Over-indexing tables and indexing low-selectivity columns (e.g., boolean flags), which adds cost without benefit. Periodically review unused indexes. Final Answer: Correct

Question 16

Specifying ranges in SQL predicates: can you establish a range using comparison operators like < and > (and <=, >=), or must a special operator be used?

Accepted Answer

Introduction / Context: Filtering by ranges is common: date windows, price bands, score thresholds. This question checks whether standard comparison operators suffice, or a special operator is mandatory. Given Data / Assumptions: We can use operators <, <=, >, >=, BETWEEN. Data types include numbers, dates/timestamps, and text (for lexicographic ranges). SQL predicate logic supports conjunctions with AND. Concept / Approach: Ranges can be expressed directly: WHERE amount >= 100 AND amount < 200 or using BETWEEN (inclusive on both ends): WHERE amount BETWEEN 100 AND 200. Both approaches are valid; the choice depends on inclusivity needs and readability. These operators also work for temporal types (e.g., order_date >= DATE '2025-01-01' AND order_date < DATE '2025-02-01'). Step-by-Step Solution: Determine boundary inclusivity requirements.Use <, <=, >, >= combined with AND to form the range.Alternatively, use BETWEEN when inclusive bounds are desired.Test edge values to confirm correct inclusion/exclusion. Verification / Alternative check: Compare results of BETWEEN with explicit comparisons for off-by-one or end-of-day timestamp concerns. For timestamps, half-open intervals (>= start AND < next_start) are often safest. Why Other Options Are Wrong: Saying only BETWEEN can define ranges is false; comparison operators suffice. Window functions handle analytic partitions, not simple filtering ranges. Ranges work for numerics, dates, and even text using collation order. Common Pitfalls: Mishandling inclusivity, especially with timestamps—use half-open intervals to avoid missing late-night rows. Final Answer: Correct

Question 17

Column aliases and readability: does SQL provide the AS keyword to assign meaningful output names to expressions and built-in function results in a SELECT list?

Accepted Answer

Introduction / Context: Readable column headers help consumers of query results and downstream tools. This question asks whether SQL supports assigning aliases (via AS) to expressions, including built-in function outputs. Given Data / Assumptions: We are writing a SELECT with functions or expressions. We want friendly names for result columns. We consider standard SQL behavior. Concept / Approach: Aliases rename the output column: SELECT COUNT() AS order_count, UPPER(customer_name) AS customer_name_upper FROM ...; The AS keyword is optional in several DBMSs (you can write COUNT() order_count), but AS improves clarity. Aliases are also useful inside subqueries and common table expressions, where outer queries reference the alias. Step-by-Step Solution: Identify any computed columns or function outputs in the SELECT list.Add AS alias_name to each expression for readability.If using the result in a parent query, reference the alias.For names with spaces or reserved words, use quoted identifiers as required by the DBMS. Verification / Alternative check: Run the query and inspect column headers in the result grid—aliases should appear as defined, aiding users and BI tools. Why Other Options Are Wrong: Aliases are not limited to views or aggregates. Quoted identifiers are needed only if the alias contains spaces/special characters; otherwise quoting is optional. “Incorrect” ignores standard SELECT aliasing. Common Pitfalls: Forgetting that some DBMSs treat unquoted aliases as folded to lower/upper case depending on settings. Be consistent, especially when referencing aliases in ORDER BY. Final Answer: Correct

Question 18

WHERE clause expressiveness: can a WHERE condition reference multiple values/columns and combine predicates (using AND/OR/IN), or is it restricted to a single value?

Accepted Answer

Introduction / Context: The WHERE clause filters rows before projection and grouping. This question checks whether WHERE can express complex conditions or is limited to a single value test. Given Data / Assumptions: Standard SQL boolean operators are available: AND, OR, NOT. Set membership and subqueries can be used (IN, EXISTS). Predicates may involve multiple columns and expressions. Concept / Approach: WHERE is fully expressive for row filtering: WHERE (status IN ('NEW','PENDING')) AND (amount > 100) AND (region = 'EMEA'). It can compare column-to-column, column-to-expression, and even include scalar subqueries. It is not restricted to a single value comparison. WHERE applies before GROUP BY; HAVING filters groups after aggregation. Step-by-Step Solution: List all business rules to filter rows.Translate each rule into a predicate.Combine predicates with AND/OR, and use parentheses for clarity.Use IN/EXISTS for set logic and subqueries when needed. Verification / Alternative check: Compare counts when applying individual predicates versus the full combined WHERE to ensure logic matches requirements. Why Other Options Are Wrong: Limiting WHERE to a single value is incorrect. Subqueries are optional; many cases use literals or joins. HAVING is not required for multi-value filters; it is for post-aggregation filtering. WHERE supports many data types and operators beyond numeric equality. Common Pitfalls: Ambiguous logic from missing parentheses, and confusing WHERE vs. HAVING. Always test boundary conditions and NULL handling. Final Answer: Incorrect

Question 19

Complex boolean logic: when combining three or more AND/OR conditions in SQL, is it usually easier to switch to NOT and NOT IN instead of using parentheses and explicit logic?

Accepted Answer

Introduction / Context: SQL predicates often involve multiple conditions. The question asserts that with three or more AND/OR terms, it is typically easier to use NOT and NOT IN. This probes best practices for clarity and correctness in boolean logic. Given Data / Assumptions: We may have complex filters across multiple columns. We can employ parentheses, operator precedence, and De Morgan’s laws. Readability and correctness are primary goals. Concept / Approach: While NOT and NOT IN are valid, they are not inherently simpler. Good practice is to write clear, positive logic with explicit parentheses to control evaluation and avoid precedence surprises. Use IN/NOT IN for set membership when it simplifies expression, but do not default to negation for complexity. For example, WHERE (status IN ('NEW','PENDING')) AND (amount >= 100) OR (vip_flag = true) may be clearer than a negated equivalent. Parentheses communicate intent and prevent logic bugs. Step-by-Step Solution: List conditions as positive tests where possible.Combine with AND/OR and add parentheses to reflect the intended precedence.Introduce NOT/NOT IN only if it shortens or clarifies the predicate.Validate with test data and truth tables for edge cases (NULLs, empty sets). Verification / Alternative check: Create a small truth table to compare the original AND/OR logic with a proposed NOT/NOT IN rewrite; ensure equivalence and prefer the clearer version. Why Other Options Are Wrong: “Correct” overgeneralizes—negation can obscure intent. Limiting by data type or client UI is irrelevant; this is about logical clarity. Creating views is unnecessary for writing clear predicates. Common Pitfalls: Relying on default precedence (AND before OR) without parentheses, and misusing NOT IN with NULLs (which can yield unexpected filtering due to three-valued logic). Prefer explicit parentheses and consider IS DISTINCT FROM where supported. Final Answer: Incorrect

Question 20

In SQL, can the BETWEEN keyword be used in a WHERE clause to select rows whose column values fall within an inclusive range (lower and upper bounds included)?

Accepted Answer

Introduction / Context: This item tests knowledge of basic SQL filtering syntax, specifically how the BETWEEN operator works inside a WHERE clause to capture values in a continuous interval. Given Data / Assumptions: Standard SQL WHERE clause is used for row filtering. BETWEEN has the form: column BETWEEN lower AND upper. Lower and upper endpoints are included. BETWEEN works for comparable types such as numbers, dates/timestamps, and text (when collation/ordering applies). Concept / Approach: BETWEEN is syntactic sugar equivalent to column >= lower AND column <= upper. With dates, it selects rows whose date is on or after the lower bound and on or before the upper bound. With strings, inclusion follows the database’s collation order. Step-by-Step Solution: Recognize that a “range of values” means contiguous values between two bounds.Recall the equivalence: a BETWEEN b AND c ⇔ a >= b AND a <= c.Note inclusivity at both ends.Conclude the statement is accurate. Verification / Alternative check: Write quick examples, e.g., WHERE amount BETWEEN 100 AND 200 or WHERE order_date BETWEEN DATE '2023-01-01' AND DATE '2023-01-31'; both include boundary values 100, 200 and the first and last day. Why Other Options Are Wrong: Exclusive bounds are incorrect; BETWEEN is inclusive. It is valid in WHERE as well as HAVING, and it applies to multiple data types, not only character data. Common Pitfalls: For timestamps, upper bounds like '2023-01-31' may exclude times later that day; use an upper bound of the next day or an explicit < condition. For strings, understand collation implications. Final Answer: Applies — BETWEEN selects values within an inclusive range

Introduction to SQL Questions