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

Which of the following statements are correct?

  1. Every entity must have a primary key

  2. It is not necessary to define primary key for an Entity

  3. @Primary annotation is used for denoting a simple primary key

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

In JPA and entity frameworks, every entity MUST have a primary key to uniquely identify each instance. This is a fundamental requirement for entity mapping and database operations. Option B is incorrect because the primary key IS required. Option C is incorrect because @Primary is not a standard JPA annotation for simple primary keys (typically @Id is used). Option D is incorrect since not all statements are valid.

Multiple choice technology databases

Which Oracle access method is the fastest way for Oracle to retrieve a single row?

  1. Primary key access

  2. Access via unique index

  3. Table access by ROWID

  4. Full table scan
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Access by ROWID is the fastest method because ROWID represents the direct physical address of a row in the database file. Once the ROWID is known, Oracle can go directly to that physical location without any index traversal, block searching, or table scanning.

Multiple choice technology databases

To produce a meaningful result set without any cartesian products, what is the minimum number of conditions that should appear in the WHERE clause of a four-table join?

  1. 8

  2. 3

  3. 2

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

When joining N tables, you need at least N-1 join conditions to avoid Cartesian products. For 4 tables, this means 3 join conditions minimum. Each condition should properly link two tables, creating a connected join structure. With fewer conditions, some tables would remain unconnected, producing cross products.

Multiple choice technology databases

What best describes the relationship between indexes and SQL performance?

  1. Indexes are only used in special cases

  2. Indexes are used to make table storage more efficient

  3. Indexes rarely make a difference in SQL performance

  4. Indexes exist solely to improve query speed
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Database indexes are separate data structures designed specifically to speed up data retrieval operations, making query execution faster at the cost of additional storage and write overhead.

Multiple choice technology databases

Which is a major problem with SQL?

  1. SQL cannot support object-orientation

  2. The same query can be written in many ways, each with vastly different execution plans.

  3. SQL syntax is too difficult for non-computer professionals to use

  4. SQL creates excessive locks within the database
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SQL is non-procedural and declarative, meaning the optimizer decides how to execute queries. A major challenge is that the same logical query can be written in multiple ways, yielding different execution plans and performance profiles.

Multiple choice technology databases

INTERVAL PARTITIONING IS NOT SUPPORTED FOR INDEX-ORGANIZED TABLES.

  1. True

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

Index-Organized Tables (IOTs) in Oracle have specific partitioning restrictions. Interval partitioning, which automatically creates partitions based on a numeric or date range, cannot be applied to IOTs. IOTs support only certain partitioning methods like range or list partitioning, but the interval partitioning extension is not supported for them.

Multiple choice technology databases

IS IT POSSIBLE TO SPECIFY THE EXECUTION ORDER OF TRIGGERS IN ORACLE 10G

  1. True

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

Oracle 10g does not allow manual specification of trigger execution order. Triggers fire in a predetermined sequence: first statement-level triggers, then row-level triggers, and within those categories, the order is based on trigger creation time. The FOLLOWS clause for ordering triggers was introduced in Oracle 11g, not 10g.

Multiple choice technology embedded technologies

Which of the following can be a valid column name?

  1. Column

  2. 1966_Invoices

  3. Catch_#22

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

Oracle column names must start with a letter (A-Z or a-z) and can contain letters, numbers, and underscores. Option A 'Column' is a reserved keyword, Option B starts with a number, and Option D starts with '#'. Only Option C 'Catch_#22' is valid: it starts with a letter and the '#' symbol is allowed in non-leading positions.

Multiple choice technology databases

Need to create a trigger on the EMP table that monitors every row that is changed and place this info into the AUDIT_TABLE What type of Trigger to be used ?

  1. FOR EACH ROW trigger on the EMP table

  2. Statement-level Trigger on the EMP table

  3. FOR EACH ROW trigger on the AUDIT_TABLE table

  4. Statement-level Trigger on the AUDIT_TABLE
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To monitor every row change on a table, you need a row-level trigger using 'FOR EACH ROW' clause. This trigger fires once for each affected row during INSERT, UPDATE, or DELETE operations. A statement-level trigger fires only once per statement, regardless of how many rows are affected. The trigger must be on the source table (EMP), not the audit table.

Multiple choice technology databases

CREATE OR REPLACE TRIGGER update_emp AFTER UPDATE ON EMP BEGIN INSERT INTO AUDIT_TABLE values(user ,sysdate ); END ; you issue an update command in the EMP tables that result in a update of 10 rows. How many rows are inserted into the AUDIT_TABLE ?

  1. 1

  2. 10

  3. none

  4. Value equal to number of rows in EMP table
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

This is a statement-level trigger (no FOR EACH ROW clause). Statement-level triggers execute exactly once per triggering statement, regardless of how many rows are affected. Updating 10 rows still fires the trigger only once, inserting 1 row.

Multiple choice technology databases

Which statment about trigger is TRUE ?

  1. we can use Application Trigger to fire when a DELETE statement occurs

  2. we can use database Trigger to fire when a INSERT statement occurs

  3. we can use System event Trigger to fire when a DELETE statement occurs

  4. we can use INSTEAD OF trigger to fire when a INSERT statement occurs
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Database triggers are stored programs that execute automatically in response to specific events on a table or view. Option A is incorrect because Application Trigger is not a standard database trigger type. Option B is correct - database triggers CAN fire when an INSERT statement occurs (among other DML operations). Option C is incorrect - system event triggers fire for DDL or database events, not DML DELETE. Option D is incorrect - INSTEAD OF triggers fire on views, not directly on INSERT statements to tables.