SQL & PL/SQL
Questions and Answers
- 1.
PL/SQL is a _______ language?
- A.
Object oriented
- B.
Block structured
- C.
Object structured
- D.
All the above
Correct Answer
B. Block structuredExplanation
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:
-
- 2.
New values to the variables are assigned in which part of the PL/SQL code?
- A.
Declaration section
- B.
Command line
- C.
Executable section
- D.
Cannot assign at all
Correct Answer
C. Executable sectionExplanation
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:
-
- 3.
Which of the following provide an additional level of table security in SQL?
- A.
Schema
- B.
Index
- C.
Views
- D.
Segments
Correct Answer
C. ViewsExplanation
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:
-
- 4.
During the duplicate checking the null values are ignored?
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
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:
-
- 5.
’grant’ and ‘revoke’ are _______ statements
- A.
DDL
- B.
DML
- C.
DCL
- D.
None of the above
Correct Answer
C. DCLExplanation
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:
-
- 6.
Which Oracle access method is the fastest way for Oracle to retrieve a single row?
- A.
Primary key access
- B.
Access via unique index
- C.
Table access by ROWID
- D.
Full table scan
Correct Answer
A. Primary key accessExplanation
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:
-
- 7.
Which of the following can be a valid column name?
- A.
Column
- B.
1966_Invoices
- C.
Catch_#22
- D.
#Invoices
Correct Answer
C. Catch_#22Explanation
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:
-
- 8.
Which of the following SQL functions can operate on any datatype?
- A.
TO_CHAR
- B.
LOWER
- C.
LPAD
- D.
CEIL
Correct Answer
D. CEILExplanation
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:
-
- 9.
Pick the mandatory block from the given blocks of a PL\SQL code
- A.
Declarative
- B.
Executable
- C.
Exeption handling
Correct Answer
B. ExecutableExplanation
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:
-
- 10.
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"?
- A.
SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
- B.
SELECT LastName>'Hansen' AND LastName'Hansen' AND LastName'Hansen' AND LastName'Hansen' AND LastName
- C.
SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName
Correct Answer
C. SELECT * FROM Persons WHERE LastName>'Hansen' AND LastNameExplanation
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:
-
- 11.
With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?
- A.
DELETE FirstName='Peter' FROM Persons
- B.
DELETE FROM Persons WHERE FirstName = 'Peter'
- C.
DELETE ROW FirstName='Peter' FROM Persons
Correct Answer
B. DELETE FROM Persons WHERE FirstName = 'Peter'Explanation
The correct answer is "DELETE FROM Persons WHERE FirstName = 'Peter'". This SQL statement is used to delete records from the "Persons" table where the value in the "FirstName" column is equal to "Peter".Rate this question:
-
- 12.
In this PL/SQL statement, which of the following lines will produce an error?
- A.
Cursor CAPITALS is
- B.
Select CITY, STATE
- C.
Into my_city, my_state
- D.
From CITIES
- E.
Where CAPITAL = 'Y';
Correct Answer
C. Into my_city, my_stateExplanation
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:
-
- 13.
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?
- A.
NULL
- B.
0
- C.
Results in a compilation error
- D.
An exception will be raised
Correct Answer
A. NULLExplanation
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:
-
- 14.
PL/SQL raises an exception, in which TWO of the following cases
- A.
When a SELECT statement returns no rows
- B.
When a SELECT statement returns more than one row
- C.
When the datatypes of SELECT clause and INTO clause do not match
- D.
When INTO statement is missing in the SELECT statement
Correct Answer(s)
A. When a SELECT statement returns no rows
B. When a SELECT statement returns more than one rowExplanation
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:
-
- 15.
What is the result if two NULL values are compared to each other?
- A.
TRUE
- B.
FALSE
- C.
Undefined
- D.
NULL
Correct Answer
D. NULLExplanation
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:
-
- 16.
In this PL/SQL statement, which of the following lines will produce an error?
- A.
Create or replace function vat (vat_rate IN NUMBER, price IN NUMBER)
- B.
Declare
- C.
L_vat NUMBER;
- D.
Begin
- E.
L_vat := (vat_rate/100) * price; return l_vat;
- F.
End;
Correct Answer
B. DeclareExplanation
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:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 21, 2023Quiz Edited by
ProProfs Editorial Team -
Sep 30, 2011Quiz Created by
Gaurav.4086
Back to top