SQL Expressions for RADL Database

67 replies · 6K views · last reply
mathmari
Gold Member
MHB
Messages
4,984
Reaction score
7
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:
 
[/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"

🤔
 
Klaas van Aarsen said:
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:
Klaas van Aarsen said:
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! 🤔
 
mathmari said:
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/
 
Klaas van Aarsen said:
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 :
Screenshot of the phpMyAdmin table list for database student5, showing tables arbeitsvorgang, auftrag, auftragsposten, avo_belegung, kunde, lager, lieferant, personal, teilereservierung, teilestamm and teilestruktur, each with action buttons.


Then at "personal" we have :
Table structure of the personal table with columns persnr, name, strasse, plz, ort, gebdatum, stand, vorgesetzt, gehalt, beurteilung and aufgabe, with their data types, NOT NULL flags and key icons on persnr and vorgesetzt.
Therefore at expression 1 do we have the following ?

Code:
SELECT *
FROM personal
WHERE name = "Ernst Pach"
Then at 2 do we have the following ?

Code:
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):
Screenshot of a database table structure editor with columns Spalte, Datentyp, Nicht Null, Standardwert, Constraints and Aktionen; rows teilenr (integer, NOT NULL, key icons), lagerort (character(6)), bestand (smallint, NOT NULL), mindbest, reserviert and bestellt (smallint).


Then do we have the following ?

Code:
SELECT *
FROM lager 
WHERE betand > "5"

Is that correct? :unsure:
For 4 we use the table for the customer (kunde) :
Screenshot of a database table definition in German with columns Spalte, Datentyp, Nicht Null, Standardwert, Constraints and Aktionen; rows nr integer NOT NULL, name, strasse, plz, ort and sperre character types.


How can we check if for a customer an order exists ? :unsure:
At 5 thee table is :
Screenshot of a database table structure in a phpMyAdmin-style interface with columns Spalte, Datentyp, Nicht Null, Standardwert, Constraints and Aktionen; rows posnr integer NOT NULL with key icon, auftrnr integer and teilenr integer with key and link icons, anzahl smallint, gesamtpreis numeric(10,2).


:unsure:
 
mathmari said:
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)

mathmari said:
Then at 2 do we have the following ?
Code:
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. 🤔

mathmari said:
For the expression 3 I think we have to use the table for the warehouse (lager):
Code:
SELECT *
FROM lager
WHERE betand > "5"
Shouldn't it be >= 5?
And there is a typo.
So it should be bestand >= 5. 🤔

mathmari said:
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:
Code:
SELECT *
FROM customers AS k, auftrag AS a
WHERE k.kundnr = a.kundnr;
🤔

mathmari said:
At 5 thee table is :

Looks as if we will need a query with a function in it. 🤔
 
Last edited:
Klaas van Aarsen said:
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?

Screenshot of the phpPgAdmin interface showing a PostgreSQL 9.3.24 connection on localhost:5432 as user student5, with the database UebungsDokDat selected, and a row of navigation tabs labelled Tabellen, Sichten, Sequenzen, Funktionen, Volltextsuche, Domänen, Rechte and Exportieren.


:unsure:
 
mathmari said:
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:
Windows Start menu expanded under PostgreSQL 11, listing Application Stack Builder, Installation notes, pgAdmin 4, pgAdmin documentation, PostgreSQL documentation, PostgreSQL release notes, Reload Configuration, and SQL Shell (psql) with the cursor over it.

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.
Cropped screenshot of a toolbar with two buttons, labelled Durchsuchen and Abfrage, in a grey dialog.

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. 🤔
 
Klaas van Aarsen said:
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 :
Screenshot of a phpPgAdmin SQL query page with the server set to PostgreSQL (localhost:5432), database UebungsDokDat, schema search path student5,public, and an empty SQL text box with Ausführen and Zurücksetzen buttons.


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:
 
mathmari said:
At SQL I get :

So there I will the codes that we wrote above?
Yep. (Sun)
 
Klaas van Aarsen said:
Yep. (Sun)

I get :
Screenshot of a German-language PostgreSQL error: SQL-Fehler, ERROR: column "Ernst Pach" does not exist, LINE 3: WHERE name = "Ernst Pach", with the query SELECT * FROM personal WHERE name = "Ernst Pach" below.


Have I done something wrong or does it mean that in the table that name is not included? :unsure:
 
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. 🤔
 
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:

SQL Fiddle browser window running PostgreSQL 9.6: left editor holds CREATE TABLE personal with name, ort and gehalt columns and an INSERT of 'Ernst Pach', 'Regensburg', 5000; right editor holds SELECT * FROM personal WHERE name = 'Ernst Pach'; results table shows one row: Ernst Pach, Regensburg, 5000.
 
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 :
Screenshot of a German database query result titled Abfrageergebnis, a table with columns posnr, auftrnr, teilenr, anzahl and gesamtpreis listing 11 rows of order positions.


Is that correct? :unsure:
 
Last edited by a moderator:
mathmari said:
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)

mathmari said:
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;
🤔
 
Klaas van Aarsen said:
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
Screenshot of a German database query result titled Abfrageergebnis, a table with columns posnr, auftrnr, teilenr, anzahl and gesamtpreis, listing six rows.


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:
 
mathmari said:
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)
 
Klaas van Aarsen said:
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)
Screenshot of a German database query result table titled Abfrageergebnis, with columns posnr, auftrnr, teilenr, anzahl and gesamtpreis, showing two rows: posnr 31, auftrnr 3, teilenr 100001, anzahl 1, gesamtpreis 1350.00; and posnr 41, auftrnr 4, teilenr 100001, anzahl 1, gesamtpreis 1350.00.


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:)
 
mathmari said:
(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? 🤔
 
Klaas van Aarsen said:
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:
 
mathmari said:
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. 🤔
 
Klaas van Aarsen said:
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:
 
Here are all the given tables :

arbeitsvorgang :
Table structure view of a database table with columns Spalte, Datentyp, Nicht Null, Standardwert, Constraints and Aktionen: avonr integer NOT NULL, bezeichnung character(25), arbzeit character(3) NOT NULL, platznr character(2) NOT NULL, platzbez character(15), kosten numeric(7,2).
auftrag :
Table structure view with columns Spalte, Datentyp, Nicht Null, Standardwert, Constraints, Aktionen and Kommentar, listing four fields: auftrnr (integer, NOT NULL, key icon), datum (date), kundnr (integer, key icon), persnr (integer, key icon).
auftragsposten :
Table structure screenshot with German column headers Spalte, Datentyp, Nicht Null, Standardwert, Constraints and Aktionen; columns posnr, auftnr, teilenr, anzahl and gesamtpreis, with integer, smallint and numeric(10,2) types and key icons on auftnr and teilenr.
avo_belegung :
Table column editor screenshot in German showing rows for columns posnr (integer, NOT NULL, key icon) and avonr (integer, NOT NULL, key icon) and anzahl (smallint), each with Durchsuchen, Ändern, Rechte and Löschen buttons.
kunde :
Table structure view of a database table with columns Spalte, Datentyp, Nicht Null, Standardwert, Constraints, Aktionen and Kommentar; rows nr integer NOT NULL with a key icon, name character(25) NOT NULL, strasse character(30), plz character(5), ort character(20), sperre character(1).

lager :
Table column list from a database design tool, with German headings Spalte, Datentyp, Nicht Null, Standardwert, Constraints, Aktionen and Kommentar. Rows: teilenr integer NOT NULL with key icon; lagerort character(6); bestand smallint NOT NULL; mindbest, reserviert, bestellt smallint. Each row has Durchsuchen, Ändern, Rechte and Löschen buttons.

lieferant :
Table structure view in German showing columns nr (integer, NOT NULL, key icon), name (character(25), NOT NULL), strasse (character(30)), plz (character(5)), ort (character(20)) and sperre (character(1)), each with action buttons Durchsuchen, Ändern, Rechte and Löschen.
personal :
1636989853409.png

teilereservierung :
Screenshot of a German phpMyAdmin table structure view listing columns posnr, teilenr and anzahl with data types, NOT NULL flags, key icons and action buttons.
teilestamm :
phpMyAdmin-style table structure view listing columns teilnr, bezeichnung, preis, mass, einheit, typ, liefernr, zeit and avonr with their data types, NOT NULL flags and key icons.
teilestruktur :
Table structure view listing columns oberteilnr (integer, NOT NULL, key icon), einzelteilnr (integer, NOT NULL, key icon), anzahl (smallint), and einheit (character(2)), each with German action links Durchsuchen, Ändern, Rechte, Löschen.

Do we maybe use any other table from them ? :unsure:
 
mathmari said:
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;

? 🤔
 
Klaas van Aarsen said:
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:
 
mathmari said:
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. 🤔
 
Klaas van Aarsen said:
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:
 
I checked it and it is probably meant the total price ofan order... So we do :

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

Is that correct? :unsure:
 
mathmari said:
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:

mathmari said:
I checked it and it is probably meant the total price of an order... So we do :
Code:
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:
 
mathmari said:
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