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

How many rows records the following query will display.. select * from TEMP where rownum in (1,3,3,2) N.R. Temp table has 30 records

  1. NULL

  2. 1 row

  3. 3 rows

  4. 4 rows

  5. 0 row
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

ROWNUM IN (1,3,3,2) behaves similarly to ROWNUM IN (1,2,3) after deduplication. The first row gets ROWNUM=1 (matches), second row gets ROWNUM=2 (matches), third row gets ROWNUM=3 (matches). Rows 4-30 get ROWNUM values >3 and don't match. Thus, 3 rows are returned.

Multiple choice technology databases

select '1'+'1' from dual ; The o/p would be..

  1. 2

  2. 1+1

  3. Error: invalid number

  4. Error: invalid Character

  5. Error: invalid identifier
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In Oracle, the '+' operator with strings causes implicit conversion to numbers. Both '1' strings are converted to numbers, then added. The result is 2 (numeric), not '11' (string concatenation would use ||). The implicit numeric conversion makes this a valid arithmetic operation.

Multiple choice technology databases

O/p of the below query ?? select 'a''a' from dual;

  1. aa

  2. a a

  3. a'a

  4. Error: invalid identifier

  5. Error: invalid character
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In Oracle SQL, a single quote within a string literal is escaped by doubling it. Two consecutive single quotes ('') represent one literal quote character. The string 'a''a' contains characters a, ', a - outputting a'a.

Multiple choice technology databases

select dummy from dual ?

  1. NULL

  2. Error: invalid identifier

  3. X

  4. Error: invalid SQL Statemet

  5. A
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Oracle's DUAL table contains a pseudo-column called DUMMY. The DUMMY column always contains the value 'X'. Selecting this column returns X - this is documented Oracle behavior for backwards compatibility.

Multiple choice technology databases

select * from dual ?

  1. A

  2. X

  3. NULL

  4. Error: Invalid SQL statement

  5. Error: Invalid Identifier
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In Oracle SQL, the DUAL table is a dummy table containing a single row with a single column DUMMY that has the value 'X'. Querying it with select * retrieves this default row and returns 'X'.

Multiple choice technology databases

select dummy from dual minus select * from dual what would be output ?

  1. No rows returned

  2. Error: invalid SQL statement

  3. Error: invalid identifier

  4. Error: syntax error

  5. 1 row
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The DUAL table contains exactly one column named DUMMY and one row with the value 'X'. Because select dummy from dual and select * from dual both return exactly the same single row (X), subtracting one from the other via MINUS yields an empty set with no rows.

Multiple choice technology databases

select version from dual ;

  1. Error: "VERSION": invalid identifier

  2. Error: "VERSION": invalid character

  3. Error: invalid SQL statement

  4. shows oracle database version

  5. Null
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

VERSION is not a column in the DUAL table. Selecting a non-existent column causes ORA-00904 invalid identifier error. To get version info, use V$VERSION or PRODUCT_COMPONENT_VERSION from the dictionary views, not DUAL.

Multiple choice technology

While using a lookup transformation , the columns which go to index cache are

  1. Columns specified in condition

  2. Columns specified other than the condition

  3. Primary key

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

In Informatica Lookup transformations, the columns used in the lookup condition are cached in the index cache for fast searching. Other columns go to the data cache. This optimization improves lookup performance by indexing on the join/condition columns.

Multiple choice technology databases

The o/p of the below query would be.. select nvl('abc','a') from dual union all select nvl('abc',0) from dual minus select replace('abc','a','a') from dual

  1. No rows returned

  2. abc

  3. Two rows of value 'abc'

  4. Error: Syntax error
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Both NVL queries return 'abc' (non-null value). The REPLACE query also returns 'abc'. UNION ALL combines the two 'abc' rows, then MINUS removes rows matching the REPLACE result. Since both are 'abc', MINUS removes everything, leaving no rows.

Multiple choice technology databases

select instr('Hi Partha Sarathi Ojha','a',6,-1) from dual; The output would be..

  1. Error: Syntax error

  2. 0

  3. 5

  4. Error: Argument '-1' is out of range
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

INSTR's fourth parameter (occurrence) cannot be negative - valid values are positive integers. The function throws ORA-01428 'argument out of range' when occurrence is -1. Negative values work for the third parameter (start position) to search backwards, but not for occurrence.

Multiple choice technology databases

Want to search character 'a' from last in the string 'Hi Partha Sarathi Ojha'. So what would be the query...

  1. select instr('Hi Partha Sarathi Ojha',last,'a') from dual

  2. select instr('Hi Partha Sarathi Ojha','a',-1) from dual

  3. select instr('Hi Partha Sarathi Ojha',left,'a') from dual

  4. select instr('Hi Partha Sarathi Ojha',-1,'a') from dual

  5. select instr('Hi Partha Sarathi Ojha','-a') from dual
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

INSTR with a negative start position (third parameter) searches backwards from the end of the string. Setting start to -1 means start searching from the last character. This finds the last occurrence of 'a' in the string.