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

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 during SPUFI (SQL Processor Using File Input) occurs when the output dataset runs out of space. This is a disk space allocation issue, not a data or query problem. The error message specifically indicates that the dataset needs more space allocated.

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

In DB2 lock escalation, a Share lock held on a row is automatically converted (promoted) to an Update lock when the same application needs to update that row. The lock doesn't need to be released first, nor does it escalate to a table-level lock. This automatic conversion is part of DB2's lock management system.

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 distinct and allows the column to be referenced by a foreign key. While Unique constraints allow NULLs by default, when combined with NOT NULL (implied by 'will not accept NULL'), it creates a key suitable for foreign key references. Check constraints validate values, Default constraints set initial values, and Informational constraints are for documentation only.

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 is the specific DB2 privilege required to create tables. ADMIN privileges are broader than necessary and not specifically required. 'No Privileges' is incorrect, and USERTAB is not a valid DB2 privilege name.

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

DECIMAL(10,3) provides 7 digits before the decimal and 3 after (9999999.999), which matches the requirement. INTEGER has no decimals, REAL uses floating-point with potential precision loss, and NUMERIC(7,3) only allows 4 digits before the decimal (9999.999). The DECIMAL type ensures precise arithmetic operations.

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 aggregate function syntax to count all rows in a table. The asterisk must be in parentheses. COUNT * (A) is invalid syntax, COUNT* uses wrong brackets, and COUNT(ALL) (D) is not the standard way to count rows.

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 CREATE TABLE statement will fail at COL2 because 'WITH DEFAULT NONE' is not valid DB2 syntax. The DEFAULT clause requires a specific value (like a constant, CURRENT DATE, or NULL). 'NONE' is not recognized as a valid default value. The other column definitions are syntactically correct.

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 in DB2 can be evaluated using a VALUES statement or embedded directly inside a projection list of a SELECT query. It cannot be executed using CALL, which is strictly for stored procedures.

Multiple choice technology databases

To retrieve data from more than one table we could use

  1. Joins

  2. Unions

  3. Nested SELECT

  4. All of the above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To retrieve data from multiple tables, SQL provides several mechanisms: Joins combine columns from different tables; Unions combine rows from different queries; and Nested SELECT statements query tables hierarchically. Thus, all of these methods can be used.

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 within host programs like C, Java, COBOL), UNION operations must be used within a CURSOR declaration. This is because embedded SQL requires explicit result set handling mechanisms - a cursor provides the necessary interface to navigate through the UNION result set row by row. Without a cursor, the host program cannot properly retrieve and process the combined data from the UNION.

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 virtual tables that provide restricted or alternate access to underlying data. Their purposes include: restricting user access to specific rows/columns (security), providing alternate representations of data (simplification), and with CHECK OPTION ensuring inserted rows conform to the view definition. However, producing an action as a result of a change is what TRIGGERS do - they execute code in response to data modification events (INSERT, UPDATE, DELETE). Views are passive queries; triggers are active procedures.

Multiple choice technology programming languages

Which of the following SQL statements will not execute successfully? (Choose all that apply)

  1. SELECT lname, jobid, sal, sal+500 FROM emp;

  2. SELECT lname, jobid, sal FROM emp (sal + 500);

  3. SELECT lname, jobid, sal, sal + 500 “Bonus” FROM emp;

  4. SELECT lname, jobid, sal, sal + 500 bonus FROM emp

  5. SELECT lname || to_char(sal+500) “Employee Bonus” FROM emp;

  6. SELECT lname, sal + 500 Employee Bonus FROM emp;
Reveal answer Fill a bubble to check yourself
B,D,F Correct answer
Explanation

The invalid statements are: SELECT lname, jobid, sal FROM emp (sal + 500); due to incorrect parenthesis syntax; SELECT lname, jobid, sal, sal + 500 bonus FROM emp due to a missing semicolon; and SELECT lname, sal + 500 Employee Bonus FROM emp; because multi-word aliases require double quotes.

Multiple choice technology programming languages

You run the following SQL statement: SELECT a.lname, a.location, b.dname, b.hiredate FROM emp a, emp b WHERE a.empid=b.empid; Which options are correct? (Choose all that apply)

  1. This is an equijoin.

  2. This is a self join.

  3. Line 2 will return an error.

  4. Line 3 will return an error.

  5. The statement will execute successfully.
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

This is a self join because the emp table is joined to itself using two different aliases (a and b). A self join is used when a table needs to be compared with itself, such as finding relationships between rows in the same table. The WHERE clause a.empid=b.empid creates an equijoin condition (using equality), but the defining characteristic is that it's a self join. The statement is syntactically valid and will execute successfully, returning each employee's lname and location paired with their dname and hiredate from the same row.