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

To be callable from SQL expressions, a user-defined function must: 1. Be a stored function 2. Accept only IN parameters 3. Accept only valid SQL data types, not PL/SQL specific types, as parameters 4. Return data types that are valid SQL data types, not PL/SQL specific types

  1. All are true

  2. 1, 2 and 4 are true

  3. 1 and 2 alone true

  4. 2 and 3 alone are ture
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

All four statements are correct requirements for functions called from SQL. The function must be stored (not anonymous), accept only IN parameters (OUT/IN OUT not permitted in SQL context), and use only SQL data types for both parameters and return values (PL/SQL types like BOOLEAN or RECORD are not allowed in SQL).

Multiple choice technology databases

In For Loop syntax to go in reverse order

  1. begin For 1 in reverse 1..3 loop dbms_output.put_line(‘I is’ ||i); End loop; End; /

  2. begin For 1 in 1..3 reverse loop dbms_output.put_line(‘I is’ ||i); End loop; End; /

  3. begin For 1 in 3..1 loop dbms_output.put_line(‘I is’ ||i); End loop; End; /

  4. begin For 1 in 3..1 reverse loop dbms_output.put_line(‘I is’ ||i); End loop; End; /
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The correct syntax for a reverse FOR loop is 'FOR variable IN REVERSE lower..upper LOOP'. Option A shows the correct structure with REVERSE positioned correctly between the loop variable and the range. Options B and D place REVERSE incorrectly, and C lacks it entirely.

Multiple choice technology databases

Declare Cursor c is select * from emp; I emp%row_type; Begin open c; fetch c into I; loop Dbms_output_line(‘Emp name:’ || i.ename); fetch c into I; exit when c%notfound; End loop; End; What is the error in above PL/SQL Block

  1. Close cursor statement is not included

  2. No exception block present

  3. exit when c%notfound; this should have been given as exit when c%not_found;

  4. I emp%row_type; it should be given as %ROWTYPE
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The correct attribute for declaring a row variable that matches a table structure is %ROWTYPE (all caps), not %row_type. Option D correctly identifies this syntax error. The cursor attributes %NOTFOUND, %ISOPEN, and %ROWCOUNT are all spelled correctly, so option C is wrong.

Multiple choice technology databases

Declare A char := ‘AB’; Begin A = 1; End; What is the error in the above block?

  1. There is no error in the statement

  2. Exception handler is missing

  3. CHAR is not a valid datatype

  4. Run time error The error displayed as Declare * Error at line 1: ORA-06502: PL/SQL: numeric or value error: character string too small. Will come
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Declaring A CHAR := 'AB' allocates only 1 byte (CHAR defaults to length 1). Assigning A := '1' works, but the initial declaration itself will cause ORA-06502 because 'AB' (2 bytes) exceeds the 1-byte capacity. Option D correctly identifies this runtime error.

Multiple choice technology databases

What is the smallest PL/SQL Block?

  1. Begin Dbms_output.put_line('Heello World'); exception when others then Dbms_output.put_line('error'); end; /

  2. Begin null; End;

  3. Declare a varchar2(100) := 'Hello World'; Begin Dbms_output.put_line(a); exception when others then Dbms_output.put_line('error'); end; /

  4. begin Dbms_output.put_line('Heello World'); end; /
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The smallest valid PL/SQL anonymous block must contain a BEGIN and END clause; the body can be a single null statement. The block BEGIN NULL; END; meets this requirement and compiles, making it the correct minimal example. The stored answer correctly identifies this option.

Multiple choice technology databases

DECLARE sal_limit NUMBER ( 4 ) := 0 ; my_ename emp.ename%TYPE ; my_sal emp.sal%TYPE ; CURSOR my_cursor IS SELECT ename , sal FROM emp WHERE sal > sal_limit ; BEGIN sal_limit := 1200 ; OPEN my_cursor; fetch my_cursor INTO my_ename , my_sal ; LOOP FETCH my_cursor INTO my_ename , my_sal ; EXIT WHEN my_cursor%NOTFOUND ; -- nothing returned INSERT INTO new_table VALUES ( my_ename , my_sal ) ; END LOOP ; COMMIT ; END ;

  1. For cursor fetching we have to use Cursor For loops

  2. There is no close statement for closing the cursor statement

  3. No errors in the block

  4. No exception handler found
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The code opens a cursor and fetches rows in a loop, but never closes the cursor with 'CLOSE my_cursor'. Failing to close explicit cursors can cause memory leaks. Option B correctly identifies this missing CLOSE statement as the error.

Multiple choice technology databases

CREATE OR REPLACE FUNCTION dml_call_sql (p_sal NUMBER) RETURN NUMBER IS BEGIN INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary) VALUES(1, 'employee 1', '[email protected]', SYSDATE, 'SA_MAN', 1000); RETURN (p_sal + 100); END; / Function created. UPDATE employees SET salary = dml_call_sql(2000) WHERE employee_id = 170; What is the error in the block

  1. No errors in the block

  2. ORA-04091: table PLSQL.employees is mutating. Trigger/function may not see it. ORA-06512: at PL/SQL "DBMS_CALL_SQL"

  3. There is no exception handler in the function

  4. No rows updated exception will occur
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The function dml_call_sql performs DML (INSERT) on the EMPLOYEES table, then the UPDATE statement calls this function on the same table. This causes a mutating table error (ORA-04091) because you cannot query or modify a table that's being modified by the triggering statement. Option B correctly identifies this error.

Multiple choice technology databases

Implicit cursors are declared for

  1. Some DML,PL/SQL statements

  2. All DML,PL/SQL statements

  3. Only PL/SQL statements

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

Implicit cursors are automatically declared by Oracle for ALL DML statements (INSERT, UPDATE, DELETE) and PL/SQL SELECT INTO statements. They track the operation's results (%ROWCOUNT, %FOUND, etc.). Option B correctly states this.

Multiple choice technology databases

For processing rows in a specific sequence,

  1. Use ORDER BY clause

  2. Use ROW clause

  3. Use ROW BY clause

  4. None of the Above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The ORDER BY clause in SQL is specifically designed to sort query results in a specified sequence. It allows you to arrange rows based on one or more columns in ascending or descending order. ROW and ROW BY are not valid SQL clauses for sorting purposes.

Multiple choice technology databases

What is true about triggers I. instead of trigger can be used on tables and views 2. instead of trigger can fire at eah row and statement levels. 3. Compund trigger in 10g allows us to specify multiple timig events on single database objects. 4. we should not use the same object which fired the trigger inside the trigger. this will result in mutating trigger error.

  1. 2 and 4 are true

  2. 1 , 3 and 4 are true

  3. 1 and 3 are true

  4. Only 4 is true
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Statement 4 is correct because accessing the same table that fired the trigger within the trigger causes a mutating table error in Oracle. This is a well-known restriction. Statements 1 and 3 are incorrect: INSTEAD OF triggers can only be used on views (not tables), and compound triggers don't allow multiple timing events on the same object in the manner described. Statement 2 is incorrect as INSTEAD OF triggers only fire at the statement level, not row level.

Multiple choice technology databases

What is the difference between Union and Union all statements

  1. Union all used for combining multiples queries using single union all

  2. There is no difference

  3. the union will eliminate duplicate records in result set but, union all will print the duplicate records

  4. Union all is used for select with multiple columns
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The UNION operator combines query results and eliminates duplicate rows. Conversely, the UNION ALL operator combines all rows from the queries, retaining duplicate rows in the final result set.

Multiple choice technology databases

what is true about Delete and truncate 1. Truncate can not be used in PL/SQL 2. Delete will remove the data and will not move watermark. 3. Both statements require commit 4. truncate can remove selected records

  1. 1 and 2 are correct

  2. All are true

  3. 1,2 and 3 are correct

  4. 2,3 and 4 are correct
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

TRUNCATE is a DDL command, so it cannot be run directly in PL/SQL without dynamic SQL, and it automatically commits. DELETE is a DML command that removes data without resetting the high-water mark.