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 of the following is not a valid Oracle PL/SQL exception.

  1. NO_DATA_FOUND ORA-01403

  2. DUP_VAL_ON_INDEX ORA-00001

  3. TWO_MANY_ROWS ORA-01422

  4. OTHERS

  5. None of the above. These are all valid.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to have knowledge of Oracle PL/SQL exceptions and their syntax.

The correct answer is option C. TWO_MANY_ROWS ORA-01422 is not a valid Oracle PL/SQL exception.

Explanation for each option:

A. NO_DATA_FOUND ORA-01403: This is a valid exception in Oracle PL/SQL. It is raised when a SELECT INTO statement returns no rows.

B. DUP_VAL_ON_INDEX ORA-00001: This is a valid exception in Oracle PL/SQL. It is raised when a unique index constraint is violated.

C. TWO_MANY_ROWS ORA-01422: This is not a valid exception in Oracle PL/SQL. It is an invalid exception.

D. OTHERS: This is a valid exception in Oracle PL/SQL. It is used to catch any exception that is not explicitly handled.

E. None of the above. These are all valid: This option is incorrect as option C is not a valid exception.

Therefore, the answer is: C.

Multiple choice technology databases

The || is is an example of what function SELECT last_name || ', ' || first_name || ' ' || middle_name FROM employees;

  1. Incantination

  2. Integration

  3. Continuation

  4. Concatenation

  5. Pipeline

  6. None of the above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To solve this question, the user needs to know the SQL syntax and the concept of concatenation.

The given SQL statement is using the || operator to join or concatenate multiple columns together.

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

A. Incantination: This option is incorrect as "Incantation" is not a valid SQL function or concept.

B. Integration: This option is incorrect as "Integration" is not a valid SQL function or concept.

C. Continuation: This option is incorrect as "Continuation" is not a valid SQL function or concept.

D. Concatenation: This option is correct. The || operator is used to concatenate or combine two or more strings in SQL, resulting in a single string that contains all the characters from the original strings.

E. Pipeline: This option is incorrect as "Pipeline" is not a valid SQL function or concept.

F. None of the above: This option is incorrect as option D, "Concatenation", is the correct answer.

Therefore, the answer is: D. Concatenation

Multiple choice technology databases

Assuming the date and time is 09/09/2009 09:09:09, what value will the following statement return SELECT TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH24:MI:SS') FROM dual;

  1. 09/09/2009 00:00:00

  2. 09/09/2009 09:09:09AM

  3. 09/09/2009

  4. 09/09/2009 09:09:09

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

The given statement is:

SELECT TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH24:MI:SS') FROM dual;

Let's break down the statement:

  1. TRUNC(SYSDATE): This truncates the current date and time to the nearest day, setting the time component to 00:00:00.

  2. TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH24:MI:SS'): This converts the truncated date and time to a string format with the specified format mask.

Considering the given date and time is 09/09/2009 09:09:09, when we truncate this date and time, the time component will be set to 00:00:00, resulting in 09/09/2009 00:00:00.

Therefore, the statement will return "09/09/2009 00:00:00".

The correct answer is A) 09/09/2009 00:00:00.

Multiple choice technology databases

Select the best answer. Which listed attribute is an invalid attribute of an Explicit cursor.

  1. %NOTFOUND

  2. %FOUND

  3. %ROWCOUNT

  4. %ISOPEN

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

To answer this question, the user needs to have knowledge of explicit cursors in SQL.

The explicit cursor is a SELECT statement that gets declared in the declaration section of PL/SQL block, and it is used to retrieve more than one row from the table.

Now, let's examine each option to see which attribute is invalid for an explicit cursor:

A. %NOTFOUND: This attribute is used to check whether the cursor returns no rows. It is a valid attribute of an explicit cursor.

B. %FOUND: This attribute is used to check whether the cursor returns rows. It is a valid attribute of an explicit cursor.

C. %ROWCOUNT: This attribute is used to return the number of rows affected by the most recent DML statement. It is a valid attribute of an explicit cursor.

D. %ISOPEN: This attribute is used to check whether the cursor is open or not. It is a valid attribute of an explicit cursor.

E. None of the above. All of these are valid: This option is incorrect because it is asking for an invalid attribute. All of the attributes listed in the question (A, B, C, and D) are valid attributes of an explicit cursor.

Therefore, the correct answer is:

The Answer is: E

Multiple choice technology databases

Where do you declare an explicit cursor in the PL/SQL language?

  1. In the PL/SQL working storage section

  2. In the PL/SQL declaration section

  3. In the PL/SQL body section

  4. In the PL/SQL exception section

  5. None of the above
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To declare an explicit cursor in the PL/SQL language, the user needs to know how to write PL/SQL code and the concept of cursors.

An explicit cursor is a cursor that is defined and managed by the user. It requires the user to declare a cursor variable that references the cursor and then define the SQL statement that will be executed when the cursor is opened.

With that in mind, the correct answer is:

B. In the PL/SQL declaration section

The declaration section of a PL/SQL block is where variables, constants, and cursors are declared. In this section, the user can declare the explicit cursor variable by specifying its name, data type, and the query to be executed. Once the cursor variable is declared, the user can then open, fetch, and close the cursor in the body section of the block.

Option A is incorrect because the PL/SQL working storage section does not exist.

Option C is incorrect because the PL/SQL body section is where the logic of the block is implemented, not where the cursor is declared.

Option D is incorrect because the PL/SQL exception section is where exception handling code is written.

Option E is incorrect because one of the above options (Option B) is the correct answer.

Therefore, the answer is: B. In the PL/SQL declaration section.

Multiple choice technology databases

Select the best answer. This is an example of what _____ type of cursor? DECLARE l_date DATE; BEGIN SELECT TRUNC(SYSDATE) INTO l_date FROM DUAL; END;

  1. Explicit

  2. Implicit

  3. Select

  4. PL/SQL

  5. None of the above
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Implicit cursors are automatically opened, fetched, and closed by Oracle whenever a SQL statement (like SELECT INTO, INSERT, UPDATE, or DELETE) is executed directly in PL/SQL. Explicit cursors must be defined in the DECLARE section and managed manually.

Multiple choice technology databases

Select the best answer. This is an example of what _____ type of cursor? DECLARE l_date DATE; CURSOR c1 IS SELECT TRUNC(SYSDATE) FROM DUAL; BEGIN OPEN c1; FETCH c1 INTO l_date; CLOSE c1; END;

  1. Explicit

  2. Implicit

  3. Select

  4. PL/SQL

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

This is an explicit cursor because it is explicitly declared with the CURSOR keyword, then explicitly opened, fetched from, and closed. Explicit cursors give you fine-grained control over query execution. Implicit cursors are created automatically by Oracle for SQL statements embedded in PL/SQL code (like SELECT...INTO or implicit cursor FOR loops).

Multiple choice technology databases

Select the best answer to complete this variable declaration for a record. DECLARE l_foo_table SOME_TABLE_________; BEGIN ...

  1. %ROWTYPE

  2. %TABLE

  3. %COLUMNTYPE

  4. %TYPE

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

Declaring a PL/SQL record variable based on the entire column structure of a table uses %ROWTYPE. The %TYPE attribute is for declaring variables based on a single table column or another variable, while %TABLE and %COLUMNTYPE are invalid attributes.

Multiple choice technology databases

Select the best answer to complete this variable declaration for a column value. DECLARE l_foo_column_id SOME_TABLE.SOME_COLUMN_________; BEGIN ...

  1. %ID

  2. %ROWTYPE

  3. %TYPE

  4. %COLUMNTYPE

  5. None of the above.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The %TYPE attribute anchors a variable's datatype to a specific column's datatype. If SOME_TABLE.SOME_COLUMN is defined as VARCHAR2(50), then l_foo_column_id will also be VARCHAR2(50). This provides maintainability - if the column's datatype changes, variables declared with %TYPE automatically adapt. %ROWTYPE anchors to an entire row's structure, not a single column.

Multiple choice technology databases

Select invalid variable types

  1. VARCHAR2

  2. VARCHAR1

  3. CHAR

  4. CHAR2

  5. INTEGER

  6. NUMBER
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

VARCHAR2, CHAR, INTEGER, and NUMBER are standard, valid variable types in ABAP and Oracle databases. VARCHAR1 and CHAR2 are not valid types - the correct forms are VARCHAR2 (not VARCHAR1) and CHAR (not CHAR2). The question asks specifically for invalid types, so B and D are the correct selections.

Multiple choice technology programming languages

Transaction code for the Database utility is _______

  1. se14

  2. se11

  3. se16n

  4. All the Above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

SE14 is the standard SAP transaction code for the Database Utility utility. SE11 is the transaction code for the ABAP Dictionary (Data Dictionary), and SE16N is for general data table browsing. The Database Utility (SE14) is used for database-specific operations like checking table consistency, converting tables, and performing database repairs.

Multiple choice technology programming languages

Transaction code for the Database Browser is ___________

  1. se16

  2. /nn

  3. se16n

  4. All the Above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

This question tests knowledge that SE16, /n (or /nn), and SE16N are all transaction codes related to database browsing functionality in SAP. SE16 is the basic Data Browser, SE16N is the generalized table display transaction, and /n or /nn are command codes used to navigate to these transactions. Option D 'All the Above' is correct because all three codes are associated with database browsing.

Multiple choice technology programming languages

Maximum "Include Structure" you can use in a Database Table

  1. 1

  2. None

  3. 9

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

In SAP ABAP Dictionary, a transparent table can include up to 9 include structures in its definition. Include structures allow you to reuse the same set of fields in multiple table definitions. Option A (1) is incorrect - you're not limited to just one. Option B (None) is incorrect - include structures are supported. Option D (4) is incorrect - the limit is higher than 4.

Multiple choice technology programming languages

Transaction for Table Maintenance Generator is?

  1. sm30

  2. /nn

  3. sap_maintain

  4. All the Above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

SM30 is the transaction code for the Table Maintenance Generator in SAP. This utility is used to create and maintain table maintenance dialogs for custom tables. Option B (/nn) is a navigation prefix, not the Table Maintenance transaction. Option C (sap_maintain) is not the correct transaction code name.

Multiple choice technology programming languages

Can View have Indexes?

  1. Multiple

  2. Single Index

  3. Two are Allowed

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

Views in SAP (and most databases) cannot have their own indexes. A view is a virtual table that doesn't physically store data - it retrieves data from underlying base tables. Indexes are physical structures created on actual database tables to improve query performance. Since views don't store data, you cannot create indexes on them directly.