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 code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased?

  1. ALTER TABLE emp ADD CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1);

  2. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp - 6 - FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, ‘Do not decrease salary not increase by more than 10%’); END;

  3. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, ‘Do not decrease salary not increase by more than 10%’); END;

  4. CREATE OR REPLACE TRIGGER check_sal AFTER UPDATE OR sal ON emp WHEN (new.sal < old.sal OR -new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, ‘Do not decrease salary not increase by more than 10%’); END;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To answer this question, the user needs to know about SQL triggers and how they can be used to enforce constraints on database operations. Option B is the correct answer because it creates a trigger that runs before each update of the sal (salary) column in the emp table. The trigger checks if the new salary is less than the old salary or greater than the old salary multiplied by 1.1 (i.e., increased by more than 10%). If either of these conditions is true, the trigger raises an error to prevent the update from occurring.

Option A is incorrect because it uses a check constraint to limit the salary increase, but it does not prevent salary decreases.

Option C is almost correct, but it has a syntax error. The FOR EACH ROW clause is not needed in this case, and the semicolon at the end of the RAISE_APPLICATION_ERROR function call is missing.

Option D is also incorrect because it creates a trigger that runs after each update of the sal column, which means the constraint is not enforced until after the update has already occurred.

Therefore, the correct answer is:

The Answer is: B. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp

  • FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1)
  • BEGIN
  • RAISE_APPLICATION_ERROR ( - 20508, ‘Do not decrease salary not increase by more than 10%’);
  • END;
Multiple choice technology databases

Examine the code and state which of the following options is correct? CREATE OR REPLACE PACKAGE bonus IS g_max_bonus NUMBER := .99; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER; FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY bonus IS v_salary employees.salary%TYPE; v_bonus employees.commission_pct%TYPE; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER - 7 - IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employee_id = p_emp_id; RETURN v_bonus * v_salary; END calc_bonus FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employees RETURN v_bonus * v_salary + v_salary; END cacl_salary; END

  1. You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table.

  2. You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table.

  3. You can call the BONUS.CALC_SALARY packaged function form a DELETE command against the EMPLOYEES table.

  4. You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The BONUS.CALC_SALARY function can be called from a SELECT statement because packaged functions are permitted in queries despite containing DML operations internally. The function has purity violations (reads and writes database state), but Oracle allows this in SELECT contexts - INSERT, UPDATE, and DELETE operations would fail due to purity restrictions.

Multiple choice technology databases

Which statement is valid when removing procedures?

  1. Use a drop procedure statement to drop a standalone procedure.

  2. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package specification.

  3. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package body.

  4. For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

DROP PROCEDURE removes standalone procedures. Packaged procedures cannot be dropped individually - you must drop and recreate the entire package specification and body. The REUSE SETTINGS clause mentioned in option D does not exist in Oracle's syntax for procedure removal.

Multiple choice technology databases

You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE. What type of trigger do you create?

  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 table
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To solve this question, the user needs to know the concept of triggers in database management systems. The user must also understand the difference between statement-level triggers and row-level triggers.

In this scenario, we need to create a trigger that monitors every row that is changed in the EMP table and inserts that information into the AUDIT_TABLE. Therefore, we need to create a row-level trigger.

Now, let's go through each option and explain why it is right or wrong:

A. FOR EACH ROW trigger on the EMP table. This option is correct. A FOR EACH ROW trigger on the EMP table will monitor every row that is changed in the EMP table and execute the trigger code for each affected row. This is the type of trigger that we need to create to accomplish the task.

B. Statement-level trigger on the EMP table. This option is incorrect. A statement-level trigger on the EMP table triggers once for each SQL statement that is executed, regardless of the number of rows that are affected. This type of trigger would not be suitable for the task at hand.

C. FOR EACH ROW trigger on the AUDIT_TABLE table. This option is incorrect. A FOR EACH ROW trigger on the AUDIT_TABLE table would monitor every row that is changed in the AUDIT_TABLE table, but this is not what we need. We need to monitor changes in the EMP table.

D. Statement-level trigger on the AUDIT_TABLE table. This option is incorrect. A statement-level trigger on the AUDIT_TABLE table would only trigger once for each SQL statement that is executed, regardless of the number of rows that are affected. This type of trigger would not be suitable for the task at hand.

The Answer is: A. FOR EACH ROW trigger on the EMP table.

Multiple choice technology mainframe

Sqlcode -911 means

  1. Timestamp mismatch

  2. Authorization failure

  3. Deadlock, rollback done

  4. Deadlock or time-out
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

SQLCODE -911 in DB2 indicates that a deadlock or timeout occurred, and the database system has automatically rolled back the transaction. This is a resource contention error where the transaction could not complete due to conflicts with other transactions or exceeded the wait timeout. Option C is correct because rollback is explicitly mentioned.

Multiple choice technology mainframe

Image copy cannot be done for which of the below object?

  1. Database

  2. Tablespace

  3. Index

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

Image copies (backups) cannot be created for Views because Views are virtual tables - stored queries without physical data storage. Database, Tablespace, and Index are physical storage objects that can be backed up, while a View only exists as a definition.

Multiple choice technology databases

PL/SQL raises an exception, in which TWO of the following cases:

  1. When a SELECT statement returns no rows

  2. When a SELECT statement returns more than one row

  3. When the datatypes of SELECT clause and INTO clause do not match

  4. When INTO statement is missing in the SELECT statement
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

PL/SQL's SELECT...INTO expects exactly one row. If NO rows are returned, NO_DATA_FOUND is raised. If MORE than one row is returned, TOO_MANY_ROWS is raised. Datatype mismatch raises VALUE_ERROR, not a standard exception. Missing INTO is a compile-time syntax error, not a runtime exception.

Multiple choice technology databases

What is the smallest possible PL/SQL block?

  1. Begin null; end;

  2. Declare x varchar2(100); begin dbms_output.put_line(x); end;

  3. Begin .. statement1; exception ... end;

  4. Begin dbms_output.put_line(sysdate); end;;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The smallest possible PL/SQL block is 'BEGIN NULL; END;'. This is a valid PL/SQL block that does nothing - it executes a NULL statement. PL/SQL requires at least one executable statement between BEGIN and END, and NULL is the minimal statement that satisfies this requirement. Options B and D include additional statements or declarations, making them larger than minimal. Option C is incomplete syntax with '.. statement1' placeholder.

Multiple choice technology databases

maximum of triggers in sybase

  1. 3

  2. 12

  3. 6

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

Sybase ASE allows a maximum of three triggers per table: one each for INSERT, UPDATE, and DELETE operations. This is a fundamental design limit in Sybase, distinguishing it from systems that allow multiple triggers per operation type.

Multiple choice technology databases

Maximum nesting levels in triggers.

  1. 32

  2. 16

  3. Can't nest triggers inside others.

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

Sybase ASE allows trigger nesting up to 16 levels deep by default. When a trigger fires another trigger (possibly on another table), the nesting increments. This depth is configurable via server settings, with 16 being the standard default limit.

Multiple choice technology databases

The maximum size for expressions, variables, and arguments passed to stored procedures.

  1. 255

  2. 256

  3. 16483

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

Sybase imposes size limits on expressions, variables, and procedure parameters. The value 16483 bytes (approximately 16KB) represents the batch size limit for SQL statements and parameter data in certain Sybase configurations, constraining how much data can be passed in a single operation.

Multiple choice technology

In Filter stage, settting Options "Output rows only once" to TRUE will do whcih of the below action

  1. Removing the duplicates for the records which statisfies the where clause

  2. rows are only output down the link of the first Where clause they satisfy

  3. rows output down the links of all Where clauses that they satisfy.

  4. None of the above
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The Output rows only once option ensures each row goes down only the first matching output link. Once a row satisfies a where clause, it's output and doesn't evaluate subsequent clauses. This prevents duplicate routing when rows match multiple filter conditions.

Multiple choice technology databases

create or replace function add_three_numbers (a number := 0, b number := 0, c number := 0) return number is Begin return a+b+c; End; / Which is not a valid procedure call in Oracle 10g

  1. Begin Dbms_output.put_line(add_three_numbers (3,4,5)); End; /

  2. Begin Dbms_output.put_line(add_three_numbers (a => 3, b=>4,c=>5)); End; /

  3. Begin Dbms_output.put_line (add_three_numbers (3, b=>4,c=>5)); End; /

  4. select add_three_numbers (3, b=>4,c=>5) from dual
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Calling a PL/SQL function from a SQL SELECT statement cannot use named parameter notation; only positional arguments are allowed. The statement select add_three_numbers (3, b=&gt;4,c=&gt;5) from dual is therefore invalid, making the stored answer correct.