SQL & PL/SQL

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Gaurav.4086
G
Gaurav.4086
Community Contributor
Quizzes Created: 3 | Total Attempts: 2,210
| Attempts: 1,463
SettingsSettings
Please wait...
  • 1/16 Questions

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

    • DELETE FirstName='Peter' FROM Persons
    • DELETE FROM Persons WHERE FirstName = 'Peter'
    • DELETE ROW FirstName='Peter' FROM Persons
Please wait...
SQL & PL/SQL - Quiz
About This Quiz

This SQL & PL\/SQL quiz assesses knowledge on block-structured language specifics, executable sections, table security through views, handling of null values, DCL statements, and efficient data retrieval methods. It's tailored for learners aiming to enhance their database management skills.

Quiz Preview

  • 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:

  • 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:

  • 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:

  • 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:

  • 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:

  • 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:

Quiz Review Timeline (Updated): Mar 21, 2023 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Mar 21, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Sep 30, 2011
    Quiz Created by
    Gaurav.4086
Back to Top Back to top
Advertisement