The prerequisites – a few basics about the SELECT statement

Vimeo

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von Vimeo.
Mehr erfahren

Video laden

We use a SELECT query in many small examples even before it is covered in detail in this course. This is no problem at all for most participants, because we limit ourselves here to the most basic things that you may already know from OpenSQL in ABAP. For all others I would like to explain here at the beginning the SELECT query briefly. If you enter the examples in the SQL console, I recommend you also use a different table from the demo data model to familiarize yourself with the structures and contents.

A SELECT query returns a table as a result. We define the columns in the field list of the so-called SELECT clause. We usually refer to the column names of the source table or we take all columns with an asterisk (*):

-- Syntax: 
SELECT <column> FROM <source table>;

-- example with *:
SELECT * 
  FROM tasks;

-- example with single column name that are separated by a comma
SELECT id, 
       title,
       due_date
  FROM tasks;

If the rows are to be counted, then we can do that with the aggregate expression COUNT(*). If there are other columns in the query that are not aggregated, we need to repeat them in the GROUP BY clause.

-- # of rows in table TASKS
SELECT  COUNT(*) 
  FROM tasks;

-- # of rows in table TASKS per assignee
SELECT assignee,
       COUNT(*)
  FROM tasks
 GROUP BY assignee;

To restrict the set of rows from the source table, there is the WHERE clause. There you can specify logical conditions that must be met for all records to be considered when calculating the query result.

-- Only tasks for assignee 1
SELECT * 
  FROM tasks
 WHERE assignee = 1;

-- compare with a value set - tasks in status 1, 2 or 3
SELECT * 
  FROM tasks
 WHERE status IN (1, 2, 3);

We will go into much more detail about the different clauses later.