Computer Knowledge

Database and SQL

3,929 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

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID, LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT; END; This procedure must invoke the UPD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?

  1. EXECUTE UPD_BAT_STAT(V_ID);

  2. UPD_BAT_STAT(V_ID);

  3. RUN UPD_BAT_STAT(V_ID);

  4. START UPD_BAT_STAT(V_ID);
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In PL/SQL, a procedure is invoked by name with parameters in parentheses, without any command keyword. EXECUTE is SQL*Plus syntax, not PL/SQL. RUN and START are SQL*Plus commands for executing scripts, not for calling procedures. The correct syntax is simply the procedure name with arguments.

Multiple choice technology databases

Which type of argument passes a value from a procedure to the calling environment?

  1. VARCHAR2

  2. BOOLEAN

  3. OUT

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

OUT parameters are used to pass values from a procedure back to the calling environment. IN parameters pass values into the procedure, while OUT parameters return results. VARCHAR2 and BOOLEAN are data types, not parameter modes. The OUT mode specifically enables returning computed values.

Multiple choice technology databases

Examine this procedure: CREATE OR REPLACE PROCEDURE INSERT_TEAM (V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT ‘AUSTIN’, V_NAME in VARCHAR2) IS BEGIN INSERT INTO TEAM (id, city, name) VALUES (v_id, v_city, v_name); COMMIT; END Which two statements will successfully invoke this procedure in SQL *Plus?

  1. EXECUTE INSERT_TEAM;

  2. EXECUTE INSERT_TEAM(3, V_NAME=>’LONGHORNS’, V_CITY=>’AUSTIN’);

  3. EXECUTE INSERT_TEAM(3, ‘AUSTIN’,’LONGHORNS’);

  4. EXECUTE INSERT_TEAM (V_ID := V_NAME := ‘LONGHORNS’, V_CITY :=‘AUSTIN’);
Reveal answer Fill a bubble to check yourself
B,C Correct answer
Explanation

Option B uses named notation with the EXECUTE command (SQL*Plus syntax), and Option C uses positional notation with EXECUTE. Option A fails because the first parameter V_ID is required (no DEFAULT). Option D uses incorrect assignment syntax (:= instead of => for named parameters in SQL*Plus).

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.