Computer Knowledge

Database and SQL

3,929 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 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.

Multiple choice technology programming languages

Oracle database can use "N" types of segments ; The value of "N" =

  1. 2

  2. 3

  3. 4

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

Oracle databases use 4 main types of segments: data segments (table data), index segments (index data), undo segments (for rollback/undo), and temporary segments (for temporary operations). These are the core segment types in Oracle's storage architecture. Options A, B, and D don't represent the complete set.

Multiple choice technology programming languages

Which of the following statements uses embedded dynamic SQL?

  1. SELECT col1 INTO :hv from table1

  2. EXECUTE IMMEDIATE SELECT col1 INTO :hv FROM table1

  3. DECLARE c1 CURSOR FOR s1

  4. IMMEDIATE SELECT col1 INTO :hv FROM table1
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

EXECUTE IMMEDIATE is the statement used to execute dynamic SQL statements that are constructed and executed at runtime. Option A uses static SQL with a host variable. Option C declares a cursor for a prepared statement. Option D has incorrect syntax.

Multiple choice technology databases

Can we have multiple primary keys in a table?

  1. True

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

A table can have only one primary key constraint, though that primary key can consist of multiple columns (a composite key). The statement asks about multiple primary keys, which is not allowed in standard SQL - hence the answer is False.

Multiple choice technology databases

We have unique constraint on sal column on emp table. can the column accept multiple null values?

  1. True

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

Unique constraints in SQL allow multiple NULL values because NULL represents unknown or missing data, not an actual value. The uniqueness check only applies to non-NULL values - each NULL is considered distinct from other NULLs. Therefore, you can insert as many NULL values as needed into a column with a unique constraint.