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

which of the following denote single value function used in SQL

  1. AVG ( )

  2. SQRT ( )

  3. COUNT ( )

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

Single-value functions (also called scalar functions) operate on individual values and return a single value. SQRT() is a classic example - it takes one number and returns its square root. AVG() and COUNT() are aggregate functions that operate on multiple rows. Option D 'All the Above' is incorrect because it mixes scalar and aggregate functions.

Multiple choice technology testing

Validating the API is depends on the

  1. Return value of the API.

  2. Updates that the API does to the database tables.

  3. Exceptions that are thrown by the API.

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

Comprehensive API validation requires checking all aspects: the return value verifies the output is correct, database updates confirm data persistence works properly, and exception handling ensures the API responds appropriately to errors. Each validation type catches different issues - return values check business logic, database updates verify data integrity, and exception testing confirms error handling.

Multiple choice technology testing

What are the Test Conditions for a database unit test?

  1. Scalar Value

  2. Row count

  3. Empty/not-empty result sets

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

Database unit tests in VSTT support multiple test conditions: Scalar Value checks verify specific return values from functions or procedures, Row count validates that queries return the expected number of rows, and Empty/not-empty result set conditions confirm whether a query returned any data. Each condition type serves different validation needs - scalar values test computed results, row counts verify data volume, and result set checks confirm data presence.

Multiple choice technology databases

Which normal forms require that non-key columns be functionally dependent solely on the primary key?

  1. Second

  2. Fifth

  3. Third

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

Third Normal Form (3NF) requires that all non-key attributes are functionally dependent only on the primary key, eliminating transitive dependencies where a non-key attribute depends on another non-key attribute. 2NF only addresses partial dependencies, while 5NF deals with join dependencies.

Multiple choice technology databases

Table with no clustered index is called

  1. AOL

  2. BOL

  3. DOL

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

In SQL Server, a table without a clustered index is called a heap. Data in a heap is stored without any organized order based on key values, unlike tables with clustered indexes where rows are physically sorted. The other options (AOL, BOL, DOL) are not SQL Server storage terminology.

Multiple choice technology databases

Which one of the following constraints enforces domain integrity?

  1. Primary key constraint

  2. Check constraint

  3. Foreign key constraint

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

Domain integrity ensures that data values fall within defined valid domains for each column. Check constraints enforce domain integrity by validating column values against specific conditions (e.g., age > 0, price BETWEEN 0 AND 1000). Primary key and unique constraints enforce entity integrity (row uniqueness), while foreign key enforces referential integrity between related tables.

Multiple choice technology databases

Sub-queries can be nested in…

  1. DELETE statements only.

  2. INSERT statements only.

  3. UPDATE statements only.

  4. UPDATE, DELETE, INSERT and SELECT statements.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

SQL subqueries can be nested within all major Data Manipulation Language statements. They provide powerful query capabilities in SELECT statements for filtering and projection, and can also be used in UPDATE, DELETE, and INSERT statements to determine which rows are affected or what values are inserted.

Multiple choice technology databases

Which is not parameters for STUFF function

  1. First string

  2. Second string

  3. Start position

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

The STUFF function in SQL Server uses four parameters: the source string (first string), start position (where deletion begins), length (number of characters to delete), and the replacement string (second string). There is no 'end position' parameter - the function uses length instead to determine how many characters to remove before inserting the replacement string.

Multiple choice technology databases

select trim(column_name1),left(column_name2,5),substring(column_name3,2,4) from Table1 - The result of the above statement is

  1. Blank removed Column_name1, first 5 character of column_name2,2 to 4 character part of string from Column_name3

  2. Blank removed Column_name1, last 5 character of column_name2,2 to 4 character part of string from Column_name3

  3. Blank removed Column_name1, first 5 character of column_name2 and 2,3,4,5 character part of string from Column_name3

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

The TRIM function removes leading and trailing blanks from column_name1. LEFT(column_name2,5) returns the first 5 characters from the left side. SUBSTRING(column_name3,2,4) extracts 4 characters starting from position 2, which gives characters at positions 2, 3, 4, and 5. Option A incorrectly states '2 to 4 character part' when it should be 4 characters starting from position 2.

Multiple choice technology databases

@@sqlstatus can't have any of the below values

  1. 1

  2. 3

  3. 2

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

The @@sqlstatus system variable in Sybase returns specific status codes: 0 for successful completion, 1 for an error occurring during execution, and 2 for a data truncation warning. The value 3 is not a valid return value for @@sqlstatus. Option D is incorrect because option B (3) is indeed not a possible value.

Multiple choice technology databases

Which is not part of "case" keyword in sybase

  1. End case

  2. When

  3. then

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

In Sybase CASE statements, the valid keywords are CASE, WHEN, THEN, ELSE, and END. 'End case' as two words is not the correct syntax - the proper keyword is just 'END' (or 'END CASE' is sometimes used but 'END' alone terminates the CASE). The question correctly identifies that 'End case' as a phrase is not part of the CASE keyword structure.