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

Consider this syntax MERGE INTO t1 USING t2 ON (join predicate) .... What does the MERGE syntax do ?

  1. It performs a merge join of the row from T2 only if it does'nt exist in the T1 table.

  2. It creates a natural join of table T1 and T2 for all columns that have the same name.

  3. It creates a Cartesian prodcut of table T1 and table T2 for all columns that have the same name.

  4. For each row from T2, it updates the row if it exists witin table T1, otherwise it inserts the row into T1.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

MERGE performs an upsert operation: for each row from the source (T2), it updates the target row if it exists in T1 based on the join condition, or inserts the row if it doesn't exist. MERGE is not a join type - it's a data manipulation command. It doesn't create natural joins or Cartesian products. The ON clause specifies the matching condition between source and target tables.

Multiple choice technology databases

You need to enforce these two business rules: 1. No two rows of a table can have duplicate values in the specified column. 2. A column cannot contain null values. Which type of constraint ensure that both of the above rules are true ?

  1. Check

  2. Unique

  3. Not Null

  4. Primary Key

  5. Foreign Key
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A Primary Key constraint enforces both uniqueness (no duplicate values in a column or set of columns) and the not-null constraint (columns in a primary key cannot contain nulls). A Unique constraint allows nulls, Not Null does not enforce uniqueness, and Check and Foreign Key constraints enforce different rules.

Multiple choice technology databases

Pick the odd one out

  1. PLAN_TABLE

  2. EMPLOYEE_MASTER

  3. DSN_STATEMNT_TABLE

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

SYSTABLES is a DB2 system catalog table that automatically stores metadata about all tables in the database. The others (PLAN_TABLE, EMPLOYEE_MASTER, DSN_STATEMNT_TABLE) are either user-created or auxiliary tables for specific purposes like explain plans.

Multiple choice technology programming languages

select all the valid values for collation

  1. sqlupper

  2. sqlvarchar

  3. exact

  4. mvr

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

Valid collation values in Cache ObjectScript are sqlupper (case-insensitive comparison), exact (case-sensitive exact match), and mvr (numeric range). Sqlvarchar and truncate are not valid collation values in the system. Collation determines how string comparison and sorting operations are performed.

Multiple choice technology databases

What is the default character for specifying substitution variables in select statements?

  1. Ellipses

  2. Quotation marks

  3. Ampersand

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

In SQL*Plus and Oracle SQL Developer, the ampersand character (&) is the default prefix used to denote substitution variables. When the SQL parser encounters an ampersand, it prompts the user for a value, making this the correct option while other symbols are incorrect.

Multiple choice technology databases

You are managing constraints on a table in Oracle. Which of the following choices correctly identifies the limitations on check constraints?

  1. Values must be obtained from a lookup table.

  2. Values must be part of a fixed set defined by create or alter table.

  3. Values must include reserved words, such as SYSDATE and USER.

  4. The column cannot contain a NULL value.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A check constraint restricts the values that can be entered into a column. It must be defined as a fixed condition during table creation or alteration. Check constraints cannot reference external lookup tables, pseudo-columns like SYSDATE, or enforce non-null values directly without specific syntax.

Multiple choice technology databases

The transaction control that prevents more than one user from updating data in a table is which of the following?

  1. lock

  2. commit

  3. rollback

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

The LOCK transaction control prevents concurrent users from updating the same data simultaneously. Unlike commit (save changes), rollback (undo changes), or savepoint (mark a rollback point), lock actually restricts access to prevent conflicts during multi-user scenarios. It ensures data consistency by serializing access to the data.

Multiple choice technology databases

You are using SQL operations in Oracle. All of the following DATE functions return a DATE datatype except one. Which one is it?

  1. NEW_TIME

  2. LAST_DAY

  3. ADD_MONTHS

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

The MONTHS_BETWEEN function calculates the number of months between two dates, returning a numeric value rather than a DATE datatype. Functions like NEW_TIME, LAST_DAY, and ADD_MONTHS all perform date calculations and return a DATE datatype, making the numeric function the correct exception.