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 programming languages

Carl, a database user, creates and executes the following SQL statement: CREATE SYNONYM employee FOR stanley.employee; What statements below are true?

  1. Only Carl can access the EMPLOYEE synonym.

  2. The EMPLOYEE synonym is a public synonym.

  3. The synonym name cannot be the same as the object name.

  4. Stanley can access the EMPLOYEE synonym.

  5. The synonym can be accessed remotely.

  6. The EMPLOYEE synonym is a private synonym.
Reveal answer Fill a bubble to check yourself
D,F Correct answer
Explanation

The CREATE SYNONYM statement without the PUBLIC keyword creates a private synonym that only Carl (the creator) and Stanley (the object owner) can access. Since 'PUBLIC' was not specified, this is a private synonym, not a public one. Option A is incorrect because Stanley can also access it, option B is incorrect because it's private, option C is incorrect because synonym names can match object names, and option E is incorrect because this is a local synonym.

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

The B37 abend code during SPUFI (SQL Processor Using File Input) execution indicates insufficient disk space to allocate the required dataset. This is a space-related error, not a data integrity or query result issue. Options B, C, and D describe different types of issues that would produce different abend codes.

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 holding a Share (S) lock on a row attempts to update that row, DB2 automatically attempts to convert the existing row-level Share lock into an Update (U) or Exclusive (X) lock to perform the modification.

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 that a column does not accept NULL values and enforces uniqueness, making it eligible to be referenced by a foreign key from another table. CHECK constraints validate values against a condition, DEFAULT constraints provide default values, and informational constraints are not enforced. Option A is for validation rules, option B provides default values, and option D represents non-enforced constraints.

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

To create a table in a database, a user must be granted the CREATETAB privilege. This specific privilege allows the user to define new tables within their schema or database.

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 values up to 9999999.999 (7 digits before decimal, 3 after), DECIMAL(10,3) provides the needed precision with 10 total digits and 3 decimal places. INTEGER has no decimals, REAL is approximate floating-point, and NUMERIC(7,3) only allows 4 digits before the decimal (9999.999 max), insufficient for the required range. Options A, B, and C cannot represent the full range of required values.

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 definition of col2 contains WITH DEFAULT NONE, which is invalid syntax in DB2. If you specify NOT NULL, you cannot combine it with WITH DEFAULT NONE in this manner, causing the CREATE TABLE statement to fail.

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.