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

What is the syntax of select .... into statement

  1. SELECT row_name INTO variable_name FROM table_name [WHERE condition];

  2. SELECT column_name INTO variable_name FROM table_name [WHERE condition];

  3. SELECT column_name INTO variable_name FROM table_name [WHEN condition];

  4. SELECT table_name INTO variable_name FROM table_name [WHERE condition];
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The SELECT INTO syntax in PL/SQL requires specifying the column name first, followed by the INTO keyword, then the variable name, then FROM, and optionally a WHERE clause. Option B correctly shows this structure with column_name as the source and variable_name as the destination.

Multiple choice technology databases

The Syntax to declare a record of a User-defined datatype is:

  1. table_name record_type_name;

  2. column_name record_type_name;

  3. record_type_name record_name;

  4. record_name record_type_name;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In PL/SQL record declarations, the syntax follows the pattern 'record_name record_type_name' where the variable name comes first, followed by its type. This is consistent with how other variables are declared in PL/SQL.

Multiple choice technology databases

Which of the following is not a way to execute stored functions

  1. EXECUTE [or EXEC] database.function_name;

  2. employee_name := employer_details_func;

  3. SELECT employer_details_func FROM dual;

  4. dbms_output.put_line(employer_details_func);
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The EXECUTE command (or EXEC) is used for stored procedures, not stored functions. Functions are typically called within expressions (assignment), SELECT statements, or as parameters to other procedures like dbms_output.put_line. Option A shows procedure execution syntax, not function invocation.

Multiple choice technology programming languages

PL/SQL raises an exception, in which TWO of the following cases:

  1. When a SELECT statement returns no rows

  2. When a SELECT statement returns more than one row

  3. When the datatypes of SELECT clause and INTO clause do not match

  4. When INTO statement is missing in the SELECT statement
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

PL/SQL's implicit SELECT...INTO statement raises NO_DATA_FOUND when zero rows are returned and TOO_MANY_ROWS when multiple rows return. These are predefined runtime exceptions. Datatype mismatch raises VALUE_ERROR during assignment, and missing INTO causes a compile-time error.

Multiple choice technology programming languages

Which of the following statements gets the total value of the column 'Price' in the 'Sales' table?

  1. SELECT ADD(Price) FROM Sales

  2. SELECT SUM(Price) WHERE Sales

  3. SELECT TOTAL(Price) FROM Sales

  4. SELECT SUM(Price) FROM Sales
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To solve this question, the user needs to know the correct SQL syntax for getting the total value of a column in a table. The correct SQL syntax for getting the total value of a column in a table is to use the SUM() function.

Now, let's go through each option and explain why it is right or wrong:

A. SELECT ADD(Price) FROM Sales: This option is incorrect because there is no ADD() function in SQL. The user should have used SUM() instead of ADD().

B. SELECT SUM(Price) WHERE Sales: This option is incorrect because the WHERE clause is incomplete. The correct SQL syntax would be to use the FROM keyword after the WHERE clause to specify the table from which to retrieve the data. Additionally, the correct SQL syntax for getting the total value of a column is to use the SUM() function, not the WHERE keyword.

C. SELECT TOTAL(Price) FROM Sales: This option is incorrect because there is no TOTAL() function in SQL. The user should have used SUM() instead of TOTAL().

D. SELECT SUM(Price) FROM Sales: This option is correct. The user has used the correct SQL syntax to get the total value of the 'Price' column in the 'Sales' table. The SUM() function is used to add up all the values in the 'Price' column and return the total value.

The Answer is: D

Multiple choice technology programming languages

Which of the following SQL statements does count the rows in the 'Sales' table?

  1. SELECT COUNT(*) FROM Sales

  2. SELECT COUNT(*) IN Sales

  3. SELECT COUNTER(*) FROM Sales

  4. SELECT NUM() FROM Sales
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To count the rows in a table, we use the COUNT() function in SQL. The correct answer is:

The Answer is: A. SELECT COUNT(*) FROM Sales

Explanation:

A. SELECT COUNT() FROM Sales: This option is correct because it uses the COUNT() function to count all the rows in the 'Sales' table. The () symbol means that we are counting all the rows.

B. SELECT COUNT(*) IN Sales: This option is incorrect because the syntax is not correct. The keyword IN cannot be used in this context.

C. SELECT COUNTER(*) FROM Sales: This option is incorrect because there is no such function as COUNTER() in SQL. The correct function name is COUNT().

D. SELECT NUM() FROM Sales: This option is incorrect because there is no such function as NUM() in SQL. We need to use the COUNT() function to count the rows in the table.

Therefore, option A is the correct answer.

Multiple choice technology databases

SQL%ISOPEN attribute always returns null in implicit Cursors.

  1. True

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

In Oracle PL/SQL, implicit cursors are automatically managed by the Oracle engine for SQL statements like SELECT, INSERT, UPDATE, DELETE. The SQL%ISOPEN attribute is used to check if a cursor is currently open. For implicit cursors, Oracle automatically opens, executes, and closes the cursor, so SQL%ISOPEN always returns FALSE (not NULL) for implicit cursors because they're never left open - the engine handles everything. NULL is different from FALSE; NULL represents an unknown value.

Multiple choice technology databases

Implicit Cursors are created by users in PL/SQL block.

  1. True

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

Implicit cursors in Oracle PL/SQL are NOT created by users - they are automatically created and managed by the Oracle engine for SQL statements (SELECT, INSERT, UPDATE, DELETE). Users create and manage EXPLICIT cursors using the CURSOR...IS syntax and explicit OPEN, FETCH, CLOSE operations. The statement claims users create implicit cursors, which is false.

Multiple choice technology databases

Oracle engine creates Cursors for every SELECT statements.

  1. True

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

Oracle does NOT create cursors for every SELECT statement. Cursors are created only for SELECT statements that return rows (queries, not SELECT...INTO statements). SELECT statements used in INTO clauses (singleton selects) or SQL embedded in PL/SQL that don't return multiple rows don't necessarily create cursors in the same way. Also, some SELECT operations may be optimized differently. The blanket statement 'every SELECT' is too broad and incorrect.