Computer Knowledge

Database and SQL

3,929 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

COUNT(DISTINCT expr) returns

  1. No. of rows in a table

  2. No. of rows with non-null values of the expr

  3. No. of distinct non-null values of the expr

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

COUNT(DISTINCT expr) counts the number of unique, non-null values in the specified expression. NULL values are ignored, and duplicate values are counted only once. This is different from regular COUNT(*) which counts all rows including NULLs in some contexts.

Multiple choice technology databases

Which function forces group functions to include null values?

  1. Group By

  2. NVL

  3. Order By

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

Aggregate functions like COUNT, SUM, AVG ignore NULL values by default. The NVL function converts NULL values to a specified value, effectively forcing them to be included in the aggregation. GROUP BY, ORDER BY, and HAVING are clauses, not functions for handling NULL values.

Multiple choice technology databases

Which is the clause used to restrict groups?

  1. HAVING

  2. WHERE

  3. Order By

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

HAVING is used to filter groups after aggregation, similar to how WHERE filters individual rows before aggregation. You cannot use WHERE with aggregate functions - HAVING is the correct clause for restricting groups based on aggregate conditions.

Multiple choice technology databases

We can use Group functions in the WHERE clause

  1. True

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

Group functions (aggregate functions) cannot be used in the WHERE clause because WHERE operates on individual rows before grouping. To filter based on aggregate values, you must use HAVING after the GROUP BY clause. This is a fundamental SQL execution order rule.

Multiple choice technology databases

Delete from Employee where grade = ‘Y’” deletes

  1. All rows of the Employee table

  2. Rows of the Employee table where the set grade is ‘Y’

  3. Delete 1 row

  4. Nothing will be deleted
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The DELETE statement with a WHERE clause removes only those rows that satisfy the condition. In this case, only employees whose grade column contains 'Y' will be deleted. All other rows remain untouched. The DELETE without WHERE would remove all rows.

Multiple choice technology databases

Which keyword used in DML statements prohibits you from changing rows that are not in the sub-query?

  1. CHECK keyword

  2. WITH CHECK keyword

  3. WITH CHECK OPTION keyword

  4. None of the Above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

WITH CHECK OPTION is used with updatable views to prevent modifications that would make rows invisible to the view. It ensures that INSERT or UPDATE operations result in rows that still satisfy the view's defining query, maintaining data integrity and view consistency.

Multiple choice technology databases

Explicit Defaults can be used in

  1. INSERT statement

  2. UPDATE statement

  3. Both of the Above

  4. None of the Above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Explicit defaults can be specified in both INSERT and UPDATE statements. In INSERT, you can explicitly set a column to its default value. In UPDATE, you can reset a column back to its default value using the DEFAULT keyword. This provides flexibility in managing default values during data manipulation.

Multiple choice technology databases

Which is not a group function?

  1. Count

  2. Avg

  3. Total

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

In Oracle SQL, the standard group (aggregate) functions are COUNT, AVG, MAX, MIN, SUM, STDDEV, VARIANCE, etc. 'Total' is NOT a valid Oracle group function, making it the correct answer to 'which is NOT a group function'. While some other databases might have a TOTAL function, Oracle does not.

Multiple choice technology databases

In a join condition it is mandatory to specify the table name along with the Column name.

  1. True

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

In join conditions, specifying table names is NOT mandatory when column names are unambiguous. If a column name exists in only one of the joined tables, you can reference it directly without the table prefix. Table prefixes (or aliases) are only required when column names are ambiguous (exist in multiple tables). The claimed answer 'False' is correct.

Multiple choice technology databases

The minimum number of join statements required to join 10 tables together are:

  1. 9

  2. 11

  3. 5

  4. 12

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

To join N tables together, you need at minimum N-1 join conditions. This is because each join reduces the number of unconnected tables by one. Starting with 10 tables, you need 9 joins to connect them all into a single result set. For example: (T1 JOIN T2) JOIN T3 ... JOIN T10 requires 9 joins.