Computer Knowledge

Database and SQL

4,213 Questions

Master structured query language commands, database joins, table constraints, and alias generation. This section covers relational database management concepts and query outputs essential for technical aptitude. These technical questions feature prominently in banking IT officer exams and computer knowledge sections.

SQL queries and aliasesDatabase table constraintsDatabase joins and transformationsStored procedures and functions

Database and SQL Questions

Multiple choice technology databases

Which two describe a stored procedure?

  1. A stored procedure is typically written in SQL.

  2. A stored procedure is a named PL/SQL block that can accept parameters

  3. A stored procedure is a type of PL/SQL subprogram that performs an action

  4. A stored procedure has three parts: the specification, the body, and the exception handler part

  5. The executable section of a stored procedure contains statements that assign values, control execution, and return values to the calling environment.
Reveal answer Fill a bubble to check yourself
B,C Correct answer
Explanation

A stored procedure is a named PL/SQL block that can accept parameters (B) and is a type of PL/SQL subprogram that performs an action (C). They are typically written in PL/SQL, not just SQL (A is wrong). They don't have a separate specification part like packages (D is wrong), and while they have executable sections, the description in E is too broad and not specifically about stored procedures.

Multiple choice technology databases

What is true about stored procedures?

  1. A stored procedure uses the DECLARE keyword in the procedure specification to declare formal parameters

  2. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification

  3. A stored procedure must have at least one executable statement in the procedure body.

  4. A stored procedure uses the DECLARE keyword in the procedure body to declare formal
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A stored procedure body must contain at least one executable statement (such as NULL; if no action is needed). It does not require parameter declarations, does not use the DECLARE keyword in the parameter block, and uses IS or AS instead of DECLARE in the body block.

Multiple choice technology databases

Examine this code: CREATE OR REPLACE PROCEDURE insert_dept (p_location_id NUMBER) IS v_dept_id NUMBER(4); BEGIN INSERT INTO departments VALUES (5, ‘Education’, 150, p_location_id); SELECT department_id INTO v_dept_id FROM employees WHERE employee_id=99999; END insert_dept; / CREATE OR REPLACE PROCEDURE insert_location ( p_location_id NUMBER, p_city VARCHAR2) IS BEGIN INSERT INTO locations (location_id, city) VALUES (p_location_id, p_city); insert_dept(p_location_id); END insert_location; / You just created the departments, the locations, and the employees table. You did not insert any rows. Next you created both procedures. You now invoke the insert_location procedure using the following command: EXECUTE insert_location (19, ‘San Francisco’) What is the result in this EXECUTE command?

  1. The locations, departments, and employees tables are empty

  2. The departments table has one row. The locations and the employees tables are empty

  3. The location table has one row. The departments and the employees tables are empty.

  4. The locations table and the departments table both have one row. The employees table is empty.
Reveal answer Fill a bubble to check yourself
A Correct answer
Multiple choice technology databases

Examine this procedure: CREATE OR REPLACE PROCEDURE DELETE_PLAYER (V_ID IN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE ID = V_ID; EXCEPTION WHEN STATS_EXITS_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE (‘cannot delete this player, child records exist in PLAYER_BAT_STAT table’); END; What prevents this procedure from being created successfully?

  1. A comma has been left after the STATS_EXIST_EXCEPTION exception.

  2. The STATS_EXIST_EXCEPTION has not been declared as a number

  3. The STATS_EXIST_EXCEPTION has not been declared as an exception

  4. Only predefined exceptions are allowed in the EXCEPTION section.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to have knowledge about PL/SQL syntax, specifically about creating stored procedures, and error handling using exceptions.

Option A is incorrect. The procedure does not have any syntax errors; there is no errant comma.

Option B is incorrect. The STATS_EXIST_EXCEPTION is not a number or a variable that needs to be declared. It is a user-defined exception that will be raised in case of an exception with the specified condition.

Option C is correct. The STATS_EXIST_EXCEPTION is not declared as an exception, which will cause an error during compilation of the stored procedure. The correct way of declaring a user-defined exception is as follows:

EXCEPTION
  WHEN  THEN
      -- handle the exception

Option D is incorrect. PL/SQL allows users to define their own exceptions using the EXCEPTION keyword. Predefined exceptions are not the only exceptions allowed in the EXCEPTION section.

Therefore, the correct answer is:

The Answer is: C. The STATS_EXIST_EXCEPTION has not been declared as an exception.

Multiple choice technology databases

Which is a major problem with SQL

  1. SQL cannot support object-orientation

  2. The same query can be written in many ways, each with vastly different execution plans

  3. SQL syntax is too difficult for non-computer professionals to use

  4. SQL creates excessive locks within the database
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A primary challenge with SQL is its declarative nature: the same logical query can be written in multiple ways. This leaves execution path determination to the optimizer, often resulting in highly variable performance for semantically identical queries.

Multiple choice technology databases

Which of the following deletion rules on CREATE TABLE will allow parent table rows to be deleted if a dependent row exists?

  1. ON DELETE RESTRICT

  2. ON DELETE NO ACTION

  3. ON DELETE SET NO VALUE

  4. ON DELETE CASCADE
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

ON DELETE CASCADE allows deletion of parent rows and automatically deletes all dependent child rows, maintaining referential integrity. RESTRICT and NO ACTION prevent deletion if dependents exist. SET NO VALUE is not a valid deletion rule.

Multiple choice technology databases

Which of the following CANNOT be used to restrict specific values from being inserted into a column in a particular table?

  1. Index

  2. Check constraint

  3. Referential constraint

  4. Default constraint
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A Default constraint provides a value when none is specified but does NOT restrict what values can be inserted. Check constraints, referential constraints, and unique indexes can all restrict specific values from being inserted.

Multiple choice technology databases

The following SQL statements were executed in sequence: CREATE DISTINCT TYPE salary AS decimal(7,2) WITH COMPARISONS; CREATE TABLE staffsalary(empid INT, empsalary salary); INSERT INTO staffsalary VALUES (10, 50000), (20, 50000.00); UPDATE staffsalary SET empsalary = 60000 WHERE salary(50000) = empsalary; What is the current content of the staffsalary table?

  1. ID | EMPSALARY 10 | 60000 20 | 50000.00

  2. ID | EMPSALARY 10 | 50000.00 20 | 50000.00

  3. ID | EMPSALARY 10 | 60000.00 20 | 60000.00

  4. .ID | EMPSALARY 10 | 60000.00 20 | 50000.00
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The UPDATE statement matches both rows since salary(50000) matches 50000 and 50000.00 (both equal the distinct type value). Both rows are updated to 60000. The UPDATE WHERE clause uses the salary constructor which matches both values.

Multiple choice technology databases

Which of the following objects contains control structures that are considered the bound form of SQL statements

  1. UDT

  2. Trigger

  3. Package

  4. Access plan
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A Package in DB2 contains the bound form of SQL statements. When SQL statements are prepared and bound, they are stored in a package object, which includes control structures and the access plan. UDTs are user-defined types, Triggers are event-driven actions, and Access plans are execution plans, but only Packages serve as containers for bound SQL control structures.

Multiple choice technology databases

Given the following ALTER SEQUENCE statement: ALTER SEQUENCE myseq RESTART WITH 0 INCREMENT BY 1 NO MAXVALUE CACHE 5 ORDER Assuming that the sequence had reached a value of 100 prior to the RESTART, which of the following is true?

  1. The next value will be 0 and the sequence will never use the values 101 to 105.

  2. The next value will be 101 to ensure uniqueness between existing and newly generated sequence values.

  3. Previously cached values are retained by DB2, and after the restart, will be used for values 101 to 105.

  4. The next value will be 0 and DB2 will not ensure uniqueness between existing and newly generated values.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

After ALTER SEQUENCE with RESTART WITH 0, the next value generated will be 0. DB2 does not guarantee uniqueness between previously used sequence values (like 101-105) and newly generated values after a restart. Options A, B, and C incorrectly suggest that cached values are retained or uniqueness is maintained - the RESTART command explicitly discards cached values and resets the sequence.

Multiple choice technology databases

A user utilizing an alias to update a subset of columns in a table must have UPDATE privileges on which of the following DB2 objects?

  1. Table

  2. Columns

  3. Table and Alias

  4. Columns and Alias
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When using an alias to update a table, the user must have UPDATE privileges on the underlying Table itself, not on the alias or individual columns. DB2 checks privileges on the base object that the alias references. The alias is simply an alternative name for the table and doesn't require separate privileges.

Multiple choice technology databases

In which of the following scenarios would a stored procedure be beneficial?

  1. An application running on a remote client needs to be able to convert degrees Celsius to degrees Fahrenheit and vice versa

  2. An application running on a remote client needs to collect three input values, perform a calculation using the values provided, and store the input data, along with the results of the calculation in two different base tables

  3. An application running on a remote client needs to track every modification made to a table that contains sensitive data

  4. An application running on a remote client needs to ensure that every new employee that joins the company is assigned a unique, sequential employee number
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A stored procedure is beneficial when a remote client performs multiple database actions (e.g., inserts into two tables), as it runs entirely on the server to reduce network traffic. Simple conversions use functions, change tracking uses triggers, and unique IDs are generated via sequences.

Multiple choice technology databases

A programmer wants to generate values for a numeric ID column in their EXPENSE table. The ID column values need to be incremented by 1000 for each new expense report added to the EXPENSE table. Which DB2 object can be referenced by an INSERT statement to meet thisrequirement?

  1. Sequence

  2. Table Function

  3. Identity Column

  4. INSTEAD OF Trigger
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A DB2 Sequence can generate numeric values with any increment value, including 1000. Sequences are independent database objects designed specifically for generating unique numeric values. Identity columns are tied to specific tables, table functions return result sets, and INSTEAD OF triggers modify insert operations but don't generate incremental values.

Multiple choice technology databases

Given the following DDL for the PARTS table: CREATE TABLE parts (part_no INT(9) NOT NULL, part_name VARCHAR(24), part_remain INT(9)); All part numbers entered will be different and all rows should be displayed in order of increasing part numbers whenever the table is queried. Which of the following create index statements will meet this criteria and require the least amount of storage for the index object?

  1. CREATE UNIQUE INDEX idx_partno ON parts(part_no)

  2. CREATE UNIQUE INDEX idx_partno ON parts(part_name ASC)

  3. CREATE UNIQUE INDEX idx_partno ON parts(part_name, part_no ASC)

  4. CREATE UNIQUE INDEX idx_partno ON parts(part_no, part_name ASC)
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

CREATE UNIQUE INDEX idx_partno ON parts(part_no) is correct because it creates a unique index on part_no (ensuring all part numbers are different) and orders by part_no by default. It uses the least storage by indexing only the required column. Options B, C, and D incorrectly include part_name in the index or order by the wrong column, increasing storage without meeting the requirements.

Multiple choice technology databases

What type of constraint can be used to ensure that, in any given row in a table, the value of one column never exceeds the value of another column?

  1. Check

  2. Range

  3. Referential

  4. Informational
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A Check constraint can enforce that the value of one column never exceeds another by using a condition like 'column1 <= column2'. Check constraints validate row data against user-defined conditions. Range, Referential, and Informational are not standard DB2 constraint types - Referential integrity is enforced through foreign key constraints.