Question 1

Stored procedures: which of the following is NOT an advantage of using stored procedures in a database system?

Accepted Answer

Introduction / Context: Stored procedures encapsulate SQL and logic on the server. They are used for performance, security, and maintainability. This question asks you to identify which option is not a benefit. Given Data / Assumptions: Stored procedures execute on the database server. Clients call procedures with parameters instead of sending large ad-hoc SQL blocks. DBMSes cache execution plans and enforce privileges at the procedure boundary. Concept / Approach: Benefits include reduced round trips (less network traffic), consistent logic reuse (code sharing), improved security (expose only the proc), and plan reuse/optimization. Therefore, “increased network traffic” contradicts the usual effect. Step-by-Step Solution: List typical advantages: security, plan optimization, reuse, and less data over the wire.Evaluate each option versus this list.Identify the negative outlier: “Increased network traffic.” Verification / Alternative check: Compare two approaches: sending many individual SQL statements vs. one stored procedure call with parameters. The latter generally reduces bytes and latency. Why Other Options Are Wrong: Greater security: procedures can limit direct table access. SQL can be optimized: plan caching and tuning are common. Code sharing: procedures centralize shared logic. Common Pitfalls: Assuming stored procedures always improve performance. Poorly written procedures or excessive server logic can still perform badly; however, they do not inherently increase network traffic. Final Answer: Increased network traffic

Question 2

SQL Data Definition Language (DDL): which statement is used to create a named view over one or more base tables?

Accepted Answer

Introduction / Context: A view is a saved SELECT definition that presents a virtual table. Knowing the correct DDL for creating a view is fundamental in SQL. Given Data / Assumptions: Standard SQL syntax is assumed. We want the statement that defines (not queries) a view. Concept / Approach: The DDL command for creating a view is CREATE VIEW view_name AS <select-statement>. Other verbs like MAKE, SELECT VIEW, or INSERT VIEW are not valid SQL commands. Step-by-Step Solution: Recall standard DDL verbs: CREATE, ALTER, DROP.Combine with the object type: VIEW.Therefore: CREATE VIEW. Verification / Alternative check: RDBMS documentation (PostgreSQL, SQL Server, Oracle, MySQL) consistently uses CREATE VIEW ... AS SELECT ... Why Other Options Are Wrong: MAKE VIEW / SELECT VIEW / INSERT VIEW are not SQL DDL statements. Common Pitfalls: Confusing the DDL (CREATE VIEW) with querying a view (SELECT ... FROM view_name). Final Answer: CREATE VIEW

Question 3

In SQL, what does the DROP TABLE statement do with respect to the table’s structure and data, considering referential integrity?

Accepted Answer

Introduction / Context: Managing schema objects is a core DBA task. DROP TABLE is a destructive DDL operation that removes a table definition and its stored rows. Given Data / Assumptions: Standard SQL behavior is considered. Foreign key constraints may reference the table. Concept / Approach: DROP TABLE removes both the table's metadata and all its data pages. However, if other objects (for example, foreign keys) depend on it, the DBMS may block the drop unless constraints are removed or CASCADE options are used (varies by DBMS). Step-by-Step Solution: Interpret choices: structure only vs. structure and data.DROP TABLE eliminates the table entirely, including its data.Note: Referential integrity can prevent a drop unless explicitly handled. Verification / Alternative check: Experiment on a test schema: create a table, insert rows, DROP TABLE; afterward, both the definition and rows are gone. Why Other Options Are Wrong: Structure only: incorrect; TRUNCATE removes data but leaves structure, DROP removes both. “Always works”: false; constraints can block it. “Not an SQL statement”: incorrect. Common Pitfalls: Confusing DROP TABLE with TRUNCATE TABLE (which removes rows but keeps the table) or with DELETE (DML) which removes rows but not structure. Final Answer: Deletes the table structure along with all table data

Question 4

Terminology check: a program stored inside the database that performs a common action on data is called a(n) ________.

Accepted Answer

Introduction / Context: Databases support server-side code for performance, security, and reuse. Two common programmable objects are triggers and stored procedures. They differ by how they are invoked. Given Data / Assumptions: We want the generic program that can be invoked deliberately (for example, EXEC or CALL) to perform actions. The code resides in the database and can contain SQL and procedural logic. Concept / Approach: A stored procedure is a named program stored in the database, executed by clients or other procedures. A trigger is also stored but fires automatically in response to DML/DDL events; it is not invoked directly by users in normal operation. Step-by-Step Solution: Match “program that performs some common action and is stored in the database” with “stored procedure.”Differentiate from triggers (event-driven).Eliminate distractors (pseudofile / none). Verification / Alternative check: Examples: PostgreSQL functions, SQL Server stored procedures, Oracle procedures/packages. Why Other Options Are Wrong: Trigger: event-driven code, not ad hoc invoked. Pseudofile: not a DBMS programmable object. None: incorrect because “stored procedure” fits. Common Pitfalls: Assuming triggers are interchangeable with procedures; triggers are tied to table/view events and execute implicitly. Final Answer: stored procedure

Question 5

SQL constraints: which of the following is NOT a standard SQL constraint type supported by mainstream relational databases?

Accepted Answer

Introduction / Context: Constraints enforce rules about data validity. Knowing official constraint types helps you model and implement integrity correctly. Given Data / Assumptions: Common SQL constraint types include PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL. Terminology may differ between modeling and implementation. Concept / Approach: In logical modeling, “alternate key” means any candidate key that is not chosen as the primary key. However, standard SQL does not have an “ALTERNATE KEY” constraint keyword. You implement alternate keys by declaring UNIQUE constraints. Step-by-Step Solution: List official constraint types.Note that “ALTERNATE KEY” is a modeling term, not a SQL DDL keyword.Therefore, select “ALTERNATE KEY” as not a type of SQL constraint. Verification / Alternative check: RDBMS documentation shows CREATE TABLE ... UNIQUE (...) for alternate keys rather than an ALTERNATE KEY clause. Why Other Options Are Wrong: PRIMARY KEY, FOREIGN KEY, UNIQUE are valid SQL constraints. Common Pitfalls: Confusing conceptual terms (alternate key) with DDL keywords; implement alternate keys with UNIQUE constraints. Final Answer: ALTERNATE KEY

Question 6

What do we call an SQL virtual table constructed from one or more base tables using a saved SELECT definition?

Accepted Answer

Introduction / Context: Views are a core abstraction in SQL that present data from base tables through a stored query, often to simplify access or enforce security. Given Data / Assumptions: A virtual table is mentioned. The definition is created from other tables via a SELECT. Concept / Approach: A view is defined with CREATE VIEW ... AS SELECT ... and behaves like a table in queries, though it does not store its own data (except materialized variants). It is distinct from ad hoc query results, which are transient. Step-by-Step Solution: Identify keywords “virtual table” and “constructed from other tables.”Associate with the SQL object “view.”Select “A view.” Verification / Alternative check: Attempt SELECT * FROM view_name after creation; this works like selecting from a table. Why Other Options Are Wrong: “Just another table”: physical tables store rows; a view is virtual. “A relation”: generic term for a table, not specific to virtual constructs. “Query results”: ephemeral; a view is persistent metadata. Common Pitfalls: Assuming views permanently store data; unless materialized, they compute results at query time. Final Answer: A view

Question 7

SQL views: for what purposes can views be used (rows/columns security and complexity hiding)?

Accepted Answer

Introduction / Context: Views are powerful for security and abstraction. They can filter rows, project specific columns, and encapsulate complex joins and calculations behind a simple name. Given Data / Assumptions: A view can contain a WHERE clause (row filtering). A view can select a subset of columns (column filtering). A view can wrap complex SELECTs so consumers use simple SELECT * FROM view. Concept / Approach: Because a view is defined by an arbitrary SELECT, it can include or exclude columns, include conditions to restrict rows, and combine multiple tables. Thus, all three use cases apply. Step-by-Step Solution: Assess each offered use: column projection, row filtering, complexity hiding.Recognize all are achievable via the view’s SELECT definition.Therefore choose the combined option. Verification / Alternative check: Create sample views: one selecting limited columns, one with WHERE department_id = 10, and one wrapping a complex join. All are valid and useful. Why Other Options Are Wrong: Each individual option is too narrow; views support more than a single purpose. Common Pitfalls: Relying on views alone for security: combine with proper privileges and, when needed, row-level security policies for robust protection. Final Answer: All of the above are uses for SQL views

Question 8

Updatable views: to update a view, the DBMS must be able to map the update to which underlying element(s)?

Accepted Answer

Introduction / Context: Not all views are updatable. When they are, the DBMS must deterministically translate the view update into an underlying base-table operation on exactly one target row. Given Data / Assumptions: The view references underlying base table(s). We are considering UPDATE/DELETE/INSERT via the view. Deterministic mapping is required (no ambiguity). Concept / Approach: For an UPDATE to be valid, the DBMS must locate the row in a specific base table that corresponds to the row seen through the view. Column mapping follows from the view SELECT list, but the critical requirement is identifying the exact base table row, typically via keys. Step-by-Step Solution: Consider an update like UPDATE v SET salary = salary + 1000 WHERE emp_id = 10.The engine must find the row in base table EMP where emp_id = 10.Thus the essential mapping is to “a particular row in a particular underlying table.” Verification / Alternative check: DBMS rules often disallow updating views with joins or aggregates because the target base row(s) cannot be uniquely determined. Why Other Options Are Wrong: “Particular column in a table/row” focuses on attributes, but without the row identity the update cannot be applied. “None” is incorrect because successful updates require a precise row mapping. Common Pitfalls: Expecting to update complex views (joins, GROUP BY) without INSTEAD OF triggers or rules that guide the mapping. Final Answer: a particular row in a particular underlying table

Question 9

Oracle triggers: which timing/event trigger types are supported for table/view operations?

Accepted Answer

Introduction / Context: Oracle Database supports multiple trigger types to respond to DML events. Understanding timing points and applicability to tables versus views is important for design. Given Data / Assumptions: We are considering DML triggers (INSERT/UPDATE/DELETE). We need to know which trigger kinds Oracle supports. Concept / Approach: Oracle supports BEFORE and AFTER triggers on tables. For views (which are not inherently updatable), Oracle supports INSTEAD OF triggers to define how DML on the view maps to base tables. Therefore, all three listed types exist. Step-by-Step Solution: List timing points: BEFORE and AFTER for tables.Add INSTEAD OF for views to handle DML redirection.Conclude “All of the above.” Verification / Alternative check: Oracle documentation shows CREATE [OR REPLACE] TRIGGER ... BEFORE|AFTER ... ON table and INSTEAD OF ... ON view. Why Other Options Are Wrong: Any single timing option is incomplete; Oracle supports the full set for their respective targets. Common Pitfalls: Attempting a BEFORE/AFTER trigger on a view; for views you use INSTEAD OF triggers. Final Answer: All of the above

Question 10

In SQL-92 compliant relational databases, views that are defined using a SELECT query have certain restrictions. Which clause is not allowed inside the defining SELECT of a view (i.e., the view's query text itself)?

Accepted Answer

Introduction / Context: SQL views are virtual tables defined by a stored SELECT statement. Standards such as SQL-92 impose rules on what the defining query may contain. Understanding these rules helps avoid portability problems and subtle errors when designing database abstractions. Given Data / Assumptions: The question targets SQL-92 behavior for view definitions. We are considering clauses that appear inside the SELECT used to create the view. The goal is to identify which clause is disallowed in the view’s defining query. Concept / Approach: Views represent unordered sets of rows. Because a view may be referenced from other queries that apply their own ORDER BY, the standard forbids embedding ORDER BY in the view definition. Other clauses like FROM, WHERE, GROUP BY, and HAVING are allowed because they define filtering and aggregation but do not impose a permanent presentation order. Step-by-Step Solution: List common SELECT clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.Check SQL-92 rule: view definitions must not specify ORDER BY.Therefore, among the options, ORDER BY is the disallowed clause. Verification / Alternative check: Vendors often note that ORDER BY in a view is ignored or rejected unless combined with vendor-specific extensions (for example, TOP in some systems). The portable, standards-compliant approach is to sort only in the outer query that consumes the view. Why Other Options Are Wrong: FROM: Required to specify source tables/views. WHERE: Allowed for filtering rows. GROUP BY / HAVING: Allowed to define aggregated views. Common Pitfalls: Expecting a view to always return rows in a given order. Without ORDER BY in the outer query, result ordering is never guaranteed. Final Answer: ORDER BY

Question 11

In relational databases, what can an SQL view hide from end users or client code?

Accepted Answer

Introduction / Context: Views are named, stored queries that expose a tailored, virtual table to consumers. They are a core abstraction that improves security, simplicity, and reuse. Given Data / Assumptions: A view returns a subset or transformation of base tables. We can control which columns and rows are visible through the view. We can encapsulate joins, functions, and complex calculations inside the view’s definition. Concept / Approach: Views can both restrict data visibility (project fewer columns and select specific rows) and hide complexity (joins, CASE logic, window functions, calculations). This shields application code and users from writing repetitive, error-prone SQL and supports security by exposing only what is necessary. Step-by-Step Solution: Consider visibility: a view may omit sensitive columns or filter rows.Consider complexity: a view may encapsulate complex joins and expressions.Therefore, a view hides both data details (columns/rows) and complex SQL. Verification / Alternative check: Role-based permissions granted on the view (but not the base tables) enforce column/row-level exposure, while keeping query logic centralized. Why Other Options Are Wrong: Columns and rows only: Ignores the complexity-hiding benefit. Complicated SQL syntax only: Ignores security/visibility filtering. None of the above / Only physical storage: Views are independent of physical storage; they provide logical abstraction. Common Pitfalls: Assuming views automatically enforce row-level security without proper GRANT/REVOKE; permissions must be configured correctly. Final Answer: Both columns/rows and complicated SQL syntax

Question 12

In SQL and relational databases, what do we call a stored program attached to a table or view that automatically fires in response to DML events (INSERT, UPDATE, DELETE)?

Accepted Answer

Introduction / Context: Automation in databases often relies on server-side programs that react to data changes. These programs help enforce integrity, transform data, or audit changes close to the data. Given Data / Assumptions: The mechanism is attached to a table or view. It fires automatically on INSERT, UPDATE, or DELETE events. It runs inside the same transaction as the triggering statement. Concept / Approach: A trigger is a stored routine associated with a table or view that executes in response to DML events. Triggers can be BEFORE or AFTER (depending on RDBMS), row-level or statement-level, and are used for auditing, denormalized updates, validation, and enforcing complex constraints not expressible with CHECK or foreign keys. Step-by-Step Solution: Identify the behavior: automatic execution on DML.Map to database feature → trigger.Confirm scope: bound to a table or a view, not free-standing. Verification / Alternative check: RDBMS documentation consistently defines triggers as the event-driven database programs attached to tables/views. Why Other Options Are Wrong: Pseudofile / Rowset macro / Auto-procedure: Not standard SQL terms. Embedded SELECT statement: A query inside another, not an event-driven routine. Common Pitfalls: Overusing triggers for business logic that belongs in the application; triggers should be reserved for data-integrity concerns or unavoidable side effects. Final Answer: Trigger

Question 13

Considering standard SQL, what is true about inserting rows using the SQL INSERT statement?

Accepted Answer

Introduction / Context: SQL provides flexible ways to add data to tables. Knowing the variations helps with performance, ETL tasks, and application simplicity. Given Data / Assumptions: We are using standard INSERT syntax. We may insert literal rows or copy results from another query. NULL defaults and column lists can be used to omit some columns. Concept / Approach: INSERT supports two common modes: row-at-a-time with VALUES, and set-based with INSERT ... SELECT, which can insert many rows in one statement. The choice affects performance and transaction behavior. It is not limited to one row per statement, and it is perfectly valid to copy rows from other tables or views via SELECT. Step-by-Step Solution: Recognize INSERT VALUES for single or multiple rows (vendor-dependent syntax allows VALUES lists).Recognize INSERT ... SELECT to insert a set returned by a query (potentially thousands of rows).Conclude that both per-row and set-based inserts are supported. Verification / Alternative check: Examples: INSERT INTO t(col1,col2) SELECT a,b FROM src WHERE ...; or INSERT INTO t VALUES (1,'a'), (2,'b') in systems that allow multi-values. Why Other Options Are Wrong: Only modify / cannot copy: Confuses INSERT with UPDATE; copying via SELECT is standard. Only one at a time: Not true for INSERT ... SELECT (and multi-VALUES in many RDBMS). Requires values for every column: Column lists and defaults/NULLs allow partial inserts. Common Pitfalls: For large batch inserts, remember to manage constraints and indexes to maintain performance and integrity. Final Answer: Rows can be inserted one at a time or in groups (using INSERT ... SELECT)

Question 14

In SQL Data Definition Language (DDL), what can the ALTER TABLE statement be used to do?

Accepted Answer

Introduction / Context: SQL separates data definition (DDL) from data manipulation (DML). ALTER TABLE is a DDL command used to evolve schema safely as requirements change. Given Data / Assumptions: We want to modify a table's structure or constraints. We are not directly editing row contents via ALTER. Standard SQL semantics apply. Concept / Approach: ALTER TABLE alters schema objects: add/drop columns, change data types where allowed, add/drop constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK), rename objects (vendor-specific), or adjust storage options. DML like UPDATE, INSERT, DELETE manipulates data, not schema. Step-by-Step Solution: Identify need: structural change → ALTER TABLE.Data changes use UPDATE/INSERT/DELETE, not ALTER.Therefore, ALTER TABLE is for schema evolution. Verification / Alternative check: RDBMS documentation classifies ALTER TABLE under DDL, distinct from DML statements. Why Other Options Are Wrong: Change table data / add rows / delete rows: Performed by UPDATE, INSERT, DELETE respectively. Export data: Handled by utilities (COPY, bcp) or SELECT INTO OUTFILE (vendor-specific), not ALTER. Common Pitfalls: Altering types or constraints on large tables can lock or rewrite data; plan maintenance windows and backups. Final Answer: Change the table structure (add/modify/drop columns, constraints, etc.)

Question 15

Which of the following is NOT one of the common categories for SQL CHECK constraints used to limit acceptable values?

Accepted Answer

Introduction / Context: CHECK constraints are declarative rules attached to a table that validate each row against logical conditions. Understanding common patterns helps design robust, self-enforcing schemas. Given Data / Assumptions: We compare typical categories of CHECK logic. We look for the option that does not describe a common category. CHECK operates per-row and is evaluated at INSERT/UPDATE. Concept / Approach: Common CHECK uses include range checks, membership in a set, column-to-column comparisons within the same row, and simple pattern checks. While one can reference deterministic functions, loosely referring to “system date” as a general category is not standard; moreover, some systems restrict nondeterministic references in CHECK constraints to maintain consistency across statements. Step-by-Step Solution: Identify typical categories: range, set membership, inter-column comparisons, simple patterns.Note that “system date” is not a canonical category and can be problematic or disallowed depending on vendor settings.Therefore select ”System date” as the item that is not a common category. Verification / Alternative check: Vendor docs illustrate CHECK with expressions like col BETWEEN low AND high, col IN (...), col1 < col2, or col LIKE 'A%'; “system date” is a special-case function usage, not a category. Why Other Options Are Wrong: Range checks / lists / column comparisons / pattern checks: All are standard and portable uses of CHECK constraints. Common Pitfalls: Depending on nondeterministic functions (like current timestamp) in constraints can lead to nonportable behavior and unexpected validation results. Final Answer: System date (e.g., tying acceptance to the server's current date in general)

Question 16

What is an advantage of placing computed expressions or derived columns inside an SQL view definition?

Accepted Answer

Introduction / Context: Views can encapsulate business rules and computed values, providing a stable interface while underlying tables evolve. This reduces duplication and prevents errors. Given Data / Assumptions: We want to reuse a calculation across many queries. We want uniform results for all consumers. We prefer centralizing logic in one definition point. Concept / Approach: By placing expressions (e.g., price * quantity, tax calculations, standardized CASE logic) in a view, developers avoid rewriting them in every report or query. Centralization ensures consistent behavior and simplifies maintenance if the business rule changes—update the view once and all consumers benefit. Step-by-Step Solution: Identify repeated expression → move it into the view’s SELECT list as a derived column.Grant permissions on the view to expose the computed result while hiding raw columns if needed.Consumers select from the view, gaining both convenience and consistency. Verification / Alternative check: Compare codebases before/after: fewer duplicated expressions, reduced defect surface area, and simpler queries. Why Other Options Are Wrong: Only one of the benefits: Ignores the other equally important advantage. Cannot appear in a view / storage size: Views are virtual; computations in the SELECT are fully supported and do not directly change physical storage. Common Pitfalls: Complex computations in views can impact performance if not indexed properly (consider indexed/materialized views where supported and appropriate). Final Answer: Both of the above

Question 17

Which SQL statement creates a new base table (i.e., defines a table in the schema)?

Accepted Answer

Introduction / Context: Data Definition Language (DDL) statements create, alter, and drop database objects. Choosing the correct verb is essential for clear and portable SQL. Given Data / Assumptions: The goal is to define a brand-new table and its columns. We are operating in standard SQL, not vendor-specific pseudo-syntax. We distinguish CREATE from ALTER and other verbs. Concept / Approach: CREATE TABLE defines a new table, its columns, data types, constraints, and optionally storage or partitioning options (vendor-specific). Other DDL statements modify existing objects (ALTER) or remove them (DROP). Step-by-Step Solution: Identify action: create new table → use CREATE TABLE.Exclude unrelated or nonstandard verbs.Confirm that column definitions and constraints follow in parentheses. Verification / Alternative check: Consult any SQL reference: CREATE TABLE is the canonical statement for creating tables. Why Other Options Are Wrong: ALTER TABLE: Modifies an existing table. MAKE/DEFINE/INIT TABLE: Not standard SQL keywords. Common Pitfalls: Forgetting to declare primary keys, NOT NULL, and CHECK constraints at creation time can lead to data-quality issues. Final Answer: CREATE TABLE

Question 18

Why might you use an SQL view specifically to hide certain columns from end users?

Accepted Answer

Introduction / Context: Views are often used as a security and abstraction layer. By exposing only selected columns, you can present a minimal, business-friendly interface while protecting confidential attributes (such as salaries or personal identifiers). Given Data / Assumptions: The base table contains both public and sensitive columns. Different user groups require different levels of access. The database supports GRANT permissions on views independently of base tables. Concept / Approach: Define a view that projects only non-sensitive columns (and optionally filters rows). Grant users access to the view, not the base table. This both simplifies query writing and prevents accidental exposure of confidential data. Step-by-Step Solution: Identify sensitive columns → exclude them from the view definition.Grant SELECT on the view only to intended roles.Applications query the view to get just what they need. Verification / Alternative check: Audits and permissions show users can read from the view but cannot SELECT from base tables lacking grants. Why Other Options Are Wrong: Only simplification / only protection: Views can do both simultaneously. Views do not hide columns: They do by projecting a subset. Improve plans only: Plan quality is not the primary reason for views; abstraction and security are. Common Pitfalls: Granting base-table access inadvertently bypasses the view’s protections. Always enforce least privilege. Final Answer: To accomplish both simplification and protection of sensitive columns

Question 19

In standard SQL, which constraint type is used directly on a column or table to limit allowed values (for example, enforce a range, a list, or a comparison rule)?

Accepted Answer

Introduction / Context: To keep data valid at the source, SQL provides declarative constraints that the database enforces automatically. Choosing the right constraint ensures data integrity without application-side duplication. Given Data / Assumptions: We want to restrict a column's allowed values (e.g., 1–100, set membership, or column relationships). The constraint should be enforced on every INSERT and UPDATE. We prefer a portable, standard mechanism. Concept / Approach: The CHECK constraint evaluates a Boolean expression per row. If the expression evaluates to true, the row is allowed; otherwise the statement fails. CHECK can reference one or more columns of the same row and supports ranges, sets, and simple predicates. Step-by-Step Solution: Define desired rule as a Boolean expression.Attach it as a CHECK constraint to the column or table.Database enforces the rule for all DML operations. Verification / Alternative check: Attempt to insert an invalid value; the database rejects it with a CHECK violation, proving the constraint works. Why Other Options Are Wrong: LIMIT / VALUE / FILTER constraint: Not standard SQL constraint names. None of the above: Incorrect because CHECK is the standard mechanism. Common Pitfalls: Writing overly complex CHECK expressions can hurt readability; keep them clear and document business rules. Final Answer: CHECK constraint

Question 20

A view that contains a computed column can be easily updated.

Accepted Answer

False

SQL for Database Construction Questions