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 programming languages

Consider the table and query below: Table Name: products category type price indoor kitchen 1.50 outdoor garden 6.00 indoor Bathroom 4.75 outdoor garden 20.20 indoor bathroom 4.25 Office desktop 9.80 SELECT category, COUNT(DISTINCT type) FROM products GROUP BY category How many rows are returned by the SQL statement listed above?

  1. One row

  2. Two rows

  3. Three rows

  4. Four rows
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The GROUP BY category produces one row per distinct category value. The table contains three unique categories: indoor, outdoor, and Office (these are category names in the leftmost column, not types). COUNT(DISTINCT type) counts distinct type values within each category, but this doesn't change the number of rows returned.

Multiple choice technology programming languages

While creating a view from two tables, you need to ensure that view cannot be affected by modifications to underlying table schema. What should you do to accomplish this goal with least possible amount of overhead?

  1. Create CHECK constraints on the tables

  2. Create a DDL trigger to rollback any changes to the tables if then changes affect the columns in the view.

  3. Create the view, specifying the WITH SCHEMABINDING option.

  4. Create the view, specifying the WITH CHECK option.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

SCHEMABINDING locks the view definition to the exact schema of underlying objects, preventing any schema changes (like dropping or renaming columns) that would break the view. This is a lightweight, declarative solution that imposes minimal overhead compared to triggers or manual constraint checking. It's the standard way to protect view integrity.

Multiple choice technology programming languages

You have created a cursor named test_cursor inside your stored procedure. Following is the cursor code: --------------------------------------------------------------------- DECLARE @Name AS VARCHAR(100) DECLARE test_cursor CURSOR FOR SELECT empname FROM Employee Order BY empid OPEN test_cursor FETCH NEXT FROM test_cursor INTO @Name WHILE @@FETCH_STATUS <> 0 BEGIN -- Do your internal procesing here FETCH NEXT FROM test_cursor INTO @Name END CLOSE test_cursor DEALLOCATE test_cursor --------------------------------------------------------------------- What correction is required in the code above?

  1. First Deallocate and then close

  2. Use @@FETCH_STATUS = 0 instead of @@FETCH_STATUS <> 0

  3. No need to use "FETCH NEXT FROM test_cursor INTO @Name" again before END statement

  4. Both i and ii
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The stored answer is incorrect. The loop condition @@FETCH_STATUS &lt;&gt; 0 is wrong because a status of 0 indicates success. To process rows, the loop should run while @@FETCH_STATUS = 0. Thus, option 540472 is correct.

Multiple choice technology databases

If a new object is created , DB2 automatically starts using it (True or False)

  1. True

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

To solve this question, the user needs to know how DB2 handles new objects.

The option A is incorrect because it's false. DB2 does not automatically start using a new object immediately after creation. The object must be activated before it can be used.

The option B is correct because DB2 does not automatically start using a new object. The object must be activated using the ACTIVATE DATABASE command or the ACTIVATE OBJECT command before it can be used.

Therefore, the correct answer is:

The Answer is: B

Multiple choice technology databases

Which one is the correct option for taking unload from the table EMPTAB

  1. UNLOAD TABLE DSN1DB04.EMPTABSP

  2. UNLOAD TABLESPACE DSN1DB04.EMPTABSP

  3. UNLOAD DATABASE DSN1DB04.EMPTABSP

  4. UNLOAD DATABASE DSN1DB04 EMPTABSP
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

UNLOAD TABLESPACE is the correct syntax for extracting data from a specific tablespace in DB2. UNLOAD TABLE and UNLOAD DATABASE are not valid DB2 utility syntax. The tablespace qualifier (DSN1DB04.EMPTABSP) specifies the exact object to unload.

Multiple choice technology databases

No need to use RUNSTATS when

  1. a table is unloaded

  2. a table is loaded

  3. an index is created

  4. a table is reorganized
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) A table is unloaded - This option is correct. When a table is unloaded, there is no need to use RUNSTATS. Unloading a table means that the data is being removed from the table, and therefore, there is no data to collect statistics on.

Option B) A table is loaded - This option is incorrect. When a table is loaded, it is recommended to use RUNSTATS to collect statistics on the newly loaded data. This helps the database optimizer make better decisions when generating query plans.

Option C) An index is created - This option is incorrect. When an index is created, it is recommended to use RUNSTATS to collect statistics on the indexed columns. This helps the database optimizer make better decisions when using the index in query plans.

Option D) A table is reorganized - This option is incorrect. When a table is reorganized, it is recommended to use RUNSTATS to collect statistics on the reorganized table. This helps the database optimizer make better decisions when generating query plans for the reorganized table.

The correct answer is A) a table is unloaded. This option is correct because there is no need to use RUNSTATS when a table is unloaded, as there is no data to collect statistics on.

Therefore, the correct answer is option A.

Multiple choice technology databases

To create a trigger you need the following option

  1. Triggering Table

  2. Triggering Event

  3. Triggered Action

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

Creating a trigger requires all three components: the triggering table (where the event occurs), the triggering event (INSERT/UPDATE/DELETE), and the triggered action (what executes). Each element is essential - without any one, the trigger definition is incomplete.

Multiple choice technology databases

Choose the correct options on LOCKSIZE

  1. Database

  2. Tablespace

  3. Table

  4. View
Reveal answer Fill a bubble to check yourself
B,C Correct answer
Explanation

LOCKSIZE is a parameter that specifies the granularity at which locks are acquired during operations. Valid LOCKSIZE options include: TABLESPACE, TABLE, PAGE, ROW, and ANY. Database (option A) is too coarse - locking at database level would lock all objects, which is not a standard LOCKSIZE value. View (option D) is not a physical storage object and cannot have LOCKSIZE specified. Tablespace (B) and Table (C) are valid LOCKSIZE values.

Multiple choice technology databases

Referential constraint is

  1. Designates the values that columns of a table can contain

  2. The limiting of a set of foreign key values to a set of primary key values

  3. The limiting of a set of Primary key values to a set of foreign key values

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

A referential constraint (foreign key constraint) ensures database integrity by restricting the values in a foreign key column to only those values that already exist in the referenced primary key column.

Multiple choice technology databases

Triggers can be defined on a system catalog table (True or False)

  1. True

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

A system catalog is a group of tables and views that incorporate important information about a database. It includes tables that contain information about schemas, tables, columns, users, and so forth. A database management system (DBMS) typically manages these system catalog tables, and they are used to maintain consistent and reliable database operations.

While it's technically possible to create triggers on regular tables in a database, system catalog tables are typically not allowed to have triggers. The system catalog tables are managed by the DBMS itself, and any modifications to these tables are tightly controlled to avoid inconsistency or corruption of the system catalog. Therefore, DBMSs usually restrict users from creating triggers on system catalog tables.

Let's go through each option to understand why it is correct or incorrect:

Option A) True - This option is incorrect because system catalog tables are managed by the DBMS, and it typically prevents users from creating triggers on these tables.

Option B) False - This option is correct because, as mentioned above, creating triggers on system catalog tables is usually not allowed.

The correct answer is B) False. This option is correct because creating triggers on system catalog tables is typically not permitted by the DBMS to prevent potential data inconsistencies or system corruption.

Multiple choice technology databases

By default, how much tablespace can any account use for a new table?

  1. None

  2. Up to the current free space in the tablespace

  3. Unlimited space, including autoextends

  4. Up to the default quota established at tablespace creation time
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

By default, a newly created user account has a quota of 'None' (zero) on any tablespace, meaning they cannot create tables or insert data until they are explicitly granted a tablespace quota.

Multiple choice technology databases

Which of the following is not an object privilege on a table?

  1. SELECT

  2. DEBUG

  3. REFERENCES

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

Oracle table object privileges include SELECT, INSERT, UPDATE, DELETE, REFERENCES, DEBUG, ON COMMIT REFRESH, QUERY REWRITE, and READ is NOT a valid object privilege on tables. READ exists for directories and materialized views in some contexts, but not for regular tables. SELECT is the correct privilege for reading table data.

Multiple choice technology databases

In Oracle 10g , which statement regarding the contents of the V$PARAMETER view is true?

  1. Displays only the list of all default values.

  2. Displays only the list of all basic parameters.

  3. Displays currently in effect parameter values.

  4. Displays only the list of all advanced parameter.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

V$PARAMETER displays all parameter values currently in effect for the running instance, including both dynamic changes (ALTER SESSION/SYSTEM) and static parameters. This is different from V$PARAMETER2 (splits list values) or V$SYSTEM_PARAMETER (shows instance-level values). It shows actual effective values, not just defaults or basic/advanced classifications.