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

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

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.

Multiple choice

Which of the following statements is correct about UNIQUE key in Oracle?

  1. Two rows for a given column having UNIQUE key can have same values

  2. Two rows for a given column having UNIQUE key can have nulls

  3. Both option 1 and option 2 are correct.

  4. Neither option 1 nor option 2 is correct.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

This answer is correct. Two rows for a given column having UNIQUE key can have nulls. In Oracle, there is an exception that two rows can have nulls though the UNIQUE key is defined for the given column.

Multiple choice

Which of the following is true?

i. A primary key by default checks for not null and uniqueness conditions. ii. A primary key by default checks only for uniqueness. iii. A primary key can be a composite key. iv. A primary key can never be a composite key.

  1. i. and iv. are true

  2. i. and iii. are true

  3. ii. and iv. are true

  4. ii. and iii. are true
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

This answer is correct. A primary key by default checks for both not null and the uniqueness conditions. Primary key will verify whether the value inside the column is null or not, and also unique from the other values or not. A primary key can also be a composite key. It can have one or more columns as a primary key.

Multiple choice

Which command is used to disable the Primary Key constraint?

  1. ALTER CONSTRAINTcommand

  2. ALTER TABLE command

  3. ALTER COLUMN command

  4. ALTER QUERY command
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

This answer is correct. ALTER TABLE command is used to disable the primary key. To enable the primary key, along with ALTER TABLE command, USING INDEX and TABLESPACE also must be specified along with the command.

Multiple choice

Which of the following statements is true?

i. For enabling primary key constraint, ALTER TABLE is used. ii. For enabling primary key constraint, ENABLE keyword is used all alone. iii. For disabling primary key, index will be dropped. iv. For disabling primary key, index will not be dropped.

  1. i. and iii. are true

  2. i. and iv. are true

  3. ii. and iii. are true

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

This answer is correct because for enabling primary key constraint, ALTER TABLE is used followed by ENABLE key word. Similarly, for disabling primary key constraint, index will be dropped because the index comes along with primary key by default and since primary key constraint is not applied on the records, the index is also dropped.