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

What should be done to import data and to avoid migration?

  1. Use the CASCADE option during import.

  2. Use the COMPRESS option during export.

  3. Use the RECORDLENGTH option during export.

  4. Coalesce free space.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Coalescing free space combines adjacent free extents into larger contiguous spaces, which helps avoid row migration during import by ensuring sufficient contiguous space. Options A, B, and C are export/import parameters that don't prevent migration.

Multiple choice

You are declaring a variable of data type NUMBER in a PL/SQL block. During the execution of the PL/SQL block, a query returns seven rows, and the next FETCH statement is issued. If the variable was assigned a value by using %ROWCOUNT attribute, which of the following values will be stored in it?

  1. 0

  2. 7

  3. 8

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

%ROWCOUNT returns the number of rows fetched so far by the cursor. After a query returns 7 rows and a FETCH is issued, %ROWCOUNT will be 7. It starts at 0 before the first fetch and increments with each successful fetch.

Multiple choice

Which three definitions are associated with implicit cursors?

  1. %rowtype, %toomanyrows, %found

  2. %found, %notfound, %rowcount

  3. %rowtype, %rowcount, %notfound

  4. None of the above

  5. All of the above

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

Oracle PL/SQL implicit cursors (used in implicit SELECT statements and DML operations) have three key attributes: %FOUND (returns TRUE if a row was affected), %NOTFOUND (returns TRUE if no row was affected), and %ROWCOUNT (returns number of rows affected). %ROWTYPE is used for declaring record variables based on table structures, not as a cursor attribute. Option B correctly lists the three implicit cursor attributes.

Multiple choice

What is a correlated sub query?

  1. The result retrieved by the inner query is fed to the outer query.

  2. A correlated sub-query is dependent upon the outer query.

  3. The outer query gives a reference to the sub query.

  4. Both (1) and (2)
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A correlated subquery executes once for each row processed by the outer query, referencing values from the outer query in its WHERE clause. This creates a correlation between the inner and outer queries. Option A describes a regular (non-correlated) subquery where the inner query runs independently. Option C is incomplete - the outer query does provide references, but the key characteristic is the dependency. Option B correctly states the defining feature.

Multiple choice

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

EMPLOYEES:

EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2 (25) LAST_NAME VARCHAR2 (25) HIRE_DATE DATE

NEW_EMPLOYEES:

EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2 (60)

Which MERGE statement is valid?

  1. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

  2. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

  3. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

  4. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||', '||e.! last_name);
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Option A uses correct Oracle MERGE syntax: MERGE INTO target USING source ON (condition) WHEN MATCHED THEN UPDATE... WHEN NOT MATCHED THEN INSERT... Options B and C incorrectly use WHEN EXISTS instead of WHEN MATCHED. Option D has multiple errors: uses FROM instead of INTO and INSERT INTO instead of just INSERT. Option A correctly updates the name column when employee_id exists and inserts new rows when it doesn't.

Multiple choice

Which privilege can only be granted to a user and not to a role?

  1. DELETE

  2. ALTER

  3. INSERT

  4. EXECUTE

  5. REFERENCES
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

The REFERENCES privilege (foreign key constraint) can only be granted to a specific user, not to a role. This is because foreign key constraints require explicit permission to reference another user's table, and Oracle doesn't allow this through roles for security and integrity reasons. All other privileges listed (DELETE, ALTER, INSERT, EXECUTE) can be granted to both users and roles.

Multiple choice

Examine the syntax for creating a DEPARTMENT table: CREATE TABLE department( Deptno NUMBER(4), Dname VARCNAR2(30), mgr NUMBER(6), Loc NUMBER(4)) STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 50 MINEXTENTS 1 MAXEXTENTS 5) TABLESPACE userdata; What is the size defined for the fifth extent?

  1. 200 K

  2. 300 K

  3. 450 K

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

With PCTINCREASE 50, each extent grows by 50% over the previous extent. Extent 1: 200K (INITIAL). Extent 2: 200K (NEXT). Extent 3: 200K + 100K (50% of 200K) = 300K. Extent 4: 300K + 150K (50% of 300K) = 450K. Extent 5: 450K + 225K (50% of 450K) = 675K. Option D correctly calculates the fifth extent size using the PCTINCREASE formula.

Multiple choice

For which of the following tasks would it be most appropriate to use a PL/SQL WHILE loop?

  1. To add six new services to the SERVICE table

  2. To change MACHINE_ID 123456 to 456789 and change all others to 900000

  3. To change MACHINE_ID 123456 to 456789

  4. To add new services to the SERVICE table until finished
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A WHILE loop is used when you need to iterate based on a condition and don't know in advance how many iterations are required. Option D describes 'until finished' which implies conditional iteration based on completion status. The other options (A, B, C) are one-time operations that don't require looping - they can be accomplished with single SQL statements.

Multiple choice

An export that backs up tables that have changed since the last cumulative or complete export is known as _____.

  1. an incremental export

  2. a cumulative export

  3. a complete export

  4. a full export

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

A cumulative export backs up only tables that have changed since the last cumulative or complete export. This is different from incremental export (since last incremental export of any type) and complete/full export (all tables regardless of changes). Cumulative provides a middle ground, capturing changes since the last major backup.

Multiple choice

Which character is used to continue a statement in SQL*Plus?

  2. /

  4. @

  5. =

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

In SQL*Plus, the hyphen (-) is used to continue a SQL statement on the next line. When you end a line with a hyphen, SQL*Plus treats the next line as a continuation of the current statement. Other symbols have different meanings: * is for comments, / executes the SQL buffer, and @ runs a script file.

Multiple choice

The titles of all the books that meet the following criteria has to be displayed:

  1. Purchased before Feb 21, 2002
  2. Price is less than \$500 or greater than \$900 The result has to be sorted by the date of purchase, starting with the most recently bought book. Which of the following statements should be used?

  1. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < '21-FEB-2002' ORDER BY purchase_date

  2. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date< '21-FEB-2002' ORDER BY purchase date ASC

  3. SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC

  4. SELECT Book_title FROM books WHERE (price < 500 OR Price>900)AND purchase_date<'21-FEB-2002' ORDER BY purchase date DESC
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The correct query filters books purchased before Feb 21, 2002 with price outside the $500-$900 range (less than \$500 OR greater than \$900). The date condition uses < for 'before'. Results are sorted by purchase_date DESC to show most recent first. Option D has the correct logic with proper parentheses and DESC sorting. Note: there are minor typos but the logic is correct.

Multiple choice

All of the following can only be used with numeric data types except _____.

  1. AVG

  2. COUNT

  3. SUM

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

COUNT is the only aggregate function listed that works with all data types (numeric, character, date). AVG, SUM, and STDDEV are mathematical functions that only work with numeric data. COUNT(*) counts rows, while COUNT(expression) counts non-null values regardless of data type.

Multiple choice

What is the output of the given query? SELECT ROWNUM,ENAME,SAL FROM EMP WHERE ROWNUM<=2

  1. It shows only the second record.

  2. It shows only the record 1 and 2.

  3. It shows no row rows selected.

  4. None of these
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The query returns no rows. This is because ROWNUM is assigned BEFORE the WHERE clause filter is applied. When you filter with ROWNUM <= 2, Oracle tries to assign row numbers, but the WHERE clause eliminates rows before ROWNUM can be properly assigned. The condition ROWNUM <= 2 never allows any row to be returned. To limit rows, use ROWNUM in a subquery or use FETCH FIRST in modern Oracle.