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

Which of the following is NOT a valid loop in PL/SQL?

  1. For loop.. end loop

  2. loop..end loop

  3. While loop.. end loop

  4. Do while loop..end loop
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

There is no 'Do while' loop in PL/SQL. To write conditional loops, we can either use the 'simple' or the 'while' loop.

Multiple choice

When a 'Select' command is written inside a PL/SQL program, the ____ keyword must be included in it.

  1. where

  2. in

  3. group by

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

All PL/SQL 'select' statements must contain the 'into' keyword. The 'into' keyword is used to copy the values returned by a select statement into variables. For example, the statement - 'select bookno, bookname, price into bkno, bkname,bkprice from books where title='Oracle''- will copy the values of bookno, bookname and price columns in the variables bkno, bkname and bkprice, respectively. The 'into' clause is not used when the 'select' statement is used at the SQL prompt.

Multiple choice

Which of the following is NOT a valid cursor attribute?

  1. %ISOPEN <!--?xml:namespace prefix = o /--><o:p></o:p> <o:p></o:p>

  2. %EXISTS

  3. %FOUND <o:p></o:p>

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

The %exists is not a valid cursor attribute. If used with a cursor name, it will result in compilation error.

Multiple choice

For which of the following SQL statements can a trigger not be written?

  1. Alter

  2. Update

  3. Delete

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

A trigger is a PL/SQL block structure, which is fired when DML statements like Insert, Delete, Update are executed on a database table. 'Alter' is not a DML statement but a DDL statement that is used to alter or change the structure of a table like adding, dropping and resizing a column. We cannot write a trigger for a DDL command.

Multiple choice

In PL/SQL, which of the following is the correct syntax of the 'if-else' statement?

  1. If condition then statements elseif condition then statements else statements end if;

  2. if condition statements elseif condition statements else statements end if;

  3. if condition then statements elif condition then statements else statements end if;

  4. if condition statements elsif condition statements else statements endif;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

'If-else' is a condition statement that is used to write conditional statement in a PL/SQL program. For example, the following code will print a message depending on whether the value of A is greater, B is greater or both of them have the same values - if a>b then      dbms_output.put_line('A is greater'); elsif b>a then      dbms_output.put_line('B is greater'); else      dbms_output.put_line('Both are equal'); end if;

Multiple choice

Which of the following statements is FALSE regarding triggers?

  1. A trigger can be written for handling updates of a particular column.

  2. A trigger can be run either before or after the execution of an SQL statement.

  3. A trigger cannot be written on a view.

  4. A trigger cannot contains a condition.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A trigger can be written on a view if it is declared using the 'instead of' keyword. The difference between a normal trigger and an instead of trigger is that when a normal trigger is fired, both the triggers and the firing DML statement like Delete, Insert or Update is also executed. On the other hand, in case of an instead of trigger the DML statements only invoke the trigger, they are not executed. A normal trigger is written against a table whereas an instead of trigger is written on a view.

Multiple choice

Which of the following is the correct method of declaring a constant in PL/SQL?

  1. Data constant number (3)=10

  2. Data constant number (3) := 10;

  3. Data constant number (3);

  4. Constant Data number (3) := 10;

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

A constant is a value used in a PL/SQL block that remains unchanged throughout the program. It is declared using the assignment operator ':='. We must assign a value to a constant at the time of declaration. If we do not assign a value to a constant while declaring it and try to assign a value in the execution section, we will get an error. 

Multiple choice

Which of the following is NOT a difference between SQL and PL/SQL ?

  1. SQL is declarative, while PL/SQL is procedural.

  2. SQL is used to write queries, whereas PL/SQL is used to code programs.

  3. SQL can be embedded within PL/SQL but a PL/SQL statement cannot be embedded in SQL.

  4. PL/SQL executes one statement at a time, whereas SQL is used to write a code block.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

PL/SQL is used to write individual statement in the form of a unit or program. When the program is executed, all the statements written in a complete logic are executed. On the other hand, SQL statements are executed one at a time when the user presses enter key to run the statement.

Multiple choice

In a PL/SQL 'case' statement, if all the given conditions are false, the statements written in the ____ block are executed.

  1. default

  2. else

  3. case

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

The 'case' statement is a conditional statement like the 'if-else' statement. It contains both the conditions and the statements that are processed if the condition is true. A 'case' statement can have as many 'cases' or statements as required. If all specified conditions in the 'case' statement are false, the control automatically transfers to the 'else' part. Although optional, the 'else' part will be processed if none of the conditions above is true.

Multiple choice

In a PL/SQL program, to declare a variable with the same data type as a column of a table (anchored declaration), the correct syntax will be

  1. Varname tablename.colname%rowtype

  2. Varname tablename.colname%istype

  3. Varname tablename.colname%totype

  4. Varname tablename.colname%type
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

PL/SQL provides a very useful feature called variable anchors. It refers to the use of keyword %TYPE to declare a variable with the same data type as the column data type in a table. For example, if we want to declare a variable named 'salary' with the same data type as of 'basic_salary' column of a table named 'employee' we can write the following statement: salary employee.basic_salary%type;

Multiple choice

With reference to OLD and NEW pseudorecords, which of the following statements is FALSE ?

  1. For an INSERT trigger, OLD contains no value and NEW contains new values.

  2. For an ALTER trigger, OLD contains the old values, and NEW contains the new values.

  3. For an UPDATE trigger, OLD contains the old values, and NEW contains the new values.

  4. For a DELETE trigger, OLD contains old values, and NEW contains no value.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

ALTER is a DDL statement and triggers are written on execution of a DML statement. Therefore, we cannot create a trigger that fires when an Alter statement is issued.

Multiple choice

Which of the following statements is FALSE about 'Implicit Cursors'?

  1. Oracle performs the open, fetches, and close for you automatically.

  2. It is less efficient than an explicit cursor.

  3. It is less vulnerable to data errors.

  4. It gives you less programmatic control.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

If an implicit SELECT statement returns more than one row, it raises the TOO_MANY_ROWS exception. In this case, execution in the current block terminates and control is passed to the exception section. An implicit cursor is more prone to errors.

Multiple choice

Which of the following commands will cause an automatic commit to occur?

  1. CONNECT

  2. RENAME

  3. SELECT

  4. INSERT

  5. DELETE

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

In Oracle, the RENAME command performs an automatic commit because it is a DDL (Data Definition Language) operation. All DDL commands in Oracle have implicit commit behavior. Other options listed are either DML (INSERT, DELETE) or session commands (CONNECT, SELECT) which do not auto-commit.

Multiple choice

The following INSERT statement was issued: INSERT /* +APPEND */ INTO arch.curr_order_hist

SELECT * FROM ord.curr_order;

What type of INSERT is performed?

  1. Conventional

  2. Direct-Load

  3. Parallel Direct-Load

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

The APPEND hint in Oracle INSERT statements enables Direct-Load insert, which bypasses the buffer cache and writes data directly to data files. This method is faster than conventional path inserts and is used for bulk data loading operations. The '+' prefix indicates a hint.