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

Auto commit occurs on

  1. On any DML

  2. On any DDL

  3. On both

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

Autocommit occurs automatically when DDL (Data Definition Language) statements like CREATE, ALTER, DROP are executed. DML (Data Manipulation Language) statements like INSERT, UPDATE, DELETE require explicit COMMIT unless autocommit mode is specifically enabled. DDL statements cannot be rolled back in Oracle, which is why they trigger automatic commits.

Multiple choice technology databases

What is the main reason to create a reverse key index on a column

  1. The column is populated by sequence

  2. The column contains many different values

  3. The column is mainly used for value range scans

  4. The columns implementing an inverted list attribute.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Reverse key indexes are specifically designed for columns populated by sequences. When sequences generate ascending values (1, 2, 3...), normal B-tree indexes cause contention on the rightmost leaf block. Reverse key indexes reverse the bytes of each value, distributing inserts across different leaf blocks and eliminating this hotspot. They're not ideal for range scans (option C) or for high-cardinality columns without sequential insert patterns.

Multiple choice technology databases

GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION;

  1. MANAGER must be a role.

  2. It allows the MANAGER to pass the specified privileges on to other users.

  3. It allows the MANAGER to create tables that refer to the STUDENT_GRADES table.

  4. It allows the MANAGER to apply all DML statements on the STUDENT_GRADES table.

  5. It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES table.

  6. It allows the MANAGER the ability to select from, delete from, and update the STUDENT_GRADES table.
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

The WITH GRANT OPTION clause allows the grantee (MANAGER) to both exercise the granted privileges (SELECT, INSERT, UPDATE on STUDENT_GRADES) and grant those same privileges to other users. Option B correctly describes the GRANT OPTION aspect, while option E correctly describes the privileges granted. MANAGER can be either a user or a role.

Multiple choice technology databases

Which best describes an inline view?

  1. A Schema Object

  2. A subquery that can contain an ORDER BY clause

  3. Another name for a view that contains group functions

  4. A subquery that is part of the FROM clause of another query
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

An inline view is a subquery that appears in the FROM clause of a SELECT statement and is treated as a table during query execution. It is not a schema object (that's a regular view), can contain ORDER BY, and is not specifically related to group functions.

Multiple choice technology databases

Which three are true?

  1. A MERGE statement is used to merge the data of one table with data from another.

  2. A MERGE statement replaces the data of one table with that of another.

  3. A MERGE statement can be used to insert new rows into a table.

  4. A MERGE statement can be used to update existing rows in a table.
Reveal answer Fill a bubble to check yourself
A,B,C Correct answer
Multiple choice technology platforms and products

For Short to medium sized lists that change frequently, the suitable option that should be specified in the property table is----

  1. Local list

  2. Field value

  3. Remote list

  4. Prompt list

  5. Class key value
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In Maximo property tables, 'field value' (using synonym/domain tables) is the best choice for small to medium-sized lists that change frequently. It offers good performance and easy maintenance. Local lists (A) are only for very small, static lists. Remote lists (C) are for external systems, prompt lists (D) require user input, and class key values (E) serve different purposes.

Multiple choice technology platforms and products

Which of the following rules can be used to run an activity whenever the value of a specified property changes

  1. Rule-Declare-Index

  2. Rule-Declare-OnChange

  3. Rule-Declare-Constraints

  4. Rule-Declare-Trigger
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Rule-Declare-OnChange is specifically designed in Pega to run activities automatically whenever the value of a specified property changes. Rule-Declare-Index is for improving database query performance, Rule-Declare-Constraints defines validation rules, and Rule-Declare-Trigger executes activities when objects are created, updated, or deleted - not specifically for property value changes.

Multiple choice technology databases

Which of the following are correct for a view

  1. a. View is a named SQL query stored in data dictionary

  2. b. View is a database object which stores data

  3. c. View is used for data hiding

  4. d. Views doesn’t occupy memory
Reveal answer Fill a bubble to check yourself
A,C,D Correct answer
Explanation

Views are virtual tables based on SQL queries stored in the data dictionary (option A correct). They do NOT store data themselves - data remains in underlying tables (option B incorrect). Views provide data hiding by restricting access to specific columns or rows (option C correct). Views don't occupy additional storage memory as they don't duplicate data (option D correct).

Multiple choice technology databases

Can we apply update, insert or delete on a view?

  1. True

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

DML operations (INSERT, UPDATE, DELETE) can be performed on some views, but not all. Updatable views must meet conditions: derived from a single table, no DISTINCT, GROUP BY, aggregates, or computed columns. The modifications affect the underlying base tables. Simple views are generally updatable, complex views may be read-only.

Multiple choice technology databases

There is a % sign in one field of a column. Say for example table test_chk has a column name1 which contains a value “desdf%dsf”. What will be the queries to find it?

  1. SELECT * FROM test_chk ;

  2. select * from test_chk where name1 like '%%\%' escape ''

  3. select * from test_chk where name1 like ' \ ' escape '';

  4. select * from test_chk where name1 like '%!%%' escape '!'
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Explanation

To find a literal '%' in SQL LIKE, you need an escape character. Option A (SELECT *) works but returns ALL rows inefficiently. Option D uses '!' as escape - '%!%%' means: any characters, then literal % (escaped as %!), then any characters. Option B is wrong (syntax error), and C doesn't match the pattern.