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

Once a sequence is created

  1. It is automatically used in all INSERT and UPDATE

  2. It is linked to a specific table.

  3. It belongs to a specific schema.

  4. It is automatically available to all users.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Sequences are independent database objects that belong to a specific schema (like tables, views, and other schema objects). They are not automatically used in any statements, not linked to specific tables, and not available to all users unless granted. A sequence must be explicitly referenced (e.g., NEXTVAL) and can be used across multiple tables.

Multiple choice technology databases

PRODUCT ======= Product ID Product Description Manufacturer ID MANUFACTURER ============ Manufacturer ID Manufacturer Name Referring to the above table, what type of relationship exists between the Product table and the Manufacturer table?

  1. Product - Many,Manufacturer - Many

  2. Product - One or Many,Manufacturer - One or Many

  3. Product - Many,Manufacturer - One

  4. Product - One,Manufacturer - One

  5. Product - One,Manufacturer - Many
Reveal answer Fill a bubble to check yourself
B Correct answer
Multiple choice technology databases

For performance, you denormalize your database design and create some redundant columns. Referring to the scenario above, what RDBMS construct can you use to automatically prevent the repeated columns from getting out of sync?

  1. Cursors

  2. Constraints

  3. Views

  4. Stored procedures

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

Triggers automatically execute in response to data changes (INSERT/UPDATE/DELETE), making them ideal for synchronizing denormalized data across multiple tables to prevent inconsistent redundant values.

Multiple choice technology databases

You are running a query against a relational database. Referring to the scenario above, what clause or command do you use in the query to help avoid a costly tablescan?

  1. GROUP BY clause

  2. INDEX command

  3. HAVING clause

  4. FROM clause

  5. WHERE clause [/quote]
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To avoid a costly tablescan, the database engine uses an index to locate rows quickly. The INDEX command creates this database object. The WHERE clause filters results but relies on indices to avoid scans, and clauses like GROUP BY or HAVING do not prevent table scans.

Multiple choice technology web technology

Given there is non null data for Login column of Employee table, Running the statement "SELECT [LOGIN] FROM EMPLOYEE WHERE [LOGIN] <> NULL " will give the correct result only after executing which statement?

  1. SET ANSI_NULLS ON

  2. SET ANSI_NULLS OFF

  3. SET CONCAT_NULL_YIELDS_NULL ON

  4. SET CONCAT_NULL_YIELDS_NULL OFF
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

When ANSI_NULLS is ON, the comparison operator '<>' does not work with NULL values - comparisons to NULL always return UNKNOWN. Setting ANSI_NULLS OFF allows the '<>' operator to work as expected with NULL comparisons. Options A and D are incorrect settings - CONCAT_NULL_YIELDS_NULL controls string concatenation behavior, not NULL comparisons.

Multiple choice technology databases

The query performance increases by using NOT operator.

  1. True

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

The NOT operator typically degrades query performance because it prevents efficient index usage and often requires scanning more rows. Query optimizers struggle to use indexes effectively with NOT conditions. Optimized queries avoid NOT and use equivalent positive conditions instead.

Multiple choice technology databases

If there are 3 persons with last names JOHN, J and JAMES how many of them are included in the result set of the query "SELECT LAST_NAME FROM STAFF WHERE LAST_NAME BETWEEN 'A' AND 'J'"

  1. 3

  2. 2

  3. 1

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

SQL BETWEEN is inclusive, so BETWEEN 'A' AND 'J' includes strings starting with both 'A' and 'J'. However, lexicographic ordering means 'JAMES' > 'J' (since 'JAMES' has more characters). Only 'JOHN' falls between 'A' and 'J' because 'J' alone matches the upper bound. The result is 1 row.

Multiple choice technology databases

NULL values are ingnored for all SQ: 2008 aggregate functions.

  1. True

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

Under the SQL standard, aggregate functions (like SUM, AVG, MIN, MAX, and COUNT) ignore NULL values in their evaluation, except for COUNT(). However, the prompt contains a typographical error ('ingnored', 'SQ: 2008'), and if COUNT is counted as an aggregate function, not *all ignore NULLs. Given standard exam content, the intended answer is True, but the statement has minor errors.

Multiple choice technology

Which are true about updating Sales order ?

  1. Changes to the order sets are always respected when changing a line in the set

  2. Defaulting rules can execute multiple lines

  3. All updates to a sales order must pass processing constraint rules except when canceling the order line

  4. Updating an order header after defaulting rules have been applied would have no effect on existing order lines.
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Multiple choice technology databases

The query performance increases by using NOT operator.

  1. True

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

Using the NOT operator in SQL queries DECREASES performance. NOT prevents the database from using indexes efficiently and forces additional negation processing. Queries using NOT (or !=, NOT IN, NOT EXISTS) are slower than their positive equivalents because they require evaluating and rejecting rows rather than directly matching them.

Multiple choice technology platforms and products

Which one of the following doesn’t represent a 'Transform' component of ETL?

  1. Rounding decimal values to Integers

  2. Removing special characters from a Text field

  3. Defining Primary Key constraint on a Table

  4. Assigning a default value to a field.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Defining a Primary Key constraint is a database schema operation, not a data transformation. The other options (rounding decimals, removing special characters, assigning default values) are all data transformations that modify the data itself during the ETL process. Constraints are structural, not transformational.