SQL & PL/SQL

2.

’grant’ and ‘revoke’ are _______ statements

DDL
DML
DCL
None of the above

Correct Answer

A. DCL

Explanation

The correct answer is DCL (Data Control Language). DCL statements are used to control access to the database and manage user privileges. The 'grant' statement is used to give users permission to perform certain actions on the database, while the 'revoke' statement is used to take away those permissions. DCL statements are different from DDL (Data Definition Language) and DML (Data Manipulation Language), which are used to define and manipulate the structure and data of the database respectively.

Rate this question:

0

3.

Pick the mandatory block from the given blocks of a PL\SQL code

Declarative
Executable
Exeption handling

Correct Answer

A. Executable

Explanation

The executable block is a mandatory block in a PL/SQL code. It contains the actual code that is executed when the program is run. This block includes statements such as assignments, loops, conditional statements, and procedure calls. Without the executable block, the code would not have any actions or operations to perform. Therefore, it is necessary to have this block in order to have a functional PL/SQL code.

Rate this question:

4.

In a PL/SQL block, a variable is declared as NUMBER without an initial value. What will its value be when it is first used in the executable section of the PL/SQL block?

NULL
0
Results in a compilation error
An exception will be raised

Correct Answer

A. NULL

Explanation

When a variable is declared as NUMBER without an initial value in a PL/SQL block, its value will be NULL when it is first used in the executable section of the block.

Rate this question:

5.

In this PL/SQL statement, which of the following lines will produce an error?

Cursor CAPITALS is
Select CITY, STATE
Into my_city, my_state
From CITIES
Where CAPITAL = 'Y';

Correct Answer

A. Into my_city, my_state

Explanation

The line "into my_city, my_state" will produce an error because the variables my_city and my_state are not declared or defined in the PL/SQL statement. The INTO clause is used to assign values from the query result to variables, but the variables must be declared before they can be used in this context.

Rate this question:

6.

What is the result if two NULL values are compared to each other?

TRUE
FALSE
Undefined
NULL

Correct Answer

A. NULL

Explanation

When two NULL values are compared to each other, the result is NULL. This is because NULL represents the absence of a value, so it cannot be compared to any other value, including another NULL. Therefore, the comparison is inconclusive and the result is NULL.

Rate this question:

7.

Which of the following provide an additional level of table security in SQL?

Schema
Index
Views
Segments

Correct Answer

A. Views

Explanation

Views provide an additional level of table security in SQL. Views allow users to access specific columns or rows of a table, while hiding the underlying structure of the table. This helps to protect sensitive information and restrict unauthorized access. Views can also be used to simplify complex queries and provide a more user-friendly interface to the database.

Rate this question:

8.

Which of the following can be a valid column name?

Column
1966_Invoices
Catch_#22
#Invoices

Correct Answer

A. Catch_#22

Explanation

The column name "Catch_#22" can be a valid column name because it follows the rules for column naming in most database systems. It starts with a letter, can contain letters, numbers, and underscores, and does not exceed the maximum length allowed for column names. The use of special characters like "#" is also allowed in column names.

Rate this question:

1

9.

PL/SQL is a _______ language?

Object oriented
Block structured
Object structured
All the above

Correct Answer

A. Block structured

Explanation

PL/SQL is a block structured language. This means that the code is organized into blocks, where each block contains a set of related statements. These blocks can be nested within each other, allowing for better organization and modularization of the code. The block structure also enables the use of variables, control structures, and exception handling, making PL/SQL a powerful and flexible language for developing database applications.

Rate this question:

1

10.

New values to the variables are assigned in which part of the PL/SQL code?

Declaration section
Command line
Executable section
Cannot assign at all

Correct Answer

A. Executable section

Explanation

In the executable section of the PL/SQL code, new values can be assigned to variables. This section is where the actual logic and operations are performed. It is where the program executes statements and performs calculations or manipulations on the data. Therefore, it is the appropriate part of the code to assign new values to variables.

Rate this question:

11.

During the duplicate checking the null values are ignored?

True
False

Correct Answer

A. False

Explanation

During the duplicate checking process, null values are not ignored. When checking for duplicates, null values are considered as a valid value and are taken into account. Therefore, the correct answer is False.

Rate this question:

12.

Which of the following SQL functions can operate on any datatype?

TO_CHAR
LOWER
LPAD
CEIL

Correct Answer

A. CEIL

Explanation

CEIL is the correct answer because it is a SQL function that can operate on any datatype. The CEIL function is used to round a number up to the nearest whole number or specified decimal place. It can be applied to integer, decimal, or floating-point numbers, making it versatile and applicable to any datatype. TO_CHAR, LOWER, and LPAD functions, on the other hand, have specific use cases and may not be able to operate on all datatypes.

Rate this question:

13.

Which Oracle access method is the fastest way for Oracle to retrieve a single row?

Primary key access
Access via unique index
Table access by ROWID
Full table scan

Correct Answer

A. Primary key access

Explanation

Primary key access is the fastest way for Oracle to retrieve a single row because the primary key is a unique identifier for each row in a table. This means that Oracle can directly access the specific row using the primary key value, without having to search through an index or scan the entire table. This method is highly efficient and allows for quick retrieval of the desired row.

Rate this question:

14.

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

When a SELECT statement returns no rows
When a SELECT statement returns more than one row
When the datatypes of SELECT clause and INTO clause do not match
When INTO statement is missing in the SELECT statement

Correct Answer(s)

A. When a SELECT statement returns no rows
A. When a SELECT statement returns more than one row

Explanation

PL/SQL raises an exception when a SELECT statement returns no rows because it is expecting at least one row to be returned. If no rows are returned, it indicates that the query did not find any matching data.

PL/SQL also raises an exception when a SELECT statement returns more than one row because it is expecting only a single row to be returned. If multiple rows are returned, it indicates that the query found multiple matches for the given conditions, which can cause data integrity issues.

The other two options, when the datatypes of SELECT clause and INTO clause do not match and when the INTO statement is missing in the SELECT statement, do not raise exceptions. Instead, they would cause compilation or runtime errors.

Rate this question:

0

15.

With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
SELECT LastName>'Hansen' AND LastName'Hansen' AND LastName'Hansen' AND LastName'Hansen' AND LastName
SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName

Correct Answer

A. SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName

Explanation

The correct answer is "SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName". This query selects all the records from the table "Persons" where the "LastName" is alphabetically greater than "Hansen". The "AND" operator is used to include records where the "LastName" is equal to "Hansen".

Rate this question:

1

16.

In this PL/SQL statement, which of the following lines will produce an error?

Create or replace function vat (vat_rate IN NUMBER, price IN NUMBER)
Declare
L_vat NUMBER;
Begin
L_vat := (vat_rate/100) * price; return l_vat;
End;

Correct Answer

A. Declare

Explanation

The line "declare" will produce an error because the "declare" statement should be placed before the "begin" statement in a PL/SQL block.

Rate this question:

SQL & PL/SQL

With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?

Quiz Preview

’grant’ and ‘revoke’ are _______ statements

Pick the mandatory block from the given blocks of a PL\SQL code

In a PL/SQL block, a variable is declared as NUMBER without an initial value. What will its value be when it is first used in the executable section of the PL/SQL block?

In this PL/SQL statement, which of the following lines will produce an error?

What is the result if two NULL values are compared to each other?

Which of the following provide an additional level of table security in SQL?

Which of the following can be a valid column name?

PL/SQL is a _______ language?

New values to the variables are assigned in which part of the PL/SQL code?

During the duplicate checking the null values are ignored?

Which of the following SQL functions can operate on any datatype?

Which Oracle access method is the fastest way for Oracle to retrieve a single row?

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

With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

In this PL/SQL statement, which of the following lines will produce an error?