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

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

Detail Outer Join returns all rows from the master table plus matching rows from the detail table. If no match exists in detail, NULLs are returned for detail columns. Master is the driving table in Joiner transformations - this is the table with fewer rows for optimal performance.

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 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/UFT, transactions are started and ended using the Services object with the syntax Services.StartTransaction "Name" and Services.EndTransaction "Name".

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

Multiple choice technology programming languages

How do you know in your Java program that a SQL warning is generated as a result of executing a SQL statement in the database?

  1. O (a) You must catch the checked SQLException which is thrown by the method which executes

  2. O (b) You must catch the unchecked SQLWarningException which is thrown by the method

  3. O (c) You must invoke the getWarnings() method on the Statement object (or a sub interface

  4. O (d) You must query the ResultSet object about possible warnings generated by the database
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To know in your Java program that a SQL warning is generated as a result of executing a SQL statement in the database, you must invoke the getWarnings() method on the Statement object (or a sub interface). Therefore, option C is the correct answer.

Option A is incorrect because SQLException is thrown when an SQL error occurs, not a warning. It is a checked exception and must be caught or declared to be thrown.

Option B is incorrect because SQLWarningException is not a valid exception class in Java. SQLWarning is the class that represents a warning issued by the database.

Option D is incorrect because ResultSet object is used to retrieve data from the database after executing a query, not to retrieve warnings.

Therefore, the correct answer is:

The Answer is: C. O (c) You must invoke the getWarnings() method on the Statement object (or a sub interface).