TAW_11 Chapter 4, ABAP open SQL

Question 1

What are the two components of the SAP database interface (DBSS)

Answer 1

ABAP Open SQL interface and native SQL interface

Question 2

What are the tasks of the ABAP OPEN SQL interface?

Answer 2

Convert OPEN SSQL to native sql
Client handling
Managing the table buffer

Question 3

What tcode is used for sql tracing

Answer 3

ST05

Question 4

There are two types of database optimizers. What are they?

Answer 4

Rule based and Cost based

Question 5

What is the recommended maximum size of a buffered table?

Answer 5

Less than 10mb

Question 6

Will secondary indexes use the table buffers?

Answer 6

Nope

Question 7

What table stores info for possible buffered table changes?

Answer 7

DDLOG

Question 8

What transaction contains the buffer trace?

Answer 8

ST05

Question 9

When can ORDER BY not be used?

Answer 9

Cluster and Pooled tables

Question 10

What are the prereqs for ORDER BY PRIMARY KEY?

Answer 10

All primary key fields including MANDT must be in the field list. Only a single transparent table can be in the FROM

Question 11

When using DISTINCT on pooled and cluster tables what syntax must be used?

Answer 11

SELECT DISTINCT *

You cannot select individual columns

Question 12

What are the 5 aggregate expressions for open SQL

Answer 12

MAX( col )
MIN( col )
COUNT( * ) RETURNED AS TYPE I
AVG( col )  RETURNED AS TYPE F
SUM

Question 13

Which aggregate expressions can be combined with DISTINCT?

Answer 13

All of them, but min, max, avg , and sum make little sense primarily it is used with count

Question 14

Can a GROUP BY be used with SELECT *?

Answer 14

No, a field list must exist

Question 15

Can group by be used with POOL or CLUSTER tables

Answer 15

no

Question 16

What field types can not be used in GROUP BY

Answer 16

STRING and RAWSTRING

Question 17

What fields must be listed in the group by?

Answer 17

All fields that are not part of the aggregate functions

Question 18

Can group by contain table fields that are not part of the field list?

Answer 18

Yes, but it is a bad idea because it makes the results difficult to read

Question 19

Can HAVING be used without GROUP BY?

Answer 19

No

Question 20

What fields can be contained in the HAVING

Answer 20

Fields that are part of the GROUP BY and aggregate functions. In this case the aggregate functions will operate against the GROUPS created by the group by

Question 21

The only reason to use

SELECT …. ENDSELECT is

Answer 21

Extremely large amounts of data that would overwhelm the app server memory

Question 22

With in a SELECT … END?SELECT can I select into an internal table

Answer 22

Yes, if you use the package size addition

Question 23

Does SAP Open SQL support UNION and UNION ALL

Answer 23

Yes

Question 24

If in a select statement I want to combine field with a literal, what would that look like

Answer 24

SELECT bob && ‘name’ AS bobname

Question 25

What are the new operators and functions in open sql for string expressions

Answer 25

&&
LENGTH
LPAD
LTRIM
RTRIM
RIGHT
REPLACE
SUBSTRING
CONCAT

Question 26

Is there a difference between && and CONCAT?

Answer 26

&& can have multiple occurrences such as
fld1 && fld2 && fld3
CONCAT has exactly 2 occurrences
The result of all && can be 255 chars, CONCAT can be 1333 chars
&& is only used between elementary objects. CONCAT can handle string objects and string functions
&& keeps spaces in constants and lieterals, CONCAT has no special space handling

Question 27

What are the new operators and functions in open SQL for arithmetic expressions

Answer 27

+,-,*,/

ABS, CEIL, FLOOR DIV, MOD, ROUND

Question 28

If the field in the select does not have the required target type, how can i be converted?

Answer 28

With the CAST as type

CAST( fld1 as FLTP )

Question 29

When using operator / in a select, what type do the operands need to be?

Answer 29

Type F
So CAST( num1 AS FLTP ) /
CAST( num2 AS FLTP) *
CAST( 100 AS FLTP ) AS fld2

Question 30

Give an example of case in a select

Answer 30

Simple
CASE fld1
  WHEN opr1 then result1
  WHEN opr2 then result2
  ELSE result3
END  AS fld2
Complex
CASE WHEN sql_cond1 then result1
           WHEN sql_cond2 then result2
           ELSE result3
END AS fld2

Question 31

In a JOIN can fields from the right table exist in the where clause?

Answer 31

Yes they can now!

Question 32

Is a field from the right table required for the ON of a join?

Answer 32

Not anymore

Question 33

Must the mandt be supplied in a join

Answer 33

not anymore

Question 34

How many tables can be in a join

Answer 34

50

Question 35

How many tables can be in a join

Answer 35

50

Question 36

The ON operator can only be =, is this true

Answer 36

Not anymore Between, less than, greater than

Question 37

How is a subquery initiated?

Answer 37

WHERE EXISTS ( Select….) or
WHERE fld1 = ( SELECT MAX( myfld ) from …)
WHERE fld1 IN ( SELECT DISTINCT fld1 )