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 packaged enterprise solutions

After running an EIM task, if the value of IF_ROW_STAT is “FOREIGN KEY”. It means that the import of the record has failed because required column has null value.

  1. True

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

IF_ROW_STAT value of 'FOREIGN KEY' indicates that the record failed because a referenced foreign key value does not exist in the target table. Null values in required columns typically result in different status codes like 'REQUIRED' or 'MISSING'. The FOREIGN KEY status specifically points to parent record lookup failures.

Multiple choice technology packaged enterprise solutions

Exists() query operator is used to search for a pattern in the dynamic picklist.

  1. True

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

The Exists() operator checks for the existence of records matching search criteria, not for patterns within picklists. For pattern searching in dynamic picklists, operators like Like() or wildcard characters are used. Exists() returns boolean results about record presence, not pattern matching.

Multiple choice technology mainframe

What is the table name to see IBS ordering days

  1. IBS_ORDR_ST_DTL

  2. LOB_ORDR_TYP_DMN

  3. IBS_ORDR_ST_DTL_H

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

The table IBS_ORDR_ST_DTL follows standard naming conventions where IBS stands for the system, ORDR for order, ST for state/status, and DTL for detail. This table stores ordering day status information. Option C adds _H which typically denotes history tables, while option B is for order type domain/master data.

Multiple choice technology databases

Which of the following statements about subqueries are true? (Choose 3)

  1. Subqueries can have up to 16 levels of nesting

  2. Subqueries can have up to 50 levels of nesting

  3. Subqueries returning only one row can follow “=” in a WHERE clause

  4. Subqueries returning only one row can follow “IN” in a WHERE clause
Reveal answer Fill a bubble to check yourself
A,C,D Correct answer
Explanation

Subqueries can be nested up to 16 levels deep in SQL Server. Single-row subqueries can follow = operator in WHERE clauses, while both single and multiple-row subqueries can follow IN operator. Options A, C, and D are all valid statements about subquery behavior.

Multiple choice technology databases

For which of the following queries may a merge join be considered?

  1. Outer joins

  2. Multi-table updates and deletes

  3. Equi-joins

  4. Equi-join with a text column in select list
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Merge joins require sorted input datasets on the join keys, which means they are exclusively considered for equi-joins containing equality predicates. They cannot be used for non-equi joins, and text columns cannot be sorted, ruling out the other options.

Multiple choice technology databases

A UNION of two or more queries is valid provided the queries meet which of the following conditions? (Choose 3)

  1. All select lists in the UNION must have the same number of expressions

  2. Corresponding columns must have the same column names

  3. Corresponding columns must be of the same datatype or the server must be able to implicitly convert them

  4. Corresponding columns must be in the same order
Reveal answer Fill a bubble to check yourself
A,C,D Correct answer
Explanation

For UNION queries to be valid, all SELECT statements must have the same number of columns (A), corresponding columns must be compatible datatypes (C), and columns must be in the same order (D). Column names need not match (B is false).

Multiple choice technology databases

Choose the correct statements about the union clause in ASE (Choose 2)

  1. Duplicate rows are removed from the result set by default

  2. ASE evaluates a statement containing union operators from left to right

  3. Order by and group by clauses are not allowed in a union query

  4. The into clause can be used only in the first query
Reveal answer Fill a bubble to check yourself
A,B,D Correct answer
Explanation

In ASE, UNION removes duplicate rows by default (A), evaluates left-to-right (B), and allows INTO clause only in the first query to specify the result table (D). ORDER BY and GROUP BY are allowed (C is false).

Multiple choice technology databases

Which of the following are true about triggers? (Choose 2)

  1. the inserted and deleted tables are created manually

  2. triggers can be disabled using alter table

  3. a single trigger can be created for both insert and delete

  4. @@sqlstatus contains the number of rows affected
Reveal answer Fill a bubble to check yourself
B,C Correct answer
Explanation

Triggers in ASE can be disabled using ALTER TABLE, and a single trigger can handle multiple DML operations (INSERT, UPDATE, DELETE) as shown in the CREATE TRIGGER syntax. The inserted and deleted tables are system-created temporary tables, not manually created. @@sqlstatus contains error codes, not row counts (that's @@rowcount).

Multiple choice technology databases

Table t and trigger trig are created as follows: create table t (a int) go create trigger trig on t for insert, update, delete as begin if @@rowcount > 1 rollback trigger end go The following transaction is now executed: begin tran insert t values (1) insert t values (2) insert t select * from t insert t values (3) commit go After completing this transaction, how many rows will table t contain?

  1. 0

  2. 1

  3. 2

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

The trigger rolls back when @@rowcount > 1, preventing multi-row inserts. The first two single-row inserts (1, 2) succeed. The multi-row insert 'insert t select * from t' tries to insert 2 rows, causing rollback. The final insert (3) succeeds. Result: rows 1, 2, 3.

Multiple choice technology databases

Which T-SQL elements can be used in a stored procedure? (Choose 2)

  1. 'return', without a return value

  2. 'rollback transaction'

  3. 'rollback trigger'

  4. the built-in function 'update()'
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

RETURN without a value is valid in stored procedures for control flow. ROLLBACK TRANSACTION can be used within procedures to manage transactions. ROLLBACK TRIGGER is only valid within triggers, not procedures. update() is a trigger-specific function unavailable in procedures.

Multiple choice technology databases

The following trigger exists: create trigger trig on t for insert, update, delete as begin print 'This is 'trig' ' end The following command is now executed: create trigger trig on t for insert as begin print 'This is 'trig' ' end After executing this command, for which DML actions on table 't' will a trigger be fired?

  1. insert

  2. update

  3. insert, update, delete

  4. delete,update
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Creating a trigger with an existing name on the same table replaces its definition in database systems like Sybase ASE. Because the second command specifies only for insert, it overwrites the original multi-action trigger, leaving a trigger that only fires on insert operations.

Multiple choice technology

Which one of the below table is not an FAS transaction table?

  1. tmw_clmtrxn

  2. tmw_postmsg

  3. tmw_ogiswork

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

tmw_prcsctrl_eur is not an FAS transaction table, while tmw_clmtrxn, tmw_postmsg, and tmw_ogiswork are valid FAS transaction tables. The prcsctrl_eur table appears to be a control or processing table rather than a transaction table.