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

A query Record Group was created at design time. Which built-in can be used to execute the query defined in the Record Group object?

  1. ADD_GROUP_ROW

  2. POPULATE_GROUP

  3. ADD_GROUP_COLUMN

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

POPULATE_GROUP executes the query associated with a query record group and populates it with data. This built-in is specifically designed for record groups created at design time or runtime that need to be populated from their defined query.

Multiple choice

The primary key on table EMP is the EMPNO column. Which of the following statements will not use the associated index on EMPNO?

  1. select * from EMP where nvl(EMPNO, '00000') = '59384';

  2. select * from EMP where EMPNO = '59384';

  3. select EMPNO, LASTNAME from EMP where EMPNO = '59384';

  4. select 1 from EMP where EMPNO = '59834';
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The NVL function wraps the EMPNO column, which prevents the database from using the index on EMPNO. When a column is modified by a function in the WHERE clause, the optimizer cannot perform an index range scan on that column because the indexed values don't match the function-transformed values. The other options (B, C, D) all use direct equality comparisons on EMPNO, which can leverage the index.

Multiple choice

What does the TRUNCATE statement do?

  1. Removes the table

  2. Removes all rows from a table

  3. Shortens the tale to 10 rows

  4. Removes all columns from a table
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

TRUNCATE is a DDL command that removes all rows from a table while preserving the table structure. Unlike DELETE (which is DML and can be rolled back), TRUNCATE cannot be rolled back and is faster because it doesn't generate redo logs for individual rows. It does not remove the table itself (option A), does not limit rows to 10 (option C), and does not affect column structure (option D).

Multiple choice

MILLER is an administrator who has FULL DBA privileges. When he attempts to drop the DEFAULT profile as shown, he receives the error message shown. Which of the following options best explains this error?

SQL> drop profile SYS.DEFAULT;

drop profile SYS.DEFAULT

*

ERROR at line 1:

ORA-00950: invalid DROP option

  1. The DEFAULT profile cannot be dropped.

  2. MILLER requires the DROP PROFILE privilege.

  3. Profiles created by SYS cannot be dropped.

  4. The CASCADE option was not used in the DROP PROFILE command.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The DEFAULT profile in Oracle is a special system profile that cannot be dropped. Oracle requires this profile to exist as a fallback for users who are not explicitly assigned other profiles. Even a user with FULL DBA privileges cannot drop it because it's a protected system object. The error message ORA-00950 indicates an invalid DROP option for this reason. Option B is incorrect because DBA privileges already include all necessary permissions. Option C is incorrect because SYS-created objects can generally be dropped unless they're protected like DEFAULT. Option D is incorrect because CASCADE is used for profiles with assigned users, not for DEFAULT itself.

Multiple choice

In this SQL statement, which of the following lines will produce an error?

  1. CREATE OR REPLACE TRIGGER check_age

  2. BEFORE INSERT OR UPDATE ON employee FOR EACH ROW

  3. IS

  4. years_old NUMBER;

  5. BEGIN

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

In the trigger code shown, line C ("IS") is on its own line before the variable declarations. This is incorrect syntax because the declaration section cannot begin immediately after "IS" without proper context. The correct order should be the trigger header (lines A-B), then "IS" on the same line or followed by declarations, but not as a standalone line. Lines A and B correctly define the trigger. Line D (variable declaration) and line E (BEGIN) are syntactically correct in their positions.

Multiple choice

Evaluate the following DELETE statement:

DELETE employee_id, salary, job_id FROM employees

WHERE dept_id = 40;

Why does the DELETE statement fail when executed?

  1. There is no row with dept_id 90 in the EMPLOYEES table.

  2. The JOB_ID column cannot be deleted because it is a NOT NULL column.

  3. Column names were not specified in the DELETE clause of the DELETE statement.

  4. The EMPLOYEE_ID column cannot be deleted because it is the primary key of the table.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The DELETE statement fails because it incorrectly lists column names in the DELETE clause. The correct DELETE syntax is DELETE FROM table_name WHERE condition - you don't specify columns to delete in DELETE. The DELETE operation removes entire rows, not specific columns. If the goal was to delete rows from employees where dept_id = 40, the correct syntax would be DELETE FROM employees WHERE dept_id = 40. Option A is irrelevant (wrong dept_id anyway). Option B is incorrect because DELETE doesn't have column-level constraints. Option D is incorrect because deleting rows doesn't affect the primary key definition.

Multiple choice

Which of the following statements is correct with regard to Data properties of a text item?

  1. If the Maximum Length of an item is set to a value that is greater than the value for Width, the item will not be displayed at run time.

  2. The Initial Value for an item can be set outside the range of values specified by the Lowest Allowed Value and the Highest Allowed Value properties because Initial Value defines an exception to that range.

  3. If the Required property is set to Yes for an item whose corresponding database column does not have a NOT NULL constraint, an error will occur at run time.

  4. If Data Length Semantics property is set to BYTE, the Maximum Length property may have to be manually adjusted depending on the character set that is being used.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When the Data Length Semantics property is set to BYTE, Oracle measures column length in bytes rather than characters. For multi-byte character sets (like UTF-8 or AL32UTF8), a single character might require multiple bytes. If Maximum Length is set based on character count but BYTE semantics are used, the actual byte capacity may be insufficient for the expected number of characters, requiring manual adjustment. Option A is false because display issues depend on item width, not Maximum Length. Option B is false because Initial Value must respect validation range constraints. Option C is false because Required property at the form level is independent of database constraints.

Multiple choice

Top N analysis requires ____________ and ____________.

  1. the use of rowed, only an inline view

  2. a GROUP BY clause, only an inline view

  3. an ORDER BY clause, an inline view and an outer query

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

Top-N queries require sorting data and then limiting results, which needs ORDER BY for sorting, an inline view to perform the sorting, and an outer query to restrict the rows returned. Option A is missing the outer query. Option B incorrectly suggests GROUP BY and lacks the outer query. The standard pattern is SELECT * FROM (SELECT ... ORDER BY ...) WHERE ROWNUM <= N.

Multiple choice

The following INSERT statement was issued:

INSERT /* +APPEND */ INTO arch.curr_order_hist

SELECT * FROM ord.curr_order;

Which of the following types of INSERT is performed?

  1. Conventional

  2. Direct-Load

  3. Parallel Direct-Load

  4. Serial-Conventional
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The /*+APPEND */ hint directs Oracle to perform a Direct-Load insert, which bypasses the buffer cache and writes data directly to datafiles, significantly improving performance for large inserts. This is different from Conventional insert (uses buffer cache) and Parallel Direct-Load (requires parallel execution). The hint makes Oracle treat the insert as a direct-path operation.

Multiple choice

What is the use of Translate Function in Oracle?

  1. It performs a word wise replacement of a string of numbers.

  2. It performs a character wise replacement of a number.

  3. It performs a word wise replacement of a string.

  4. It performs a character wise replacement of a string.
Reveal answer Fill a bubble to check yourself
B Correct answer
Multiple choice

What is the correct use of the Trunc command on a date?

  1. TRUNC=To_Date('09-Jan-02,DD-MON-YY,'YEAR',"Date" from Dual;

  2. Select TRUNC(To_Date('09-Jan-02,DD-MON-YY,YEAR')) "DATE" from Dual;

  3. Date =TRUNC(To_DATE('09-Jan-02','DD-MON-YY'),'YEAR'),'YEAR)"DATE: from DUAL;

  4. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

TRUNC on a date truncates it to a specified precision (year, month, day, etc.). The correct syntax is TRUNC(date_value, 'format'). Option D shows correct syntax: TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') truncates the date to the first day of the year. Options A, B, and C have malformed syntax with incorrect quote placement, missing parentheses, or wrong function structure.

Multiple choice

Which of the following operators is used in a correlated subquery?

  1. IN

  2. EXISTS

  3. UNION

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

The EXISTS operator is specifically designed for correlated subqueries, testing whether the subquery returns any rows. It's evaluated for each row of the outer query. While IN can also be used in subqueries, EXISTS is the canonical operator for correlated subqueries because it can stop processing once a match is found. UNION and INTERSECT are set operators for combining queries, not subquery correlation.

Multiple choice

Employee table has salary field in this case if we give the query : select * from employee where sal<2000 and sal>9000 which will yield result as ____.

  1. rows with Sal<2000 and Salary>9000 will be displayed

  2. all the rows are displayed with any one of the condition satisfied

  3. no rows will be selected

  4. between sal 2000 and 9000 will be displayed
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The condition sal<2000 AND sal>9000 is logically impossible - a value cannot be both less than 2000 and greater than 9000 simultaneously. Therefore, no rows will satisfy both conditions, and the query returns zero rows. This is a classic example of a mutually exclusive condition.

Multiple choice

After a table has been created, its structure can be modified using the SQL command ______.

  1. UPDATE TABLE [TableName]

  2. MODIFY TABLE [TableName]

  3. ALTER TABLE [TableName]

  4. CHANGE TABLE [TableName]
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

ALTER TABLE is the standard SQL DDL command for modifying the structure of an existing table. This command can be used to add, drop, or rename columns, change column data types, add or drop constraints, and modify other table properties. The UPDATE command modifies data within a table, not its structure. MODIFY, CHANGE, and other variations are not valid SQL commands for table structure modification - only ALTER TABLE is the correct syntax.

Multiple choice

What is the correct use of the Trunc command on a date?

  1. TRUNC=To_Date('09-Jan-02,DD-MON-YY,'YEAR',"Date" from Dual;

  2. Select TRUNC(To_Date('09-Jan-02,DD-MON-YY,YEAR')) "DATE" from Dual;

  3. Date =TRUNC(To_DATE('09-Jan-02','DD-MON-YY'),'YEAR'),'YEAR)"DATE: from DUAL;

  4. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Option D demonstrates the correct TRUNC function syntax with proper date formatting and string quoting. TRUNC accepts a date value and a format string, returning the date truncated to the specified precision level - in this case, 'YEAR' truncates to January 1st of that year. Options A, B, and C contain syntax errors including missing closing parentheses, incorrect quote usage, malformed function calls, and improper string literal formatting. The correct syntax requires TRUNC(date_value, format_string) with proper parentheses and string quotes.