Exercise (Chapter 11):

In this exercise, you will write a program to help you to understand aggregate functions, joins, and subqueries.

Objectives

After completing this exercise, you will be able to:

• Understand how aggregate functions work

• Understand how to use joins

• Understand how to use subqueries

Estimated time to complete this exercise: 70 minutes

Exercises

These exercises provide practice working with the concepts learned in Chapter 11.

Exercise 1

You are to create a program that generates a report with concert information (from the YCONCERT table). To do this, copy program YIN015 from the development class YINTRODUCTION as your own and rename it YIN015_XX. Fill in the select statement using the appropriate aggregate functions.

This report should include the number of entries in the table, the sum of the seats occupied, the maximum number of seats occupied, and the average number of seats occupied. Use aggregate functions to calculate these amounts. See the sample output to determine the contents and layout of the report. Refer to the manual for the answers.

Exercise 2

Create a new program named YIN0016A_XX. Understanding the concept of inner and outer joins will be an important part of your ABAP training. Projects will generally discourage the use of nested selects and/or logical databases. For all the exercises that follow, you should use joins wherever possible. For this exercise you should use tables MARA and MARD. Using an inner join, write a report that gives the following information about materials with a material type of “DIEN":

Material Number

Material Type

Plant

Storage Location

Use the system field sy-dbcnt to track the number of database hits. Refer to Program YIN016A for the solution.

Create a program named YIN016B_XX. Returning the same fields as the inner join, use an outer join to write the report. Again, include sy-dbcnt in your output list to track the database hits. Which table is the driving table, and why? Does the OUTER JOIN return a different list? Refer to Program YIN016B for the solution.

Exercise 3

Name your program YIN0017_XX. This exercise will familiarize you with the concept of subqueries. Using a sub-query, find all the records in the SCARR table that do not exist in SFLIGHT. Write out the name of the airline carrier, and their carrier Ids. Your report will return 17 records. Refer to Program YIN017 for the solution.

Introduction to ABAP ABAP Exercises

Exercise

Chapter 11

---