MHB SQL Expressions for RADL Database

[mathmari]

Hey! :giggle:

Use the RADL database as a basis for formulating the following SQL commands.
1. Give an SQL expression that outputs all personnel information for the employee Ernst Pach.
2. Give an SQL expression that outputs all personnel information about the employees, who earn more than 4000 EUR and live in Regensburg.
3. Give an SQL expression that outputs all information about parts in the warehouse, of which at least five pieces are actually in the warehouse.
4. Give an SQL expression that outputs all customers for whom an order exists and they come from Regensburg. Do not use a Cartesian product or join in the expression.
5. Give an SQL expression that outputs the order items with the lowest volume or part number 100001.At 1 do we have the following expression ?

SELECT *
FROM Kunde
WHERE Name = Ernst Pach
At 2 do we have the following expression ?

SELECT *
FROM Kunde
WHERE Kto > 4000 AND Ort = Regensburg :unsure:

 

[I like Serena]

[/QUOTE]
Hey mathmari!

What is that RADL database? (Wondering)

Your expressions seem correct to me, although we will need quotes around the strings.
So it should be for instance:

SELECT *
FROM Kunde
WHERE Name = "Ernst Pach"

 

[mathmari]

What is that RADL database? (Wondering)

Hmm I am not really familiar with SQL and I don't really know what RADL database is. Can we give the expressions into a kind of compiler and getthe desired result? :unsure:

Your expressions seem correct to me, although we will need quotes around the strings.
So it should be for instance:

SELECT *
FROM Kunde
WHERE Name = "Ernst Pach"

Ah ok!

 

[I like Serena]

Hmm I am not really familiar with SQL and I don't really know what RADL database is. Can we give the expressions into a kind of compiler and getthe desired result? :unsure:

I assumed that the RADL database is some kind of example set of tables that you're supposed to use for the problems.
What are the schema's of the example tables? Where does it say for instance that you have a column named "Kto"?

And yes, we can try out SQL on various locations.
For instance here:
http://sqlfiddle.com/

 

[mathmari]

I assumed that the RADL database is some kind of example set of tables that you're supposed to use for the problems.
What are the schema's of the example tables? Where does it say for instance that you have a column named "Kto"?

Ahh ok, we have the following :

Then at "personal" we have :

Therefore at expression 1 do we have the following ?

SELECT *
FROM personal
WHERE name = "Ernst Pach"

Then at 2 do we have the following ?

SELECT *
FROM personal 
WHERE gehalt > "4000" AND ort = "Regensburg"

:unsure:
For the expression 3 I think we have touse the table for the warehouse (lager):

Then do we have the following ?

SELECT *
FROM lager 
WHERE betand > "5"

Is that correct? :unsure:
For 4 we use the table for the customer (kunde) :

How can we check if for a customer an order exists ? :unsure:
At 5 thee table is :

:unsure:

 

[I like Serena]

Ahh ok, we have the following :

It looks as if you have access to a database program with the tables.
If so, then perhaps you can also try your SQL queries on it directly?
I think that one of the buttons at the top will likely gives you access to a window where you can type SQL queries. (Wondering)

Then at 2 do we have the following ?

SELECT *
FROM personal
WHERE gehalt > "4000" AND ort = "Regensburg"

If it is a number, then we shouldn't use quotes.
So we should be able to write gehalt > 4000.

For the expression 3 I think we have to use the table for the warehouse (lager):

SELECT *
FROM lager
WHERE betand > "5"

Shouldn't it be >= 5?
And there is a typo.
So it should be bestand >= 5.

For 4 we use the table for the customer (kunde) :

How can we check if for a customer an order exists ?

We can do a query on multiple tables.
For instance:

SELECT *
FROM customers AS k, auftrag AS a
WHERE k.kundnr = a.kundnr;

At 5 thee table is :

Looks as if we will need a query with a function in it.

 

[mathmari]

It looks as if you have access to a database program with the tables.
If so, then perhaps you can also try your SQL queries on it directly?
I think that one of the buttons at the top will likely gives you access to a window where you can type SQL queries. (Wondering)

Where can I type the SQL queries? Do you have an idea?

:unsure:

 

[I like Serena]

Where can I type the SQL queries? Do you have an idea?

I see "SQL" at the top right in the caption bar, which looks like something we might be able to click on.

Alternatively Sichten, Sequenzen, and Funktionen are candidates.

I see you have PostgreSQL. I found this:

That is, it may be that you have a dedicated SQL Shell (psql) in your windows menu where PostgreSQL is.

I also see that you have buttons for Durchsuchen and others.

Durchsuchen probably pops up a search dialog, which may support SQL.
It's also possible that we need to click an "Advanced" button or some such to get to SQL in a text window.

 

[mathmari]

I see "SQL" at the top right in the caption bar, which looks like something we might be able to click on.

At SQL I get :

So there I will the codes that we wrote above?

For example for 1 I will write there :

SELECT *
FROM personal
WHERE name = "Ernst Pach"

right? :unsure:

 

[I like Serena]

At SQL I get :

So there I will the codes that we wrote above?

Yep. (Sun)

 

[mathmari]

Yep. (Sun)

I get :

Have I done something wrong or does it mean that in the table that name is not included? :unsure:

 

[I like Serena]

I think that the syntax is somehow wrong.

What do you get if you run just SELECT * FROM personal; ?

Perhaps we need WHERE name = 'Ernst Pach' with single quotes instead of double quotes.

 

[I like Serena]

It appears that the problem is indeed the quotes, which must be single quotes.
I could get the same problem with http://sqlfiddle.com.
I selected PostgreSQL 9.6 and was able to execute:

 

[mathmari]

At 4 do you mean:

SELECT *
FROM kunde k, auftrag a
WHERE k.kundnr = a.kundnr And k.ort='Regensburg';

Or how do we write that? :unsure:At 5 do we have the following?

SELECT *
FROM auftragsposten a
WHERE a.anzahl<=ALL (select a.anzahl from auftragsposten) OR a.teilenr=100001;Here I get :

Is that correct? :unsure:

 

[I like Serena]

At 4 do you mean:

SELECT *
FROM kunde k, auftrag a
WHERE k.kundnr = a.kundnr And k.ort='Regensburg';

Or how do we write that?

Looks correct to me. (Nod)

At 5 do we have the following?

SELECT *
FROM auftragsposten a
WHERE a.anzahl<=ALL (select a.anzahl from auftragsposten) OR a.teilenr=100001;

That doesn't seem to work, since I see rows that are not at the minimum Anzahl. (Shake)

Instead we should be able to do:

SELECT *
FROM auftragsposten
WHERE anzahl=(SELECT MIN(anzahl) FROM auftragsposten) OR teilenr=100001;

 

[mathmari]

That doesn't seem to work, since I see rows that are not at the minimum Anzahl. (Shake)

Instead we should be able to do:

SELECT *
FROM auftragsposten
WHERE anzahl=(SELECT MIN(anzahl) FROM auftragsposten) OR teilenr=100001;

Ok! Then we get

Do we get that result because all items with teilenr = 100001 have number of items 1? Or why are all "anzahl" equal to 1, sincewe ask either to have the minimum volume OR the specific number? :unsure:

 

[I like Serena]

Do we get that result because all items with teilenr = 100001 have number of items 1? Or why are all "anzahl" equal to 1, sincewe ask either to have the minimum volume OR the specific number?

What do we get if we do:

SELECT *
FROM auftragsposten
WHERE teilenr=100001;

?
Are there parts with teilenr 100001 that have a higher anzahl? (Wondering)

 

[mathmari]

What do we get if we do:

SELECT *
FROM auftragsposten
WHERE teilenr=100001;

?
Are there parts with teilenr 100001 that have a higher anzahl? (Wondering)

Ok! So all parts with this number have anzahl 1, that's why! :geek:

(The volume that is asked means the number of items, or not? :unsure:)

 

[I like Serena]

(The volume that is asked means the number of items, or not? )

I presume 'volume' was translated from German. What was the question in German?

 

[mathmari]

I presume 'volume' was translated from German. What was the question in German?

The original statement is :

Geben Sie einen SQL-Ausdruck an, der die Auftragsposten mit dem geringsten Volumen oder der Teilnummer 100001 ausgibt.

:unsure:

 

[I like Serena]

The original statement is :

Geben Sie einen SQL-Ausdruck an, der die Auftragsposten mit dem geringsten Volumen oder der Teilnummer 100001 ausgibt.

I'm not entirely sure what they mean by Volume. It could indeed be the anzahl.

More generally I think volume means the total amount in transactions for a particular part.
So it could mean that we need to sum the anzahl's of all orders for a specific part.

 

[mathmari]

More generally I think volume means the total amount in transactions for a particular part.
So it could mean that we need to sum the anzahl's of all orders for a specific part.

I haven't really understood this part. Could you explain that furher to me? :unsure:

 

[mathmari]

Here are all the given tables :

arbeitsvorgang :

auftrag :

auftragsposten :

avo_belegung :

kunde :

lager :

lieferant :

personal :

teilereservierung :

teilestamm :

teilestruktur :

Do we maybe use any other table from them ? :unsure:

 

[I like Serena]

Do we maybe use any other table from them ? :unsure:

I think you had the correct table, but maybe we need to aggregate it differently.

What do you get for:

SELECT teilenr, SUM(anzahl)
FROM auftragsposten
GROUP BY teilenr;

?

 

[mathmari]

I think you had the correct table, but maybe we need to aggregate it differently.

What do you get for:

SELECT teilenr, SUM(anzahl)
FROM auftragsposten
GROUP BY teilenr;

?

Could you explain to me what it does exactly? Why do we take the sum? GROUP BY means that that we consider more than 1 column? :unsure:

 

[I like Serena]

Could you explain to me what it does exactly? Why do we take the sum? GROUP BY means that that we consider more than 1 column?

It finds the sum of anzahl's for each teilenr, which should correspond to the volume for that teilenr.
GROUP BY means that we group the rows by teilenr and find the sum of anzahl for each group.

 

[mathmari]

It finds the sum of anzahl's for each teilenr, which should correspond to the volume for that teilenr.
GROUP BY means that we group the rows by teilenr and find the sum of anzahl for each group.

I haven't really understood why wehave to consider the sum and not just the anzahl? Could you explain that further to me? :unsure:

 

[mathmari]

I checked it and it is probably meant the total price ofan order... So we do :

SELECT *  
FROM auftragsposten  
WHERE gesamtpreis=(SELECT MIN(gesamtpreis) FROM auftragsposten) OR teilenr=100001;

Is that correct? :unsure:

 

[I like Serena]

I haven't really understood why wehave to consider the sum and not just the anzahl? Could you explain that further to me?

When trading in stocks, volume is the "number of shares of a security traded during a given period of time".
I thought that it perhaps applied here to, and that perhaps the volume was the number of parts sold. :unsure:

I checked it and it is probably meant the total price of an order... So we do :

SELECT *
FROM auftragsposten
WHERE gesamtpreis=(SELECT MIN(gesamtpreis) FROM auftragsposten) OR teilenr=100001;

Hmm, it asks for "Auftragsposten mit dem geringsten Volumen".
So I guess it's about the volume of individual parts. If the volume is the number of parts, it should be anzahl. If the volume is supposed to be the price of the parts, it should be gesamtpreis. :unsure:

 

[Jameson]

Could you explain to me what it does exactly? Why do we take the sum? GROUP BY means that that we consider more than 1 column? :unsure:

GROUP BY will aggregate by a column, so it needs a function like sum, max, min, etc. that goes across the group.

Let's say we have data like this.

Name, score
Jameson, 1
mathmari, 2
Jameson, 5
Jameson, 4
mathmari, 8
Klaas, 1
Klaas 8

If we do SELECT name, sum(score) from table GROUP BY name we would get

Jameson, 10
mathmari, 10
Klaas, 9

 

[mathmari]

I want to give a SQL expression also for the following :

1. Give a SQL expression that will find out which employee received the order from Maier Ingrid.
2. Give a SQL expression that lists the names of all employees with the names of their managers. If an employee does not have a manager, it should also be listed.
3. All customers are searched for who currently there are more than one order in the database. Enter a SQL command for this that uses statistical functions and grouping with GROUP BY.
4. All tasks of personnel are searched for whom exactly one person carries out this task. Give a SQL command for this that uses statistical functions and grouping using GROUP BY with associated HAVING.
5. Give a SQL expression that returns all employees who have at least the average salary of all employees.
6. Give one SQL expression that gives the following information:
(a) Which customer placed the order with order number 3?
(b) Who accepted the order?
(c) How expensive is the order?
Use the relations "kunde", "personal", "auftrag" and "auftragsposten".
As for 1 I am confused about which table we should consider. Could you explain that for me? :unsure:

At 2 we consider the table "personal" and check the field "vorgesetzt" ? :unsure:

 

[I like Serena]

1. Give a SQL expression that will find out which employee received the order from Maier Ingrid.
2. Give a SQL expression that lists the names of all employees with the names of their managers. If an employee does not have a manager, it should also be listed.

As for 1 I am confused about which table we should consider. Could you explain that for me?

Looks like we need to consider the kunde table to find the KUNDNR of 'Maier Ingrid'.
Then the autrag table to find orders from that customer.
Then the personal table to find which employee handled the order.

At 2 we consider the table "personal" and check the field "vorgesetzt" ?

Yes. I think "vorgesetzt" is a "persnr" in the personal table. :unsure:

 

[mathmari]

Looks like we need to consider the kunde table to find the KUNDNR of 'Maier Ingrid'.
Then the autrag table to find orders from that customer.
Then the personal table to find which employee handled the order.

So we have :

SELECT p.name
FROM personal p, auftrag a, kunde k
WHERE (p.persnr = a.auftrnr) AND (a.kundnr = k.nr) AND (k.name = 'Maier Ingrid') I get as a result a name... Is that command correct for that what we want to do? :unsure:
At 2 :

We want to print all names of personal and next to each name the manager, if it exists, right?

So we do :

SELECT p1.name, p2.name
FROM personal p1, personal p2
WHERE

But which condition do we write so that next to p1.name we get the correspronding p2.name? :unsure:

 

[I like Serena]

So we have :

SELECT p.name
FROM personal p, auftrag a, kunde k
WHERE (p.persnr = a.auftrnr) AND (a.kundnr = k.nr) AND (k.name = 'Maier Ingrid') I get as a result a name... Is that command correct for that what we want to do?

I believe so. (Nod)

At 2 : We want to print all names of personal and next to each name the manager, if it exists, right?
So we do :
SELECT p1.name, p2.name
FROM personal p1, personal p2
WHERE
But which condition do we write so that next to p1.name we get the corresponding p2.name?

Perhaps we should call them "employee" and "manager" instead of p1 and p2.
Then we need that "employee.vorgesetzt = manager.persnr".

 

[mathmari]

Perhaps we should call them "employee" and "manager" instead of p1 and p2.
Then we need that "employee.vorgesetzt = manager.persnr".

Writing the command :

SELECT employee.name, manager.name
FROM personal employee, personal manager
WHERE employee.vorgesetzt = manager.persnr

I get :

This is only the result that the employee has a manager, right? We have to print also the employees that have no manager, right? Do we write this condition in the below form somehow? :unsure:

WHERE employee.vorgesetzt = manager.persnr OR (employee.vorgesetzt != ALL (SELECT manager.persnr FROM personal)

As it is right now I think I get all possible combinations of employee and manager :

So do we maybe not have to use OR but something else? :unsure:

 

[I like Serena]

This is only the result that the employee has a manager, right? We have to print also the employees that have no manager, right? Do we write this condition in the below form somehow? :unsure:

WHERE employee.vorgesetzt = manager.persnr OR (employee.vorgesetzt != ALL (SELECT manager.persnr FROM personal)

If an employee does not have a manager, then the "vorgesetzt" field should be unspecified.
We should be able to test that with "employee.vorgesetzt IS NULL".

Still, we might not be able to easily combine that in 1 query.
So we might have 2 queries that we can combine with UNION.

 

[mathmari]

If an employee does not have a manager, then the "vorgesetzt" field should be unspecified.
We should be able to test that with "employee.vorgesetzt IS NULL".

Still, we might not be able to easily combine that in 1 query.
So we might have 2 queries that we can combine with UNION.

Do you mean as follows? :unsure:

SELECT employee.name, manager.name
FROM personal employee, personal manager
WHERE employee.vorgesetzt = manager.persnr
UNION
SELECT employee.name, manager.name
FROM personal employee, personal manager
WHERE employee.vorgesetzt = NULLIf there is no manager.name will it print the empty field there? :unsure:

 

[I like Serena]

Yes. I think we shouldn't have a manager in the 2nd query though.
To be honest, I'm not sure what we will get exactly, so I think we should just try it.

 

[mathmari]

3. All customers are searched for who currently there are more than one order in the database. Enter a SQL command for this that uses statistical functions and grouping with GROUP BY.

Using the commands :

SELECT k.name, count(a.auftrnr)
FROM kunde k, auftrag a
WHERE k.nr = ANY (SELECT kundnr from auftrag)
GROUP BY k.name

I get :

Is that correct? :unsure:

4. All tasks of personnel are searched for whom exactly one person carries out this task. Give a SQL command for this that uses statistical functions and grouping using GROUP BY with associated HAVING.

Do we write :

SELECT p.aufgabe
FROM personal p
GROUP BY p.aufgabe
HAVING (COUNT(p.aufgabe))= 1I get :

To check it we go to the table personal -> aufgabe :

or not? Or have I understood that question wrongly? :unsure:

5. Give a SQL expression that returns all employees who have at least the average salary of all employees.

I have written the command :

SELECT p.name
FROM personal p
WHERE p.gehalt > (SELECT AVG(gehalt) FROM personal)and I get :

which is correct! (Party)

6. Give one SQL expression that gives the following information:
(a) Which customer placed the order with order number 3?
(b) Who accepted the order?
(c) How expensive is the order?
Use the relations "kunde", "personal", "auftrag" and "auftragsposten".

I have written the command :

SELECT k.name, p.name, SUM(g.gesamtpreis)
FROM kunde k, auftrag a, personal p, auftragsposten g
WHERE k.nr = a.kundnr AND a.auftrnr = 3 AND a.persnr = p.persnr
GROUP BY k.name, p.nameand I get :

Is this correct? :unsure:

 

[mathmari]

Yes. I think we shouldn't have a manager in the 2nd query though.
To be honest, I'm not sure what we will get exactly, so I think we should just try it.

I figures it out!

Using the command :

SELECT employee.name, manager.name
FROM personal employee, personal manager
WHERE employee.vorgesetzt = manager.persnr
UNION
SELECT employee.name, angestellt.name
FROM personal employee, personal manager
WHERE employee.vorgesetzt IS NULLI get :

It is correct, isn't it? :geek:

 

[I like Serena]

Is that correct?

They all look correct to me. (Nod)

Do we write :
SELECT p.aufgabe
FROM personal p
GROUP BY p.aufgabe
HAVING (COUNT(p.aufgabe))= 1

To check it we go to the table personal -> aufgabe :
or not? Or have I understood that question wrongly?

I'm not exactly sure what "aufgabe" means in this context. :unsure:

SELECT employee.name, manager.name
FROM personal employee, personal manager
WHERE employee.vorgesetzt = manager.persnr
UNION
SELECT employee.name, angestellt.name
FROM personal employee, personal manager
WHERE employee.vorgesetzt IS NULL

It is correct, isn't it?

Where did "angestellt" come from?
It seems to me that the 2nd query should not include "personal manager" at all, since we're establishing that there is no manager.

 

[mathmari]

It seems to me that the 2nd query should not include "personal manager" at all, since we're establishing that there is no manager.

Ah when we use UNION do we not have to use the same tables at FROM at the first and the second part? :unsure:

 

[mathmari]

I want to write SQL commands also for the following :

1. We are looking for all employees who process at least three orders.
2. We are looking for two ways to find the names of all customers who live in a town where a supplier is also resident.
3. We are looking for two ways to find the part or the parts that cost the most.
4. You are looking for the names of all parts that have never been sold.
5. How many individual parts do the upper parts of the relation parts structure (Teilstruktur) consist of? The result should be a list in descending order one after the upper part number. Provide an SQL statement that does this using statistical functions, GROUP BY and ORDER BY. At 1 do we write the following ?

SELECT employee.name
FROM personal employee, auftrag a
WHERE employee.persnr = a.persnr
GROUP BY employee.name
HAVING count(a.auftrnr) >= 3 I get as a result one name, but I think that there must be more employess with at least three orders. Is something wrong at my command? :unsure:

 

[I like Serena]

Ah when we use UNION do we not have to use the same tables at FROM at the first and the second part?

We must have matching columns in both parts of the union yes.
However, we can add a virtual column like this:

SELECT employee.name, manager.name
FROM personal employee, personal manager
WHERE employee.vorgesetzt = manager.persnr
UNION
SELECT employee.name, 'no manager'
FROM personal employee
WHERE employee.vorgesetzt IS NULL

At 1 do we write the following ?

SELECT employee.name
FROM personal employee, auftrag a
WHERE employee.persnr = a.persnr
GROUP BY employee.name
HAVING count(a.auftrnr) >= 3

I get as a result one name, but I think that there must be more employess with at least three orders. Is something wrong at my command?

I think your command is correct.
If I try it with tables I created myself, it gives the correct results.

You might want to change for instance the 3 into a 2 to see if you get more matches then.

 

[mathmari]

I think your command is correct.
If I try it with tables I created myself, it gives the correct results.

You might want to change for instance the 3 into a 2 to see if you get more matches then.

Ah ok! I changed it to 2 and I got two names. :geek:

2. We are looking for two ways to find the names of all customers who live in a town where a supplier is also resident.

Is one way to do that, the following ?

SELECT k.name
FROM kunde k
WHERE k.ort = ANY (SELECT l.ort FROM lieferant l)Is the second way the following ?

SELECT k.name
FROM kunde k
WHERE EXISTS
(SELECT l.ort FROM lieferant l WHERE k.ort = l.ort);

:unsure:

3. We are looking for two ways to find the part or the parts that cost the most.

Is one way to do that, the following ?

SELECT T.bezeichnung
FROM teilestamm T
WHERE T.preis = (SELECT MAX(T.preis) FROM teilestamm T)Is the second way the following ?

SELECT T.bezeichnung
FROM teilestamm T
WHERE T.preis >= ALL
(SELECT T.preis
FROM teilestamm T);:unsure:

4. You are looking for the names of all parts that have never been sold.

How do we check that? Do we maybe check if there is a liefernr?

SELECT T.bezeichnung
FROM teilestamm T
WHERE T.liefernr is NULL

:unsure:

5. How many individual parts do the upper parts of the relation parts structure (Teilstruktur) consist of? The result should be a list in descending order one after the upper part number. Provide an SQL statement that does this using statistical functions, GROUP BY and ORDER BY.

Is one way to do that, the following ?

SELECT T.oberteilnr, COUNT(T.einzelteilnr)
FROM teilestruktur T
GROUP BY T.oberteilnr
ORDER BY T.oberteilnr DESC

Is the idea correct? We don;t have to use the column "anzahl" do we?

Which is an other way? :unsure:

 

[I like Serena]

How about a HAVING clause?

 

[mathmari]

How about a HAVING clause?

At which case do you mean? :unsure:

 

[I like Serena]

At which case do you mean?

Can't we do something like this for question 2?

SELECT k.name
FROM kunde k, lieferant l
WHERE k.ort = l.ort
GROUP BY k.name
HAVING COUNT(l.ort) > 0

:unsure:

 

[mathmari]

Can't we do something like this for question 2?

SELECT k.name
FROM kunde k, lieferant l
WHERE k.ort = l.ort
GROUP BY k.name
HAVING COUNT(l.ort) > 0

:unsure:

Ah yes! (Malthe) That is a third way to do that, right? I mean the two ways I wrote above are also correct, aren't they? :unsure:

 

[I like Serena]

2 and 3 look good to me, although we should try to run them and see if the results make sense. :unsure:

4. You are looking for the names of all parts that have never been sold.

How do we check that? Do we maybe check if there is a liefernr?
SELECT T.bezeichnung
FROM teilestamm T
WHERE T.liefernr is NULL

I think that would show the parts for which there is no supplier.
But such parts wouldn't even be in the database.
So I don't think that is what we are looking for. (Shake)

Instead we are looking for parts for which we cannot find an order in the auftrag table.

5. How many individual parts do the upper parts of the relation parts structure (Teilstruktur) consist of? The result should be a list in descending order one after the upper part number. Provide an SQL statement that does this using statistical functions, GROUP BY and ORDER BY.

Is one way to do that, the following ?

SELECT T.oberteilnr, COUNT(T.einzelteilnr)
FROM teilestruktur T
GROUP BY T.oberteilnr
ORDER BY T.oberteilnr DESC

Is the idea correct? We don;t have to use the column "anzahl" do we?

I find it difficult to understand the question. :unsure:

Is it the number of part types that an upper part consist of?
Or the total number of parts? That seems more plausible in which case we would need to use anzahl. We do have to assume that the unit is the same I guess. :unsure:

The sentence "The result should be a list in descending order one after the upper part number" does not seem to be grammatically correct.
It is not clear to me how we are supposed to order the results.
It doesn't really make to sort on oberteilnr though. That is just a unique number that identifies the upper part. :unsure: