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

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.

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.