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 technology databases

___is the act of breaking the submitted statement down into its component parts, determining what type of statement it is (query, DML, or DDL), and performing various checks on it

  1. Parsing

  2. Optimization

  3. Row source generation

  4. Statement execution

  5. All of the above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Parsing breaks SQL statements into component parts (keywords, table names, column names) and determines the statement type (DML, DDL, DCL, or query). It performs syntactic validation before optimization or execution occurs.

Multiple choice technology databases

Select the steps involved in executing statements in Oracle

  1. Parsing

  2. Optimization

  3. Row source generation

  4. Statement execution

  5. All of the above
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Oracle statement execution follows a sequence: parsing (syntax/semantic validation), optimization (execution plan generation), row source generation (retrieval strategy), and statement execution (actual data access). All steps are required.

Multiple choice technology platforms and products

Match the EJB functions given below with the functionality equivalent in SQL A.) ejbStore() 1.) INSERT B.) ejbLoad() 2.) UPDATE C.) ejbCreate() 3.) SELECT

  1. A->1, B->2, C->3

  2. A->3, B->2, C->1

  3. A->2, B->1, C->3

  4. A->2, B->3, C->1
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

ejbStore() synchronizes entity bean state to database (UPDATE), ejbLoad() refreshes bean state from database (SELECT), and ejbCreate() inserts new data (INSERT). The mapping is ejbStore->UPDATE, ejbLoad->SELECT, ejbCreate->INSERT.

Multiple choice technology databases

What option is suited for adding an identity column in a existing table index to make a non uniqe index a unique one

  1. auto identity

  2. identity in nonunique index

  3. unique auto_identity index

  4. async log service
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The 'identity in nonunique index' configuration option allows Sybase ASE to automatically add an identity column to a non-unique index, effectively making it unique. This is useful for enabling unique constraints without changing the table schema.

Multiple choice technology databases

What clause can be used to check if a dump is valid or not along with load database statement

  1. with header

  2. with headerofdump

  3. with dumpstatus

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

The 'with listonly' clause used with load database allows you to verify a dump's validity without actually performing the full restore. It displays the dump header information and checks for corruption, serving as a pre-restore validation step.

Multiple choice technology databases

What are the DML statements you can use on a synonym created for a table?

  1. Select

  2. Insert

  3. Update

  4. Delete
Reveal answer Fill a bubble to check yourself
A,B,C,D Correct answer
Explanation

Synonyms function as transparent aliases for underlying tables, supporting full DML operations. You can execute SELECT, INSERT, UPDATE, and DELETE statements on a synonym just as you would on the base table itself. The synonym simply redirects the operation to the actual table, so all standard table operations are available. This makes synonyms useful for simplifying long table names or providing location-independent references.

Multiple choice technology databases

Which are not an advantages of a synonym?

  1. Alias for a schema object

  2. Provide security by hiding the name of the object

  3. Provide security by hiding the schema owner of the object

  4. Reduce complexity in SQLs by creating short synonyms names for objects with long names

  5. By granting privilege to synonym, granting privilege to the underlying object can be avoided
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Synonyms provide several advantages including creating aliases for schema objects, hiding object names and schema owners for security, and reducing SQL complexity with short names. However, granting privileges on a synonym does NOT automatically grant privileges on the underlying object - you must still grant privileges directly on the base object. Privileges must be granted on the actual table, view, or other object that the synonym references, not on the synonym itself.

Multiple choice technology databases

There are 2 schemas – S1 and S2 in a database. S1 has a table T1 in it. S2 don’t have any privileges on objects of S1. A synonym SYN_T1 is created in the schema S2 for S1.T1 and select privilege is granted to S2 on the synonym. Now, the newly created synonym is dropped. What will be the output of the following query executed from S2: Select * from S1.T1

  1. Error - Table or view does not exist

  2. Error - Synonym translation is invalid

  3. Output – all the rows of S1.T1

  4. Output – no rows retrieved
Reveal answer Fill a bubble to check yourself
C Correct answer
Multiple choice technology databases

Which table is used to identify the changes made to objects
  1. V$Session

  2. USER_CHANGES

  3. USER_OBJECTS

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

The USER_OBJECTS data dictionary view contains information about all objects owned by the current user, including their creation and modification timestamps. This view can be used to track when objects were created, last modified, or dropped, making it useful for identifying changes made to database objects. V$Session is for session information, while USER_CHANGES and USER_TRACKER are not standard Oracle views for this purpose.

Multiple choice technology databases

There are 2 schemas – S1 and S2 in a database. S1 has a table T1 in it. The following command is executed from the DBA user: Create public synonym S2.SYN_T1 for S1.T1 What will be the result?

  1. Public synonym will be created

  2. Only a private synonym will be created

  3. Error

  4. Public synonym will be created, but accessible only from S2
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The CREATE PUBLIC SYNONYM command creates a public synonym in the database's public schema, not in a specific user's schema like S2. Oracle does not allow specifying a schema name (like S2) when creating a public synonym - public synonyms are stored in a special PUBLIC schema accessible to all users. The syntax 'Create public synonym S2.SYN_T1 for S1.T1' is invalid because it attempts to qualify a public synonym with a schema name, which will result in an error.

Multiple choice technology databases

PL/SQL and Java can be used to create Oracle stored procedures and triggers

  1. True

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

Oracle supports multiple languages for creating stored procedures and triggers. PL/SQL is Oracle's native procedural language, while Java can also be used through Oracle's Java Virtual Machine integration. Both languages are fully supported for creating these programmatic database objects, giving developers flexibility in choosing their preferred implementation language. This is an accurate statement about Oracle's stored procedure language support.

Multiple choice technology databases

Limitation for level of label Nesting in a PL/SQL program

  1. No limitations

  2. 89

  3. 76

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

Oracle PL/SQL has a maximum nesting level of 98 for labels (block labels). Labels are used to name PL/SQL blocks for GOTO statements and qualified references with the <> syntax. While you can create deeply nested block structures, Oracle imposes this practical limit to prevent excessive complexity and potential performance issues. The 'no limitations' option is incorrect, and the specific numbers 89 and 76 are not the actual Oracle limits.