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

Tables must always be assigned a Primary Index at creation

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In Teradata, every table must have a Primary Index defined at creation time. The Primary Index determines how data is distributed across AMPs (Access Module Processors) and is fundamental to Teradata's parallel architecture. This is a mandatory requirement, not optional.

Multiple choice technology databases

Primary Index can not be

  1. Null

  2. dropped

  3. have duplicates

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

Once defined, a Primary Index in Teradata cannot be dropped or altered without recreating the table. However, a Primary Index can contain NULL values and can have duplicate values (in a Non-Unique Primary Index). The 'cannot be dropped' constraint is permanent and fundamental to Teradata's architecture.

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

_____ is dropped when the table or tablespace is dropped

  1. SYNONYM

  2. ALIAS

  3. All of the above

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

In DB2, an alias is defined on a table. If the underlying table or its tablespace is dropped, the alias is not automatically dropped; it remains but becomes invalid. Conversely, a synonym is automatically dropped when its underlying table is dropped.

Multiple choice technology databases

A collection of named objects is called ______

  1. Schema

  2. Table

  3. Alias

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

A schema is a logical container or collection of named database objects, such as tables, views, and indexes, owned by a particular database user. Tables and aliases are individual objects within a schema, making them incorrect.

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.