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

What type of join would you use to return records from one table, regardless of the existence of associated records in the related table?

  1. OUTER JOIN

  2. RIGHT JOIN

  3. INNER JOIN

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

An OUTER JOIN returns all records from one table regardless of whether there are matching records in the related table. INNER JOIN only returns matching records, while LEFT/RIGHT JOINs are specific types of outer joins.

Multiple choice technology databases

Which of the following dictionary views gives information about the position of a column in a primary key?

  1. ALL_PRIMARY_KEYS

  2. ALL_TABLES

  3. ALL_IND_COLUMNS

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

ALL_IND_COLUMNS provides information about index columns, including their position within the index. Since primary keys are implemented using indexes in Oracle, this view shows column positions within primary keys. Option A doesn't exist, option B shows table metadata, and option D shows constraint info but not column positions.

Multiple choice technology databases

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

  1. Commits

  2. Locks

  3. Savepoints

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

Locks are the transaction control mechanism that prevents concurrent updates - they 'lock' data so only one transaction can modify it at a time. Commits finalize changes, savepoints mark rollback points, and rollbacks undo changes - none prevent concurrent access like locks do.

Multiple choice technology databases

Which of the following methods should you use to increase the number of nullable columns for a table?

  1. Use the alter table statement

  2. Ensure that all column values are NULL for all rows

  3. First, increase the size of adjacent column datatypes, and then add the column

  4. Add the column, populate the column, and then add the not NULL constraint
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To add nullable columns to a table, you use ALTER TABLE ADD COLUMN - columns are nullable by default unless you specify NOT NULL. Option D would work for a NOT NULL column, but it's unnecessarily complex for nullable columns. Options B and C are incorrect approaches.

Multiple choice technology databases

Once defined, how long will a variable remain defined in SQL*Plus?

  1. Until the database is shut down

  2. Until the instance is shut down

  3. Until the statement completes

  4. Until the session completes
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In SQL*Plus, user-defined variables (created via DEFINE or ACCEPT) persist for the duration of the current SQL*Plus session. They are cleared only when explicitly undefined or when the session completes (exits).

Multiple choice technology web technology

Which of the following is a way to force a restriction?

  1. Create a conditional select using the appropriate database function.

  2. Infer a where clause using the where statement of an object's properties.

  3. Apply a restriction set to a group of users that forces a row level where clause.

  4. All of the above.

Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

All three methods are valid ways to enforce restrictions in database queries: conditional selects using database functions, inferred where clauses from object properties, and restriction sets applied to user groups at row level. Each technique serves different use cases.

Multiple choice technology web technology

When you have created or edited your object to include a restriction:

  1. Always view the inferred SQL to check whether the Select statement includes the restriction.

  2. Save and export your universe.

  3. Test it by making a query in the User module.

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

Testing the restriction by running an actual query in the User module is the essential validation step after creating or editing an object. While viewing inferred SQL (A) and exporting (B) are good practices, only testing confirms the restriction works correctly in practice.

Multiple choice technology web technology

Queries from universes containing SQL traps:

  1. Return fewer rows than expected.

  2. Return too many rows.

  3. Return incompatible objects error.

  4. Are correct only if an aggregate is applied
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SQL traps (fan traps, chasm traps) cause duplicate rows in query results by generating incorrect joins that multiply rows. This returns MORE rows than expected, not fewer. The issue is structural join problems, not incompatible objects or missing aggregates.

Multiple choice technology mainframe

Which of the following can NOT be done using the ALTER TABLE statement

  1. Add a new column

  2. Drop a check constraint

  3. Change a columns name

  4. Change the length of VARCHAR column
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The ALTER TABLE statement can add columns, drop constraints, and modify VARCHAR column lengths, but cannot rename a column directly. Column renaming typically requires a database-specific approach or dropping and recreating the column with the new name.