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

Tom is developing a Human Resources form for HR clerks to insert, update, and delete records from the EMPLOYEES table. When the user commit records, Forms displays the default informative message 'FRM-40400: Transaction complete: records applied and saved.' He wants to replace that with the message 'Records inserted:', 'Records updated:', 'Records deleted:', Which of the following triggers must be created to modify or to accomplish this?

  1. On-Message only

  2. Post-Commit and On-Message

  3. Post-Database-Commit and On-Message

  4. Post-Insert, Post-Update, Post-Delete, and On-Message

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

To replace the default Forms message with specific counts for insert/update/delete operations, you need Post-Insert, Post-Update, and Post-Delete triggers to capture counts after each operation type, plus an On-Message trigger to intercept and replace the FRM-40400 message. Post-Database-Commit fires too late (after transaction completes), and Post-Commit doesn't distinguish between operation types.

Multiple choice

An index needs to be created on the EMP_ID column of the EMPLOYEE table which satisfies the following conditions:

  1. The index will be called EMP_PK.
  2. The index should be sorted in ascending order.
  3. The index should be created in the INDEX01 tablespace, which is a dictionary managed tablespace.
  4. All extents of the index should be 1 MB in size.
  5. The index should be unique.
  6. No redo information should be generated when the index is created.
  7. 20% of each data block should be left free for future index entries.

Which of the following commands create the index and meets all the requirements?

  1. CREATE UNIQUE INDEX emp_pk ON Employee(Emp_id) TABLESPACE index0l PCTFREE 20 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0);

  2. CREATE UNIQUE INDEX emp_pk ON employee(emp_id) TABLESPACE index0l PCTFREE 20 STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0) NOLOGGING;

  3. CREATE UNIQUE INDEX emp_pk ON Employee(emp_id) TABLESPACE index0l PCTUSED 80 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0) NOLOGGING;

  4. CREATE UNIQUE INDEX emp_pk ON employee(emp_id) TABLESPACE index0l PCTUSED 80 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0);
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Option B meets all requirements: UNIQUE constraint ensures uniqueness, NOLOGGING suppresses redo generation, PCTFREE 20 reserves 20% of each block, STORAGE clause sets 1M extents, and the index is created in INDEX01 tablespace. Option A uses 'lm' instead of '1m' (typos), C incorrectly uses PCTUSED, and D lacks NOLOGGING.

Multiple choice

A SQL SELECT statement containing the ROWNUM pseudocolumn, an ORDER BY clause, and a subquery was created. What construct was created?

  1. A Complex View

  2. A Top-n Query

  3. A Materialized View

  4. A Hierarchical Report
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A Top-n query uses ROWNUM with ORDER BY in a subquery to limit results to the top n rows after sorting. The construct involves nesting a query with ORDER BY inside another that filters on ROWNUM to get the first n records. This is different from complex views, materialized views, or hierarchical reports.

Multiple choice

The DBA added a STATE column defined as VARCHAR2(2) to the State table. This column will contain two-letter state abbreviations because the State table is quite large, storing complete state names would waste space in the database. The DBA asks JOHN to add a corresponding State item to the State block of the Geographical Management form. JOHN opened the Layout Editor of the Geographical Management form and created a list item on the canvas in the State block. In the Property Palette for the list item, he changed the Name property to State and the Database Item property to Yes. He then open the Elements in List property and add some state abbreviations and names to the list, as shown in the exhibit. When he ran the form to test it, after selecting a value from the list and clicking Commit, he received an FRM error. Its corresponding database error is the following: ORA-01401: inserted value too large for column What should he do to correct this problem?

  1. Have the DBA increase the column width in the database.

  2. Change the List Style property for the State item to Combo Box.

  3. Change the List Style property for the State item to T-list.

  4. Change the elements in the list of the State item so that the list elements are the state names and the list element values are the state abbreviations.t.

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

The error occurs because John is storing full state names in the list elements, but the database column is VARCHAR2(2) expecting only abbreviations. The solution is to use state names as display labels (what users see) while mapping them to 2-character abbreviations as actual values stored in the database.

Multiple choice

When is the use of fine-grained auditing beneficial?

  1. Unaudited access should be allowed to the CONTACT_ ADDRESS column of the CUSTOMER table from within the organization, but any access to the CONTACT_ADDRESS column occurring via the Internet should be tracked

  2. The Human Resources Administrator should be alerted every time someone accesses an executive's salary in the EMPLOYEE table

  3. Comparison between the before and after values of each update of the PRICE column of the PRODUCT table should be facilitated

  4. All users who updated the CUSTOMER table on a specific date should be identifiable

  5. All accesses of the EMPLOYEE table should be tracked
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

Fine-grained auditing allows conditional audit policies based on specific column access or access conditions. Option A correctly uses FGA to track internet-based access to sensitive customer address data while allowing internal access. Option B demonstrates FGA's alert capability for specific high-value columns like executive salaries. Option C describes standard value-based auditing using triggers, not FGA. Option D and E describe standard table-level auditing which doesn't require FGA's granularity.

Multiple choice

What operator is used to prevent the error: 'ORA-01427:single row sub query returns more than one row'?

  1. Use the IN operator

  2. Use the >= operator

  3. Use the CAN EXIST operator

  4. Use the = operator
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The ORA-01427 error occurs when a subquery that should return only one row returns multiple rows. The IN operator is designed to handle multiple values and eliminates this error by checking if a value exists in the result set rather than requiring equality with a single row.

Multiple choice

The DBA issues the following command: SQL< alter table ORDER SHRINK SPACE What be the result of this statement?

  1. There would be only a compacting of the data, not a release of data

  2. There would be no impact on DML operations in the database

  3. There would be immediate release of the free space

  4. There would be an impact on the DML operations in the database
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The SHRINK SPACE operation compacts data and can release free space, but it requires exclusive locks on segments. During this operation, DML (Data Manipulation Language) operations like INSERT, UPDATE, DELETE are blocked, causing an impact on database operations.

Multiple choice

How many triggers can be applied to a single table in Oracle?

  1. 10

  2. 12

  3. 15

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

Oracle allows 12 triggers per table: one BEFORE and one AFTER for each of the six operations (INSERT, UPDATE, DELETE, plus their row-level variants INSERT ROW, UPDATE ROW, DELETE ROW). This gives maximum flexibility for enforcing business rules at different execution points.

Multiple choice

Which SELECT statement is an equijoin query between two tables?

  1. SELECT location.location_name, employee.salary FROM location, employee WHERE location.id = employee.location_no;

  2. SELECT location.location_name, employee.salary FROM location, employee WHERE employee.salary BETWEEN location.avg_salary AND location.max_salary;

  3. SELECT location.location_name, employee info.last_name FROM employee location, employee employee info WHERE employee info.id >= location.manager_id;

  4. SELECT location.location_name, employee.salary FROM location, employee WHERE location.id = employee.region_no(+);
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

An equijoin is a join based on equality between columns from two tables. Option A shows the classic equijoin syntax using a WHERE clause with the equality operator (=) to match location.id with employee.location_no. Option B uses a BETWEEN range condition, not equality. Option C uses >= which is a non-equal comparison. Option D uses the outer join operator (+), which creates an outer join, not an equijoin.

Multiple choice

Evaluate the following SQL statement:

SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales WHERE e.employee_id = s.emp_id;

What will happen if all the parentheses are removed from the calculation?

  1. The value displayed in the CALC_VALUE column will be lower

  2. The value displayed in the CALC_VALUE column will be higher

  3. There will be no difference in the value displayed in the CALC_VALUE column

  4. An error will be reported
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

SQL operator precedence ensures multiplication always happens before addition, regardless of parentheses. The expression (.15*e.salary) + (.5*e.commission_pct) + (s.sales_amount * (.35*e.bonus)) evaluates identically without parentheses because all multiplication operations occur before any addition operations. The nesting of parentheses doesn't change this fundamental precedence rule. Options A and B are incorrect because the value remains the same. Option D is incorrect because no error occurs.

Multiple choice

Which SQL phrase is not supported by Oracle?

  1. ON DELETE CASCADE

  2. ON UPDATE CASCADE

  3. CREATE SEQUENCE [SequenceName]

  4. DROP SEQUENCE [SequenceName]
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Oracle SQL supports ON DELETE CASCADE for foreign key constraints, which automatically deletes child rows when the parent row is deleted. However, Oracle does not support ON UPDATE CASCADE - this is a MySQL-specific feature. If you need to update primary key values, you must manually update all dependent foreign key values or recreate the rows. Options A, C, and D are all supported Oracle SQL statements.

Multiple choice

TOM wants to create a new data block. He clicked the Data Blocks node in the Object Navigator, and then clicked Create to invoke the Create Data Block dialog box. As he clicked through the pages of the wizard, Create Relationship option appears. Which of the following statements hold true about the form?

  1. The block he is creating is based on a view

  2. The block he is creating is based on a stored procedure

  3. The form contains at least one block in addition to the block he is creating

  4. In addition to the block he is creating, the form contains at least two other blocks that are master detail blocks

  5. The block he is creating is based on a table that has at least one foreign key relationship to another table
Reveal answer Fill a bubble to check yourself
B Correct answer
Multiple choice

What MySQL property is used to create a surrogate key in MySQL?

  1. UNIQUE

  2. SEQUENCE

  3. AUTO_INCREMENT

  4. None of the above -- Surrogate keys are not implemented in MySQL
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

AUTO_INCREMENT is the MySQL property used to create surrogate keys. It automatically generates a unique numeric value for each new row, typically used for primary key columns. UNIQUE is a constraint that ensures column values are distinct but doesn't auto-generate values. MySQL doesn't use SEQUENCE objects like some other databases (Oracle, PostgreSQL).

Multiple choice

What MySQL property is used to create a surrogate key in MySQL?

  1. UNIQUE

  2. SEQUENCE

  3. AUTO_INCREMENT

  4. None of the above -- Surrogate keys are not implemented in MySQL
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

AUTO_INCREMENT is the MySQL property used to create surrogate keys. It automatically generates a unique numeric value for each new row, typically used for primary key columns. UNIQUE is a constraint that ensures column values are distinct but doesn't auto-generate values. MySQL doesn't use SEQUENCE objects like some other databases (Oracle, PostgreSQL).