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

In an Outer Join, what happens to columns that are not matched?

  1. All matched columns returns NULLS.

  2. All unmatched columns returns NULLS.

  3. All matched and unmatched columns returns NULLS.

  4. Cant say
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In an outer join operation, columns from the non-preserved side (the table that may not have matching rows) return NULL for unmatched rows. Matched columns from both sides return their actual values, not NULL. Option A is incorrect because matched columns don't return NULL - only the unmatched columns from the non-preserved table do.

Multiple choice technology databases

User_Source table contains code for which all Objects?

  1. Functions

  2. Package body

  3. Triggers

  4. Cursor
Reveal answer Fill a bubble to check yourself
A,B,C Correct answer
Explanation

The Oracle data dictionary view USER_SOURCE stores the source code for stored PL/SQL objects including Functions, Package bodies, Procedures, and Triggers. Cursors are not stored objects - they are declared within PL/SQL blocks and are not stored in USER_SOURCE. The question tests understanding of which code objects have their source persisted in the data dictionary.

Multiple choice technology web technology

What is Prepared Statements?

  1. Prepared statement is a precomplied SQL statement

  2. Prepared statement is a complied SQL statement

  3. Both of above

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

A Prepared Statement in JDBC is a precompiled SQL statement. Precompilation improves execution performance for repeated queries and provides strong protection against SQL injection.

Multiple choice technology mainframe

Modes of INSERT are?

  1. LOAD MODE

  2. INSERT MODE

  3. UPDATE MODE

  4. MODIFY MODE
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

In DL/I, INSERT operations can be performed in different modes. LOAD mode is used for bulk initial data loading, while UPDATE mode allows inserting records with update capabilities. Both LOAD MODE and UPDATE MODE are valid modes for INSERT operations in DL/I.

Multiple choice technology databases

How many columns are presented after executing this query:SELECT address1||','||address2||','||address2 "Adress" FROM employee;

  1. 1

  2. 2

  3. 3

  4. 0

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

The || operator concatenates strings in SQL. The entire expression address1||','||address2||','||address2 is treated as a single column with the alias 'Adress'. No matter how many columns or literals you concatenate, the result is always one column in the output.

Multiple choice technology databases

To produce a meaningful result set without any cartesian products, what is the minimum number of conditions that should appear in the WHERE clause of a four-table join?

  1. 8

  2. 2

  3. 3

  4. 4

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

To avoid cartesian products when joining n tables, you need at least n-1 join conditions in the WHERE clause. This ensures each new table is linked to the previous ones in a chain. For 4 tables, you need 4-1 = 3 conditions.

Multiple choice technology databases

Assuming today is Monday, 10 July 2000, what is returned by this statement: SELECT to_char(NEXT_DAY(sysdate, 'MONDAY'), 'DD-MON-RR') FROM dual;

  1. 03-JUL-00

  2. 10-JUL-00

  3. 12-JUL-00

  4. 11-JUL-00

  5. 17-JUL-00
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

To solve this question, the user needs to understand the function of each element of the SQL statement and how it affects the output.

The SQL statement is selecting a date and formatting it according to the given format string. Specifically:

  • sysdate returns the current date and time.
  • NEXT_DAY(sysdate, 'MONDAY') returns the next occurrence of the day specified as the second argument, after the current date and time (sysdate). In this case, it returns the next Monday after 10 July 2000, which is 17 July 2000.
  • to_char() converts the resulting date into a string using the format specified as the second argument. In this case, the format is 'DD-MON-RR', which returns the day of the month (DD), the abbreviated month name (MON), and the 2-digit year (RR).

Therefore, the correct answer is E. 17-JUL-00, as the SQL statement returns the formatted date of the next Monday after 10 July 2000, which is 17 July 2000.

Multiple choice technology databases

The primary key on table EMP is the EMPNO column. Which of the following statements will not use the associated index on EMPNO?

  1. select * from EMP where nvl(EMPNO, '00000') = '59384'

  2. select * from EMP where EMPNO = '59384'

  3. select EMPNO, LASTNAME from EMP where EMPNO = '59384'

  4. select 1 from EMP where EMPNO = '59834'
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The NVL function wraps the indexed column EMPNO, which prevents Oracle from using the index on EMPNO. When a function is applied to a column in the WHERE clause, the index on that column cannot be used unless a function-based index exists. Options B, C, and D directly compare EMPNO, allowing index usage.