SQL commands with subtraction and multiplication

[mathmari]

Hey! :giggle:

The following relations of a project administration of a company are given, where the primary key of the respective relations are underlined.

An employee can be assigned to several projects. Furthermore, an employee can have different competencies, which are billed at different daily rates. Each employee can do a task only on a daily basis. The assignment of employees to projects is contained in the relationship projektzuord. The relations mitarb und projekte contain the master data for the existing employees and projects.

In order to solve the tasks in the following, enter general SQL statements. The commands of the task should be as generally as possible, i.e. they should also be applicable to other data of the same form. Hint: You can calculate with the data type DATE like with an INTEGER

1) Which different employees (first name and last name) are assigned to the project with the name Lagerreorganisation ?
2) Which different employees (first name and last name) generate the most sales? Give a sorted list. The turnover is calculated from the number of planned days multiplied by the daily rate.
3) Which competencies (designation of competency) will be needed at least twice in the planned projects ?
4) How many employees with which skills are available in the period from 03/15/2022 to 04/09/2022, that is, not assigned to other projects?
5) There is a new project p42 called Datenreorganisation of customer kn99 and a Volume of 12000 euros. The employee Maier will be responsible for this new project from 01/03/2022 to 31/05/2022. Give SQL commands to update the database.
I have done the following :1)
SELECT DISTINCT m.vorname, m.name
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND z.projnr = 'p17'Is that correct ? :unsure:2)
SELECT m.vorname, m.name, MAX(DATEDIFF(day, z.datumbis, z.datumvon) * m.tagesastz)
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND m.kompetenz = z.kompetenzIs that the correct way to check the employees with the most sales? :unsure:

 

[mathmari]

At (3) I have done :

SELECT p.projektbez
FROM projektzuord z, projekte p
GROUP BY p.projektbez, p.projnr
HAVING COUNT(p.projektbez)>=2 AND p.projnr = ANY (SELECT z.projnr FROM projektzuord z)I think it is correct! :unsure:
At (4) I have done :

SELECT COUNT(m.mnr), m.kompetenz
FROM projektzuord z, mitarb m
WHERE ((z.datumbis < '20220315' OR z.datumvon > '20220409') AND (m.mnr = z.mitarbnr)) OR m.mnr != ALL (SELECT z.mitarbnr FROM projektzuord z)
GROUP BY m.mnr, m.kompetenzI don't get a result. Is that correct, i.e. in this period there is not available emplyee, is there? :unsure:

 

[mathmari]

At (2) I have done :

SELECT m.vorname, m.name, MAX(DATEDIFF(day, z.datumbis, z.datumvon)*m.tagessatz)
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND m.kompetenz = z.kompetenz
GROUP BY m.vorname, m.name
ORDER BY MAX(DATEDIFF(day, z.datumbis, z.datumvon)*m.tagessatz) But "DATEDIFF" is not working. How do we use that corrctly ? :unsure:

 

[I like Serena]

In order to solve the tasks in the following, enter general SQL statements. The commands of the task should be as generally as possible, i.e. they should also be applicable to other data of the same form. Hint: You can calculate with the data type DATE like with an INTEGER

1) Which different employees (first name and last name) are assigned to the project with the name Lagerreorganisation ?

SELECT DISTINCT m.vorname, m.name
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND z.projnr = 'p17'

Hey mathmari!

You have matched the employee on the column "mnr", but that is not a primary key is it?
That is, it does not uniquely identify an employee. (Worried)

Furthermore, it "should also be applicable to other data of the same form".
Suppose we want want to use it for "Facilitymanagement" instead of "Lagerreorganisation", then the query won't work, will it? (Worried)

 

[mathmari]

You have matched the employee on the column "mnr", but that is not a primary key is it?
That is, it does not uniquely identify an employee. (Worried)

"mnr" is a primary kry for mitarb, it is underlined. :unsure:

Furthermore, it "should also be applicable to other data of the same form".
Suppose we want want to use it for "Facilitymanagement" instead of "Lagerreorganisation", then the query won't work, will it? (Worried)

This one was also unclear to me. What does this mean that it should be also be applicable to other data of the same form? How can it be general ? :unsure:

 

[I like Serena]

"mnr" is a primary kry for mitarb, it is underlined.

There is only one primary key in a table, which is a combination of 1 or more columns.
In this case the primary key should consist of the combination of mnr and kompetenz.
To be fair, it does look a bit strange that they made kompetenz part of the primary key, but that is what it says.

Consider the table projectzuord. It has projnr underlined, but that is not a primary key on its own is it?
Any projnr will definitely be duplicated in multiple rows as there will usually be more than 1 employee that is assigned to a project.

This one was also unclear to me. What does this mean that it should be also be applicable to other data of the same form? How can it be general ?

The question has a desired output, and a given input.
The given input is "Lagerreorganisation". To make it applicable to other data of the same form, we should be able to replace 'Lagerreorganisation' by some other project name and then the query should still give the desired output. Since you have hardcoded the projnr 'p17', that won't work.

 

[mathmari]

The question has a desired output, and a given input.
The given input is "Lagerreorganisation". To make it applicable to other data of the same form, we should be able to replace 'Lagerreorganisation' by some other project name and then the query should still give the desired output. Since you have hardcoded the projnr 'p17', that won't work.

How do we give that "Lagerreorganisation" as input ? Do we set a variable equal to that befor we write SELECT ? :unsure:

 

[I like Serena]

How do we give that "Lagerreorganisation" as input ? Do we set a variable equal to that befor we write SELECT ?

There are multiple ways to do it.
If I'm not mistaken, we can use a nested select in the WHERE clause. Or a nested select in the FROM clause. Or a WITH clause. Or we can use a VIEW that we create beforehand. Or instead, we can also just use another table in the query.

 

[mathmari]

If I'm not mistaken, we can use a nested select in the WHERE clause.

You mean to write WHERE "Lagerreorganisation" is from the given table? Or how do you mean that? I haven't really understood that. :unsure:

 

[I like Serena]

You mean to write WHERE "Lagerreorganisation" is from the given table? Or how do you mean that? I haven't really understood that.

See for instance https://beginner-sql-tutorial.com/sql-subquery.htm.

 

[mathmari]

See for instance https://beginner-sql-tutorial.com/sql-subquery.htm.

So do you mean it as follows ?

SELECT DISTINCT m.vorname, m.name
FROM mitarb m, projektzuord z, projekte p
WHERE m.mnr = z.mitarbnr AND p.projektbez = (SELECT p.projektbez FROM projekte p WHERE p.projektbez = 'Lagerreorganisation' ) AND p.projnr = z.projnr :unsure:

 

[I like Serena]

So do you mean it as follows ?

SELECT DISTINCT m.vorname, m.name
FROM mitarb m, projektzuord z, projekte p
WHERE m.mnr = z.mitarbnr AND p.projektbez = (SELECT p.projektbez FROM projekte p WHERE p.projektbez = 'Lagerreorganisation' ) AND p.projnr = z.projnr

Did you check if it works?

Either way, I think it should be:

SELECT DISTINCT m.vorname, m.name
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND z.projnr IN (SELECT p.projnr FROM projekte p WHERE p.projektbez = 'Lagerreorganisation' )

That is, we don't need `projekte p` in the FROM clause, and we don't need to refer to it in the WHERE clause either.
I've also changed the "=" into "IN" since the result of the sub query is a set. Btw, alternatively we can also do:

SELECT DISTINCT m.vorname, m.name
FROM mitarb m, projektzuord z, projekte p
WHERE m.mnr = z.mitarbnr AND p.projnr = z.projnr AND p.projektbez = 'Lagerreorganisation'

can't we?

 

[mathmari]

Did you check if it works?

Yes, I got the correct result, the same with the result I get with your commands! :geek:

Either way, I think it should be:

SELECT DISTINCT m.vorname, m.name
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND z.projnr IN (SELECT p.projnr FROM projekte p WHERE p.projektbez = 'Lagerreorganisation' )

That is, we don't need `projekte p` in the FROM clause, and we don't need to refer to it in the WHERE clause either.
I've also changed the "=" into "IN" since the result of the sub query is a set. Btw, alternatively we can also do:

SELECT DISTINCT m.vorname, m.name
FROM mitarb m, projektzuord z, projekte p
WHERE m.mnr = z.mitarbnr AND p.projnr = z.projnr AND p.projektbez = 'Lagerreorganisation'

can't we?

I see !

At (2) I have written the below commands :

SELECT m.vorname, m.name, MAX(DATEDIFF(day, z.datumbis, z.datumvon)*m.tagessatz)
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND m.kompetenz = z.kompetenz
GROUP BY m.vorname, m.name
ORDER BY MAX(DATEDIFF(day, z.datumbis, z.datumvon)*m.tagessatz)


I get an error because of the DATEDIFF but in Google I found that it is defined like that. What am I writing wrong? And is the idea in ngeneral of that commands correct ? Do we have to use also here some nested WHERE ? :unsure:

 

[I like Serena]

At (2) I have written the below commands :

SELECT m.vorname, m.name, MAX(DATEDIFF(day, z.datumbis, z.datumvon)*m.tagessatz)
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND m.kompetenz = z.kompetenz
GROUP BY m.vorname, m.name
ORDER BY MAX(DATEDIFF(day, z.datumbis, z.datumvon)*m.tagessatz)I get an error because of the DATEDIFF but in Google I found that it is defined like that. What am I writing wrong? And is the idea in ngeneral of that commands correct ? Do we have to use also here some nested WHERE ?

What is the error that you get?

I think DATEDIFF will work, but we're not using it right. We shouldn't take a MAX of it. Instead we should list for each employee what their turnover is, give the column a name (for instance "AS turnover"), and sort on that name in the ORDER clause in descending order.

 

[mathmari]

What is the error that you get?

I get an error about "day" : no such column: day. :unsure:

 

[I like Serena]

I get an error about "day" : no such column: day.

What do you get if you run:

SELECT DATEDIFF(day, '2011-08-25', '2017-08-25') AS DateDiff;

 

[mathmari]

What do you get if you run:

SELECT DATEDIFF(day, '2011-08-25', '2017-08-25') AS DateDiff;

The same error as previously. :unsure:

 

[I like Serena]

The same error as previously.

Which SQL are you using?

If I run it with w3schools, it works just fine:
https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_datediff

 

[mathmari]

Which SQL are you using?

I use this one . So is it because of the compiler or am I doing something wrong? :unsure:

 

[I like Serena]

I use this one . So is it because of the compiler or am I doing something wrong?

Yes.

I found that we get that error if we try to use PostgreSQL.
In its manual I found that we can use for instance:
SELECT DATE_PART('day', '2011-12-31 01:00:00'::timestamp - '2011-12-29 23:00:00'::timestamp);
instead.

Alternatively we can use a different SQL compiler.
If we pick:

then it works for me.

 

[mathmari]

I think DATEDIFF will work, but we're not using it right. We shouldn't take a MAX of it. Instead we should list for each employee what their turnover is, give the column a name (for instance "AS turnover"), and sort on that name in the ORDER clause in descending order.

Do we not do that as follows ?

SELECT m.vorname, m.name, DATEDIFF(day, z.datumbis, z.datumvon)*m.tagessatz
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND m.kompetenz = z.kompetenz
GROUP BY m.vorname, m.name
ORDER BY MAX(DATEDIFF(day, z.datumbis, z.datumvon)*m.tagessatz) I get the error : " Column 'projektzuord.datumbis' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. "

:unsure:

 

[I like Serena]

I get the error : " Column 'projektzuord.datumbis' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. "

That's because you have GROUP BY and ORDER BY clauses that have certain requirements. If we leave out those clauses, it works just fine.

 

[mathmari]

That's because you have GROUP BY and ORDER BY clauses that have certain requirements. If we leave out those clauses, it works just fine.

So is the following correct ?

SELECT m.vorname, m.name, DATEDIFF(day, z.datumvon, z.datumbis)*m.tagessatz
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND m.kompetenz = z.kompetenzDo we just have to order the list that we get? :unsure:

 

[I like Serena]

So is the following correct ?

SELECT m.vorname, m.name, DATEDIFF(day, z.datumvon, z.datumbis)*m.tagessatz
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND m.kompetenz = z.kompetenz

Do we just have to order the list that we get?

If I run that, then I get:

I see a couple of problems:
1. Armin shows up twice.
2. The 3rd column doesn't have a header.
3. The table doesn't have the employee with the biggest sales at the top.
(Worried)

 

[mathmari]

If I run that, then I get:
View attachment 11689

I see a couple of problems:
1. Armin shows up twice.
2. The 3rd column doesn't have a header.
3. The 3rd column has negative numbers.
(Worried)

I corrected the problems 2 and 3. Do we not solve the first problem with DISTINCT ?

I have now this but I get again Armin several times. :unsure:

 

[I like Serena]

I corrected the problems 2 and 3. Do we not solve the first problem with DISTINCT ?

I have now this but I get again Armin several times.

Armin can work both as a programmer and as a system administrator and he gets paid different rates for those roles.
I think we have to combine them somehow.

 

[mathmari]

Armin can work both as a programmer and as a system administrator and he gets paid different rates for those roles.
I think we have to combine them somehow.

Do we do that with GROUP BY ? :unsure:

 

[I like Serena]

Do we do that with GROUP BY ?

Yes.

 

[mathmari]

Yes.

Do we not write that as follows ?

SELECT DISTINCT m.vorname, m.name, DATEDIFF(day, z.datumvon, z.datumbis)*m.tagessatz AS Umsatz
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND m.kompetenz = z.kompetenz
GROUP BY m.vorname, m.name Because I get the error :
Column 'projektzuord.datumvon' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

:unsure:

 

[I like Serena]

Because I get the error :
Column 'projektzuord.datumvon' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

We are trying to group the 2 rows that refer to Armin into 1 row.
Those 2 rows have different numbers for the Umsatz.
How should those 2 numbers be combined (aka aggregated)?

 

[mathmari]

We are trying to group the 2 rows that refer to Armin into 1 row.
Those 2 rows have different numbers for the Umsatz.
How should those 2 numbers be combined (aka aggregated)?

We have to add the corresponding values of Umsatz, right? So do we write something like :

SELECT DISTINCT m.vorname, m.name, DATEDIFF(day, z.datumvon, z.datumbis)*m.tagessatz AS Umsatz
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND m.kompetenz = z.kompetenz
GROUP BY m.vorname, m.name,SUM(DATEDIFF(day, z.datumvon, z.datumbis)*m.tagessatz) :unsure:

 

[I like Serena]

We have to add the corresponding values of Umsatz, right? So do we write something like

Something like that yes (Nod)

Does it work?

 

[mathmari]

Something like that yes (Nod)

Does it work?

No, I get an error : I get this :unsure:

 

[I like Serena]

No, I get an error : I get this

It says:
> Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Perhaps we shouldn't put the SUM in the GROUP BY clause, but somewhere else?
Can we find an example how to use a GROUP BY clause?

 

[mathmari]

It says:
> Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Perhaps we shouldn't put the SUM in the GROUP BY clause, but somewhere else?
Can we find an example how to use a GROUP BY clause?

Ah I fixed it ! Now it works!
Is my below attempt correct ? :unsure:

At (3) :

SELECT p.projektbez
FROM projektzuord z, projekte p
GROUP BY p.projektbez, p.projnr
HAVING COUNT(p.projektbez)>=2 AND p.projnr = ANY (SELECT z.projnr FROM projektzuord z)At (4) :

SELECT COUNT(m.mnr), m.kompetenz
FROM projektzuord z, mitarb m
WHERE ((z.datumbis $<$ '20220315' OR z.datumvon $>$ '20220409') AND (m.mnr = z.mitarbnr)) OR m.mnr != ALL (SELECT z.mitarbnr FROM projektzuord z)
GROUP BY m.mnr, m.kompetenzAt (5) do we make INSERT the new values in the corresponding tables?

INSERT INTO projekte
VALUES
('p42', 'Datenreorganisation', 'kn99', 12000);

INSERT INTO projektzuord
VALUES
('p42', 123, 'k3', '2022-03-01', '2022-05-31');
:unsure: