Using Code Pushdown in ABAP SQL Flashcards

1
Q

What are the two kinds of literals in ABAP SQL, and how are they specified?

A

text literals and number literals.

Text literals are specified within simple quotes, while number literals can be positive or negative whole numbers.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What types are literals in ABAP SQL, and how do they correspond to ABAP types?

A

Text literals in ABAP SQL are of type C (character), while number literals are of type I (integer). These types correspond to the types used in ABAP, where character literals are of type C and number literals are of type I.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is the recommended practice for using literals in ABAP SQL, and why?

A

Instead of using literals directly, it’s recommended to define constants and use them inside ABAP SQL statements. This practice improves readability and provides access to more types than just character and integer. When using a constant in an ABAP SQL statement, the prefix “@” is mandatory.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

How are constants used in ABAP SQL, and what prefix is mandatory when using them?

A

Constants are used in ABAP SQL by prefixing their name with “@”. This prefix is mandatory when using constants in ABAP SQL statements. For example, if a constant named “C_NUMBER” of type I with a value of 1234 is defined, it would be used in an ABAP SQL statement as “@C_NUMBER”.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is the purpose of the CAST expression in ABAP SQL?

A

The CAST expression in ABAP SQL allows for explicit type conversions, unlike ABAP itself which supports implicit type conversions. It enables developers to convert data from one type to another in a structured manner.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What types of operands can be used for type conversion in the CAST expression?

A

The operands for type conversion in the CAST expression can include literals, fields from the data source, arithmetic expressions, predefined functions, and various other expressions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What types can be specified as target types in the CAST expression?

A

Most predefined dictionary types, such as INT4, CHAR, DEC, FLTP, DATS, and others.
However, it’s not possible to use Dictionary Data elements as target types in ABAP SQL.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Are there any restrictions or limitations when using the CAST expression in ABAP SQL?

A

Yes, there are restrictions regarding the combination of source type and target type in the CAST expression. Some combinations are not supported at all, while others may work with limitations and could lead to runtime errors.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How are predefined types in the ABAP Dictionary mapped to specific ABAP types?

A

Predefined types in the ABAP Dictionary are mapped to specific ABAP types based on their definitions.

For example, the Dictionary type CHAR is mapped to ABAP type C, INT to type I, FLTP to type F, DEC to P, and so on.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Are there any Dictionary types that do not have a direct counterpart in ABAP?

A

Some Dictionary types, like MANDT, UNIT, and CUKY, are mapped to ABAP types C or P.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How is the length specified for Dictionary types DEC, QUAN, and CURR?

A

The length for DEC, QUAN, and CURR in the Dictionary is specified by the number of digits, while for ABAP type P, it’s specified by the number of bytes.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What does ABAP SQL’s case distinction provide?

A

a single value based on specified conditions, starting with “CASE” and ending with “END.”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What are the important restrictions when using division (/) in ABAP SQL arithmetic expressions?

A

The division operator (/) is only allowed in floating point expressions. Mixing floating point types with other numeric types in the same expression is not possible.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How can you ensure compatibility with floating point expressions in ABAP SQL?

A

You can use the CAST() function to convert any numeric operand to FLTP, ensuring compatibility within a floating point expression.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How can you ensure that a number literal, such as 100, is treated as a floating-point number in an arithmetic expression in ABAP SQL?

A

You can convert the number literal to type FLTP using the CAST() function. Alternatively, you can define a constant of type f with the value 100.0 and use it in the SQL expression.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is the default display format for floating-point numbers in ABAP SQL?

A

Floating-point numbers in ABAP SQL are displayed in scientific notation by default.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What are the four numeric functions?

A

DIV( sql_exp1, sql_exp2 )
MOD( sql_exp1, sql_exp2 )
DIVISION( sql_exp1, sql_exp2, dec )
ROUND( sql_exp, pos)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is the purpose of the operator && in ABAP?

A

In ABAP, the operator && allows you to combine char-like arguments into one long string.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

How is the operator && used in ABAP SQL?

A

In ABAP SQL, the operator && can be used to perform concatenation operations directly on the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Why is it necessary to manually add blanks when using the operator && in ABAP SQL?

A

The operator && does not add separators of any kind, so if blanks are needed between concatenated values, they must be added manually.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Give a few examples for important string functions.

A

. Function CONCAT( sql_exp1, sql_exp2 ) concatenates two strings (without
blanks).

. Function CONCAT_WITH_SPACE( sql_exp1, sql_exp2, num ) does the same but inserts a specified number of blanks in the middle.

. Functions UPPER( sql_exp ) and LOWER( sql_exp ) transforms an argument to uppercase / lowercase.

. Function INITCAP( sql_exp ) works like LOWER( ) but transforms the first letter of each word to upper case.

. Functions LEFT ( sql_exp, num ) extracts the first num characters from a given argument.

. RIGHT( sql_exp, num ) does the same but starts from the right.

. Function SUBSTRING( sql_exp, pos, num ) extracts a given numberof characters, starting from a given position.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

How does the CONCATENATE operator && behave with trailing blanks?

A

The CONCATENATE operator && ignores trailing blanks and only keeps a single blank if specified explicitly as ‘ ‘.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What types of functions are available for date, time, and timestamp information in ABAP SQL?

A

ABAP SQL offers both type-specific functions, which require input of a specific data type (e.g., DATS, DATN, TIMS, TIMN), and generic functions that can handle different types of input. Type-specific functions start with a prefix indicating the type they require, while generic functions have generic names.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is the difference between TIMS and TIMN in ABAP?

A

TIMS and TIMN represent time data types in ABAP. TIMS stores time information as a character-like string in the format ‘HHMMSS’, while TIMN stores time information in the format preferred by the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

How is TIMESTAMP data stored in ABAP?

A

TIMESTAMP stores time stamp information as a number without decimals, where each digit of the number represents the year, month, day, hours, minutes, and seconds.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What are the options for storing date information in a database field?

A

In ABAP, you can choose between DATS and DATN to store date information in a database field. DATS stores the date as a character of length 8 in the format ‘YYYYMMDD’, while DATN stores the information in the date type of the database, automatically converting it back to the 8-digits format when read with ABAP SQL.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Can you explain the difference between type-specific and generic functions?

A

Type-specific functions are designed to work with a specific data type, such as dates (DATS), times (TIMS), or timestamps (TIMESTAMPL). Generic functions, on the other hand, can handle different types of input and are not tied to a specific data type.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

How can we use these functions in ABAP SQL (DATS, DATN, TIMS, TIMN)?

A

You can use these functions in SELECT statements to manipulate, validate, or extract information from date, time, or timestamp fields. For example, you can use the IS_VALID() function to check the validity of a date or timestamp, or to perform calculations based on date or time information.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What are some examples of conversion functions available in ABAP SQL?

A

DATS_FROM_DATN() converts from the DB-specific date representation (DATN) to the ABAP-specific char-like date format (DATS).

DATS_TO_DATN() performs the conversion from DATS to DATN.

TMSTMP_TO_DATS() converts a timestamp into a date field.

TMSTMP_TO_TIMS() converts a timestamp into a time field.

UNIT_CONVERSION() converts a quantity from a source unit to a target unit, such as kilometers to miles.

CURRENCY_CONVERSION() converts an amount in one currency to the same amount in another currency on a specific reporting date.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

How can we use conversion functions in ABAP SQL?

A

These conversion functions can be used in SELECT statements to perform specific conversions on data retrieved from database tables.

31
Q

What does the DATS_FROM_DATN() function do?

A

It converts from DB-specific date representation (DATN) to ABAP-specific char-like date format (DATS).

32
Q

What is the purpose of the TMSTMP_TO_TIMS() function?

A

It converts a timestamp into a time field.

33
Q

How does the UNIT_CONVERSION() function work?

A

It converts a quantity from a source unit to a target unit.

34
Q

What is the function of CURRENCY_CONVERSION()?

A

It converts an amount in one currency to the same amount in another currency on a specific reporting date.

35
Q

What does the addition “order by” do in a SELECT statement?

A

It applies a sort order to the result set returned by the database.

36
Q

Where should the “order by” addition be placed in a SELECT statement?

A

It should be placed after the WHERE clause (if any) and before the INTO clause.

37
Q

What is the purpose of the GROUP BY clause in a SELECT statement?

A

The GROUP BY clause is used to separate the data set into subsets based on the values of specified columns. This allows aggregate functions to be applied independently to each subset, producing a result set with one row for each group.

38
Q

What is the default search direction when using the Order By clause?

A

Ascending.

39
Q

How can you specify the search direction in the Order By clause?

A

By using the keywords “Ascending” or “Descending” after each element of the Order By clause.

40
Q

What is the effect of adding the “distinct” keyword in a SELECT statement?

A

Adding “distinct” ensures that the SELECT statement returns only unique records, removing any duplicates from the result set based on all fields in the field list.

41
Q

What does the SUM function in ABAP SQL do?

A

calculates the sum of the results of a given SQL expression across a row set. The result set’s data type matches that of the SQL expression provided, which must be a numeric type.

42
Q

What do the MIN and MAX functions in ABAP SQL do?

A

The MIN function returns the minimum value, while the MAX function returns the maximum value, of the results of a specified SQL expression in a row set.

43
Q

What does the AVG function in ABAP SQL do?

A

The AVG function calculates the average value of the content of a specified column in a row set. By default, the result type is FLTP (floating point), but you can explicitly define the result type using the optional “AS dtype” addition.

44
Q

What is the purpose of COUNT(* ) or COUNT() in ABAP SQL?

A

Both COUNT( ) and COUNT() serve the same purpose, which is to count the number of rows in a row set.

45
Q

What does COUNT( DISTINCT sql_exp) do in ABAP SQL?

A

COUNT( DISTINCT sql_exp) calculates the number of distinct values of the results of the SQL expression sql_exp in a row set. The result type is INT4.

46
Q

What happens if the field list of a SELECT statement contains only aggregate functions?

A

If the field list contains only aggregate functions, the result set always consists of exactly one row, even if the aggregation is based on an empty data set.

47
Q

What is a join in SQL?

A

A join in SQL is a method used to combine rows from two or more tables based on a related column between them, resulting in a single result set containing columns from both tables.

48
Q

What are the supported join types in ABAP SQL?

A

INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN.

49
Q

How are columns of data sources identified in join conditions in ABAP SQL?

A

columns of data sources are fully identified by the name of the data source and the column name, separated by the tilde sign (~). For example, /DMO/CARRIER~CARRIER_ID.

50
Q

What does the “ON” keyword specify in a join condition?

A

It defines the relationship between columns of the joined data sources.

51
Q

How are columns referenced when using aliases in ABAP SQL?

A

Columns are referenced by the alias followed by a tilde sign (~) and then the column name.

52
Q

Why is it recommended to use aliases for data source names in ABAP SQL?

A

It shortens the SQL statement, improves readability, and avoids ambiguity when referencing columns.

53
Q

Why are aliases necessary in self-joins?

A

Aliases are necessary in self-joins to distinguish between multiple usages of the same data source, allowing us to reference each instance separately.

54
Q

How are aliases used in self-joins?

A

Aliases are assigned to each instance of the data source in the join condition, allowing us to differentiate between them when specifying join conditions, field lists, and WHERE clauses.

Picture: In this kind of self-join, the alias name is required in front of each column name because every column name exists in both data sources.

55
Q

What is an inner join in ABAP SQL?

A

An inner join combines rows from two tables based on a related column, selecting only the rows where the join condition is met in both tables.

56
Q

How do you write an inner join in ABAP SQL?

A

SELECT field_list
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

57
Q

What is a left outer join in ABAP SQL?

A

A left outer join returns all rows from the left table and the matched rows from the right table. If there’s no match, it returns NULL values for the columns from the right table.

58
Q

How do you write a left outer join in ABAP SQL?

A

SELECT field_list
FROM table1
LEFT OUTER JOIN table2
ON table1.column = table2.column;

59
Q

What is a right outer join in ABAP SQL?

A

A right outer join returns all rows from the right table and the matched rows from the left table. If there’s no match, it returns NULL values for the columns from the left table.

60
Q

How do you write a right outer join in ABAP SQL?

A

SELECT field_list
FROM table1
RIGHT OUTER JOIN table2
ON table1.column = table2.column;

61
Q

How can you join multiple data sources in ABAP SQL?

A

You can join multiple data sources by nesting joins within each other, adding additional data sources one at a time with their respective join conditions.

62
Q

What is the syntax for joining more than two data sources?

A

To join more than two data sources, you first surround the existing join with brackets and then add another join with the desired data source, alias, and join condition. This process can be repeated for each additional data source.
Currently, up to 50 data sources (49 joins) are allowed in one SELECT statement.

63
Q

How can you sequence joins in ABAP SQL?

A

Joins can be sequenced by using brackets to group joins together and then using the resulting join as a data source for subsequent joins.
(there are situations where the result can depend on the sequence in which the database evaluates the joins.)

64
Q

What is the advantage of using brackets to sequence joins?

A

Using brackets allows for more flexibility in the sequence of joins and enables the use of joins as data sources for subsequent joins, which can be necessary for complex queries involving multiple tables.

65
Q

Why is it recommended to use brackets in nested joins?

A

Using brackets in nested joins improves readability and helps avoid confusion about the sequence of evaluation, especially when there are multiple joins involved.

66
Q

What determines the sequence of evaluation in nested joins?

A

The sequence of evaluation in nested joins is determined by the sequence of the ON conditions, not by the sequence of the data sources in the FROM clause.

67
Q

Which of the following uses of SQL function SUBSTRING( ) returns the same result as LEFT( text_field, 1)?

A
SUBSTRING(text_field, 0, 1 )

B
SUBSTRING(text_field, 1, 0 )

C
SUBSTRING(text_field, 1, 1 )

A

C
SUBSTRING(text_field, 1, 1 )

68
Q

When are you forced to define alias names for the data sources of a join?

A
If the same data sources is used more than once.

B
Always, alias names for data sources are always mandatory.

C
Never, alias names for data sources are always optional.

D
If a field name is used in several data sources.

A

A
If the same data sources is used more than once.

That is right! Alias names for the data sources are optional unless the same data source is used more than once.

69
Q

Which of the following statements is true?

A
There are more predefined types in the ABAP language than in the ABAP Dictionary.

B
There is exactly the same number of predefined types in the ABAP Dictionary than in the ABAP language.

C
There are more predefined types in the ABAP Dictionary than in the ABAP language.

A

C
There are more predefined types in the ABAP Dictionary than in the ABAP language.

You are right. Several predefined types in the ABAP Dictionary are mapped to the same predefined type in the ABAP language. For example, types CHAR, MANDT, CUKY, UNIT are all mapped to type C. Therefore there are more predefined types in the ABAP Dictionary than in the ABAP language.

70
Q

You want to calculate the ratio of two numeric values in ABAP SQL. The result should be rounded to exactly 1 decimal. What do you use to calculate the division?

A
Numeric function DIVISION( )

B
Numeric function DIV( )

C
Operator /

A

A
Numeric function DIVISION( )

That is correct! Only function DIVISION( ) allows you to specify the number of decimals. DIV( ) returns an integer value and when you use operator /, the result is a floating point number.

71
Q

Your ABAP SQL SELECT statement contains the following FIELDS list: field1, field2, sum( field4 ). Only one of the following GROUP BY clauses causes a syntax error. Which one?

A
GROUP BY field1, field2

B
GROUP BY field1

C
GROUP BY field1, field2, field3

A

B
GROUP BY field1

You are right. Fields field1 and field2 are listed in the FIELDS list outside of the aggregate function. These two fields are mandatory in the GROUP BY clause. It is allowed (but not very common) to have more fields in the GROUP BY clause than necessary.

72
Q

EXTRACT_MONTH( ) is a generic SQL function which can handle input of different types. Which of the following built-in types are allowed for this function?

A
UTCLONG

B
DATN

C
TIMS

D
DATS

E
TIMESTAMPL

A

A
UTCLONG

B
DATN

D
DATS

You are right! EXTRACT_MONTH supports both data types and timestamps of type UTCLONG. Like all other generic SQL functions it does not support TIMESTAMP and TIMESTAMPL. Time fields are not a valid input for this specific function because you cannot extract a month from a point in time.

73
Q

You analyze ABAP code with an ABAP SQL SELECT. The FROM clause contains a nested joins but there are no brackets. What do you look at to find out in which sequence the joins are evaluated?

A
The sequence of data sources

B
the sequence of ON conditions

A

B
the sequence of ON conditions

That is correct! The sequence in which the nested joins are evaluated is derived from the sequence of the ON conditions.