MHB SQL Expressions for RADL Database

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

 

[I like Serena]

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

The query with EXISTS will find a name once if there exists at least 1 employee in Nurnberg that helped them.
Suppose there are 2 employees in Nurnberg that helped a particular customer.
Then the query with EXISTS will find the customer once, but the first query will find the customer twice. (Worried)

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

Yep. (Nod)

We may want to pick a VIEW that makes sense to have, such as one with all "interesting" columns from teilestamm and lager.
Then we can have a SELECT query that selects a specific column from it.

 

[I like Serena]

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

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

I think it's indeed only the salary. (Nod)

We might give the column an understandable name.
We could do for instance

CREATE VIEW KosteN AS
SELECT sum(p.gehalt) AS 'Jährliche Personalkosten'
FROM personal p;

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 KosteNPOr does it mean to create the two tables but when we write what the output shuld be we write it in one expression?

The GROUP BY clause should not do anything, should it?
Does anything change if we leave it out? (Wondering)

The only thing it could conceivably do, is merge rows together if they have the same data.
This could happen if there is more than 1 employee with the same name, and perhaps also the same salary.
Then it would be undesired to merge them together in any way!

Instead we should probably include the persnr in the view to make sure we treat employees with the same name as different.

 

[mathmari]

I think it's indeed only the salary. (Nod)

We might give the column an understandable name.
We could do for instance

CREATE VIEW KosteN AS
SELECT sum(p.gehalt) AS 'Jährliche Personalkosten'
FROM personal p;

Since it is the total annual personnel costs do we have to multiply the sum of all p.gehalt by $12$ ? :unsure:

The GROUP BY clause should not do anything, should it?
Does anything change if we leave it out? (Wondering)

The only thing it could conceivably do, is merge rows together if they have the same data.
This could happen if there is more than 1 employee with the same name, and perhaps also the same salary.
Then it would be undesired to merge them together in any way!

Instead we should probably include the persnr in the view to make sure we treat employees with the same name as different.

Do you mean that we have to write it either as above without GROUP BY, i.e.

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

SELECT *
FROM KosteNP or with GROUP BY but with persnr instead of name? :unsure:

 

[I like Serena]

Since it is the total annual personnel costs do we have to multiply the sum of all p.gehalt by $12$ ?

That depends on whether the gehalt is per month or per year.
Does it have a description anywhere?

Do you mean that we have to write it either as above without GROUP BY, i.e.

or with GROUP BY but with persnr instead of name?

I meant without GROUP BY and with an persnr extra as the first column.

 

[mathmari]

That depends on whether the gehalt is per month or per year.
Does it have a description anywhere?

In that column we have :

Can these be the salary per year? They seem to be the salary per month, don't they? :unsure:

I meant without GROUP BY and with an persnr extra as the first column.

Do you mean nit as follows ?

CREATE VIEW KosteNP AS
SELECT persnr, p.name, p.gehalt
FROM personal p

SELECT *
FROM KosteNP :unsure:

 

[I like Serena]

Can these be the salary per year? They seem to be the salary per month, don't they?

Yep. Looks like a salary per month. So then we must multiply by 12. (Nod)

Do you mean nit as follows ?

CREATE VIEW KosteNP AS
SELECT persnr, p.name, p.gehalt
FROM personal p

SELECT *
FROM KosteNP

Yep. (Nod)

 

[mathmari]

Yep. Looks like a salary per month. So then we must multiply by 12. (Nod)

Yep. (Nod)

So should it be as follows? CREATE VIEW KosteN AS
SELECT sum(p.gehalt)*12 AS 'Jährliche Personalkosten'
FROM personal p;

SELECT *
FROM KosteN
CREATE VIEW KosteNP AS
SELECT persnr, p.name, p.gehalt*12 AS 'Jährliche Personalkosten Pro Mitarbeiter'
FROM personal p

SELECT *
FROM KosteNP :unsure:

 

[I like Serena]

I think so yes. (Nod)