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 is the output: select mod(1600,300) from dual;

  1. 300

  2. 100

  3. 500

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

The MOD function returns the remainder when the first number is divided by the second. Here, 1600 divided by 300 equals 5 with a remainder of 100 (since 300 × 5 = 1500). Option B correctly shows 100 as the remainder. Options A and C (300 and 500) are the divisor and a multiple respectively, while D (000) is just a formatting variation of zero.

Multiple choice technology databases

Which method returns the number of rows in a set.

  1. sum

  2. num_rows

  3. count

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

The COUNT() function is specifically designed to return the number of rows in a result set or table. SUM adds numeric values, num_rows is not a standard SQL function (it may exist in specific programming interfaces), and 'total' is not a built-in SQL function. COUNT is the universal SQL method for row counting.

Multiple choice technology databases

An application developer needs to write a PL/SQL code, wherein salary raise to be processed for all the employees of grade "C2" and above. As per the Oracle recommended standards, Which PL/SQL objects should be part of his application code? Choose all that apply

  1. Package

  2. Stored Procedure

  3. Cursor

  4. Stored Function

  5. View

  6. Trigger
Reveal answer Fill a bubble to check yourself
A,B,C,E Correct answer
Explanation

For processing salary raises for employees meeting a grade criteria, a Stored Procedure encapsulates the business logic, a Package organizes related procedures/functions, a Cursor iterates through employee rows, and a View can filter employees by grade C2 and above. Stored Functions return values (not ideal for DML operations), Triggers fire automatically (not for scheduled processing). The correct answers are A, B, C, E.

Multiple choice technology databases

As per the business requirement of a Shipping firm, Inventory Manager should be notified when the Inventory Count for a category "CONTAINER" in the INVENTORY table drops down below 100. However, the user transaction should NOT be aborted, in case the inventory count for a "CONTAINERS" category drops down below 100. The columns of the INVENTORY table, which stores the category name and count are CAT_NAME and TOTAL_COUNT respectively. Specify, which type of TRIGGER an application developer needs to write to address this issue?

  1. Trigger Event - UPDATE of TOTAL_COUNT, Trigger Type - BEFORE ROW, Trigger Restriction - NULL

  2. Trigger Event - UPDATE of TOTAL_COUNT, Trigger Type - BEFORE ROW, Trigger Restriction - WHERE CAT_NAME='CONTAINER' AND NEW.TOTAL_COUNT<100

  3. Trigger Event - UPDATE of TOTAL_COUNT, Trigger Type - BEFORE STATEMENT, Trigger Restriction - NULL

  4. Trigger Event - UPDATE of TOTAL_COUNT, Trigger Type - BEFORE STATEMENT, Trigger Restriction - WHERE CAT_NAME='CONTAINER' AND NEW.TOTAL_COUNT<100

  5. Trigger Event - UPDATE of TOTAL_COUNT, Trigger Type - AFTER ROW, Trigger Restriction - NULL

  6. Trigger Event - UPDATE of TOTAL_COUNT, Trigger Type - AFTER ROW, Trigger Restriction - WHERE CAT_NAME='CONTAINER' AND NEW.TOTAL_COUNT<100
Reveal answer Fill a bubble to check yourself
F Correct answer
Explanation

The requirement needs notification WITHOUT aborting the transaction, which means AFTER ROW trigger (not BEFORE, which could validate/abort). AFTER ROW fires after the row is successfully updated but before the transaction commits, allowing notification logic to run. The restriction WHERE CAT_NAME='CONTAINER' AND NEW.TOTAL_COUNT<100 filters to only the relevant category and threshold. Option F is correct: AFTER ROW trigger with proper restriction.

Multiple choice technology databases

Identify the BOOLEAN variables in the context of the TRIGGER. Choose all that apply.

  1. UPDATING

  2. CREATING

  3. DELETING

  4. SELECTING

  5. INSERTING

  6. ALTERING
Reveal answer Fill a bubble to check yourself
A,C,E Correct answer
Explanation

PL/SQL provides three boolean predicates for trigger context: INSERTING (true if trigger fired due to INSERT), UPDATING (true if due to UPDATE), and DELETING (true if due to DELETE). These help write logic that behaves differently based on the triggering event. CREATING, SELECTING, and ALTERING are NOT valid PL/SQL boolean trigger variables.

Multiple choice technology databases

SQLCODE and SQLERRM cannot be used within a SQL statement directly. Instead, you must assign their values to local variables, then use the variables in the SQL statement

  1. True

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

In PL/SQL, functions SQLCODE and SQLERRM cannot be referenced directly within SQL DML statements. They must first be assigned to local PL/SQL variables, which can then be safely used inside SQL queries or statements.

Multiple choice technology databases

Stored Procedures and stored function are the same, except

  1. Stored function returns a value and a stored procedure does not.

  2. Stored procedure returns a value and a stored function does not.

  3. Stored function returns a value and a stored procedure may OR may not.

  4. Stored procedure returns a value and a stored function may OR may not.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The fundamental difference is that a stored function MUST return a single value, while a stored procedure does NOT return a value (it can have OUT parameters but no RETURN statement). Option A captures this precisely. Option C is misleading because stored functions always return a value - they cannot optionally return or not return.

Multiple choice technology databases

What is meant by cascading in the context of the database triggers?

  1. When a trigger fires, a SQL statement within its trigger action potentially can fire other triggers

  2. When a trigger fires, a SQL statement within its trigger action potentially can update other tables

  3. When a trigger fires, a SQL statement within its trigger action potentially can fire other stored procedures

  4. When a trigger fires, a SQL statement within its trigger action potentially can drop other tables
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Cascading in database triggers refers to the domino effect where one trigger's action fires another trigger, which may fire yet another, creating a chain reaction. Option A correctly defines this phenomenon. Options B, C, and D describe normal database operations (updates, procedure calls, drops) rather than the specific cascading behavior of trigger chains.

Multiple choice technology databases

A stored procedure must have at least one argument

  1. True

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

A stored procedure can have ZERO parameters - it is entirely valid to create a procedure that takes no arguments and performs fixed operations. Many utility procedures are parameterless. The statement 'must have at least one argument' is false, making Option B correct.

Multiple choice technology databases

Primary difference between a stored procedure and stored function is that, function must return a value through an explicit return statement wherein stored procedure does NOT.

  1. True

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

Functions must return a value using an explicit RETURN statement in SQL. Stored procedures can modify data and return values through OUT parameters, but they are not required to return a value. This is a fundamental distinction in database programming.

Multiple choice technology databases

DDL statements are allowed in the database triggers

  1. True

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

DDL statements (CREATE, ALTER, DROP, TRUNCATE, RENAME) are NOT allowed in database triggers because triggers execute within the context of a transaction and DDL operations issue implicit commits. This would break the transactional integrity. Only DML and DCL are permitted.

Multiple choice technology databases

An application developer has written a database package for an Order Entry application using SQL Plus editor. While compiling the package, he receives errors. What will be the next step to troubleshoot the error?

  1. Execute the command FIX ERROR

  2. Execute the command SHOW ERROR

  3. Execute the command TROUBLESHOOT ERROR

  4. Execute the command INVESTIGATE ERROR

  5. Execute the command ANALYZE ERROR
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In SQL*Plus, the SHOW ERRORS (or SHOW ERROR) command displays compilation errors for the most recently compiled PL/SQL block or database object. Commands like FIX, TROUBLESHOOT, INVESTIGATE, or ANALYZE error are not valid SQL*Plus instructions.

Multiple choice technology databases

A database TRIGGER is made up of ... Choose all that apply

  1. TRIGGER SPECIFICATION

  2. TRIGGER EVENT

  3. TRIGGER TYPE

  4. TRIGGER BODY

  5. TRIGGER RESTRICTION

  6. TRIGGER ACTION
Reveal answer Fill a bubble to check yourself
B,C,E,F Correct answer
Explanation

A database trigger consists of TRIGGER EVENT (INSERT/UPDATE/DELETE), TRIGGER TYPE (BEFORE/AFTER/INSTEAD OF, statement/row level), TRIGGER RESTRICTION (WHEN condition), and TRIGGER ACTION (the PL/SQL block executed). TRIGGER SPECIFICATION and TRIGGER BODY are not standard terminology components.