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

Which two statements are true about constraints? (Choose two.)

  1. The UNIQUE constraint does not permit a null value for the column.

  2. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.

  3. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index

  4. The NOT NULL constraint ensures that null values are not permitted for the column
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

PRIMARY KEY and UNIQUE constraints automatically create unique indexes to enforce uniqueness. NOT NULL explicitly prevents null values. UNIQUE constraints do permit null values (one per column in Oracle, multiple in standard SQL - though Oracle treats NULLs as not equal to each other), and FOREIGN KEY constraints create indexes for referential integrity checking but not unique indexes.

Multiple choice technology databases

Which three are true? (Choose three.)

  1. A MERGE statement is used to merge the data of one table with data from another.

  2. A MERGE statement replaces the data of one table with that of another.

  3. A MERGE statement can be used to insert new rows into a table.

  4. A MERGE statement can be used to update existing rows in a table.
Reveal answer Fill a bubble to check yourself
A,C,D Correct answer
Explanation

MERGE combines insert and update operations in a single statement to synchronize tables. It inserts new rows when matches aren't found and updates existing rows when matches exist. MERGE does not replace entire table contents - it performs targeted operations based on match conditions between source and target.

Multiple choice technology databases

Which is a valid CREATE TABLE statement?
  1. CREATE TABLE EMP9$# AS (empid number(2));

  2. CREATE TABLE EMP*123 AS (empid number(2));

  3. CREATE TABLE PACKAGE AS (packid number(2));

  4. CREATE TABLE 1EMP_TEST AS (empid number(2));
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Oracle table names must start with a letter, can contain letters, digits, and underscores, and cannot be a reserved word. Option A (EMP9$#) is valid because $ and # are allowed characters. Options B (*), C (PACKAGE is reserved), and D (starts with digit 1) are invalid.

Multiple choice technology databases

Which three are true regarding the use of outer joins? (Choose three.)

  1. You cannot use IN operator in a condition that involves an outer join

  2. You use (+) on both sides of the WHERE condition to perform an outer join

  3. You use (*) on both sides of the WHERE condition to perform an outer join

  4. You use an outer join to see only the rows that do not meet the join condition

  5. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outer join

  6. You cannot link a condition that is involved in an outer join to another condition by using the OR operator.
Reveal answer Fill a bubble to check yourself
A,E,F Correct answer
Explanation

In Oracle's legacy outer join syntax, you cannot use the IN operator or link outer join conditions with OR. Additionally, the (+) operator is placed next to the column of the table that is deficient in matching rows.

Multiple choice technology databases

Which constraint can be defined only at the column level?

  1. UNIQUE

  2. NOT NULL

  3. CHECK

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

NOT NULL is the only constraint that must be defined at the column level. All other constraints (UNIQUE, PRIMARY KEY, CHECK, FOREIGN KEY) can be defined at either the column level or table level. NOT NULL cannot be defined at the table level because it applies to individual columns.

Multiple choice technology databases

Which best describes an inline view?

  1. a schema object

  2. a subquery that can contain an ORDER BY clause

  3. another name for a view that contains group functions

  4. a subquery that is part of the FROM clause of another query
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

An inline view is a subquery enclosed in parentheses and placed in the FROM clause of a parent query. It acts as a temporary virtual table during query execution and is not stored as a schema object in the database.

Multiple choice technology programming languages

How do you rename a table ?

  1. alter table table_name rename to new_table_name;

  2. RENAME table_name new_table_name;

  3. Dropping the table and creating once again

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

In SQL, the standard DDL command to rename an existing table is ALTER TABLE table_name RENAME TO new_table_name;.

Multiple choice technology web technology

What is the out put of Joiner Transformation, if we use Detail Outer Join

  1. All the rows from master and detail table

  2. Matching rows from detail table and all rows from master table

  3. Matching rows from master table and all rows from detail table

  4. Only matching from master and detail table
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In a Detail Outer Join, all rows from the Master table are preserved, and only matching rows from the Detail table are included. Non-matching detail rows are discarded, while unmatched master rows are retained with null values for detail columns.

Multiple choice technology programming languages

Under which two circumstances do you design database triggers? (Choose two)

  1. a) To duplicate the functionality of other triggers.

  2. b) To replicate built-in constraints in the Oracle server such as primary key and foreign key.

  3. c) To guarantee that when a specific operation is performed, related actions are performed.

  4. d) For centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement.
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

Database triggers are designed for two key purposes: (1) ensuring related actions occur when specific operations happen (data integrity and automation), and (2) implementing centralized global operations that fire regardless of which user or application invokes the statement (cross-cutting concerns). You should NOT duplicate other triggers' functionality or replicate built-in constraints like primary/foreign keys - those are anti-patterns.

Multiple choice technology programming languages

All users currently have the INSERT privileges on the PLAYER table. You want only your users to insert into this table using the ADD_PLAYER procedure. Which two actions must you take? (Choose two)

  1. a) GRANT SELECT ON ADD_PLAYER TO PUBLIC;

  2. b) GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;

  3. c) GRANT INSERT ON PLAYER TO PUBLIC;

  4. d) GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;

  5. e) REVOKE INSERT ON PLAYER FROM PUBLIC;
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

To restrict insertion to only the ADD_PLAYER procedure, you must revoke the direct INSERT privilege on the PLAYER table from public users and grant them EXECUTE privilege on the procedure.

Multiple choice technology programming languages

Examine this procedure: CREATE OR REPLACE PROCEDURE UPD_BAT_STAT (V_ID IN NUMBER DEFAULT 10, V_AB IN NUMBER DEFAULT 4) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB WHERE PLAYER_ID = V_ID; COMMIT; END; Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)

  1. a) EXECUTE UPD_BAT_STAT;

  2. b) EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);

  3. c) EXECUTE UPD_BAT_STAT(31, 'FOUR', 'TWO');

  4. d) UPD_BAT_STAT(V_AB=>10, V_ID=>31);

  5. e) RUN UPD_BAT_STAT;
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

The procedure has default values (V_ID=10, V_AB=4). Option A works because defaults apply. Option B works using named notation with explicit values. Option C fails because it passes 3 arguments (string literals) to a 2-parameter procedure expecting NUMBER types. Option D fails because 'UPD_BAT_STAT' without EXECUTE isn't valid SQL*Plus syntax. Option E fails because 'RUN' isn't valid SQL*Plus syntax for invoking procedures.

Multiple choice technology testing

In QTP, what command is used to insert the transactions in test?

  1. Services.StartTransaction "Name", Services.EndTransaction "Name";

  2. StartTransaction.Services “Name”,EndTransaction. Services "Name";

  3. StartTransaction ("Name"), EndTransaction ("Name");

  4. Services.StartTransaction "Name", EndTransaction. Services "Name";
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In QTP (QuickTest Professional), transaction operations are invoked through the Services object. The StartTransaction and EndTransaction methods are called on Services with the transaction name as a string argument. This syntax properly marks the beginning and end of a transaction for timing and reporting purposes.

Multiple choice technology programming languages

Which type of Statements can execute parameterized queries?

  1. O (a) PreparedStatement

  2. O (b) ParameterizedStatement

  3. O (c) ParameterizedStatement and CallableStatement

  4. O (d) All kinds of Statements (i.e. which implement a sub interface of Statement)
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A PreparedStatement is designed to execute precompiled SQL queries that can accept input parameters (parameterized queries) represented by placeholders (?).

Multiple choice technology programming languages

How can you execute DML statements (i.e. insert, delete, update) in the database?

  1. O (a) By making use of the InsertStatement, DeleteStatement or UpdateStatement classes

  2. O (b) By invoking the execute(...) or executeUpdate(...) method of a normal Statement object

  3. O (c) By invoking the executeInsert(...), executeDelete(...) or executeUpdate(...) methods of

  4. O (d) By making use of the execute(...) statement of the DataModificationStatement object
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To execute DML statements (i.e. insert, delete, update) in the database, the user needs to know the methods or classes available to achieve this.

Option A is incorrect because there are no predefined classes in Java named InsertStatement, DeleteStatement or UpdateStatement for executing DML statements in a database.

Option B is correct. You can execute DML statements by invoking the execute() or executeUpdate() method of a normal Statement object. The execute() method is used for executing any type of SQL statement and can return a boolean value indicating whether the query returns a ResultSet or not. The executeUpdate() method is used for executing insert, delete, and update statements and returns an integer value representing the number of rows affected by the query.

Option C is incorrect because there are no predefined methods named executeInsert(), executeDelete() or executeUpdate() for executing DML statements in Java.

Option D is incorrect because there is no class named DataModificationStatement in Java.

Therefore, the correct answer is:

The Answer is: B