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

B37 abend during SPUFI is due to

  1. Space Issues

  2. Data Issues

  3. No records found while querying the table

  4. Both a & b
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

B37 abend in DB2 indicates insufficient space. During SPUFI (SQL Processor Using File Input) execution, this typically means either the output dataset allocated doesn't have enough space to hold the query results, or the tablespace where data resides has space issues. It is a space-related abend code.

Multiple choice technology databases

An application has acquired a Share lock on a row in a table and now wishes to update the row. Which of the following statements is true?

  1. The application must release the row-level Share lock it holds and acquire an Update lock on the row

  2. The application must release the row-level Share lock it holds and acquire an Update lock on the table

  3. The row-level Share lock will automatically be converted to a row-level Up-date lock

  4. The row-level Share lock will automatically be escalated to a table-level Up-date lock
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

When an application holds a Share (S) lock on a row and wants to update it, DB2 automatically attempts to upgrade or convert the existing row-level Share lock to an Exclusive (X) or Update (U) lock. The application does not manually release it first.

Multiple choice technology databases

Which of the following is used to indicate a column will not accept NULL values and can be referenced in another table's foreign key specification?

  1. Check constraint

  2. Default constraint

  3. Unique constraint

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

A unique constraint ensures all values in a column are different and cannot be NULL. This makes it suitable for foreign key references since other tables need reliable, non-null values to establish relationships.

Multiple choice technology databases

What authority is required to create a table?

  1. ADMIN Privileges

  2. CREATETAB Privileges

  3. No Privileges

  4. USERTAB Privileges
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

CREATETAB privilege specifically authorizes a user to create tables in a database. Without this privilege, users cannot execute CREATE TABLE statements regardless of other permissions they might have.

Multiple choice technology databases

Given the following scenario: Table TABLE1 needs to hold specific numeric values up to 9999999.999 in column COL1. Once TABLE1 is populated, arithmetic operations will be performed on data stored in col-umn COL1. Which of the following would be the most appropriate DB2 data type to use for column COL1?

  1. INTEGER

  2. REAL

  3. NUMERIC (7,3)

  4. DECIMAL(10,3)
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To store numeric values up to 9999999.999, the column needs to hold 7 digits before the decimal point and 3 digits after, requiring a total precision of 10 and scale of 3. Thus, DECIMAL(10,3) is the correct and most appropriate data type.

Multiple choice technology databases

How do you find the total number of rows in a table ?

  1. SELECT COUNT *

  2. SELECT COUNT(*)

  3. SELECT COUNT[*]

  4. SELECT COUNT(ALL)
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

COUNT(*) is the correct SQL syntax to count all rows in a table. The parentheses are required for the COUNT function, and the asterisk indicates all rows should be counted regardless of NULL values.

Multiple choice technology databases

Given the following statement: CREATE TABLE t1 col1  SMALLINT NOT NULL PRIMARY KEY, col2  VARCHAR(200) NOT NULL WITH DEFAULT NONE, col3  DECIMAL(5,2) CHECK (col3 >= 100.00), col4  DATE NOT NULL WITH DEFAULT) Which of the following definitions will cause the CREATE TABLE statement to fail?

  1. COL1

  2. COL2

  3. COL3

  4. COL4
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The WITH DEFAULT NONE clause is invalid SQL syntax. DEFAULT requires a valid value or constraint like CURRENT DATE, a literal, or NULL. NONE is not a recognized default value specification, causing the CREATE TABLE to fail.

Multiple choice technology databases

Given the following table: TEMP_DATA TEMP DATE _____________ 45    12/25/2006 51    12/26/2006 67    12/27/2006 72    12/28/2006 34    12/29/2006 42    12/30/2006 And the following SQL statement: TE FUNCTION degf_to_c (temp INTEGER) RETURNS INTEGER LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC BEGIN ATOMIC DECLARE newtemp INTEGER; SET newtemp = temp - 32; SET newtemp = newtemp * 5; RETURN newtemp / 9; END Which two of the following SQL statements illustrate the proper way to invoke the calar function DEGF_TO_C?

  1. VALUES degf_to_c(32)

  2. SELECT date, degf_to_c(temp) AS temp_c FROM temp_data

  3. CALL degf_to_c(32)

  4. Both a and b
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A scalar user-defined function like degf_to_c can be executed inline within a SELECT statement or evaluated using a VALUES statement. It cannot be called using the CALL statement, which is reserved for stored procedures.

Multiple choice technology databases

What is the restriction on using UNION in embedded SQL ?

  1. It has to be in a CURSOR

  2. cannot be used in Embedded SQL

  3. No restrictions

  4. None of the above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In embedded SQL (SQL embedded in host languages like C or Java), UNION operations that return multiple rows typically require a cursor context to properly fetch and process the result set. This is because UNION combines multiple result sets and the cursor provides a mechanism to iterate through the combined rows. Other isolation levels either use fewer locks or release locks earlier.

Multiple choice technology databases

Which of the following is a NOT a valid reason for defining a view on a table?

  1. Restrict users' access to a subset of table data

  2. Ensure that rows inserted remain within the scope of a definition

  3. Produce an action as a result of a change to a table

  4. Provide users with an alternate view of table data
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Views are used to restrict access, simplify queries, or provide alternative representations of data. They are passive structures and cannot produce an action (such as executing code) in response to data changes; that is the role of a trigger.

Multiple choice technology databases

what is the maximum number of triggers can apply on a single table

  1. 4

  2. no limit

  3. 24

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

A maximum of 12 triggers can be defined on a single table in a database, covering combinations of INSERT, UPDATE, DELETE operations with BEFORE and AFTER timing. This limit exists due to the finite combinations of trigger events and timing options. Options A, B, and C do not represent the actual trigger limit.