SQL Expressions for RADL Database

[mathmari]

Hey! :giggle:

Use the RADL database as a basis for formulating the following SQL commands.
1. Give an SQL expression that outputs all personnel information for the employee Ernst Pach.
2. Give an SQL expression that outputs all personnel information about the employees, who earn more than 4000 EUR and live in Regensburg.
3. Give an SQL expression that outputs all information about parts in the warehouse, of which at least five pieces are actually in the warehouse.
4. Give an SQL expression that outputs all customers for whom an order exists and they come from Regensburg. Do not use a Cartesian product or join in the expression.
5. Give an SQL expression that outputs the order items with the lowest volume or part number 100001.At 1 do we have the following expression ?

SELECT *
FROM Kunde
WHERE Name = Ernst Pach
At 2 do we have the following expression ?

SELECT *
FROM Kunde
WHERE Kto > 4000 AND Ort = Regensburg :unsure:

 

[I like Serena]

[/QUOTE]
Hey mathmari!

What is that RADL database? (Wondering)

Your expressions seem correct to me, although we will need quotes around the strings.
So it should be for instance:

SELECT *
FROM Kunde
WHERE Name = "Ernst Pach"

 

[mathmari]

What is that RADL database? (Wondering)

Hmm I am not really familiar with SQL and I don't really know what RADL database is. Can we give the expressions into a kind of compiler and getthe desired result? :unsure:

Your expressions seem correct to me, although we will need quotes around the strings.
So it should be for instance:

SELECT *
FROM Kunde
WHERE Name = "Ernst Pach"

Ah ok!

 

[I like Serena]

Hmm I am not really familiar with SQL and I don't really know what RADL database is. Can we give the expressions into a kind of compiler and getthe desired result? :unsure:

I assumed that the RADL database is some kind of example set of tables that you're supposed to use for the problems.
What are the schema's of the example tables? Where does it say for instance that you have a column named "Kto"?

And yes, we can try out SQL on various locations.
For instance here:
http://sqlfiddle.com/

 

[mathmari]

I assumed that the RADL database is some kind of example set of tables that you're supposed to use for the problems.
What are the schema's of the example tables? Where does it say for instance that you have a column named "Kto"?

Ahh ok, we have the following :

Then at "personal" we have :

Therefore at expression 1 do we have the following ?

SELECT *
FROM personal
WHERE name = "Ernst Pach"

Then at 2 do we have the following ?

SELECT *
FROM personal 
WHERE gehalt > "4000" AND ort = "Regensburg"

:unsure:
For the expression 3 I think we have touse the table for the warehouse (lager):

Then do we have the following ?

SELECT *
FROM lager 
WHERE betand > "5"

Is that correct? :unsure:
For 4 we use the table for the customer (kunde) :

How can we check if for a customer an order exists ? :unsure:
At 5 thee table is :

:unsure:

 

[I like Serena]

Ahh ok, we have the following :

It looks as if you have access to a database program with the tables.
If so, then perhaps you can also try your SQL queries on it directly?
I think that one of the buttons at the top will likely gives you access to a window where you can type SQL queries. (Wondering)

Then at 2 do we have the following ?

SELECT *
FROM personal
WHERE gehalt > "4000" AND ort = "Regensburg"

If it is a number, then we shouldn't use quotes.
So we should be able to write gehalt > 4000.

For the expression 3 I think we have to use the table for the warehouse (lager):

SELECT *
FROM lager
WHERE betand > "5"

Shouldn't it be >= 5?
And there is a typo.
So it should be bestand >= 5.

For 4 we use the table for the customer (kunde) :

How can we check if for a customer an order exists ?

We can do a query on multiple tables.
For instance:

SELECT *
FROM customers AS k, auftrag AS a
WHERE k.kundnr = a.kundnr;

At 5 thee table is :

Looks as if we will need a query with a function in it.

 

[mathmari]

It looks as if you have access to a database program with the tables.
If so, then perhaps you can also try your SQL queries on it directly?
I think that one of the buttons at the top will likely gives you access to a window where you can type SQL queries. (Wondering)

Where can I type the SQL queries? Do you have an idea?

:unsure:

 

[I like Serena]

Where can I type the SQL queries? Do you have an idea?

I see "SQL" at the top right in the caption bar, which looks like something we might be able to click on.

Alternatively Sichten, Sequenzen, and Funktionen are candidates.

I see you have PostgreSQL. I found this:

That is, it may be that you have a dedicated SQL Shell (psql) in your windows menu where PostgreSQL is.

I also see that you have buttons for Durchsuchen and others.

Durchsuchen probably pops up a search dialog, which may support SQL.
It's also possible that we need to click an "Advanced" button or some such to get to SQL in a text window.

 

[mathmari]

I see "SQL" at the top right in the caption bar, which looks like something we might be able to click on.

At SQL I get :

So there I will the codes that we wrote above?

For example for 1 I will write there :

SELECT *
FROM personal
WHERE name = "Ernst Pach"

right? :unsure:

 

[I like Serena]

At SQL I get :

So there I will the codes that we wrote above?

Yep. (Sun)

 

[mathmari]

Yep. (Sun)

I get :

Have I done something wrong or does it mean that in the table that name is not included? :unsure:

 

[I like Serena]

I think that the syntax is somehow wrong.

What do you get if you run just SELECT * FROM personal; ?

Perhaps we need WHERE name = 'Ernst Pach' with single quotes instead of double quotes.

 

[I like Serena]

It appears that the problem is indeed the quotes, which must be single quotes.
I could get the same problem with http://sqlfiddle.com.
I selected PostgreSQL 9.6 and was able to execute:

 

[mathmari]

At 4 do you mean:

SELECT *
FROM kunde k, auftrag a
WHERE k.kundnr = a.kundnr And k.ort='Regensburg';

Or how do we write that? :unsure:At 5 do we have the following?

SELECT *
FROM auftragsposten a
WHERE a.anzahl<=ALL (select a.anzahl from auftragsposten) OR a.teilenr=100001;Here I get :

Is that correct? :unsure:

 

[I like Serena]

At 4 do you mean:

SELECT *
FROM kunde k, auftrag a
WHERE k.kundnr = a.kundnr And k.ort='Regensburg';

Or how do we write that?

Looks correct to me. (Nod)

At 5 do we have the following?

SELECT *
FROM auftragsposten a
WHERE a.anzahl<=ALL (select a.anzahl from auftragsposten) OR a.teilenr=100001;

That doesn't seem to work, since I see rows that are not at the minimum Anzahl. (Shake)

Instead we should be able to do:

SELECT *
FROM auftragsposten
WHERE anzahl=(SELECT MIN(anzahl) FROM auftragsposten) OR teilenr=100001;

 

[mathmari]

That doesn't seem to work, since I see rows that are not at the minimum Anzahl. (Shake)

Instead we should be able to do:

SELECT *
FROM auftragsposten
WHERE anzahl=(SELECT MIN(anzahl) FROM auftragsposten) OR teilenr=100001;

Ok! Then we get

Do we get that result because all items with teilenr = 100001 have number of items 1? Or why are all "anzahl" equal to 1, sincewe ask either to have the minimum volume OR the specific number? :unsure:

 

[I like Serena]

Do we get that result because all items with teilenr = 100001 have number of items 1? Or why are all "anzahl" equal to 1, sincewe ask either to have the minimum volume OR the specific number?

What do we get if we do:

SELECT *
FROM auftragsposten
WHERE teilenr=100001;

?
Are there parts with teilenr 100001 that have a higher anzahl? (Wondering)

 

[mathmari]

What do we get if we do:

SELECT *
FROM auftragsposten
WHERE teilenr=100001;

?
Are there parts with teilenr 100001 that have a higher anzahl? (Wondering)

Ok! So all parts with this number have anzahl 1, that's why! :geek:

(The volume that is asked means the number of items, or not? :unsure:)

 

[I like Serena]

(The volume that is asked means the number of items, or not? )

I presume 'volume' was translated from German. What was the question in German?

 

[mathmari]

I presume 'volume' was translated from German. What was the question in German?

The original statement is :

Geben Sie einen SQL-Ausdruck an, der die Auftragsposten mit dem geringsten Volumen oder der Teilnummer 100001 ausgibt.

:unsure:

 

[I like Serena]

The original statement is :

Geben Sie einen SQL-Ausdruck an, der die Auftragsposten mit dem geringsten Volumen oder der Teilnummer 100001 ausgibt.

I'm not entirely sure what they mean by Volume. It could indeed be the anzahl.

More generally I think volume means the total amount in transactions for a particular part.
So it could mean that we need to sum the anzahl's of all orders for a specific part.

 

[mathmari]

More generally I think volume means the total amount in transactions for a particular part.
So it could mean that we need to sum the anzahl's of all orders for a specific part.

I haven't really understood this part. Could you explain that furher to me? :unsure:

 

[mathmari]

Here are all the given tables :

arbeitsvorgang :

auftrag :

auftragsposten :

avo_belegung :

kunde :

lager :

lieferant :

personal :

teilereservierung :

teilestamm :

teilestruktur :

Do we maybe use any other table from them ? :unsure:

 

[I like Serena]

Do we maybe use any other table from them ? :unsure:

I think you had the correct table, but maybe we need to aggregate it differently.

What do you get for:

SELECT teilenr, SUM(anzahl)
FROM auftragsposten
GROUP BY teilenr;

?

 

[mathmari]

I think you had the correct table, but maybe we need to aggregate it differently.

What do you get for:

SELECT teilenr, SUM(anzahl)
FROM auftragsposten
GROUP BY teilenr;

?

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? :unsure:

 

[I like Serena]

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]

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? :unsure:

 

[mathmari]

I checked it and it is probably meant the total price ofan order... So we do :

SELECT *  
FROM auftragsposten  
WHERE gesamtpreis=(SELECT MIN(gesamtpreis) FROM auftragsposten) OR teilenr=100001;

Is that correct? :unsure:

 

[I like Serena]

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. :unsure:

I checked it and it is probably meant the total price of an order... So we do :

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. :unsure:

 

[Jameson]

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? :unsure:

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]

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: