MHB SQL Expressions for RADL Database

[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:

 

[mathmari]

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

I get the same results with both ways, in 2 and in 3.

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.

With the code I wrote above I get :

These have NULL as liefernr. I thought to check if they have a liefernr since I don't see how the parts are related with the auftrag table. So is my approach wrong? :unsure:

To see the whole table I wrote :

SELECT *
FROM teilestamm T

and we get :

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 original statement is :
Aus wie vielen Einzelteilen bestehen die Oberteile der Relation Teilestruktur? Das Ergebnis soll eine nach Oberteilnummer absteigend sortierte Liste sein.

So we are looking for the number of part types that an upper part consist of, or not?

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:

We have to sort the list as for oberteilnr in descending order.

With the above code I get :

So it is correct, isn't it? :unsure:

 

[I like Serena]

These have NULL as liefernr. I thought to check if they have a liefernr since I don't see how the parts are related with the auftrag table. So is my approach wrong? :unsure:

Doesn't liefer mean supplier? :unsure:
Perhaps the company sells bicycles (Rad)? That would explain why bicyles do not have a supplier.

I think we have a company that buys parts from suppliers.
The employees work to put those parts together to create upper parts.
And those employees sell the parts to customers who place orders to get them.

The table auftragposten identifies which parts are part of an auftrag.
So I believe we are looking for parts that are not referred to in auftragposten.

So we are looking for the number of part types that an upper part consist of, or not?
We have to sort the list as for oberteilnr in descending order.

With the above code I get :

So it is correct, isn't it?

It seems to me that they are asking for the number of individual parts.
If that is the case then I think we should sum the anzahl's of the parts instead of counting them.
Other than that I think your query is correct. :unsure:

 

[mathmari]

Doesn't liefer mean supplier? :unsure:
Perhaps the company sells bicycles (Rad)? That would explain why bicyles do not have a supplier.

I think we have a company that buys parts from suppliers.
The employees work to put those parts together to create upper parts.
And those employees sell the parts to customers who place orders to get them.

The table auftragposten identifies which parts are part of an auftrag.
So I believe we are looking for parts that are not referred to in auftragposten.

So you mean to write :

SELECT t.bezeichnung
FROM teilestamm t
WHERE t.teilnr != ALL (SELECT a.teilenr FROM auftragsposten a)

right? :unsure:

 

[I like Serena]

Yep. (Nod)

 

[mathmari]

Yep. (Nod)

Great! (Sun)Next, I want to write SQL commands for the following :

1. A SQL expression should be used to check which customers (name) are being looked after by people from Nurnberg for their orders. Give at least two different solutions and use in one solution the EXISTS command.
2. Create a view that outputs all parts from the Teilestamm that are not in the warehouse.
3. Create a view that calculates the total annual personnel costs and another view that calculates the annual costs per employee.I have done the following :

1 :
First Way :

SELECT k.name
FROM kunde k, auftrag a, personal p
WHERE k.nr = a.kundnr AND a.persnr = p.persnr AND p.ort = 'Nurnberg'

Second Way with EXISTS:

SELECT k.name
FROM kunde k
WHERE EXISTS
(SELECT * FROM auftrag a, personal p WHERE k.nr = a.kundnr AND a.persnr = p.persnr AND p.ort = 'Nurnberg')Or is it meant to use EXISTS in an other way, since these ways are almost the same? :unsure:
2 :

Could you explain to me what views are? Does this mean that the result should be in a table?

Do we write it as follows (although I get still an error) ?

CREATE VIEW [Teile] AS
SELECT T.teilnr
FROM teilestamm T, lager L
WHERE T.teilnr = L.teilenr AND L.bestand = 0;

:unsure:

 

[I like Serena]

1 :
First Way :

SELECT k.name
FROM kunde k, auftrag a, personal p
WHERE k.nr = a.kundnr AND a.persnr = p.persnr AND p.ort = 'Nurnberg'

Second Way with EXISTS:

SELECT k.name
FROM kunde k
WHERE EXISTS
(SELECT * FROM auftrag a, personal p WHERE k.nr = a.kundnr AND a.persnr = p.persnr AND p.ort = 'Nuernberg')Or is it meant to use EXISTS in an other way, since these ways are almost the same?

Did you run the queries?
I suspect they will give different output. (Worried)

And yes, I think those queries are intended.
I think it's an exercise to learn about EXISTS.

2 :

Could you explain to me what views are? Does this mean that the result should be in a table?

Do we write it as follows (although I get still an error) ?

CREATE VIEW [Teile] AS
SELECT T.teilnr
FROM teilestamm T, lager L
WHERE T.teilnr = L.teilenr AND L.bestand = 0;

A view is a virtual table that can be constructed from multiple tables for convenience.
It allows us to use simpler queries.

However, a view on its own does not create output.
We still need a SELECT query on the view to get output.

Your syntax seems to be correct although there shouldn't be square brackets around Teile.

 

[mathmari]

Did you run the queries?
I suspect they will give different output. (Worried)

And yes, I think those queries are intended.
I think it's an exercise to learn about EXISTS.

Besides that I had a typo at the second query at "Nurnberg", I get the same results, two names, I just get them in a different order. So are the queries correct or do we have to amend something? :unsure:

A view is a virtual table that can be constructed from multiple tables for convenience.
It allows us to use simpler queries.

However, a view on its own does not create output.
We still need a SELECT query on the view to get output.

Your syntax seems to be correct although there shouldn't be square brackets around Teile.

I deleted the square brackets, but I still get no results. :unsure:

 

[I like Serena]

Besides that I had a typo at the second query at "Nurnberg", I get the same results, two names, I just get them in a different order. So are the queries correct or do we have to amend something?

The first query could have duplicate names. It would happen if a customer has orders that have been handled by different people that live in Nurnberg.
That is, I believe we need DISTINCT.

I deleted the square brackets, but I still get no results.

That is expected. The view was merely created.
We need for instance SELECT * FROM Teile afterwards to get output.

 

[mathmari]

The first query could have duplicate names. It would happen if a customer has orders that have been handled by different people that live in Nurnberg.
That is, I believe we need DISTINCT.

Why do we need the DISTINCT only at the first query, without EXISTS ? :unsure:

That is expected. The view was merely created.
We need for instance SELECT * FROM Teile afterwards to get output.

So we create a table that includes that data that we want and then we print the column of this table as usual, right? :unsure:

 

[mathmari]

3. Create a view that calculates the total annual personnel costs and another view that calculates the annual costs per employee.

The cost is the "gehalt" or are there also other costs besides the salary? :unsure:

If we consider only the salary, do we write the following ?

CREATE VIEW KosteN AS
SELECT sum(p.gehalt)
FROM personal p;

SELECT *
FROM KosteN For the annual costs per employee do we write ?

CREATE VIEW KosteNP AS
SELECT p.name, p.gehalt
FROM personal p
GROUP BY p.name, p.gehalt;

SELECT *
FROM KosteNP Or does it mean to create the two tables but when we write what the output shuld be we write it in one expression? :unsure: