Practice Question Answers and Explanations

1. Correct answer: A

   SQL language uses a set-oriented paradigm. Object-oriented and procedural paradigms are used for application-level programming languages. SQL is database-focused rather than application-focused. (Imperative logic is more application-focused.)

2. Correct answer: D

   The SELECT statement belongs to the Data Query Language (DQL) subset of SQL. Data Manipulation Language (DML) refers to SQL statements like INSERT and UPDATE. Data Definition Language (DDL) refers to creating tables and so on. Data Control Language (DCL) refers to security.

3. Correct answer: A

   True. Yes, delimited identifiers are treated as case-sensitive. Undelimited (without quotes) identifiers are treated as if they were written in uppercase.

4. Correct answer: C

   NULL is used in SQL to indicate that there is no value.

5. Correct answer: B

   A GLOBAL TEMPORARY table stores results for each session, but you do not have to recreate the table for every session. It is persisted. History column and virtual tables are not related to sessions. A local temporary table deletes the table after each session.

6. Correct answer: C

   A semicolon (;) is used to terminate SQL statements.

7. Correct answer: A

   A SELECT * statement is not recommended for use with column tables. Column tables are stored per column, and SELECT * forces the database to read every column. It becomes much faster when it can ignore entire columns.

8. Correct answer: B

   False. A UNION ALL statement returns all values in a result set, even duplicate records. UNION only returns the unique records.

9. Correct answer: B

   A colon (:) is prepended to a SQLScript variable when it is used as an input variable.

10. Correct answer: C

    You can loop through records using imperative logic in SQLScript. It does NOT deliver the best possible performance. Declarative logic (the set-oriented paradigm) is much faster. The word only makes this statement “You can only use if-then logic” false, because we can also use loop logic with imperative logic.

11. Correct answers: B, C

    Dynamic SQL can be used for SQL injection, and you can dynamically change your data sources. The word always makes the “It is always bad for security” statement false. Dynamic SQL does NOT deliver the best possible performance.

12. Correct answer: B

    You create a scalar function when it has a return type of BIGINT. This returns a single value, and not a table-like result set. SQL functions and window functions are not user-defined functions.

13. Correct answers: A, D

    You can create procedures in SQLScript and R in SAP HANA.

14. Correct answer: D

    A user-defined function with the INVOKER security option checks the authorizations of the user calling the function when this function is executed. The _SYS_REPO user is used if the DEFINER security option is used. The owner of the function is no longer applicable once the function has been transported to the production system. The SYSTEM user is never used in this context.

15. Correct answer: C

    You first have to know that the default security option for a procedure is the DEFINER security option.

    The _SYS_REPO user is used if the DEFINER security option is used. The user calling the function is used for the INVOKER security option. The developer of the procedure is no longer applicable once the function has been transported to the production system.

16. Correct answer: C

    The preferred way to create a procedure is to create a .hdbprocedure file. All the other methods are deprecated! (This shows how much things have changed over the last few years.)

17. Correct answers: C, D

    You can call a table function with SELECT and EXEC statements. EXEC can be used to build a dynamic SQL statement that contains a SELECT statement, for example. You CANNOT use a function to INSERT INTO, because it’s read-only. CALL is how procedures are called.

18. Correct answers: C, D

    JOIN is allowed in a user-defined function. COMMIT, EXEC, and INSERT are not read-only statements. A UDF will not even activate when you have these in the function.

19. Correct answers: B, D

    You need the WITH RESULTS VIEW and READS SQL DATA clauses to call a procedure in a SELECT statement.

20. Correct answer: B

    You have to run a CREATE TYPE statement before you can return table results from a procedure.

    CREATE TABLE TYPE is deprecated.

21. Correct answers: C, D

    GROUPING SETS can return multiple result sets from a single SQL statement. All the other statements use multiple sources, but return a single result set.

22. Correct answers: C, D

    Procedures can return multiple result sets. Table functions and views can only return single result sets. Scalar functions return (multiple) single values, not sets.

23. Correct answer: A

    UDFs can use imperative logic. They cannot commit transactions or use dynamic SQL (because they are read-only), and they cannot call procedures. (However, procedures can call UDFs.)

24. Correct answers: A, D, E

    Procedures can call views and table functions, and they may have input parameters. They may, but not must, return values. Because they can use dynamic SQL, they are open to SQL injection.

25. Correct answer: C

    We recommend using table functions rather than scripted calculation views.

26. Correct answers: B, D

    The migration tool workflow requires a script-based calculation view as input, and it produces a table function linked to a graphical calculation view. Graphical calculation views do not need to be migrated. It produces not only a table function, but also a graphical calculation view.