Computer Knowledge

Database and SQL

3,929 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

Which of the following actions is required to prevent row migration on a table?

  1. Decrease PCTUSED

  2. Increase PCTUSED

  3. Decrease PCTFREE

  4. Increase PCTFREE

  5. Increase database block size

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

Row migration occurs when an updated row no longer fits in its original block and must move to another block, leaving a pointer behind. Increasing PCTFREE reserves more space in each block for future updates, allowing rows to grow in place. This prevents the need for rows to migrate to new blocks, maintaining performance and reducing chaining.

Multiple choice

If you want to change a replicated table's structure, how will you proceed?

  1. Open Oracle Enterprise manager and change the table's structure.

  2. Make the change by calling DBMS_REPCAT.ALTER_MASTER_REPOBJECT().

  3. Get any popular third party GUI tool to make the change.

  4. Open SQL*Plus and make the change at each of the replicated sites.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

When using Oracle replication (advanced replication), you cannot directly modify replicated table structures with DDL commands. You must use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT() procedure to ensure the change propagates correctly to all replication sites while maintaining replication consistency.

Multiple choice

Examine the structure of the given STUDENTS table. STUDENT_ID NUMBER NOT NULL, Primary Key
STUDENT_NAME VARCHAR2 (30)
COURSE_ID VARCHAR2 (10) NOT NULL MARKS NUMBER START_DATE DATE FINISH_DATE DATE A report is to be created of ten students who achieved the highest ranking in the course INT_SQL and completed the course in the year 1999. Which SQL statements accomplishes this task?

  1. SELECT student_id, marks, ROWNUM “Rank” FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL'ORDER BY marks DESC;

  2. SELECT student_id, marks, ROWID “Rank” FROM students WHERE ROWID <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'AND course_id = 'INT_SQL'ORDER BY marks;

  3. SELECT student_id, marks, ROWNUM “Rank” FROM (SELECT student_id, marks FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC);

  4. SELECT student_id, marks, ROWNUM “Rank” FROM (SELECT student_id, marks FROM students WHERE finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC)WHERE ROWNUM <= 10 ;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

ROWNUM is assigned BEFORE the ORDER BY clause executes, so filtering with ROWNUM <= 10 in the same query level returns arbitrary 10 rows. To get the top 10 by marks, you must ORDER BY marks DESC in a subquery first, then apply ROWNUM <= 10 in the outer query. Option D correctly implements this pattern.

Multiple choice

What type of view is implemented as a subquery embedded in the FROM clause of a SELECT statement and is identified by an alias?

  1. Simple

  2. Inline

  3. Complex

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

An inline view is a subquery that appears in the FROM clause and is treated like a table with an alias. Unlike a named view stored in the data dictionary, an inline view exists only for the duration of the query execution. It allows you to simplify complex queries and join derived result sets.

Multiple choice

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

  1. cursor CAPITALS is

  2. select CITY, STATE

  3. into my_city, my_state

  4. from CITIES

  5. where CAPITAL = 'Y';

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

The INTO clause must come after the SELECT statement's column list but before the FROM clause. The correct order in a cursor declaration is: CURSOR name IS SELECT column_list INTO variable_list FROM table WHERE condition. The 'into my_city, my_state' line is misplaced in the structure.

Multiple choice

What is the purpose of the INCLUDING clause in the CREATE TABLE syntax for an Index Organized Table?

  1. It specifies at which column to break a row into two pieces when a row's length exceeds the size set aside in PCTTHRESHOLD.

  2. It specifies the name of the primary key column in the index organized table.

  3. It specifies what percentage of the entire data block to hold open in order to store the row data associated with a primary key value.

  4. It specifies the tablespace where the second half of the row data will be stored when the row's length exceeds the size set aside in PCTTHRESHOLD.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In Index Organized Tables (IOTs), the INCLUDING clause specifies which column should be the last column stored in the index segment when a row exceeds PCTTHRESHOLD. Columns after the INCLUDING column are stored in the overflow segment. This controls how rows are split between the index and overflow areas.

Multiple choice

How many columns are presented after executing the given query? SELECT address1||','||address2||','||address2 Adress FROM employee;

  1. 0

  2. 1

  3. 2

  4. 3

  5. 4

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

The concatenation operator (||) combines multiple column values into a single expression. The query concatenates address1, address2, and address3 (note: address2 appears twice in the statement) into one column aliased as 'Adress'. Despite concatenating 3 source columns, the result is a single output column.

Multiple choice

To produce a meaningful result set without any cartesian products, what is the minimum number of conditions that should appear in the WHERE clause of a four-table join?

  1. 8

  2. 5

  3. 4

  4. 3

  5. 2
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To avoid cartesian products in a four-table join, you need at least 3 join conditions (n-1 for n tables). Each condition links a new table to the already-joined set, ensuring all tables are properly connected. With 4 tables, 3 conditions create a chain like T1-T2-T3-T4, preventing disconnected tables and cartesian products.

Multiple choice

Which of the following would stop the execution of a query if the query failed to rewrite?

  1. select /+ REWRITE_ON_ERROR/...

  2. select /+ REWRITE_OR_ERROR/...

  3. select /+ REWRITE_IN_ERROR/...

  4. select /+ REWRITE_ERROR/...

  5. select /+ REWRITE_If_ERROR/...
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The REWRITE_OR_ERROR hint causes query execution to stop if the optimizer fails to rewrite the query using a materialized view. Other listed hint variants (REWRITE_ON_ERROR, REWRITE_IN_ERROR, REWRITE_ERROR, REWRITE_If_ERROR) are not valid Oracle hints. This is useful when you want to ensure materialized view usage for performance.

Multiple choice

Which of the following statements is valid regarding index clusters?

  1. Index clusters can only be used for tables with low cardinality columns.

  2. Index clusters are generally well suited for tables that have many full table scans.

  3. Normal B-Tree indexes do not store null key values, whereas cluster indexes store null keys.

  4. A cluster index always takes up much more storage space than a normal index for the same set of key values.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In Oracle databases, standard B-Tree indexes exclude rows with NULL key values to save space, while cluster indexes include NULL entries in the index structure. This distinction is important because cluster indexes maintain the cluster key relationship even when values are null. Options A and B are incorrect because index clusters are typically used for high-cardinality columns and are not optimized for full table scans. Option D is incorrect because storage overhead depends on clustering factor, not the index type itself.

Multiple choice

A view was created containing groups of data. It does not allow DML operations, and does not contain a subquery. What type of view was created?

  1. Inline

  2. Simple

  3. Complex

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

A complex view contains GROUP BY clauses, DISTINCT expressions, or aggregate functions, which prevent DML operations through the view. The presence of GROUP BY makes this view complex by definition. Simple views allow DML operations and don't have such complexity. Inline views are subqueries in the FROM clause, not a separate view type. Explicit refers to how the view is created, not its characteristics.

Multiple choice

The EMP table contains the following columns: EMPNO NUMBER(6)

HIREDATE DATE

Review the following SELECT statement:

SELECT empno, TO_DATE('31-DEC-05') - hiredate

FROM emp

WHERE hiredate BETWEEN '31-DEC-04' AND '31-DEC-05';

What are the results of this statement?

  1. Only numeric values are returned.

  2. The employee numbers of any employees hired on January 1, 2004 are displayed.

  3. Date and numeric values are returned.

  4. The employee numbers of all employees hired before the year 2000 are displayed.

  5. Only the employee numbers of all employees are displayed because the BETWEEN operator used in the WHERE clause condition is a number function.
Reveal answer Fill a bubble to check yourself
A Correct answer
Multiple choice

Select can be used in FROM clause of SQL select.

  1. True

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

A SELECT statement can indeed be used in the FROM clause of another SELECT statement. This creates an inline view or derived table, allowing the results of a subquery to be treated as a table source in the outer query. This is a fundamental SQL feature used for breaking complex queries into simpler, more readable components. The option claiming 'False' is incorrect.