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 performance

The Oracle optimizer should be controlled in choosing the type of joins

  1. True

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

Modern Oracle optimizers are cost-based and generally choose the most efficient join methods based on statistics. Explicitly controlling join types (using hints) is rarely necessary and can degrade performance if not done carefully. The optimizer should be trusted unless there's a proven performance issue.

Multiple choice technology programming languages

2) Expand ROW

  1. A) Row of lock

  2. B) Row of locking

  3. C) Row level lock

  4. D) Row level locking
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

ROW in database context typically stands for Row Level Locking, a mechanism that locks individual rows rather than entire tables during transactions. This allows higher concurrency by letting multiple transactions access different rows simultaneously.

Multiple choice technology databases

What is the proper syntax for assigning a column alias?

  1. SELECT column_name ALIAS alias_name FROM table_name;

  2. SELECT column_name alias_name FROM table_name;

  3. SELECT alias_name FROM table_name;

  4. ASSIGN alias_name TO column_name;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In SQL, a column alias is assigned simply by placing the alias name after the column name, optionally with the AS keyword. The syntax 'SELECT column_name alias_name' is correct. Option A shows incorrect ALIAS keyword, and Option C just selects the alias as a column name.

Multiple choice technology databases

Which of the following are reasonable series of transaction-control commands?

  1. Insert some records, COMMIT, ROLLBACK

  2. SAVEPOINT, insert some records, ROLLBACK, ROLLBACK, COMMIT

  3. ROLLBACK, insert some records, COMMIT

  4. ROLLBACK, insert some records, COMMIT, COMMIT
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Option C is valid: ROLLBACK with no active transaction is a no-op (does nothing harmlessly), then insert records, then COMMIT saves them. Option A is invalid because COMMIT then ROLLBACK makes no sense - records are already committed. Option D has redundant COMMIT. Option B has improper ROLLBACK sequence.

Multiple choice technology programming languages

Creating a foreign-key constraint between columns of two tables defined with two different datatypes will produce an error.

  1. True

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

In relational databases like Oracle or SQL Server, referencing a primary key with a foreign key requires the columns to have matching or compatible datatypes. Creating a constraint between different incompatible datatypes results in a compilation error.

Multiple choice technology programming languages

SELECT (TO_CHAR(NVL(SQRT(59483), “INVALID”)) FROM DUAL is a valid SQL statement

  1. True

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

This SQL statement has multiple syntax errors making it invalid: the TO_CHAR function is missing a closing parenthesis, and the quotes around 'INVALID' are curly smart quotes instead of straight ASCII quotes. A correct version would be SELECT TO_CHAR(NVL(SQRT(59483), 'INVALID')) FROM DUAL; with proper parentheses and straight quotes.

Multiple choice technology programming languages

The NVL function

  1. Place the alias at the beginning of the statement to describe the table.

  2. Place the alias after each column, separated by white space, to describe the column.

  3. Place the alias after each column, separated by a comma, to describe the column

  4. Place the alias at the end of the statement to describe the table.
Reveal answer Fill a bubble to check yourself
C Correct answer
Multiple choice technology programming languages

Issuing the DEFINE_EDITOR=”emacs” will produce which outcome?

  1. The emacs editor will become the SQL*Plus default text editor.

  2. The emacs editor will start running immediately.

  3. The emacs editor will no longer be used by SQL*Plus as the default text editor.

  4. The emacs editor will be deleted from the system.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Defining DEFINE_EDITOR in SQL*Plus sets the default text editor used by the EDIT command. Therefore, setting it to 'emacs' makes emacs the default editor for editing SQL buffers, but does not start it immediately.

Multiple choice technology mainframe

Which of the following statement falls under Data Control Language ?

  1. CREATE

  2. INSERT

  3. DROP

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

Data Control Language (DCL) commands manage permissions and access control. GRANT gives users privileges, while REVOKE removes them. CREATE, INSERT, and DROP are Data Definition Language (DDL) commands that create/modify database structures and manipulate data - they do not control access permissions.

Multiple choice technology mainframe

Which of the following error code is displayed when the column name is not a column of table name?
    • 205
    • 206
    • 207
    • 208
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Error code -205 (or 205) typically indicates an invalid column name referenced in a SQL statement - the column specified does not exist in the table. Error codes -206, -207, and -208 represent other SQL errors like undefined objects or descriptor issues depending on the specific DBMS. The exact mapping varies by database system.