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

Which of the following is not true of SQL views?

  1. Oracle views cannot use the ORDER BY clause in view definitions.

  2. Oracle views are created using the standard SQL-92 CREATE VIEW command.

  3. Oracle views can be queried.

  4. The SQL-92 standard does not allow the use of the ORDER BY clause in view definitions.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The SQL-92 standard explicitly prohibits ORDER BY in view definitions because views represent unordered sets. However, Oracle extends this by allowing ORDER BY in conjunction with top-N queries or specific restrictions. Option A correctly states that Oracle views cannot use ORDER BY in standard view definitions without special syntax.

Multiple choice

When using SQL*Plus, Oracle commands, column names, table names and all other database elements ____.

  1. are case insensitive

  2. are case sensitive

  3. must always be in lower case

  4. must always be in upper case
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Oracle SQL*Plus is case insensitive for commands, table names, column names, and other database elements. 'SELECT * from employees' works the same as 'select * FROM EMPLOYEES'. However, string literals in WHERE clauses remain case sensitive.

Multiple choice

A table is being imported and it must be ensured that the table is put into a single contiguous area. There is not a single contiguous area large enough for the entire space allocated to the table. What should be done to achieve this?

  1. Use the cascade option.

  2. Use the constraint option.

  3. Use the primary key option.

  4. Coalesce the tablespace the table is using.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When a tablespace becomes fragmented with non-contiguous free space, using COALESCE merges adjacent free extents into larger contiguous extents. This is necessary when importing requires more contiguous space than any single free extent can provide.

Multiple choice

Which ALTER TABLE statement should be used to add a PRIMARY KEY constraint on the MANUFACTURER_ID column of the PRODUCT table?

  1. ALTER TABLE product ADD PRIMARY KEY (manufacturer_id);

  2. ALTER TABLE product MODIFY CONSTRAINT PRIMARY KEY manufacturer_id;

  3. ALTER TABLE product MODIFY manufacturer_id CONSTRAINT PRIMARY KEY;

  4. ALTER TABLE product ADD CONSTRAINT manufacturer_id PRIMARY KEY;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The correct syntax is ALTER TABLE table_name ADD PRIMARY KEY (column_name). Option A uses proper syntax with ADD PRIMARY KEY and the column in parentheses. Other options incorrectly use MODIFY or wrong keyword order.

Multiple choice

An Application was created based on the Employees Personal Information and the Department in which they work. It consists of two blocks. The first one is the Database Data block based on the Employees Personal Information like Name and Address and the other is non database data block based on the Employees Department Information. At runtime, the cursor is initially in the non database data block, the user clicks the Enter Query toolbar button and tries to search the Personal Information record of Employee whose Name is TOM. What would be the result?

  1. Query would execute and the Record providing the Department Information of the Employee matching the criteria would be displayed.

  2. Query would execute and the Record providing the Personal Information of the Employee matching the criteria would be displayed.

  3. Query would not execute as in the Enter query mode user is not allowed to navigate to other block so user can't even provide the search criteria of Employee Name in the Database data block.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In Enter Query mode, the cursor remains in the current block and navigation to other blocks is restricted. Since the cursor starts in the non-database block, the user cannot navigate to the database block to enter query criteria (Name = TOM), so the query cannot execute.

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 removes all rows from a table while keeping the table structure intact. Unlike DELETE (which is DML and can be rolled back), TRUNCATE is DDL that deallocates data pages and cannot be rolled back. It's faster but doesn't support WHERE clauses.

Multiple choice

JOHN is running a multiple-form application. The Customers Form invoked the Orders Form. When the Orders Form launches, it displays orders for only the active customer from the Customers form. Forms exchange data with the GLOBAL.CUSTOMER_ID variable. The Orders Form should be restricted to the orders for the current customer only when the query is issued for the first time after invoking the Orders form. Which built-in helps him do this by setting the ONETIME_WHERE property to refer dynamically to the GLOBAL.CUSTOMER_ID?

  1. SET_APPLICATION_PROPERTY

  2. SET_FORM_PROPERTY

  3. SET_BLOCK_PROPERTY

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

SET_BLOCK_PROPERTY is the correct built-in because ONETIME_WHERE is a block-level property in Oracle Forms that restricts a query to execute only once when the block is first queried. SET_APPLICATION_PROPERTY operates at application level, SET_FORM_PROPERTY at form level, and SET_RECORD_PROPERTY at individual record level - none of these can set the ONETIME_WHERE clause which belongs to the block's WHERE clause behavior.

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 query uses AND to combine two contradictory conditions: sal<2000 AND sal>9000. Since a value cannot simultaneously be less than 2000 AND greater than 9000, no rows will satisfy both conditions, resulting in an empty result set.

Multiple choice

You have linked SQL*Plus to an external text editor. To invoke the text editor for use within SQL*Plus, which command do you use?

  1. Open [FileName]

  2. Show [FileName]

  3. Alter [FileName]

  4. Edit [FileName]
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The EDIT command in SQL*Plus invokes the external text editor that has been linked to SQL*Plus. This allows you to edit SQL statements or PL/SQL blocks in your preferred editor and then return to SQL*Plus to execute them.

Multiple choice

Where is the GROUP BY clause statement placed in a SELECT statement that includes a WHERE clause?

  1. Immediately after the SELECT clause

  2. Before the WHERE clause

  3. After the ORDER BY clause

  4. After the WHERE clause
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In a SELECT statement with both WHERE and GROUP BY clauses, the WHERE clause must come before the GROUP BY clause. The correct order is: SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY. WHERE filters rows before grouping, while GROUP BY aggregates the filtered rows.

Multiple choice

To see the contents of the SQL*Plus buffer, type _____.

  1. CONTENTS

  2. BUFFER

  3. CURRENT

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

The LIST command displays the current contents of the SQL*Plus buffer. This is useful when you need to review or modify a SQL statement before executing it. Other options like CONTENTS, BUFFER, and CURRENT are not valid SQL*Plus commands.