MCQs on SQL and SQLScript in SAP HANA | SAP HANA MCQs Questions

Mastering SQL and SQLScript in SAP HANA is essential for data professionals working with this advanced database platform. This chapter focuses on foundational concepts like SQL syntax, query optimization, stored procedures, and virtual tables. Enhance your understanding with these SAP HANA MCQ questions and answers, crafted to assess and deepen your knowledge systematically.

---

Multiple Choice Questions (MCQs)

1. Introduction to SQL in SAP HANA

1. Which of the following statements is true about SQL in SAP HANA?
   a) SQL in SAP HANA is case-sensitive for keywords
   b) SQL in SAP HANA supports OLTP and OLAP operations
   c) SQL in SAP HANA does not support aggregation functions
   d) SQL in SAP HANA uses NoSQL syntax
2. What is the default schema in SAP HANA?
   a) SYSTEM
   b) PUBLIC
   c) SYS
   d) HANA
3. Which of these is a valid SAP HANA SQL data type?
   a) TEXT
   b) INTEGER
   c) VARCHAR2
   d) DECIMAL8
4. Which of the following is used to create a new database table in SAP HANA?
   a) CREATE DATABASE
   b) CREATE SCHEMA
   c) CREATE TABLE
   d) CREATE INDEX
5. In SAP HANA, the command to delete rows from a table is:
   a) REMOVE FROM
   b) DELETE FROM
   c) DROP FROM
   d) TRUNCATE

---

2. SQLScript Basics: Syntax and Procedures

6. SQLScript in SAP HANA is primarily used for:
   a) Writing front-end applications
   b) Performing procedural logic with SQL extensions
   c) Managing user roles and authorizations
   d) None of the above
7. What keyword is used to declare a procedure in SQLScript?
   a) DECLARE
   b) PROCEDURE
   c) FUNCTION
   d) CREATE PROCEDURE
8. Which of the following is not a valid control statement in SQLScript?
   a) IF…ELSE
   b) WHILE
   c) FOR…NEXT
   d) CASE
9. What is the purpose of the RETURN statement in SQLScript?
   a) To end a loop
   b) To exit a procedure and return a value
   c) To rollback a transaction
   d) None of the above
10. In SQLScript, which statement is used to execute a stored procedure?
    a) EXECUTE
    b) CALL
    c) RUN
    d) APPLY

---

3. Query Optimization Techniques

11. What is the primary purpose of query optimization in SAP HANA?
    a) To reduce code complexity
    b) To improve query execution performance
    c) To create temporary tables
    d) None of the above
12. Which tool helps analyze query performance in SAP HANA?
    a) Plan Analyzer
    b) Performance Manager
    c) SQL Execution Analyzer
    d) SQL Plan Cache
13. A SQL statement in SAP HANA will execute faster if:
    a) It uses multiple nested subqueries
    b) Joins are replaced with virtual tables
    c) Indexes are used efficiently
    d) It does not include WHERE conditions
14. What is a key benefit of using SQLScript for optimization?
    a) It eliminates the need for indexes
    b) It allows procedural extensions for complex logic
    c) It disables query caching
    d) None of the above
15. How does SAP HANA improve the performance of JOIN queries?
    a) By creating columnar indexes
    b) By using row-based storage
    c) By utilizing pushdown operations
    d) None of the above

---

4. Hands-on: Writing Queries and Stored Procedures

16. Which clause is used to filter data in SQL queries?
    a) SELECT
    b) FILTER
    c) WHERE
    d) GROUP BY
17. What is the correct syntax to create a parameterized procedure?
    a) CREATE PROCEDURE proc_name(param INT);
    b) CREATE proc_name(param: INTEGER);
    c) DEFINE proc_name INT;
    d) PROCEDURE CREATE(param: INT);
18. To combine data from multiple tables, you use:
    a) UNION
    b) JOIN
    c) COMBINE
    d) MERGE
19. In SAP HANA, temporary tables are created using:
    a) CREATE TEMP TABLE
    b) CREATE GLOBAL TEMPORARY TABLE
    c) CREATE TEMPORARY SCHEMA
    d) CREATE SESSION TABLE
20. What is the result of the GROUP BY clause?
    a) Filters rows based on conditions
    b) Combines rows with the same values in specified columns
    c) Adds new columns to a table
    d) Deletes duplicate rows

---

5. Working with Virtual Tables and Joins

21. Virtual tables in SAP HANA are primarily used to:
    a) Create temporary indexes
    b) Access external data sources
    c) Store metadata
    d) Optimize joins
22. Which join returns all records from both tables with NULLs in unmatched rows?
    a) INNER JOIN
    b) LEFT JOIN
    c) RIGHT JOIN
    d) FULL OUTER JOIN
23. The default join type in SAP HANA is:
    a) INNER JOIN
    b) LEFT JOIN
    c) CROSS JOIN
    d) FULL JOIN
24. A CROSS JOIN in SQL:
    a) Matches rows based on keys
    b) Produces a Cartesian product of both tables
    c) Filters data based on conditions
    d) None of the above
25. What is a key advantage of using virtual tables?
    a) Reduced storage requirements
    b) Improved row-based operations
    c) Faster aggregation operations
    d) Simplified data deletion

---

Answers

Qno	Answer
1	b) SQL in SAP HANA supports OLTP and OLAP operations
2	a) SYSTEM
3	b) INTEGER
4	c) CREATE TABLE
5	b) DELETE FROM
6	b) Performing procedural logic with SQL extensions
7	d) CREATE PROCEDURE
8	c) FOR…NEXT
9	b) To exit a procedure and return a value
10	b) CALL
11	b) To improve query execution performance
12	d) SQL Plan Cache
13	c) Indexes are used efficiently
14	b) It allows procedural extensions for complex logic
15	c) By utilizing pushdown operations
16	c) WHERE
17	a) CREATE PROCEDURE proc_name(param INT);
18	b) JOIN
19	b) CREATE GLOBAL TEMPORARY TABLE
20	b) Combines rows with the same values in specified columns
21	b) Access external data sources
22	d) FULL OUTER JOIN
23	a) INNER JOIN
24	b) Produces a Cartesian product of both tables
25	a) Reduced storage requirements