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

Select the VALID trigger type(s)?

  1. AFTER Statement Trigger

  2. INSERT row Trigger

  3. All the above

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

Valid trigger classifications include timing (BEFORE/AFTER) and level (statement/row). AFTER statement triggers fire once after the triggering statement completes. INSERT row trigger lacks proper timing classification and is not a standard trigger type designation in the classification system.

Multiple choice technology

What output will the follwing statement produce? Select NVL2(NULL,'NOT NULL', NULL) from dual;

  1. NULL

  2. NOT NULL

  3. Function NVL2 is not defined

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

The NVL2 function returns the second argument if the first is NOT NULL, otherwise returns the third argument. Since the first argument (NULL) is null, NVL2 returns the third argument which is NULL in this case. The result of selecting this from dual will be NULL.

Multiple choice technology programming languages

Which of the following exception is raised when an insert or update attempts to create two rows with duplicate values in columns constrained by unique index

  1. NOT_LOGGED_ON

  2. NO_DATA_FOUND

  3. VALUE_ERROR

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

DUP_VAL_ON_INDEX is the Oracle exception raised when trying to insert or update a row that would violate a UNIQUE constraint. NO_DATA_FOUND is when SELECT returns no rows, VALUE_ERROR is for type conversion issues, and NOT_LOGGED_ON relates to database connection.

Multiple choice technology programming languages

Which of the following is not true about 'Dual'

  1. It is a oracle table with only one row and one column

  2. It supports arithmetic calculations

  3. It doesnot support date retrival

  4. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The DUAL table in Oracle is a special table with one row and one column (DUMMY, value 'X'). It DOES support arithmetic calculations (SELECT 1+2 FROM DUAL) and DOES support date retrieval (SELECT SYSDATE FROM DUAL). Therefore option C is NOT true, making it the correct answer to the question.

Multiple choice technology embedded technologies

pick out the incorrect table name from the list mentioned below

  1. sppspupgmresource

  2. sppspmresource

  3. sppspuresource

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

In Ericsson's SPM resource management, the valid table names follow specific naming conventions: sppspupgmresource, sppspmresource, and sppspuresource are all valid. 'sppresource' is not a valid table name as it doesn't follow the established naming pattern. Option D is the correct answer.

Multiple choice technology web technology

How can you make a property be read from the database but not modified in anyway

  1. By using the insert="false" and update="false" attributes.

  2. By using the isinsert="false" and isupdate="false" attributes

  3. By using the isinsert="no" and isupdate="no" attributes

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

In Hibernate mappings, setting insert='false' and update='false' makes a property read-only - it will be retrieved from the database but never included in INSERT or UPDATE statements. The attribute names in options B and C are incorrect.

Multiple choice technology web technology

Which statement is correct?

  1. bag has index column

  2. bag permits duplicate element values

  3. bag does not permits duplicate element values

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

A 'bag' in Hibernate is a collection that ALLOWs duplicate elements and does not maintain element order (no index column). Unlike Lists (which have indexes) or Sets (which enforce uniqueness), bags can contain duplicates.

Multiple choice technology programming languages

what is the default value in hibernate ?

  1. lazy=false;

  2. lazy=true;

  3. lazy=yes;

  4. lazy=no;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In Hibernate 3 and newer versions, lazy initialization (lazy=true) is the default fetching strategy for associations and collections to optimize performance by avoiding loading unneeded related objects. Other options are syntax errors.

Multiple choice technology databases

Which two statements are true about the working of fine-grained access? (Choose two.)

  1. Security policies can be associated only with tables, but not with views.

  2. Different policies can be used for SELECT, INSERT, UPDATE, and DELETE statements.

  3. User statements are dynamically modified by the Oracle server through a security policy function.

  4. Fine-grained access control policies always remain in effect until they are dropped from a table or view.
Reveal answer Fill a bubble to check yourself
B,C Correct answer
Explanation

Oracle's Fine-Grained Access Control (FGAC) dynamically modifies SQL statements using policies that append WHERE clauses. Policies can differ by statement type (SELECT, INSERT, etc.) and apply to both tables and views.

Multiple choice technology databases

Examine the code in the following PL/SQL block: DECLARE TYPE NumList IS TABLE OF INTEGER; List1 NumList := NumList(11,22,33,44); BEGIN List1.DELETE(2); DBMS_OUTPUT.PUT_LINE ( 'The last element# in List1 is ' || List1.LAST || ' and total of elements is '||List1.COUNT); List1.EXTEND(4,3); END; / Which two statements are true about the above code? (Choose two.)

  1. LAST and COUNT give different values.

  2. LAST and COUNT give the same values.

  3. The four new elements that are added contain the value 33.

  4. The four new elements that are added contain the value 44.
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

After deleting element at position 2, LAST returns 4 (highest index) while COUNT returns 3 (actual elements), so they differ. The EXTEND(4,3) operation copies the value from index 3 (which is 33) into four new elements.

Multiple choice technology databases

Examine the structure of the DEPARTMENTS table. Name Null? Type ----------------------------- ------------ ---------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) LOCATION_ID NUMBER(4) View the Exhibit and examine the code that you plan to use for creating a package to obtain the details of an employee using a host variable on the client side. In SQL*Plus, you plan to use the following commands: SQL> VARIABLE x REFCURSOR SQL> EXECUTE emp_data.get_emp(195,:x) SQL> PRINT x Which statement is true about the above scenario?

  1. The package executes successfully and passes the required data to the host variable.

  2. The package specification gives an error on compilation because cursor variable types cannot be defined in the specification.

  3. The package specification gives an error on compilation because the cursor variable parameter was specified before you defined it.

  4. The package executes successfully, but does not pass the required data to the host variable because the cursor is closed before the
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The package executes successfully but fails to pass data because the cursor is closed before the host variable can access it. The cursor must remain open for the client to fetch the results through the host variable.