PL/SQL vs. T-SQL and Procedural SQL Fundamentals Quiz Quiz

Explore key differences and similarities between PL/SQL, T-SQL, and procedural SQL concepts with these beginner-friendly multiple-choice questions. Enhance your understanding of syntax, error handling, variables, and transaction control relevant to popular procedural SQL dialects.

1. Language Association

   Which procedural SQL language is primarily associated with Oracle databases?

   1. T-SQL
   2. PL/SQL
   3. SQL/PSM
   4. MySQL SQL

   Explanation: PL/SQL is the procedural language extension used mainly in Oracle database systems. T-SQL is connected to a different database system. SQL/PSM is a more generic procedural language standard. MySQL SQL refers to SQL used in another database but is not specifically a procedural extension.

2. Syntax Differences

   What keyword is used in T-SQL for variable declaration, as shown in 'DECLARE @amount INT;'?

   1. VAR
   2. ASSIGN
   3. LET
   4. DECLARE

   Explanation: In T-SQL, the DECLARE keyword is used for variable declarations like DECLARE @amount INT;. VAR is not valid syntax in this context. LET and ASSIGN are also not used for variable declaration in T-SQL. Misusing these keywords would result in an error.

3. Loop Constructs

   In PL/SQL, which loop structure would you use to repeat a set of statements an unknown number of times until a condition is met?

   1. WHILE LOOP
   2. GO LOOP
   3. FOR LOOP
   4. REPEAT LOOP

   Explanation: WHILE LOOP is used in PL/SQL to repeat actions as long as a condition remains true. FOR LOOP is intended for repeating a fixed number of times. REPEAT LOOP is not standard PL/SQL syntax. GO LOOP is not a recognized loop construct in procedural SQL.

4. Error Handling

   Which keyword is used in PL/SQL for exception handling after a BEGIN-END block?

   1. EXCEPTION
   2. ERROR
   3. CATCH
   4. RESCUE

   Explanation: The EXCEPTION section is used in PL/SQL for handling errors after a BEGIN-END block. CATCH and RESCUE are found in other programming languages, not in PL/SQL. ERROR is not a valid handler in PL/SQL's error management structure.

5. Transaction Commands

   Which statement is commonly used in both T-SQL and PL/SQL to make database changes permanent?

   1. FINISH
   2. END
   3. SAVE
   4. COMMIT

   Explanation: COMMIT is widely used for finalizing transactions and saving changes in both T-SQL and PL/SQL. FINISH and END are not valid transactional commands in this context. SAVE exists as part of SAVEPOINT, but SAVE by itself does not commit transactions.

6. Comments in Procedures

   How do you write a single-line comment in PL/SQL?

   1. /* comment */
   2. -- comment
   3. # comment
   4. // comment

   Explanation: PL/SQL uses two hyphens (--) for single-line comments. Double slashes (//) and hash (#) are not accepted for single-line comments in PL/SQL. Block comments use /* */, not for single lines, making -- the correct answer here.

7. Output Statements

   What command allows you to display output for debugging purposes in PL/SQL?

   1. LOG_OUTPUT
   2. SHOW
   3. DBMS_OUTPUT.PUT_LINE
   4. PRINT

   Explanation: DBMS_OUTPUT.PUT_LINE is used in PL/SQL to print output, useful for debugging. PRINT is used in T-SQL, not PL/SQL. SHOW and LOG_OUTPUT are not valid commands to display output in PL/SQL procedures.

8. Scope of Variables

   Where can you declare a variable in PL/SQL to restrict its use only to a specific block of code?

   1. Within the SELECT statement
   2. At the server level
   3. In the global section
   4. Inside the DECLARE section of a block

   Explanation: Variables declared inside the DECLARE section of a PL/SQL block are local to that block. There is no 'global section' in standard PL/SQL. Declaring variables at the server level is not a function of PL/SQL, and variables cannot be declared within a standard SELECT statement.

9. Batch Separators

   Which keyword is used in T-SQL scripts to signal the end of a batch of statements?

   1. STOP
   2. END
   3. GO
   4. HALT

   Explanation: In T-SQL, the GO statement indicates the end of a batch and is recognized by many T-SQL environments. END is used for closing code blocks, not batches. STOP and HALT are not used for batch separation in T-SQL scripts.

10. Stored Procedure Parameters

    How does T-SQL indicate an input parameter in a stored procedure named @id?

    1. By double quotes
    2. By using $
    3. By using a colon :
    4. By prefixing with @

    Explanation: T-SQL uses the @ symbol to denote parameters and variables, such as @id, in stored procedures. A colon is used for binding variables in other languages. The dollar sign and double quotes are not used to indicate procedure parameters in T-SQL.