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

For which of the following tasks would it be most appropriate to use a PL/SQL WHILE loop?

  1. To add six new services to the SERVICE table

  2. To change MACHINE_ID 123456 to 456789 and change all others to 900000

  3. To change MACHINE_ID 123456 to 456789

  4. To add new services to the SERVICE table until finished
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A WHILE loop is used when you need to iterate based on a condition and don't know in advance how many iterations are required. Option D describes 'until finished' which implies conditional iteration based on completion status. The other options (A, B, C) are one-time operations that don't require looping - they can be accomplished with single SQL statements.

Multiple choice

In creating a procedure, you may get a message if you have compile errors. Which of the following is true?

  1. The line numbers reported match the line numbers you see in your text editor.

  2. SQL*Plus will automatically show the errors to you.

  3. To see the errors, enter SHOW ERRORS in SQL*Plus.

  4. If there are no syntax errors, you will receive the message NO ERRORS.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

After creating a procedure with compilation errors, SQL*Plus displays a warning but doesn't automatically show the errors. You must explicitly type SHOW ERRORS (or SHO ERR) to see the error messages with line numbers. The line numbers in error messages often don't match text editor line numbers because Oracle counts differently. The message 'NO ERRORS' is never displayed for successful compilation.

Multiple choice

An export that backs up tables that have changed since the last cumulative or complete export is known as _____.

  1. an incremental export

  2. a cumulative export

  3. a complete export

  4. a full export

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

A cumulative export backs up only tables that have changed since the last cumulative or complete export. This is different from incremental export (since last incremental export of any type) and complete/full export (all tables regardless of changes). Cumulative provides a middle ground, capturing changes since the last major backup.

Multiple choice

Which character is used to continue a statement in SQL*Plus?

  2. /

  4. @

  5. =

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

In SQL*Plus, the hyphen (-) is used to continue a SQL statement on the next line. When you end a line with a hyphen, SQL*Plus treats the next line as a continuation of the current statement. Other symbols have different meanings: * is for comments, / executes the SQL buffer, and @ runs a script file.

Multiple choice

The titles of all the books that meet the following criteria has to be displayed:

  1. Purchased before Feb 21, 2002
  2. Price is less than $500 or greater than $900 The result has to be sorted by the date of purchase, starting with the most recently bought book. Which of the following statements should be used?

  1. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < '21-FEB-2002' ORDER BY purchase_date

  2. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date< '21-FEB-2002' ORDER BY purchase date ASC

  3. SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC

  4. SELECT Book_title FROM books WHERE (price < 500 OR Price>900)AND purchase_date<'21-FEB-2002' ORDER BY purchase date DESC
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The correct query filters books purchased before Feb 21, 2002 with price outside the $500-$900 range (less than $500 OR greater than $900). The date condition uses < for 'before'. Results are sorted by purchase_date DESC to show most recent first. Option D has the correct logic with proper parentheses and DESC sorting. Note: there are minor typos but the logic is correct.

Multiple choice

What is the output of the given query? SELECT ROWNUM,ENAME,SAL FROM EMP WHERE ROWNUM<=2

  1. It shows only the second record.

  2. It shows only the record 1 and 2.

  3. It shows no row rows selected.

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

The query returns no rows. This is because ROWNUM is assigned BEFORE the WHERE clause filter is applied. When you filter with ROWNUM <= 2, Oracle tries to assign row numbers, but the WHERE clause eliminates rows before ROWNUM can be properly assigned. The condition ROWNUM <= 2 never allows any row to be returned. To limit rows, use ROWNUM in a subquery or use FETCH FIRST in modern Oracle.

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.