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

JOE was allocated 10 MB of quota in the USERS tablespace. He created database objects in the USERS tablespace. The total space allocated for the objects owned by JOE is 5 MB. In order to revoke JOE's quota from the USERS tablespace, the following command was issued. ALTER USER JOE QUOTA 0 ON users; What will be the result?

  1. The statement raises the error: ORA-00940: invalid ALTER command.

  2. The statement raises the error: ORA-00922: missing or invalid option.

  3. The objects owned by JOE are automatically deleted from the revoked USERS tablespace.

  4. The objects owned by JOE remain in the revoked tablespace, but these objects cannot be allocated any new space from the USERS tablespace.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When a user's tablespace quota is revoked by setting it to 0, existing objects owned by that user are not automatically dropped, moved, or otherwise affected. The objects remain in the tablespace occupying their current space. However, the user cannot allocate any additional space from that tablespace for new objects or for extending existing ones. This behavior ensures existing data isn't lost while preventing future space consumption. The quota change affects only future allocations, not existing storage.

Multiple choice

Which of the following SQL statements defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?

  1. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);

  2. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

  3. CRETE TABLE EM (empno NUMBER(4), ename VARCHAR2(35) deptno NUMBER (7,2) NOT NULL, CONSTRAINT em_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));

  4. CREATE TABLE EMP (empno NUMBER (4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno fk REFERENCES dept (deptno));
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Option B correctly defines a FOREIGN KEY constraint using inline constraint syntax with the REFERENCES clause directly in the column definition. The syntax "deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)" properly creates a foreign key named emp_deptno_fk that references the deptno column in the dept table. Option A is missing parentheses around the FOREIGN KEY keyword. Option C contains typos (CRETE instead of CREATE, EM instead of EMP) and incorrect constraint placement. Option D has incorrect keyword order with FOREIGN KEY placed after CONSTRAINT name instead of using the REFERENCES clause properly.

Multiple choice

The DBA issues the following command:

SQL< alter table ORDER SHRINK SPACE

What be the result of this statement?

  1. There would be only a compacting of the data, not a release of data.

  2. There would be no impact on DML operations in the database.

  3. There would be immediate release of the free space.

  4. There would be an impact on the DML operations in the database.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

There would be an impact on the DML operations in the database.

Multiple choice

The following INSERT statement was issued:

INSERT /* +APPEND */ INTO arch.curr_order_hist

SELECT * FROM ord.curr_order;

Which 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 enables Direct-Load INSERT, which bypasses the buffer cache and writes data directly to datafiles. This is much faster than conventional path INSERT which uses the buffer cache. Option C (Parallel) is wrong because no parallel hint was used; options A and D describe conventional path inserts.

Multiple choice

In which clauses of a SELECT statement can substitution variables be used?

  1. The SELECT and FROM clauses, but not the WHERE clause

  2. The SELECT, FROM, WHERE, and GROUP BY clauses, but not the ORDER BY clause

  3. The SELECT, WHERE, GROUP BY, and ORDER BY clauses, but not the FROM clause

  4. The SELECT and FROM clauses, but not the WHERE clause

  5. The SELECT, FROM, WHERE, GROUP BY, ORDER BY, and HAVING clauses
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Substitution variables can be used in all clauses of a SELECT statement: SELECT, FROM, WHERE, GROUP BY, ORDER BY, and HAVING. They are replaced before statement execution. Options A-D incorrectly restrict which clauses can use substitution variables.

Multiple choice

_____________ can be declared by us for the queries that return more than one row.

  1. Implicit cursors

  2. Explicit cursors

  3. REF cursor

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

Explicit cursors must be declared by developers for queries that return multiple rows. Implicit cursors are automatically created by Oracle for single-row queries (like SELECT...INTO). REF cursors are cursor variables used to pass result sets, not a standard cursor type.

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

Three possible credit ratings are assigned to customers: Poor, Good, and Excellent (represented numerically by 1, 2, and 3). A RATING column was added to the CUSTOMERS table and the DBA was asked to add an item to the form so that data entry clerk can populate the RATING column. To restrict data entry clerks to one of these three values, the DBA decided to create a radio group for the Rating item. With the Customers block selected in the Layout Editor, he used the radio button to create three radio buttons on the canvas. He then set the labels (Poor, Good, and Excellent) and values (1, 2, and 3) for the radio buttons. He set the name of the radio group to RATING, its Data Type to Number, and its Database column to Rating. He then ran the form to test it. When he executed a query, however, no records were returned, although there were many customer records in the database. What can he do to make the query return the records?

  1. Set the Mapping of Other Values property for the radio group to Null.

  2. Create a fourth radio button for an undetermined credit rating and leave its value blank.

  3. Create a fourth radio button for an undetermined credit rating and explicitly set its value to Null.

  4. Choose a different type of input, because radio groups do not allow query of Null values.

  5. None of the above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Radio groups in Oracle Forms cannot match NULL database values because every radio button must have a non-null value. When records contain NULL in the RATING column, no radio button matches, so queries return no records. The solution is to use a different input type like a list item (poplist) or LOV that can handle NULL values.

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.