MHB SQL Expressions for RADL Database

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