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 technology databases

Which two statements about sequences are true?

  1. A. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value

  2. B. You use a CURRVAL pseudo column to look at the current value just Generated from a sequence, without affecting the further values to be generated from the sequence.

  3. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence

  4. Both A and B
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To answer this question, we need to understand the concepts of sequences in the context of databases.

A sequence is an object in a database that generates a sequence of unique values. In Oracle, two pseudo columns are used in conjunction with sequences: NEXTVAL and CURRVAL.

Statement A: You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value. This statement is true. The NEXTVAL pseudo column allows you to obtain the next possible value from a sequence without affecting the further values to be generated. It effectively increments the sequence by 1 and returns the next value.

Statement B: You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence. This statement is also true. The CURRVAL pseudo column allows you to obtain the current value generated from a sequence without affecting the further values to be generated. It does not increment the sequence; it only returns the current value.

Statement C: You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence. This statement is incorrect. The NEXTVAL pseudo column does not retrieve the value from the sequence; it only returns the next possible value without affecting the sequence.

Therefore, the correct answer is option D: Both A and B. Both statements A and B correctly describe the usage of NEXTVAL and CURRVAL pseudo columns in relation to sequences in Oracle databases.

Multiple choice technology databases

You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the SELECT statement on which the view was create.) How do you obtain the definition of the view?

  1. Use the DESCRIBE command in the EMP_DEPT VU view.

  2. Use the DEFINE VIEW command on the EMP_DEPT VU view

  3. Use the DESCRIBE VIEW command on the EMP_DEPT VU view

  4. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To view the definition of a database view, you query the USER_VIEWS data dictionary view, which stores the view's SQL query text in the TEXT column. DESCRIBE only lists columns, data types, and nullability, not the defining query, and DEFINE/DESCRIBE VIEW are invalid commands.

Multiple choice technology databases

For which two constraints does the Oracle Server implicitly create a unique index?

  1. NOT NULL and PRIMARY KEY

  2. PRIMARY KEY and UNIQUE key

  3. FOREIGN KEY and PRIMARY KEY

  4. CHECK

  5. UNIQUE
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Oracle database automatically and implicitly creates a unique index to enforce unique value constraints. This occurs specifically when a PRIMARY KEY or a UNIQUE key constraint is defined on a table, ensuring fast lookups and preventing duplicate entries, whereas other constraints do not.

Multiple choice technology databases

You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables: EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME. The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES tables. How can you accomplish this task?

  1. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);

  2. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

  3. ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employee e, departments d WHERE e.department_id = d.department_id;

  4. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To add a column to an existing view in Oracle, you cannot use ALTER VIEW or MODIFY VIEW. The view must be recreated with the new column using the CREATE OR REPLACE VIEW statement along with the updated query select list.

Multiple choice technology databases

In ISQL plus how to see errorsof a plsql code

  1. A.SHOW ERRORS

  2. B. Query the USER_ERRORS data dictionary view

  3. Both A and B

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

In iSQL*Plus (and SQL*Plus), compilation errors in PL/SQL code can be viewed using the SHOW ERRORS command or by querying the USER_ERRORS data dictionary view. Both methods are valid options for developers to inspect errors, making 'Both A and B' the correct choice.

Multiple choice technology databases

What is true of using group functions on columns that contain NULL Values

  1. Group functions on columns ignore NULL values.

  2. Group functions on columns returning dates include NULL values

  3. Group functions on columns returning numbers include NULL values.

  4. Group functions on columns cannot be accurately used on columns that contain NULL values.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To answer this question, the user needs to understand the behavior of group functions when used on columns that contain NULL values.

Option A is correct. Group functions ignore NULL values in columns. For example, the AVG function calculates the average of all non-NULL values, while the COUNT function counts all non-NULL values. This means that NULL values are not included in the calculation and do not affect the result.

Option B and Option C are incorrect because they describe the behavior of specific group functions, rather than the behavior of group functions in general. The behavior of each group function depends on the specific function being used and the data type of the column being aggregated.

Option D is incorrect because group functions can be accurately used on columns that contain NULL values. However, it is important to keep in mind that NULL values may affect the result of certain operations, such as the SUM function. If a column contains both NULL and non-NULL values, the SUM function will return a NULL value, unless the column is aggregated using the NVL function or a similar function that replaces NULL values with a default value.

Therefore, the correct answer is:

The Answer is: A. Group functions on columns ignore NULL values.

Multiple choice technology databases

Which table should you query to determine when your procedure was last compiled?

  1. USER_OBJECTS

  2. USER_PROCEDURES

  3. USER_PROCS

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

USER_OBJECTS is the correct view to find compilation timestamps for stored procedures. It contains information about all schema objects including procedures, functions, packages, and their last modification dates (LAST_DDL_TIME). USER_PROCEDURES and USER_PROCS don't exist as standard views. USER_PLSQL_UNITS is not a valid Oracle view.

Multiple choice technology databases

A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A transaction begins with the first executable SQL statement (or after a previous transaction ends) and finishes with a COMMIT or ROLLBACK. It is not defined as the statements between two such commands, since a COMMIT/ROLLBACK terminates the current transaction rather than separating active ones.

Multiple choice technology databases

Choose correct statement below

  1. When a parameter is passed as an IN variable it is passed by reference

  2. When variables are passed in OUT or INOUT mode it is pass by value

  3. The NOCOPY clause tells to PL/SQL engine to pass the variable by reference

  4. All of the above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

IN parameters are passed by reference (a pointer to the value). OUT and IN OUT parameters are passed by value by default. The NOCOPY compiler hint tells PL/SQL to pass OUT and IN OUT parameters by reference instead of value for performance. All three statements about parameter passing modes are correct.

Multiple choice technology databases

How can you migrate from a LONG to a LOB data type for a column?

  1. Use the DBMS_MANAGE_LOB.MIGRATE procedure

  2. Use the UTL_MANAGE_LOB.MIGRATE procedure.

  3. Use the ALTER TABLE command

  4. You cannot migrate from a LONG to a LOB date type for a column
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Migrating from LONG to LOB is done using ALTER TABLE MODIFY command. You use ALTER TABLE table_name MODIFY column_name CLOB (or BLOB). There are no DBMS_MANAGE_LOB or UTL_MANAGE_LOB packages. The migration is fully supported and recommended as LONG is deprecated.

Multiple choice technology databases

You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE. What type of trigger do you create?

  1. FOR EACH ROW trigger on the EMP table.

  2. Statement-level trigger on the EMP table.

  3. FOR EACH ROW trigger on the AUDIT_TABLE table.

  4. Statement-level trigger on the AUDIT_TABLE table.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To monitor every row change in EMP and insert into AUDIT_TABLE, you need a row-level trigger (FOR EACH ROW) on the source table EMP. Statement-level triggers fire once per statement regardless of rows affected. The trigger should be on EMP, not AUDIT_TABLE, since you're monitoring changes to EMP.

Multiple choice technology databases

Select correct type of parameter mode used in oracle

  1. IN

  2. OUT

  3. IN OUT

  4. All of the above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Oracle procedures support three parameter modes: IN (read-only input), OUT (write-only output), and IN OUT (read-write input/output). These modes define how parameters can be modified within the procedure. All three modes are valid and commonly used in PL/SQL.