Question 1

When you need to remove duplicate rows from the output of a SELECT query, which SQL keyword should you include to return only unique rows?

Accepted Answer

Introduction / Context: Queries that project fewer columns than the primary key may return duplicate rows. SQL provides a direct way to collapse duplicates in the result set without additional grouping logic. Given Data / Assumptions: The query uses SELECT and may retrieve repeating values. We only want unique combinations of the selected columns in the output. We are not aggregating; we simply want to deduplicate. Concept / Approach: The DISTINCT keyword modifies SELECT to return only unique rows across the selected columns. Some systems accept SELECT DISTINCT ON (columns) with special semantics, but the general keyword is DISTINCT. UNIQUE is not the standard keyword for SELECT and may be reserved for constraints; ONLY, SINGLE, or NODUP are not standard. Step-by-Step Solution: Determine that duplicates may occur in the projection.Add DISTINCT immediately after SELECT, for example: SELECT DISTINCT city FROM customers;Execute and observe that duplicates collapse into one row each. Verification / Alternative check: Compare row counts with and without DISTINCT to confirm deduplication. Why Other Options Are Wrong: UNIQUE: Usually used in constraints, not as a SELECT modifier in standard SQL. ONLY / SINGLE / NODUP: Not standard SQL keywords for deduplication. Common Pitfalls: Using DISTINCT to mask poor join conditions. Always ensure joins are correct; DISTINCT should not be a band-aid for cartesian products. Final Answer: DISTINCT

Question 2

In aggregation queries, what does the HAVING clause do, and how does it differ from WHERE when filtering results?

Accepted Answer

Introduction / Context: Aggregations with GROUP BY produce group-level rows. Filtering can occur before grouping (row-level) or after grouping (group-level). Correct placement of predicates determines whether they apply to individual rows or to aggregated results. Given Data / Assumptions: We are using GROUP BY and aggregate functions such as COUNT, SUM, AVG. We may need to filter groups based on aggregated values. We distinguish row-level filters from group-level filters. Concept / Approach: WHERE filters individual rows before grouping and cannot reference aggregate results. HAVING filters after GROUP BY and may reference aggregates, thereby including or excluding entire groups based on conditions like COUNT() > 10 or SUM(amount) > 1000. Step-by-Step Solution: Decide whether the predicate depends on aggregated values.If yes, place it in HAVING; if not, place it in WHERE.Example: SELECT dept, COUNT() FROM emp GROUP BY dept HAVING COUNT(*) > 5; Verification / Alternative check: Attempting to use aggregates in WHERE yields an error; moving the condition to HAVING resolves it. Why Other Options Are Wrong: B/C: Misstate scope; WHERE is not for columns nor does it act after grouping. D: HAVING and WHERE are not identical in timing or allowable expressions. E: Sorting is handled by ORDER BY, not HAVING. Common Pitfalls: Using HAVING for conditions that could be in WHERE, which can hurt performance by grouping unnecessary rows. Prefer WHERE when possible. Final Answer: Acts like a WHERE clause but is used for groups rather than rows.

Question 3

From a database theory perspective, what is SQL most accurately categorized as within the data stack?

Accepted Answer

Introduction / Context: SQL is central to relational data access, yet it is not a general-purpose programming language in the same sense as Java or Python. Understanding its role clarifies when to use SQL versus application code. Given Data / Assumptions: SQL provides data definition (DDL), data manipulation (DML), and control features (DCL/TCL). It works in conjunction with a DBMS that executes SQL statements. It is not itself an operating system or a DBMS. Concept / Approach: SQL is widely described as a data sublanguage designed specifically for defining and manipulating relational data. It is embedded in applications or executed via tools, but it does not manage memory, threads, or I/O in the way a full programming language or operating system does. Step-by-Step Solution: Eliminate options that correspond to system software (OS) or engines (DBMS).Recognize SQL’s scope: define schemas, query, and modify data.Choose the classification: data sublanguage. Verification / Alternative check: Academic texts and standards literature consistently refer to SQL as a specialized, declarative data sublanguage. Why Other Options Are Wrong: Programming language: SQL is declarative and domain-specific; not general-purpose. Operating system: Not applicable. DBMS: SQL is executed by a DBMS; it is not the DBMS itself. Markup language: SQL is not a presentation markup like HTML. Common Pitfalls: Assuming SQL alone can implement full application logic. Complex workflows are best handled in application code or stored procedures as needed. Final Answer: a data sublanguage.

Question 4

When you run a valid SQL SELECT statement, what logical structure does the database return to the client (independent of how it is displayed by tools)?

Accepted Answer

Introduction / Context: SQL queries are declarative: you specify what data you want, and the DBMS returns a structured set of rows and columns. Understanding the nature of that result clarifies how to process it in applications and BI tools. Given Data / Assumptions: A SELECT statement executes successfully. The database returns data to a client or tool. Presentation layers may format the data for human consumption. Concept / Approach: The result of a SELECT is a relation (a table-like structure): a set or multiset of rows, each with the same columns. While GUIs might render reports, grids, or charts, the underlying logical form is a table. Files, forms, or diagrams are external representations, not the SQL result itself. Step-by-Step Solution: Consider the structure: rows and columns with defined types and order.Recall terminology: a SELECT returns a result set that is relationally a table.Choose the option that names this logical structure. Verification / Alternative check: APIs such as JDBC, ODBC, and ADO.NET expose the result as row/column data sets, reinforcing the table concept. Why Other Options Are Wrong: Report/form: Presentation artifacts created by separate tools. File: A storage container; SQL results can be exported to files but are not files by definition. Diagram: A visualization, not a data structure returned by SQL. Common Pitfalls: Confusing what the client tool shows with the underlying data structure. The result is always tabular even if later pivoted or charted. Final Answer: table

Question 5

In SQL predicates, the BETWEEN operator is used for which type of comparison when filtering rows in a WHERE clause?

Accepted Answer

Introduction / Context: When filtering data by a lower and upper bound, SQL provides a concise predicate to express inclusive range checks. Correct use makes queries more readable and reduces errors compared with chained comparisons. Given Data / Assumptions: You need to select values within a start and end boundary. The comparison can be numeric, date/time, or even text (collation dependent). Inclusivity of endpoints matters. Concept / Approach: BETWEEN is an inclusive range predicate equivalent to: column >= lower AND column <= upper. It is commonly used for dates (for example, order_date BETWEEN '2025-01-01' AND '2025-01-31') or numbers. It does not limit selected columns (that is SELECT list control) and is unrelated to wildcards or joins. Step-by-Step Solution: Identify lower and upper bounds for the filter.Apply WHERE column BETWEEN low AND high for inclusive ranges.Confirm that rows equal to low or high are included. Verification / Alternative check: Compare results of WHERE x BETWEEN 10 AND 20 with WHERE x >= 10 AND x <= 20; they are equivalent. Why Other Options Are Wrong: Limit columns displayed: Use the SELECT list, not BETWEEN. Wildcard matching: Use LIKE with % or _ for patterns. None of the above: Incorrect because BETWEEN exists for ranges. Joining tables: Use JOIN syntax and ON conditions. Common Pitfalls: Using BETWEEN for datetime boundaries without considering time components; prefer half-open intervals or cast/normalize to avoid off-by-one-day errors. Final Answer: for ranges.

Question 6

Writing simpler multi-condition filters in SQL When three or more AND and OR conditions are being combined to test membership or non-membership in a list of values, which SQL keyword(s) make the expression clearer and easier to maintain?

Accepted Answer

Introduction / Context: As SQL predicates become longer, readability and correctness suffer. A classic case is when you need to check whether a column's value belongs to several alternatives, or explicitly does not belong to them. Instead of chaining many OR or AND comparisons, SQL provides concise list-membership operators that make intent obvious and reduce mistakes. Given Data / Assumptions: You are filtering rows based on whether a column equals one of several values, or must avoid a set of values. Three or more comparisons are present; maintainability matters. The value list is finite and known in the statement or parameterized. Concept / Approach: Use IN to replace a chain of OR equality tests, and use NOT IN to replace a chain of AND inequality tests. These operators express set membership and non-membership directly, improving clarity and aiding optimizers in some systems. They are ideal when comparing a single expression against a list of constants or a subquery result set. Step-by-Step Solution: Write a long OR chain like: color = 'red' OR color = 'blue' OR color = 'green'.Replace it with: color IN ('red', 'blue', 'green').Write a long AND chain like: status <> 'X' AND status <> 'Y' AND status <> 'Z'.Replace it with: status NOT IN ('X', 'Y', 'Z').Confirm that null-handling rules meet your needs (NOT IN with NULLs behaves differently than expected; consider IS NULL or NOT EXISTS as appropriate). Verification / Alternative check: Most vendor style guides recommend IN/NOT IN for readability and correctness when evaluating membership, and many show measurable simplification in code reviews and query linting tools. Why Other Options Are Wrong: LIKE only: pattern matching, not set membership. IN only or NOT IN only: both are needed for membership and non-membership cases. BETWEEN only: tests a continuous range, not a discrete list. Common Pitfalls: Using NOT IN with a subquery that can return NULL; rows will be filtered out unexpectedly. Prefer NOT EXISTS in that case. Forgetting to parameterize long lists that repeat across queries. Final Answer: Both IN and NOT IN.

Question 7

Deleting rows from a table in SQL Which command correctly removes one or more rows from the table named CUSTOMER while allowing you to filter which rows are affected?

Accepted Answer

Introduction / Context: SQL provides specific statements for distinct tasks: creating structures, modifying data, and dropping objects. Deleting rows is a data manipulation task and must be expressed with the correct DML statement so that the DBMS can enforce constraints, log changes, and maintain transactional integrity. Given Data / Assumptions: A table named CUSTOMER already exists. You intend to remove one or more rows based on a condition. You want transactional, logged deletion that can be rolled back if needed. Concept / Approach: Use DELETE to remove rows. Pair it with an appropriate WHERE clause to target only the intended subset. Without a WHERE clause, all rows are removed. DROP is for removing entire objects like tables or views; UPDATE modifies existing values; TRUNCATE removes all rows but does not accept a WHERE clause and has different logging/locking behavior depending on the DBMS. Step-by-Step Solution: Choose the DML verb: DELETE.Specify the table: DELETE FROM CUSTOMER.Add a filter: WHERE city = 'Chennai' (example).Wrap the statement in a transaction if required and test with SELECT before committing. Verification / Alternative check: DBMS documentation shows DELETE as the canonical row-removal command with optional WHERE; DROP removes the table itself; TRUNCATE is a fast, all-rows operation without WHERE. Why Other Options Are Wrong: REMOVE FROM: not valid SQL. DROP FROM: drops objects, not rows. UPDATE FROM: changes values, does not delete. TRUNCATE ... WHERE: TRUNCATE has no WHERE and removes all rows. Common Pitfalls: Forgetting the WHERE clause and deleting all rows unintentionally. Violating foreign key constraints; use ON DELETE rules or delete in the correct order. Final Answer: DELETE FROM CUSTOMER WHERE ...

Question 8

SQL as an ANSI standard Which technology was adopted as a national standard by ANSI in 1992 (often called SQL-92)?

Accepted Answer

Introduction / Context: Standards bodies such as ANSI and ISO formalize specifications so that vendors can interoperate and users can rely on consistent behavior. In the relational database world, SQL was standardized early and has undergone multiple revisions, with a major milestone in 1992. Given Data / Assumptions: The question refers to 1992, the year the SQL-92 standard was published. Vendor products implement the standard to varying degrees. ANSI standardization is separate from any single vendor product. Concept / Approach: SQL (Structured Query Language) became an ANSI standard initially in the late 1980s, with the significant SQL-92 revision adopted in 1992. Oracle, Access, and dBASE are products that may implement SQL but are not standards themselves. Step-by-Step Solution: Identify the domain: database query language.Recall the 1992 standard revision: SQL-92.Choose the language (SQL) rather than vendor products. Verification / Alternative check: Historical references list SQL-86, SQL-89, SQL-92, and later editions (1999, 2003, 2008, 2011, etc.). Why Other Options Are Wrong: Oracle/Access/dBASE: vendor-specific systems, not standards. COBOL: a programming language standardized separately and not the 1992 ANSI database standard in question. Common Pitfalls: Confusing a product that implements SQL with the SQL standard itself. Final Answer: SQL

Question 9

Understanding the SQL WHERE clause What does the WHERE clause restrict when you run a SELECT statement against a table or view?

Accepted Answer

Introduction / Context: SELECT statements are composed of clauses with distinct responsibilities. Understanding what each clause does improves correctness and performance. Confusing the job of WHERE with SELECT or ORDER BY is a common beginner error. Given Data / Assumptions: We are discussing a standard SELECT ... FROM ... WHERE ... statement. Columns vs rows are different concerns. The DBMS evaluates clauses in a logical processing order (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY). Concept / Approach: WHERE filters rows before projection. The SELECT list chooses which columns (and expressions) to return, while WHERE removes rows that do not satisfy the predicate. ORDER BY controls the result ordering and does not change which rows qualify. Step-by-Step Solution: Write a predicate, e.g., WHERE amount >= 1000.The DBMS evaluates rows and keeps only those meeting the condition.The SELECT list then projects specified columns.ORDER BY can sort the final qualifying rows. Verification / Alternative check: Logical query processing order shows WHERE acting before SELECT; thus, WHERE affects row qualification, not column selection. Why Other Options Are Wrong: A: Column limitation is done by SELECT, not WHERE. C: Both is incorrect; WHERE does not choose columns. D/E: WHERE absolutely affects which rows are returned. Common Pitfalls: Trying to reference column aliases from SELECT in WHERE (not allowed in many DBMSs because WHERE runs earlier). Using WHERE to filter aggregated results instead of HAVING after GROUP BY. Final Answer: It limits which rows are returned.

Question 10

Inserting new rows with SQL In a relational database, which SQL statement is used to add (append) a new row into an existing table?

Accepted Answer

Introduction / Context: Data manipulation involves inserting new rows, updating existing rows, and deleting unwanted rows. Knowing the correct DML verb helps you write portable SQL that behaves consistently across database vendors. Given Data / Assumptions: An existing table is available to receive a new row. All required columns will be provided or will take defaults. Constraints such as NOT NULL and foreign keys will be enforced. Concept / Approach: INSERT adds a new row to a table. CREATE defines new database objects (tables, indexes). ADD and MAKE are not standard SQL verbs for row insertion; APPEND is a common English term but not a standard SQL keyword. Step-by-Step Solution: Specify the statement: INSERT INTO table_name (col1, col2) VALUES (v1, v2);Alternatively, insert from a query: INSERT INTO table_name (col1, col2) SELECT ...;Validate that mandatory columns receive values or defaults.Commit or roll back the transaction as appropriate. Verification / Alternative check: Every major DBMS documents INSERT as the canonical row-creation command in the DML family alongside UPDATE and DELETE. Why Other Options Are Wrong: CREATE: DDL for objects, not rows. ADD/MAKE/APPEND: not standard SQL row-insert verbs. Common Pitfalls: Not listing columns, which can break when schema changes. Prefer explicit column lists. Violating constraints or missing foreign key references. Final Answer: INSERT

Question 11

Sorting query results Which SQL clause is used to sort the rows returned by a query in ascending or descending order by one or more expressions?

Accepted Answer

Introduction / Context: Result ordering is different from grouping or filtering. SQL separates these concerns so that you can control the presentation of rows without changing which rows qualify or how aggregates are computed. Given Data / Assumptions: We already have a SELECT statement that returns rows. We want to specify the order of those rows. Ordering may use column positions, names, or expressions. Concept / Approach: ORDER BY instructs the DBMS to sort the final result set. You can specify ASC or DESC for each sort key. GROUP BY forms groups for aggregation and does not imply any particular output order unless ORDER BY is also used. Non-standard clauses like SORT BY or ALIGN BY are not part of ANSI SQL (though some systems may offer syntactic sugar). Step-by-Step Solution: Add ORDER BY to the end of the SELECT statement.Provide one or more expressions: ORDER BY last_name ASC, first_name ASC.Use DESC for descending when needed: ORDER BY order_date DESC.Be aware of NULLS FIRST/NULLS LAST behavior depending on DBMS. Verification / Alternative check: ANSI SQL grammar places ORDER BY after SELECT ... FROM ... WHERE ... GROUP BY ... HAVING. Why Other Options Are Wrong: SORT BY / ALIGN BY / RANK BY: not ANSI-standard sort clauses. GROUP BY: used for aggregation, not sorting. Common Pitfalls: Relying on implicit order; without ORDER BY, the row order is not guaranteed. Sorting large datasets without proper indexes can be expensive; consider adding indexes on sort keys for frequent sorts. Final Answer: ORDER BY

Question 12

Scope of SQL capabilities Which statement best describes what SQL can do across data definition and data manipulation tasks?

Accepted Answer

Introduction / Context: SQL is a full declarative language for relational databases with multiple sublanguages. It is not limited to querying; it includes defining schemas, manipulating data, and controlling permissions. Understanding this breadth is essential for both developers and DBAs. Given Data / Assumptions: We consider ANSI-standard SQL capabilities. Vendors may add extensions, but core functions are common. Tasks include DDL, DML, and DCL/TCL. Concept / Approach: SQL includes: DDL (CREATE, ALTER, DROP) to manage structures; DML (SELECT, INSERT, UPDATE, DELETE, MERGE) to read and change data; DCL (GRANT, REVOKE) for privileges; TCL (COMMIT, ROLLBACK, SAVEPOINT) for transactions. Therefore, SQL can create structures, query data, and modify data. Step-by-Step Solution: Map each capability to SQL verbs: CREATE for structures, SELECT for queries, INSERT/UPDATE/DELETE for modifications.Recognize that a single language covers these roles.Conclude that “All of the above” is accurate. Verification / Alternative check: All major DBMS manuals document these categories explicitly under SQL reference sections. Why Other Options Are Wrong: Each of A, B, and C is too narrow; SQL is not restricted to just one category. E: Operating-system user administration is outside SQL's scope, though DB roles map to OS identities in some systems. Common Pitfalls: Thinking SQL equals SELECT only; forgetting DDL/DCL/TCL. Relying solely on application logic for permissions rather than GRANT/REVOKE. Final Answer: All of the above can be done by SQL.

Question 13

Reading data from tables Which SQL statement is used to query (read) data from a table or view and return a result set to the client?

Accepted Answer

Introduction / Context: The most common SQL operation is reading data. Knowing the precise keyword that initiates a query helps differentiate between standard SQL and vendor-specific commands, and avoids confusion with cursor operations or procedural code. Given Data / Assumptions: We want a statement that retrieves data. We are using ANSI-standard SQL syntax. Results should be returned as a set of rows and columns. Concept / Approach: The SELECT statement projects columns, filters rows, groups, aggregates, and joins tables to produce a relational result set. Other words like READ or QUERY are English descriptions, not SQL keywords. FETCH exists but is used with cursors to retrieve the next row from an already opened result set, not to issue the initial query. Step-by-Step Solution: Issue SELECT ... FROM ... to define the data source.Add optional WHERE, GROUP BY, HAVING, ORDER BY for filtering, aggregation, and sorting.Return the resulting dataset to the client or next processing step. Verification / Alternative check: Every SQL reference begins with the SELECT statement to demonstrate data retrieval operations. Why Other Options Are Wrong: READ/QUERY: not SQL keywords. FETCH: used with cursors after a SELECT has been executed. D: Incorrect because SELECT is correct. Common Pitfalls: Confusing FETCH (cursor navigation) with issuing a SELECT. Relying on SELECT * in production; specify columns for stability and performance. Final Answer: SELECT

Question 14

Original goals of the SQL language What was SQL originally designed to accomplish within relational database systems?

Accepted Answer

Introduction / Context: SQL emerged from the relational model to provide a declarative, vendor-neutral way to define schemas and manipulate data. Its scope covers both structure and data, enabling a complete lifecycle from creation to query to update using a single language. Given Data / Assumptions: Relational databases require a language for structure (DDL) and data (DML). Standards bodies formalized SQL so multiple vendors could implement it. Constraints and relationships are part of the structural definition. Concept / Approach: SQL's charter includes DDL (CREATE/ALTER/DROP) to define tables, keys, and constraints; DML (SELECT/INSERT/UPDATE/DELETE) to read and modify data; and related control statements for permissions and transactions. Therefore, its original purpose encompasses all items listed except operating-system concerns. Step-by-Step Solution: Map DDL responsibilities to structure definition and constraints.Map DML responsibilities to data retrieval and modification.Confirm that SQL was intended to unify these under one standard.Select the comprehensive choice: All of the above. Verification / Alternative check: Historical papers on System R and standards documentation confirm SQL's dual role in structure and manipulation, later expanded by security and transaction controls. Why Other Options Are Wrong: A/B/C alone are too narrow; SQL was not limited to a single facet. E: Outside SQL's scope; OS file handles are managed by the DBMS internals, not SQL. Common Pitfalls: Equating SQL only with querying, ignoring DDL and constraints that enforce data quality. Final Answer: All of the above.

Question 15

Most companies keep at least two versions of any database they are using.

Accepted Answer

True

Question 16

The SQL statement: SELECT Number1 + Number 2 AS Total FROM NUMBER_TABLE; adds two numbers from each row together and lists the results in a column named Total.

Accepted Answer

True

Question 17

Microsoft Access has become ubiquitous, and being able to program in Access is a critical skill.

Accepted Answer

False

Question 18

SQL is a data sublanguage.

Accepted Answer

True

Question 19

SUM, AVG, MIN, and MAX can only be used with numeric columns.

Accepted Answer

False

Question 20

The HAVING clause acts like a WHERE clause, but it identifies groups that meet a criterion, rather than rows.

Accepted Answer

True

Introduction to SQL Questions