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

Which of the following statements creates a view and does not allow the values to be changed through the view?

  1. CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH READ ONLY;

  2. CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH CHECK OPTION;

  3. CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH CHECK CONSTRAINT;

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

The WITH READ ONLY clause creates a view that prohibits INSERT, UPDATE, or DELETE operations through it. This is the standard Oracle syntax for making views read-only. Option B's WITH CHECK OPTION ensures DML operations don't create rows that would be invisible to the view, but doesn't prevent all DML. Option C's WITH CHECK CONSTRAINT is not valid Oracle syntax. Option D is incorrect because WITH READ_ONLY is the correct answer.

Multiple choice

Which of the following are not legal?1) DECLARE V_Destination_India Number;2) DECLARE V_123, V_213, V_132 Varchar2(10);3) DECLARE V_sys Boolean := 1;4) DECLARE V_destination_India Number(4);5) DECLARE V_Hiredate NOT NULL DATE := '01-JAN-00';

  1. 1, 3, 5

  2. 1, 2, 3, 4

  3. 2, 3, 5

  4. 1, 3, 4
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Statement 2 is illegal because multiple variables in one declaration require individual type declarations: V_123 VARCHAR2(10), V_213 VARCHAR2(10), V_132 VARCHAR2(10). Statement 3 is illegal because Boolean variables can only be assigned TRUE, FALSE, or NULL, not the numeric value 1. Statement 5 is illegal because NOT NULL must follow the datatype: V_Hiredate DATE NOT NULL. Statements 1 and 4 are legal.

Multiple choice

Which of the following statements regarding DML statement functionality is true?

  1. UPDATE can update multiple columns in one table.

  2. INSERT must contain a VALUES clause.

  3. MERGE will delete rows that do NOT exist in either table.

  4. UPDATE will add rows to a table if an INTO clause is specified.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

UPDATE can modify multiple columns in one table with a single statement using SET column1=value1, column2=value2. INSERT can use subqueries instead of VALUES. MERGE matches rows to update or insert, not delete. UPDATE doesn't add rows - INSERT does. INTO clause is for subqueries, not UPDATE.

Multiple choice

A table is being imported and it must be ensured that the table is put into a single contiguous area. There is not a single contiguous area large enough for the entire space allocated to the table. What should be done to achieve this?

  1. Use the cascade option.

  2. Use the constraint option.

  3. Use the primary key option.

  4. Coalesce the tablespace the table is using.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When a tablespace is fragmented with insufficient contiguous space, COALESCE merges adjacent free extents into larger ones. This creates the needed contiguous space. Cascade, constraint, and primary key options don't relate to space consolidation. Coalescing is the correct approach for import requirements.

Multiple choice

How many join conditions should be there to avoid a cartesian join for joining three tables?

  1. 1

  2. 2

  3. 3

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

To avoid a cartesian product when joining n tables, you need n-1 join conditions. For 3 tables, that's 2 join conditions. Each condition links one new table to the existing result set. Without sufficient join conditions, every row from every table matches every other row - an explosive cartesian product.

Multiple choice

A developer wants to change an existing application which requires to change the source table of the block from Order items to Products table to provide information related to the various products sold by the company during the year 2004-2005. How can this change be implemented?

  1. Select the Frame consisting of the items of the Order Items block. Click on the Tools menu button and click the Layout Wizard.

  2. Multi Select the Items of the Orders Items table, then right-click and click on the Data Block wizard.

  3. The table which is being associated with a Database Data block cannot be changed once it has been created.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In Oracle Forms, when you need to change the source table associated with an existing data block, you use the Data Block wizard after selecting the items. The Layout Wizard (option A) is for visual arrangement, not changing data sources. Option C is incorrect because table sources can be changed.

Multiple choice

A form was being developed that serves as a front end to access the Order Entry application. Within the form, the usernames of those users should be stored who have permissions to run the application. This information is accessed when the form starts up to determine if the user is authorized. The list of usernames is not stored in the database and should not be visible to users. How should this information be stored?

  1. In a list item

  2. In a list of values

  3. In a set of parameters

  4. In a record group
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Record groups in Oracle Forms are programmatic data structures that can store data in memory and are not visible to users. They are ideal for storing authorization data that needs to be accessed programmatically but hidden from the user interface. List items and lists of values are visible UI elements.

Multiple choice

____________ directs the complier to add the user defined error message to the oracle error number.

  1. Pragma Init Exception

  2. Pragma Exception

  3. ORA_EXCEPTION

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

PRAGMA EXCEPTION_INIT is a compiler directive that associates a user-defined error message with a specific Oracle error number. The other options are not valid PL/SQL constructs - ORA_EXCEPTION and ORA_USER_EXCEPTION don't exist, and 'Pragma Exception' is incomplete syntax.

Multiple choice

DANNY is coding a When-New-Form-Instance trigger to populate a hierarchical tree item called Emp_Tree that should initially appear as shown in the exhibit. Mr. King, the president of the company, is the only employee who does not have a manager.

In the trigger, DANNY declares a variable called rg_emps that is of the RECORD GROUP data type. He will use this record group to populate the tree. He uses the following code to create the record group:

rg_emps := Create_Group_Form_Query('rg_emps', 'Select 1, level, last_name, NULL, to_char(employee_id) from employees connect by prior employee_id = manager_id; start with manager_id is null');

He then programmatically populates the record group, and then populates the tree with the record group. He runs the form to test it. Will the tree initially appear as shown? If not, why?

  1. Yes, the tree will appear as shown.

  2. No, the first element selected in the select statement should be 4 because he wanted four levels of the tree to be displayed.

  3. No, he should eliminate the last element selected in the select statement, because he does not want to display the employees ID.

  4. No, the 'connect by' statement should be 'connect by prior manager_id = employee_id'.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The hierarchical query syntax is correct: CONNECT BY PRIOR employee_id = manager_id builds the tree from parent to child, and START WITH manager_id IS NULL correctly identifies Mr. King as the root. The query structure (1, level, last_name, NULL, to_char(employee_id)) properly defines tree depth, label, and data attributes. Option D's suggestion to reverse PRIOR would produce incorrect hierarchy.

Multiple choice

The STUDENT_GRADES table has the following columns:STUDENT_ID NUMBER (12)SEMESTER_END DATEGPA NUMBER (4, 3)

Which of the following statements finds the highest Grade Point Average (GPA) per semester?

  1. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;

  2. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

  3. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;

  4. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;

  5. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To find the highest GPA per semester, you need MAX(gpa) with GROUP BY semester_end. Option C has the correct clause order: SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end. Options B, D, and E have incorrect SQL syntax - GROUP BY must come after WHERE but before HAVING, and SELECT cannot appear inside parentheses.

Multiple choice

Which of the following can be a valid column name?

  1. Column

  2. 1966_Invoices

  3. Catch_#22

  4. #Invoices

  5. None of the above

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

Oracle column names must start with a letter, can contain letters, digits, underscore (), dollar sign ($), and pound sign (#). Option C (Catch#22) is valid because it starts with a letter and only contains allowed characters. Option A has a trailing space, Option B starts with a digit, and Option D starts with # instead of a letter.

Multiple choice

Evaluate the following SELECT statement:

SELECT emp_id, name FROM emp WHERE emp_id NOT IN (SELECT emp_id FROM emp WHERE dept_id = 30 AND job = 'SALESMAN');

What would happen if the inner query returned a NULL value?

  1. No rows would be selected from the EMPLOYEE table.

  2. A syntax error would be returned.

  3. All the EMPLOYEE_ID and NAME values in the EMPLOYEE table would be displayed.

  4. Only the rows with EMPLOYEE_ID values equal to NULL would be included in the results.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When the inner query returns NULL, NOT IN (1, 2, NULL) evaluates to UNKNOWN for every comparison. A WHERE condition of UNKNOWN returns no rows, so the outer query selects nothing. This is a classic SQL NULL pitfall.

Multiple choice

Which of the following is a DDL command?

  1. INSERT

  2. TRUNCATE

  3. UPDATE

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

This answer is correct. TRUNCATE statement is a DDL (Data Definition Language) statement but not a DML (Data Manipulation Language) statement. TRUNCATE statement when executed will remove all rows from the given table and will automatically commit the transaction.

Multiple choice

Which of the following are true?

i. TRUNCATE command is used to remove all the rows in a given table without rollback. ii. TRUNCATE command is used to drop the table. iii. DELETE command is used to remove all the rows in a given table with rollback. iv. DELETE command is used to remove all the rows in a given table without rollback.

  1. Only i and iii

  2. Only ii and iii

  3. Only i and iv

  4. Only ii and iv
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

This answer is correct. TRUNCATE command is a DDL (Data Definition Language) command, which is used to remove all the rows from a given table without rollback. Since it is a DDL (Data Definition Language) command, it automatically commits the transaction. DELETE command is a DML (Data Manipulation Language) command, which is used to remove all the rows from a given table with rollback option.