# SQL commands

#### Klaas van Aarsen

##### MHB Seeker
Staff member
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

##### Well-known member
MHB Site Helper
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?

#### mathmari

##### Well-known member
MHB Site Helper
I checked it and it is probably meant the total price ofan order... So we do :

Code:
SELECT *
FROM auftragsposten
WHERE gesamtpreis=(SELECT MIN(gesamtpreis) FROM auftragsposten) OR teilenr=100001;
Is that correct?

#### Klaas van Aarsen

##### MHB Seeker
Staff member
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.

I checked it and it is probably meant the total price of an order... So we do :
Code:
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.

#### Jameson

Staff member
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?
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

##### Well-known member
MHB Site Helper
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?

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

#### Klaas van Aarsen

##### MHB Seeker
Staff member
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.

#### mathmari

##### Well-known member
MHB Site Helper
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?

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?

#### Klaas van Aarsen

##### MHB Seeker
Staff member
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.

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

##### Well-known member
MHB Site Helper
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?

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?

#### Klaas van Aarsen

##### MHB Seeker
Staff member
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?

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.

Last edited:

#### mathmari

##### Well-known member
MHB Site Helper
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?

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 = NULL

If there is no manager.name will it print the empty field there?

#### Klaas van Aarsen

##### MHB Seeker
Staff member
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

##### Well-known member
MHB Site Helper
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?

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))= 1

I get :

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

or not? Or have I understood that question wrongly?

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!!

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.name

and I get :

Is this correct?

Last edited:

#### mathmari

##### Well-known member
MHB Site Helper
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 NULL

I get :

It is correct, isn't it?

#### Klaas van Aarsen

##### MHB Seeker
Staff member
Is that correct?
They all look correct to me.

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.

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

##### Well-known member
MHB Site Helper
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?

#### mathmari

##### Well-known member
MHB Site Helper
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?

#### Klaas van Aarsen

##### MHB Seeker
Staff member
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.

Last edited:

#### mathmari

##### Well-known member
MHB Site Helper
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.

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);

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);

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

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?

Last edited:

#### Klaas van Aarsen

##### MHB Seeker
Staff member
How about a HAVING clause?

MHB Site Helper

#### Klaas van Aarsen

##### MHB Seeker
Staff member
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

#### mathmari

##### Well-known member
MHB Site Helper
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

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

#### Klaas van Aarsen

##### MHB Seeker
Staff member
2 and 3 look good to me, although we should try to run them and see if the results make sense.

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.

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.

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.

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.